Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to convert my query to stored procedure

591556Oct 21 2007 — edited Oct 21 2007

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. I am new to psp and stored procedures in oracle, have very little knowledge in this area, please help

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 18 2007
Added on Oct 21 2007
4 comments
1,042 views