Re: How is stored procedure created?

ispa_at_beagle.ispa.fsu.edu (ispa) wrote asking how to create a stored procedure from a .SQL script that takes an input argument.

For brevity I include the modifications...  

CREATE OR REPLACE
  PROCEDURE this_is_a_stored_proc (num IN BUDGET_ALLOCATION.BUD_NO%TYPE) AS

    code	BUDGET_ALLOCATION.BUD_CODE%TYPE;
    CURSOR	newalloc IS
		SELECT BUD_CODE FROM BUDGET_CODE ;

• Clear any previous references DELETE BUDGET_ALLOCATION WHERE BUD_NO = num;
• Create new budget allocations and set them to zero OPEN newalloc; LOOP FETCH newalloc INTO code; EXIT WHEN newalloc%NOTFOUND; INSERT INTO BUDGET_ALLOCATION VALUES (num, code, 0, 0, 0, 0); END LOOP; CLOSE newalloc; COMMIT; END; /

This will create the stored procedure in SQL*Plus. To call the procedure, simply do the following (from SQL*Plus)...

execute this_is_a_stored_proc (10);

To call from another SQL statement, you don't need to include the execute word. You can also create stored functions that return values, and define input or output parameters to a proc/function.

Hope this gets you going!. Received on Tue Feb 28 1995 - 19:38:57 CET