The use of Pro*c

A pro*c Program Overview:
1. What is the PRO*C program
In Oracle database management and system, there are three ways to access the database;
(1) With Sql*plus, it has SQL commands to access the database with interactive applications;
(2) using the fourth generation of language application development tools developed by the application Access database, these tools have sql*froms,ql*reportwriter,sql*menu;
(3) Access using SQL language or Oracle library functions embedded in a third-generation language.
Pro*c is one of the third development tools that combines procedural language C and non-procedural language SQL to the best of its kind,
With a complete process processing capacity, but also to complete any database substandard goods tasks, so that users can be programmed to complete various types of reports. You can embed the SQL language in the PRO*C program,
These SQL languages enable you to dynamically create, modify, and delete tables in a database, to query, insert, modify, and delete rows in database tables, and to implement transaction submission and rollback.
Pl/sql blocks can also be embedded in PRO*C programs to improve application performance, especially in networked environments, reducing the total overhead of network transmission and processing.

2. PRO*C's program Structure diagram
In layman's terms, the PRO*C program is actually embedded with a SQL statement or a pl/sql block of C programs, so its composition is very similar to the C program. But because it's embedded with SQL statements or Pl/sql blocks,
So it also contains a different ingredient. In order for everyone to have a perceptual understanding of pro*c, the two differences are compared as follows:
The whole variable description of C
C Source program function 1: same function K.
Function 2: same function K.



Description of local variables in C
function K
Executable statement

Description of external variables for application header C
External description Segment (Oracle variable description)
Communication Area Description

Pro*c source program function 1: same function K.
Function 2: same function K.
C Local Variable description
Internal Description section of internal description part of the program body
Communication Area Description
Executable statement for function K C
Executable statements that EXECUTE statement SQL

or Pl/sql block

Two The composition structure of the PRO*C program

Each Pro*c program includes two parts: (1) application header; (2) Application body
The application header defines the relevant variables for the Oracle database.
Prepare for manipulating Oracle databases in C. The application body is basically composed of PRO*C SQL statement calls. Mainly refers to the query Select, INSERT, UPDATE, delete and other statements.
The composition of the application is as shown in the figure:
Application Header
Description section


SQL Communication Area

Application Body
EXEC SQL BEGIN DECLARE section
(definition of SQL variable)
EXEC SQL end DECLARE section;
EXEC SQL INCLUDE Sqlla;
EXEC SQL connect:< User name >
Identified by: < password >
Use of SQL statements and cursors


1. Application Header
The first part of the application is the beginning of the pro*c. It consists of the following three parts:
L c variable Description section;
L SQL Variable Description section (declare part);
• SQL Communication area.

(1). Declare section (Description section)
The description section describes the SQL variables of the program, defines the section with exec SQL begin DECLARE sections, and starts with the exec SQL end DECLARE
A section of the end. It can appear in the main part of the program, or it may appear in the local
Description and use of SQL variables
The data types that can be specified for SQL variables in the Description section are as shown in the table:
Data type description
CHAR
CHAR (N)
Int
Short
LONG
FLOAT
DOUBLE
VarChar single character
N-character Array
Integer
Short integer
Single-precision floating-point numbers
Double-precision floating point numbers
Variable length string

These data types are actually the data types of the C language, where varchar is considered an extension of the C data type. This will be talked about in the future.
The use of SQL variables should be noted in the following points:
L must be clearly defined in the Description section
L must use the same case format as its definition
When used in an SQL statement, you must precede it with a ":" (colon), but you do not need a colon to reference in the C statement.
L cannot be a reserved word in an SQL command.
L can carry indicator variables.
For example: EXEC SQL BEGIN DECLARE SECTIONS;
VARCHAR programe[30];
Int Porgsal, Pempno;
EXEC SQL end DECLARE section;


EXEC SQL SELECT ename, SAL
Into:programe,: Progsal
From EMP
WHERE EMPNO =: pempno;


