Question

Create an anonymous PL/SQL block that accepts the current salary and raise percentage through SQL*Plus substitution variables and calculate the new salary based on the input: new salary := old salary * ( 1 + raise percentage / 100 ) The result should be stored in a PL/SQL variable and printed on the screen. Your program should handle NULL values. A NULL value entered for either one or both of the input values is equivalent to a numerical 0 (Note: To associate NULL values for your SQL*Plus substitution variables, just enter NULL.

Answer 1

SET SERVEROUTPUT ON;

DECLARE
new_salary NUMBER(10,2);
current_salary NUMBER(10,2):=10000;
raise_percent NUMBER(10,2):=5;

BEGIN
new_salary := NVL(current_salary,0) * ( 1 + NVL(raise_percent,0)/ 100 );
DBMS_OUTPUT.PUT_LINE( new_salary );
END;

Output( None of the variable is NULL)

Chrome File Edit View History Bookmarks People Tab Tab Window Help 43%D Sat 12:29 PM Q i whatsapı (3) What x Oracle LI X I compile x </> Online o x y? oracle pix y! sql plus y cast cha X Oracle CX y! oracle su X Oracle SX + livesql.oracle.com/apex/f?p=590:1:4423195949213::NO:.. O * ... = Live SQL Feedback Help [email protected] Home SQL Worksheet Clear Find Actions Save Run > SQL Worksheet 5 My Session Schema 1 SET SERVEROUTPUT ON; 2 3 DECLARE 4 new_salary NUMBER(10,2); 5 current_salary NUMBER(10,2):=10000; 6 raise_percent NUMBER(10,2):= 5; 7 8 BEGIN 9 new_salary := NUL(current_salary,)*(1 + NVL (raise_percent,)/100); 10 DBMS_OUTPUT.PUT_LINE( new_salary); 11 END; * Quick SQL My Scripts My Tutorials Code Library Unsupported Command Statement processed. 10500 2020 Oracle Corporation. Privacy Terms of Use ORACLE Integrated Cloud Oracle Learning Library. Ask Tom Dev Gym Database Doc 190 , 18 , 12c Follow on Twitter Applications & Platform Services Live SQL 20.2.2, running Oracle Database 19c Enterprise Edition - 19.5.0.0.0 Built with using Oracle APEX 1 W х ***

SET SERVEROUTPUT ON;

DECLARE
new_salary NUMBER(10,2);
current_salary NUMBER(10,2);
raise_percent NUMBER(10,2):=5;

BEGIN
new_salary := NVL(current_salary,0) * ( 1 + NVL(raise_percent,0)/ 100 );
DBMS_OUTPUT.PUT_LINE( new_salary );
END;

Output ( Salary is NULL)

Chrome File Edit View History Bookmarks People Tab Tab Window Help 43%D Sat 12:29 PM Q i whatsapı (3) What x Oracle LI X I compile x </> Online o x y? oracle pix y! sql plus y cast cha X Oracle CX y! oracle su X Oracle SX + livesql.oracle.com/apex/f?p=590:1:4423195949213::NO::: O * ... = Live SQL Feedback Help [email protected] Home SQL Worksheet Clear Find Actions Save Run SQL Worksheet 5 My Session Schema 1 SET SERVEROUTPUT ON; 2 3 DECLARE 4 new_salary NUMBER(10,2); 5 current_salary NUMBER(10,2); 6 raise_percent NUMBER(10,2)= 5; 7 8 BEGIN 9 new_salary := NUL(current_salary,)*(1 + NVL (raise_percent,)/100); 10 DBMS_OUTPUT.PUT_LINE( new_salary); 11 END; * Quick SQL My Scripts My Tutorials Code Library Unsupported Command Statement processed. 2020 Oracle Corporation. Privacy Terms of Use ORACLE Integrated Cloud Oracle Learning Library. Ask Tom Dev Gym Database Doc 190 , 18 , 12c Follow on Twitter Applications & Platform Services Live SQL 20.2.2, running Oracle Database 19c Enterprise Edition - 19.5.0.0.0 Built with using Oracle APEX 1 W х ***

SET SERVEROUTPUT ON;

DECLARE
new_salary NUMBER(10,2);
current_salary NUMBER(10,2):=10000;
raise_percent NUMBER(10,2);

BEGIN
new_salary := NVL(current_salary,0) * ( 1 + NVL(raise_percent,0)/ 100 );
DBMS_OUTPUT.PUT_LINE( new_salary );
END;

Output ( Raise is NULL)

Chrome File Edit View History Bookmarks People Tab Tab Window Help 43%D Sat 12:29 PM Q i whatsapı (3) What x Oracle Lix I compile x </> Online o x y? oracle pix y! sql plus y cast cha X Oracle CX y! oracle su X Oracle SX + livesql.oracle.com/apex/f?p=590:1:4423195949213::NO::: O 62 ... = Live SQL Feedback Help [email protected] Home SQL Worksheet Clear Find Actions A Save Run SQL Worksheet 5 My Session Schema 1 SET SERVEROUTPUT ON; 2 3 DECLARE 4 new_salary NUMBER(10,2); 5 current_salary NUMBER(10,2)=10000; 6 raise_percent NUMBER(10,2): 7 8 BEGIN 9 new_salary := NUL(current_salary,) (1 + NVL (raise_percent,0)/100); 10 DBMS_OUTPUT.PUT_LINE( new_salary); 11 END; * Quick SQL My Scripts My Tutorials Code Library Unsupported Command Statement processed. 10000 2020 Oracle Corporation. Privacy Terms of Use ORACLE Integrated Cloud Oracle Learning Library. Ask Tom Dev Gym Database Doc 190 , 18 , 12c Follow on Twitter Applications & Platform Services Live SQL 20.2.2, running Oracle Database 19c Enterprise Edition - 19.5.0.0.0 Built with using Oracle APEX 1 W х ***