| Pro*C/C++プログラマーズ・ガイド 12c リリース1(12.1) B71397-03 |
|
 前 |
 次 |
Person_t
Person_tのサブタイプとしてEmployee_t
Person_tのサブタイプとしてStudent_t
Student_tのサブタイプとしてPartTimeStudent_t
また、次の表も作成されます。
Person_tとそのサブタイプのオブジェクトを保持するためのperson_tab
次のSQLファイルinhdemo1.sqlでは、オブジェクト型と表が生成されてから、表に値が挿入されます。
connect scott/tiger; rem ** Always drop your objects in reverse dependency order drop table person_tab; drop type PartTimeStudent_t; drop type Student_t; drop type Employee_t; drop type Person_t; rem ** Create the TYPES, TYPED TABLES and TABLES we need rem ** Create a Person_t ADT CREATE TYPE Person_t AS OBJECT ( ssn NUMBER, name VARCHAR2(30), address VARCHAR2(100)) NOT FINAL; / rem ** Create a Person_t subtype Employee_t CREATE TYPE Employee_t UNDER Person_t ( empid NUMBER, mgr VARCHAR2(30)); / rem ** Create a Person_t subtype Student_t CREATE TYPE Student_t UNDER Person_t ( deptid NUMBER, major VARCHAR2(30)) NOT FINAL; / rem ** Create a Student_t subtype PartTimeStudent_t CREATE TYPE PartTimeStudent_t UNDER Student_t ( numhours NUMBER); / rem ** Create a typed table for person_t objects CREATE table person_tab of person_t; rem ** Insert 2 Employee_t objects into the person_t typed table insert into person_tab values (Employee_t(123456, 'Alison Laurence', '100 Geary Street, San Francisco, CA 94013', 1001, 'CEO')); insert into person_tab values (Employee_t(234567, 'William Bates', '123 Main Street, Anytown, WA 97818', 1002,'CFO')); rem ** Insert 2 Student_t objects into the person_t typed table insert into person_tab values (Student_t(20001, 'Van Gates', '1825 Aikido Way, Los Angeles, CA, 45300', 20, 'English')); insert into person_tab values (Student_t(20002, 'Bill Wallace', '12 Shugyo Blvd, Los Angeles, CA, 95100', 30, 'Computer Science')); rem ** Insert 1 PartTimeStudent_t object into the person_t typed table insert into person_tab values (PartTimeStudent_t(20003, 'Jean Claude', '874 Richmond Street, New York, NY 45100', 40, 'Music',20)); commit;
例で使用したintypeファイルinhdemo1.typのリストを次に示します。
case=same type person_t type employee_t type student_t type parttimestudent_t
プリコンパイラ・ファイルinhdemo1.pcのリストを次に示します。
/*****************************************************************************
*
* This is a simple Pro*C/C++ program designed to illustrate how to
* access type inheritance objects.
*
* To build the executable:
*
* 1. Execute the SQL script, inhdemo1.sql in SQL*Plus to create:
* - 4 object types person_t, employee_t as a subtype of person_t,
* student_t as a subtype of person_t and parttimestudent_t as
* a subtype of student_t.
* - 1 typed table person_tab to hold "person_t" and its subtype objects
*
* 2. Run OTT: (The following command should appear on one line)
* ott intype=inhdemo1.typ hfile=inhdemo1.h outtype=out.typ
* code=c userid=scott/tiger
*
* 3. Precompile using Pro*C/C++:
* proc inhdemo1 intype=out.typ
* 4. Compile/Link (This step is platform specific)
*
****************************************************************************/
/* Include files */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlda.h>
#include <sqlca.h> /* SQL Communications Area */
#include <sql2oci.h> /* SQLLIB interoperability routines for OCI8 */
#include "inhdemo1.h" /* OTT-generated header with C typedefs for the */
/* database types "person" and "address" */
/* Macros */
#define ARRAY_SIZE 10
#define NAME_LENGTH 31
#define ADDR_LENGTH 101
/* Global variables */
char *uid="scott/tiger";
int i;
int count;
VARCHAR dynstmt[100];
main()
{
printf("\n*** STARTING OBJECT TYPE INHERITANCE DEMO ***\n");
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");
EXEC SQL connect :uid;
printf("Connected successfully.\n");
exec sql select count(*) into :count from person_tab;
printf("\nThere are %d entries in table person_tab.\n", count);
do_fetch_all(); /* Fetch person_t objects */
do_fetch_employee(); /* Fetch employee_t objects */
do_fetch_student(); /* Fetch only student_t objects */
do_fetch_parttimestudent(); /* Fetch parttimestuden_t objects */
do_fetch_student_employee(); /* Fetch student_t and employee_t objects */
printf("\nFetching only student_t objects with dynamic sql:\n");
strcpy((char *)dynstmt.arr,
"SELECT value(p) from person_tab p where value(p) is of (only student_t)");
do_dynamic_fetch(); /* Fetch student_t object with dynamic sql */
printf("\nFetching student_t and its subtype objects with dynamic sql:\n");
strcpy((char *)dynstmt.arr,
"SELECT treat(value(p) as student_t) from person_tab p where value(p) is
of(student_t)");
do_dynamic_fetch(); /* Fetch student_t object with dynamic sql */
printf("\n*** END OF OBJECT TYPE INHERITANCE DEMO ***\n");
exit(EXIT_SUCCESS);
}
void printPerson(person)
person_t *person;
{
int writtenSSN=-1;
text writtenName[NAME_LENGTH];
text writtenAddr[ADDR_LENGTH];
EXEC SQL OBJECT GET SSN, NAME, ADDRESS FROM :person INTO
:writtenSSN, :writtenName, :writtenAddr;
printf("\nSSN=%10d\nNAME=%s\nAddr=%s\n", writtenSSN, writtenName,
writtenAddr);
}
void printEmployee(employee)
employee_t *employee;
{
int writtenID=-1;
text writtenMgr[NAME_LENGTH];
printPerson(employee);
EXEC SQL OBJECT GET EMPID, MGR FROM :employee INTO :writtenID, :writtenMgr;
printf("EMPID=%10d\nMGR=%s\n", writtenID, writtenMgr);
}
void printStudent(student)
student_t *student;
{
int writtendeptid=-1;
text writtenMajor[NAME_LENGTH];
printPerson(student);
EXEC SQL OBJECT GET DEPTID, MAJOR FROM :student INTO :writtendeptid, :writtenMajor;
printf("DEPTID=%10d\nMAJOR=%s\n", writtendeptid, writtenMajor);
}
void printPartTimeStudent(parttimes)
parttimestudent_t *parttimes;
{
int written_numhours=-1;
printStudent(parttimes);
EXEC SQL OBJECT GET NUMHOURS FROM :parttimes INTO :written_numhours;
printf("NUMHOURS=%10d\n", written_numhours);
}
/* Declare error handling function. */
sql_error(msg)
char *msg;
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
/*****************************************************************************
* The following function shows how to select person_t objects
****************************************************************************/
do_fetch_all()
{
person_t *personArray[ARRAY_SIZE];
person_t_ind *personArray_ind[ARRAY_SIZE];
printf("\nFetching person_t objects:\n");
exec sql declare c1 cursor for
select value(p) from person_tab p;
exec sql allocate :personArray:personArray_ind;
exec sql open c1;
exec sql whenever not found goto :done;
while(sqlca.sqlcode==0)
{
exec sql fetch c1 into :personArray:personArray_ind;
if (sqlca.sqlcode == 1403) goto done;
for (i=0; i < ARRAY_SIZE; i++ )
printPerson(personArray[i]);
}
done:
for (i=0; i < sqlca.sqlerrd[2] % ARRAY_SIZE; i++)
printPerson(personArray[i]);
printf("Total number of person_t objects fetched: %d.\n",
sqlca.sqlerrd[2]);
exec sql close c1;
exec sql free :personArray:personArray_ind;
}
/*****************************************************************************
* The following function shows how to select person_t subtype employee_t
* objects
****************************************************************************/
do_fetch_employee()
{
employee_t *empArray[ARRAY_SIZE];
employee_t_ind *empArray_ind[ARRAY_SIZE];
printf("\nFetching employee_t objects:\n");
exec sql allocate :empArray:empArray_ind;
exec sql declare c2 cursor for
select value(p) from person_tab p
where value(p) is of (employee_t);
exec sql open c2;
exec sql whenever not found goto :done_emp;
while(sqlca.sqlcode==0)
{
exec sql fetch c2 into :empArray:empArray_ind;
for (i=0; i < ARRAY_SIZE; i++ )
printEmployee(empArray[i]);
}
done_emp:
for (i=0; i < sqlca.sqlerrd[2] % ARRAY_SIZE; i++)
printEmployee(empArray[i]);
printf("Total number of employee_t objects fetched: %d.\n",
sqlca.sqlerrd[2]);
exec sql close c2;
exec sql free :empArray:empArray_ind;
}
/*****************************************************************************
* The following function shows how to select person_t subtype student_t
* objects
****************************************************************************/
do_fetch_student()
{
student_t *studentArray[ARRAY_SIZE];
student_t_ind *studentArray_ind[ARRAY_SIZE];
printf("\nFetching student_t objects:\n");
exec sql declare c3 cursor for
select value(p) from person_tab p
where value(p) is of (student_t);
exec sql allocate :studentArray:studentArray_ind;
exec sql open c3;
exec sql whenever not found goto :done_student;
for (;;)
{
exec sql fetch c3 into :studentArray:studentArray_ind;
for (i=0; i < ARRAY_SIZE; i++ )
printStudent(studentArray[i]);
}
done_student:
for (i=0; i < sqlca.sqlerrd[2] % ARRAY_SIZE; i++)
printStudent(studentArray[i]);
printf("Total number of student_t objects fetched: %d.\n",
sqlca.sqlerrd[2]);
exec sql close c3;
exec sql free :studentArray:studentArray_ind;
}
/*****************************************************************************
* The following function shows how to select student_t subtype
* parttimestudent objects
****************************************************************************/
do_fetch_parttimestudent()
{
parttimestudent_t *parttimestudentArrayArray[ARRAY_SIZE];
parttimestudent_t_ind *parttimestudentArrayArray_ind[ARRAY_SIZE];
printf("\nFetching parttimestudent_t objects:\n");
exec sql declare c4 cursor for
select value(p) from person_tab p
where value(p) is of (parttimestudent_t);
exec sql allocate :parttimestudentArrayArray:parttimestudentArrayArray_ind;
exec sql open c4;
exec sql whenever not found goto :done_parttimestudent;
while(sqlca.sqlcode==0)
{
exec sql fetch c4 into :parttimestudentArrayArray:parttimestudentArrayArray_ind;
for (i=0; i < ARRAY_SIZE; i++ )
printPartTimeStudent(parttimestudentArrayArray[i]);
}
done_parttimestudent:
for (i=0; i < sqlca.sqlerrd[2] % ARRAY_SIZE; i++)
printPartTimeStudent(parttimestudentArrayArray[i]);
printf("Total number of parttimestudent_t objects fetched: %d.\n",
sqlca.sqlerrd[2]);
exec sql close c4;
exec sql free :parttimestudentArrayArray:parttimestudentArrayArray_ind;
}
/*****************************************************************************
* The following function shows how to select person_t subtypes student_t
* and employee_t objects
****************************************************************************/
do_fetch_student_employee()
{
person_t *personArray[ARRAY_SIZE];
person_t_ind *personArray_ind[ARRAY_SIZE];
printf("\nFetching only student_t and employee_t objects:\n");
exec sql declare c5 cursor for
select value(p) from person_tab p
where value(p) is of (only student_t, employee_t);
exec sql allocate :personArray:personArray_ind;
exec sql open c5;
exec sql whenever not found goto :done_student_employee;
while(sqlca.sqlcode==0)
{
exec sql fetch c5 into :personArray:personArray_ind;
for (i=0; i < ARRAY_SIZE; i++ )
printPerson(personArray[i]);
}
done_student_employee:
for (i=0; i < sqlca.sqlerrd[2] % ARRAY_SIZE; i++)
printPerson(personArray[i]);
printf("Total number of stuent_t and employee_t objects fetched: %d.\n",
sqlca.sqlerrd[2]);
exec sql close c5;
exec sql free :personArray:personArray_ind;
}
/*****************************************************************************
* The following function shows how to select person_t subtype student_t
* objects using dynamic sql.
****************************************************************************/
do_dynamic_fetch()
{
student_t *student;
student_t_ind *student_ind;
exec sql allocate :student:student_ind;
dynstmt.len = (unsigned short)strlen((char *)dynstmt.arr);
EXEC SQL PREPARE S FROM :dynstmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
exec sql whenever not found do break;
for (;;)
{
EXEC SQL FETCH C INTO :student:student_ind;
printStudent(student);
}
printf("\nQuery returned %d row%s.\n", sqlca.sqlerrd[2],
(sqlca.sqlerrd[2] == 1) ? "" : "s");
EXEC SQL CLOSE C;
exec sql free :student:student_ind;
}
