Working with Stored Procedure Parameters
- WORKING WITH DATE AS INPUT PARAMETER
- WORKING WITH CHARACTER AS INPUT PARAMETER
- WORKING WITH MULTIPLE INPUT PARAMETERS
- PASSING RECORD TYPE AS INPUT PARAMETER
- PASSING TABLE TYPE AS INPUT PARAMETER
- TABLE TYPE AS OUT PARAMETER
- CURSOR AS OUT PARAMETER
WORKING WITH DATE AS INPUT PARAMETER:
CREATE OR REPLACE PROCEDURE SP_EMP_DATE_PARAM(P_HIRE_DATE EMPLOYEES.HIRE_DATE%TYPE)
IS
BEGIN
FOR I IN (SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY
FROM EMPLOYEES
WHERE HIRE_DATE >= P_HIRE_DATE
)
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> JOB_ID -> '||I.JOB_ID||' HIRE_DATE -> '||I.HIRE_DATE||' -> SALARY -> '||I.SALARY);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/ EXEC SP_EMP_DATE_PARAM(TO_DATE('01-JAN-2008', 'DD-MON-RRRR')); WORKING WITH CHARACTER AS INPUT PARAMETER:
CREATE OR REPLACE PROCEDURE SP_EMP_CHAR_PARAM(P_JOB_ID EMPLOYEES.JOB_ID%TYPE)
IS
BEGIN
FOR I IN (SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY
FROM EMPLOYEES
WHERE JOB_ID = P_JOB_ID
)
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> JOB_ID -> '||I.JOB_ID||' HIRE_DATE -> '||I.HIRE_DATE||' -> SALARY -> '||I.SALARY);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/ WORKING WITH MULTIPLE INPUT PARAMETERS:
CREATE OR REPLACE PROCEDURE SP_EMP_MULTI_PARAM(P_HIRE_DATE EMPLOYEES.HIRE_DATE%TYPE,
P_JOB_ID EMPLOYEES.JOB_ID%TYPE,
P_DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
BEGIN
FOR I IN (SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
HIRE_DATE,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPARTMENT_ID
AND JOB_ID = P_JOB_ID
AND HIRE_DATE >= P_HIRE_DATE
)
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> JOB_ID -> '||I.JOB_ID||' HIRE_DATE -> '||I.HIRE_DATE||' -> SALARY -> '||I.SALARY);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/ EXEC SP_EMP_MULTI_PARAM(TO_DATE('01-JAN-2002', 'DD-MON-RRRR'), 'ST_MAN', 50); PASSING RECORD TYPE AS INPUT PARAMETER:
CREATE OR REPLACE PACKAGE PKG_INIT_COLL_REC IS TYPE EMP_REC IS RECORD (EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE, FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE, LAST_NAME EMPLOYEES.LAST_NAME%TYPE, JOB_ID EMPLOYEES.JOB_ID%TYPE, SALARY EMPLOYEES.SALARY%TYPE ); V_EMP_REC EMP_REC; END; /
CREATE OR REPLACE PROCEDURE SP_GET_EMP_DTL_REC(P_EMP_REC PKG_INIT_COLL_REC.EMP_REC)
IS
V_EMP_REC PKG_INIT_COLL_REC.EMP_REC;
BEGIN
V_EMP_REC := P_EMP_REC;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE INFO -> ID -> '||V_EMP_REC.EMPLOYEE_ID||'-> NAME -> '||V_EMP_REC.FIRST_NAME||' '||V_EMP_REC.LAST_NAME||' -> JOB -> '||V_EMP_REC.JOB_ID||' -> SALARY -> '||V_EMP_REC.SALARY);
END;
/ SET SERVEROUTPUT ON;
DECLARE
V_EMP_REC PKG_INIT_COLL_REC.EMP_REC;
BEGIN
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY
INTO V_EMP_REC
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
--
SP_GET_EMP_DTL_REC(V_EMP_REC);
END;
/ PASSING TABLE TYPE AS INPUT PARAMETER:
CREATE OR REPLACE PACKAGE PKG_INIT_COLL IS TYPE EMP_REC IS RECORD (EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE, FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE, LAST_NAME EMPLOYEES.LAST_NAME%TYPE, JOB_ID EMPLOYEES.JOB_ID%TYPE, SALARY EMPLOYEES.SALARY%TYPE ); TYPE EMP_TAB IS TABLE OF EMP_REC INDEX BY PLS_INTEGER; V_EMP_TAB EMP_TAB; END; /
CREATE OR REPLACE PROCEDURE SP_GET_EMP_DTL(P_EMP_TAB PKG_INIT_COLL.EMP_TAB)
IS
V_EMP_TAB PKG_INIT_COLL.EMP_TAB;
BEGIN
V_EMP_TAB := P_EMP_TAB;
FOR I IN 1..V_EMP_TAB.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE INFO -> ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||'-> NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' -> JOB -> '||V_EMP_TAB(I).JOB_ID||' -> SALARY -> '||V_EMP_TAB(I).SALARY);
END LOOP;
END;
/ SET SERVEROUTPUT ON;
DECLARE
V_EMP_TAB PKG_INIT_COLL.EMP_TAB;
BEGIN
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY
BULK COLLECT INTO V_EMP_TAB
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;
--
SP_GET_EMP_DTL(V_EMP_TAB);
END;
/ TABLE TYPE AS OUT PARAMETER:
CREATE OR REPLACE PROCEDURE SP_GET_EMP_DTL_OUT(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE,
P_EMP_TAB OUT PKG_INIT_COLL.EMP_TAB)
IS
V_EMP_TAB PKG_INIT_COLL.EMP_TAB;
BEGIN
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
JOB_ID,
SALARY
BULK COLLECT INTO V_EMP_TAB
FROM EMPLOYEES
WHERE DEPARTMENT_ID = P_DEPTNO;
--
P_EMP_TAB := V_EMP_TAB;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/ SET SERVEROUTPUT ON;
DECLARE
V_EMP_TAB PKG_INIT_COLL.EMP_TAB;
BEGIN
SP_GET_EMP_DTL_OUT(30, V_EMP_TAB);
FOR I IN 1..V_EMP_TAB.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('EMPLOYEE INFO -> ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||'-> NAME -> '||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' -> JOB -> '||V_EMP_TAB(I).JOB_ID||' -> SALARY -> '||V_EMP_TAB(I).SALARY);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/ CURSOR AS OUT PARAMETER:
CREATE OR REPLACE PROCEDURE SP_GET_EMP_DTL_CUR_OUT(P_DEPTNO EMPLOYEES.DEPARTMENT_ID%TYPE, P_EMP_CUR OUT SYS_REFCURSOR) IS V_EMP_CUR SYS_REFCURSOR; BEGIN OPEN V_EMP_CUR FOR SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = P_DEPTNO; -- P_EMP_CUR := V_EMP_CUR; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM); END; /
SET SERVEROUTPUT ON;
DECLARE
V_EMP_CUR SYS_REFCURSOR;
V_EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE := NULL;
V_FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE := NULL;
V_LAST_NAME EMPLOYEES.LAST_NAME%TYPE := NULL;
V_JOB_ID EMPLOYEES.JOB_ID%TYPE := NULL;
V_SALARY EMPLOYEES.SALARY%TYPE := NULL;
BEGIN
SP_GET_EMP_DTL_CUR_OUT(30, V_EMP_CUR);
LOOP
FETCH V_EMP_CUR INTO V_EMPLOYEE_ID,
V_FIRST_NAME,
V_LAST_NAME,
V_JOB_ID,
V_SALARY;
EXIT WHEN V_EMP_CUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE INFO -> ID -> '||V_EMPLOYEE_ID||'-> NAME -> '||V_FIRST_NAME||' '||V_LAST_NAME||' -> JOB -> '||V_JOB_ID||' -> SALARY -> '||V_SALARY);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/ RELATED TOPICS: