PL/SQL--动态sql的使用

为什么使用动态sql,因为plsql块中不能定义create等表结构操作语句；

注意：
1.动态sql结束不能有分号；
2.不能用单引号，要使用双引号

方式一：DBMS_SQL.PARSE

declare
  cursor_name INTEGER;
BEGIN
  cursor_name := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cursor_name, 'select * from employees',DBMS_SQL.NATIVE );
  dbms_output.put_line(DBMS_SQL.EXECUTE (cursor_name));
  DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

方式二： execute immediate

create or replace procedure query_dep_all_salary
  (dep_id         in        number default -1,
   emp_count      out       number,
   all_salary     out       number)
is
begin
  --下面这句中带冒号的变量自动与using中的变量匹配；
  --into 的作用，和select中的into作用一致
  execute immediate 'select count(*), sum(salary) from employees group by department_id having department_id = :d_id'
          into emp_count, all_salary
          using dep_id;
end;

--测试
declare
  v_dep_id        number := 60;
  v_emp_count     number := 0;
  v_all_salary    number := 0; 
begin
  query_dep_all_salary(dep_id => v_dep_id, all_salary => v_all_salary, emp_count => v_emp_count);
  dbms_output.put_line('部门ID为'||v_dep_id||'的工资总额为'||v_all_salary||'元，员工总数为'||v_emp_count||'人');
end;  

动态sql多行查询

create or replace procedure query_dep_all_emp
  (dep_id number)
is
  type t_cursor is ref cursor;--定义游标类型
  v_sql_statement varchar2(200);
  v_emp_row employees%rowtype;
  cursor_dep t_cursor;
begin
  v_sql_statement := 'select * from employees where department_id = :d_id';

  open cursor_dep 
    for v_sql_statement 
    using dep_id;

  loop
    fetch cursor_dep into v_emp_row;
    exit when cursor_dep%NOTFOUND;
    dbms_output.put_line(v_emp_row.first_name);
  end loop;

end;

应用：写一个匿名块，创建一张临时表，hand_teacher_temp, 结构与 hand_teacher 相同。
取所有课程及格率高于50%的教师信息，插入到 hand_teacher_temp

declare
  v_sql_create_temp varchar2(500);
  cursor cursor_teacher is 
    select *
      from hand_teacher teacher
      where teacher.teacher_no in
           (select distinct teacher.teacher_no
              from hand_teacher      teacher,
                   hand_student_core core,
                   hand_course       course
             where teacher.teacher_no = course.teacher_no
               and course.course_no = core.course_no
               and (select count(*)
                      from hand_student_core core,
                           hand_teacher      teacher,
                           hand_course       course
                     where (core.core > 60 or core.core = 60)
                       and core.course_no = course.course_no
                       and course.teacher_no = teacher.teacher_no) >
                   (select count(*)
                      from hand_student_core core, hand_teacher teacher
                     where core.core < 60
                       and core.course_no = course.course_no
                       and course.teacher_no = teacher.teacher_no));
begin
  --创建临时表
  v_sql_create_temp := '
    create global temporary table hand_teacher_temp
      (TEACHER_NO   VARCHAR2(10),
       TEACHER_NAME VARCHAR2(20),
       MANAGER_NO   VARCHAR2(10))
       on commit delete rows';
  execute immediate v_sql_create_temp;

  for teacher_temp in cursor_teacher loop
    --此处必须使用动态sql插入数据，不知道为什么
    --如果直接使用insert会提示临时表没有创建
    execute immediate 'insert into hand_teacher_temp values(:1, :2, :3)' 
      using teacher_temp.TEACHER_NO,teacher_temp.TEACHER_NAME,teacher_temp.MANAGER_NO;
  end loop;
end;