2011-11-16 %ROWTYPE的声明与使用

http://www.itpub.net/thread-1499223-15-1.html

150楼

我连接到HR用户并运行了如下语句：

CREATE TABLE plch_parts(   partnum    NUMBER, partname   VARCHAR2 (50))/BEGIN   INSERT INTO plch_parts        VALUES (1, 'Chassis');   COMMIT;END;/CREATE OR REPLACE PACKAGE plch_pkgIS   TYPE parts_t IS TABLE OF plch_parts%ROWTYPE                      INDEX BY PLS_INTEGER;   PROCEDURE plch_show_parts (parts_in IN parts_t);END;/CREATE OR REPLACE PACKAGE BODY plch_pkgIS   PROCEDURE plch_show_parts (parts_in IN parts_t)   IS   BEGIN      FOR indx IN 1 .. parts_in.COUNT      LOOP         DBMS_OUTPUT.put_line (parts_in (indx).partname);      END LOOP;   END;END;/GRANT SELECT ON plch_parts TO scott/GRANT EXECUTE ON plch_pkg TO scott/

然后我又用SCOTT用户连接并创建了这张表：

CREATE TABLE plch_parts(   partnum    NUMBER, partname   VARCHAR2 (50))/BEGIN   INSERT INTO plch_parts        VALUES (100, 'Wheel');   COMMIT;END;/

下列的哪些选项包含了下列语句块的一个声明部分，从而使得这个块执行之后"Wheel"会被显示出来？

BEGIN   SELECT *     BULK COLLECT INTO l_parts     FROM plch_parts;   hr.plch_pkg.plch_show_parts (l_parts);END;

(A)

DECLARE   TYPE parts_t IS TABLE OF plch_parts%ROWTYPE                      INDEX BY PLS_INTEGER;   l_parts   parts_t;

SQL> DECLARE  2    TYPE parts_t IS TABLE OF plch_parts%ROWTYPE INDEX BY PLS_INTEGER;  3    4    l_parts parts_t;  5    6  BEGIN  7    SELECT * BULK COLLECT INTO l_parts FROM plch_parts;  8    9    yoga.plch_pkg.plch_show_parts(l_parts); 10  END; 11  /DECLARE  TYPE parts_t IS TABLE OF plch_parts%ROWTYPE INDEX BY PLS_INTEGER;  l_parts parts_t;BEGIN  SELECT * BULK COLLECT INTO l_parts FROM plch_parts;  yoga.plch_pkg.plch_show_parts(l_parts);END;ORA-06550: 第 9 行, 第 3 列: PLS-00306: 调用 'PLCH_SHOW_PARTS' 时参数个数或类型错误ORA-06550: 第 9 行, 第 3 列: PL/SQL: Statement ignoredSQL>

(B)

DECLARE   TYPE parts_t IS TABLE OF hr.plch_parts%ROWTYPE                      INDEX BY PLS_INTEGER;   l_parts   parts_t;

SQL> DECLARE  2    TYPE parts_t IS TABLE OF yoga.plch_parts%ROWTYPE INDEX BY PLS_INTEGER;  3    4    l_parts parts_t;  5    6  BEGIN  7    SELECT * BULK COLLECT INTO l_parts FROM plch_parts;  8    9    yoga.plch_pkg.plch_show_parts(l_parts); 10  END; 11  /DECLARE  TYPE parts_t IS TABLE OF yoga.plch_parts%ROWTYPE INDEX BY PLS_INTEGER;  l_parts parts_t;BEGIN  SELECT * BULK COLLECT INTO l_parts FROM plch_parts;  yoga.plch_pkg.plch_show_parts(l_parts);END;ORA-06550: 第 9 行, 第 3 列: PLS-00306: 调用 'PLCH_SHOW_PARTS' 时参数个数或类型错误ORA-06550: 第 9 行, 第 3 列: PL/SQL: Statement ignoredSQL>

(C)

DECLARE   l_parts   hr.plch_pkg.parts_t;

SQL> DECLARE  2    l_parts yoga.plch_pkg.parts_t;  3    4  BEGIN  5    SELECT * BULK COLLECT INTO l_parts FROM plch_parts;  6    7    yoga.plch_pkg.plch_show_parts(l_parts);  8  END;  9  /WheelPL/SQL procedure successfully completedSQL>

(D)

DECLARE   SUBTYPE parts_t IS hr.plch_pkg.parts_t;   l_parts   parts_t;

SQL> DECLARE  2    SUBTYPE parts_t IS yoga.plch_pkg.parts_t;  3    l_parts parts_t;  4    5  BEGIN  6    SELECT * BULK COLLECT INTO l_parts FROM plch_parts;  7    8    yoga.plch_pkg.plch_show_parts(l_parts);  9  END; 10  /WheelPL/SQL procedure successfully completedSQL>

实测用yoga代替hr，test代替scott

答案在158楼

2011-11-16 答案CD.

%ROWTYPE是一种记录，如果你定义两个结构一模一样的记录类型，它们仍然不能够互换，会报类型不匹配的错误。