A group of PL/SQL statements is called a procedure and what makes it unique is that it can be called by name. The call spec or the call specification is utilised to call a Java method or a third-generation language routine from SQL and PL/SQL, and to serve this purpose it specifies their existence.
There are three types of parameters that must be specified while creating a procedure.
- IN: Being a default parameter, it is used to pass a value to the subprogram.
- OUT: When specified, it is used to return a value to the caller.
- IN OUT: When specified, it is used to get an updated value to the caller by passing an initial value to the subprogram.
Syntax: To create or replace a procedure.
CREATE OR REPLACE PROCEDURE procedure_name (parameters) IS declaration_section BEGIN executable_section EXCEPTION exception_section END procedure_name;
Syntax: To drop a procedure.
DROP PROCEDURE procedure_name;
Example: Creating a procedure.
ID NAME AGE 1 Joy 20 2 Smiley 19 3 Happy 21 4 James 22 5 Bond 25
Create or replace procedure "INSERT_RECORD" (id IN NUMBER, name IN VARCHAR2, age IN NUMBER) is begin insert into students values(id,name,age); end;
Call Procedure Code:
BEGIN INSERT_RECORD (6, 'Tom', 18); dbms_output.put_line('SUCCESS'); END;
ID NAME AGE 1 Joy 20 2 Smiley 19 3 Happy 21 4 James 22 5 Bond 25 6 Tom 18
The ‘students’ is an already existing table. We first created a procedure for the insertion of new data in the ‘students’ table. After calling the procedure code we inserted the required data into the concerned table. The newly inserted records can be checked in the ‘students’ table.
Example: Eliminating a procedure.
DROP PROCEDURE INSERT_RECORD;
The INSERT_RECORD is an already existing procedure. The above code will eliminate the INSERT_RECORD procedure from the Oracle database.