Example SQL Script use Cursor

person Jason Huangfolder_openCode, Oraclelocal_offer, , access_time October 9, 2009

Here is a very very simple Oracle P-SQL script that uses a cursor to do some operation:

SET SERVEROUTPUT ON
 
DECLARE
	CURSOR	curMLPList
	IS
	  SELECT DISTINCT IDENTITY
	  FROM	 MLP_HEADER
	  WHERE ROWNUM < 10
	  ORDER BY IDENTITY;
 
	pid varchar(20);
	pver varchar(10);
 
 
BEGIN
	 OPEN curMLPList;
	 LOOP
	 	 FETCH curMLPList into pid;
		 EXIT WHEN curMLPList%NOTFOUND;
 
		 SELECT MAX(PRODUCT_VERSION) INTO pver FROM MLP_HEADER WHERE IDENTITY = pid;
 
		 DBMS_OUTPUT.PUT_LINE(pid);
		 DBMS_OUTPUT.PUT_LINE(pver);
		 DBMS_OUTPUT.PUT_LINE('---');
 
		 INSERT INTO MLP_HEADER (IDENTITY, PRODUCT_VERSION, DESCRIPTION)
		 		SELECT IDENTITY, TO_NUMBER(PRODUCT_VERSION)+1 AS PRODUCT_VERSION, DESCRIPTION FROM MLP_HEADER
				where IDENTITY = pid AND PRODUCT_VERSION = pver;
 
--       SELECT * FROM MLP_HEADER WHERE IDENTITY = pid AND PRODUCT_VERSION = pver;
 
	 END LOOP;
 
	 CLOSE curMLPList;
 
END;
/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>