DBMS/U3 Topic 9 Oracle- Procedures
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.
CREATE[OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
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.
create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.
createor replace procedure “INSERT USER”
(id IN NUMBER,
name IN VARCHAR2)
insert into user values(id,name);
Oracle program to call procedure
Let’s see the code to call above created procedure.
put_line(‘record inserted successfully’);
Now, see the “USER” table, you will see one record is inserted.
Oracle Drop Procedure
DROP PROCEDURE procedure_name;
Example to drop procedure
DROP PROCEDURE pro1;