View Exhibit1 and examine the structure of the employees table.
View Exhibit2 and examine the code.
What is the outcome when the code is executed?
A. Both blocks compile and execute successfully when called.
B. Both blocks compile successfully but the CALC_SAL procedure gives an error on execution.
C. The CALC_SAL procedure gives an error on compilation because the amt variable should be declared in the RAISE_SALARY procedure.
D. The CALC_SAL procedure gives an error on compilation because the RAISE_SALARY procedure cannot call the stand-alone increase function.
Correct answer A:
Checked that both blocks compile and execute successfully in HR schema:
create or replace function increase (emp_num number)
return number is
inc_amt number;
sal number;
begin
select salary into sal from employees where employee_id=emp_num;
inc_amt:=sal*.10;
return inc_amt;
end increase;
/
Function created.
create or replace procedure calc_sal is
emp_num number(6):=120;
amt number:=0;
procedure raise_salary (emp_id number) is
begin
amt:=increase(emp_num);
update employees set salary=salary+amt where employee_id=emp_id;
end raise_salary;
begin
raise_salary(emp_num);
end calc_sal;
/
Procedure created.
SQL> select salary from employees where employee_id=120;
SALARY
———-
8000
SQL> exec calc_sal;
PL/SQL procedure successfully completed.
SQL> select salary from employees where employee_id=120;
SALARY
———-
8800
SQL> rollback;
Rollback complete.
SQL> select salary from employees where employee_id=120;
SALARY
———-
8000
PROCEDURE raise_salary (emp_id number)
whats about emp_id which it wasn’t defined ??
It is the parameter in raise_salary, which will be substituted in the call: raise_salary(emp_num);