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 to interactively access the database; (2) Use the fourth generation language application Development tool development application access database, these tools include SQL * froms, QL * ReportWriter, SQL * MENU, etc .; (3) Accessing using SQL language or Oracle library function embedded within the third generation language. Pro * c belongs to one of the third development tools, which combines process-based language C and non-processed language SQL, with complete process processing capabilities, and completes any database processing tasks, users Various types of reports can be completed by programming. In the Pro * C program, you can embed the SQL language. Use these SQL languages to complete dynamically, modify, and delete the tables in the database, you can also query, insert, modify, and delete lines in the database table, you can also implement transaction And roll back. In the Pro * C program, the PL / SQL block can also be embedded to improve the performance of the application, especially in a network environment, can reduce the total overhead of network transmission and processing.
2. Pro * C The program structure diagram is popular. The Pro * C program is actually embedded with a SQL statement or a PL / SQL block C program, so its composition is similar to the C program. But because of its embedded SQL statement or PL / SQL block, it also contains different ingredients. In order to let everyone have a sense of sensibility to Pro * C, the two differences are compared to the following: C Full variables Description C Source Process 1: With the function K. Function 2: That with functions K.
C Partial Variable Description Function K Executive Statement
External variable of the application header Description External Description Segment (Oracle Variable Description) Communication Area Description
Pro * C Source Process 1: With Function K. Function 2: That with functions K. C Local Variable Description Professional Internal Description Part Interior Description Segment Communication Area Description Function K C can perform statement executable statement
Or PL / SQL block
two. Composition of Pro * C Programs
Each Pro * C program includes two parts: (1) application header; (2) Application application first defines the relevant variables of the Oracle database, which is ready to manipulate the Oracle database in the C language. The application body is basically called by Pro * C SQL statement. Mainly refer to SELECT, INSERT, UPDATE, DELETE and other statements. The composition structure of the application is shown in the figure: Application header
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:
1. The first application of the application header is the beginning of Pro * C. It includes the following three parts: L C variable description section; L SQL variable description section (DECLAR portion); l SQL communication area.
(1). Declare section (description section) Description section describes the SQL variable of the program, the defined portion is ended with Exec SQL Begin Declare Section; ending with Exec SQL End Declare Section; It can appear in the main unit of the program, or the description of the local L SQL variable and the data type specified in the description segment can be specified for the SQL variable, as shown: Data Type Description Charchar (N) Int Short Long Float Double Varchar Single-character n character array integer short plural single-precision floating point double precision floating point number variable length string These data types are actually the data type of C language, where varchar is expanded as C data type. This is talking later. The use of SQL variables should be aware of the following: l You must explicitly define that l must be used in the description, which must be used in the SQL statement, but it must be added to the SQL statement, but The citation is quoted in the C statement. l Can't be the reserved word in the SQL command. l You can take an indication variable. For example: Exec SQL Begin Declare Sections; VARCHAR Program [30]; int PORGSAL, PEMPNO; Exec SQL End Declare Section;
Exec SQL SELECT ENAME, SAL INTO: Program,: ProgSal from Emp where Empno =: Pempno
(2). Description of the indicator variables and reference indication variables are actually a class of SQL variables, which are used to manage host variables associated therewith (ie, when the SQL statement is incoming or output). Each host variable can define an indicator variable, which is mainly used for processing a null value (NULL) indicator variable, which is basically the same as general SQL variables, but must define 2 bytes of integers, such as short, int. When referenced in the SQL statement, it should also be added ":" (colon), and must be attached to the associated host variable, can be used independently in the C statement. When the indicator variable is -1, a null value is indicated. 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); where IND - NUM is the indicator variable of DEPT - Number. When the input DEPT-Number value is 0, an empty value is inserted to the DEPTNO column of the DEPT table. (3). Description of the pointer SQL variable and the use of the pointer SQL variable must first be described in the DECLARE section before reference. It illustrates the format as C language. When referenced in the SQL statement, the pointer is prefixed before the name is ":" (colon) without adding "*". Usage in the C statement like a pointer variable in the C language. (4). Instructions for array SQL change and references When reference arrays in the SQL statement, only write an array name (before the name of the name), do not need to write the subscript, in the C statement like the array variables in the C language. Using an array can greatly reduce network transmission overhead. To insert 100 row data to a table, if there is no array, you must repeat 100 times, and after reference, you only need to execute an insert statement will insert it. For example: EXEC SQL BEGIN DECLARE Section; int EMP_NUMBER [100]; char EMP_NAME [100] [15]; float salary [100], communication [100]; int debt_number; exec SQL End Declare section; ... .exec SQL SELECT EMPNO, ENAME, SAL, COMMINTO: EMP_NUMBER,: EMP_NAME,: SALIN:: CommsSIONFROM Empwhere Deptno =: dept_number; When using array, pay attention to the following points; L does not support the argument array L only support one-dimensional array, and EMP-NAME [ 100] [15] It is considered to be a one-dimensional string L array maximum number of dimensions 32767L to reference multiple arrays in a SQL statement, and these array dimensions should be the same L VALUES, SET, INTO, or WHERE sub-names, not allowed Mixing simple SQL variables and array sql variables cannot be initialized in the delere section, such as: 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, SALINTO: EMP - NUM [I],: Emp - Name [i],: SALAG [I] from EMP (5) Experiments of Pseudo Type VARCHAR and reference VARCHAR variables must also be explained before reference Segment Description, the maximum length of the string must be pointed out, such as exec SQL Begin Declare section; int book - number; varchab / name [50]; exec SQL End Declare section; during precuction, book - name is translated A structural variable in the C language; Struct {UNSIGNED SHORT LEN; UNSIGNED Chart Arr [20];} Boo - Name It is thus seen that the varchar variable is actually a structural variable that contains length members and array members. When referenced in the SQL statement, reference should be referenced to the structure name prefixed with a colon, and the standard member is referenced in the C statement. When the VARCHAR variable is automatically set by Oracle, when as an input variable, the program should put the string into an array member, and the length is deposited in the length member, and then reference it 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 SQL communication area is described by following statements: Exec SQL include SQLCA; this section provides the failure and failure of user running programs and Error handling. SQLCA's composition SQLCA is a structural type variable, which is an interface of Oracle and applications. When executing the Pro * C program, Oracle stores the status information performed by the SQL statement into SQLCA, according to these Information, can judge whether the execution of the SQL statement is successful, the number of lines, error information, etc., the composition is shown in the table: struct sqlca {char sqlcaid [8]; ---- à à identity communication area long sqlabc; --- à Communication area Long Sqlcode; - à Reserved the status code of the 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 common in the program, it retains the status code of the recently executed SQL statement. Programmer makes according to these status code Processing. These status code values are as follows: 0: Indicates that the SQL statement is executed correctly, no errors and exceptions have occurred.> 0: Oracle executes the statement, but encounter an exception (if any data is found). <0: Due to the error of the database, system, network, or application, Oracle does not execute the SQL statement. When such an error occurs, the current transaction should generally be rolled back.
2. Application Body In the Pro * C program, the SQL statement and the C statement can be freely mixed, and can use SQL variables in the SQL statement, the writing method of embedded SQL statement is: l Start L with keyword exec SQL C language statement terminology (semicolon) The role of the SQL statement is mainly used to deal with the database. The C language program is used to control, input, output, and data processing, etc.. (1) Connecting to the Oracle database Before you get the data inventory, you must first connect the program to the Oracle database. Log in to Oracle. The connected command should be the first executable command of the application. The connection command format is as follows: Exec SQL Connect:
If you have determined that the query only returns a row, you don't have to use a cursor, you can only add an INTO clause to the SELECT statement. How many output primary variables have multiple output main variables in the query in the query in the query in the query in the query in the query. If the number of expressions in the SELECT item is not equal to the number of main variables in the INTO clause, set SQLCA.SQLWARN [3] to "W". 2) Multi-line query and cursor use If the query returns multiple lines or does not know how many rows returned, use the SELECT statement with Oracle cursor (CURSOR). The cursor is Oracle and Pro * C store the work area of the query results. A cursor (named) is associated with a SELECT statement. Operating cursor has 4 commands: (1) DECLARE CURSOR; (2) Open cursor; (3) fetch; (4) Close Cursor.
A. Defining a cursor a cursor must first be defined first before you can use it. Syntax: EXEC SQL DECLARE B. Open the cursor Open the Open statement of the cursor is mainly used to enter the content of the main variable, which is mainly the main variable used in WHERE. Open a cursor is: Exec SQL Open Start inquiry: SELECT Open Cursor: Open Jump data from activity: Fetch Query completion Close Cursor: Close Note: 1) The first line of the cursor is in the first line of the active set; 2) If you change the input main variable, you must re-open the cursor. C. Take the data from the active set to the process of sending a row or a process of sending the results into the output main variable. The definition of the output main variable is taken in the data statement. Take the statement of the data as follows: Exec SQL Fetch The results of the query result cursor FETCH query results are output after the cursor is opened to the current ...... The query result shown in the figure refers to the result of the query condition. Use fetch to pay attention to the following: l The cursor must be defined first. l Only the FETCH statement is performed only after the cursor is opened. l The FETCH statement is executed once, from the current line or the current group, the next line or the next set is moved up. The row or group of the cursor is the current line or the current group, and Fetch is the data specified by the target each time. l When the cursor activity is vacant, Orcle returns a SQLCA. SQLCA (= 1403). l If you want this cursor to operate, you must first turn it off again. l You can open a memory space in the C program to store the results of the operation, so you can use the open space to flexibly manipulate the results of the query. D. After closing the cursor, you must close the cursor to release resources related to the cursor. Close the format of the cursor is: Exec SQL Close Tagname; for example: EXEC SQL Close C1; Oracle V5.0 supports SQL format "CURRENT OF CURSOR". This statement will point to the latest rows in a cursor for modifying and deleting operations. This statement must be used after the operation, it is equivalent to storeing a ROWID and uses it. (4). Examples EXEC SQL DECLARE SALESPEOPLE CURSOR FOR SELECT SSNO, NAME, SALARYFROM EMPLOYEEWHERE DNAME = 'Sales'; EXEC SQL OPEN SALESPEOPLE; EXEC SQL FETCH SALESPEOPLEINTO: SS,: NAME,: SAL; EXEC SQL CLOSE SALESPEOPLE; (5) SQL nested method and application embedded SQL and interactive SQL in the form There is a difference: 1) Increase prefix "Exec SQL" before the SQL statement, this small difference is that the purpose is easy to pre-translate Identify to process each SQL as a high-level language. 2) Each SQL statement is divided into two major classes of the illustrative statement and the executable statement. The executable statement is divided into data definition, data control, data manipulation, and data retrieval. The executable SQL statement is written in the executive of the advanced language; the illustrative SQL statement is written in the descriptive place in the advanced language. For example, in the Pro * C program, a table structure called Book is created, the process is as follows: #include SQL Nested Source Sample #unclude (6) Error detection and recovery When using SQL statements and Pro * C, there is often a field null value, unconditional deletion, no row return, data overflow, and truncation, this phenomenon can use SQLCA and instructions. Detector variables to detect. 1 SQLCA structure In the Pro * C program, the SQLCA structure is as follows: struct sqlca {char SQLCAID [8]; long sqlabc; long sqlerrm1; 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's error body. 2) Sqlca.sqlerrd: The current Oracle status, only SQLCA.SQlerRD [2] meaningful, indicating the number of rows processed by the DML statement. 3) Sqlca.sqlwarn: Provides the conditions that may encounter. After each SQL statement is executed, Oracle puts the return result in SQLCA, but except the statement. Use SQLCA to view the execution result of the SQL statement. There are often three results: = 0: Successful execution; sqlca.sqlcode => 0: Perform a status value of success; <0: Failure, not allowed to continue. 2 Indicator Variable Indicator Variables Sometimes the indication variables. Indicates the variables associated with a primary variable, pointing out the return of the main variable. = 0: The return value is not empty, not truncated, the value is placed in the main variable; return Value => 0: The return value is empty, ignores the value of the primary variable; <0: The main variable length is not cut off. Use indication variables to note: l You cannot use the indication variable in the WHERE clause. Test null values with NULL properties. For example, the following subsequence: select ... from ... where ename is null; is correct, and where ename =: peme: peme1 is wrong. l Indicates that the variable is -1L to output a null value before the empty value is inserted. 3 WHENEVER statement WHENEVER is an explanatory statement, does not return SQLCode, just specify the relevant measures based on the return code in SQLCA. Format EXEC SQL WHENEVER [SQLERROOR | SQLWARNING | NOTFORUND] [STOP | Continue | goto , for,, After the cursor cursor, when the SELECT queries Emp, you can return multiple lines from the database, which is a CURSOR's activity area. Note: 1) Define the cursor must be done before the cursor operation; 2) Pro * c cannot reference the undefined cursor; 3) After the cursor is defined, its scope is the entire program. So for a program, it is wrong to define two identical cursors.
