DBMS_SQL

最新推荐文章于 2021-04-08 07:39:02 发布

cuili9760

最新推荐文章于 2021-04-08 07:39:02 发布

阅读量219

点赞数

dbms_sql

用途：执行动态语句
由于execute immediate语句的简捷易用，dbms_sql包的使用已经非常少了，而且很早就传言说要废弃，但其功能强大，而且有

些时候无法被替代，因此还是值得学习的。

使用场景：当我们的单条语句超过32K时，有位置数目或类型的输入、输出参数时，需要更精细化的控制时只能使用DBMS_SQL包

。

官方文档plsql package中包含了几十页该包的讲解，在说明其功能的同时，也暗示了其重要地位。

使用方法：
（1）对于一般的select操作：
打开游标-》解析-》定义列-》执行-》插入行（fetch）-》关闭游标

（2）对于insert、update语句：
打开游标-》解析-》绑定变量-》执行-》关闭游标

（3）对ddl和delete语句：
打开游标-》解析-》执行-》关闭游标

打开：打开游标时需要给游标命名
解析阶段：对动态语句的语法语义进行检查，并与游标名对应
列定义、列长度定义、列值定义：对列进行说明
变量绑定或数组绑定：对于大多数动态语句，需要在运行时输入变量作为查询条件，变量绑定或数组绑定阶段就是定义这些变

量的属性
执行阶段：调用execute函数执行语句
插入（fetch）：fetch_Rows函数返回满足查询条件的数据
关闭游标

在解析后立即调用last_error_position函数可以获得出错信息。

例1：编译时已经知道语句的内容，但不知道要查询的值

功能：删除emp表中salary列数值大于执行时所输入数值的所有数据
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER; --游标名 integer类型
rows_processed INTEGER; --执行游标的语句 integer类型
BEGIN
cursor_name := dbms_sql.open_cursor; --打开游标
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',dbms_sql.native);--游标名为cursor_name，内容是单

引号中内容，native是固定的格式
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);--将输入参数salary赋给游标cursor_name中变量：x
rows_processed := dbms_sql.execute(cursor_name);--执行游标cursor_name
DBMS_SQL.close_cursor(cursor_name);--关闭游标
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

此过程完全可以用其他方式替代。

运行结果：
SQL>

Procedure created

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980-12-17     800.00               20
7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20
7839 KING       PRESIDENT       1981-11-17    5000.00               10
7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
7900 JAMES      CLERK      7698 1981-12-3      950.00               30
7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

14 rows selected

SQL> exec demo(salary => '1500');

PL/SQL procedure successfully completed

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH      CLERK      7902 1980-12-17     800.00               20
7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20
7900 JAMES      CLERK      7698 1981-12-3      950.00               30
7934 MILLER     CLERK      7782 1982-1-23     1300.00               10

7 rows selected

================================
例2：DDL语句或不含值的DML（DELTET）的执行将语句作为变量输入，包中解析并执行
CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS --string是要输入的语句
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

调用语句为：
exec('create table acct(c1 integer)');
exec@hq.com('CREATE TABLE acct(c1 INTEGER)');

================================
例3：假设目标表和源表都有以下列：
(id NUMBER，
name VARCHAR2(30)，
birthdate DATE)

创建存储过程进行数据的复制：
CREATE OR REPLACE PROCEDURE copy (
source IN VARCHAR2,
destination IN VARCHAR2) IS
id_var NUMBER;
name_var VARCHAR2(30);
birthdate_var DATE;
source_cursor INTEGER;
destination_cursor INTEGER;
ignore INTEGER;
BEGIN
-- Prepare a cursor to select from the source table:
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,'SELECT id, name, birthdate FROM ' || source,DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);
ignore := DBMS_SQL.EXECUTE(source_cursor);

-- Prepare a cursor to insert into the destination table:
destination_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(destination_cursor,'INSERT INTO ' || destination ||' VALUES (:id_bind, :name_bind,

:birthdate_bind)',DBMS_SQL.native);

-- Fetch a row from the source table and insert it into the destination table:
LOOP
IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);
-- Bind the row into the cursor that inserts into the destination table.
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var);--指定destination_cursor中：id_bind赋值id_var
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var);--指定destination_cursor中：name_bind赋值

name_var
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind',birthdate_var);--指定destination_cursor中：

birthday_bind赋值birthday_var
ignore := DBMS_SQL.EXECUTE(destination_cursor);
ELSE
-- No more rows to copy:
EXIT;
END IF;
END LOOP;

-- Commit and close all cursors:
COMMIT;
DBMS_SQL.CLOSE_CURSOR(source_cursor);
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
END IF;
RAISE;
END;
/

