PL/SQL on-machine exercises (II.)

Function:
1.

Function View string contains several special characters, such as ' A**B*CEDF ' contains several ' * '

2.
function to concatenate different work in the employee table into a string

3.
function calculates personal income tax, salary, salary income minus 3500,
Tax-free rate (%) rate divisor
No more than 1500 3 0
Over 1500 to 4500 10 105
Over 4500 to 9000 20 555
Over 9000 to 35000 25 1005

--1Create or Replace functionSpecial_char (cinch varchar2Vinch varchar2)        return   Number isS_num Number; beginS_num:=Length (c)-LengthReplace(C,v,"')); return(S_num); End; DeclareNum Number;beginNum:=Special_char ('Aa****aa','*'); Dbms_output.put_line (num);End;--2Create or Replace functionall_workreturn varchar2  isWnamevarchar2( -) ;begin       Selectwm_concat (Job) intoWname from(Select distinctJob fromEMP); return(wname);Endall_work;DeclareWnamevarchar( -);beginWname:=all_work ();d bms_output.put_line (wname);End;--3Create or Replace functionTax (p_empnoinchEmp.empno%type)return   Number  isT Number ;begin  SelectSal+NVL (Comm,0)-3500    intoT fromEMPwhereEmpno=P_empno; ifT<0  ThenT:=0; elsif T< the  ThenT:=T*0.03; elsif T<4500  ThenT:=T*0.1- the; elsif T<9000  ThenT:=T*0.2-555; elsif T<35000  ThenT:=T*0.25-1005; End if; return(t);EndTax ;DeclareT Number;beginT:=Tax7839);d bms_output.put_line (t);End;

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

PL/SQL on-machine exercises (II.)