(2). Description and reference of indicator variable
Indicates that a variable is actually a class of SQL variable that is used to manage the host variable associated with it (that is, charging in the SQL statement
When input or output variables). Each host variable can define an indicator variable, primarily for handling null values (NULL)
The description of the indicator variable is basically the same as a generic SQL variable, but it must be defined as a 2-byte integer, such as short, INT. When referenced in an SQL statement,
It should also be preceded by a ":" (colon) and must be attached to its associated host variable, which can be used independently in the C statement. When the indicator variable is-1, a null value is represented. For example:
EXEC SQL BEGIN DECLARE section;
INT Dept-number;
Short Ind–num;
CHAR Emp–name;
EXEC SQL end DECLARE section;



SCANF ("90d%s", & Dept-number, Dept–name);
If (Dept–number ==0)
Ind–num =-1;
Else
Ind–num = 0;
EXEC SQL INSERT into DEPT (DEPTNO, Dname)
VALUES (:d ept–number:ind-num,:d ept–name);
Where Ind–num is the indicator variable for the dept–number. When the Dept–number value entered is 0 o'clock, the null value is inserted into the Deptno column of the Dept table.
(3). Description and use of pointer SQL variables
The pointer SQL variable must also be declare before it is referenced
section to explain first. The description format is the same as the C language. When referenced in an SQL statement, the pointer name is preceded by a prefix of ":" (a colon) without a "*" (asterisk). Use a pointer variable in the C statement as a C language.
(4). Description and reference of an array SQL change
When referencing an array in an SQL statement, you simply write the array name (preceded by a colon) and do not need to write the subscript, which is used in the C statement as an array variable in the C language.
Using arrays can significantly reduce network transport overhead. If you want to insert 100 rows of data into a table, if there is no array, repeat 100 times, and after the reference, you only need to execute an INSERT statement once and insert it once. For example:
EXEC SQL BEGIN DECLARE section;
Int emp_number[100];
Char emp_name[100][15];
Float salary[100],commission[100];
Int Dept_number;
EXEC SQL end DECLARE section;
....
EXEC SQL SELECT Empno,ename,sal,comm
Into:emp_number,:emp_name,:salary,:commission
From EMP
WHERE deptno=:d Ept_number;
When using arrays, you should pay attention to the following points;
l do not support pointer arrays
L supports only one-dimensional arrays, whereas Emp-name [100][15] is considered a one-dimensional string
The maximum dimension of the L array is 32767
l when referencing multiple arrays in an SQL statement, these array dimensions should be the same
L do not allow simple SQL variables to be mixed with array SQL variables in values, SET, into, or where child names
L cannot initialize an array in the Delare section
For example: The following reference is illegal
EXEC SQL BEGIN DECLARE section;
Int Dept–num [3] = {10,20,30};
EXEC SQL end DECLARE section;

EXEC SQL SELECT EMPNO, ename, SAL
Into:emp–num [i],: emp–name [i],: salarg [i]
From EMP
(5) Description and reference of pseudo-type varchar
The varchar variable must be described in the Description section before it is referenced, and must indicate the maximum number of strings
Length, such as:
EXEC SQL BEGIN DECLARE section;
Int Book–number;
VARCHAR Book–name [50];
EXEC SQL end DECLARE section;
Book–name is translated into a structural variable in C language when it is programmed;
Struct {unsigned short len;
Unsigned Chart arr [20];
} boo–name
As a result, the varchar variable is actually a struct variable with members of length and array. When referencing in an SQL statement, you should refer to a struct name prefixed by a colon, without subscript, referencing the struct member in the C statement.
The varchar variable is automatically set by Oracle when it is an output variable, and the program should first store the string in an array member as an input variable.
The length is stored in the length member and then referenced in the SQL statement. For example:
Main ()
{ .......
scanf ("90s, 90d", Book–name. Arr, & Book–number);
Book–name. Len = strlen (book–name. arr);
EXEC SQL UPDATE Book
SET bname =: book–name;
Bdesc =: book–number;
}
(6) SQL Communication area
The SQL communication area is described in the following statement:
EXEC SQL INCLUDE SQLCA;
This section provides a success or failure record and error handling for the user to run the program.

