oracle 每日一题-游标的参数

转自http://www.itpub.net/thread-2084177-1-1.html 感谢newkid

原始出处：
http://www.plsqlchallenge.com/

作者：Steven Feuerstein 

运行环境：SQLPLUS, SERVEROUTPUT已打开

我创建了如下的表并且填充了数据：

CREATE TABLE plch_parts
(
   partnum    INTEGER PRIMARY KEY
, partname   VARCHAR2 (100) UNIQUE
)
/

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');

   INSERT INTO plch_parts
        VALUES (500, 'Monitor');

   COMMIT;
END;
/

哪些代码块执行之后会显示如下输出：

1
500


注意：每个选项都有一模一样的如下执行代码：

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;
/

你可以专注于每个选项的声明部分和游标打开方式。

(A) 
DECLARE
   l_filter   VARCHAR2 (100) := 'M%';

   CURSOR parts_cur
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE l_filter
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur;

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;
/

(B) 
DECLARE
   CURSOR parts_cur (filter_in IN VARCHAR2)
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE filter_in
       ORDER BY partnum;

   l_part   plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur ('M%');

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;
/

(C) 
DECLARE
   l_filter   VARCHAR2 (100);

   CURSOR parts_cur (filter_in IN VARCHAR2)
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE l_filter
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur ('M%');

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;
/

(D) 
DECLARE
   CURSOR parts_cur (filter_in IN VARCHAR2 DEFAULT 'M%')
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE filter_in
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur ();

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;
/

(E) 
DECLARE
   l_filter   VARCHAR2 (100) := 'M%';

   CURSOR parts_cur (filter_in IN OUT VARCHAR2)
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE filter_in
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur (l_filter);

   LOOP
      FETCH parts_cur INTO l_part;
      EXIT WHEN parts_cur%NOTFOUND;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
   END LOOP;
   CLOSE parts_cur;
END;
/

(F)
DECLARE
   l_filter   VARCHAR2 (100) := 'M%';

   CURSOR parts_cur (filter_in OUT VARCHAR2)
   IS
      SELECT *
        FROM plch_parts
       WHERE partname LIKE filter_in
       ORDER BY partnum;

   l_part     plch_parts%ROWTYPE;
BEGIN
   OPEN parts_cur (l_filter);

   LOOP
      FETCH parts_cur INTO l_part;
      sys.DBMS_OUTPUT.put_line (l_part.partnum);
      EXIT WHEN parts_cur%NOTFOUND;
   END LOOP;
   CLOSE parts_cur;
END;
/

A: 这个选项用了一个局部变量，在游标内部直接引用，来得到所需的结果。这不是写这类代码的最佳方法，因为该游标对声明于更高作用域内的变量有着某种“全局”的依赖。更好的方法是为游标定义一个参数，并且将l_filter传递给游标，如下：

OPEN parts_cur (l_filter);

B: 这个选项用了一个参数化的游标来传递过滤条件。
C: 这个选项用了一个参数化的游标，但是参数从未被使用！相反，游标仍然在WHERE子句中引用l_filter，它从未被赋予一个值。代码能够编译执行不出错，但是不会显示任何结果。
D: 游标的参数有一个缺省值，就像程序参数那样，你可以打开游标而无需为那个参数传递一个值，Oracle会使用缺省值。
E: 这个选项指定了IN OUT作为参数模式。Oracle文档只显示了IN模式的使用，但是Oracle并不会抱怨IN OUT，而它当然也不会修改变量的值。
F: 这个选项将游标参数模式指定为OUT。Oracle在文档中没有允许这种做法，但是实际上它不会报编译错误。然而，既然模式是OUT，在游标中看不到这个变量的值，所以它找不到任何数据，也没有输出。

没什么好说的,没用过游标的可以百度用法。