Oracle Procedures

  • Procedures are named block, which is to solve particular task. Procedure may or may not return values.
  • Whenever we are using CREATE OR REPLACE keyword in front of the procedure, those procedures are permanently stored in the database. Because of this procedure is also called as stored procedure.
  • Procedure is used to improve performance of the application.
    Because procedure internally having one time compilation in all database system.

Procedures are having two parts
1. Procedure specification: Declaration of procedure name, parameters, variable.
2. Body: PL/SQL Block code.

Syntax:
CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(arguments)
IS/AS
Variable/Cursor declaration/Exception
BEGIN
Statements;
EXCEPTION
Statements;
END;

Procedure execution:
METHOD 1: EXEC PROCEDURE_NAME(parameter);

METHOD 2: Using a anonymous block
BEGIN
PROCEDURE_NAME(parameter);
END;

METHOD 3: Using call statement
sql>CALL PROCEDURE_NAME(parameter);

Example:

CREATE OR REPLACE PROCEDURE p1(p_empno number)
is
p_name varchar2(20);
p_sal number(10);
BEGIN
SELECT ename, sal INTO p_name, v_sal from emp
WHERE empno=p_empno;
dbmsoutput.put_line(p_ename||’ ‘||p_sal);
END;
/

Procedure Execution:
Execution Method 1:
sql> EXEC p1(7499);

Execution Method 2:
sql> BEGIN
p1(7876);
END;

Execution Method 3:
sql> call p1(7900);

Procedure Parameters:
In all database systems, subprograms having parameters. These parameters are used to pass values into procedure and retrun values from the procedure.

Syntax: parameter_name mode_type;

Parameters support three modes:
IN: In is used to pass the values into procedure body. In mode behaves like a constant in procedure body. Procedure default mode is IN.

Syntax: parameter in datatype;

Example:

CREATE OR REPLACE PROCEDURE p2(p_deptno in number, p_dname in varchar2, p_loc in varachar2))
is
BEGIN
INSERT INTO dept values(p_name,p_dname, p_loc);
dbmsoutput.put_line(‘Records are added successfully through procedure.’);
END;
/

OUT: Out mode is used to return values from the procedure. Out mode behaves like uninitialized variable in procedure body. Here explicitly we must specify the out keyword.

Syntax:parameter out datatype

Example:

CREATE OR REPLACE PROCEDURE p3(p_ename in varachar2, p_sal out number)
is
BEGIN
SELECT sal into p_sal from emp
WHERE ename=p_name;
END;
/

Execution: In Oracle when a procedure having out or in out parameters, those procedures are executed using bind variable and anonymous block.

Using Bind varaible:
sql> varaible a number;
sql> exec p3(‘KING’,a);

Using anonymous block:
sql> DECLARE
k number(10);
BEGIN
P3(‘SMITH’,k);
dbmsoutput.put_line(k);
END;
/

INOUT: This mode is used to pass the values into procedure and return values from the procedure. This mode behaves like constant initialized variable in procedure body.

Syntax:parameter inout datatype

Example:

CREATE OR REPLACE PROCEDURE p4(p_x inout number)
IS
BEGIN
SELECT sal INTO p_x FROM emp
WHERE emp=p_x;
END;
/

Execution:
sql> variable a number;
sql> exec p4(:a);
sql> print a;

Powered by k2schools