utility_b.sql

Java写的ERP系统

CREATE OR REPLACE PACKAGE BODY Utility
AS
	/**
	 *	Create Lookups and return in Arrays
	 */
	FUNCTION GetLookup
	(
		AD_Column_ID	IN  VARCHAR2,
		keyA			OUT Key_Array,
		valueA			OUT Value_Array,
		nameA			OUT	Name_Array
	)
	RETURN NUMBER
	AS
		TYPE RefCurTyp 	IS REF CURSOR;
		cv				RefCurTyp;
		keyV			NUMBER;
		valueV			VARCHAR2(30);
		nameV			VARCHAR2(60);
		--
		sqlCmd			VARCHAR(2000);
		Counter			NUMBER := 0;
	BEGIN
		--	Create Arrays
		keyA := Key_Array();
		valueA := Value_Array();
		nameA := Name_Array();
		--	Get SQL
		sqlCmd := Utility.getLookupSQL(AD_Column_ID, SYS_CONTEXT('AccortoInc','Language'));
		IF (sqlCmd IS NULL OR sqlCmd = '') THEN
			RETURN Counter;
		END IF;

		--	Query and fill Arrays
		OPEN cv FOR sqlCmd;
		LOOP
			FETCH cv INTO keyV, valueV, nameV;
			EXIT WHEN cv%NOTFOUND;

			Counter := Counter + 1;

            --	Add to key or value array and reset the other
            IF (keyV IS NOT NULL) THEN
				keyA.EXTEND;
				keyA(Counter) := keyV;
                valueA := NULL;
            ELSE
				valueA.EXTEND;
				valueA(Counter) := valueV;
                keyA := NULL;
            END IF;

			nameA.EXTEND;
			nameA(Counter) := nameV;

		END LOOP;
		CLOSE cv;

		RETURN Counter;

	END GetLookup;

END Utility;