Databases Reference
In-Depth Information
To search the
EMPLOYEES
table of the schema
HR
by
FIRST_NAME
or
LAST_NAME
field,
we can write the following procedure:
CONNECT hr@TESTDB/hr
CREATE OR REPLACE PACKAGE DYNAMICSQL AS
TYPE T_REFCURSOR IS REF CURSOR;
PROCEDURE SEARCH_EMPLOYEES(
FIRST_NAME IN EMPLOYEES.FIRST_NAME%TYPE,
LAST_NAME IN EMPLOYEES.LAST_NAME%TYPE,
SEARCH_CURSOR OUT T_REFCURSOR);
END;
/
CREATE OR REPLACE PACKAGE BODY DYNAMICSQL AS
PROCEDURE SEARCH_EMPLOYEES(
FIRST_NAME IN EMPLOYEES.FIRST_NAME%TYPE,
LAST_NAME IN EMPLOYEES.LAST_NAME%TYPE,
SEARCH_CURSOR OUT T_REFCURSOR) IS
stmt VARCHAR2(4000);
bindvar varchar2(100);
BEGIN
stmt := 'SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES';
bindvar := NULL;
IF (FIRST_NAME IS NOT NULL) THEN
stmt := stmt || ' WHERE FIRST_NAME LIKE :B ORDER BY
FIRST_NAME';
ELSIF (LAST_NAME IS NOT NULL) THEN
stmt := stmt || ' WHERE LAST_NAME LIKE :B ORDER BY
LAST_NAME';
ELSE
raise_application_error(-20001, 'No values for
FirstName/LastName');
END IF;
bindvar := '%';
OPEN SEARCH_CURSOR FOR stmt USING bindvar;
END;
END;
/
