Lab 20.1 Exercises


20.1.1 Use the EXECUTE IMMEDIATE Statement

Create the following PL/SQL script:

 
 -- ch20_1a.sql, version 1.0 SET SERVEROUTPUT ON DECLARE    sql_stmt VARCHAR2(200);    v_student_id NUMBER := &sv_student_id;    v_first_name VARCHAR2(25);    v_last_name VARCHAR2(25); BEGIN    sql_stmt := 'SELECT first_name, last_name'                '  FROM student'                              ' WHERE student_id = :1';    EXECUTE IMMEDIATE sql_stmt    INTO v_first_name, v_last_name    USING v_student_id;    DBMS_OUTPUT.PUT_LINE ('First Name: 'v_first_name);    DBMS_OUTPUT.PUT_LINE ('Last Name:  'v_last_name); END; 

Execute the script, then complete the following exercises:

a)

Explain the script ch20_1a.sql shown above.

b)

Modify the script so that the student's address (street, city, state, and zip code) is displayed on the screen as well.

c)

Modify the script created in the previous exercise (ch20_1b.sql) so that the SELECT statement can be run against either the STUDENT or INSTRUCTOR table. In other words, a user can specify table name used in the SELECT statement at run-time.




Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net