Stored procedure oracle - oracle tutorial - sql tutorial
What is procedure in Oracle ?
- A procedure is a group of PL/SQL statements that can be called by name.
- The call specification (sometimes called call spec) specifies a java method or a third-generation language routine so that it can be called from SQL and PL/SQL.
Oracle Procedure Query Execution
Create Procedure
Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
click below button to copy the code. By - oracle tutorial - team
- Following are the three types of procedures that must be defined to create a procedure.
- IN: It is a default parameter. It passes the value to the subprogram.
- OUT: It must be specified. It returns a value to the caller.
- IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.
Oracle Create procedure example
- In this example, we are going to insert record in the "user" table. So you need to create user table first.
Table creation:
create table user(id number(10) primary key,name varchar2(100));click below button to copy the code. By - oracle tutorial - team
- 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;
/
click below button to copy the code. By - oracle tutorial - team
Output:
Procedure created.
Oracle 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;
/
click below button to copy the code. By - oracle tutorial - team
- Now, see the "USER" table, you will see one record is inserted.
| ID | Name |
|---|---|
| 101 | Smith |
Oracle Drop Procedure
Syntax
DROP PROCEDURE procedure_name;
click below button to copy the code. By - oracle tutorial - team
Example to drop procedure
DROP PROCEDURE pro1;
click below button to copy the code. By - oracle tutorial - team
Oracle Stored Procedures Example
Stored Procedure Vs Functions
