PROCEDURE in Oracle

ORACLE PROCEDURE 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. Students Table:

Procedure Code:

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;

Output:

Procedure created.

Call Procedure Code:

BEGIN    
INSERT_RECORD (6, 'Tom', 18);  
dbms_output.put_line('SUCCESS');    
END;

Output:

Explanation: 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;   

Explanation: The INSERT_RECORD is an already existing procedure. The above code will eliminate the INSERT_RECORD procedure from the Oracle database.

Please follow and like us: