CREATE OR REPLACE PACKAGE AbbrvData AS /* Package: AbbrvData By: Kelly Conway, WSA CORP Updated: 2/26/2002 Purpose: Provides all data access and manipulation for the ABBRV table. [Well, it will eventually...<s>] */ -- Define one "TABLE OF" per field to appear in returned cursor. TYPE tblAbbrv_code IS TABLE OF abbrv.abbrv_code%TYPE INDEX BY BINARY_INTEGER; TYPE tblTitle_1 IS TABLE OF abbrv.title_1%TYPE INDEX BY BINARY_INTEGER; TYPE tblTitle_2 IS TABLE OF abbrv.title_2%TYPE INDEX BY BINARY_INTEGER; TYPE tblTitle_3 IS TABLE OF abbrv.title_3%TYPE INDEX BY BINARY_INTEGER; TYPE tblPriority_C IS TABLE OF abbrv.priority_c%TYPE INDEX BY BINARY_INTEGER; TYPE tblP_Key IS TABLE OF abbrv.p_key%TYPE INDEX BY BINARY_INTEGER; PROCEDURE GetData ( key_value_in IN abbrv.abbrv_code%TYPE, abbrv_code OUT tblAbbrv_code, title_1 OUT tblTitle_1, title_2 OUT tblTitle_2, title_3 OUT tblTitle_3, priority_c OUT tblPriority_C, P_Key OUT tblP_Key ); END; /* Package Declaration */ / CREATE OR REPLACE PACKAGE BODY AbbrvData AS PROCEDURE GetData ( key_value_in IN abbrv.abbrv_code%TYPE, abbrv_code OUT tblAbbrv_code, title_1 OUT tblTitle_1, title_2 OUT tblTitle_2, title_3 OUT tblTitle_3, priority_c OUT tblPriority_C, P_Key OUT tblP_Key ) IS /* NOTE: The key_value_in IN parameter is optional. If not passed, ALL records are returned. NOTE: Despite the existence of p_key in this table, whoever set up the database decided for some reason to use abbrv_code as the foreign key in related tables. */ CURSOR c_data IS SELECT abbrv_code, title_1, title_2, title_3, priority_c, p_key FROM abbrv WHERE key_value_in IS NULL OR abbrv_code = key_value_in ORDER BY abbrv_code; table_index NUMBER := 1; BEGIN /* This section is commented because the compiler chokes on the first "c_data", saying that it expected a symbol like ;@ etc. IF key_value_in IS NULL THEN CURSOR c_data IS SELECT abbrv_code, title_1, title_2, title_3, priority_c, p_key FROM abbrv ORDER BY abbrv_code; ELSE CURSOR c_data IS SELECT abbrv_code, title_1, title_2, title_3, priority_c, p_key FROM abbrv WHERE abbrv_code = key_value_in END IF; *** End of commented section *** */ -- Loop through the cursor and fill the TABLE arrays. -- Note that, by using the {resultset ...} syntax featured -- in several MS KB articles wrt getting cursors from Oracle -- procedures, this mess of arrays is returned as columns of -- a single cursor. FOR SingleRecord IN c_Data LOOP abbrv_code ( table_index ) := SingleRecord.abbrv_code; title_1 ( table_index ) := SingleRecord.title_1; title_2 ( table_index ) := SingleRecord.title_2; title_3 ( table_index ) := SingleRecord.title_3; priority_c ( table_index ) := SingleRecord.priority_c; p_key ( table_index ) := SingleRecord.p_key; table_index := table_index + 1; END LOOP; END; END; /* Package Body Declaration */ /The VFP syntax I'm using to execute this procedure:
nConnection = SQLSTRINGCONNECT ( ... ) cSQL = "{call AbbrvData.GetData(?cCode, {resultset 1000, " ; + "abbrv_code, title_1, title_2, title_3, priority_c, p_key})}" cCode = "CEO" ? SQLEXEC ( nConnection, cSQL, "c_Abbrv" ) * One record is returned, the one with abbrev_code = "CEO" cCode = NULL ? SQLEXEC ( nConnection, cSQL, "c_Abbrv" ) * All ABBRV table records are returned (since the table has < 1000 records)