Composition of the SQLCA
SQLCA is a variable of a struct type, which is an interface between Oracle and the application. When the PRO*C program is executed, ORACLE
The state information executed by each Embedded SQL statement is stored in SQLCA, according to which the execution of the SQL statement can be judged to be successful, the number of rows processed, the error message, etc., which is composed as shown in the table:
Struct Sqlca
{char sqlcaid [8];----à identifies the communication area
Long SQLABC; The length of the---à communication area
Long Sqlcode; ---à keeps the status code of the most recently executed SQL statement
struct {unsigned short sqlerrml;-----à information text length
}SQLERRM;
Char SQLERRP [8];
Long SQLERRD [6];
Char Sqlwarn [8];
Char sqlext [8];
}
struct SQLCA Sqlca;
Where Sqlcode is most commonly used in programs, it retains the status code of the most recently executed SQL statement. The programmer makes the corresponding processing according to these status codes. These status code values are as follows:
0: The SQL statement is executed correctly, without errors and exceptions.
>0:oracle executed the statement, but encountered an exception (if no data was found).
<0: Indicates that Oracle did not execute the SQL statement because of a database, system, network, or application error.
When this type of error occurs, the current transaction should generally be rolled back.

2. Application Body
In the PRO*C program, SQL statements and C statements can be mixed to write freely, and SQL variables can be used in SQL statements, the writing grammar of Embedded SQL statements is:
L start with the keyword exec sql
L non-terminal (semicolon) End of statement in C language
The role of SQL statements is primarily used to deal with databases. C Language Program for control, input, output and data processing.
(1) Connect to Oracle Database
Before accessing the database, you must first connect the program to the Oracle database. That is, log on to Oracle. The connected command should be the first executable command for the application. The connection command format is as follows:
EXEC SQL connect:< username > identified by: < password >
or exec SQL CONNECT: < username >/< password >
When you log in using both of these formats, you should first define the name and password of the
SQL variables and set them before you perform the connect, which can cause a login failure. For example:
EXEC SQL BEGIN DECLARE section;
VARCHAR Usename [20];
VARCHAR PASSWORD[20];
EXEC SQL End DECLARE
..........
strcpy (Usename.arr, "Csott");
Usename.len = strlen (Username.arr);
strcpy (Password.arr, "TIGER");
password. Len = strlen (password. arr);
EXEC SQL whenever SQLERROR GOTO sqlerr;
EXEC SQL connect:username indntified By:password;
Note: You cannot write the username and password directly into the Connect statement, or enclose the letter in quotation marks (') in the Connect statement, as the following statement is invalid.
EXEC SQL CONNECT SCOTT inentified by TIGER;
EXEC SQL CONNECT ' SCOTT ' identified by ' TIGER ';
(2). Inserting, updating, and deleting
When you talk about the SQL language in detail, this is not an example.
(3). Database query and the use of cursors
In Pro*c, queries can be grouped into two categories:
L Returns a query for a single line or a fixed number of rows;
L returns multiple rows of queries. This query requires the use of cursors to control each row or group (the primary variable is an array).
1) Returns a query for a single line or a fixed number of rows
The query SQL SELECT statement in PRO*C consists of the following clauses:
SELECT
Into
From
WHERE
CONNECT by
UNION
INTERSECT
Minus
GROUP by
Having
ORDER BY
Where the query condition in the WHERE clause can be a collection of attributes or multiple properties, the primary variable to which the assignment is performed can also be placed in the WHERE clause. The primary variable used in the WHERE clause is called the input master variable. Such as:
SELECT EMPNO, JOB, SAL
Into:pname,:P job,:P Sal.
From EMP
WHERE empno=:P EMPNO;
If no qualifying row is found, Sqlca.sqlcode returns "+1403" indicating "not found".
The main variable in the INTO clause is called the output master variable, which provides the information needed for the query.
Oracle is required to convert these items to the data type of the primary variable before any items are given to the primary variable. The number is done by truncation (for example, 9.23 to 9).
If you have determined that the query returns only one row, then you do not have to use a cursor, just add an into clause to the SELECT statement. The number of output master variables in a query in which the semantically into statement has a number of selections before the From. If the number of expressions in the Select item is not equal to the number of primary variables in the INTO clause, the sqlca.sqlwarn[3 is set to "W".

2 Multi-line query and the use of cursors
If the query returns multiple rows or does not know how many rows are returned, use a SELECT statement with an Oracle cursor (CURSOR).
Cursors are the work area where Oracle and Pro*c store query results. A cursor (named) is associated with a SELECT statement. The action cursor has 4 commands: (1) DECLARE
CURSOR (2) OPEN CURSOR (3) FETCH; (4) Close CURSOR.

A. Defining cursors
A cursor must be defined first in order to use it. The syntax is:
EXEC SQL declare〈 cursor name 〉corsor for
Select〈 column
From〈 table
For example:
EXEC SQL DECLARE csor, CURSOR for
SELECT ename, JOB, SAL
From EMP
WHERE Deptno=:deptno;
When assigned to a cursor cursor associated with a query, multiple rows are returned from the database when the Select query emp, which is an active region of the cursor.
Attention:
1 defines that the cursor must be completed before the cursor operation;
2) Pro*c cannot refer to a cursor without a definition;
3 after a cursor is defined, its scope is the entire program. So it is wrong for a program to define two identical cursors at the same time.

