Derek Pike Derek Pike - 12 days ago 9
SQL Question

Procedure help pl/sql

Create a procedure, LE_P6 that accepts a single numeric input parameter p_deptno representing a department number, sums the salaries of all employees fro that particular department, and then outputs that information to the screen. To handle the case where an inputted p_deptno does not exist, test if the um is null. If the sum is null, raise a user-defined exception and output to the screen a message indicating an error has occurred. In addition, test the procedure using the following cases and include the output.

p_deptno is 10

p_deptno is 50

set serveroutput on
CREATE OR REPLACE PROCEDURE LE_P6 (
p_deptno dept.deptno%TYPE,
p_sal emp.sal%TYPE) IS
null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN
select SUM(SAL)
Into p_sal
from emp;
EXCEPTION
WHEN null_salary THEN
DBMS.OUTPUT_PUT.LINE ('Salary cannot be null');
End LE_P6;

Answer

You need only one parameter not two and you have a typo in DBMS_OUTPUT package name. Try:

CREATE OR REPLACE PROCEDURE LE_P6 (
  p_deptno dept.deptno%TYPE) IS
  null_salary EXCEPTION;
  p_sal number;
PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN
  select SUM(SAL) 
  Into p_sal
  from emp
  where deptno = p_deptno;
  DBMS_OUTPUT.PUT_LINE (p_sal);
EXCEPTION
WHEN null_salary THEN
  DBMS_OUTPUT.PUT_LINE ('Salary cannot be null');
End LE_P6;