During the development of C language, it is necessary to operate the database. Generally, Oracle Pro*C/C++ precompiler is used to use Oracle database.
This article describes the Oracle Pro*C/C++ precompiler, its role in developing applications that manipulate Oracle data, and what it enables your applications to do. Specific such as use.
This chapter introduces Oracle Pro*C/C++ embedded SQL statements.
Introduction to Embedded SQL
Embedded SQL refers to SQL statements placed in an application. Because it contains SQL statements, the application is called the host program and the language in which it is written is called the host language. For example, Pro*C/C++ provides the ability to embed certain SQL statements in C or C++ host programs.
To manipulate and query Oracle data, you can use INSERT, UPDATE, DELETE, and SELECT statements. INSERT adds rows to a database table, UPDATE modifies rows, DELETE deletes rows that are not needed, and SELECT retrieves rows that meet search criteria.
The powerful SET ROLE statement lets you dynamically manage database permissions. A role is the system and object permissions associated with it, or a named group that grants system or object permissions associated with a named group to a user or other role. Role definitions are stored in the Oracle data dictionary. Your application can use the SET ROLE statement to enable and disable roles as needed.
Embedded SQL classification
Embedded SQL includes all interactive SQL statements and other statements that allow you to transfer data between Oracle and the host program. There are two types of embedded SQL statements: executable statements and instructions.
- Executable statement
Executable statements call the runtime library SQLLIB, which can be used to connect to Oracle, define, query, and manipulate Oracle data, control access to Oracle data, and process transactions, and can be placed anywhere executable statements in THE C or C++ language can be placed.
| Executable statement | instructions |
|---|---|
| ALLOCATE | Define and control Oracle data |
| ALTER | |
| ANALYZE | |
| DELETE | DML statements |
| INSERT | |
| SELECT | |
| UPDATE | |
| COMMIT | Commit the transaction |
| ROLLBACK | |
| SAVEPOINT | |
| SET TRANSACTION | |
| DESCRIBE | Using Dynamic SQL |
| EXECUTE | |
| PREPARE | |
| ALTER SESSION | Control of the session |
| SET ROLE |
- instruction
The directive does not call SQLLIB and does not operate on Oracle data. You can use instructions to declare Oracle objects, communication areas, and SQL variables anywhere you can put C or C++ variable declarations.
| instruction | instructions |
|---|---|
| ARRAYLEN | Define and control Oracle data |
| BEGIN DECLARE SECTION | Start by declaring the host variable |
| END DECLARE SECTION | Declare the host variable to end |
| DECLARE | Declare an Oracle user object |
| INCLUDE | Copy the file |
| TYPE | Equivalent data type |
| VAR | Equivalent variable |
| WHENEVER | Handle runtime errors |
Embedded SQL Syntax
In your application, you are free to mix full SQL statements with full C statements and use C variables or structures in SQL statements. The only special requirement for building SQL statements into a host program is that they begin with the keyword EXEC SQL and end with a semicolon. Pro*C/C++ converts all EXEC SQL statements into calls to the runtime library SQLLIB.
/* Embedded ROLLBACK statement */The EXEC SQL ROLLBACK.Copy the codeHost and indicator variables
Host variables are the key to communication between Oracle and programs. A host variable is a scalar or collection variable declared in C and shared with Oracle, which means that both programs and Oracle can reference its value.
The program uses input host variables to pass data to Oracle. Oracle uses output host variables to pass data and state information to the program. The program assigns a value to the input host variable; Oracle assigns values to output host variables.
Host variables can be used anywhere SQL expressions can be used. In SQL statements, host variables must be prefixed with a colon (:) to distinguish them from SQL keywords.
You can also use C constructs to include many host variables. When you name a structure in an embedded SQL statement, prefixed with a colon, Oracle uses each component of the structure as the main variable.
In SQL statements, indicator variables must be prefixed with a colon (:) and immediately followed by their associated pivot variable. The key INDICATOR can be placed between the master variable and its INDICATOR for added clarity.
If host variables are encapsulated in a structure and you want to use indicator variables, simply create a structure with an indicator variable for each host variable in the host structure and name the indicator structure in the SQL statement, immediately following the main variable structure and prefixed with a colon. You can also use the INDICATOR keyword to separate the host structure from its associated INDICATOR structure.
Oracle Data Types
Typically, the host program inputs data to Oracle, and Oracle outputs data to the program. Oracle stores input data in database tables and output data in program master variables. To store a data item, Oracle must know its data type, which specifies the storage format and the range of valid values.
Oracle recognizes two types of data: internal and external. Internal data types specify how Oracle stores data in database columns. Oracle also uses internal data types to represent database pseudo columns that return specific data items but are not actual columns in the table.
External data types specify how data is stored in host variables. When your host program enters data into Oracle, Oracle converts, if necessary, between the external data types of the input host variables and the internal data types of the target database columns. When Oracle outputs data to a host program, it converts, if necessary, between the internal data types of the source database columns and the external data types that output host variables.
An array of
Pro*C/C++ allows you to define array host variables (called host arrays) and structure arrays and operate on them using a single SQL statement. Using array SELECT, FETCH, DELETE, INSERT, and UPDATE statements, you can easily query and manipulate large amounts of data. You can also use host arrays in host variable struct.
Data type equivalence
Pro*C/C++ increases the flexibility of applications by letting you peer to peer data types. This means you can customize how Oracle interprets the input data and formats the output data.
On a variable-by-variable basis, you can equate supported C data types with Oracle external data types, and you can also equate user-defined data types with Oracle external data types.
Private SQL areas, cursors, and active sets
To process SQL statements, Oracle uses a workspace called the private SQL region. Private SQL areas store information needed to execute SQL statements. Identifiers called cursors let you name SQL statements, access information in their private SQL areas, and have some control over their processing.
For static SQL statements, there are two types of cursors: implicit and explicit. Oracle implicitly declares a cursor for all data definition and data manipulation statements, including SELECT statements (queries) that return only one row. However, for queries that return multiple rows, to process after the first row, you must explicitly declare a cursor (or use a host array).
The returned rowset is called an active set; Its size depends on how many rows meet the query search criteria. You use an explicit cursor to identify the row that is currently being processed, called the current row.
By referring to the set of rows returned to the terminal screen, the screen cursor can point to the first line to be processed, then the next, and so on. Also, an explicit cursor “points” to the current move in the activity set, which allows your program to process one line at a time.
The transaction
A transaction is a series of logically related SQL statements (for example, credit a bank account and debit another two updates) that Oracle treats as a unit so that all changes brought about by the statement are made permanent or undone at the same time.
All data operation statements executed since the last data definition, COMMIT, or ROLLBACK statement were executed constitute the current transaction.
To help ensure database consistency, Pro*C/C++ allows you to define transactions using COMMIT, ROLLBACK, and SAVEPOINT statements.
COMMIT makes permanent any changes made during the current transaction. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the transaction; Used with ROLLBACK, it undoes some transactions.
Errors and warnings
When an embedded SQL statement is executed, it either succeeds or fails and may result in an error or a warning. You need a way to process these results. Pro*C/C++ provides two error handling mechanisms: THE SQL communication area (SQLCA) and the WHENEVER statement.
The SQLCA is a data structure contained (or hard-coded) in a host program that defines program variables that Oracle uses to pass runtime state information to a program. Using the SQLCA, you can take different actions based on Oracle’s feedback on the work you just tried. For example, you can check whether the DELETE statement was successful and, if so, how many rows were deleted.
With the WHENEVER statement, you can specify the action to be taken automatically when Oracle detects an error or warning condition. These operations are: continue to the next statement, call a function, branch to a labeled statement, or stop.