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;
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');