Pro*c Foundation

Declaration of SQL variable: EXEC SQL BEGIN DECLARE section; Type variable name "Length" Varchar2 serv_number[ One]; Where you can define C variable exec SQL END DECLARE section; EXEC SQL INCLUDE SLQCA; Describes the SQL communication area, which records the state of executing each SQL statement. Error information processing exec SQL whenever SQLERROR do SQLERROR (); Indicates how an error should be handled when an SQL statement is executed. Connect database exec SQL Connect:username identified by:p assword; Embedded SQL statement or PL/the keywords in the SQL block should be capitalized. The SQL variable is defined in the declaration of the variable, allowing only the following statements to be included: (i) The type description statement of the SQL variable (ii) EXEC SQL INCLUDE (iii) EXEC SQL VAR (iv) EXEC SQL Typeexec SQL var and exec SQL type respectively with To the equivalent of SQL variables and external variables. Use the following: EXEC SQL VAR host_variable is type_name[(length)]; can be user-defined type and equivalent:structScreen { ShortLen; Charbuff[ -];} typedefstructScreen graphics; EXEC SQL BEGIN DECLARE section; EXEC SQL TYPE Graphics is Varraw (4000); EXEC SQL END DECLARE section; You can define a user-described type as either a displayed or an implicit pointer, and then use the new type. Reference must be used, such as: EXEC SQL TYPE Graphics Varraw (4000) Reference; Graphics My_raw; You can use the following modifiers to describe the nature of the variable: autoStaticextern Constconstants that are definedvolatilethere is a method that the program does not know (such as through a device) to change the description and application of the value varchar variable: C does not have this type, specifically introduced by this type, called pseudo-type. EXEC SQL BEGIN DECLARE section; VARCHAR User_name ( One); EXEC SQL END DECLARE section; at precompilation time, the variable is translated as:struct{unsigned Shortlen;unsignedChararr[ -];} User_name; Description of the Communication area: Oracle provides two communication areas SQL Communication Area (SQLCA) and Oracle Communication Area (ORACA) Sqlca is a scalar of struct type, is the interface of Oracle and application, executes pro*C, Oracle deposits the state of each embedded SQL execution into the SQLCA;SQLCA structure:structsqlca{Charsqlcaid[8];//identify a SQL communication areaLongSQLBC;//length of the communication areaLongSqlcode; /*There are three possible values for the status code after the most recent SQL execution: 0 correctly ended >0 Oracle executed the statement, but encountered an exception <0 Oracle did not execute the statement, and when such an error occurs, the transaction should be rolled back. */struct{unsigned ShortSQLERRML;//Save the length of text in SQLERRMCUnsignedCharsqlerrmc[ -];//Save Sqlcode encoding for error messages, only apply SQLERRMC if the value of Sqlcode is negative, only 70 characters, if you want to apply more than 70 characters of information, you must use the SQLGLM () function. If Sqlcode executes as 0 o'clock, SQLERRMC is the most recent SQL information. }SQLERRM;Charsqlerrp[8];Longsqlerrd[6];Charsqlwarn[8];Charsqlext[8];} SQLCA; How the communication area is applied: EXEC SQL INCLUDE Sqlca, or copy the above code into C code. The application of SQLGLM, SQLGLM (Message_buffer,&buffer_size,&message_length); Message_buffer: A text buffer that retains the wrong information here, with spaces to complement the space behind. Buffer_size: The length of the buffer. Message_length:oracle the actual length of the error message. Oraca is similar to SQLCA, but more information than SQLCA provides. The Include statement copies a file into a program, similar to the # include definition: EXEC SQL INCLUDE filename, and a copy of filename is used in the program. Disconnect database connection exec SQL COMMIT work RELEASE; error handling method: Exec SQL Whenerve SQLERROR GOTO sqlerror;sqlerror:exec SQL Whenerver Sqler    ROR CONTINUE;       EXEC SQL ROLLBACK; Exit (1); You can also use the method: SQLError ();voidSQLError; EXEC SQL whenever SQLERROR do SQLERROR ();voidSQLError () {EXEC SQL whenerver sqlerror CONTINUE;       EXEC SQL ROLLBACK; Exit (1); } Data not found processing method: EXEC SQL whenever not FOUND GOTO notfound;notfound:printf ("Not found"); SQLCA. sqlerrd[2]//number of rows processed by the recordSelectthe FOR clause is not allowed in the statement. When multiple rows are determined by SELECT, the array can be used, but there must be a mode={ansi| ansi13|ORACLE}, you can use the cursor when the row returned by select is not deterministic. The idea of the cursor: When using the cursor, a buffer is opened, the query result of the select is stored in the buffer, and the pointer is pointed to the first record.           Use of cursor statements: (i) Description of a cursor using DECLARE cursor EXEC SQL DECLARE emp_cursor cursor for SELECT Brand_id,brand_desc From Tb_det_brand WHERE brand_all_id=: Emp_number; Note: The cursor must be in a compilation area throughout.     (ii) Open cursor EXEC SQL open emp_cursor; The function of the Open statement:1. Define a cursor buffer2to retrieve a row that satisfies a condition into a buffer3. Point the cursor to the first row4. Line Calculator sqlca.sqlerrd[2] Qing 0; (c) Extract exec SQL Fetch emp_cursor into:brand,:brand_name; (d) Close cursor exec SQL close emp_cursor; Tip: Strings in Oracle are Null end. Thus, you can use type equivalence implementations. typedefCharAciz;aciz Name;aciz brand; EXEC SQL TYPE Aciz is srting ( -in this way, Aciz is a null-terminated character. Example of cursor use: Main () {....     EXEC SQL whenever SQLERROR do SQLERROR (); //connecting to a databaseEXEC SQL DECLARE emp_cursor cursor for SELECT brand_id,brand_desc from Tb_det_brand WHERE brand_all_id=: Emp_number;     EXEC SQL OPEN emp_cursor;  for(;;) {EXEC SQL whenever not FOUND do Break;         EXEC SQL fecth emp_cursor into:brand,:brand_name; //Processing Information} EXEC SQL CLOSE emp_cursor; Exit (0);} Pro*c Embedding pl/where the SQL block is considered characteristic: you can use the RECODE type Test_type is RECODE (brand_id varchar2 (4) notNULL, Brand_desc varchar2 ( the) ); Embed PL/SQL method exec sql EXECUTE DECLARE---pl/SQL BEGIN END; end_exec; indicator variable: function: Receives a null value from the host program into the database table to output null or truncated values to the host variable use method: For example: Emp_name:ind_emp process: when entering PL/sql block, if the value of the indicator is-1, then pl/SQL automatically assigns null values to the appropriate host variables. When exiting the block, if the value of the host variable is null, assign 1 to the corresponding indicator variable. PL/when only part of an array is used in a SQL block, you can specify the smallest array size: Method: EXEC SQL arraylen Host_array (Array_integer);   EXEC SQL BEGIN DECLARE section; floatsalary[ -]; intm; EXEC SQL Arraylen salary (m); EXEC SQL END DECLARE section; Specify the dimension of the data m= -in the example above, only 30 elements were passed to PL/the SQL block. Method: (i) Start Oracle Communication area EXEC Oracle OPTION (Oraca=YES);(II) designation Release_cursor=yes, used to release pro*c executes post-SQL resources, which will guarantee that after data operations, Oracle does not persist Table analysis locks and subsequent data definition operations do not result in parsing lock errors. EXEC ORACLE OPTION (release_cursor=YES); error handling dynamic SQL Technology Dynamic SQL Method 1: For non-SELECT statements, method: Execute immedite ' insert into ... '; do not allow input of host variables; dynamic SQL Method 2: (For non-select) PRE Pare statement: (Parse and name a dynamic SQL statement) EXEC SQL PREPARE [Statement_name][block_name] from [: host_string][string_literal]; Example: strcpy ( SQL_T1,"DELETE from EMP WHERE emp_name=:v"); EXEC SQL PREPARE p1 from:sql_t1; Execute statement: Example EXEC SQL execute P1 using:ind_emp, Method 1 and Method 2 differ: Method 1 is parsed once, and method two is analyzed once and executed multiple times.              Dynamic SQL Method 3: (for SELECT statements only) Example: EXEC SQL BEGIN DECLARE section; VARCHAR sqlstmt[ the]; intimp; intbrand; intbrand_a; EXEC SQL END DECLARE section; .... Main () {//Constructing dynamic SQLsqlstmt.len=sprintf (Sqlstmt.arr,"Select brand_id,brand_all_id from Tb_det_brand where Brand_all_id=:v"); //analyze the dynamic SQLEXEC SQL PREPARE p1 from:sqlstmt; //Description CursorEXEC SQL DECLARE C1 CURSOR for P1; Imp=1001; ......       //Open CursorEXEC SQL OPEN C1 using:imp; EXEC SQL whenever not FOUND do Break;  while(true)       {             //Extracting DataEXEC SQL FETCH C1 into:brand,:brand_a;           ................         } //Close CursorEXEC SQL CLOSE C1;} Dynamic method Four: Features: Before executing SQL, table entries, number of host variables, type until execution is still unknown. Thus, a complete Oracle call cannot be generated at precompilation time.    For method Four, it is required to provide the following information: Select the number of table and real host variables.    A long distance for each table item and the real host variable.    The type of each table item and the real-host variable. Each output host variable (the value that stores the selected table entry) and the memory cell address of the real input host variable. Method of providing information: a description of the SQL provided by Oracle to illustrate that it provides a SQLDA data structure. The process is as follows: Save the Select table item and the virtual input host variable separately in the Select Sqlda and combine Sqlda. The values and names of the selected table entries are stored in the output buffer, while the values and names of the real input host variables are stored in the input buffer. The address of the output buffer is stored in the Select Sqlda, and the address of the input buffer is stored in the binding Sqlda. Sqlda method of writing information: (a) the Sqlald () function writes the name of the SLI (select Table Entry) or P (virtual input host variable) to Sqlda in the buffer when assigning the description area and buffer. (b) The application writes to the Sqlda the address, length, and data type of the buffer that holds the value of SLI or BV (binding variable or real input host scalar).          (iii) DESCRIBE DESCRIBE SELECT list checks the information for each selection table entry to store the information in the Select Sqlda and output buffers. DESCRIBE BIND VARIABLES checks the information for each virtual input host variable to store the information in a combined sqlda and input buffer. Description and application of Sqlda1. Using the include EXEC SQL include Sqlda;2. Use the pointer EXEC SQL INCLUDE Sqlda; Sqlda*BIND_DP; Sqlda*SELECT_DP; BIND_DP=Sqlald (...); SELECT_DP=Sqlald (...); Sqlda application for each dynamic SQL should explain a Sqlda. Library function SQLPRC () separation precision and calibration. User Export (SQL*Forms) EXEC IAF getexec IAF putexec IAF get takes the value out of the form assigned to the host variable example EXEC IAF GET brand_id into:brand;//assign the value of brand_id to the host variable brandexec IAF put put the value of a constant or host variable into forms in the example EXEC IAF put brand VALUES (: brand_id); User Exit Reference method: Define the trigger, in the trigger, with User_exit (user_exit_s Tring[,error_string]) references export. Error handling whenever clause exec SQL whenever {SQLERROR| Not found|SQLWarning} {CONTINUE| do function call| Do Break| GOTO table| STOP};

Pro*c Foundation