《Oracle PL/SQL实例精讲》学习笔记18——本地动态SQL （第一部分）

本章内容：

1. EXECUTE IMMEDIATE语句

2. OEN-FOR、FETCH和CLOSE语句

代码如下：

1. EXECUTE IMMEDIATE语句

SQL> DECLARE
  2     sql_stmt VARCHAR2(200);
  3     v_student_id NUMBER := &sv_student_id;
  4     v_first_name VARCHAR2(25);
  5     v_last_name VARCHAR2(25);
  6  BEGIN
  7     sql_stmt := 'SELECT first_name, last_name'||
  8                 '  FROM student'              ||
  9                 ' WHERE student_id = :1';
 10     EXECUTE IMMEDIATE sql_stmt
 11     INTO v_first_name, v_last_name
 12     USING v_student_id;
 13
 14     DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
 15     DBMS_OUTPUT.PUT_LINE ('Last Name:  '||v_last_name);
 16  END;
 17  /
Enter value for sv_student_id: 103
old   3:    v_student_id NUMBER := &sv_student_id;
new   3:    v_student_id NUMBER := 103;
First Name: J.
Last Name:  Landry

PL/SQL procedure successfully completed.

SQL> DECLARE
  2     sql_stmt VARCHAR2(200);
  3     v_student_id NUMBER := &sv_student_id;
  4     v_first_name VARCHAR2(25);
  5     v_last_name VARCHAR2(25);
  6     v_street VARCHAR2(50);
  7     v_city VARCHAR2(25);
  8     v_state VARCHAR2(2);
  9     v_zip VARCHAR2(5);
 10  BEGIN
 11     sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
 12                 '      ,b.city, b.state, b.zip'                     ||
 13                 '  FROM student a, zipcode b'                       ||
 14                 ' WHERE a.zip = b.zip'                              ||
 15                 '   AND student_id = :1';
 16     EXECUTE IMMEDIATE sql_stmt
 17     INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip
 18     USING v_student_id;
 19
 20     DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
 21
 22     DBMS_OUTPUT.PUT_LINE ('Last Name:  '||v_last_name);
 23     DBMS_OUTPUT.PUT_LINE ('Street:     '||v_street);
 24     DBMS_OUTPUT.PUT_LINE ('City:       '||v_city);
 25     DBMS_OUTPUT.PUT_LINE ('State:      '||v_state);
 26     DBMS_OUTPUT.PUT_LINE ('Zip Code:   '||v_zip);
 27  END;
 28  /
Enter value for sv_student_id: 102
old   3:    v_student_id NUMBER := &sv_student_id;
new   3:    v_student_id NUMBER := 102;
First Name: Fred
Last Name:  Crocitto
Street:     101-09 120th St.
City:       Richmond Hill
State:      NY
Zip Code:   11419

SQL> DECLARE
  2     sql_stmt VARCHAR2(200);
  3     v_table_name VARCHAR2(20) := '&sv_table_name';
  4     v_id NUMBER := &sv_id;
  5     v_first_name VARCHAR2(25);
  6     v_last_name VARCHAR2(25);
  7     v_street VARCHAR2(50);
  8     v_city VARCHAR2(25);
  9     v_state VARCHAR2(2);
 10     v_zip VARCHAR2(5);
 11  BEGIN
 12     sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
 13                 '      ,b.city, b.state, b.zip'                     ||
 14                 '  FROM '||v_table_name||' a, zipcode b'            ||
 15                 ' WHERE a.zip = b.zip'                              ||
 16                 '   AND '||v_table_name||'_id = :1';
 17     EXECUTE IMMEDIATE sql_stmt
 18     INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip
 19     USING v_id;
 20
 21     DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
 22     DBMS_OUTPUT.PUT_LINE ('Last Name:  '||v_last_name);
 23     DBMS_OUTPUT.PUT_LINE ('Street:     '||v_street);
 24     DBMS_OUTPUT.PUT_LINE ('City:       '||v_city);
 25     DBMS_OUTPUT.PUT_LINE ('State:      '||v_state);
 26     DBMS_OUTPUT.PUT_LINE ('Zip Code:   '||v_zip);
 27  END;
 28  /
Enter value for sv_table_name: student
old   3:    v_table_name VARCHAR2(20) := '&sv_table_name';
new   3:    v_table_name VARCHAR2(20) := 'student';
Enter value for sv_id: 102
old   4:    v_id NUMBER := &sv_id;
new   4:    v_id NUMBER := 102;
First Name: Fred
Last Name:  Crocitto
Street:     101-09 120th St.
City:       Richmond Hill
State:      NY
Zip Code:   11419