B. Open Cursors
The open statement that opens the cursor is used primarily to enter the contents of the primary variable, which is primarily the main variable used in the Where. The statement to open the cursor is: EXEC SQL open〈 cursor name
When a cursor is opened, more than one row of results can be fetched from the related query. All rows that meet the criteria for a query are composed of a collection called a cursor activity set. By taking operations, each row or group in the active set is returned one at a, and after the query is completed,
The cursor can be closed. As shown in the figure:
Defining Cursors: DECLARE

Start query: SELECT
Open cursor: Open


Fetching data from active set: Fetch

Query complete


Closing cursors: Close


NOTE: 1 The cursor is in front of the first row of the active set;
2 If you change the input master variable, you must reopen the cursor.

C. Data access
The process of fetching a row or set of results from an active set to send the result to the output master variable is called data. The definition of the output master variable is in the FETCH data statement. The statement to fetch the data is as follows:
EXEC SQL fetch〈 cursor name 〉into: Main variable 1, main variable 2, ...
The fetch process works as shown in the following illustration:

Query results
Cursor
FETCH
Query results
After the cursor is opened
Output to the current

......


The query results shown in the figure refer to the query results that satisfy the query criteria. The following points should be noted when using the fetch:
The L cursor must be defined before opening.
L The FETCH statement is executed only after the cursor is opened to fetch data.
L
Each time the FETCH statement is executed, data is fetched from the current row or group, and the next row or group moves up once. Each time the cursor refers to a row or group that is either the current row or the current group, and fetch is the data for the row or group specified by the cursor each time.
l when the cursor activity set is empty, Orcle returns a SQLCA. SQLCA (=1403).
L If you want this cursor to operate again, you must first close and then open it.
L in C program can open up a memory space to store the results of operations, so you can use open space to flexibly manipulate the results of the query.

D Close cursor
After you have finished fetching all the rows in the active set, you must close the cursor to release the resources associated with the cursor.
The format of the closing cursor is:
EXEC SQL close cursor name;
For example:
EXEC SQL Close C1;
The ORACLE V5.0 version supports the SQL format "current of CURSOR". This statement will point to the most recently fetched row in a cursor.
For modify and delete operations. The statement must be used after a fetch operation, which is equivalent to storing a rowid and using it.