=======================
例4：bulk dml 批量执行
(1)普通形式
declare
stmt          varchar2(200);
dept_no_array dbms_sql.Number_Table;
c             number;
dummy         number;
begin
dept_no_array(1) := 10;
dept_no_array(2) := 20;
dept_no_array(3) := 30;
dept_no_array(4) := 40;
dept_no_array(5) := 30;
dept_no_array(6) := 40;

stmt := 'delete from emp where deptno = :dept_array';

c := dbms_sql.open_cursor; --游标名为c 为什么要定义成number类型呢？
dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_array(c, ':dept_array', dept_no_array, 1, 4); --这句是本例的难点，
--意思是将输入dept_no_array中从1到4个值作为变量赋值给：dept_array
--结果应该是将emp中deptno为10、20、30、40的数据删除
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
    raise;
end;
/

(2)bulk dml-insert
declare
stmt          varchar2(200);
empno_array   dbms_sql.Number_Table; --此处使用了number_table
empname_array dbms_sql.Varchar2_Table;
c             number;
dummy         number;
begin
for i in 0 .. 9 loop
    empno_array(i) := 1000 + i;
    empname_array(I) := 'emp'||i;
end loop;
stmt := 'insert into emp(empno,ename) values(:num_array, :name_array)';
c    := dbms_sql.open_cursor;
dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_array(c, ':num_array', empno_array); --将数组empno_array作为参数赋值给c中的：num_array
dbms_sql.bind_array(c, ':name_array', empname_array); --将数组empname_array作为参数赋值给c中的：name_array
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
end;
/

(3)bulk dml-update
--和insert语句相比，只是语句不同，处理方式一样
--以下语句在scott用户下不能执行，理解意思就可以了
declare
stmt           varchar2(200);
emp_no_array   dbms_sql.Number_Table;
emp_addr_array dbms_sql.Varchar2_Table;
c              number;
dummy          number;
begin
for i in 0 .. 9 loop
    emp_no_array(i) := 1000 + i;
    emp_addr_array(I) := get_new_addr(i);
end loop;
stmt := 'update emp set ename = :name_array where empno = :num_array';
c    := dbms_sql.open_cursor;
dbms_sql.parse(c, stmt, dbms_sql.native);
dbms_sql.bind_array(c, ':num_array', empno_array);
dbms_sql.bind_array(c, ':name_array', empname_array);
dummy := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
exception
when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
    raise;
end;
/
=================
例5：定义数组
declare
c number;
d number;
n_tab dbms_sql.Number_Table;
indx number := -10;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select n from t order by 1', dbms_sql);
dbms_sql.define_array(c, 1, n_tab, 10, indx);
d := dbms_sql.execute(c);
loop --进行循环操作
d := dbms_sql.fetch_rows(c); --将查询c的一条结果放入d中
dbms_sql.column_value(c, 1, n_tab); --将查询c的结果一次放入n_tab中
exit when d != 10;                  --在d=10时退出循环
end loop;
dbms_sql.close_cursor(c);
exception when others then
if dbms_sql.is_open(c) then
dbms_sql.close_cursor(c);
end if;
raise;
end;
/

dbms_sql的define_array不太好理解，根据官方文档的语法：

DBMS_SQL.DEFINE_ARRAY (
   c           IN INTEGER,
   position    IN INTEGER,
       IN
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);

含义为：
c ID number of the cursor to which you want to bind an array.
position Relative position of the column in the array being defined.The first column in a statement has

position 1.
table_variable Local variable that has been declared as .
cnt Number of rows that must be fetched.
lower_bnd Results are copied into the collection, starting at this lower bound index.

也就是说，上面的 dbms_sql.define_array(c, 1, n_tab, 10, indx);含义为将游标c中第一列值从index开始的10个数值放入

到已经定义好的n_tab中。

(2)
declare
c      number;
d      number;
n_tab dbms_sql.Number_Table;
d_tab1 dbms_sql.Date_Table;
v_tab dbms_sql.Varchar2_Table;
d_tab2 dbms_sql.Date_Table;
indx   number := 10;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'select * from multi_tab order by 1', dbms_sql.native);
dbms_sql.define_array(c, 1, n_tab, 5, indx);
dbms_sql.define_array(c, 2, d_tab1, 5, indx);
dbms_sql.define_array(c, 3, v_tab, 5, indx);
dbms_sql.define_array(c, 4, d_tab2, 5, indx);
d := dbms_sql.execute(c);
loop
    d := dbms_sql.fetch_rows(c);
    dbms_sql.column_value(c, 1, n_tab);
    dbms_sql.column_value(c, 2, d_tab1);
    dbms_sql.column_value(c, 3, v_tab);
    dbms_sql.column_value(c, 4, d_tab2);
    exit when d != 5;
