PL/SQL 多字段数组的创建

Oracle9i :ProgramwithPL/SQL Additional Practices 第11题

Create a PL/SQL block to retrieve the last name and department ID of each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is less than 114. From the values retrieved from the EMPLOYEES table, populate two PL/SQL tables, one to store the records of the employee last name and the other to store the records of their department IDs. Using a loop,retrieve the employee name information and the salary information from the PL/SQL tables and display it in the window ,using DBMS_OUTPUT.PUT_LINE.Display these details for the first 15 employees in the PL/SQL tables;

要求我们从EMPLOYEES 这张表中选出EMPLOYEE_ID<114的前15个人的LAST_NAME和EMPLOYEE_ID分别保存在两张PL/SQL tables中，参考答案是这样的

SET SERVEROUTPUT ON
DECLARE
TYPE Table_Ename is table of employees.last_name%TYPE
INDEX BY BINARY_INTEGER;
TYPE Table_dept is table of employees.department_id%TYPE
INDEX BY BINARY_INTEGER;
V_Tename Table_Ename;
V_Tdept Table_dept;
i BINARY_INTEGER :=0
CURSOR C_Namedept IS SELECT last_name,department_id from employees
WHERE employee_id < 115;
V_COUNT NUMBER := 15;
BEGIN
FOR emprec in C_Namedept
LOOP
i:=i+1;
V_Tename(i) := emprec.last_name;
V_Tdept(i) := emprec.department_id;
END LOOP;
FOR i IN 1..v_count
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || V_Tename(i) ||
' Department_id: ' || V_Tdept(i));
END LOOP;
END;
/
SET SERVEROUTPUT OFF

答案很正规正矩，但如果只用一张表存该怎么写，很自然的我们可能写出以下的PL/SQL

SET SERVEROUTPUT ON
DECLARE
TYPE Table_Ename_Dept is table of (last_name employees.last_name%TYPE,department_id employees.department_id%TYPE)
INDEX BY BINARY_INTEGER;
V_Tename_dept Table_Ename_Dept;
i BINARY_INTEGER :=0;
CURSOR C_Namedept IS SELECT last_name,department_id from employees WHERE employee_id < 115;
V_COUNT NUMBER := 15;
BEGIN
  OPEN C_Namedept;
  LOOP
       i:=i+1;
       FETCH C_Namedept INTO V_Tename_dept(i);
       EXIT WHEN i=15 OR C_Namedept%NOTFOUND;
  END LOOP;
  FOR i IN 1..v_count
      LOOP
      DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || V_Tename_dept(i).last_name ||
      ' Department_id: ' || V_Tename_dept(i).department_id);
  END LOOP;
END;
/

 

 创建一张Table_Ename_Dept表保存LAST_NAME 和EMPLOYEE_ID，下面的操作基本都差不多，很可惜，我们的语句编译不通过

ORA-06550: line 4, column 35:
PLS-00103: Encountered the symbol "(" when expecting one of the following:

   <an identifier> <a double-quoted delimited-identifier> long
   double ref char time timestamp interval date binary national
   character nchar
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "INDEX" when expecting one of the following:

   ; not alter final instantiable

显然是语法错了，但无论我们怎么改，就是编译不通过，除非只保留一个字段，难道就没办法使用两个字段或者更多的字段吗？是可以的，type is table of语法标准形式是这样的

TYPE  type_name IS TABLE OF 
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL] 
| table.%ROWTYPE
[INDEX BY BINARY_INTEGER];
identifier type_name

 注意到有个column_type的类型，该类型就是自定义类型，语法如下

TYPE  type_name IS RECORD
(field_declaration[,  field_declaration]…);

修改语句如下

DECLARE
TYPE Table_Type is RECORD(last_name employees.last_name%TYPE,department_id employees.department_id%TYPE);
TYPE Table_Ename_Dept is table of Table_TypeINDEX BY BINARY_INTEGER;
V_Tename_dept Table_Ename_Dept;
i BINARY_INTEGER :=0;
CURSOR C_Namedept IS SELECT last_name,department_id from employees WHERE employee_id < 115;
V_COUNT NUMBER := 15;
BEGIN
  OPEN C_Namedept;
  LOOP
       i:=i+1;
       FETCH C_Namedept INTO V_Tename_dept(i);
       EXIT WHEN i=15 OR C_Namedept%NOTFOUND;
  END LOOP;
  FOR i IN 1..v_count
      LOOP
      DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || V_Tename_dept(i).last_name ||
      ' Department_id: ' || V_Tename_dept(i).department_id);
  END LOOP;
END;
/

红色的那行就是自定义类型，这样就可以成功编译通过执行

SQL> @'E:\test.sql';
 
Employee Name: King Department_id: 90
Employee Name: Kochhar Department_id: 90
Employee Name: De Haan Department_id: 90
Employee Name: Hunold Department_id: 60
Employee Name: Ernst Department_id: 60
Employee Name: Austin Department_id: 60
Employee Name: Pataballa Department_id: 60
Employee Name: Lorentz Department_id: 60
Employee Name: Greenberg Department_id: 100
Employee Name: Faviet Department_id: 100
Employee Name: Chen Department_id: 100
Employee Name: Sciarra Department_id: 100
Employee Name: Urman Department_id: 100
Employee Name: Popp Department_id: 100
Employee Name: Raphaely Department_id: 30
 
PL/SQL procedure successfully completed

刚开始学习PL/SQL如果还有其他什么方法，敬请相告！！