PL/SQL Fundamentals Exercises: PL/SQL block to assigns values to variable within a procedure

PL/SQL Fundamentals: Exercise-10 with Solution

Write a PL/SQL block to adjust the salary of the employee whose ID 122.

In the following example the variable empsal to the procedure salary_of_emp have been declared. The value of this variable have been calculated before and after invoking the procedure salary_of_emp.

Sample table: employees

PL/SQL Code:

DECLARE
  salary_of_emp  NUMBER(8,2);
 
  PROCEDURE approx_salary (
    emp        NUMBER, 
    empsal IN OUT NUMBER,
    addless     NUMBER
  ) IS
  BEGIN
    empsal := empsal + addless;
  END;
 
BEGIN
  SELECT salary INTO salary_of_emp
  FROM employees
  WHERE employee_id = 122;
 
  DBMS_OUTPUT.PUT_LINE
   ('Before invoking procedure, salary_of_emp: ' || salary_of_emp);
 
  approx_salary (100, salary_of_emp, 1000);
 
  DBMS_OUTPUT.PUT_LINE
   ('After invoking procedure, salary_of_emp: ' || salary_of_emp);
END;
/

Sample Output:

Before invoking procedure, salary_of_emp: 7900
After invoking procedure, salary_of_emp: 8900

Statement processed.

0.02 seconds

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to show a valid case-insensitive reference to a quoted and without quoted user-defined identifier.
Next: Write a PL/SQL block to show the operator precedence and parentheses in several more complex expressions.