end loop;
dbms_sql.close_cursor(c);
exception
when others then
    if dbms_sql.is_open(c) then
      dbms_sql.close_cursor(c);
    end if;
end;
/

==================
例6：描述列

declare
c       number;
d       number;
col_cnt integer;
f       boolean;
rec_tab dbms_sql.desc_tab;
col_num number;
procedure print_rec(rec in dbms_sql.desc_rec) is
begin
    dbms_output.new_line;
    dbms_output.put_line('col_type = ' || rec.col_type);
    dbms_output.put_line('col_maxlen = ' || rec.col_max_len);
    dbms_output.put_line('col_name = ' || rec.col_name);
    dbms_output.put_line('col_name_len = ' || rec.col_name_len);
    dbms_output.put_line('col_schema_name = ' || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len = ' || rec.col_schema_name_len);
    dbms_output.put_line('col_precision = ' || rec.col_precision);
    dbms_output.put_line('col_scale = ' || rec.col_scale);
    dbms_output.put('col_null_ok = ');
    if (rec.col_null_ok) then
      dbms_output.put_line('true');
    else
      dbms_output.put_line('false');
    end if;
end;
begin
c := dbms_sql.open_cursor;
--如果希望查看两个表，直接在bonus后面加表名就可以了
dbms_sql.parse(c, 'select * from scott.bonus', dbms_sql.native);
d := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);--括号里分别为游标名称、select查询中的列数、待查询列的描述
/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := rec_tab.first;
if (col_num is not null) then
    loop
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      exit when(col_num is null);
    end loop;
end if;
dbms_sql.close_cursor(c);
end;
/

结果为：

col_type = 1
col_maxlen = 10
col_name = ENAME
col_name_len = 5
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true

col_type = 1
col_maxlen = 9
col_name = JOB
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true

col_type = 2
col_maxlen = 22
col_name = SAL
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = -127
col_null_ok = true

col_type = 2
col_maxlen = 22
col_name = COMM
col_name_len = 4
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = -127
col_null_ok = true

============================

例7：returning子句
(1)单行插入
create or replace procedure single_Row_insert
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into tab values (:bnd1, :bnd2) ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/
(2)单行更新
create or replace procedure single_Row_update
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'update tab set c1 = :bnd1, c2 = :bnd2 ' ||
'where rownum < 2' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/

(3)单行删除
create or replace procedure single_Row_Delete
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'delete from tab ' ||
'where rownum < 2 ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/

(4)多行插入
create or replace procedure multi_Row_insert
(c1 dbms_sql.number_table, c2 dbms_sql.number_table,
r out dbms_sql.number_table) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into tab values (:bnd1, :bnd2) ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_array(c, 'bnd1', c1);
dbms_sql.bind_array(c, 'bnd2', c2);
dbms_sql.bind_array(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/

(5)多行更新
create or replace procedure multi_Row_update
(c1 number, c2 number, r out dbms_Sql.number_table) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'update tab set c1 = :bnd1 where c2 = :bnd2 ' ||
'returning c1*c2 into :bnd3', 2);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_array(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/

(6)多行删除
create or replace procedure multi_row_delete
(c1 dbms_Sql.number_table,
r out dbms_sql.number_table) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'delete from tab where c1 = :bnd1' ||
'returning c1*c2 into :bnd2', 2);
dbms_sql.bind_array(c, 'bnd1', c1);
dbms_sql.bind_array(c, 'bnd2', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd2', r);-- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
/

(7)多行绑定处理
create or replace foo (n number, square out number) is
begin square := n * n; end;/
create or replace procedure bulk_plsql
(n dbms_sql.number_Table, square out dbms_sql.number_table) is
c number;
r number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'begin foo(:bnd1, :bnd2); end;', 2);
dbms_sql.bind_array(c, 'bnd1', n);
dbms_Sql.bind_Array(c, 'bnd2', square);
r := dbms_sql.execute(c);
dbms_Sql.variable_Value(c, 'bnd2', square);
end;
/

例4和例7不太理解，待深入

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-755441/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-755441/

cuili9760

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DBMS_SQL

dbms_sql 用途：执行动态语句由于execute immediate语句的简捷易用，dbms_sql包的使用已经非常少了，而且很早就传言说要废弃，但其功能强大，而且有些时候无法被替代，因此还是值得学习的。使用场...
复制链接

扫一扫