Stored Procedures and Functions

Creating Stored Procedures and Functions

---

1. Procedures and Functions

---

1. Are named PL/SQL Blocks
2. Are called PL/SQL Subprograms
3. Have block structures similiar to anonymous Blocks

• Optional Declarative Section
• Mandatory executable section
• Optional section to handle exceptions

---

---

2. Procedure : Syntax

---

CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
…)]
IS|AS
procedure_body;

procedure_name – Name of the procedure to be created
argument – parameters
mode – Mode of argument IN, OUT, IN OUT
datatype – datatype of the parameter
procedure_body – Executable section of the Procedure

— PL/SQL Block to Insert Department Details into a Department Table using Procedure —

CREATE TABLE dept_new AS SELECT * FROM dept WHERE 1=2
/

CREATE OR REPLACE PROCEDURE add_dept_proc IS
dept_id dept_new.deptno%TYPE;
dept_name dept_new.dname%TYPE;
BEGIN
dept_id:=10;
dept_name:=’Marketing’;
INSERT INTO dept_new(deptno,dname) VALUES(dept_id,dept_name);
DBMS_OUTPUT.PUT_LINE(‘Inserted ‘||SQL%ROWCOUNT ||’ rows ‘);
END;
/

— Check the Objects created from USER_OBJECTS —
SELECT object_name,object_type FROM user_objects
WHERE object_type = ‘PROCEDURE’ AND object_name = ‘ADD_DEPT_PROC’
/

— Check the Source Code of the procedure created from USER_SOURCE —

SELECT text FROM user_source WHERE name=’ADD_DEPT_PROC’
/

— PL/SQL Block to invoke the Procedure —

SELECT * FROM dept_new
/

BEGIN
add_dept_proc;
END;
/

execute add_dept_proc;

SELECT * FROM dept_new
/

DROP TABLE dept_new PURGE
/

— PL/SQL Block to display Employee Details using Procedure —
CREATE OR REPLACE PROCEDURE employer_details_proc
IS
CURSOR emp_cur IS SELECT empno,ename FROM emp;
emp_rec emp_cur%rowtype;
BEGIN
FOR emp_rec in emp_cur
LOOP
dbms_output.put_line(emp_rec.empno || ‘ ‘ ||emp_rec.ename);
END LOOP;
END;
/

— PL/SQL Block to invoke the Procedure —

BEGIN
employer_details_proc;
END;
/

execute employer_details_proc

---

---

3. Passing Parameter Procedure

---

1. IN Parameter Mode

— PL/SQL Block to pass parameters to a Procedure —

CREATE or replace PROCEDURE emp_name_proc(p_empno emp.empno%TYPE)
IS
l_name emp.ename%TYPE;
BEGIN
SELECT ename INTO l_name FROM emp
WHERE empno = p_empno;
DBMS_OUTPUT.PUT_LINE(‘Employee Name is : ‘||l_name);
END;
/

I cannnot pass a NULL Value to a Parameter defined inside a Procedure.
EXEC emp_name_proc;

EXECUTE emp_name_proc(7369);

Invoke the Procedure in PL/SQL Block
DECLARE
l_empno emp.empno%TYPE:=&empno;
BEGIN
emp_name_proc(l_empno);
END;
/

2. IN and OUT Parameter Mode

— PL/SQL Block to pass parameters to a Procedure —

CREATE OR REPLACE PROCEDURE emp_name_proc(p_empno emp.empno%TYPE,p_ename OUT emp.ename%TYPE)
IS
BEGIN
SELECT ename INTO p_ename FROM emp
WHERE empno = p_empno;
END;
/

VARIABLE name VARCHAR2(25);
EXECUTE emp_name_proc(7369,:name);
PRINT name

Invoke the Procedure in PL/SQL Block
DECLARE
l_ename emp.ename%TYPE;
CURSOR id_cur IS SELECT empno FROM emp;
BEGIN
FOR emp_rec IN id_cur
LOOP
emp_name_proc(emp_rec.empno,l_ename);
DBMS_OUTPUT.PUT_LINE(‘The Employee ‘||l_ename||’ has ID ‘||emp_rec.empno);
END LOOP;
END;
/

1. We create a Cursor ‘id_cur’ which contains the employee number.
2. We are calling the Procedure ‘emp_name’, we are passing the ‘empno’ as IN parameter and ‘l_ename’ as OUT parameter.
3. Using DBMS_OUTPUT Package we are displaying the ID and Employee Name returned from the procedure ‘emp_name’.
4. IN OUT Parameter Mode

— PL/SQL Block to pass parameters to a Procedure —

