oracle - 如何在具有多个连接的 Pl/SQL 中编写对象类型作为 out 参数的存储过程

Question

我正在尝试使用以下示例用例和表格编写存储过程，

Employee
Emp_id Emp_Name 1 Jhon 2 Mark 3 Marry

Department
Emp_Id Dept_Id 1 A 2 B 3 C 1 B 2 D

Assets Emp_Id Asset_Name 1 AA 1 BB 2 CC 2 DD 4 EE 4 FF

关系

一名员工可以添加到多个部门。例如 Emp 1 添加到 A 和 B 部门。

一名员工可以拥有多个资产，例如，Emp 1 拥有资产 AA 和 BB。

Employee 和 Assets 之间没有外键约束。所以资产可以有 EmpId，这在 Employee Table 中不可用。例如 Emp 4

期望的输出

EmployeeInfo Emd_Id Emp_Name Array of Dept_Id[] Array of Assets[]

员工 ID 1 的期望结果

Emd_Id :1 Emp_Name :Jhon Array of Dept_Id[] :[A,B] Array of Assets[] :[AA,BB]

员工 ID 4 的期望结果

Emd_Id :4 Emp_Name :null -- As no entry in Employee table. Array of Dept_Id[] :null Array of Assets[] :[EE,FF]

所以想为此编写一个存储过程。请为此提出解决方案。这可以通过多个游标或对象类型输出变量来实现吗？

存储过程我尝试如下，

CREATE OR REPLACE PROCEDURE PRC_TEST( employeeInfo OUT SYS_REFCURSOR) IS BEGIN OPEN employeeInfo FOR SELECT e.EMP_ID ,e.EMP_NAME, d.DEPT_ID, a.ASSET_NAME FROM EMPLOYEE e, DEPARTMENT d, ASSETS a WHERE e.EMP_ID = d.EMP_ID AND e.EMP_ID = a.EMP_ID; END; 提前致谢

标签： oraclestored-proceduresplsqlcursor

Answer 1

最佳实践是创建包定义和主体。您的包定义将如下所示：

CREATE OR REPLACE PACKAGE EmployeeInfo AS

TYPE departament_type IS TABLE OF VARCHAR2(100);
TYPE assets_type      IS TABLE OF VARCHAR2(100);

PROCEDURE get_employee_info ( Emp_Id_col      IN OUT NUMBER
                             ,Emp_Name_col       OUT VARCHAR2
                             ,departament_tbl    OUT DEPARTAMENT_TYPE
                             ,assets_tbl         OUT ASSETS_TYPE);

END EmployeeInfo;

然后你的包体将与你的定义相匹配，这就是将要实施该过程的地方：

CREATE OR REPLACE PACKAGE BODY EmployeeInfo AS

PROCEDURE get_employee_info ( Emp_Id_col      IN OUT NUMBER
                             ,Emp_Name_col       OUT VARCHAR2
                             ,departament_tbl    OUT DEPARTAMENT_TYPE
                             ,assets_tbl         OUT ASSETS_TYPE)
IS 
BEGIN

BEGIN
  SELECT Emp_Name
  INTO   Emp_Name_col
  FROM   Employee
  WHERE  Emp_Id = Emp_Id_col;
EXCEPTION
  WHEN NO_DATA_FOUND THEN  
    RETURN;
END;  

departament_tbl := DEPARTAMENT_TYPE();

FOR dep_rec IN (SELECT *
                FROM   Department
                WHERE  Emp_id = Emp_Id_col) LOOP 
  departament_tbl.extend;
  departament_tbl(departament_tbl.COUNT) := dep_rec.Dep_Id;
END LOOP;

assets_tbl := ASSETS_TYPE();

FOR asset_rec IN (SELECT *
                  FROM   Assets
                  WHERE  Emp_Id = Emp_Id_col) LOOP 
  assets_tbl.extend;
  assets_tbl(assets_tbl.COUNT) := asset_rec.Asset_Name;
END LOOP;

END get_employee_info;

END EmployeeInfo;

现在这里有一个非常简单的存储过程测试脚本：

DECLARE
  Emp_Id_col            NUMBER(10);
  Emp_Name_col          Employee.Emp_Name%TYPE;
  departament_tbl       EMPLOYEEINFO.DEPARTAMENT_TYPE;
  assets_tbl            EMPLOYEEINFO.ASSETS_TYPE;
BEGIN
  Emp_Id_col := 1;
  EMPLOYEEINFO.GET_EMPLOYEE_INFO(Emp_Id_col
                                ,Emp_Name_col
                                ,departament_tbl
                                ,assets_tbl);

  DBMS_OUTPUT.PUT_LINE(Emp_Name_col          || ' - ' ||
                       departament_tbl.COUNT || ' - ' || 
                       assets_tbl.COUNT);

END;