Oracle OCI-operated UDT-related learning

1. Oracle Data type

Oracle's data types are as follows

String type char nchar varchar2 NVARCHAR2

numeric type int number integer smallint

Day Type Date Timestamp

Custom type Create type

Here the UDT represents the Oracle custom type, the syntax is as follows

CREATE type xxx As Object (field 1 type 1, field 2 Type 2, ...);

A custom type was created as follows Addr_type

The type can be applied to a column of a table, creating a cust table

There are two fields in this table, a base type ID field, and a addr field of type Addr_type.

2. SQL related

Insert Data SQL

To perform a query operation:

SELECT * FROM Cust;

Here addr the value, you need to click on the corresponding list of Sqldeveloper to see its value.

3, OCI related 3.1 select related

Before using the OCI to manipulate the custom type, you need to use the OTT tool to convert the type, and add the generated header and C files to the OCI's source project, please refer to the Oracle OTT Learning article for specific OTT related operations.

1) to initialize the OCI environment variables and assign each handle, where these operations are encapsulated, where the call to the Ocienvcreate function of the parameter mode must have Oci_object mode to support the custom type.

2) Call the C function generated by Ott to initialize

3) Connect to the database, which is also encapsulated

4) Prepare the SQL statement

Note: The at variable at the addr_type* at declaration is generated automatically by OTT. It is best to assign null values when declaring, such as addr_type* at = NULL;

5) Binding output variable ID

6) Bound output variable addr binding a custom variable here takes two steps (or 3 steps)

6.1 Call Ocidefinebypos with the Sqlt_nty parameter first

6.2 Then call Ocidefineobject to bind the at variable, the at variable is a struct, which is described above.

Once the bindings are complete, execute and fetch the results.

7) Call Ocistmtexecute EXECUTE statement

8) Loop call Ocistmtfetch to get the data

The results of the execution procedure are as follows:

3.2 Insert Operation 3.2.1 The structure created by Ott

1) Initialize the OCI environment and handle

2) assigning values to the structure addr content

3) connect to the database

4) Prepare the SQL statement, which is used in a binding way

5) Bind the custom type in the same way as in select.

6) Execute the SQL statement and carry out the related handle collection and other operations.

Note: Before assigning a value to a variable in a struct, it must be initialized to 0, or the program will error.

The point here is that the custom type as a whole is manipulated as a binding variable.

The 3.2.2 is used in a conventional manner

1) Declare the variable and initialize the OCI environment and handle

2) connect to the database

3) binding variables, where each binding the respective variable, is equivalent to disassembling the custom type.

4) executes the SQL and finally releases the handle related.

Note: This is achieved using the Addr_type (: 2,:3,:4,:5) method.

Oracle OCI-operated UDT-related learning