Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources]

Benjamin Rosenzweig

نسخه متنی -صفحه : 289/ 154

Lab 12.2 Exercises

12.2.1 Use IN and OUT Parameters with Procedures

Create the following text file in a text editor. Run the script at a SQL*Plus session.

-- ch12_02a.sql CREATE OR REPLACE PROCEDURE find_sname (i_student_id IN NUMBER, o_first_name OUT VARCHAR2, o_last_name OUT VARCHAR2 ) AS BEGIN SELECT first_name, last_name INTO o_first_name, o_last_name FROM student WHERE student_id = i_student_id; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error in finding student_id: '||i_student_id); END find_sname;

a)	Explain what is happening in the find_sname procedure. What parameters are being passed into and out of the procedure? How would you call the procedure?

Call the find_sname script with the following anonymous block:

-- ch12_03a.sql DECLARE v_local_first_name student.first_name%TYPE; v_local_last_name student.last_name%TYPE; BEGIN find_sname (145, v_local_first_name, v_local_last_name); DBMS_OUTPUT.PUT_LINE ('Student 145 is: '||v_local_first_name|| ' '|| v_local_last_name||'.' ); END;

b)	Explain the relationship between the parameters that are in the procedures header definition versus the parameters that are passed IN and OUT of the procedure.