Create a PL/SQL block to calculate the compensation amount for one year.

Create a PL/SQL block to calculate the compensation amount for one year.

Requirements

2 Pass the annual payroll and annual bonus ratios to PL/SQL blocks by using an *plus substitution variable

The 2 bonus ratio is an integer that is converted to a decimal form in the block (for example, 15 to 0.15).

2 If the salary is empty, the salary is set to 0 when the total compensation amount is calculated.

Hint: Use the NVL function to handle null values.

The following is displayed after execution:

Please enter the salary amount:50000

Please enter the bonus percentage:10

PL/SQL procedure successfully completed.

G_total

-------

55000

Sql>Accept P_sal Prompt'Please enter the salary amount:'Please enter the salary amount:50000SQL>Accept P_bonus Prompt'Please enter the bonus percentage:'Please enter the bonus percentage:TenSQL>Variable G_total Number; SQL> begin  2: g_total:=Nvl&P_sal,0)*(1+&P_bonus*0.01); 3  End; 4  /Original Value2:: G_total:=Nvl&P_sal,0)*(1+&P_bonus*0.01); new value2:: G_total:=Nvl50000,0)*(1+Ten*0.01);P L/The SQL process has completed successfully. SQL> PrintG_total; G_total----------     55000

Knowledge Points:

1.accpet: Can be used to accept pre-specified variable value, to change the prompt statement

SQL>Acceptprompt ' pleaseenter the salary amount:' Please enter the salary amount:50000

2.NVL function

  

Add: NVL2 function

The format of the NVL2 function is as follows: NVL2 (EXPR1,EXPR2, EXPR3)

The meaning is: if the first argument of the function is empty then the value of the second parameter is displayed, and if the value of the first parameter is not NULL, the value of the third parameter is displayed.

SQL>Select ename,nvl2 (comm,-1,1 from EMP;

Nullif function

Sql> Declare  2V_num1 Number:=2; 3V_num2 Number:=2; 4A Number; 5  begin  6  Select Nullif(V_NUM1,V_NUM2) intoA fromdual; 7Dbms_output.put_line ('A:'||a); 8  End; 9  /A:PL/The SQL process has completed successfully. SQL> Declare  2V_num1 Number:=2; 3V_num2 Number:=3; 4A Number; 5  begin  6   Select Nullif(V_NUM1,V_NUM2) intoA fromdual; 7Dbms_output.put_line (a); 8  End; 9  /2PL/The SQL process has completed successfully.

Create a PL/SQL block to calculate the compensation amount for one year.