CREATE OR REPLACE PACKAGE pg IS TYPE TreeViewResult IS RECORD ( ID number , CategoryNO number, CategoryName varchar2(30)); END;CREATE function TreeViewResult(p_ID number) return pg.TreeViewResult as i_CategoryNO NUMBER; i_Level NUMBER:=0; TYPE TreeViewTemp IS RECORD ( CategoryNO number NOT NULL, CategoryName varchar2(30) NOT NULL, Parent number NULL, Level number NOT NULL ) ;BEGIN INSERT INTO TreeViewTemp select I_RWID, v_rwmc, i_fatherrwid, i_Level from xmgl_rwjhmxb where i_fatherrwid = 0 and I_ZBIDD=p_ID WHILE EXISTS (SELECT CategoryNO FROM TreeViewTemp)
BEGIN SELECT CategoryNO into i_CategoryNO FROM TreeViewTemp WHERE Level = i_Level AND ROWNUM=1 ORDER BY CategoryNO ;
IF SQL%ROWCOUNT = 0 then BEGIN i_Level := i_Level - 1; CONTINUE; END END IF ;
INSERT TreeViewResult SELECT CategoryNO, SPACE(2 * i_Level) + CategoryName --不知道你space函数是干嘛的 FROM TreeViewTemp WHERE CategoryNO = i_CategoryNO ;
INSERT TreeViewTemp SELECT I_RWID, v_rwmc, i_fatherrwid, i_Level + 1 FROM xmgl_rwjhmxb WHERE i_fatherrwid = i_CategoryNO and I_ZBIDD=p_ID ;
IF SQL%ROWCOUNT <> 0 then i_Level := i_Level + 1 ; END IF; DELETE TreeViewTemp WHERE CategoryNO = i_CategoryNO; END return TreeViewTemp; END ;
--具体你什么逻辑我不是很清楚,我这样给你改了,编译通过了CREATE TABLE TreeViewTemp ( CategoryNO number not null, CategoryName varchar2(30) not null, Parent number null, Leve number not null );CREATE TABLE TreeViewResult ( ID number , CategoryNO number, CategoryName varchar2(30));CREATE SEQUENCE seq_TreeViewResult START WITH 1 INCREMENT BY 1;CREATE OR REPLACE PROCEDURE F_TreeViewResult(p_ID NUMBER, TreeViewResult OUT sys_refcursor ) AS i_CategoryNO NUMBER; i_Level NUMBER:=0; i_id NUMBER; BEGIN INSERT INTO TreeViewTemp select I_RWID, v_rwmc, i_fatherrwid, i_Level from xmgl_rwjhmxb where i_fatherrwid = 0 and I_ZBIDD=p_ID AND EXISTS (SELECT CategoryNO FROM TreeViewTemp);
BEGIN SELECT CategoryNO into i_CategoryNO FROM TreeViewTemp WHERE Level = i_Level AND ROWNUM=1 ORDER BY CategoryNO ;
IF SQL%ROWCOUNT = 0 then BEGIN i_Level := i_Level - 1; END; END IF ;
begin SELECT seq_TreeViewResult.NEXTVAL INTO i_id FROM dual; INSERT INTO TreeViewResult SELECT i_id,CategoryNO, 2 * i_Level + CategoryName --不知道你space函数是干嘛的,我就去掉了 FROM TreeViewTemp WHERE CategoryNO = i_CategoryNO ; END;
INSERT INTO TreeViewTemp SELECT I_RWID, v_rwmc, i_fatherrwid, i_Level + 1 FROM xmgl_rwjhmxb WHERE i_fatherrwid = i_CategoryNO and I_ZBIDD=p_ID ;
IF SQL%ROWCOUNT <> 0 then i_Level := i_Level + 1 ; END IF; DELETE TreeViewTemp WHERE CategoryNO = i_CategoryNO; END; OPEN TreeViewResult for 'SELECT * FROM TreeViewResult'; END ;
CREATE OR REPLACE PACKAGE pg IS
TYPE TreeViewResult IS RECORD (
ID number ,
CategoryNO number,
CategoryName varchar2(30));
END;CREATE function TreeViewResult(p_ID number)
return pg.TreeViewResult
as
i_CategoryNO NUMBER;
i_Level NUMBER:=0;
TYPE TreeViewTemp IS RECORD
(
CategoryNO number NOT NULL,
CategoryName varchar2(30) NOT NULL,
Parent number NULL,
Level number NOT NULL
) ;BEGIN
INSERT INTO TreeViewTemp select I_RWID, v_rwmc, i_fatherrwid, i_Level
from xmgl_rwjhmxb
where i_fatherrwid = 0 and I_ZBIDD=p_ID
WHILE EXISTS (SELECT CategoryNO FROM TreeViewTemp)
BEGIN
SELECT CategoryNO into i_CategoryNO
FROM TreeViewTemp
WHERE Level = i_Level AND ROWNUM=1
ORDER BY CategoryNO ;
IF SQL%ROWCOUNT = 0 then
BEGIN
i_Level := i_Level - 1;
CONTINUE;
END
END IF ;
INSERT TreeViewResult
SELECT CategoryNO, SPACE(2 * i_Level) + CategoryName --不知道你space函数是干嘛的
FROM TreeViewTemp
WHERE CategoryNO = i_CategoryNO ;
INSERT TreeViewTemp
SELECT I_RWID, v_rwmc, i_fatherrwid, i_Level + 1
FROM xmgl_rwjhmxb
WHERE i_fatherrwid = i_CategoryNO and I_ZBIDD=p_ID ;
IF SQL%ROWCOUNT <> 0 then
i_Level := i_Level + 1 ;
END IF;
DELETE TreeViewTemp WHERE CategoryNO = i_CategoryNO;
END
return TreeViewTemp;
END ;
行:18
文本:WHILE EXISTS (SELECT CategoryNO FROM TreeViewTemp)错误:PL/SQL: SQL Statement ignored
行:15
文本:INSERT INTO TreeViewTemp select I_RWID, v_rwmc, i_fatherrwid, i_Level错误:PLS-00103: 出现符号 "END"在需要下列之一时:
; <an identifier>
<a double-quoted delimited-identifier>
符号 ";" 被替换为 "END" 后继续。
行:31
文本:END IF ;错误:PLS-00103: 出现符号 "TREEVIEWRESULT"在需要下列之一时:
into
<a SQL statement>
符号 "into" 被替换为 "TREEVIEWRESULT" 后继续。
行:33
文本:INSERT TreeViewResult错误:PLS-00103: 出现符号 "TREEVIEWTEMP"在需要下列之一时:
into
<a SQL statement>
符号 "into" 被替换为 "TREEVIEWTEMP" 后继续。
行:38
文本:INSERT TreeViewTemp错误:PLS-00103: 出现符号 "TREEVIEWTEMP"在需要下列之一时:
;
行:48
文本:return TreeViewTemp;能再改一下么?
--具体你什么逻辑我不是很清楚,我这样给你改了,编译通过了CREATE TABLE TreeViewTemp
(
CategoryNO number not null,
CategoryName varchar2(30) not null,
Parent number null,
Leve number not null
);CREATE TABLE TreeViewResult (
ID number ,
CategoryNO number,
CategoryName varchar2(30));CREATE SEQUENCE seq_TreeViewResult START WITH 1 INCREMENT BY 1;CREATE OR REPLACE PROCEDURE F_TreeViewResult(p_ID NUMBER, TreeViewResult OUT sys_refcursor )
AS
i_CategoryNO NUMBER;
i_Level NUMBER:=0;
i_id NUMBER;
BEGIN
INSERT INTO TreeViewTemp select I_RWID, v_rwmc, i_fatherrwid, i_Level
from xmgl_rwjhmxb
where i_fatherrwid = 0 and I_ZBIDD=p_ID
AND EXISTS (SELECT CategoryNO FROM TreeViewTemp);
BEGIN
SELECT CategoryNO into i_CategoryNO
FROM TreeViewTemp
WHERE Level = i_Level AND ROWNUM=1
ORDER BY CategoryNO ;
IF SQL%ROWCOUNT = 0 then
BEGIN
i_Level := i_Level - 1;
END;
END IF ;
begin
SELECT seq_TreeViewResult.NEXTVAL INTO i_id FROM dual;
INSERT INTO TreeViewResult
SELECT i_id,CategoryNO, 2 * i_Level + CategoryName --不知道你space函数是干嘛的,我就去掉了
FROM TreeViewTemp
WHERE CategoryNO = i_CategoryNO ;
END;
INSERT INTO TreeViewTemp
SELECT I_RWID, v_rwmc, i_fatherrwid, i_Level + 1
FROM xmgl_rwjhmxb
WHERE i_fatherrwid = i_CategoryNO and I_ZBIDD=p_ID ;
IF SQL%ROWCOUNT <> 0 then
i_Level := i_Level + 1 ;
END IF;
DELETE TreeViewTemp WHERE CategoryNO = i_CategoryNO;
END;
OPEN TreeViewResult for 'SELECT * FROM TreeViewResult';
END ;