Sunday, October 21, 2007

how to convert my query to stored procedure

I have this procedure below:

set serveroutput on
DECLARE
v_counter NUMBER(7) := 0;
v_tax NUMBER(11,2) := 0;
v_an_sal NUMBER(11,2) := 0;
CURSOR emp_cursor IS
SELECT id, last_name, first_name, salary
FROM scott.s_emp;
--r_emp scott.s_emp%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(rpad(‘ID’,5) || rpad(‘LAST_NAME’, 15) || rpad(‘FIRST_NAME’,15) || lpad(‘ANNUAL_SALARY’,11) || lpad(‘TAX’,11));
DBMS_OUTPUT.PUT_LINE(rpad(‘____ ’,5) || rpad(‘_______________’, 15) || rpad(‘_______________’,15) || lpad(‘___________’,11) || lpad(‘___________’,11));
FOR r_emp IN emp_cursor LOOP
v_counter := v_counter + 1;
v_tax := 0;
v_an_sal := r_emp.salary * 12;
IF v_an_sal > 150000 THEN
v_tax := 47850 + (v_an_sal -150000)*0.45;
ELSIF v_an_sal > 75000 THEN
v_tax := 17850 + (v_an_sal -75000)*0.40;
ELSIF v_an_sal > 25000 THEN
v_tax := 2850 + (v_an_sal -25000)*0.30;
ELSIF v_an_sal > 6000 THEN
v_tax := (v_an_sal -6000)*0.15;
ELSE
v_tax := 0;
END IF;
DBMS_OUTPUT.PUT_LINE(rpad(r_emp.id,5) || rpad(r_emp.last_name, 15) || rpad(r_emp.first_name,15) || lpad(v_an_sal,11) || lpad(v_tax,11));
IF v_counter mod 5 = 0 THEN
DBMS_OUTPUT.PUT_LINE('******************************************************');
END IF;
END LOOP;
END;
/


I need to convert it to a stored procedure (or create a sored procedure for this) so that i can call it from my psp pages.

solution:
remove 'DECLARE set serveroutput on'
replace with 'CREATE OR REPLACE PROCEDURE proc_emp_sum_analysis AS'

No comments: