Search code examples
sqloracle-databaseplsqlprocedure

How I can resolve this Procedure in Oracle PLSQL (It's not homework or something like that)


I'm learning Oracle Database and PL/SQL. I'm trying to create a PROCEDURE to query annual salary for an specific employee. What is wrong with my code? Thanks.

CREATE OR REPLACE PROCEDURE annual_salary (
  p_lname IN employees23.last_name%TYPE,
  p_empid IN employees23.employee_id%TYPE)
IS 
BEGIN
  DECLARE
    v_annualsal employees23.salary%TYPE;
  BEGIN
    SELECT salary*12
    INTO v_annualsal
    FROM employees23
    WHERE last_name = p_lname;
    DBMS_OUTPUT.PUT_LINE(v_annualsal);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No existe empleado');
  END;
END annual_salary;
/
BEGIN
  annual_salary('Russell');
END;

Solution

  • create table employees23 as 
        select 10 emp_id, cast('Russell' as varchar2(30)) last_name, 6000.00 salary
        from dual; 
    
    create or replace procedure annual_salary (lname employees23.last_name%type) is
        annualsal employees23.salary%type;
    begin
        select salary*12 into annualsal
        from employees23
        where last_name = lname
        ;
        dbms_output.put_line (lname || ' has ' || annualsal || ' p.a.');
    exception when no_data_found then
        raise_application_error (-20000, 'emploee ' || lname || ' does not exists');
    end annual_salary;
    /
    
    exec  annual_salary('Russell');