Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

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;

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;