Use of Pro * C

Use of Pro * COverview of a Pro * C program:
1. What is a pro * C program?
There are three methods to access the database in the Oracle database management and system;
(1) use SQL * Plus, which has SQL commands to access the database through interactive applications;
(2) applications developed by application development tools in the fourth generation language access the database. These tools include SQL * froms, Ql * reportwriter, and SQL * menu;
(3) access using SQL language or Oracle database function calls embedded in the third generation language.
Pro * C is one of the third development tools. It perfectly integrates Procedural Language C with non-procedural language SQL, and has complete process processing capabilities, it can also complete the processing tasks of any database, allowing users to program various types of reports. The SQL language can be embedded in the Pro * C program. These SQL languages can be used to dynamically create, modify, and delete tables in the database, you can also query, insert, modify, and delete rows in a database table, and commit and roll back transactions.
PL/SQL blocks can be embedded in Pro * C programs to improve application performance, especially in network environments, which can reduce the total overhead of network transmission and processing.

2. program structure of Pro * C
In general, the Pro * C program is actually a C program embedded with SQL statements or PL/SQL blocks, so its composition is very similar to the C program. However, because it is embedded with SQL statements or PL/SQL blocks, it also contains different components.

Ii. Structure of the Pro * C program

Each Pro * C program consists of two parts: (1) application header; (2) Application body
The application first defines the relevant variables of the Oracle database and is ready to manipulate the Oracle database in the C language. The application body is basically composed of SQL statement calls of Pro * C. Queries select, insert, update, delete, and other statements.
Application Composition Structure:
Exec SQL begin declare Section
(Definition of SQL variables)
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 start part of Pro * C. It includes the following three parts:
L c variable description;
L description of SQL variables (declare );
L SQL Communication zone.

