oracle函数返回表类型，定义的记录表可以插入数据吗？求教，在线等

Question

SQL> create or replace type obj_dept as object
  2  (deptcode varchar2(30),
  3  deptname varchar2(60),
  4  parentcode varchar2(30),
  5  batchcount number,
  6  unprintcount number);
  7  /类型已创建。SQL> create or replace type t_depttree is table of obj_dept;
  2  /create or replace FUNCTION mfn_GetBatchDeptTree(FileInfoUID varchar2,UserName varchar2)
RETURN t_depttree
AS
cstid number;
depttree t_depttree := t_depttree();
begin

select customer_id  into cstid from FileInfo where UID=FileInfoUID;
insert into depttree
with 
P1(PrintDeptCode, BatchCount, PrintedCount) AS
(
SELECT PrintDeptCode, COUNT(*), SUM(CASE Printed WHEN 1 THEN 1 ELSE 0 END) FROM BatchInfo
WHERE FileInfo_uid=FileInfoUID
GROUP BY PrintDeptCode
), 
P (PrintDeptCode, BatchCount, UnPrintCount) AS
(
SELECT PrintDeptCode, BatchCount, BatchCount-PrintedCount FROM P1
left join CustomerDept d on P1.PrintDeptCode=d.DeptCode and d.Customer_Id=cstid
where d.username=UserName
),
DEPT (DeptCode, DeptName, ParentCode, BatchCount, UnPrintCount) AS
(
SELECT P.PrintDeptCode, D.DeptName, D.ParentCode, BatchCount, UnPrintCount FROM P 
LEFT JOIN CustomerDept D ON D.Customer_Id=cstid AND D.DeptCode=P.PrintDeptCode
UNION ALL
SELECT PARENT.DeptCode, PARENT.DeptName, PARENT.ParentCode, CHILD.BatchCount, CHILD.UnPrintCount 
FROM CustomerDept PARENT, DEPT CHILD
WHERE PARENT.DeptCode=CHILD.ParentCode AND PARENT.Customer_Id=cstid
)
 select deptcode, DeptName, ParentCode, SUM(BatchCount), SUM(UnprintCount) 
 from dept group by DeptCode, DeptName, ParentCode
order by ParentCode, DeptCode;

RETURN depttree;
END mfn_GetBatchDeptTree;

Answer 1

你的插入语法我看不懂，不好意思
参考一下简单类型的例子如何？建立和使用不包含任何方法的对象类型CREATE OR REPLACE TYPE PERSON AS OBJECT
(
   NAME VARCHAR2(10),AGE NUMBER(3),BIRTHDATE DATE
) 建立行对象
  CREATE TABLE PERSON_TABLE OF PERSON为行对象插入数据BEGIN
INSERT INTO PERSON_TABLE VALUES('张三',20,'11-4月-58');
INSERT INTO PERSON_TABLE VALUES('李四',20,'11-4月-58');
END;
DECLARE
PP PERSON;
BEGIN
SELECT VALUE(A) INTO PP FROM PERSON_TABLE A--(必须使用表别名)
where A.NAME='&NAME';
DBMS_OUTPUT.PUT_LINE(PP.NAME||'  '||PP.AGE||'  '||PP.BIRTHDATE);
END;

Answer 2

谢谢你啊，那create type t_depttree is table of obj_dept与你那个create table table_name of obj有什么区别啊，我那个with p什么的里面是结果集然后把查询的数据放在p里面，最后的目的是用到dept 里面的数据，然后再把dept里面的数据放到记录表里面，函数再返回记录表类型

Answer 3

我没有调试。不过我觉得应该这样吧
create or replace FUNCTION mfn_GetBatchDeptTree(FileInfoUID varchar2,UserName varchar2)
    RETURN t_depttree
