我写的这个存储过程很有问题。需求是用四个传入参数,查询这个输出参数,begin后面的查询语句是业务需求的,我应该改一些什么东西。
求大神们给点修改意见。
CREATE OR REPLACE PROCEDURE PRO_SACHA(
P_COMPANY IN varchar2,
P_PAY_GROUP_CD IN varchar2,
P_LOCATION_CODE IN varchar2,
P_BUSINESS_GROUP_CD IN varchar2,
P_FAMILY_NAME IN varchar2,
V_SACHA OUT varchar2
)
IS
BEGIN
SELECT SACHA INTO V_SACHA FROM(
SELECT a.COMPANY,a.PAY_GROUP_CD,a.LOCATION_CODE,a.BUSINESS_GROUP_CD,a.FAMILY_NAME,b.SACHA FROM DEU_SACHA_WD_TEST a
JOIN DEU_SACHA_TEST1 b ON a.COMPANY=b.COUNTRY
WHERE(
a.LOCATION_CODE=b.LOCATION_CD
and NVL(a.BUSINESS_GROUP_CD,'BLANK')=b.BIZ_GROUP_CD
and ascii(SUBSTR(b.FAMILY_NAME_START,1,1)) <=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and ascii(SUBSTR(b.FAMILY_NAME_START,3,1)) >=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and (instr(b.PAY_GROUP,',')>0 and instr(b.PAY_GROUP||',',a.PAY_GROUP_CD||',')>0 or (instr(b.PAY_GROUP,',')=0 and instr(b.PAY_GROUP,a.PAY_GROUP_CD)>0)))
OR (
b.LOCATION_CD ='ALL' AND a.LOCATION_CODE !='ALL' AND b.BIZ_GROUP_CD='ALL' AND NVL(a.BUSINESS_GROUP_CD,'BLANK') !='ALL'
and ascii(SUBSTR(b.FAMILY_NAME_START,1,1)) <=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and ascii(SUBSTR(b.FAMILY_NAME_START,3,1)) >=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and (instr(b.PAY_GROUP,',')>0 and instr(b.PAY_GROUP||',',a.PAY_GROUP_CD||',')>0 or (instr(b.PAY_GROUP,',')=0 and instr(b.PAY_GROUP,a.PAY_GROUP_CD,LENGTH(a.PAY_GROUP_CD))>0))
)
);
END;
求大神们给点修改意见。
CREATE OR REPLACE PROCEDURE PRO_SACHA(
P_COMPANY IN varchar2,
P_PAY_GROUP_CD IN varchar2,
P_LOCATION_CODE IN varchar2,
P_BUSINESS_GROUP_CD IN varchar2,
P_FAMILY_NAME IN varchar2,
V_SACHA OUT varchar2
)
IS
BEGIN
SELECT SACHA INTO V_SACHA FROM(
SELECT a.COMPANY,a.PAY_GROUP_CD,a.LOCATION_CODE,a.BUSINESS_GROUP_CD,a.FAMILY_NAME,b.SACHA FROM DEU_SACHA_WD_TEST a
JOIN DEU_SACHA_TEST1 b ON a.COMPANY=b.COUNTRY
WHERE(
a.LOCATION_CODE=b.LOCATION_CD
and NVL(a.BUSINESS_GROUP_CD,'BLANK')=b.BIZ_GROUP_CD
and ascii(SUBSTR(b.FAMILY_NAME_START,1,1)) <=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and ascii(SUBSTR(b.FAMILY_NAME_START,3,1)) >=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and (instr(b.PAY_GROUP,',')>0 and instr(b.PAY_GROUP||',',a.PAY_GROUP_CD||',')>0 or (instr(b.PAY_GROUP,',')=0 and instr(b.PAY_GROUP,a.PAY_GROUP_CD)>0)))
OR (
b.LOCATION_CD ='ALL' AND a.LOCATION_CODE !='ALL' AND b.BIZ_GROUP_CD='ALL' AND NVL(a.BUSINESS_GROUP_CD,'BLANK') !='ALL'
and ascii(SUBSTR(b.FAMILY_NAME_START,1,1)) <=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and ascii(SUBSTR(b.FAMILY_NAME_START,3,1)) >=ascii(SUBSTR(a.FAMILY_NAME,1,1))
and (instr(b.PAY_GROUP,',')>0 and instr(b.PAY_GROUP||',',a.PAY_GROUP_CD||',')>0 or (instr(b.PAY_GROUP,',')=0 and instr(b.PAY_GROUP,a.PAY_GROUP_CD,LENGTH(a.PAY_GROUP_CD))>0))
)
);
END;
去查一下ORACLE存储过程 的用法吧,和MSSQL的不一样,不能在存储过程里这样写的,
create[or replace] procedure procedure_name
[(parameter_name)[in|out|in out] type,[...]]
begin
procedure body
end procedure_name