(1). Declare (description)
The description section describes the SQL variables of the program. The definition section starts with exec SQL begin declare section; and ends with exec SQL end declare section. It can appear in the Main Department of the program or in the local
L description and use of SQL Variables
The data types that can be specified for SQL variables in the description section are shown in the table:
Data Type
Char Single Character
Char (n) n character array
Int integer
Short short integer
Long
Float Single-precision floating point number
Double double-precision floating point number
Varchar variable-length string description
These data types are actually C data types, in which varchar is regarded as an extension of the C data type. This will be discussed later.
Pay attention to the following points when using SQL variables:
L must be clearly defined in the description Section
L use the same case format as its definition
L when using an SQL statement, you must add a ":" (colon) before it, but you do not need to add a colon in the C statement.
L cannot be a reserved word in SQL commands.
L can contain directive variables.
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 variables
The indicator variable is actually a type of SQL variable, which is used to manage the host variables associated with it (that is, variables that are input or output in SQL statements ). Each host variable can define an indicator variable, which is mainly used to process null values)
The description of indicator variables is basically the same as that of common SQL variables, but must be defined as a 2-byte integer, such as short and Int. When using an SQL statement, you must add ":" (colon) before it, and it must be attached to its associated host variable. It can be used independently in the C statement. When the indicator variable is-1, it indicates a null value. 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 (: dept-number: Ind-num,: dept-name );
Specifically, Ind-num is the indicator variable of dept-number. When the input dept-number value is 0, a null value is inserted into the deptno column of the dept table.
(3) Description and use of pointer SQL Variables
The pointer SQL variables must be described in the declare section before being referenced. The format is the same as that of C. When using an SQL statement, you must add the prefix ":" (colon) to the pointer name without the asterisk ). In a C statement, it is used as a pointer variable in C.
(4) Description and reference of array SQL changes
When referencing an array in an SQL statement, you only need to write the array name (add a colon Before the name), and no subscript is required. In a C statement, it is used as an array variable in C language.
Array can greatly reduce network transmission overhead. If you want to insert 100 rows of data to a table without an array, you must repeat the data for 100 times. After referencing the data, you only need to execute one insert statement to insert the data at a time. For example:
Exec SQL begin declare section;
Int emp_number [2, 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 =: dept_number;
Pay attention to the following points when using Arrays;
L pointer array not supported
L only supports one-dimensional arrays, and EMP-name [100] [15] is regarded as a one-dimensional string.
L The maximum dimension of the array is 32767.
L when multiple arrays are referenced in an SQL statement, these arrays have the same dimension.
L simple SQL variables and array SQL variables cannot be mixed in values, set, into, or where subnames.
L arrays cannot be initialized in the delare section.
For example, the following reference is invalid.
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 being referenced.
Length, for example:
Exec SQL begin declare section;
Int book-number;
Varchar book-name [50];
Exec SQL end declare section;
During preparation, book-name is translated into a structure variable in C language;
Struct {unsigned short Len;
Unsigned chart arr [20];
} Boo-name
From this we can see that the varchar variable is actually a structural variable containing the length members and the number of members in the group. When using an SQL statement, the structure name prefixed with a colon should be referenced, and the structure member should be referenced in the C statement without subscript.
When the varchar variable is used as the output variable, it is automatically set by Oracle. When used as the input variable, the program should first store the string into the array member, and its length should be saved to the length member, and then reference it in the SQL statement. For example:
Main (
{.......
Scanf ("90 s, 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 zone
The SQL Communication zone is described using the following statements:
Exec SQL include sqlca;
This section provides records of success or failure and error handling for running programs.

Sqlca Composition
Sqlca is a variable of the structure type. It is an interface between Oracle and applications. When executing the Pro * C program, Oracle saves the status information of each embedded SQL statement execution to sqlca. Based on this information, it can determine whether the SQL statement execution is successful and the number of lines processed, error information, as shown in the following table:
Struct sqlca
{Char sqlcaid [8]; ---- à identifies the communication zone
Long sqlabc; --- à length of the Communication zone
Long sqlcode; --- à keep the status code of the recently executed SQL statement
Struct {unsigned short sqlerrml; ----- à message text Length
} Sqlerrm;
Char sqlerrp [8];
Long sqlerrd [6];
Char sqlwarn [8];
Char sqlext [8];
}
Struct sqlca;
Among them, sqlcode is the most commonly used in programs. It retains the status code of the recently executed SQL statement. The programmer processes the status code accordingly. The status code values are as follows:
0: indicates that the SQL statement is correctly executed without errors or exceptions.
> 0: Oracle executes this statement, but encounters an exception (if no data is found ).
<0: indicates that the SQL statement is not executed by Oracle due to database, system, network, or application errors.
When such errors occur, the current transaction should generally be rolled back.

2. Application body
In the Pro * C program, SQL statements and C statements can be freely written together and SQL variables can be used in SQL statements. The syntax for writing embedded SQL statements is:
L start with the keyword exec SQL
L end with a statement terminator (semicolon) in C Language
SQL statements are mainly used to deal with databases. C language programs are used for control, input, output, and data processing.
(1) connect to the Oracle database
Before accessing the database, you must connect the program to the Oracle database. Log on to Oracle. The connected command should be the first executable command of the application. The connection command format is as follows:
Exec SQL CONNECT: <User Name> identified by: <password>
Or exec SQL CONNECT: <User Name>/<password>
When using the preceding two formats for login, you must first define
SQL variables, and set them before running connect. Otherwise, the logon will fail. 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: the user name and password cannot be directly written into the connect statement, or the strings enclosed by quotation marks (') are in the connect statement. The following statement is invalid.
Exec SQL connect Scott inentified by tiger;
Exec SQL connect 'Scott 'identified by 'tiger ';
(2). insert, update, and delete
I have already discussed the SQL language in detail. I will not illustrate it here.
(3) Use of database queries and cursors
In Pro * C, there are two types of queries:
L returns a query of a single row or a specified number of rows;
L multi-row query is returned. This query requires the use of a cursor to control each row or each group (the main variable uses an array ).
1) returns a query of a single row or a specified number of rows.
The SQL SELECT statement in Pro * C consists of the following clauses:
Select
Into
From
Where
Connect
Union
Intersect
Minus
Group
Having
Order
The query conditions in the WHERE clause can be a set of one or more attributes, and the primary variables that are assigned values during execution can also be placed in the WHERE clause. the primary variable used in the WHERE clause is called the input primary variable. For example:
Select empno, job, Sal
Intoname, job, Sal
From EMP
Where empno = empno;
If no row is found, sqlca. sqlcode returns "+ 1403", indicating "not found ".
The main variable in the into clause is called the output main variable, which provides the information required for query.
Before any item is sent to the primary variable, Oracle is required to convert these items into the data type of the primary variable. Digits are truncated (for example, 9.23 is converted to 9 ).
If only one row is returned for a query, you do not need to use a cursor. You only need to add an into clause to the SELECT statement. In terms of semantics, The into statement has multiple primary variables output in the query before the from statement. If the number of expressions in the select clause is not equal to the number of primary variables in the into clause, set sqlca. sqlwarn [3] To "W ".

2) use of multi-row queries and cursors
If the query returns multiple rows or does not know how many rows are returned, use the SELECT statement with an oracle cursor (cursor.
A cursor is a work area where Oracle and Pro * C store query results. A cursor (named) is associated with a select statement. The operation cursor has four commands: (1) Declare cursor; (2) Open cursor; (3) Fetch; (4) Close cursor.

A. Define a cursor
A cursor must be defined before it can be used. Syntax:
Exec SQL declare <cursor Name> Corsor
Select <column> 〉
From <Table> 〉
For example:
Exec SQL declare csor, cursor
Select ename, job, Sal
From EMP
Where deptno =: deptno;
When a cursor associated with the query is assigned, multiple rows can be returned from the database when the SELECT statement queries the EMP. These rows are an active area of the cursor.
Note:
1) defining a cursor must be completed before the cursor operation;
2) Pro * C cannot reference unspecified cursors;
3) after a cursor is defined, its scope of action is the entire program. So it is wrong for a program to define two identical cursors at the same time.

B. Open the cursor.
The open statement used to open a cursor is mainly used to input the content of the primary variable. These are the primary variables used in the WHERE clause. The statement for opening a cursor is exec SQL open <cursor Name> 〉
When the cursor is enabled, you can retrieve more than one row of results from the related query. All rows that meet the query criteria form a set called a "cursor activity set ". Through the fetch operation, each row or group in the activity set returns one by one. After the query is complete, the cursor can be closed. :
Define the cursor: declare
Start query: select
Open cursor: Open
Fetch data from activity set: Fetch
Query completed
Close cursor: Close

Note: 1) the cursor is in front of the first row of the active set;
2) If the input main variable is changed, the cursor must be reopened.

