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)isbegin  --下面这句中带冒号的变量自动与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;