#############################################
#
#About native dynamic SQL
#
# To improve the performance of dynamic SQL statements
# you can also use BULK EXECUTE IMMEDIATE,
# BULK FETCH, FORALL, and COLLECT INTO statements.
#
# Structure is as follows:->
# EXECUTE IMMEDIATE dynamic_SQL_string
# [INTO defined_variable1, defined_variable2, ...]
# [USING [IN | OUT | IN OUT] bind_argument1, bind_argument2,
# ...][{RETURNING | RETURN} field1, field2, ... INTO bind_argument1,
# bind_argument2, ...]
#
#############################################
-----------example A:EXECUTE IMMEDIATE statements------------------------
declare
sql_stmt varchar2(100);
plsql_block varchar2(300);
v_zip varchar2(5) := '11106';
v_total_student 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_out.put_line(to_char(v_date,''DD-MON-YYY''));'||
'END;';
execute immediate plsql_block;
--update record in MY_STUDENT table
/**
* update the MY_STUDENT table for a given student ID and return a new value of
* zip code using the RETURNING statement. So, the EXECUTE IMMEDIATE command contains
* both USING and RETURNING INTO options. The USING option allows you to pass a value of
* student ID to the UPDATE statement at runtime, and the RETURNING INTO option allows you
* to pass a new value of zip code from the UPDATE statement into your program.
*/
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;
--------------------------------------------------------------------------------
declare
type student_cur_type is ref cursor;
student_cur student_cur_type;
v_zip varchar2(5) := '&sv_zip';
v_first_name varchar(2);
v_last_name varchar2(25);
begin
open student_cur for
'select first_name, last_name from student '||
'where zip = :1'
using v_zip;
loop
fetch student_cur into v_first_name,v_last_name;
exit when student_cur%notfound;
dbms_output.put_line('First Name :'||v_first_name);
dbms_output.put_line('Last Name :'||v_last_name);
end loop;
clost student_cur;
exception
when others then
if student_cur%isopen then
close student_cur;
end if;
dbms_output.put_line('error: '|| substr(sqlerrm,1,200));
end;
plsql学习:dynamic SQL
最新推荐文章于 2024-08-23 20:00:00 发布