5,889
社区成员
发帖
与我相关
我的任务
分享
with temptab(SORT_CD,SORT_NAME,PARENT_SORT_CD) as (
select SORT_CD,SORT_NAME,PARENT_SORT_CD
from CXMART.TN_BM_COL_SORT
where SORT_CD='VHJZ2L8d'
union all
select tn.SORT_CD, tn.SORT_NAME,temp.PARENT_SORT_CD
from CXMART.TN_BM_COL_SORT tn , temptab temp
where tn.PARENT_SORT_CD = temp.SORT_CD
) select SORT_CD from temptab;
CREATE FUNCTION DEL_SORT(SORTCD VARCHAR(50))
RETURNS table(S1 VARCHAR(50))
NO EXTERNAL ACTION
F1: BEGIN ATOMIC
RETURN
with temptab(SORT_CD,SORT_NAME,PARENT_SORT_CD) as (
select SORT_CD,SORT_NAME,PARENT_SORT_CD
from CXMART.TN_BM_COL_SORT
where SORT_CD=SORTCD
union all
select tn.SORT_CD, tn.SORT_NAME,temp.PARENT_SORT_CD
from CXMART.TN_BM_COL_SORT tn , temptab temp
where tn.PARENT_SORT_CD = temp.SORT_CD
) select SORT_CD from temptab;
END;
select S1 from table(DEL_SORT('Pff64fDD') as sort;
DELETE FROM CXMART.TN_BM_COL_SORT WHERE SORT_CD IN( select S1 from table(DEL_SORT('Pff64fDD')) as SORT);