CREATE OR REPLACE PROCEDURE emp_salary_increase_proc(p_empno IN emp.empno%TYPE,p_sal IN OUT emp.sal%TYPE)
IS
tmp_sal NUMBER;
BEGIN
SELECT sal INTO tmp_sal FROM emp
WHERE empno = p_empno;
IF tmp_sal BETWEEN 700 AND 1000 THEN
p_sal:=tmp_sal * 1.2;
ELSIF tmp_sal BETWEEN 1100 AND 2000 THEN
p_sal:=tmp_sal * 1.3;
ELSIF tmp_sal > 3000 THEN
p_sal:=tmp_sal * 1.4;
END IF;
END;
/

— Invoke the Procedure in PL/SQL Block —
DECLARE
pre_sal NUMBER;
CURSOR updated_sal IS SELECT empno,sal FROM emp;
BEGIN
FOR emp_rec IN updated_sal
LOOP
pre_sal:=emp_rec.sal;
emp_salary_increase_proc(emp_rec.empno,emp_rec.sal);
DBMS_OUTPUT.PUT_LINE(‘The Salary of ‘||emp_rec.empno||’ increased from ‘||pre_sal||’ to ‘||emp_rec.sal);
END LOOP;
END;
/

Named vs Positional Parameter In Procedures

---

CREATE OR REPLACE PROCEDURE emp_name_proc(p_empno emp.empno%TYPE,p_ename emp.ename%TYPE)
IS
v_sal emp.sal%TYPE;
v_hiredate emp.hiredate%TYPE;
BEGIN
SELECT sal,hiredate INTO v_sal,v_hiredate FROM emp
WHERE empno = p_empno AND ename = p_ename;
DBMS_OUTPUT.PUT_LINE(‘Salary is ‘||v_sal||’ and Hiring Date falls on ‘||v_hiredate);
END;
/

— Using Positional Parameter —
BEGIN
emp_name_proc(7369,’SMITH’);
END;
/

— Using Named Parameter —
BEGIN
emp_name_proc(p_ename =>’SMITH’,
p_empno =>7369);
END;
/

— Please do not execute this PL/SQL Program —

CREATE OR REPLACE PROCEDURE sp_drop_all_tables
AS
CURSOR c_tables IS SELECT object_name FROM user_objects WHERE object_type=’TABLE’
AND object_name NOT IN(‘EMP’,’DEPT’,’SALGRADE’,’BONUS’);
v_object_name user_objects.object_name%TYPE;
l_sqlstmt VARCHAR2(500);
BEGIN
OPEN c_tables; — open the cursor before fetching
LOOP
FETCH c_tables INTO v_object_name;
EXIT WHEN c_tables%NOTFOUND;
l_sqlstmt:=’DROP TABLE ‘||v_object_name||’ CASCADE CONSTRAINTS PURGE’;
EXECUTE IMMEDIATE l_sqlstmt;
END LOOP;
CLOSE c_tables;
END sp_drop_all_tables;
/

EXECUTE sp_drop_all_tables;

---

---

4. RETURNING Clause

---

The RETURNING Clause :

1. Improves performance by returning column values with INSERT,UPDATE and DELETE statements.
2. Eliminates the need for a SELECT Statement.

CREATE TABLE test_emp
AS SELECT * FROM emp
/

— Example of without a RETURNING clause —

CREATE OR REPLACE procedure update_salary_proc(emp_id NUMBER)
IS
name test_emp.ename%TYPE;
new_sal test_emp.sal%TYPE;
BEGIN
UPDATE test_emp
SET sal = sal * 1.1
WHERE empno = emp_id;
SELECT ename,sal
INTO name,new_sal
FROM test_emp
WHERE empno = emp_id;
DBMS_OUTPUT.PUT_LINE(‘The Updated Salary for the Employee ‘||name||’ is : ‘||new_sal);
END update_salary_proc;
/

select * from test_Emp
/

EXEC update_salary_proc(7369);

SELECT * FROM test_emp WHERE empno = 7369
/

— Example of RETURNING clause —

CREATE OR REPLACE procedure update_salary_proc(emp_id NUMBER)
IS
name test_emp.ename%TYPE;
new_sal test_emp.sal%TYPE;
BEGIN
UPDATE test_emp
SET sal = sal * 1.1
WHERE empno = emp_id
RETURNING ename,sal INTO name,new_sal;
DBMS_OUTPUT.PUT_LINE(‘The Updated Salary for the Employee ‘||name||’ is : ‘||new_sal);
END update_salary_proc;
/

SET SERVEROUTPUT ON

EXEC update_salary_proc(7369)

SELECT * FROM test_emp WHERE empno = 7369
/

DROP TABLE test_emp PURGE
/

---

---

5. Forward Declarations in PL/SQL Procedures

---

If nested subprograms in the same PL/SQL block invoke each other,then one requires a Forward Declaration,because a Subprogram must be declared before it can be invoked.

