Oracle存储过程案例

案例

定义变量、游标，使用游标，执行循环、选择结构…

CREATE OR REPLACE PROCEDURE DB.GET_GYBOP 
IS
 
	DP VARCHAR(64);  	-- GROUP
	PGU VARCHAR(64); 	-- PARENT GROUPUID
	LST1 VARCHAR(64); 	-- LIST1
	LST2 VARCHAR(64); 	-- LIST2
	LST3 VARCHAR(64); 	-- LIST3
	
	-- 游标
	CURSOR CRS IS 
		SELECT P1.PITEM_ID 								AS GYBOP_ID
			,PO.POBJECT_NAME 							AS GYBOP_NAME
			,PR1.PITEM_REVISION_ID 						AS GYBOP_VER_ID
			,PGY9.PGY9_PLANT 							AS GYBOP_PLANT
			,to_char((PO.PDATE_RELEASED + INTERVAL '8' HOUR),'yyyy-MM-dd HH24:mi:ss') AS GYBOP_VER_ISSUE_TIME
			,PU.PUSER_NAME||'('||PU.PUSER_ID||')' 		AS OWNING_USER
			,PPA.ROWNING_GROUPU 						AS GROUPUID
		FROM INFODBA.PITEM 						P
			,INFODBA.PITEMREVISION 				PR
			,INFODBA.PWORKSPACEOBJECT 			PO
			,INFODBA.PGY9MAINPROCESSREVISION 	PGY9
			,INFODBA.PPOM_APPLICATION_OBJECT 	PPA
			,INFODBA.PPOM_USER					PU
		WHERE P.PUID = PR.RITEMS_TAGU
			AND PR.PUID = PO.PUID
		--	AND PO.POBJECT_TYPE = 'GY9MainProcessRevision'
			AND PR.PUID = PGY9.PUID
			AND PR.PUID = PPA.PUID
			AND PPA.ROWNING_USERU = PU.PUID
			AND PO.PDATE_RELEASED IS NOT NULL
		;
			
BEGIN
	-- for循环
	FOR CR IN CRS LOOP
		LST1 := '';
		LST2 := '';
		LST3 := '';
		PGU := CR.GROUPUID;
		
		-- while循环
		WHILE PGU != 'AAAAAAAAAAAAAA' LOOP
		
			SELECT PG.PNAME, PG.RPARENTU 
			INTO DP,PGU
			FROM INFODBA.PPOM_GROUP PG 
			WHERE PG.PUID = PGU;
			
			LST3 := LST2;
			LST2 := LST1;
			LST1 := DP;
		END LOOP;
		
		-- if语句
		IF LST3 IS NOT NULL THEN
			-- 导入数据
			INSERT INTO DB.GYBOP(
				,GYBOP_ID
				,GYBOP_NAME
				,GYBOP_VER_ID
				,GYBOP_PLANT
				,GYBOP_VER_ISSUE_TIME
				,OWNING_USER
				,GYBOP_VER_BELONG_ORG_NAME
				,GYBOP_VER_PRODUCTLINE
				,DATE_NOW)
			VALUES (
				,CR.GYBOP_ID
				,CR.GYBOP_NAME
				,CR.GYBOP_VER_ID
				,CR.GYBOP_PLANT
				,CR.GYBOP_VER_ISSUE_TIME
				,CR.OWNING_USER
				,LST2
				,LST3
				,sysdate);
			COMMIT;
		END IF;
	END LOOP;
END GET_GYBOP;

补充case when语句

CASE 
	WHEN LST3 = 'Small' 	THEN LST3 := '小型';
	WHEN LST3 = 'Medium' 	THEN LST3 := '中型';
	WHEN LST3 = 'Large' 	THEN LST3 := '大型';
	ELSE LST3 := LST3;
END CASE;