| Pro*C/C++プログラマーズ・ガイド 12c リリース1(12.1) B71397-03 |
|
 前 |
 次 |
サンプル・コードでは3つのオブジェクト型が生成されます。budokaは武道家です。
Customer
Budoka
Location
さらに、次の2つの表が作成されます。
person_tab
customer_tab
次のSQLファイルnavdemo1.sqlでは、型と表が生成されてから、表に値が挿入されます。
connect scott/tiger
drop table customer_tab;
drop type customer;
drop table person_tab;
drop type budoka;
drop type location;
create type location as object (
num number,
street varchar2(60),
city varchar2(30),
state char(2),
zip char(10)
);
/
create type budoka as object (
lastname varchar2(20),
firstname varchar(20),
birthdate date,
age int,
addr location
);
/
create table person_tab of budoka;
create type customer as object (
account_number varchar(20),
aperson ref budoka
);
/
create table customer_tab of customer;
insert into person_tab values (
budoka('Seagal', 'Steven', '14-FEB-1963', 34,
location(1825, 'Aikido Way', 'Los Angeles', 'CA', 45300)));
insert into person_tab values (
budoka('Norris', 'Chuck', '25-DEC-1952', 45,
location(291, 'Grant Avenue', 'Hollywood', 'CA', 21003)));
insert into person_tab values (
budoka('Wallace', 'Bill', '29-FEB-1944', 53,
location(874, 'Richmond Street', 'New York', 'NY', 45100)));
insert into person_tab values (
budoka('Van Damme', 'Jean Claude', '12-DEC-1964', 32,
location(12, 'Shugyo Blvd', 'Los Angeles', 'CA', 95100)));
insert into customer_tab
select 'AB123', ref(p)
from person_tab p where p.lastname = 'Seagal';
insert into customer_tab
select 'DD492', ref(p)
from person_tab p where p.lastname = 'Norris';
insert into customer_tab
select 'SM493', ref(p)
from person_tab p where p.lastname = 'Wallace';
insert into customer_tab
select 'AC493', ref(p)
from person_tab p where p.lastname = 'Van Damme';
commit work;
関連項目:
intypeファイルの書式の詳細は、OTTコマンドラインを参照してください。
例に使用したintypeファイルnavdemo1.typのリストを次に示します。
case=lower type location type budoka type customer
OTTが生成するヘッダー・ファイルnavdemo1.hは、#includeプリプロセッサ・ディレクティブの付いたプリコンパイラのコードにインクルードされます。
プリコンパイラ・コード内のコメントを読み込みます。プログラムは新しいbudokaオブジェクト(Jackie Chanのもの)を追加してから、customer_tab表のすべての顧客を表示します。
プリコンパイラ・ファイルnavdemo1.pcのリストを次に示します。
/*************************************************************************
*
* This is a simple Pro*C/C++ program designed to illustrate the
* Navigational access to objects in the object cache.
*
* To build the executable:
*
* 1. Execute the SQL script, navdemo1.sql in SQL*Plus
* 2. Run OTT: (The following command should appear on one line)
* ott intype=navdemo1.typ hfile=navdemo1.h outtype=navdemo1_o.typ
* code=c user=scott/tiger
* 3. Precompile using Pro*C/C++:
* proc navdemo1 intype=navdemo1_o.typ
* 4. Compile/Link (This step is platform specific)
*
*************************************************************************/
#include "navdemo1.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
void whoops(errcode, errtext, errtextlen)
int errcode;
char *errtext;
int errtextlen;
{
printf("ERROR! sqlcode=%d: text = %.*s", errcode, errtextlen, errtext);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_FAILURE);
}
void main()
{
char *uid = "scott/tiger";
/* The following types are generated by OTT and defined in navdemo1.h */
customer *cust_p; /* Pointer to customer object */
customer_ind *cust_ind; /* Pointer to indicator struct for customer */
customer_ref *cust_ref; /* Pointer to customer object reference */
budoka *budo_p; /* Pointer to budoka object */
budoka_ref *budo_ref; /* Pointer to budoka object reference */
budoka_ind *budo_ind; /* Pointer to indicator struct for budoka */
/* These are data declarations to be used to insert/retrieve object data */
VARCHAR acct[21];
struct { char lname[21], fname[21]; int age; } pers;
struct { int num; char street[61], city[31], state[3], zip[11]; } addr;
EXEC SQL WHENEVER SQLERROR DO whoops(
sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrm.sqlerrml);
EXEC SQL CONNECT :uid;
EXEC SQL ALLOCATE :budo_ref;
/* Create a new budoka object with an associated indicator
* variable returning a REF to that budoka as well.
*/
EXEC SQL OBJECT CREATE :budo_p:budo_ind TABLE PERSON_TAB
RETURNING REF INTO :budo_ref;
/* Create a new customer object with an associated indicator */
EXEC SQL OBJECT CREATE :cust_p:cust_ind TABLE CUSTOMER_TAB;
/* Set all budoka indicators to NOT NULL. We
* will be setting all attributes of the budoka.
*/
budo_ind->_atomic = budo_ind->lastname = budo_ind->firstname =
budo_ind->age = OCI_IND_NOTNULL;
/* We will also set all address attributes of the budoka */
budo_ind->addr._atomic = budo_ind->addr.num = budo_ind->addr.street =
budo_ind->addr.city = budo_ind->addr.state = budo_ind->addr.zip =
OCI_IND_NOTNULL;
/* All customer attributes will likewise be set */
cust_ind->_atomic = cust_ind->account_number = cust_ind->aperson =
OCI_IND_NOTNULL;
/* Set the default CHAR semantics to type 5 (STRING) */
EXEC ORACLE OPTION (char_map=string);
strcpy((char *)pers.lname, (char *)"Chan");
strcpy((char *)pers.fname, (char *)"Jackie");
pers.age = 38;
/* Convert native C types to OTS types */
EXEC SQL OBJECT SET lastname, firstname, age OF :budo_p TO :pers;
addr.num = 1893;
strcpy((char *)addr.street, (char *)"Rumble Street");
strcpy((char *)addr.city, (char *)"Bronx");
strcpy((char *)addr.state, (char *)"NY");
strcpy((char *)addr.zip, (char *)"92510");
/* Convert native C types to OTS types */
EXEC SQL OBJECT SET :budo_p->addr TO :addr;
acct.len = strlen(strcpy((char *)acct.arr, (char *)"FS926"));
/* Convert native C types to OTS types - Note also the REF type */
EXEC SQL OBJECT SET account_number, aperson OF :cust_p TO :acct, :budo_ref;
/* Mark as updated both the new customer and the budoka */
EXEC SQL OBJECT UPDATE :cust_p;
EXEC SQL OBJECT UPDATE :budo_p;
/* Now flush the changes to the server, effectively
* inserting the data into the respective tables.
*/
EXEC SQL OBJECT FLUSH :budo_p;
EXEC SQL OBJECT FLUSH :cust_p;
/* Associative access to the REFs from CUSTOMER_TAB */
EXEC SQL DECLARE ref_cur CURSOR FOR
SELECT REF(c) FROM customer_tab c;
EXEC SQL OPEN ref_cur;
printf("\n");
/* Allocate a REF to a customer for use in the following */
EXEC SQL ALLOCATE :cust_ref;
EXEC SQL WHENEVER NOT FOUND DO break;
while (1)
{
EXEC SQL FETCH ref_cur INTO :cust_ref;
/* Pin the customer REF, returning a pointer to a customer object */
EXEC SQL OBJECT DEREF :cust_ref INTO :cust_p:cust_ind;
/* Convert the OTS types to native C types */
EXEC SQL OBJECT GET account_number FROM :cust_p INTO :acct;
printf("Customer Account is %.*s\n", acct.len, (char *)acct.arr);
/* Pin the budoka REF, returning a pointer to a budoka object */
EXEC SQL OBJECT DEREF :cust_p->aperson INTO :budo_p:budo_ind;
/* Convert the OTS types to native C types */
EXEC SQL OBJECT GET lastname, firstname, age FROM :budo_p INTO :pers;
printf("Last Name: %s\nFirst Name: %s\nAge: %d\n",
pers.lname, pers.fname, pers.age);
/* Do the same for the address attributes as well */
EXEC SQL OBJECT GET :budo_p->addr INTO :addr;
printf("Address:\n");
printf(" Street: %d %s\n City: %s\n State: %s\n Zip: %s\n\n",
addr.num, addr.street, addr.city, addr.state, addr.zip);
/* Unpin the customer object and budoka objects */
EXEC SQL OBJECT RELEASE :cust_p;
EXEC SQL OBJECT RELEASE :budo_p;
}
EXEC SQL CLOSE ref_cur;
EXEC SQL WHENEVER NOT FOUND DO whoops(
sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrm.sqlerrml);
/* Associatively select the newly created customer object */
EXEC SQL SELECT VALUE(c) INTO :cust_p FROM customer_tab c
WHERE c.account_number = 'FS926';
/* Mark as deleted the new customer object */
EXEC SQL OBJECT DELETE :cust_p;
/* Flush the changes, effectively deleting the customer object */
EXEC SQL OBJECT FLUSH :cust_p;
/* Associatively select a REF to the newly created budoka object */
EXEC SQL SELECT REF(p) INTO :budo_ref FROM person_tab p
WHERE p.lastname = 'Chan';
/* Pin the budoka REF, returning a pointer to the budoka object */
EXEC SQL OBJECT DEREF :budo_ref INTO :budo_p;
/* Mark the new budoka object as deleted in the object cache */
EXEC SQL OBJECT DELETE :budo_p;
/* Flush the changes, effectively deleting the budoka object */
EXEC SQL OBJECT FLUSH :budo_p;
/* Finally, free all object cache memory and log off */
EXEC SQL OBJECT CACHE FREE ALL;
EXEC SQL COMMIT WORK RELEASE;
exit(EXIT_SUCCESS);
}
プログラムの実行結果は次のとおりです。
Customer Account is AB123 Last Name: Seagal First Name: Steven Birthdate: 02-14-1963 Age: 34 Address: Street: 1825 Aikido Way City: Los Angeles State: CA Zip: 45300 Customer Account is DD492 Last Name: Norris First Name: Chuck Birthdate: 12-25-1952 Age: 45 Address: Street: 291 Grant Avenue City: Hollywood State: CA Zip: 21003 Customer Account is SM493 Last Name: Wallace First Name: Bill Birthdate: 02-29-1944 Age: 53 Address: Street: 874 Richmond Street City: New York State: NY Zip: 45100 Customer Account is AC493 Last Name: Van Damme First Name: Jean Claude Birthdate: 12-12-1965 Age: 32 Address: Street: 12 Shugyo Blvd City: Los Angeles State: CA Zip: 95100 Customer Account is FS926 Last Name: Chan First Name: Jackie Birthdate: 10-10-1959 Age: 38 Address: Street: 1893 Rumble Street City: Bronx State: NY Zip: 92510
