Oracle Stored Procedure writing (with output parameters)

Source: http://www.cnblogs.com/shengel/archive/2008/10/20/1315424.html

Reason for posting: in practice, the method of assigning values to output parameters in stored procedures is displayed. It is different from the SQL stored procedure assignment syntax.

 

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
--------------- Query the payment type 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;

-------------------- Pay the application for drawing quantities
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 );

--------------------- Project cost
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;

--------------------- Drawing engineering quantity Library
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 no row exception returned ');
Rollback to savepoint mark1;
Retvalue: =-1;
When too_many_rows then
Dbms_output.put_line ('select into return multi-row exception ');
Rollback to savepoint mark1;
Retvalue: =-1;
When others then
Case retvalue
When 0 then dbms_output.put_line ('wrong location: '|' query payment type UUID ');
When-1 then dbms_output.put_line ('wrong location: '| 'amount of work for applying for payment ');
When-2 then dbms_output.put_line ('error location: '| 'Project money ');
When-3 then dbms_output.put_line ('wrong location: '| 'Drawing engineering quantity warehouse ');
Else dbms_output.put_line ('error location: '| 'unknown location ');
End case;
Rollback to savepoint mark1;
End;

 

Declare
Refval int;
Begin
P_imsci_alertpaytype ('e8799e72-f127-45d3-b54a-f247cca47dd0 ', 'pk8gb191103b30843sd', 'xqj-TB-05C, with suspenders'
, 'Fixed total package', 'fixed unit price', refval );
End;

P_imsci_modifytype ('pk8gb191103b30843sd', 'xqj-TB-05C, with suspenders ');