|
SQL>
SQL> set feedback on escape ~
SQL>
SQL> CREATE TABLE authors (
2 id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(50),
4 last_name VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (1, 'Marlene', 'Theriault');
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (2, 'Rachel', 'Carmichael');
1 row created.
SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
2 VALUES (3, 'James', 'Viscusi');
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON ESCAPE OFF
SQL>
SQL> CREATE OR REPLACE PROCEDURE author_sel (
2 i_last_name IN AUTHORS.LAST_NAME%TYPE,
3 cv_author IN OUT SYS_REFCURSOR)
4 IS
5 v_last_name AUTHORS.LAST_NAME%TYPE;
6 BEGIN
7
8 v_last_name := '%'||UPPER(i_last_name)||'%';
9
10 OPEN cv_author FOR
11 SELECT id, first_name, last_name
12 FROM authors
13 WHERE UPPER(last_name) LIKE v_last_name;
14
15 EXCEPTION
16 WHEN OTHERS
17 THEN
18 DBMS_OUTPUT.PUT_LINE(sqlerrm);
19 END;
20 /
Procedure created.
SQL>
SQL> COL first_name FORMAT A20
SQL> COL last_name FORMAT A20
SQL>
SQL> VARIABLE x REFCURSOR
SQL> EXEC author_sel('u', :x)
PL/SQL procedure successfully completed.
SQL>
SQL> print x
ID FIRST_NAME LAST_NAME
---------- -------------------- --------------------
1 Marlene Theriault
3 James Viscusi
2 rows selected.
SQL> drop table authors;
Table dropped.
|