SQLize Online / PHPize Online  /  SQLtest Online

CREATE OR REPLACE PROCEDURE PUB_GetPubTypesByTemplate ( p_template_id IN INTEGER, p__results OUT SYS_REFCURSOR ) AS v_template_id INTEGER; BEGIN v_template_id := p_template_id; OPEN p__results FOR WITH PT_CTE (ptid, ptpid, ptypeid, level) AS ( SELECT PT.pub_template_id as ptid, PT.pub_template_parent_id as ptpid, PT.pub_type_id as ptypeid, 1 AS level FROM PUBLICATION_TEMPLATE PT WHERE PT.pub_template_id = v_template_id UNION ALL SELECT PTP.pub_template_id as ptid, PTP.pub_template_parent_id as ptpid, PTP.pub_type_id as ptypeid, level + 1 as level FROM PUBLICATION_TEMPLATE PTP INNER JOIN PT_cte PTX ON PTP.pub_template_id = PTX.ptpid ) SELECT PT_CTE.ptypeid FROM PT_CTE; END PUB_GetPubTypesByTemplate; /