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