PL/SQL Procedure - javatpoint https://www.javatpoint.
com/pl-sql-procedure
Home PL/SQL SQL MySQL MongoDB PostgreSQL SQL Server
1 of 6 11/13/22, 07:19
PL/SQL Procedure - javatpoint https://www.javatpoint.com/pl-sql-procedure
PL/SQL Procedure
The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more specific
tasks. It is just like procedures in other programming languages.
The procedure contains a header and a body.
◦ Header: The header contains the name of the procedure and the parameters or variables passed to
the procedure.
◦ Body: The body contains a declaration section, execution section and exception section similar to a
general PL/SQL block.
How to pass parameters in procedure:
When you want to create a procedure or function, you have to define parameters .There is three ways to
pass parameters in procedure:
1. IN parameters: The IN parameter can be referenced by the procedure or function. The value of the
parameter cannot be overwritten by the procedure or the function.
2. OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the
value of the parameter can be overwritten by the procedure or function.
3. INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the
value of the parameter can be overwritten by the procedure or function.
A procedure may or may not return any value.
PL/SQL Create Procedure
Syntax for creating procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Create procedure example
In this example, we are going to insert record in user table. So you need to create user table first.
2 of 6 11/13/22, 07:19
PL/SQL Procedure - javatpoint https://www.javatpoint.com/pl-sql-procedure
Table creation:
create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.
Procedure Code:
create or replace procedure "INSERTUSER"
(id IN NUMBER,
name IN VARCHAR2)
is
begin
insert into user values(id,name);
end;
/
Output:
Procedure created.
PL/SQL program to call procedure
Let's see the code to call above created procedure.
BEGIN
insertuser(101,'Rahul');
dbms_output.put_line('record inserted successfully');
END;
/
Now, see the "USER" table, you will see one record is inserted.
ID Name
101 Rahul
PL/SQL Drop Procedure
Syntax for drop procedure
DROP PROCEDURE procedure_name;
3 of 6 11/13/22, 07:19
PL/SQL Procedure - javatpoint https://www.javatpoint.com/pl-sql-procedure
Example of drop procedure
DROP PROCEDURE pro1;
← Prev Next →
Youtube For Videos Join Our Youtube Channel: Join Now
Feedback
◦ Send your Feedback to feedback@javatpoint.com
Help Others, Please Share
Learn Latest Tutorials
Splunk SPSS tutorial Swagger T-SQL tutorial Tumblr
tutorial SPSS tutorial Transact-SQL tutorial
Splunk Swagger Tumblr
React tutorial Regex tutorial Reinforcement R RxJS tutorial
ReactJS Regex learning Programming RxJS
tutorial tutorial
Reinforcement R Programming
Learning
React Native Python Design Python Pillow Python Turtle Keras tutorial
tutorial Patterns tutorial tutorial Keras
React Native Python Design Python Pillow Python Turtle
Patterns
4 of 6 11/13/22, 07:19
PL/SQL Procedure - javatpoint https://www.javatpoint.com/pl-sql-procedure
Preparation
Aptitude Logical Verbal Ability Interview Company
Aptitude Reasoning Verbal Ability Questions Interview
Reasoning Interview Questions
Questions Company
Questions
Trending Technologies
Artificial AWS Tutorial Selenium Cloud Hadoop
Intelligence AWS tutorial Computing tutorial
Tutorial Selenium tutorial Hadoop
Artificial Cloud
Intelligence Computing
ReactJS Data Science Angular 7 Blockchain Git Tutorial
Tutorial Tutorial Tutorial Tutorial Git
ReactJS Data Science Angular 7 Blockchain
Machine DevOps
Learning Tutorial
Tutorial DevOps
Machine
Learning
5 of 6 11/13/22, 07:19
PL/SQL Procedure - javatpoint https://www.javatpoint.com/pl-sql-procedure
B.Tech / MCA
DBMS tutorial Data DAA tutorial Operating Computer
DBMS Structures DAA System Network
tutorial tutorial tutorial
Data Structures Operating Computer
System Network
Compiler Computer Discrete Ethical Computer
Design Organization Mathematics Hacking Graphics
tutorial and Tutorial Tutorial Tutorial
Compiler Architecture Discrete Ethical Hacking Computer
Design Computer Mathematics Graphics
Organization
Software html tutorial Cyber Automata C Language
Engineering Web Technology Security Tutorial tutorial
Tutorial tutorial Automata C Programming
Software Cyber Security
Engineering
C++ tutorial Java tutorial .Net Python List of
C++ Java Framework tutorial Programs
tutorial Python Programs
.Net
Control Data Mining Data
Systems Tutorial Warehouse
tutorial Data Mining Tutorial
Control System Data
Warehouse
6 of 6 11/13/22, 07:19
