動态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_statement
begin
  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 子句)
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子句)
declare
  l_cnt varchar2(20);
begin
  execute immediate 'select count(1) from emp'
    into l_cnt;
  dbms_output.put_line(l_cnt);
end;      

--建立存過
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變量 
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子句前
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語句填充臨時表，
--用臨時表進行進一步的處理,也可以用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.動态定義遊标
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;      

• 學習本是一個不斷抄襲、模仿、練習、創新的過程。
• 雖然，園中已有本人無法超越的同主題博文，為什麼還是要寫。
• 對于自己，博文隻是總結。在總結的過程發現問題，解決問題。
• 對于他人，在此過程如果還能附帶幫助他人，那就再好不過了。
• 由于部落客能力有限，文中可能存在描述不正确，歡迎指正、補充！
• 感謝您的閱讀。如果文章對您有用，那麼請輕輕點個贊，以資鼓勵。
• 工控物聯Q群：995475200