ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 第十一章 本地动态SQL(转载)

第十一章 本地动态SQL(转载)

原创 Linux操作系统 作者:ofan2012 时间:2012-03-09 13:33:38 0 删除 编辑
第十一章 本地动态SQL

一、什么是动态SQL

大多数PL/SQL都做着一件特殊的结果可预知的工作。例如,一个存储过程可能接受一个雇员的编号和他的提薪金额,然后更新表emp中的信息。在这种情况下,UPDATE的全部文本内容在编译期就完全确定下来,这样的语句不会随着程序的执行而发生变化。所以,称它们为静态SQL语句。

但是,有些程序只能是在运行时建立并处理不同的SQL语句。例如,一般用途的报告打印就可能会根据用户的选择内容不同,而使我们的SELECT内容也随之变化,然后打印出相应的数据来。这样的语句在编译期是无法确定它的内容的,所以称它们为动态SQL语句。

动态SQL语句是在运行时由程序创建的字符串,它们必须是有效的SQL语句或PL/SQL块。它们也可以包含用于数据绑定的占位符。占位符是未声明的标识符,所以,它的名称并不重要,只需以冒号开头。例如,对于下面的字符串来说,PL/SQL不会认为它们有什么不同:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'

我们使用EXECUTE IMMEDIATE语句处理大多数的动态SQL语句。但是,要处理多行查询(SELECT语句),就必须使用OPEN-FOR、FETCH和CLOSE语句。

二、动态SQL的需求

只有在下的情况下我们才需要使用动态SQL:

  1. 执行数据定义语句(如CREAET),数据控制语句(如GRANT)或会话控制语句(如ALTER SESSION)。因为在PL/SQL中,这样的语句是不允许静态执行的。
  2. 为了获取更多的灵活性。例如,我们想在运行时根据我们自己的实际需求来为SELECT语句的WHERE子句选择不同的schema对象。
  3. 动态地使用包DBMS_SQL执行SQL语句,但是为了获得更好的性能,灵活方便或是DBMS_SQL不支持的功能(如对象和集合的操作)。

三、使用EXECUTE IMMEDIATE语句

EXECUTE IMMEDIATE语句能分析要执行动态的SQL语句或是匿名PL/SQL块。语法如下:

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
    [, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURNINTO bind_argument[, bind_argument]...];

dynamic_string是代表一条SQL语句或一个PL/SQL块的字符串表达式,define_variable是用于存放被选出的字段值的变量,record是用户定义或%ROWTYPE类型的记录,用来存放被选出的行记录。输入bind_argument参数是一个表达式,它的值将被传入(IN模式)或传出(OUT模式)或先传入再传出(IN OUT模式)到动态SQL语句或是PL/SQL块中。一个输出bind_argument参数就是一个能保存动态SQL返回值的变量。

除了多行查询外,动态字符串可以包含任何SQL语句(不含终结符)或PL/SQL块(含终结符)。字符串中可以包括用于参数绑定的占位符。但是,不可以使用绑定参数为动态SQL传递模式对象。

在用于单行查询时,INTO子句要指明用于存放检索值的变量或记录。对于查询检索出来的每一个值,INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。在用于DML操作时,RETURNING INTO子句要指明用于存放返回值的变量或记录。对于DML语句返回的每一个值,INTO子句中都必须有一个与之对应的、类型兼容的变量或字段。

我们可以把所有的绑定参数放到USING子句中。默认的参数模式是IN。对于含有RETURNING子句的DML语句来说,我们可以把OUT参数放到RETURNING INTO之后,并且不用指定它们的参数模式,因为默认就是OUT。如果我们既使用了USING又使用RETURNING INTO,那么,USING子句中就只能包含IN模式的参数了。

运行时,动态字符串中的绑定参数会替换相对应的占位符。所以,每个占位符必须与USING子句和/或RETURNING INTO子句中的一个绑定参数对应。我们可以使用数字、字符和字符串作为绑定参数,但不能使用布尔类型(TRUE,FALSE和NULL)。要把空值传递给动态字符串,我们就必须使用工作区。

动态SQL支持所有的SQL类型。所以,定义变量和绑定变量都可以是集合、LOB,对象类型实例和引用。作为一项规则,动态SQL是不支持PL/SQL特有的类型的。这样,它就不能使用布尔型或索引表。