C. Fetch data
The process of retrieving a row or a group of results from an activity set to the output primary variable is called data retrieval. The output primary variable is defined in the Data fetch statement. The data retrieval statement is as follows:
Exec SQL fetch <cursor Name> into: primary variable 1, primary variable 2 ,......
Fetch process:
Query Result? ------------ Cursor
Query Result fetch-> output to current
.....

The query result is the query result that meets the query conditions. Pay attention to the following points when using fetch:
L The cursor must be defined before being opened.
L The fetch statement is executed only after the cursor is opened.
L The fetch statement is executed once, and the data is retrieved from the current row or the current group once. The next row or the next group is moved up once. Each row or group referred to by a cursor is the current row or group, and each fetch is the data of the row or group specified by the cursor.
L when the cursor activity set is empty, orcle returns a sqlca. Sqlca (= 1403 ).
L if you want this cursor to be operated again, you must close it before opening it.
L a memory space can be opened up in the C program to store the operation results, so that the opened space can be used to flexibly manipulate the query results.

D. Close the cursor.
After all rows in the active set are retrieved, you must close the cursor to release resources related to the cursor.
The format of closing the cursor is:
Exec SQL close cursor name;
For example:
Exec SQL close C1;
Oracle V5.0 supports the SQL format "Current of cursor ". This statement points to the last row retrieved from a cursor for modification and deletion. This statement must be used after a get operation. It stores a rowid and uses it.

(4). Example
Exec SQL declare salespeople cursor
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 Methods and Applications
Embedded SQL and interactive SQL have the following differences in form:
1) Add the prefix "Exec SQL" before the SQL statement. The purpose of this small difference is that it is easy to identify during pre-compilation so that each SQL statement can be processed as a high-level language.
2) Each SQL statement is divided into two categories: descriptive statements and executable statements. Executable statements can be divided into four categories: Data Definition, data control, data manipulation, and data retrieval.
Executable SQL statements are written in the executable area of the advanced language, and descriptive SQL statements are written in the descriptive area of the advanced language.
For example, to create a table structure named book in the Pro * C program, the process 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 and features high-speed C language. Therefore, it can complete tasks that cannot be completed by Oracle products, for example, output results in the next fixed special format.
SQL nested source code example
# Unclude <stdio. h>
Typedef char asciz [20];
Exec SQL begin declare section;
Exec SQL type asciz is string (20) 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
Select empno, ename, Sal from EMP;
Exec SQL open C1;
Num_ret = 0;
For (;
{
Exec SQL whenever not found do 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 <n; I ++
Printf ("%-9d %-8 S % 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 %. 70 s/n ", sqlca. sqlerrm. sqlerrmc );
Exec SQL rollback work release;
Exit (1 );
}


(6) error detection and recovery
When using SQL statements and Pro * C to operate databases, null fields, unconditional deletion, row-free return, data overflow, and truncation often occur, this phenomenon can be detected using sqlca and indicator variables.

1 sqlca Structure
In the Pro * C program, the sqlca structure 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];
}
Where:
1) sqlca. sqlerrm. sqlerrmc: With sqlca. Sqlcode error body.
2) sqlca. sqlerrd: The current Oracle status, which is meaningful only to sqlca. sqlerrd [2] and indicates the number of rows processed by the DML statement.
3) sqlca. sqlwarn: Provides information about possible conditions.

