Re: How to Create procedure with Cursors, Input, Output

Hi,

I did this recently, and it works fine. I could have used a FOR...LOOP instead of direcly doing the FETCH, but I need to have more control of the looping for future enhancements.

The RETURN statement within the cursor is only really important if you use a FOR...LOOP. This enables one to pass the reurned record as a parameter to routines which take

        table_name%ROWTYPE

as a parameter.

The specification of this routine is in a seperate file, and is part of a package. The specification of the routine itself is simply the procedures signature without the IS, and terminated with a semi-colon. This makes the rutine public. The procedures and functions used within this routine are specified in the package body, this hides them from prying package users.

A good book on the subject is the Oracle Development Guide by David McClanahan from Oracle Press. This covers a lot of development areas, with a couple of large sections on PL-SQL, with a rather usefull quick reference section on the language.

Hope this helps.

PROCEDURE ProcessPrice IS

	dt			DATE ;
	CurrentCatalogue	product.catalogue%TYPE ;
	rowSellD		selldown%ROWTYPE ;

	CURSOR cSellDown 
		RETURN selldown%ROWTYPE IS
		SELECT * 
		FROM selldown ;
BEGIN
	CurrentCatalogue := GetCurrentCatalogue ;
	dt := Now ;
	OPEN cselldown ;
	LOOP
		FETCH cSellDown INTO rowSellD ;
		IF ProductExists(rowSellD.item, CurrentCatalogue) ;
			IF rowSellD.filetype = 1 THEN
				DeleteVendorCurrent(rowSellD.item, CurrentCatalogue) ;
				CreateVendorCurrent(rowSellD, CurrentCatalogue) ;
			ELSEIF rowSellD.filetype = 2 THEN
				DeleteVendorFuture(rowSellD.item, CurrentCatalogue) ;
				CreateVendorFuture(rowSellD, CurrentCatalogue) ;
			ELSEIF rowSellD.filetype = 3 THEN
				DeleteLocalCurrent(rowSellD.item, CurrentCatalogue) ;
				CreateLocalCurrent(rowSellD, CurrentCatalogue) ;
			ELSEIF rowSellD.filetype = 4 THEN
				CreateLocalFuture(rowSellD, CurrentCatalogue) ;
			END IF ;
		END IF ;
	EXIT WHEN cSellDown%NOTFOUND ;
	END LOOP ;
EXCEPTION
	WHEN others THEN
		RAISE Very_Very_Bad ;

Bryan.

-- 
Bryan Dollery BSc(Hons)
ByteSmart Systems Ltd.




Rodger wrote:


> 

> April 2, 1997


> 

> I want to create a procedure.  This procedure will have:

> - input variable(s)

> - output variable(s)

> - a cursor within the procedure.  I must be able to control the

> pointer movement in the cursor with Open, Close and Fetch statements.

> 

> What is aggravating is that I can get things to work

> in SQLPLUS, but I can't get them to work in PL/SQL.

> 

> IN SQLPLUS:

> 

> declare

> CURSOR  NTSI  IS

> Select

> ntsi_ref,

> ntsi_code

> from agt_ntsi;

> var_ntsi_ref      agt_ntsi.ntsi_ref%type;

> var_ntsi_code      agt_ntsi.ntsi_code%type;

> begin

> open ntsi;

> fetch ntsi into var_ntsi_ref, var_ntsi_code;

> DBMS_OUTPUT.PUT_LINE(var_ntsi_ref || ' , ' || var_ntsi_code);

> close ntsi;

> end ;

> 

> 1234, the_result

> PL/SQL procedure successfully completed.

> 

> PL/SQL:

> However, trying to do similar things in PL/SQL will only give:

> 

> Warning: Procedure created with compilation errors.

> 

> How can I get the simple concept to work in reality?

> 

> Thanks in Advance.