Home » SQL & PL/SQL » SQL & PL/SQL » Re: parameterized procedures
Re: parameterized procedures [message #36445] Wed, 28 November 2001 03:21
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
you have to decide how your program should act according to parameters.
look at following procedure, If you dont pass any value it will return all rows.you can call procedure below using
alex_new_draft;

CREATE OR REPLACE PROCEDURE alex_new_draft (rows IN NUMBER default null) IS

CURSOR c_client IS
SELECT client_no, bal_due
FROM franco_tab;

str_client_no franco_tab.client_no%type;
new_bal franco_tab.bal_due%type;
v_freq NUMBER := 10;
v_counter NUMBER := 0;
num_of_rows NUMBER;
BEGIN
OPEN c_client;

LOOP
v_counter := v_counter +1;
FETCH c_client INTO str_client_no, new_bal;
IF MOD(v_counter, v_freq) = 0 THEN

COMMIT;
END IF;
INSERT INTO alex_new_tab
VALUES(str_client_no, new_bal);
IF rows is null then
EXIT when c_client%notfound;
ELSE
EXIT when c_client%ROWCOUNT= rows;
END IF;

END LOOP;
CLOSE c_client;

END;

----------------------------------------------------------------------
Previous Topic: sparse vs. dense indexes
Next Topic: Delete Database ?
Goto Forum:
  


Current Time: Sun Mar 29 23:37:28 CDT 2020