A Forward Declaration declares a nested subprogram but does not define it. We must define it later in the same block.

— Example to illustrate the Forward Declaration —
DECLARE
v_number1 NUMBER:=&n1;
v_number2 NUMBER:=&n2;

— Declare proc1 (Forward Declaration Procedure but not yet defined) —
PROCEDURE proc1(number1 NUMBER);

— Declare and Define proc2 Procedure and call the proc1 —
PROCEDURE proc2(number2 NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This Procedure proc2 is calling the proc1 which is not yet defined’);
proc1(v_number1);
END proc2;

— Define proc1 Procedure —
PROCEDURE proc1(number1 NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This Procedure proc1 is calling the proc2’);
proc2(v_number2);
END proc1;

BEGIN
DBMS_OUTPUT.PUT_LINE(‘Complete Illustration of Forward Declarations’);
DBMS_OUTPUT.PUT_LINE(‘Procedure 1 Value ‘||v_number1);
DBMS_OUTPUT.PUT_LINE(‘Procedure 2 Value ‘||v_number2);
END;
/

---

---

7. AUTHID Current User in Procedure

---

The AUTHID Clause instructs Oracle as to whether the routine is to be run with the invoker’s rights (CURRENT_USER),or with the Owner’s right (DEFINER). If the clause is not specified,Oracle will default to using the AUTHID DEFINER.

— Example to illustrate the Default Definer Rights —

1.
— Connect to SYS User —
CONNECT sys/sys@orcl AS SYSDBA;

2.
— Create the First User (Table,Procedure Owner) —
CREATE USER test1 IDENTIFIED BY test1
/

3.
— Create the Second User (User who has been granted the Procedure Executon Rights) —
CREATE USER test2 IDENTIFIED BY test2
/

4.
— Grant all the Rights to User1 —
GRANT create session,create table,create procedure,unlimited tablespace TO test1
/

5.
— Grant certain Rights to User2 —
GRANT create session,execute any procedure TO test2
/

6.
— Connect to User1 for Creating the Table —
CONNECT test1/test1@orcl;

7.
— Create Claims Table in User1 —
CREATE TABLE claims_test_tab
(id NUMBER,sal NUMBER)
/

8.
— Create the Procedure pay_claim() in User1 —
CREATE OR REPLACE PROCEDURE pay_claim(p_id NUMBER,p_sal NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Without AUTHID Current User Clause in Procedure’);
INSERT INTO claims_test_tab VALUES(p_id,p_sal);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||’ Records Inserted’);
END pay_claim;
/

9. 
   — Connect to User2 for executing the Procedure —
   CONNECT test2/test2@orcl;

EXECUTE test1.pay_claim(1,5000);

10.
— Reconnect to User1 for testing the Result —
CONNECT test1/test1@orcl;

SELECT * FROM claims_test_tab
/

EXECUTE pay_claim(1,500);

— Example to illustrate the Invoker’s Rights (AUTHID CURRENT_USER) —

— Create the Procedure pay_claim() in Test1 User —
CREATE PROCEDURE pay_claim(p_id NUMBER,p_sal NUMBER)
AUTHID CURRENT_USER
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘With AUTHID Current User Clause in Procedure’);
INSERT INTO claims_test_tab VALUES(p_id,p_sal);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||’ Records Inserted’);
END pay_claim;
/

CONNECT test2/test2@orcl;

EXECUTE test1.pay_claim(1,5000);

This Execute statement will not allow the Invoke User to EXECUTE the Procedure even though he has been granted the EXECUTE ANY PROCEDURE.

To create a procedure in your own schema, you must have the CREATE PROCEDURE system privilege. To create a procedure in another user’s schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a procedure in another schema, you must have the ALTER ANY PROCEDURE system privilege.

---

---

7. Function

---

Syntax
CREATE [OR REPLACE] FUNCTION function_name
[(argument1 [mode1] datatype1,
argument2 [mode2] datatype2,
…)]
RETURN datatype
IS|AS
function_body;

A Function is a named PL/SQL Block similar to a procedure. The major difference between a Procedure and a Function is a Function must always return a value but a procedure may or may not return a value.

CREATE OR REPLACE FUNCTION check_sal(p_empno emp.empno%TYPE) RETURN BOOLEAN
IS
v_deptno emp.deptno%TYPE;
v_sal emp.sal%TYPE;
v_avg_sal emp.sal%TYPE;
BEGIN
SELECT sal,deptno INTO v_sal,v_deptno
FROM emp WHERE empno = p_empno;
SELECT avg(sal) INTO v_avg_sal FROM emp
WHERE deptno = v_deptno;
IF v_sal > v_avg_sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/

This Function check_sal Function is written to determine whether the salary of a particular employee is greater than or less than the average salary of all employees working in the same department.

The function returns a value of TRUE if the salary of the Employee is greater than the average salary of employees in the department if not it returns FALSE.
The Function returns NULL if a NO_DATA_FOUND exception is thrown.

— Invoking the created Function through PL/SQL Block —
DECLARE
v_empno emp.empno%TYPE:=&empno;
BEGIN
IF (check_sal(v_empno) IS NULL) THEN
DBMS_OUTPUT.PUT_LINE(‘The Function returned NULL due to Exception’);
ELSIF (check_sal(v_empno)) THEN
DBMS_OUTPUT.PUT_LINE(‘Salary > Average Salary’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Salary < Average Salary’);
END IF;
END;
/

CREATE or replace FUNCTION total_comp(p_sal IN emp.sal%TYPE,p_bonus IN emp.comm%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN p_sal + NVL(p_bonus,0);
END;
/

CREATE or replace FUNCTION total_comp(p_sal IN emp.sal%TYPE,p_bonus IN emp.comm%TYPE)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
v_result:=p_sal + NVL(p_bonus,0);
RETURN v_result;
END;
/

— Invoke the SQL Function using SQL Statement —

SELECT total_comp(100,3) FROM DUAL
/

— PL/SQL Function to demonstrate a Function with OUT Parameter —
CREATE OR REPLACE FUNCTION inout_func(inparam NUMBER,outparm OUT NUMBER)
RETURN NUMBER
IS
salary NUMBER;
BEGIN
SELECT sal INTO salary FROM emp WHERE empno = inparam;
RETURN salary;
END inout_func;
/

— PL/SQL Block to invoke the Function —
DECLARE
retval NUMBER;
CURSOR emp_cursor IS SELECT empno FROM emp;
v_empno emp.empno%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno;
retval:=inout_func(v_empno,retval); — Value returned by the Function with OUT —
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(retval);
END LOOP;
CLOSE emp_cursor;
END;
/

— PL/SQL Function to demonstrate a Function with OUT Parameter —
CREATE OR REPLACE FUNCTION inout_func(inout_param IN OUT NUMBER)
RETURN NUMBER
IS
salary NUMBER;
BEGIN
SELECT sal INTO salary FROM emp WHERE empno = inout_param;
RETURN salary;
END inout_func;
/

— PL/SQL Block to invoke the Function —
DECLARE
retval NUMBER;
CURSOR emp_cursor IS SELECT empno FROM emp;
v_empno emp.empno%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno;
retval:=inout_func(v_empno); — Value returned by the Function with IN OUT —
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(retval);
END LOOP;
CLOSE emp_cursor;
END;
/

— Example of a Function without any IN Parameter —
CREATE OR REPLACE FUNCTION test_func
RETURN NUMBER
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Function without any parameter’);
RETURN 0;
END test_func;
/

We cannot execute this Function using the EXECUTE keyword.
EXECUTE test_func; — This will error out —

We can execute this Function from a PL/SQL Block, Procedure.
DECLARE
a NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Function executed without any parameter from this PL/SQL Block’);
a:=test_func;
DBMS_OUTPUT.PUT_LINE(‘Function returned a Value ‘||a);
END;
/

But we can run this Function from a SQL statement
SELECT test_func FROM dual
/

---

---

8. How to Write DML Statements inside a Function

---

We cannot place any DML statement inside a function simply and also a function with a DML statement cannot be used inside a SELECT query.

CREATE TABLE test_employee AS SELECT * FROM emp
WHERE 1 = 2
/

— Example to demonstrate writing a DML statement inside a Function —
CREATE OR REPLACE FUNCTION
fun1(i_empno NUMBER) RETURN NUMBER
IS
i_count NUMBER;
BEGIN
INSERT INTO test_employee(empno)VALUES(i_empno);
i_count:=SQL%ROWCOUNT;
COMMIT;
RETURN i_count;
END fun1;
/

— Execute this Function from a SQL Query —
SELECT fun1(7777) FROM dual
/

This SQL Query will error out.
But the same function can be executed through a PL/SQL Block.

DECLARE
a NUMBER;
BEGIN
a:=fun1(7777);
DBMS_OUTPUT.PUT_LINE(‘Inserted Value is : ‘||a);
END;
/

DROP TABLE test_employee PURGE
/

---

---

9. Declaring and Defining a Function inside a Procedure

---

CREATE OR REPLACE PROCEDURE test_proc
AS
l_dt date;
— Declare and Define the function —
FUNCTION dt
RETURN date
IS
BEGIN
RETURN sysdate;
END dt;
—
BEGIN
l_dt:= dt;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_dt,’dd-mm-yyyy’));
END test_proc;
/

EXECUTE test_proc;

---

---