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.)