OCI study Note 3

OCI Definition
1. Create an OCI environment, that is, create and initialize an OCI working environment. Other OCI functions can be executed only in the OCI environment.
2. Handle types to be applied:
2.1) OCI environment handle: oci_htype_env, which defines the environment for calling all OCI functions. It is the parent handle of other handles (generated by ocienvlnit or ocienvcreate ).
2.2) Error handle: oci_htype_error, which is used as a parameter of some OCI functions to record Errors generated during the operations of these OCI functions. It is available when an error occurs.
Ocierrorget () to read the error information recorded in the error handle.
2.3) server environment handle: oci_htpye_svcctx defines the server operation environment called by OCI. It contains three handles: Server, user session, and transaction.
2.4) server handle: oci_htype_server, which identifies the data source and converts it to a physical connection to the server.
2.5) user session handle: oci_htype_session, which defines user roles and permissions and the execution environment of OCI calls.
2.6) Transaction handle: oci_htype_trans, which defines the transaction environment for performing SQL operations. The transaction environment contains the user's session status information.
2.7) Statement handle: oci_htype_stmt is an environment that identifies an SQL statement or PLSQL block and its related attributes.
2.8) bind/define handle: the sub-handle of the statement handle, which is automatically generated by the OCI library. You do not need to apply for it again. The OCI input variables are stored in the BIND handle, and the output changes.
Volume is stored in the define handle.
3. Handle attributes include:
Server Environment handle attributes: (oci_htype_svcctx)
Oci_aatr_server -- set/read service environment attributes of the service environment
Oci_attr_session -- sets/reads the session authentication environment attribute of the service environment
Oci_attr_trans -- set/read the transaction environment attribute of the service environment
User session handle attributes: (oci_htype_session)
Oci_attr_username -- set the user name used for session Authentication
Oci_attr_password -- set the user password used for session Authentication
Server handle: (oci_htype_server)
Oci_attr_noblocking_mode -- set/read server connection: = true when the server connection is set to non-blocking mode.
Statement handle: (oci_htype_stmt)
Oci_attr_row_count -- read-only, which is the currently processed call. Its default value is 1.
Oci_attr_stmt_type -- class for reading the current SQL statement
Type: oci_stmt_begin, oci_stmt_select, oci_stmt_insert, oci_stmt_update, oci_stmt_delete, oci_stmt_param_count (in the selection column of the Return Statement)
Number of columns)

4. Definition of output variables:
If you know the selection list structure of the SELECT statement before executing the statement, you can define the output operation before calling ocistmtexecute. If the parameters of the query statement are
The input must be defined after execution.
5. OCI function return value:
Oci_success -- function execution successful 0
Oci_success_with_info -- the execution is successful, but a Diagnostic message is returned, which may be a warning.
Oci_no_data -- the function execution is complete, and there is no other data
Oci_error -- function execution error
Oci_invalid_handle -- the parameter passed to the function is invalid handle, or the returned handle is invalid.
Oci_need_data -- requires the application to provide runtime data
Oci_continue -- code returned by the callback function, indicating that the callback function requires the OCI library to restore its normal processing operations
Oci_still_executing -- the service environment is established in non-blocking mode, and the OCI function is being called.

6. OCI connection method
There are two OCI Connection Methods: blocking and non_blocking)
The blocking mode means that when the OCI operation is called, the server must wait until the OCI operation is completed to return the corresponding information of the client, whether it is successful or failed. The non-blocking mode is when the client
After submitting the OCI operation to the server, the server immediately returns the oci_still_executing information without waiting for the server to complete the operation.
For the non_blocking method, if the application receives an OCI function whose return value is oci_still_executing, it must judge the return value of each OCI function again,
Determine whether it is successful or not.
You can set the server attribute to oci_attr_nonblocking_mode. The default mode is non-blocking.

7. OCI function setting modes include:
Oci_default: use the default OCI Environment
Oci_threaded: Use OCI in the thread Environment
Oci_object: Object Mode
Oci_shared: Shared Mode
Oci_enents
Oci_no_ucb
Oci_env_no_mutex: Non-mutex Access Mode
The mode can be superimposed using logical operators to set the function to multiple modes, such
Mode = oci_shreaded | oci_object

