Возможно ли реализовать CTE в процедуре? Вроде вопрос то простой, но не выходит.
Код: Выделить всё
create procedure myProc(idin Integer)
returns (id integer, namestruct varchar(100), lev integer, path varchar(100))
AS
begin
WITH RECURSIVE R_TREE_FORWARD (ID, L, PATH)
AS
(
SELECT ID, 0, CAST(ID AS VARCHAR(255))
FROM TREE
WHERE TREE.PARENT_ID=:idin
UNION ALL
SELECT TREE.ID, R_TREE_FORWARD.L + 1,R_TREE_FORWARD.PATH || '.' ||CAST(TREE.ID AS VARCHAR(8))
FROM TREE, R_TREE_FORWARD
WHERE TREE.PARENT_ID = R_TREE_FORWARD.ID
AND R_TREE_FORWARD.L < 1000
)
FOR
SELECT TREE.ID,TREE.NAMESTRUCT, R_TREE_FORWARD.L, R_TREE_FORWARD.PATH
FROM TREE, R_TREE_FORWARD
WHERE TREE.ID = R_TREE_FORWARD.ID
into :id, :namestruct, :lev, :path
DO
BEGIN
SUSPEND;
END
end;
Может этого делать нельзя?
С уважением.