DECLARE
sql_stmt VARCHAR2(100);
plsql_block VARCHAR2(300);
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
v_new_zip VARCHAR2(5);
v_student_id NUMBER := 151;
BEGIN
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student WHERE zip = '||v_zip;
EXECUTE IMMEDIATE sql_stmt;
-- Select total number of records from MY_STUDENT table
-- and display results on the screen
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
INTO v_total_students;
DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);
-- Select current date and display it on the screen
plsql_block := 'DECLARE ' ||
' v_date DATE; ' ||
'BEGIN ' ||
' SELECT SYSDATE INTO v_date FROM DUAL; '||
' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,
''DD-MON-YYYY''))
;'|| 'END;';
EXECUTE IMMEDIATE plsql_block;
-- Update record in MY_STUDENT table
sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id =
:1 '||
'RETURNING zip INTO :2';
EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO
v_new_zip;
DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip);
END;
/
Students added: 4
08-MAR-2016
New zip code: 11105
PL/SQL procedure successfully completed.
首先创建表my_student,并且使用邮政编码的指定值记录来填充它。要注意变量v_zip与create语句一起使用,而不是作为绑定参数进行传递。
为啥不能绑定变量呢?
DECLARE
sql_stmt VARCHAR2(100);
v_zip VARCHAR2(5) := '11106';
BEGIN
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student WHERE zip = :1';
EXECUTE IMMEDIATE sql_stmt using v_zip;
end;
/
DECLARE
*
ERROR at line 1:
ORA-01027: bind variables not allowed for data definition operations
ORA-06512: at line 7
通过报错可以看出DDL语句是不允许绑定变量的。
其次,获取my_student表中学生的总数量,并在屏幕上显示。在EXECUTE IMMEDIATE语句中使用into选项,因为select语句会返回单行数据。
第三,创建一个简单的PL/SQL语句块,用于获取当前日期,并在屏幕上显示。由于这个PL/SQL语句块不包含绑定参数,按照最简单的形式使用EXECUTE IMMEDIATE语句
最后,更新my_student表中指定学生ID的数据记录,并使用returning语句返回邮政编码的更新值。这样的话,EXECUTE IMMEDIATE命令会包含using和returning into选项。借助于using选项,可以在运行时向update语句传递学生ID的值;借助于returning into选项,可以把update语句中邮政编码的新值传入自己的程序。
还需要注意的一点是,使用动态SQL语句时,不可以把模式对象的名称作为绑定参数传递给动态SQL语句(pass names of schema objects to dynamic SQL)
DECLARE
sql_stmt VARCHAR2(100);
v_zip VARCHAR2(5) := '11106';
v_total_students number;
BEGIN
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student WHERE zip = '||v_zip;
EXECUTE IMMEDIATE sql_stmt;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_student'
INTO v_total_students using 'my_student';
DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);
end;
/
DECLARE
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 10
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||my_table into v_total_students;
传入NULL值
在一些情况下,需要给动态SQL语句传递NULL值,作为绑定参数的值。
DECLARE
sql_stmt varchar2(100);
BEGIN
sql_stmt := 'update course set prerequisite = :some_value';
execute immediate sql_stmt using NULL;
end;
/
execute immediate sql_stmt using NULL;
*
ERROR at line 5:
ORA-06550: line 5, column 34:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
DECLARE
sql_stmt varchar2(100);
v_null varchar2(1);
BEGIN
sql_stmt := 'update course set prerequisite = :some_value';
execute immediate sql_stmt using v_null;
end;
/
本地动态SQL,个人理解,目的就是为了使用绑定变量。如果不用动态SQL,where条件是写死的
目前为止的动态SQL都只返回1行数据,要想返回多行,需要使用Ref Cursor