PROC is one of the programming interfaces provided by the Oracle database. It is a wide range of applications. This article introduces some experiences of Proc programming and should pay attention to places.
Example:
#include
#include
#include
#include
#include
EXEC SQL include Sqlca;
/ * Release_cursor = YES makes the PROC after the release and embed SQL related resources * /
EXEC ORACLE OPTION (Release_Cursor = YES);
EXEC SQL Begin Declare Section;
VARCHAR VC_USER [20];
Long al_empno = 0;
CHAR AC_ENAME [11] = "";
CHAR AC_HIREDATE [20] = ""
Double AF_SAL = 0;
EXEC SQL VAR AC_ENAME IS STRING (11);
EXEC SQL VAR AC_HIREDATE IS STRING (20);
Exec SQL End Declare Section;
/ * Error handler * /
Void SQL_ERROR (Char * MSG)
{
Printf ("/ n% s,% ld,% s / n", msg, sqlca.sqlcode, (char *) sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE
EXIT (-1);
}
Main ()
{
EXEC SQL WHENEVER SQLEC DO SQL_ERROR ("Oracle Error:");
/*Connect to the database*/
STRCPY (VC_USER.ARR, "Scott / Tiger @ Demo");
Vc_user.len = 16;
EXEC SQL Connect: VC_User;
EXEC SQL DECLARE CUR_EMP CURSOR for
Select Empno, ENAME, TO_CHAR (HIREDATE, 'YYYY / MM / DD HH24: MI: SS'), SAL from EMP
EXEC SQL OPEN CUR_EMP;
While (1)
{
Al_empno = 0;
STRCPY (ac_ename, "");
Strcpy (ac_hidate, "");
AF_SAL = 0;
EXEC SQL FETCH CUR_EMP INTO: AL_EMPNO,: AC_ENAME: ENAME_IND,: AC_HIREDATE: HIREDATE_IND,: AF_SAL: SAL_IND;
IF (sqlca.sqlcode == 1403)
{
Break;
}
Printf ("EmpnO =% LD, ENAME =% S, HiRedate =% S, SAL =% LF / N", Al_Empno, AF_ENAME, AC_HIREDATE, AF_SAL);
}
EXEC SQL Close Cur_EMP;
Exec SQL ROLLBACK WORK RELEASE
}
1, the statement of host variables
In PROC, the variable used in the SQL statement is referred to as host variables. They should in Exec SQL Begin Declare Section; with Exec SQL Edn Declare Section;
Declaration between, as shown above. Please pay attention to the following points when declaring the host variable:
(1) Defined with fields of varchar2, varchar, char, which can be declared in the Proc, but the length should be added to the length defined in the table, because the CHAR type variable in the proc is used to do the end of the end .
Such as: ename is defined in the table as ename varchar2 (10), which can be defined in the PROC:
EXEC SQL Begin Declare Section;
Char ename [11];
Exec SQL End Declare Section;
Common error description:
If the inserted string length is greater than 10, such as: Exec SQL INSERT INTO EMP (ENAME) VALUES ('12345678901'); the following error occurs:
Error: ORA-01480: STR assignment variable lacks empty suffix.
If defined as:
EXEC SQL Begin Declare Section;
Char eName [15];
Exec SQL End Declare Section;
When the inserted string length is greater than 10, when less than 15, such as: Exec SQL INSERT INTO EMP (ENAME) VALUES ('12345678901'); the following error occurs:
Error: ORA-01401: The value insert is too large.
When the inserted string length is greater than 15, such as: EXEC SQL INSERT INTO EMP (ENAME) VALUES ('12345678901234 "); the following error occurs:
Error: ORA-01401: STR assignment variable lacks empty suffix.
(2) When the value of the word segment from the SQL statement is in the host variable, the PROC does not automatically remove the host variable to the right space. Instead, it is not enough to replenish the length of the length defined in the declare section (independent of the defined in the table). If you don't pay attention to this, the string operation is made (such as comparison, etc.). Such as:
EXEC SQL Begin Declare Section;
Char eName [10];
Exec SQL End Declare Section;
If the value of ename is 'ABC' in the table, the value taken is 'ABC';
The CHAR type variable can be redefined with statement exec SQL VAR. Such a host variable will automatically remove the right space. as follows:
EXEC SQL Begin Declare Section;
Char ename [11];
EXEC SQL VAR AC_ENAME IS STRING (11);
Exec SQL End Declare Section;
If the value of ename is 'ABC' in the table, the value taken is 'ABC';
(3) For floating-point variables, in order to ensure accuracy, it is best to declare a Double type. Because the Double type is much higher than the Float type.
(4) Integer can be declared as a long type (for longer integers, and if the platform used, if the platform is on the Sun platform, it can be declared as a long long type).
(5) DATE type: Date type generally declares as char (20).
When the Date type data is inserted into the table, the TO_DATE () function is typically converted with the TO_CHAR () function when the value is taken.
Exec SQL SELECT TO_CHAR (HIREDATE, 'YYYY / MM / DD HH24: MI: SS') INTO: AC_HIRE_DATE FROM EMP WHERE EMPNO = 1234;
Exec SQL INSERT INTO EMP (Empno, HiRedate) Values (123, To_Date) Values (123, To_Date (: AC_HIREDATE, 'YYYY / MM / DD HH24: MI: SS');
2. The scope of host variables If the host variable is declared outside of all functions, they are global variables. Pay attention to initialize the value of the variable before use, and the host variable can also be defined in the internal definition of a function. At this time they are local variables. Generally habitually declare the host variable as global variable.
3, database connection and disconnection
The connection between the database has the following methods:
(1)
STRCPY (vc_user.arr, "scott / tiger");
VC_User.len = 11;
EXEC SQL Connect: VC_User;
(2)
STRCPY (user, "scott");
STRCPY (Pass, "Tiger");
EXEC SQL Connect: user identified by: pass;
Note: Both of some platforms can be used, and only the first method can be used on some platforms.
In the Proc program, remember to use the Exec SQL ROLLBACK WORK RELEASE; disconnect the connection with the database and release the relevant database resources.
4. Processing of NULL value in PrOC
If the value taken out of a piece is Null, the report: sqlcode = -1405, SQLERR = ORA-01405: The column value read is NULL
And the value of the corresponding host variable will not be changed, and the value before performing the SQL statement. The method of the commonly used processing NULL value is:
(1) Use the indicator variable, there is no -1405 error at this time, when there must be a corresponding indicator variable for the field, if a field does not have an indicator variable, but the value taken out
For the NULL value, there will still be -1405 error. When the value is NULL, the corresponding indicator variable variable is -1, can be processed according to the value of the indicator variable.
(2) If the field is large, the field to a structure body and in the indicator structure corresponding to the structure. As the above example, the structure is defined:
Struct str_emp {
Long al_empno;
CHAR AC_ENAME;
CHAR AC_HIREDATE;
Double AF_SAL;
}
Struct str_emp_ind {
Long al_empno;
CHAR AC_ENAME;
CHAR AC_HIREDATE;
Double AF_SAL;
}
Struct Str_Emp Str_EMP;
STRCUT STR_EMP_IND STR_EMP_IND;
Memset (& str_emp, 0, sizeof (str_emp)) can be used before being taken. Clear the structure, so if it is a character type NULL, it will be "", ", the integer NULL will be 0,
The floating point will be 0.00. There is no -1405 error.
(3) The NVL () function can also be used: For example, the following:
EXEC SQL DECLARE Authors Cursor for
Select Empno, NVL (ENAME, CHR (0)), NVL (TO_CHAR (HiRedate, 'YYYY / MM / DD HH24: MI: SS'), CHR (0)), NVL (SAL, 0) from EMP;
This will not have -1405 error. When the removed value is NULL, the value specified in NVL () is automatically replaced.
CHR (0) can also be replaced directly, as follows:
Select Empno, NVL (Ename, ''), NVL (TO_CHAR (HIREDATE, 'YYYY / MM / DD HH24: MI: SS'), ''), NVL (SAL, 0) from EMP;
5, wrong processing in proc
All SQL statements may be wrong. So it is necessary to judge, but each SQL statement is added to the wrong judgment, too much trouble, can be used to use a function such as SQL_ERROR () to perform error handling,
method:
(1) Define the QL_ERROR () function.
(2) Plus EXEC SQL WHENEVER SQLERROR DO SQL_ERROR (); then when Sqlca.sqlcode <0 is incorrect, the program is automatically transferred to SQL_ERROR (). Note: For SQLCA.SQLCODE> 0 errors such as SQLCA .sqlcode = 1403 does not turn to SQL_ERROR () execution. In addition: Under UNIX, you can use Oerr to find errors. Such as: ORA ORA-1405 lookup error number is a description of -1405.
6. Method for adjusting the stored procedure in the Proc
To put the stored procedure between Exec SQL Execute and End-Exec; as follows:
Where: Al_Empno, ac_ename is an input parameter, l_return, l_errno, and c_errtext is the output parameter.
Al_empno = 8888;
STRCPY (AC_ENAME, "ABCD");
EXEC SQL EXECUTE
Begin
UP_DB_EMP (: Al_empno,: ac_ename,: l_return,: l_errno,: c_errtext);
END;
End-exec;
IF (l_return! = 0)
{
Printf ("call up_PB_EMP storage process error, errno =% ld, errtext =% S / N", L_ERRNO, C_ERRTEXT);
}
7, PROC command line option: The Proc Compiler has a lot of command line options, and directly running the PROC directly in the command line, all command line options are listed, and there is a description.
(1) Storage process: Compiling the storage process is to bring the username and password
Proc userid = scott / tiger sqlcheck = semantics iren = 512 iname = Test.cpp
(2) PARSE = NONE does not grant the non-SQL code, and the default syntax analysis is also performed on non-SQL code.
When using proc in Oracle8.1.5 on RED HAD6.3, you will be prompted: /usr/include/stdio.h and other .h files are wrong. You can add PARSE = None, just fine.
8, pay attention to: exec oracle option (release_cursor = yes);
Release_cursor = yes makes the proc in execution, release and embed SQL related resources, and ensure that Oracle does not lock database resources, such as locks, etc. after the Proc program is executed.
If you use ORACA in Proc, you have to add:
EXEC ORACLE OPTION (ORACA = YES);
9. Type conversion in PROC
First, in the C language:
(1) Character type to integer ATOI () atol (), SSCANF ()
(2) Integer, floating point to character type, available SPRINTF ()
(3) Character type to floating-point type with ATOF () is not good, it is best to use SSCANF (), for example:
EXEC SQL Begin Declare Section;
Double D_Demo;
FLOAT F_DEMO;
CHAR AC_TEXT [20] = "222";
Exec SQL End Declare Section;
(1) SSCANF (ac_text, "% f", & d_demo);
Printf ("ac_text =% S, D_DEMO =% F / N", AC_Text, D_Demo;
(2) SSCANF (ac_text, "% lf", & d_demo);
Printf ("ac_text =% S, D_DEMO =% F / N", AC_Text, D_Demo; (3) SSCANF (ac_text, "% f", & D_Demo);
Printf ("ac_text =% S, D_DEMO =% LF / N", AC_Text, D_Demo;
(4) SSCANF (ac_text, "% lf", & d_demo);
Printf ("ac_text =% S, D_DEMO =% LF / N", AC_Text, D_Demo;
Printf ("******************* / n");
(5) SSCANF (ac_text, "% f", & f_demo);
Printf ("ac_text =% s, f_demo =% f / n", ac_text, f_demo;
(6) SSCANF (ac_text, "% lf", & f_demo);
Printf ("ac_text =% s, f_demo =% f / n", ac_text, f_demo;
(7) SSCANF (ac_text, "% f", & f_demo);
Printf ("ac_text =% s, f_demo =% lf / n", ac_text, f_deemo;
(8) SSCANF (ac_text, "% lf", & f_demo);
Printf ("ac_text =% s, f_demo =% lf / n", ac_text, f_deemo;
The result of the output:
Ac_text = 222.00, D_Demo = 0.000000
Ac_text = 222.00, D_Demo = 222.000000
Ac_text = 222.00, D_Demo = 222.000032
Ac_text = 222.00, D_Demo = 222.000000
*******************
AC_Text = 222.00, f_demo = 222.000000
AC_Text = 222.00, f_demo = 0.000000
AC_Text = 222.00, f_demo = 222.000000
AC_Text = 222.00, f_demo = 0.000000
D_Demo = ATOF (ac_text);
Printf ("ac_text =% s, ATOF (ac_text) =% f / n", ac_text, d_demo;
D_Demo = ATOF (ac_text);
Printf ("ac_text =% s, ATOF (ac_text) =% lf / n", ac_text, d_demo;
f_demo = atof (ac_text);
Printf ("ac_text =% s, ATOF (ac_text) =% f / n", ac_text, f_demo;
f_demo = atof (ac_text);
Printf ("ac_text =% s, ATOF (ac_text) =% lf / n", ac_text, f_demo;
The result of the output:
AC_Text = 222.00, ATOF (ac_text) = 1243288.000000
AC_Text = 222.00, ATOF (ac_text) = 1243288.000000
AC_Text = 222.00, ATOF (ac_text) = 1243288.000000
Ac_text = 222.00, ATOF (ac_text) = 1243288.000000 From the above results visible:
The Double type should adopt SSCANF (AC_APP_CAPCITY, "% LF", & D_APP); printed with "% LF", "% f" can be. (2), (4) correct
The Float type should adopt SSCANF (AC_APP_CAPCITY, "% F", & D_APP); print "% lf", "% f" can be. (5), (7) correct
The result of the ATOF () conversion is wrong, so don't use it.
Second, write a table or take data from the table:
(1) The character type and integer can be used without conversion, using the default mode.
(2) The character type and floating point can be used without conversion, using the default mode.
(3) TO_CHAR (), to_date () can be used between the date type and character type.
10, 4 Dynamic SQL Introduction to PROC
(1) Dynamic SQL1: Can't be a query statement, and no host variables.
Usage: Fight for a truncated SQL statement and perform with Execute Immediate, such as:
EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE DYN1 (Col1 Varchar2 (4))"
(2) Dynamic SQL2: Can't be a query statement, and the number of host variables entered is known,
Usage: Made a string of dynamic SQL statements, execute with prepare, execute statement.
STRCPY (C_SQL, "DELETE FROM EMP where Empno =:?");
EXEC SQL Prepare SQL_STMT from: c_sql;
EXEC SQL EXECUTE SQL_STMT USING: EMP_NUMBER;
(3) Dynamic SQL3: Used to create a dynamic query, and the field to query the field and the number of host variables are known
Usage: Fighting a string of dynamic SQL statement, analyzing the statement with prepare, and defines a CURSOR for value
Such as: If the data to be queried is put in 12 tables in December a year. Name User_Fee_1Mon, user_fee_2mon, .... can be used in dynamic SQL3
STRCPY (C_SQL, "SELECT C_USER_ID, C_USER_NAME, TO_CHAR (T_Date, 'YYYY / MM / DD HH: MI: SS'), N_FEE / N");
STRCAT (C_SQL, "from user_fee_);
STRCAT (C_SQL, AC_MON);
STRCAT (C_SQL, "/ N where c_user_id =: v1");
EXEC SQL prepare s from: c_sql;
EXEC SQL DECLARE CUR_USER_FEE CURSOR for S;
EXEC SQL OPEN CUR_USER_FEE Using: AC_USER_ID;
While (1)
{
EXEC SQL FETCH CUR_USER_FEE INTO: C_USER_ID,: C_USER_NAME,: C_DATE,: N_FEE);
IF (Sqlca.sqlcode <0)
{
/ * Fetch Cursor failed * /
Printf ("Fetch Cursor Cur_User_Fee Fail, Sqlcode =% LD, SQLSERR =% S", Sqlca.sqlcode, Sqlca.sqlerrm.sqlerrmc);
}
IF (Sqlca.sqlcode == SQLNOTFOUND)
{
Break;
}
}
EXEC SQL Close Cur_User_fee;
(4) Dynamic SQL4: The fields to be processed and the number of input host variables and the main variable are not known in advance, such as:
INSERT INTO EMP (
11, SQLCA: SQL is a structure of Oracle, its domain for some information after the recent SQL statement execution, such as error number, error description, warning, status, etc. common
The domain is described below:
Sqlca.sqlcode: Error number, = 0 correct, = 1403 did not take data
Sqlca.sqlserrm.sqlerrmc: Error Description
Sqlca.sqlerrd [3]: The number of rows handled by a nearest SQL statement, if the statement handles fails, its value is uncertain, if the error occurs in a Cursor operation, then
Its value refers to the number of rows that have been successfully processed. In Delete, Update, it does not contain those rows that are deleted, updated due to foreign key constraints,
Delete from Emp where dept = 'saler';
Delete 20 lines in Table EMP, but if the table EMP is constrained with the table address, it causes the table Address to be deleted 20 rows, then sqlca.sqlerrd [3] = 20, not 40.
Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
