I was also thinking about the C language ternary operator where there is no IF statement but a short cut as follows:
(x==y)?"true":"false";
This is a single line IF/ELSE; without the IF/ELSE keywords. The condition is tested and if true the first part after the "?" is returned as the result of the statement or if the condition is false then the part after the ":"
Can we do similar in PL, no ternary operator in PL/SQL BUT we can as it happens use the condition in other places than an IF statement. So if I have a simple function to test if a character is ALPHA i.e. a-z or A-Z then it looks like this:
function IsAlpha(pv_c in varchar2) return boolean is
lv_ret boolean;
begin
if(((ascii(pv_c)>=65) and (ascii(pv_c)<=90))
or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122))) then
lv_ret:=true;
else
lv_ret:=false;
end if;
return(lv_ret);
exception
when others then
dbms_output.put_line('Error');
end;
As you can see the simple function uses the ascii function to test the passed in character and then return TRUE or FALSE. I realised that we do not need the IF/ELSE/END IF as we can simply put the test condition in the return() directly and the function will then return TRUE or FALSE:
function IsAlpha2(pv_c in varchar2) return boolean is
begin
return(((ascii(pv_c)>=65) and (ascii(pv_c)<=90))
or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122)));
exception
when others then
dbms_output.put_line('Error');
end;
This is now a much shorter version of the function producing the same result BUT without the IF/ELSE/END IF now. Great, we reduced the code, increased complexity slightly but made the code succinct.
Obviously we cannot do this change everywhere in all PL/SQL code. It works because the IF/ELSE/END IF returns just true or false. But it will also work not just in return but also in other places that take a condition.
The whole thing is here to prove it works:
set serveroutput on
declare
lv_ret boolean;
--
function IsAlpha(pv_c in varchar2) return boolean is
lv_ret boolean;
begin
if(((ascii(pv_c)>=65) and (ascii(pv_c)<=90))
or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122))) then
lv_ret:=true;
else
lv_ret:=false;
end if;
return(lv_ret);
exception
when others then
dbms_output.put_line('Error');
end;
--
function IsAlpha2(pv_c in varchar2) return boolean is
begin
return(((ascii(pv_c)>=65) and (ascii(pv_c)<=90))
or ((ascii(pv_c)>=97) and (ascii(pv_c)<=122)));
exception
when others then
dbms_output.put_line('Error');
end;
--
begin
lv_ret:=IsAlpha('A');
dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
lv_ret:=IsAlpha('0');
dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
lv_ret:=IsAlpha2('A');
dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
lv_ret:=IsAlpha2('0');
dbms_output.put_line(sys.diutil.bool_to_int(lv_ret));
end;
/
And running gives:
SQL> @if
1
0
1
0
PL/SQL procedure successfully completed.
SQL>
The use of diutil.bool_to_int() is just to show the result. In the original use of this function it was embedded in another call so the IF/ELSE/END IF is truly removed
#oracleace #sym42 #plsql #securecode #oracle #security #extremeplsql #23ai #23c