8. When the application is disconnected from the server and the program does not use ocitranscommit () for transaction submission, all active events will be automatically rolled back.
9. OCI redefinition of Data Types
Typedef unsigned char ub1;
Typedef signed Char SB1;
Typedef unsigned short ub2;
Typedef signed short sb2;
Typedef unsigned int ub4;
Typedef signed int sb4;
Typedef ub4 duword;
Typedef sb4 dswork;
Typedef dsword DWORD;

10. After preparing the SQL statement, you can use ociattrset (0 sets the type attribute oci_attr_stmt_type of the statement, and can read the statement attributes and process the attributes separately)
11. Bind Input and Output Parameters in batches to store data in a static array. You can submit or read the records of multiple rows at a time.
12. The placeholder variable is matched with the placeholder variable.
Placeholder: in a program, some SQL statements must be identified only when the program is running. It can be replaced by a placeholder During design. When the program is running
After the statement, you must specify a variable for each placeholder, that is, combining the placeholder with the program variable address. During execution, Oracle reads data from these variables and
When it is passed to the Oracle server for execution. OCI combined with placeholders, it associates the placeholders with program variables, and points out the data type and length of program variables.
For example, select * from test where name =: P1 and age>: P2,: P1 and: P2 are placeholders.
Indicator variable: Because the column value in Oracle can be null, but there is no null value in the C language, in order to enable the OCI program to express the null column value, the OCI function allows the program to execute
The combine variable in the statement is associated with an array of indicator variables or indicator variables to indicate whether the combined placeholder is null, whether the column value to be read is null, and how to read more
Whether the column value is truncated.
Except sqlt_nty (SQL named datatype), the Data Type of the indicator variable or indicator variable array is sb2. its value description:
-1: The OCI program assigns null to the columns in the Oracle table, ignoring the program variable values that combine placeholders.
0: The application assigns the program variable value to the specified column.
As the output variable: (such as in the SELECT statement)
-2: the length of the read column is greater than the length of the program variable, for example, truncated.
-1: The read Column value is null, and the value of the input variable is not changed.
0: The data is completely read into the specified program variable.

OCI Definition
1. Create an OCI environment, that is, create and initialize an OCI working environment. Other OCI functions can be executed only in the OCI environment.

2. Handle types to be applied:
2.1) OCI environment handle: oci_htype_env, which defines the environment for calling all OCI functions. It is the parent handle of other handles (generated by ocienvlnit or ocienvcreate ).
2.2) Error handle: oci_htype_error, which is used as a parameter of some OCI functions to record Errors generated during the operations of these OCI functions. It is available when an error occurs.

Coierrorget () to read the error information recorded in the error handle.
2.3) server environment handle: oci_htpye_svcctx defines the server operation environment called by OCI. It contains three handles: Server, user session, and transaction.
2.4) server handle: oci_htype_server, which identifies the data source and converts it to a physical connection to the server.
2.5) user session handle: oci_htype_session, which defines user roles and permissions and the execution environment of OCI calls.
2.6) Transaction handle: oci_htype_trans, which defines the transaction environment for performing SQL operations. The transaction environment contains the user's session status information.
2.7) Statement handle: oci_htype_stmt is an environment that identifies an SQL statement or PLSQL block and its related attributes.
2.8) bind/define handle: the sub-handle of the statement handle, which is automatically generated by the OCI library. You do not need to apply for it again. The OCI input variables are stored in the BIND handle, and the output changes.

Volume is stored in the define handle.

3. Handle attributes include:
Server Environment handle attributes: (oci_htype_svcctx)
Oci_aatr_server -- set/read service environment attributes of the service environment
Oci_attr_session -- sets/reads the session authentication environment attribute of the service environment
Oci_attr_trans -- set/read the transaction environment attribute of the service environment
User session handle attributes: (oci_htype_session)
Oci_attr_username -- set the user name used for session Authentication
Oci_attr_password -- set the user password used for session Authentication
Server handle: (oci_htype_server)
Oci_attr_noblocking_mode -- set/read server connection: = true when the server connection is set to non-blocking mode.
Statement handle: (oci_htype_stmt)
Oci_attr_row_count -- read-only, which is the currently processed call. Its default value is 1.
Oci_attr_stmt_type -- class for reading the current SQL statement

Type: oci_stmt_begin, oci_stmt_select, oci_stmt_insert, oci_stmt_update, oci_stmt_delete, oci_stmt_param_count (in the selection column of the Return Statement)

