动态sql 0.pl/sql块的限制 不能执行ddl操作(create、drop、alter); 不能执行部分dcl操作(grant、revoke)。
1.语法 动态sql:在执行时才能确定要执行的sql语句。 在pl/sql块中编写动态sql语句时,需要将sql语句存放到字符串变量中, 而且sql可以包含占位符。 execute immediate dynamic_string dynamic_string :是存放要被执行的sql语句的字符串变量。
2.用途 可处理: ddl语句(create,alter,drop), dcl语句(grant,revoke), dml语句(insert,update,delete) 单行select语句 多行select语句需要和游标联合使用。
3.ddl操作 execute immediate后面只需要带有ddl语句 create or replace procedure drop_table(table_name varchar2) is sql_statement varchar2(100); begin sql_statement:=‘drop table ‘||table_name; execute immediate sql_statement; end; / exec drop_table(‘emp_log‘);
4.dcl操作(grant、revoke) conn sys/123 as sysdba; create or replace procedure grant_sys_priv(priv varchar2,username varchar2) is sql_stat varchar2(100); begin sql_stat:=‘grant ‘||priv||‘ to ‘||username; execute immediate sql_stat; end; / --授权前检查 select * from user_sys_privs; --授权 exec grant_sys_priv(‘create any table‘,‘scott‘) --grant create any view,drop any view to scott;
create or replace procedure revoke_sys_priv(priv varchar2,username varchar2) is sql_stat varchar2(100); begin sql_stat:=‘revoke ‘||priv||‘ from ‘||username; execute immediate sql_stat; end; /
5.dml操作 5.1 sql语句不需要参数 declare sql_stat varchar2(100); begin --update emp set sal=sal*1.1 where deptno=30; sql_stat:=‘update emp set sal=sal*1.1 where deptno=30‘; execute immediate sql_stat; end;
5.2 sql语句需要参数 : 占位符--》绑定变量 using 传递参数 & 从客户端接收参数 declare sql_stat varchar2(100); begin sql_stat:=‘update emp set sal=sal*(1+:percent/100) where deptno=:dno‘; execute immediate sql_stat using &percent,&dno; end; 改造: create or replace procedure proc_emp_addsal(p_dno number,p_percent number) is begin update emp set sal=sal*(1+p_percent/100) where deptno=p_dno; end;
6.单行select语句 需要使用into子句接受返回数据 语法: execute immediate dynamic_string [into (define_variable|record)] [using bind_argument]
declare sql_stat varchar2(100); v_ename emp.ename%type; begin sql_stat:=‘select ename from emp where empno=:enp‘; execute immediate sql_stat into v_ename using &enp; dbms_output.put_line(‘雇员:‘||v_ename); end; 改造: create or replace function func_emp_ename(p_empno number) return varchar2 is v_ename emp.ename%type; begin select ename into v_ename from emp where empno=p_empno; return v_ename; end;
7.多行select语句 7.1 使用游标 为了处理动态的多行查询操作,必须要使用open-for语句打开游标, 使用fetch循环提取数据,close关闭游标 declare type empcurtyp is ref cursor; emp_cv empcurtyp; emp_record emp%rowtype; sql_stat varchar2(100); begin sql_stat:=‘select * from emp where deptno=:dno‘; open emp_cv for sql_stat using &dno; loop fetch emp_cv into emp_record; exit when emp_cv%notfound; dbms_output.put_line(‘雇员名:‘||emp_record.ename||‘,工资:‘||emp_record.sal); end loop; close emp_cv; end;
7.2 bulk collect into 集合 bulk collect批量操作 declare type ename_table_type is table of emp%rowtype; ename_table ename_table_type; sql_stat varchar2(100); begin sql_stat:=‘select * from emp where deptno=:dno‘; execute immediate sql_stat bulk collect into ename_table using &dno; for i in 1..ename_table.count loop dbms_output.put_line(‘雇员名:‘||ename_table(i).ename||‘,工资:‘||ename_table(i).sal); end loop; end;
改造: create or replace procedure proc_emp_enamesal(p_dno number) is begin for rs in (select * from emp where deptno=p_dno) loop dbms_output.put_line(‘雇员名:‘||rs.ename||‘,工资:‘||rs.sal); end loop; end; /
exec proc_emp_enamesal(20);
8.实际工作中的用途 8.1 对于分表处理 create or replace procedure proc_stu (p_tab varchar2,p_id number) is type ename_table_type is table of stu_oracle%rowtype; ename_table ename_table_type; sql_stat varchar2(100); begin sql_stat:=‘select * from ‘||p_tab||‘ where id=‘||p_id; execute immediate sql_stat bulk collect into ename_table; for i in 1..ename_table.count loop dbms_output.put_line(‘学生名:‘||ename_table(i).sname); end loop; end; / show err;
begin proc_stu(‘stu_java‘,11); end;
8.2 对拼接函数通用功能的实现 create or replace function func_concat(p_expectfield varchar2, p_expecttable varchar2,p_expectcause varchar2) return varchar2 is /* 函数功能:获取单个属性将其拼成一行 参数说明:p_expectfield 查询字段 p_expecttable 查询主表 p_expectcause 查询条件 */ v_sql varchar2(4000):=‘‘; type type_cursor is ref cursor; v_cur type_cursor; v_per varchar2(4000):=‘‘; v_cnt number:=0; v_return varchar2(4000):=‘‘; begin v_sql:=‘select ‘||p_expectfield||‘ from ‘||p_expecttable||‘ where ‘||p_expectcause; open v_cur for v_sql; loop fetch v_cur into v_per; exit when v_cur%notfound; if v_cnt=0 then v_return:=v_per; else v_return:=v_return||‘,‘||v_per; end if; v_cnt:=v_cnt+1; end loop; close v_cur; return v_return; end func_concat; / show err;
select func_concat(‘ename‘,‘emp‘,‘1=1‘) from dual; select func_concat(‘distinct job‘,‘deptno=20‘) from dual; select func_concat(‘dname‘,‘dept‘,‘1=1‘) from dual; --select ename from emp,dept where emp.deptno=dept.deptno and dept.deptno=20; select func_concat(‘ename‘,‘emp,dept‘,‘emp.deptno=dept.deptno and dept.deptno=20‘) from dual; --select job from emp where ename=‘scott1‘; select func_concat(‘job‘,‘ename=‘‘scott1‘‘‘) from dual; --select ename from emp,dept where emp.deptno=dept.deptno and lower(dname)=‘sales‘; select func_concat(‘ename‘,‘emp.deptno=dept.deptno and lower(dname)=‘‘sales‘‘‘) from dual;
作业:1.用动态sql创建表,给表增加列、删除列、修改列长度;2.用动态sql创建表,给表增加主键约束、唯一键约束、外键约束;3.用动态sql截断表;4.用动态sql实现部门表的Insert、Delete功能
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|