AS
cstid number;
--depttree t_depttree := t_depttree();
begin
    
    select customer_id  into cstid from FileInfo where UID=FileInfoUID;
    insert into depttree
    with 
    P1(PrintDeptCode, BatchCount, PrintedCount) AS
    (
        SELECT PrintDeptCode, COUNT(*), SUM(CASE Printed WHEN 1 THEN 1 ELSE 0 END) FROM BatchInfo
            WHERE FileInfo_uid=FileInfoUID
            GROUP BY PrintDeptCode        
    ), 
    P (PrintDeptCode, BatchCount, UnPrintCount) AS
    (    
        SELECT PrintDeptCode, BatchCount, BatchCount-PrintedCount FROM P1
                left join CustomerDept d on P1.PrintDeptCode=d.DeptCode and d.Customer_Id=cstid
            where d.username=UserName
    ),
    DEPT (DeptCode, DeptName, ParentCode, BatchCount, UnPrintCount) AS
    (
        SELECT P.PrintDeptCode, D.DeptName, D.ParentCode, BatchCount, UnPrintCount FROM P 
            LEFT JOIN CustomerDept D ON D.Customer_Id=cstid AND D.DeptCode=P.PrintDeptCode
        UNION ALL
        SELECT PARENT.DeptCode, PARENT.DeptName, PARENT.ParentCode, CHILD.BatchCount, CHILD.UnPrintCount 
            FROM CustomerDept PARENT, DEPT CHILD
            WHERE PARENT.DeptCode=CHILD.ParentCode AND PARENT.Customer_Id=cstid
    )
    open t_depttree /*修改部分*/ select deptcode, DeptName, ParentCode, SUM(BatchCount), SUM(UnprintCount) 
         from dept group by DeptCode, DeptName, ParentCode
        order by ParentCode, DeptCode;
    RETURN depttree;
    END mfn_GetBatchDeptTree;

Answer 4

--我没有调试。不过我觉得应该这样吧
create or replace FUNCTION mfn_GetBatchDeptTree(FileInfoUID varchar2,UserName varchar2)
  RETURN t_depttree
AS
cstid number;
--depttree t_depttree := t_depttree();
begin
    
  select customer_id into cstid from FileInfo where UID=FileInfoUID;
  insert into depttree
  with  
  P1(PrintDeptCode, BatchCount, PrintedCount) AS
  (
  SELECT PrintDeptCode, COUNT(*), SUM(CASE Printed WHEN 1 THEN 1 ELSE 0 END) FROM BatchInfo
  WHERE FileInfo_uid=FileInfoUID
  GROUP BY PrintDeptCode   
  ),  
  P (PrintDeptCode, BatchCount, UnPrintCount) AS
  (   
  SELECT PrintDeptCode, BatchCount, BatchCount-PrintedCount FROM P1
  left join CustomerDept d on P1.PrintDeptCode=d.DeptCode and d.Customer_Id=cstid
  where d.username=UserName
  ),
  DEPT (DeptCode, DeptName, ParentCode, BatchCount, UnPrintCount) AS
  (
  SELECT P.PrintDeptCode, D.DeptName, D.ParentCode, BatchCount, UnPrintCount FROM P  
  LEFT JOIN CustomerDept D ON D.Customer_Id=cstid AND D.DeptCode=P.PrintDeptCode
  UNION ALL
  SELECT PARENT.DeptCode, PARENT.DeptName, PARENT.ParentCode, CHILD.BatchCount, CHILD.UnPrintCount  
  FROM CustomerDept PARENT, DEPT CHILD
  WHERE PARENT.DeptCode=CHILD.ParentCode AND PARENT.Customer_Id=cstid
  )
  open t_depttree /*修改部分*/ select deptcode, DeptName, ParentCode, SUM(BatchCount), SUM(UnprintCount)  
  from dept group by DeptCode, DeptName, ParentCode
  order by ParentCode, DeptCode;
  RETURN depttree;
  END mfn_GetBatchDeptTree; 
 
 
 

Answer 5

就是增加一个 open t_depttree???

Answer 6

主要是你这个看不懂！自己定义的type。怎么感觉像当成表来用似的。
还要insert，还要depttree t_depttree := t_depttree();
。好想java里面的定义class和newclass()一样！我浆糊了

Answer 7

不好意思啊，sqlserver改的，sqlserver里面可以定义返回的table，但是oracle就不行，sqlserver里面写得就是插入，我只能照葫芦画瓢了，求指教