我有一个oracle存储过程,其中有一个输入参数v_orgNum,字符串;输出参数是输出结果集。
在存储过程中我做一个for循环,for语句中引用到输入参数,原始语句如下:如果输入参数为v_orgNum='01.01'
则我的for循环语句中想要的效果是:
select cmp.FHISTABLENAME from T_HR_CMPSCHEME cmp left join t_org_admin org on cmp.fadminorgid=org.fid
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like '01.01%' ;
现在不知道这个参数如何指代进for语句中?如下语句正确应该怎么写出来?
for i in (select cmp.FHISTABLENAME from T_HR_CMPSCHEME cmp left join t_org_admin org on cmp.fadminorgid=org.fid
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like '输入参数v_orgNum%')
在存储过程中我做一个for循环,for语句中引用到输入参数,原始语句如下:如果输入参数为v_orgNum='01.01'
则我的for循环语句中想要的效果是:
select cmp.FHISTABLENAME from T_HR_CMPSCHEME cmp left join t_org_admin org on cmp.fadminorgid=org.fid
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like '01.01%' ;
现在不知道这个参数如何指代进for语句中?如下语句正确应该怎么写出来?
for i in (select cmp.FHISTABLENAME from T_HR_CMPSCHEME cmp left join t_org_admin org on cmp.fadminorgid=org.fid
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like '输入参数v_orgNum%')
CURSOR cur (pOrgNum varchar2) IS
select cmp.FHISTABLENAME from T_HR_CMPSCHEME cmp left join t_org_admin org on cmp.fadminorgid=org.fid
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like POrgNum || '%';for r in cur(v_orgNum) loop
...循环中的操作
end loop;
CURSOR cur (pOrgNum varchar2) IS
select cmp.FHISTABLENAME from T_HR_CMPSCHEME cmp left join t_org_admin org on cmp.fadminorgid=org.fid
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like POrgNum || '%';for r in cur(v_orgNum) loop
...循环中的操作
end loop;
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like v_orgNum||'%') 另外CURSOR应该是游标把,呵呵.应该不能称作光标
where cmp.FEnable=1 and cmp.FIsDelete=0 and cmp.FHisTableName is not null and org.fnumber like v_orgNum||'%')