关于动态sql的使用方法

曾经终败给现在 2022-01-16 10:29 264阅读 0赞

原文转自 [http://www.cnblogs.com/zhaopei/p/4253474.html][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 子句) ##

![复制代码][copycode.gif]

--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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

--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;

![复制代码][copycode.gif]

## 3.动态调用存过 ##

![复制代码][copycode.gif]

--创建存过程
    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;

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

--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;

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

--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;

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

--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;

![复制代码][copycode.gif]

## 7.动态定义游标 ##

![复制代码][copycode.gif]

--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;

![复制代码][copycode.gif]

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

[http_www.cnblogs.com_zhaopei_p_4253474.html]: http://www.cnblogs.com/zhaopei/p/4253474.html
[copycode.gif]: http://common.cnblogs.com/p_w_picpaths/copycode.gif