動態sql，oracle動態sql

動態sql，oracle動態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 子句)

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