After each SQL statement is executed, Oracle puts the returned results into sqlca, except for the description statements.
You can use sqlca to view the execution results of SQL statements. There are usually three results:
= 0: Execution successful;
Sqlca. sqlcode => 0: status value of successful execution;
<0: Failed. execution cannot be continued.

2 indicator variables
An indicator variable is also called an indicator variable. It indicates that the variable is associated with a primary variable and indicates the response of the primary variable.
= 0: the return value is not empty and is not truncated. The value is placed in the main variable;
Return Value => 0: the return value is null. Ignore the value of the main variable;
<0: if the length of the main variable is insufficient, it is truncated.
Note the following when using indicator variables:
L indicator variables cannot be used in the WHERE clause. Use the null attribute to test the null value.
For example, the following clause:
Select...
From...
Where ename is null;
Is correct, and
Where ename =: peme: peme1
Yes.
L indicates that the variable is-1 before the null value is inserted.
L can output null values.

3 whenever statement
Whenever is a description statement that does not return sqlcode. It only specifies relevant measures based on the return code in sqlca. Format:
Exec SQL whenever [sqlerror | sqlwarning | notforund]
[Stop | continue | goto <number>];
Where
(1) The default value of [Stop | continue | got <label>] is continue.
(2) sqlerror: sqlca. sqlcode <0;
(3) sqlwarnign: sqlca. sqlwarn [0] = "W ";
(4) notfound: sqlca. sqlcode = 1403;
The following is a program to describe whenever usage:
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
Select <field> 〉
Form <Table> 〉
Exec SQL open csor1;
SQL
......
Exec SQL whenever not found goto good;
For (;;)
Exec SQL fetch csor, ......
Good:
......
Printf ("/n query end/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
SQL statements include dynamic and static definition statements:
(1) static definition statement: the SQL statement is pre-compiled into Pro * C and the Target Program * is formed after pre-compiler compilation *. BOJ, and then run the program Pre-run.
(2) Dynamic Definition Statement: some statements cannot be embedded in the Pro * C program beforehand. You must enter the device (such as the terminal) according to the program running status) enter the SQL statement to be executed in real time.
The dynamic definition statements include:
L execute immediate;
L prepare and execute;
L prepare, fetch, and open;
L bind and define descriptor.

1. Execute immediate statement
This statement indicates immediate execution and only returns the execution result to sqlca without any other 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;
Note:
1) execute immediate can only run dynamic statements with one parameter. Here, ABCD is a parameter, not a keyword.
2) the prerequisite for execute immediate is that SQL statements cannot contain primary variables, and SQL statements cannot be query statements.
3) any primary variable can be used as the parameter of execute immediate, or a string can be used as the primary variable.

2. prepare and execute statements
This statement indicates "pre-compilation/execution ". This statement can be pre-compiled once and executed multiple times. Syntax:
Exec SQL prepare <statement Name> from: Main variable;
Exec SQL execute <statement Name> [using: replace primary variables];
The prepare statement does two things:
(1) pre-compile SQL statements;
(2) Name of the SQL statement.
Note:
L The SQL statement cannot be a query statement;
L prepare and execute can contain primary variables;
L prepare cannot be executed multiple times.
Example: <example. PC>
# Define username "Scott"
# Define password "tiger"
# Include <stdio. h>
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: password;
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 and open statements
The dynamic statements of fetch and open are used to operate the cursor. 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: primary variable 1 [,: primary variable 2…]
Close <cursor Name> 〉

Note:
L SQL statements allow the use of query statements;
L The column names in the select clause cannot be changed dynamically and can only be preset;
L The where and order by clauses can dynamically change conditions.

1. Compilation and running of Pro * C

1. Use the Oracle pre-compiler proc to pre-process the Pro * C program. the compiler translates the SQL language embedded in the source program into C language and generates a file that can be directly compiled by the C language compiler. The file extension is. C.
2. Use C language compiler CC to compile a file with the extension. C to generate a target code file with the extension. o
3. Use the make command to connect to the target code file and generate the executable file.
For example, compile and run the preceding example. PC.
Proc INAME = example. PC
CC example. c
Make EXE = example objs = "example. O"
Example