我们可以重复为绑定变量指定新值执行动态SQL语句。但是,每次都会消耗很多资源,因为EXECUTE IMMEDIATE在每次执行之前都需要对动态字符串进行预处理。

1、动态SQL实例

下面的PL/SQL块包含了几个动态SQL的例子:

DECLARE
  sql_stmt      VARCHAR2(200);
  plsql_block   VARCHAR2(500);
  emp_id        NUMBER(4)     := 7566;
  salary        NUMBER(7, 2);
  dept_id       NUMBER(2)     := 50;
  dept_name     VARCHAR2(14)  := 'PERSONNEL';
  LOCATION      VARCHAR2(13)  := 'DALLAS';
  emp_rec       emp%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

  sql_stmt       := 'INSERT INTO dept VALUES (:1, :2, :3)';

  EXECUTE IMMEDIATE sql_stmt
              USING dept_id, dept_name, LOCATION;

  sql_stmt       := 'SELECT * FROM emp WHERE empno = :id';

  EXECUTE IMMEDIATE sql_stmt
               INTO emp_rec
              USING emp_id;

  plsql_block    := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';

  EXECUTE IMMEDIATE plsql_block
              USING 7788, 500;

  sql_stmt       :=
         'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2';

  EXECUTE IMMEDIATE sql_stmt
              USING emp_id
     RETURNING INTO salary;

  EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
              USING dept_id;

  EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;

下例中,过程接受一个数据表名(如"emp")和一个可选的WHERE子句(如"sal > 2000")。如果我们没有提供WHERE条件,程序会删除指定表中所有的行,否则就会按照给定的条件删除行:

CREATE PROCEDURE delete_rows(
  table_name   IN   VARCHAR2,
  condition    IN   VARCHAR2 DEFAULT NULL
AS
  where_clause   VARCHAR2(100) := WHERE ' || condition;
BEGIN
  IF condition IS NULL THEN
    where_clause    := NULL;
  END IF;

  EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
EXCEPTION
  ...
END;

2、USING子句的向后兼容

当动态INSERT、UPDATE或DELETE语句有一个RETURNING子句时,输出绑定参数可以放到RETURNING INTO或USING子句的后面。XXXXXXXXXX在新的应用程序中要使用RETURNING INTO,而旧的应用程序可以继续使用USING,如下例:

DECLARE
  sql_stmt   VARCHAR2(200);
  my_empno   NUMBER(4)     := 7902;
  my_ename   VARCHAR2(10);
  my_job     VARCHAR2(9);
  my_sal     NUMBER(7, 2)  := 3250.00;
BEGIN
  sql_stmt    :=    'UPDATE emp SET sal = :1 WHERE empno = :2 '
                 || 'RETURNING ename, job INTO :3, :4';

  /* Bind returned values through USING clause. */
  EXECUTE IMMEDIATE sql_stmt
              USING my_sal, my_empno, OUT my_ename, OUT my_job;

  /* Bind returned values through RETURNING INTO clause. */
  EXECUTE IMMEDIATE sql_stmt
              USING my_sal, my_empno
     RETURNING INTO my_ename, my_job;
  ...
END;

3、指定参数模式

使用USING子句时,我们不需要为输入参数指定模式,因为默认的就是IN;而RETURNING INTO子句中我们是不可以指定输出参数的模式的,因为定义中它就是OUT模式。看一下下面的例子:

DECLARE
  sql_stmt   VARCHAR2(200);
  dept_id    NUMBER(2)     := 30;
  old_loc    VARCHAR2(13);
BEGIN
  sql_stmt    := 'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2';

  EXECUTE IMMEDIATE sql_stmt
              USING dept_id
     RETURNING INTO old_loc;
  ...
END;

在适当的时候,我们必须为绑定参数指定OUT或IN OUT模式。例如,假定我们想调用下面的过程:

CREATE PROCEDURE create_dept(
  deptno   IN OUT   NUMBER,
  dname    IN       VARCHAR2,
  loc      IN       VARCHAR2
AS
BEGIN
  SELECT deptno_seq.NEXTVAL
    INTO deptno
    FROM DUAL;

  INSERT INTO dept
       VALUES (deptno, dname, loc);
END;

要从动态PL/SQL块调用过程,就必须为与形参关联的绑定参数指定IN OUT模式,如下:

DECLARE
  plsql_block   VARCHAR2(500);
  new_deptno    NUMBER(2);
  new_dname     VARCHAR2(14)  := 'ADVERTISING';
  new_loc       VARCHAR2(13)  := 'NEW YORK';
BEGIN
  plsql_block    := 'BEGIN create_dept(:a, :b, :c); END;';

  EXECUTE IMMEDIATE plsql_block
              USING IN OUT new_deptno, new_dname, new_loc;

  IF new_deptno > 90 THEN ...
END;

四、使用OPEN-FOR、FETCH和CLOSE语句

我们可以使用三种语句来处理动态多行查询:OPEN-FOR,FETCH和CLOSE。首先,用OPEN打开多行查询的游标变量。然后,用FETCH语句把数据从结果集中取出来。当所有的数据都处理完以后,就可以用CLOSE语句关闭游标变量了。

1、打开游标变量

OPEN-FOR语句可以把游标变量和一个多行查询关联起来,然后执行查询,确定结果集,并把游标放到结果集的第一行,然后把%ROWCOUNT值初始化为零。

与OPEN-FOR的静态形式不同的是,动态形式有一个可选的USING子句。在运行时,USING子句中的绑定变量可以替换动态SELECT语句中相对应的占位符,语法如下:

OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string
[USING bind_argument[, bind_argument]...];

其中,cursor_variable是一个弱类型(没有返回类型)的游标变量,host_cursor_variable是声明在PL/SQL主环境中的游标变量,dynamic_string是字符串表达式,代表一个多行查询。

在下面的例子中,我们声明一个游标变量,并把它和动态SELECT语句关联起来:

DECLARE
  TYPE empcurtyp IS REF CURSOR;   -- define weak REF CURSOR type

  emp_cv     empcurtyp;   -- declare cursor variable
  my_ename   VARCHAR2(15);
  my_sal     NUMBER       := 1000;
BEGIN
  OPEN emp_cv FOR   -- open cursor variable
                 'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
  ...
END;

其中绑定参数的值只在游标变量打开时计算一次。所以,如果我们想使用一个新的绑定值进行查询,就必须重新打开游标变量。

2、从游标变量取得数据

FETCH语句可以从多行查询的结果集中返回单独的一行数据,并把数据内容赋值给INTO子句后的对应的变量,然后属性%ROWCOUNT增加一,游标移到下一行,语法如下:

FETCH {cursor_variable | :host_cursor_variable}
 INTO {define_variable[, define_variable]... | record};

继续上面的例子,我们把从游标变量emp_cv取得的数据放到变量my_ename和my_sal:

LOOP
  FETCH emp_cv
   INTO my_ename, my_sal;   -- fetch next row

  EXIT WHEN emp_cv%NOTFOUND;   -- exit loop when last row is fetched
  -- process row
END LOOP;

3、关闭游标变量

CLOSE语句能够关闭游标变量,语法如下:

CLOSE {cursor_variable | :host_cursor_variable};

在下面的例子中,当最后一行数据处理完毕之后,我们就可以关闭游标变量emp_cv了:

LOOP
  FETCH emp_cv
   INTO my_ename, my_sal;

  EXIT WHEN emp_cv%NOTFOUND;
  -- process row
END LOOP;

CLOSE emp_cv;   -- close cursor variable

4、记录,集合和对象类型的动态SQL举例

下面,演示一下如何从结果集中取得数据放到一个记录中去:

DECLARE
  TYPE empcurtyp IS REF CURSOR;

  emp_cv     empcurtyp;
  emp_rec    emp%ROWTYPE;
  sql_stmt   VARCHAR2(200);
  my_job     VARCHAR2(15)  := 'CLERK';
BEGIN
  sql_stmt    := 'SELECT * FROM emp WHERE job = :j';

  OPEN emp_cv FOR sql_stmt USING my_job;

  LOOP
    FETCH emp_cv
     INTO emp_rec;

    EXIT WHEN emp_cv%NOTFOUND;
    -- process record
  END LOOP;

  CLOSE emp_cv;
END;

下例演示对象和集合的用法。假定我们定义了对象类型Person和VARRAY类型Hobbises:

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

现在,我们使用动态SQL编写一个利用到这些类型的包:

CREATE PACKAGE teams AS
  PROCEDURE create_table(tab_name VARCHAR2);

  PROCEDURE insert_row(tab_name VARCHAR2, p person, h hobbies);

  PROCEDURE print_table(tab_name VARCHAR2);
END;

CREATE PACKAGE BODY teams AS
  PROCEDURE create_table(tab_name VARCHAR2IS
  BEGIN
    EXECUTE IMMEDIATE    'CREATE TABLE '
                      || tab_name
                      || ' (pers Person, hobbs Hobbies)';
  END;

  PROCEDURE insert_row(tab_name VARCHAR2, p person, h hobbies) IS
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name || ' VALUES (:1, :2)'
                USING p, h;
  END;

  PROCEDURE print_table(tab_name VARCHAR2IS
    TYPE refcurtyp IS REF CURSOR;

    CV   refcurtyp;
    p    person;
    h    hobbies;
  BEGIN
    OPEN CV FOR 'SELECT pers, hobbs FROM ' || tab_name;

    LOOP
      FETCH CV
       INTO p, h;

      EXIT WHEN CV%NOTFOUND;
      -- print attributes of 'p' and elements of 'h'
    END LOOP;

    CLOSE CV;
  END;
END;

我们可以像下面这样从匿名块中调用包teams中的过程:

DECLARE
  team_name   VARCHAR2(15);
  ...
BEGIN
  ...
  team_name    := 'Notables';
  teams.create_table(team_name);
  teams.insert_row(team_name, person('John', 31),
                   hobbies('skiing''coin collecting''tennis'));
  teams.insert_row(team_name, person('Mary', 28),
                   hobbies('golf''quilting''rock climbing'));
  teams.print_table(team_name);
END;

五、使用批量动态SQL

批量绑定能减少PL/SQL和SQL引擎之间的切换,改善性能。使用下面的命令、子句和游标属性,我们就能构建批量绑定的SQL语句,然后在运行时动态地执行:

BULK FETCH 语句
BULK EXECUTE IMMEDIATE 语句
FORALL 语句
COLLECT INTO 子句
RETURNING INTO 子句
%BULK_ROWCOUNT 游标属性

1、动态批量绑定语法

批量绑定能让Oracle把SQL语句中的一个变量与一个集合相绑定。集合类型可以是任何PL/SQL集合类型(索引表、嵌套表或变长数组)。但是,集合元素必须是SQL数据类型,如CHAR、DATE或NUMBER。有三种语句支持动态批量绑定:EXECUTE IMMEDIATE、FETCH和FOR ALL。

  • 批量EXECUTE IMMEDIATE

这个语句能让我们把变量或OUT绑定参数批量绑定到一个动态的SQL语句,语法如下:

EXECUTE IMMEDIATE dynamic_string
  [[BULK COLLECTINTO define_variable[, define_variable ...]]
  [USING bind_argument[, bind_argument ...]]
  [{RETURNING | RETURN}
  BULK COLLECT INTO bind_argument[, bind_argument ...]];

在动态多行查询中,我们可以使用BULK COLLECT INTO子句来绑定变量。在返回多行结果的动态INSERT、UPDATE或DELETE语句中,我们可以使用RETURNING BULK COLLECT INTO子句来批量绑定输出变量。

  • 批量FETCH

这个语句能让我们从动态游标中取得数据,就跟从静态游标中取得的方法是一样的。语法如下:

FETCH dynamic_cursor
  BULK COLLECT INTO define_variable[, define_variable ...];

如果在BULK COLLECT INTO中的变量个数超过查询的字段个数,Oracle就会产生错误。

  • 批量FORALL

这个语句能让我们在动态SQL语句中批量绑定输入参数。此外,我们还可以在FORALL内部使用EXECUTE IMMEDIATE语句。语法如下:

FORALL index IN lower bound..upper bound
  EXECUTE IMMEDIATE dynamic_string
  USING bind_argument | bind_argument(index)
    [, bind_argument | bind_argument(index)] ...
  [{RETURNING | RETURNBULK COLLECT
    INTO bind_argument[, bind_argument ... ]];

动态字符串必须是一个INSERT、UPDATE或DELETE语句(不可以是SELECT语句)。

2、动态批量绑定实例

我们可以在动态查询中使用BULK COLLECT INTO子句来绑定变量。如下例所示,我们可以在批量的FETCH或EXECUTE IMMEDIATE语句中使用BULK COLLECT INTO。

DECLARE
  TYPE empcurtyp IS REF CURSOR;

  TYPE numlist IS TABLE OF NUMBER;

  TYPE namelist IS TABLE OF VARCHAR2(15);

  emp_cv   empcurtyp;
  empnos   numlist;
  enames   namelist;
  sals     numlist;
BEGIN
  OPEN emp_cv FOR 'SELECT empno, ename FROM emp';

  FETCH emp_cv
  BULK COLLECT INTO empnos, enames;

  CLOSE emp_cv;

  EXECUTE IMMEDIATE 'SELECT sal FROM emp'
  BULK COLLECT INTO sals;
END;

只有INSERT、UPDATE和DELETE语句才能拥有输出绑定参数。我们可以在EXECUTE IMMDIATE的BULK RETURNING INTO子句中进行绑定:

DECLARE
  TYPE namelist IS TABLE OF VARCHAR2(15);

  enames      namelist;
  bonus_amt   NUMBER       := 500;
  sql_stmt    VARCHAR(200);
BEGIN
  sql_stmt    := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';

  EXECUTE IMMEDIATE sql_stmt
              USING bonus_amt
     RETURNING BULK COLLECT INTO enames;
END;

要在SQL语句中绑定输入参数,就要使用FORALL语句和USING子句,但这时的SQL语句不能是查询语句,如下例:

DECLARE
  TYPE numlist IS TABLE OF NUMBER;

  TYPE namelist IS TABLE OF VARCHAR2(15);

  empnos   numlist;
  enames   namelist;
BEGIN
  empnos    := numlist(1, 2, 3, 4, 5);
  FORALL i IN 1 .. 5
    EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1 ' ||
                      'RETURNING ename INTO :2'
                USING empnos(i)
       RETURNING BULK COLLECT INTO enames;
  ...
END;

六、动态SQL的技巧与陷阱

这节会让我们了解如何完全利用动态SQL语句并避免一些常见的缺陷。

1、改善性能

下例中,Oracle为每个不同的emp_id单独打开一个游标。这就造成资源浪费并降低了效率:

CREATE PROCEDURE fire_employee(emp_id NUMBERAS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;

我们可以使用绑定变量来改善性能,如下例所示。这就能让Oracle为不同的emp_id值重用同一个游标。

CREATE PROCEDURE fire_employee(emp_id NUMBERAS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num'
              USING emp_id;
END;

2、让过程对任意模式对象起作用

假设我们需要一个过程,让它接受数据表名,然后将指定的表从数据库中删除。我们可能会下面这样编写使用动态SQL的独立过程:

CREATE PROCEDURE drop_table(table_name IN VARCHAR2AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE :tab'
              USING table_name;
END;

但是,在运行的时候,这个过程可能会因为表名错误而无法执行成功。这就是我们为什么不能用参数绑定来为动态SQL传递模式对象的名称。解决方法是直接把参数嵌套到字符串中。我们把上面的EXECUTE IMMEDIATE语句修改一下:

CREATE PROCEDURE drop_table(table_name IN VARCHAR2AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;

这样,我们就可以向动态SQL语句传递任意数据表名称了。

3、使用重复占位符

动态SQL语句中的占位符与USING子句中的绑定参数是位置关联的,而不是名称关联。所以,如果在SQL语句中同样的占位符出现两次或多次,那么,它的每次出现都必须与一个USING子句中的绑定参数相关联。例如下面的动态字符串:

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

我们可以为动态字符串编写对应的USING子句:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

但是,动态PL/SQL块中只有唯一的占位符才与USING子句中的绑定参数按位置对应。所以,如果一个占位符在PL/SQL块中出现两次或多次,那么所有这样相同的占位符都只与USING语句中的一个绑定参数相对应。比如下面的例子,第一个占位符(x)与第一个绑定参数(a)关联,第二个占位符(y)与第二个绑定参数(b)关联。

DECLARE
  a   NUMBER := 4;
  b   NUMBER := 7;
BEGIN
  plsql_block    := 'BEGIN calc_stats(:x, :x, :y, :x); END';

  EXECUTE IMMEDIATE plsql_block
              USING a, b;
  ...
END;

4、使用游标属性

每个显式的游标都有四个属性:%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。它们都能返回与静态或动态SQL语句执行结果相关的有用信息。

为处理SQL数据操作语句,Oracle会打开一个名为SQL的隐式游标。它的属性会返回最近一次执行的INSERT、UPDATE、DELETE或单行SELECT的相关信息。例如,下面函数就使用%ROWCOUNT返回从数据表中删除的行数:

CREATE FUNCTION rows_deleted(table_name IN VARCHAR2, condition IN VARCHAR2)
  RETURN INTEGER AS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || ' WHERE ' || condition;

  RETURN SQL%ROWCOUNT;   -- return number of rows deleted
END;

同样,当我们把游标变量的名字附加进去时,游标的属性也能返回多行查询执行结果的相关信息。

5、传递空值

下面,我们来为动态SQL传递空值,见下面的EXECUTE IMMEDIATE语句:

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;

但是,这个语句会因为在USING子句中使用NULL而执行失败,因为USING语句中所传递的参数是不能为空的。所以,要想解决这个问题,直接使用字符串就可以了:

DECLARE
  a_null   CHAR(1);   -- set to NULL automatically at run time
BEGIN
  EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x'
              USING a_null;
END;

6、远程操作

如下例所示,PL/SQL子程序能够执行引用远程数据库对象的动态SQL语句:

PROCEDURE delete_dept(db_link VARCHAR2, dept_id INTEGERIS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link || ' WHERE deptno = :num'
              USING dept_id;
END;

同样,远程过程调用(RPC)的目标也包括动态SQL语句。例如,假设下面返回数据表中记录个数的函数存放在Chicago的数据库上:

CREATE FUNCTION row_count(tab_name VARCHAR2)
  RETURN INTEGER AS
  ROWS   INTEGER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name
               INTO ROWS;

  RETURN ROWS;
END;

下面是一个从匿名SQL块调用远程函数的例子:

DECLARE
  emp_count   INTEGER;
BEGIN
  emp_count    := row_count@chicago('emp');
END;

7、使用调用者权限

默认情况下,存储过程是使用定义者权限执行的,而不是调用者权限。这样的过程是绑定在它们所属的模式对象上的。假设下面用于删除数据库对象的过程存放在模式对象scott上:

CREATE PROCEDURE drop_it(kind IN VARCHAR2, NAME IN VARCHAR2AS
BEGIN
  EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;
END;

我们把用户jones赋予允许他执行上面的存储过程的EXECUTE权限。当用户jones调用drop_it时,动态SQL就会使用用户scott的权限来执行语句:

SQL> CALL drop_it('TABLE''dept');

这样的话,由于数据表dept的前面并没有限定修饰词进行限制,语句执行时删除的就是scott上的数据表,而不是jones上的。

但是,AUTHID子句可以让存储过程按它的调用者权限来执行,这样的存储过程就不会绑定在一个特定的schema对象上。例如下面的新版本drop_it就会按调用者权限执行:

CREATE PROCEDURE drop_it(kind IN VARCHAR2, NAME IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
  EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;
END;

8、使用RESTRICT_REFERENCES

从SQL语句调用函数时,我们必须要遵守一定的规则来避免负面印象。为了检测冲突,我们可以使用编译指示RESTRICT_REFERENCES。它能确保函数没有读和/或写数据表和/或打包变量。

但是,如果函数体包含了动态INSERT、UPDATE或DELETE语句,那它就总与规则"write no database state" (WNDS)和"read no database state" (RNDS)相冲突。这是因为动态SQL语句是在运行时才被检验,而不是编译期。在一个EXECUTE IMMEDIATE语句中,只有INTO子句才能在编译期检验是否与RNDS冲突。

9、避免死锁

有些情况下,执行SQL数据定义语句会导致死锁。例如,下面的过程就能引起死锁,因为它尝试着删除自身。为了避免死锁,就不要用ALTER或DROP来操作正在使用的子程序或包。

CREATE PROCEDURE calc_bonus (emp_id NUMBERAS
BEGIN
  ...
  EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';

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

请登录后发表评论 登录
全部评论

注册时间:2012-01-14

  • 博文量
    31
  • 访问量
    127411