为什么使用动态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;