Number of columns)

4. Definition of output variables:
If you know the selection list structure of the SELECT statement before executing the statement, you can define the output operation before calling ocistmtexecute. If the parameters of the query statement are

The input must be defined after execution.

5. OCI function return value:
Oci_success -- function execution successful 0
Oci_success_with_info -- the execution is successful, but a Diagnostic message is returned, which may be a warning.
Oci_no_data -- the function execution is complete, and there is no other data
Oci_error -- function execution error
Oci_invalid_handle -- the parameter passed to the function is invalid handle, or the returned handle is invalid.
Oci_need_data -- requires the application to provide runtime data
Oci_continue -- code returned by the callback function, indicating that the callback function requires the OCI library to restore its normal processing operations
Oci_still_executing -- the service environment is established in non-blocking mode, and the OCI function is being called.

6. OCI connection method
There are two OCI Connection Methods: blocking and non_blocking)
The blocking mode means that when the OCI operation is called, the server must wait until the OCI operation is completed to return the corresponding information of the client, whether it is successful or failed. The non-blocking mode is when the client

After submitting the OCI operation to the server, the server immediately returns the oci_still_executing information without waiting for the server to complete the operation.
For the non_blocking method, if the application receives an OCI function whose return value is oci_still_executing, it must judge the return value of each OCI function again,

Determine whether it is successful or not.
You can set the server attribute to oci_attr_nonblocking_mode. The default mode is non-blocking.

7. OCI function setting modes include:
Oci_default: use the default OCI Environment
Oci_threaded: Use OCI in the thread Environment
Oci_object: Object Mode
Oci_shared: Shared Mode
Oci_enents
Oci_no_ucb
Oci_env_no_mutex: Non-mutex Access Mode
The mode can be superimposed using logical operators to set the function to multiple modes, such
Mode = oci_shreaded | oci_object

8. When the application is disconnected from the server and the program does not use ocitranscommit () for transaction submission, all active events will be automatically rolled back.

9. OCI redefinition of Data Types
Typedef unsigned char ub1;
Typedef signed Char SB1;
Typedef unsigned short ub2;
Typedef signed short sb2;
Typedef unsigned int ub4;
Typedef signed int sb4;
Typedef ub4 duword;
Typedef sb4 dswork;
Typedef dsword DWORD;

10. After preparing the SQL statement, you can use ociattrset (0 sets the type attribute oci_attr_stmt_type of the statement, and can read the statement attributes and process the attributes separately)

11. Bind Input and Output Parameters in batches to store data in a static array. You can submit or read the records of multiple rows at a time.

12. The placeholder variable is matched with the placeholder variable.
Placeholder: in a program, some SQL statements must be identified only when the program is running. It can be replaced by a placeholder During design. When the program is running

After the statement, you must specify a variable for each placeholder, that is, combining the placeholder with the program variable address. During execution, Oracle reads data from these variables and

When it is passed to the Oracle server for execution. OCI combined with placeholders, it associates the placeholders with program variables, and points out the data type and length of program variables.
For example, select * from test where name =: P1 and age>: P2,: P1 and: P2 are placeholders.
Indicator variable: Because the column value in Oracle can be null, but there is no null value in the C language, in order to enable the OCI program to express the null column value, the OCI function allows the program to execute

The combine variable in the statement is associated with an array of indicator variables or indicator variables to indicate whether the combined placeholder is null, whether the column value to be read is null, and how to read more

Whether the column value is truncated.
Except sqlt_nty (SQL named datatype), the Data Type of the indicator variable or indicator variable array is sb2. its value description:
=-1: The OCI program assigns null to the columns in the Oracle table, ignoring the program variable values associated with placeholders
> = 0: The application assigns the program variable value to the specified column.
As the output variable: (such as in the SELECT statement)
=-2: the length of the read column is greater than the length of the program variable, such as being truncated.
=-1: The read Column value is null, and the value of the input variable is not changed.
= 0: The data is completely read into the specified program variable.

> 0: if the data length of the column to be read is greater than the length of the program variable, it is truncated. The indicator variable value is the actual length before the data to be read is truncated.

 

 

 

Http://wenku.baidu.com/view/9e71a70203d8ce2f006623fd.html? From = related & hasrec = 1