plsql学习:dynamic SQL

最新推荐文章于 2025-08-16 11:28:50 发布

原创最新推荐文章于 2025-08-16 11:28:50 发布 · 190 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#runtime

plsql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用Oracle数据库中的动态SQL来提高性能，包括EXECUTE IMMEDIATE、BULK EXECUTE IMMEDIATE、BULK FETCH等语句的应用。通过示例展示了创建表、查询记录数、更新记录并返回新值等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#############################################
#
#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;