动态sql

目录

1.给动态语句传值(USING 子句)

2.从动态语句检索值(INTO子句)

3.动态调用存过 

4.将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量

5.传递并检索值.INTO子句用在USING子句前

6.多行查询选项.对此选项用insert语句填充临时表

7.动态定义游标

 

--动态执行sql 包括ddl dml--1.execute immediate  推荐使用 性能更优--2.dbms_utility.exec_ddl_statementbegin  execute immediate 'select * from scott.emp';  execute immediate 'create table emp1121(id number,name varchar2(10),salary number )';  dbms_utility.exec_ddl_statement('create table emp111(id number,name varchar2(10),salary number )');end;

1.给动态语句传值(USING 子句)

--1.给动态语句传值(USING 子句)declare  l_depnam varchar2(20) := 'testing';  l_loc    varchar2(10) := 'Dubai';begin  execute immediate 'insert into dept values   (:1, :2, :3)'    using 50, l_depnam, l_loc;  commit;end;select * from dept

 

2.从动态语句检索值(INTO子句)

--2.从动态语句检索值(INTO子句)declare  l_cnt varchar2(20);begin  execute immediate 'select count(1) from emp'    into l_cnt;  dbms_output.put_line(l_cnt);end;

 

3.动态调用存过 

--创建存过create or replace procedure get_rowcnt(l_tblnam varchar2,                                       l_cnt    out number,                                       l_status out varchar2) asbegin  select t.empno into l_cnt from emp t where rownum = 1;  l_cnt    := 3;  l_status := 'OK';end;--3.动态调用存过  declare  l_routin varchar2(100) := 'get_rowcnt';  l_tblnam varchar2(20) := 'emp';  l_cnt    number;  l_status varchar2(200);begin  execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'    using in l_tblnam, out l_cnt, in out l_status;  if l_status != 'OK' then    dbms_output.put_line('error');  end if;end;

 

4.将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量

--4.将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量 declare  type empdtlrec is record(    empno  number(4),    ename  varchar2(20));  empdtl empdtlrec;begin    execute immediate ' select empno, ename from  emp where rownum=1'    into empdtl;  dbms_output.put_line(empdtl.empno);end;

 

5.传递并检索值.INTO子句用在USING子句前

--5.传递并检索值.INTO子句用在USING子句前declare  l_dept pls_integer := 20;  l_nam  varchar2(20);  l_loc  varchar2(20);begin  execute immediate 'select dname, loc from dept where deptno = :1'    into l_nam, l_loc    using l_dept;  dbms_output.put_line(l_nam);end;

 

6.多行查询选项.对此选项用insert语句填充临时表

--6.多行查询选项.对此选项用insert语句填充临时表，--用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.create table temp(empno number,ename varchar2(111));declare  l_sal pls_integer := 2000;begin  execute immediate 'insert into temp(empno, ename) ' ||                    '           select empno, ename from emp ' ||                    '           where   sal > :1'    using l_sal;  commit;end;select * from temp;

 

7.动态定义游标

--7.动态定义游标declare  msql    varchar2(111) := 'select * from emp';  tbl_emp emp%rowtype;  type cur_type is ref CURSOR;  cur cur_type;begin  OPEN cur for msql; --打开动态定义的游标  LOOP    FETCH cur      into tbl_emp; --循环赋值    EXIT WHEN cur%NOTFOUND; --跳出条件    dbms_output.put_line(tbl_emp.empno || '  ' || tbl_emp.ename); --打印  END LOOP; end;