| Pro*C/C++プログラマーズ・ガイド 12c リリース1(12.1) B71397-03 |
|
 前 |
 次 |
COLLECTION SQL文を使用するSQLおよびPro*C/C++コードの例を示します。
scott/tigerで接続し、SQLを使用して次の型を作成するとします。
CREATE TYPE employee AS OBJECT ( name VARCHAR2(10), salary NUMBER(8,2) ) ; CREATE TYPE employees AS VARRAY(15) OF employee ; CREATE TYPE department AS OBJECT ( name VARCHAR2(15), team employees ) ;
Object Type Translator(OTT)によって、ヘッダー・ファイルが生成されます。OTTの入力として、次の入力ファイル(ファイル名in.typ)が使用されます。
case=lower type employee type employees type department
次のコマンドによりヘッダー・ファイルが生成されます。
ott intype=in.typ outtype=out.typ hfile=example.h user=scott/tiger code=c
このヘッダー・ファイルexample.hは、OTTにより生成されます。
#ifndef EXAMPLE_ORACLE
# define EXAMPLE_ORACLE
#ifndef OCI_ORACLE
# include <oci.h>
#endif
typedef OCIRef employee_ref ;
typedef OCIArray employees ;
typedef OCIRef department_ref ;
struct employee
{
OCIString * name ;
OCINumber salary ;
} ;
typedef struct employee employee ;
struct employee_ind
{
OCIInd _atomic ;
OCIInd name ;
OCIInd salary ;
} ;
typedef struct employee_ind employee_ind ;
struct department_ind
{
OCIInd _atomic ;
OCIInd name ;
OCIInd team ;
} ;
typedef struct department_ind department_ind ;
#endif
注意:
ファイルoci.hには、OCIArrayを定義したtypedefを持つorl.hが格納されています。typedefは、次の「typedef OCIColl OCIArray;」のようになります。OCICollは、汎用コレクションを表す不透明な構造体です。
次の1列が含まれる簡単な表を作成します。
CREATE TABLE division ( subdivision department ) ;
この表に複数の行を挿入します。
INSERT INTO division (subdivision) VALUES
(department('Accounting',
employees(employee('John', 75000),
employee('Jane', 75000)))
);
INSERT INTO division (subdivision) VALUES
(department('Development',
employees(employee('Peter', 80000),
employee('Paula', 80000)))
) ;
INSERT INTO division (subdivision) VALUES
(department('Research',
employees(employee('Albert', 95000),
employee('Alison', 95000)))
);
これらの型定義および表の情報を、次の例で使用します。
この例では、オブジェクトのコレクション属性から値を取り出し、簡単な修正を加え、コレクションに戻します。
まず、example.hをインクルードし、オブジェクト型の変数を宣言する必要があります。
#include <example.h> department *dept_p ;
「開発」部門を部署表から選択します。
EXEC SQL ALLOCATE :dept_p ;
EXEC SQL SELECT subdivision INTO :dept_p
FROM division WHERE name = 'Development' ;
employeeオブジェクト型のチームVARRAYの変数および単一のemployeeオブジェクトを表す変数が必要です。また、開発部門のすべてのメンバーの給料を昇給するので、そのための変数が必要です。
employees *emp_array ;
employee *emp_p ;
double salary ;
作成したVARRAY Cコレクションおよびemployeeオブジェクト記述子に、ALLOCATEを実行する必要があります。ナビゲーショナル・アクセス用インタフェースを使用して、オブジェクトから実際のコレクションを取り出します。
EXEC SQL ALLOCATE :emp_array ;
EXEC SQL ALLOCATE :emp_p ;
EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;
ループを使用し、VARRAY要素に対して処理を繰り返します。WHENEVERディレクティブを使用してループの終了を制御します。
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
まず、コレクションから変更する要素を取り出します。実際の要素型は、employeeオブジェクトです。
EXEC SQL COLLECTION GET :emp_array INTO :emp_p ;
実際のオブジェクト要素を取り出したため、既存のナビゲーショナル・アクセス用インタフェースを使用して属性の値を変更します。この例では、全員の給料を10%増やします。
EXEC SQL OBJECT GET salary FROM :emp_p INTO :salary ;
salary += (salary * .10) ;
EXEC SQL OBJECT SET salary OF :emp_p TO :salary ;
変更が終わると、コレクションに現在含まれているオブジェクト要素の属性の値を更新できます。
EXEC SQL COLLECTION SET :emp_array TO :emp_p ;
}
すべてのコレクション要素に対して処理を繰り返した後に、そのコレクションを含むオブジェクトが格納されている表の列を更新する必要があります。
EXEC SQL UPDATE division SET subdivision = :dept_p ;
次に、FREEを実行してすべてのリソースを解放し、COMMITを実行してこの一連の操作を終了します。
EXEC SQL FREE :emp_array ;
EXEC SQL FREE :emp_p ;
EXEC SQL FREE :dept_p ;
EXEC SQL COMMIT WORK ;
簡単な例ですが、必要な処理はすべて含まれています。ナビゲーショナルOBJECT GET文を応用して、コレクション属性をオブジェクトから取り出し、Cコレクション記述子に格納する方法は、明確に説明されています。さらに、そのCコレクション記述子を使用して、実際のコレクションの要素を取出しおよび更新を行うための、新しいCOLLECTION GET文およびSET文の使用方法について説明しました。コレクション・オブジェクト要素型の属性値の変更には、ナビゲーショナル・アクセス用インタフェースを使用しています。
この例では、DESCRIBE SQL文の使用方法を示します。任意のコレクションについての基本情報を検索します。
まず、例で使用されているヘッダー・ファイル、オブジェクト・ポインタおよびSQLコレクション記述子が必要です。
#include <example.h> department *dept_p ;
前と同様に、オブジェクト・ポインタのALLOCATEを実行し、表からオブジェクトを取り出します。
EXEC SQL ALLOCATE :dept_p ; EXEC SQL SELECT subdivision INTO :dept_p FROM division WHERE name = 'Research' ;
検索するコレクション属性情報を格納するPro*C/C++変数を宣言します。
int size ; char type_name[9] ; employees *emp_array ;
コレクション記述子を割り当て、ナビゲーショナル・アクセス用インタフェースを使用して、オブジェクトからコレクション属性を取り出します。
EXEC SQL ALLOCATE :emp_array ; EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;
最後に、新しいCOLLECTION DESCRIBE文を使用して目的のコレクション属性情報を抽出します。
EXEC SQL COLLECTION DESCRIBE :emp_array GET SIZE, TYPE_NAME INTO :size, :type_name ;
注意:
この例のように、目的のコレクション属性名と同じホスト変数名を使用できます。
型employeesは、オブジェクトemployeeのVARRAYのため、型名を抽出できます。
DESCRIBEが正常に実行されると、SIZEの値は2(このコレクション・インスタンスResearchの場合、2つの要素、AlbertおよびAlisonが存在します)になります。type_name変数は「EMPLOYEE\0」(デフォルトではCHARZ)になります。
SQL記述子およびオブジェクト・ポインタを使用した処理が終了した後に、FREEを実行してリソースを解放します。
EXEC SQL FREE :emp_array ; EXEC SQL FREE :dept_p ;
この例では、Cコレクション記述子の参照先の基礎となるコレクションについて、記述子から情報を抽出するために使用するDESCRIBEのメカニズムについて説明しました。
開発の従業員の給料を昇給するかわりに、GETおよびSETの例のように、部署全体の給料を昇給します。
前の例と同様に、Object Type Translator(OTT)で生成されたサンプル・ヘッダー・ファイルなどを処理します。ただし、今回は、カーソルを使用して部署の部門ごとに、一度に1部門ずつ繰り返し実行します。
#include <example.h> EXEC SQL DECLARE c CURSOR FOR SELECT subdivision FROM division ;
データを操作するローカル変数が必要になります。
department *dept_p ; employees *emp_array ; employee *emp_p ; double salary ; int size ;
オブジェクト変数およびコレクション変数を使用する前に、次のALLOCATE文を使用して初期化する必要があります。
EXEC SQL ALLOCATE :emp_array ; EXEC SQL ALLOCATE :emp_p ;
カーソルを使用して、部署のすべての部門に対して繰り返し処理を行うことができるようになりました。
EXEC SQL OPEN c ;
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
EXEC SQL FETCH c INTO :dept_p ;
ここで、部門オブジェクトを使用します。ナビゲーショナル・アクセス用インタフェースを使用して、部門からVARRAY属性teamを抽出する必要があります。
EXEC SQL OBJECT GET team FROM :dept_p INTO :emp_array ;
コレクションへの参照を開始する前に、RESET文を使用して、スライスのエンドポイントが現行のコレクション・インスタンスの始点(前のインスタンスの最後ではありません)に設定されていることを確認してください。
EXEC SQL COLLECTION RESET :emp_array ;
VARRAYのすべての要素を繰り返し処理し、前と同様に給料を更新します。このループの場合も、既存のWHENEVERディレクティブは引き続き有効です。
while (TRUE)
{
EXEC SQL COLLECTION GET :emp_array INTO :emp_p ;
EXEC SQL OBJECT GET salary FROM :emp_p INTO :salary ;
salary += (salary * .05) ;
EXEC SQL OBJECT SET salary OF :emp_p TO :salary ;
処理が完了すると、コレクション属性を更新します。
EXEC SQL COLLECTION SET :emp_array TO :emp_p ;
}
前の例と同様に、変更が完了したコレクションを含むオブジェクトが格納された表の列を更新する必要があります。
EXEC SQL UPDATE division SET subdivision = :dept_p ;
}
ループが終了すると、処理は終了です。FREEを実行してすべてのリソースを解放し、COMMITで作業内容を送信します。
EXEC SQL CLOSE c ; EXEC SQL FREE :emp_p ; EXEC SQL FREE :emp_array ; EXEC SQL FREE :dept_p ; EXEC SQL COMMIT WORK ;
この例では、同じコレクション型の異なるインスタンスに対して、ALLOCATEで割り当てられたコレクション記述子の再利用方法について説明しています。COLLECTION RESET文により、スライスのエンドポイントが必ず現在のコレクション・インスタンスの最初にリセットされます。エンドポイントは、前のコレクション・インスタンスの参照中に移動された後は、それまでの位置に残りません。
COLLECTION RESET文をこのように使用すると、アプリケーション開発者は同じコレクション型の新しいインスタンスを作成するたびに、コレクション記述子に対して明示的にFREEおよびALLOCATEを実行する必要がなくなります。
次のプログラムcoldemo1.pcは、demoディレクトリにあります。
この例では、Pro*Cクライアントからコレクション型データベース列を操作する3種類の方法について説明しています。この例では、NESTED TABLEを使用していますが、VARRAYにも適用できます。
この例では、SQL*Plusファイル、coldemo1.sqlを使用して、挿入データおよびcalidata.sqlに格納されているデータを使用する表をセットアップします。
REM ************************************************************************ REM ** This is a SQL*Plus script to demonstrate collection manipulation REM ** in Pro*C/C++. REM ** Run this script before executing OTT for the coldemo1.pc program REM ************************************************************************ connect scott/tiger; set serveroutput on; REM Make sure database has no old version of the table and types DROP TABLE county_tbl; DROP TYPE citytbl_t; DROP TYPE city_t; REM ABSTRACTION: REM The counties table contains census information about each of the REM counties in a particular U.S. state (California is used here). REM Each county has a name, and a collection of cities. REM Each city has a name and a population. REM IMPLEMENTATION: REM Our implementation follows this abstraction REM Each city is implemented as a "city" OBJECT, and the REM collection of cities in the county is implemented using REM a NESTED TABLE of "city" OBJECTS. CREATE TYPE city_t AS OBJECT (name CHAR(30), population NUMBER); / CREATE TYPE citytbl_t AS TABLE OF city_t; / CREATE TABLE county_tbl (name CHAR(30), cities citytbl_t) NESTED TABLE cities STORE AS citytbl_t_tbl; REM Load the counties table with data. This example uses estimates of REM California demographics from Janurary 1, 1996. @calidata.sql; REM Commit to save COMMIT;
表の設定方法およびこのプログラムでデモンストレーションする機能の説明については、次のプログラムの最初のコメントを参照してください。
/* ***************************************** */
/* Demo program for Collections in Pro*C */
/* ***************************************** */
/*****************************************************************************
In SQL*Plus, run the SQL script coldemo1.sql to create:
- 2 types: city_t (OBJECT) and citytbl_t (NESTED TABLE)
- 1 relational table county_tbl which contains a citytbl_t nested table
Next, run the Object Type Translator (OTT) to generate typedefs of C structs
corresponding to the city_t and citytbl_t types in the databases:
ott int=coldemo1.typ outt=out.typ hfile=coldemo1.h code=c user=scott/tiger
Then, run the Pro*C/C++ Precompiler as follows:
proc coldemo1 intype=out.typ
Finally, link the generated code using the Pro*C Makefile:
(Compiling and Linking applications is a platform dependent step).
****************************************************************************
Scenario:
We consider the example of a database used to store census
information for the state of California. The database has a table
representing the counties of California. Each county has a name
and a collection of cities. Each city has a name and a population.
Application Overview:
This example demonstrates three ways for the Pro*C client to
navigate through collection-typed database columns. Although the
examples presented use nested tables, they also apply to varrays.
Collections-specific functionality is demonstrated in three
different functions, as described in the following section.
PrintCounties shows examples of
* Declaring collection-typed host variables and arrays
* Allocating and freeing collection-typed host variables
* Using SQL to load a collection-typed host variable
* Using indicators for collection-typed host variables
* Using OCI to examine a collection-typed host variables
PrintCounty shows examples of
* Binding a ref cursor host variable to a nested table column
* Allocating and freeing a ref cursor
* Using the SQL "CURSOR" clause
CountyPopulation shows examples of
* Binding a "DECLARED" cursor to a nested table column
* Using the SQL "THE" clause
****************************************************************************/
/* Include files */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h> /* SQL Communications Area */
#include <coldemo1.h> /* OTT-generated header with C typedefs for the */
/* database types city_t and citytbl_t */
#ifndef EXIT_SUCCESS
# define EXIT_SUCCESS 0
#endif
#ifndef EXIT_FAILURE
# define EXIT_FAILURE 1
#endif
#define CITY_NAME_LEN 30
#define COUNTY_NAME_LEN 30
#define MAX_COUNTIES 60
/* Function prototypes */
#if defined(__STDC__)
void OptionLoop( void );
boolean GetCountyName( char *countyName );
void PrintCounties( void );
long CountyPopulation( CONST char *countyName );
void PrintCounty( CONST char *countyName );
void PrintSQLError( void );
void PrintCountyHeader( CONST char *county );
void PrintCity( city_t *city );
#else
void OptionLoop();
boolean GetCountyName(/*_ char *countyName _*/);
void PrintCounties();
long CountyPopulation(/*_ CONST char *countyName _*/);
void PrintCounty(/*_ CONST char *countyName _*/);
void PrintSQLError(/*_ void _*/);
void PrintCountyHeader(/*_ CONST char *county _*/);
void PrintCity(/*_ city_t *city _*/);
#endif
/*
* NAME
* main
* COLLECTION FEATURES
* none
*/
int main()
{
char * uid = "scott/tiger";
EXEC SQL WHENEVER SQLERROR DO PrintSQLError();
printf("\nPro*Census: Release California - Jan 1 1996.\n");
EXEC SQL CONNECT :uid;
OptionLoop();
printf("\nGoodbye\n\n");
EXEC SQL ROLLBACK RELEASE;
return(EXIT_SUCCESS);
}
/*
* NAME
* OptionLoop
* DESCRIPTION
* A command dispatch routine.
* COLLECTION FEATURES
* none
*/
void OptionLoop()
{
char choice[30];
boolean done = FALSE;
char countyName[COUNTY_NAME_LEN + 1];
while (!done)
{
printf("\nPro*Census options:\n");
printf("\tlist information for (A)ll counties\n");
printf("\tlist information for one (C)ounty\n");
printf("\tlist (P)opulation total for one county\n");
printf("\t(Q)uit\n");
printf("Choice? ");
fgets(choice, 30, stdin);
switch(toupper(choice[0]))
{
case 'A':
PrintCounties();
break;
case 'C':
if (GetCountyName(countyName))
PrintCounty(countyName);
break;
case 'P':
if (GetCountyName(countyName))
printf("\nPopulation for %s county: %ld\n",
countyName, CountyPopulation(countyName));
break;
case 'Q':
done = TRUE;
break;
default:
break;
}
}
}
/*
* NAME
* GetCountyName
* DESCRIPTION
* Fills the passed buffer with a client-supplied county name.
* Returns TRUE if the county is in the database, and FALSE otherwise.
* COLLECTION FEATURES
* none
*/
boolean GetCountyName(countyName)
char *countyName;
{
int count;
int i;
printf("County name? ");
fgets(countyName, COUNTY_NAME_LEN + 1, stdin);
/* Convert the name to uppercase and remove the trailing '\n' */
for (i = 0; countyName[i] != '\0'; i++)
{
countyName[i] = (char)toupper(countyName[i]);
if (countyName[i] == '\n') countyName[i] = '\0';
}
EXEC SQL SELECT COUNT(*) INTO :count
FROM county_tbl WHERE name = :countyName;
if (count != 1)
{
printf("\nUnable to find %s county.\n", countyName);
return FALSE;
}
else
return TRUE;
}
/*
* NAME
* PrintCounties
* DESCRIPTION
* Prints the population and name of each city of every county
* in the database.
* COLLECTION FEATURES
* The following features correspond to the inline commented numbers
* 1) Host variables for collection-typed objects are declared using
* OTT-generated types. Both array and scalar declarations are allowed.
* Scalar declarations must be of type pointer-to-collection-type, and
* array declarations must of type array-of-pointer-to-collection-type.
* 2) SQL ALLOCATE should be used to allocate space for the collection.
* SQL FREE should be used to free the memory once the collection is
* no longer needed. The host variable being allocated or free'd
* can be either array or scalar.
* 3) SQL is used to load into or store from collection-typed host variables
* and arrays. No special syntax is needed.
* 4) The type of an indicator variable for a collection is OCIInd.
* An indicators for a collections is declared and used just like
* an indicator for an int or string.
* 5) The COLLECTION GET Interface is used to access and manipulate the
* contents of collection-typed host variables. Each member of the
* collection used here has type city_t, as generated by OTT.
*/
void PrintCounties()
{
citytbl_t *cityTable[MAX_COUNTIES]; /* 1 */
OCIInd cityInd[MAX_COUNTIES]; /* 4 */
char county[MAX_COUNTIES][COUNTY_NAME_LEN + 1];
int i, numCounties;
city_t *city;
EXEC SQL ALLOCATE :cityTable; /* 2 */
EXEC SQL ALLOCATE :city;
EXEC SQL SELECT name, cities
INTO :county, :cityTable:cityInd FROM county_tbl; /* 3, 4 */
numCounties = sqlca.sqlerrd[2];
for (i = 0; i < numCounties; i++)
{
if (cityInd[i] == OCI_IND_NULL) /* 4 */
{
printf("Unexpected NULL city table for %s county\n", county[i]);
}
else
{ /* 5 */
PrintCountyHeader(county[i]);
EXEC SQL WHENEVER NOT FOUND DO break;
while (TRUE)
{
EXEC SQL COLLECTION GET :cityTable[i] INTO :city;
PrintCity(city);
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
}
}
EXEC SQL FREE :city;
EXEC SQL FREE :cityTable; /* 2 */
}
/*
* NAME
* PrintCountyHeader
* COLLECTION FEATURES
* none
*/
void PrintCountyHeader(county)
CONST char *county;
{
printf("\nCOUNTY: %s\n", county);
}
/*
* NAME
* PrintCity
* COLLECTION FEATURES
* none
*/
void PrintCity(city)
city_t *city;
{
varchar newCITY[CITY_NAME_LEN];
int newPOP;
EXEC SQL OBJECT GET NAME, POPULATION from :city INTO :newCITY, :newPOP;
printf("CITY: %.*s POP: %d\n", CITY_NAME_LEN, newCITY.arr, newPOP);
}
/*
* NAME
* PrintCounty
* DESCRIPTION
* Prints the population and name of each city in a particular county.
* COLLECTION FEATURES
* The following features correspond to the inline commented numbers
* 1) A ref cursor host variable may be used to scroll through the
* rows of a collection.
* 2) Use SQL ALLOCATE/FREE to create and destroy the ref cursor.
* 3) The "CURSOR" clause in SQL can be used to load a ref cursor
* host variable. In such a case, the SELECT ... INTO does an
* implicit "OPEN" of the ref cursor.
* IMPLEMENTATION NOTES
* In the case of SQL SELECT statements which contain an embedded
* CURSOR(...) clause, the Pro*C "select_error" flag must be "no"
* to prevent cancellation of the parent cursor.
*/
void PrintCounty(countyName)
CONST char *countyName;
{
sql_cursor cityCursor; /* 1 */
city_t *city;
EXEC SQL ALLOCATE :cityCursor; /* 2 */
EXEC SQL ALLOCATE :city;
EXEC ORACLE OPTION(select_error=no);
EXEC SQL SELECT
CURSOR(SELECT VALUE(c) FROM TABLE(county_tbl.cities) c)
INTO :cityCursor
FROM county_tbl
WHERE county_tbl.name = :countyName; /* 3 */
EXEC ORACLE OPTION(select_error=yes);
PrintCountyHeader(countyName);
EXEC SQL WHENEVER NOT FOUND DO break;
while (TRUE)
{
EXEC SQL FETCH :cityCursor INTO :city;
PrintCity(city);
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE :cityCursor;
EXEC SQL FREE :cityCursor; /* 2 */
EXEC SQL FREE :city;
}
/*
* NAME
* CountyPopulation
* DESCRIPTION
* Returns the number of people living in a particular county.
* COLLECTION FEATURES
* The following features correspond to the inline commented numbers
* 1) A "DECLARED" cursor may be used to scroll through the
* rows of a collection.
* 2) The "THE" clause in SQL is used to convert a single nested-table
* column into a table.
*/
long CountyPopulation(countyName)
CONST char *countyName;
{
long population;
long populationTotal = 0;
EXEC SQL DECLARE cityCursor CURSOR FOR
SELECT c.population
FROM THE(SELECT cities FROM county_tbl
WHERE name = :countyName) AS c; /* 1, 2 */
EXEC SQL OPEN cityCursor;
EXEC SQL WHENEVER NOT FOUND DO break;
while (TRUE)
{
EXEC SQL FETCH cityCursor INTO :population;
populationTotal += population;
}
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CLOSE cityCursor;
return populationTotal;
}
/*
* NAME
* PrintSQLError
* DESCRIPTION
* Prints an error message using info in sqlca and calls exit.
* COLLECTION FEATURES
* none
*/
void PrintSQLError()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("SQL error occurred...\n");
printf("%.*s\n", (int)sqlca.sqlerrm.sqlerrml,
(CONST char *)sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
