Pro * c Introduction - Inline SQL

xiaoxiao2021-03-06 15

[Translate by z.jingwei. Document address: http://www-db.stanford.edu/~ setman/fcdb/oracle/or-proc.html]

Pro * c Introduction of embedded SQL

Summary Pro * C Syntax

SQL pre-processing instruction statement label host variable

Basic pointer structure array indicator variable data type equivalent dynamic SQL transaction error handling

SQLCA WHENEVER statement DEMO program C users list of embedded SQL Statements Supported by Pro * C

Summary SQL is a method of combining a high-level language such as C / C computing power and SQL database processing capabilities. It allows you to perform any SQL statement in the program. Oracle's embedded SQL environment is called Pro * C.

Pro * c program is compiled in two steps. First, the pre-compiler of Pro * C identifies the SQL statement embedded in the program and converts these statements to the appropriate calls for function (functions) in SQL Runtime Library. The output is pure C / C code and unprocessed pure C / C code. Then, compile the code with a conventional C / C compiler and generate an executable. For more detailed content, please refer to the Demo program.

Demo program.

Pro * C syntax

All SQL all SQL statements must start with Exec SQL and end with semicolons; The SQL statement can be placed anywhere in the C / C block, but the executable (SQL) statement should be behind the declaration statement. example:{

Int a;

/ * ... * /

Exec SQL SELECT SALY INTO: A

From Employee

WHERE SSN = 876543210;

/ * ... * /

Printf ("THE SALARY IS% D / N", A);

/ * ... * /

}

The preprocessing instructions can work normally in Pro * c, the C / C pre-processing instructions are #include and #if. Pro * c cannot recognize #define. The following code is wrong: #define the_ssn 876543210

/ * ... * /

Exec SQL SELECT SALY INTO: A

From Employee

Where ssn = the_ssn; / * invalid * /

The statement label can jump to the C / C mark EXEC SQL WHENEVER SQLERROR GOTO ERROR_IN_SQL in SQL;

/ * ... * /

Error_in_sql:

/ * Do Error Handling * /

We will talk about the meaning of WHENEVER in the following error handling section.

Error handling section

The meaning of WHENEVER.

Host variable

basis

The host variable is the key to connecting host programs and databases. The host variable expression must be considered (resolve to) left value (can be assigned). You can declare the host variable by declaring normal C variables. The statement of the host variable can be placed where any C variable declaration can be placed. (C users need to use "Declare Section"; refer to C Users) Oracle's C data types include:

C users) Oracle can use C data types include:

Char char [n] int short long float double varchar [n] - it is a pre-processing type (PSUEDO-TYPE) that can be identified by the pre-encoder of Pro * C. It is used to indicate the bell string of blank padding (Blank-padded, translation: '/ 0'). The PRO * C pre-encode converts it to a structural body having a 2-byte (Byte) long domain and an N-word (Byte). You can't specify register storage type (translation: pointer) as host variables.

You can use a colon in the SQL expression ":" to refer to a host variable, but you cannot use a semicolon in the C expression. When using a string as a host variable, the reference must be omitted; Pro * C understands that you are specifying a string based on the host variable declaration type (the description of this sentence is when defining a string as a host variable, char * Str = "string", when used in embedding SQL, omitted "*" instead of * STR). The expression cannot be used as a host variable without calculating C function calls and most pointers, even if they are indeed interpreted as left. The following code also illustrates the reference to the legitimate and illegal host variables:

INT DePtnos [3] = {000, 111, 222};

INT GET_DEPTNO () {return deptnos [2];

INT * GET_DEPTNOPTR () {Return & (Deptnos [2]);

int main ()

{

INT x; char * y; int Z;

/ * ... * /