The create or replace function is_complete_cause_data (acct_year in varchar2, comp_code in varchar2, copy_code in varchar2, cause_code in varchar2, type_code int)
Return varchar2 as

The begin

Declare the result varchar2

If type_code:=1
The begin
If the exists (select 1 from HTCP_WORK_CAUSE_DATA where ACCT_YEAR=ACCT_YEAR and COMP_CODE=COMP_CODE
And COPY_CODE=COPY_CODE and WORK_CAUSE_CODE=cause_code)
The begin
Set the result='completed'
The end;
The else

The begin
Set the result='unfinished'
The end;
The end;
The else
The begin
If the exists (select 1 from HTCP_RES_CAUSE_DATA where HTC_ACCT_YEAR=acct_year and HTC_COMP_CODE=comp_code
And HTC_COPY_CODE=copy_code and RES_CAUSE_CODE=cause_code)
The begin
Set the result='completed'
The end;
The else

The begin
Set the result='unfinished'
The end;
The end;

Return the result

The end;

CodePudding user response:

If the exists (select... )

To use this piece instead of:

V_count:=0;
Select count (*) into v_count where the from t...

If (v_count & gt; 0) then
- TODO
End the if;