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.