关于动态sql的使用方法

原文转自  http://www.cnblogs.com/zhaopei/p/4253474.html

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) as
begin
  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;

转载于:https://blog.51cto.com/leboit/1618599