Oracle存储过程编写(带输出参数)

原文来源:http://www.cnblogs.com/shengel/archive/2008/10/20/1315424.html

转贴理由:实际运用中的,展示了存储过程输出参数赋值的方法.与SQL存储过程赋值语法不同

create or replace procedure P_IMSCI_ALERTPAYTYPE
(
paramContractuuid in varchar2,
paramMapid in varchar2,
paramMappartid in varchar2,
paramSetpaytypename in varchar2 := null,
paramBuypaytypename in varchar2 := null,
retValue out int
)
is
var_Setpaytypeuuid ci_worktype.setpaytypeuuid%type := null;
var_Buypaytypeuuid ci_worktype.buymeasureuuid%type := null;

begin
---------------查询支付类型UUID
retValue := 0;
if ( paramSetpaytypename is not null and length( paramSetpaytypename ) > 0) then
select ty.paytypeuuid into var_Setpaytypeuuid from ci_paytype ty where ty.paytypename = paramSetpaytypename and ty.contractuuid = paramContractuuid;
end if;
if ( paramBuypaytypename is not null and length( paramBuypaytypename ) > 0) then
select ty.paytypeuuid into var_Buypaytypeuuid from ci_paytype ty where ty.paytypename = paramBuypaytypename and ty.contractuuid = paramContractuuid;
end if;
savepoint mark1;

--------------------支付申请 图纸工程量
retValue := -1;
update ci_worktype ty set ty.setpaytypeuuid = var_Setpaytypeuuid,ty.bbuypaytypeuuid = var_Buypaytypeuuid
where ty.contractuuid = paramContractuuid and ty.worktypeuuid =
(select mp.worktypeuuid from ci_scaleapp mp
where mp.mapid || mp.mappartid = paramMapid || paramMappartid
and mp.contractuuid = paramContractuuid);

---------------------工程款
retValue := -2;
update ci_scalewpc tt set tt.setpaytypeuuid = var_Setpaytypeuuid,
tt.buypaytypeuuid = var_Buypaytypeuuid ,
tt.setpaytypename=paramSetpaytypename,tt.buypaytypename=paramBuypaytypename
where tt.contractuuid = paramContractuuid
and tt.mapid || tt.mappartid = paramMapid || paramMappartid;

---------------------图纸工程量库
retValue := -3;
update ci_mapprojware tt set
tt.setpaytypename=paramSetpaytypename,tt.buypaytypename=paramBuypaytypename
where tt.contractuuid=paramContractuuid
and tt.mapid||tt.mappartid= paramMapid || paramMappartid;
retValue := 0;
commit;
Exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('select into 没有返回行异常');
rollback to savepoint mark1;
retValue :=-1;
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('select into 返回多行异常');
rollback to savepoint mark1;
retValue :=-1;
when OTHERS then
case retValue
when 0 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '查询支付类型UUID');
when -1 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '支付申请 图纸工程量');
when -2 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '工程款');
when -3 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '图纸工程量库');
else DBMS_OUTPUT.PUT_LINE('错误位置:' || '未知位置');
end case;
rollback to savepoint mark1;
end;

declare
refval int;
begin
P_IMSCI_ALERTPAYTYPE('e8799e72-f127-45d3-b54a-f247cca47dd0','PK8GB191103B30843SD','XQJ-TB-05C,配吊杆'
,'固定总包','固定单价', refval );
end;

P_IMSCI_modifytype('PK8GB191103B30843SD','XQJ-TB-05C,配吊杆');