存储过程创建无法通过，请高手指点。。。。

Question

CREATE OR REPLACE PROCEDURE P_Permission_GetUserPermission
(
 P_UserID IN NUMBER
)
AS
 RET VARCHAR2(4000);
BEGIN
 RET := '';
 WITH T AS (
  SELECT DISTINCT ActionID FROM RoleAction WHERE EXISTS(SELECT 1 FROM UserRole WHERE UserID = P_UserID AND UserRole.RoleID = RoleAction.RoleID)
  UNION ALL
  SELECT ActionID FROM UserAction WHERE UserID = P_UserID
 ),
 T1 AS (
  SELECT A.ID, A.ParentID FROM Action A JOIN T ON A.ID = T.ActionID
  UNION ALL
  SELECT A.ID, A.ParentID FROM Action A JOIN T1 ON A.ID = T1.ParentID
 ),
 T2 AS (
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.Action
 )
 SELECT WM_CONCAT(ActionCode) INTO RET FROM T2;
 SELECT RET;
END P_Permission_GetUserPermission;---------------------------------
这个存储过程的功能是获取某个用户的权限代码。
但我怎么搞都搞不同。

Answer 1

我是在oracle下开发的，但我只懂sql server.
开发oracle很恼火。
在oracle下怎么写呢？

Answer 2

T1 AS ( 
  SELECT A.ID, A.ParentID FROM Action A JOIN T ON A.ID = T.ActionID 
  UNION ALL 
  SELECT A.ID, A.ParentID FROM Action A JOIN T1 ON A.ID = T1.ParentID 
), 
这里面怎么引用了自己
T2 AS ( 
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.Action 
) 这里t1.action没有在t1里定义啊SELECT RET; 这是什么

Answer 3

(
P_UserID IN NUMBER
) 
-->(
P_UserID IN NUMBER,
o_Ret OUT varchar2
) T2 AS (
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.Action
)
SELECT WM_CONCAT(ActionCode) INTO RET FROM T2;
SELECT RET; ->T2 AS (
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.ID
)
SELECT WM_CONCAT(ActionCode) INTO RET FROM T2;
o_Ret :=RET;
exception when others then
 raise;

Answer 4

RET := ''; 你这赋值语句用的是Oracle的怎么你又是在SQL下？
最重要的是CTE
CTE 是一种临时命名的结果集，可通过定义语句加以引用。可在查询的 FROM 子句中引用 CTE，这与引用派生表和视图的方法相同。sql2005本身及以上才支持CTE，CTE通过关键字WITH建立.Oracle目前版本应该还没有CTE这样的用法，所以怎么能对呢？ 
WITH CTE_name[ (column_name [,...n] ) ]AS( CTE_query_specification ) 

Answer 5

按crazylaa的做法，修改如下：CREATE OR REPLACE PROCEDURE P_Permission_GetUserPermission
(
 P_UserID IN NUMBER,
 o_Ret OUT varchar2
)
IS
BEGIN
 WITH T AS (
  SELECT DISTINCT ActionID FROM RoleAction WHERE EXISTS(SELECT 1 FROM UserRole WHERE UserID = P_UserID AND UserRole.RoleID = RoleAction.RoleID)
  UNION ALL
  SELECT ActionID FROM UserAction WHERE UserID = P_UserID
 ),
 T1 AS (
  SELECT A.ID, A.ParentID FROM Action A JOIN T ON A.ID = T.ActionID
  UNION ALL
  SELECT A.ID, A.ParentID FROM Action A JOIN T1 ON A.ID = T1.ParentID
 ),
 T2 AS (
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.ID
 )
 SELECT WM_CONCAT(ActionCode) INTO RET FROM T2;
 o_Ret :=RET; 
 exception when others then
  raise;
END P_Permission_GetUserPermission;---------------
但还是有错：Warning: PROCEDURE created with compilation errors.

Answer 6

show err;
看看报什么错。

Answer 7

T1 AS (
  SELECT A.ID, A.ParentID FROM Action A JOIN T ON A.ID = T.ActionID
  UNION ALL
  SELECT A.ID, A.ParentID FROM Action A JOIN T1 ON A.ID = T1.ParentID
), -->这里怎么union all里面有自己(T1)？？？

Answer 8

我用的是TOAD,怎么看错误报告？

Answer 9

T1 AS (
  SELECT A.ID, A.ParentID FROM Action A JOIN T ON A.ID = T.ActionID
  UNION ALL
  SELECT A.ID, A.ParentID FROM Action A JOIN T1 ON A.ID = T1.ParentID
),-->这里怎么union all里面有自己(T1)？？？ 
----------------------------------------------
SQL SERVER 里面通过这种方式可以循环获取数据。

Answer 10

SQL> select id from t_org start with id=2 connect by prior pid = id;        ID
----------
         2
         1SQL> select * from t_org;        ID CODE            PID NAME