(4). Example
EXEC SQL DECLARE Salespeople CURSOR for
SELECT Ssno, NAME, SALARY
From EMPLOYEE
WHERE dname= ' Sales ';
EXEC SQL OPEN salespeople;
EXEC SQL FETCH Salespeople
Into:ss,:name,:sal;
EXEC SQL Close salespeople;

(5) SQL nesting method and its application
Embedded SQL differs from interactive SQL in form as follows:
1 Add prefix "EXEC SQL" to SQL statement, this small difference is intended to be easily recognizable when precompiled,
To handle each SQL as a high-level language.
2 Each SQL statement is divided into descriptive statements and executable statements of two broad categories. The executable statement is divided into four categories: data definition, data control, data manipulation and data retrieval.
Executable SQL statements are written in the executable of a high-level language, and descriptive SQL statements are written in the high-level language's descriptive place.
For example: In the PRO*C program, create a table structure called book, which is as follows:
#include 〈stdio.h〉
EXEC SQL BEGIN DECLARE section;
VARCHAR uid[20], pwd[20];
EXEC SQL end DECLARE section;
EXEC SQL INCLUDE SQLCA;
Main ()
{
/*login database*/
strcpy (Uid.arr, ' Wu ');
Uid.len=strlen (Uid,arr);
strcpy (Pwd.arr, ' Wu ');
Pwd.len=strlen (Pwd.arr);
EXEC SQL connect:uid identifeed by:pwd;
EXEC SQL CREATE TABLE Book
(acqnum number, copies number, price number);
EXEC SQL COMMIT WORK release;
EXIT;
Pro*c can easily and flexibly access the data in the Orcle database, but also has the characteristics of C language, so that some Oracle products can not be completed tasks, such as the following a fixed special format output results.
Examples of SQL nested source programs
#unclude
typedef char ASCIZ[20];
EXEC SQL BEGIN DECLARE section;
EXEC SQL TYPE Asciz is STRING REFERENCE;
Asciz username;
Asciz password;
Asciz Emp_name (5);
int Emp_number (5a);
float salary[5];
EXEC SQL end DECLARE section;
EXEC SQL INCLUDE Sqlca;
Void print_rows ();
Void SQLError ();
Main ()
{
int Num_ret;
strcpy (username, "SCOTT");
strcpy (password, "TYGER");
EXEC SQL whenever SQLERROR do SQLERROR ();
EXEC SQL Connect:username identified By:password;
Print ("\nconnected to ORACLE as user:%s\n", username);
EXEC SQL DECLARE C1 CURSOR for
SELECT EMPNO, ename, SAL from EMP;
EXEC SQL OPEN C1;
Num_ret = 0;
for (;;)
{
EXEC SQL whenever not FOUND does break;
EXEC SQL FETCH C1 into:emp_number,: Emp_name,: Salary;
Print_rows (Sqlca.sqlerrd[2]–num_ret);
NUM_RET=SQLCA.SQLERRD[2];
}
if ((Sqlca.sqlerrd[2]–num_ret) >0);
Print _rows (Sqlca.sqlerrd[2]–num_ret);
EXEC SQL Close C1;
Printf ("\have a good day.\n");
EXEC SQL COMMIT WORK release;
}

void Print_rows (n);
int n;
{
int i;
printf ("\nnumber Employee salary\n");
printf ("------------------------------\ n");
For (i=0;i printf ("%-9d%-8s%9.2f\n", Emp-number[i], emp---name[i],salary[i];
}
void SQLError ()
{
EXEC SQL whenever SQLERROR CONTINUE;
Printf ("\noracle error detected:\n");
Printf (' \n%.70s\n ', SQLCA.SQLERRM.SQLERRMC);
EXEC SQL ROLLBACK WORK release;
Exit (1);
}


(6) Error detection and recovery
When using SQL statements and pro*c to manipulate a database, there are often field null values, unconditional deletion, no line return, data overflow and truncation, which can be detected with SQLCA and indicator variables.

1 Structure of the SQLCA
The SQLCA structure in the PRO*C program is as follows:
STRUCT sqlca{
Char Sqlcaid[8];
Long sqlabc;
Long Sqlcode;
struct{
Unsigned Sqlerrm1;
Char SQLERRMC[10];
}SQLERRM;
Char Sqlerrp[8];
Long Sqlerrd[6];
Char Sqlwarn[8];
Char Sqlext[8];
}
which
1) SQLCA.SQLERRM.SQLERRMC: with SQLCA. Sqlcode the error text.
2 SQLCA.SQLERRD: Current Oracle state, only sqlca.sqlerrd[2] meaningful, representing the number of rows processed by DML statements.
3) Sqlca.sqlwarn: Provides conditional information that may be encountered.

After each execution of an SQL statement, Oracle puts the return result in Sqlca, except for the statement.
Use Sqlca to view the execution results of the SQL statement. There are often three kinds of results:
= 0: Execution success;
SQLCA. Sqlcode= >0: The state value of the execution success;
<0: Failed, continue execution not allowed.

2 indicator variable
Indicator variables are sometimes also called indicator variables. Indicates that the variable is associated with a primary variable, indicating the return of the primary variable.
= 0: The return value is not empty, is not truncated, and the value is placed in the main variable;
return value = >0: The return value is NULL, ignoring the value of the primary variable;
<0: The primary variable is not long enough to be truncated.
Use indicator variables to note:
L cannot use indicator variables in the WHERE clause. The Null property is used to test for null values.
For example, the following clauses:
SELECT ...
From ...
WHERE ename is NULL;
is right, and
WHERE ename=:peme:peme1
Is wrong.
L indicates that the variable is 1 before inserting a null value
L can output null values.

3 Whenever statement
Whenever is a description statement that does not return Sqlcode, but only specifies the relevant measures according to the return code in the SQLCA. Format is
EXEC SQL whenever [sqlerror| Sqlwarning| Notforund]
[stop| continue| goto< label >];
which
(1) [stop| continue| The default value for got< label is continue.
(2) sqlerror:sqlca.sqlcode<0;
(3) sqlwarnign:sqlca.sqlwarn[0]= "W";
(4) notfound:sqlca.sqlcode=1403;
A procedure to illustrate the use of whenever is given below:
EXEC SQL BEGIN deelare section;
VARCHAR UID[20];
VARCHAR PASW[20];
......
EXEC SQL end DECLARE section;
EXEC SQL INCLUDE SQLCA;
Main ()
{
......
EXEC SQL whenever SQLERROR GOTO ERR;
EXEC SQL connect:uid/:pwd;
......
EXEC SQL DECLARE CSOR1 CURSOR for
Select〈 Field
Form〈 table
EXEC SQL OPEN CSOR1;
Sql
......
EXEC SQL whenever not FOUND GOTO good;
for (;;)
EXEC SQL FETCH csor, into ...
Good:
......
printf ("\ nthe query ends \ n");
EXEC SQL Close C1;
EXEC SQL whenever SQLERROR CONTINUE.
EXEC SQL COMMIT WORK Release:
Exit ();
Printf ("\n%70s|n", SQLCA.SQLERRM.SQLERRMC);
EXEC SQL ROLLBACK WORK Release:
Exit (1);
}

(7) Dynamic definition statement
The SQL statement is divided between dynamic definition statements and static definition statements:
(1) Static definition statement: SQL statement in advance into Pro*c, after the precompiled compiler to form the target program *. The BOJ then executes the target program in advance.
(2) Dynamic definition statement: Some statements can not be embedded in the PRO*C program, according to the program, the user himself from the input device (such as terminal) real-time input to execute the SQL statement.
Dynamic definition statements are:
L EXECUTE IMMEDIATE;
L PREPARE and execute;
L Prepare with fetch and OPEN;
L Bind and define descriptor.

1. EXECUTE Immediate Statement
This statement represents immediate execution and returns execution results only to SQLCA, with no additional information. For example:
EXEC SQL BEGIN DECLARE section;
VARCHAR abcd[89];
VARCHAR DEAY[20];
EXEC SQL end DECLARE section;
/** output string to ABCD **/
EXEC SQL EXECUTE IMMEDIATE:ABCD;
Attention:
1 EXECUTE immediate can only run dynamic statements with one parameter. Where ABCD is a parameter, not a keyword.
2 the prerequisite for using EXECUTE immediate is that the SQL statement cannot contain the primary variable, and the SQL statement cannot be a query statement.
3 any master variable can be used as the parameter of the execute immediate, and the string can also be used as the primary variable.

2. Prepare and Execute statements
This statement indicates precompilation/execution. This statement can be precompiled one time and executed multiple times. The syntax is:
EXEC SQL prepare〈 statement name 〉from: Master variable;
EXEC SQL execute〈 statement name 〉[using: replace main variable];
The prepare statement does two things:
(1) Pre-compiling SQL statements;
(2) Give the statement name of the SQL statement.
Attention:
L SQL statements cannot be query statements;
L prepare and execute can contain master variables;
L prepare cannot be executed multiple times.
For example:
#define USERNAME "SCOTT."
#define PASSWORD "TIGER"
#include
EXEC SQL INCLUDE Sqlca;
EXEC SQL BEGIN DECLARE section;
Char * USERNAME=USERNAME;
Char * PASSWORD=PASSWORD;
VARCHAR sqlstmt[80];
Int Emp_number;
VARCHAR emp_name[15];
VARCHAR JOB[50];
EXEC SQL end DECLARE section;
Main ()
{
EXEC SQL whenever SQLERROR goto:sqlerror;
EXEC SQL Connect:username identified by:p Assword;
sqlstmt.len=sprintf (Sqlstmt.arr, "INSERT into EMP" (empno,ename,job,sal)
VALUES (: V1,:V2,:V3,:V4) ");
Puts (Sqlstmt.arr);
EXEC SQL PREPARE S from:sqlstmt;
for (;;)
{
printf ("\nenter Employee Number:");
scanf ("%d", &emp_number);
if (emp_number==0) break;
printf ("\nenter Employee Name:");
scanf ("%s", &emp_name.arr);
Emp_name.len=strlen (Emp_name.arr);
printf ("\nenter Employee Job:");
scanf ("%s", Job.arr);
Job.len=strlen (Job.arr);
printf ("\nenter Employee Salary:");
scanf ("%f", &salary);
}
EXEC SQL EXECUTE S using:emp_number,:emp_name,:job,:salary;
}


3. FETCH statements and OPEN statements
The set of dynamic statements, the FETCH statement and the Open statement, operate on the cursor, and the execution process is as follows:


Prepare〈 statement name 〉from〈 main variable string;


Declare〈 cursor name 〉for〈 statement name;



Open〈 cursor name 〉[using: Replace variable 1[: Replace variable ...]


Fetch〈 cursor name 〉into: Main variable 1[,: Main variable 2 ...]



Close〈 cursor Name


Attention:
L SQL statements allow query statements;
The column names in the SELECT clause cannot be dynamically changed and can only be preset;
The l where and ORDER BY clauses can dynamically change conditions.

Compilation and operation of Pro*c

1.
The PRO*C program is preprocessed with Oracle precompiled Proc, which translates the SQL language embedded in the source program into the C language and produces a file that the C compiler can compile directly. The file name extension is
. C
2. Compile the file with the. c extension with the C language compiler cc, generating the target code file with the extension. o
3. Use the make command to connect to the target code file to generate a running file
For example: Compile and run the example.pc above
PROC iname=example.pc
CC example.c
Make Exe=example objs= "EXAMPLE.O"
Example