---------- -------- ---------- ------------------------------------------------------
-------------------------------------------------------------------------------------
         1 YKBD                35kV玉坑变
         2 KZKX              1 坑仔口线625
         3 KDX1              1 魁斗线623
         4 YDX6              1 玉斗线611
         5 AAX1              2 AA线
         6 ZFX1              1 竹凤线613
         7 BBX1              5 BB线
         8 CCX1              7 CC线
         9 DDX1              7 DD线
        10 4JX1              8 4级支线1
        11 4JX2              8 4级支线2
        12 4JX3              9 4级支线3
        13 4JX4              9 4级支线4
        14 YXBD             10 玉西13#变
        15 PLKQ             12 盘龙山矿泉水专变
        16 XXBD             11 XX变已选择16行。

Answer 11

SQL> select id from t_org start with id=12 connect by prior pid = id;        ID
----------
        12
         9
         7
         5
         2
         1已选择6行。

Answer 12

那我要获取T中的ID，和T中PrantID的ParentID。
能这样写吗？
SELECT ID, ParentID FROM Action START WITH ID IN (SELECT ActionID FROM T) CONNECT BY PRIOR ParentID = ID；
-----------------------------------
CREATE OR REPLACE PROCEDURE P_Permission_GetUserPermission
(
 P_UserID IN NUMBER,
 o_Ret OUT varchar2
)
IS
BEGIN
 WITH T AS (
  SELECT DISTINCT ActionID FROM RoleAction WHERE EXISTS(SELECT 1 FROM UserRole WHERE UserID = P_UserID AND UserRole.RoleID = RoleAction.RoleID)
  UNION ALL
  SELECT ActionID FROM UserAction WHERE UserID = P_UserID
 ),
 T1 AS (
  SELECT ID, ParentID FROM Action START WITH ID IN (SELECT ActionID FROM T) CONNECT BY PRIOR ParentID = ID
 ),
 T2 AS (
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.ID
 )
 SELECT WM_CONCAT(ActionCode) INTO RET FROM T2;
 o_Ret :=RET; 
 exception when others then
  raise;
END P_Permission_GetUserPermission;
--------------
这样还是有错

Answer 13

SQL> select id from t_org start with id in(12,11) connect by prior pid = id;        ID
----------
        11
         8
         7
         5
         2
         1
        12
         9
         7
         5
         2
         1已选择12行。可以这么写，但不知道oracle怎么用with t,t1,t2你这里好像是嵌套查询。不如你用select * from
 (select * from 
    (select * from Action a,....
    )t1
  where t1.**...
)t2
where t2...
这种嵌套的方式来做呢？
  

Answer 14

这样不知道行不？select WM_CONCAT(ActionCode) into RET
from
(
SELECT DISTINCT ActionCode FROM Action A,
(
SELECT ID, ParentID FROM Action START WITH ID IN 
(
SELECT ActionID FROM 
(
SELECT DISTINCT ActionID FROM RoleAction WHERE EXISTS
(
SELECT 1 FROM UserRole WHERE UserID = P_UserID 
AND UserRole.RoleID = RoleAction.RoleID
)
UNION ALL
SELECT ActionID FROM UserAction WHERE UserID = P_UserID 
)
) 
CONNECT BY PRIOR ParentID = ID 
)t1
where a.id = t1.id
);

Answer 15

上面的没格式了，重贴下select WM_CONCAT(ActionCode) into RET
from
(
SELECT DISTINCT ActionCode FROM Action A,
(
SELECT ID, ParentID FROM Action START WITH ID IN 
(
SELECT ActionID FROM 
(
SELECT DISTINCT ActionID FROM RoleAction WHERE EXISTS
(
SELECT 1 FROM UserRole WHERE UserID = P_UserID 
AND UserRole.RoleID = RoleAction.RoleID
)
UNION ALL
SELECT ActionID FROM UserAction WHERE UserID = P_UserID 
)
) 
CONNECT BY PRIOR ParentID = ID 
)t1
where a.id = t1.id
);

Answer 16

谢谢，crazylaa
--------------CREATE OR REPLACE PROCEDURE P_Permission_GetUserPermission
(
 P_UserID IN NUMBER,
 o_Ret OUT varchar2
)
IS
BEGIN
 WITH T AS (
  SELECT DISTINCT ActionID FROM RoleAction WHERE EXISTS(SELECT 1 FROM UserRole WHERE UserID = P_UserID AND UserRole.RoleID = RoleAction.RoleID)
  UNION ALL
  SELECT ActionID FROM UserAction WHERE UserID = P_UserID
 ),
 T1 AS (
  SELECT ID, ParentID FROM Action START WITH ID IN (SELECT ActionID FROM T) CONNECT BY PRIOR ParentID = ID
 ),
 T2 AS (
  SELECT DISTINCT ActionCode FROM Action A JOIN T1 ON A.ID = T1.ID
 )
 SELECT WM_CONCAT(ActionCode) INTO o_Ret FROM T2;
 exception when others then
  raise;
END P_Permission_GetUserPermission;
---------------
这样可以运行了