Go Oracle's proc Usage explained

Pro*c is a high-level usage, and OCI is the basic use of Oracle

How to compile a. pc file: proc code=cpp parse=none iname=filename.pc oname=filename.cpp

First, I want to cover the header file #include this header file

Secondly, we must define the struct SQLCA Sqlca before declaring the host variable; This variable.

Three, all variables related to the Oracle database SQL statement must be declared as the host variable allocated space before they can be used,

A host variable can only be a data type supported by Oracle, typically a basic type, struct type. Only the host variable can communicate with other variables in SQL and functions.

Example:

EXEC SQL BEGIN DECLARE section; Start to affirm
Char strsql[512]={0};

EXEC SQL END DECLARE section; Closing statement

Four, static SQL statement to look up a record of the mode, through the host variable to receive the results of the SQL statement found

Example: Into : Totalrecord,: Totalmoney , receiving results, Proc: binding variables

EXEC SQL Select count (b.msisdn), sum (b.payamount)
Into:totalrecord,: Totalmoney
From Om_mpay_user_info A, Om_mpay B
WHERE a.msisdn = B.msisdn and A.region=trim (: AreaCode) and b.pay_date =: sqltime;

Five, query multiple records in general we use dynamic SQL statements, combined with cursors to collect the results of multiple records, the second thing to note is that the loop to get the structure.

Example: sprintf (strSQL, "select *from mytable")

     exec SQL PREPARE   Sqlcountregion  from  :strsql;  //or active area
     exec SQL DECLARE  curregion  CURSOR for  sqlcountregion;  //apply cursor to Active area
      EXEC SQL OPEN  curregion;   //open a cursor, and you can assign a value to it using a using

do{
EXEC SQL FETCH Curregion into: regionnum; //loop to capture the value of a variable in a cursor
if (Sqlca.sqlcode ==1403)

Break
cout<<regionnum<<endl;

}while (1);

EXEC SQL CLOSE Curregion; //Close Cursors
EXEC SQL rollback work release; //Rollback data off release all resources and links

Six, catch exception error and stop program

The following means that when an error exception occurs, jump to the statement following the execution tag at the mark

EXEC SQL whenever SQLError goto errinfo;

Errinfo:
printf ("sqlca.sqlcode=%d, sqlca.errm=%s\n", Sqlca.sqlcode, SQLCA.SQLERRD);

EXEC SQL whenever SQLError stop; //When an exception occurs, the entire program is launched, all connections are released to release all resources

return sqlca.sqlcode;

Seven, the SQL statement in Proc do not add a semicolon (;), note if the C + + project is used if it is C + +/or, the annotation in Oracle is--the data in the Oracle database if the data bytes are not enough bits, the default with a space complement, The host variable has more space than the database's field space.

The special meaning of the structure of the processing: Sqlca.sqlcode ==1403 means to end the lookup loop, which is more important in dynamic SQL statements,

Sqlca.sqlcode = =-1405 indicates the error when the query value is empty, this is best left to the return value to process,

Sqlca.sqlcode = =-1480 indicates that variables passed in the SQL statement are generally a failure to pass values, possibly a space-size problem.

Eight, about the special SQL statements in Proc, such as, delete, update,alter,insert into and other data-changing operations,

Note that 2 points, you can then host the variable area to request a complex structure type of pointers, with pointers to the external parameters of the data binding to the SQL statement, the next is to commit things, after the operation of the volatile data is commint commit things, rollback.

Example:

int Getdbrecode (struct buf * tempbuf)

{

EXEC SQL BEGIN DECLARE section; Start to affirm
Char strsql[512]={0};

struct BUF * temp= tempbuf;

EXEC SQL END DECLARE section; Closing statement

EXEC SQL INSERT INTO mytable (name, sex, num) VALUES (: Temp->name,: Temp->sex,: Temp->num)

if ( sqlca.sqlcode )

{

printf ("Judging whether the execution succeeds");

}

}

Example explanation

#include//#include

#include "CssCheckBill.h"

#include

int main ()

{

struct SQLCA Sqlca; Have to have
EXEC SQL BEGIN DECLARE section;
Char strsql[512]={0};
Char user[20]={0};
Char pwd[20] ={0};
Char dbname[20]={0};
Char regionnum[7];

Char totalmoney[15]={0};

Char totalrecord[15]={0};
EXEC SQL END DECLARE section;

strcpy (User, "user");
strcpy (pwd, "pwd");
strcpy (dbname, "dbname");
This is the operation to connect to the database
EXEC SQL CONNECT:user identified by:pwd USING:dbname;

EXEC SQL Select count (b.msisdn), sum (b.payamount)
into: Totalrecord,: Totalmoney //into bound variable
From Om_mpay_user_info A, Om_mpay B
WHERE a.msisdn = B.msisdn and A.region=trim (: AreaCode) and b.pay_date =: sqltime;

Set the catch Exception information flag
EXEC SQL whenever SQLError goto ora_err;
if (0 >=:: snprintf (Strsql,sizeof (strSQL), "SELECT DISTINCT (region) from Om_mpay_user_info where region is not NULL"))
{
printf ("%s snprintf create SQL Fail!\n", __function__);
return-1;
}

//Dynamic cursors get query results
 exec SQL PREPARE   Sqlcountregion  from  :strsql;
   exec SQL DECLARE  curregion  CURSOR for  SqlCountRegion; cursor for   can be directly followed by SQL statements
  exec SQL OPEN  curregion;

bo=
EXEC SQL FETCH Curregion into: regionnum;
if (sqlca.sqlcode ==1403)
Break
printf ("regionnum=%s, len=%d\n", Regionnum, strlen (Regionnum));
Delstrrightblack (Regionnum);

}while (1);

Turn off exception capture information

EXEC SQL whenever SQLError stop;
EXEC SQL CLOSE curregion;

Set rollback things
EXEC SQL rollback work release;
return sqlca.sqlcode;
Ora_err:
printf ("sqlca.sqlcode=%d, sqlca.sqlerrp=%s\n", Sqlca.sqlcode, SQLCA.SQLERRP);
return sqlca.sqlcode;

return 0;

}
This paper summarizes the methods of Pro*c stored procedure call, cursor execution of ordinary SQL statements, and dynamic SQL statement cursor execution;

Common SQL and cursors, stored procedure usage summary in Pro*c:

1) Exec SQL Select C1,c2 into:v1,v2 from Table_a;

2) EXEC SQL INSERT into table_a (V1,V2) Select b.v1,b.v2 from Table_b b where 1=2;

3) EXEC SQL INSERT into table_a (V1,V2) Select b.v1,b.v2 from Table_b b where 1=2;

Stored Procedure Calls
4) EXEC SQL call procedure_a (: V1,:V2);
5) sprintf (STA, "select V1,v2 into:v1,v2 from table_a where v3=%s", V3);
exec SQL Execute immediate:sta;

--Normal SQL statement cursor execution
6) EXEC SQL declare CUR1 cursor for
Select V1,v2,v3 from table_a where 1=2;
exec SQL Open cur1;
do{
EXEC SQL Fetch CUR1 into:v1,:v2,:v3;
if (sqlca.sqlcode==-1403) break;
....

}while (1);
exec SQL Close cur1;

--Dynamic SQL statement cursor execution
7) > sprintf (STA, "select C1,c2,c3 from Table_a where c1=%s and C2=:v1 and C3=:v2", v1);
EXEC SQL prepare select_msg From:sta;
EXEC SQL declare CUR1 cursor for select_msg;
EXEC SQL Open cur1 using:v1,:v2; Use using to pass a variable that is bound when the cursor is opened
do{
EXEC SQL Fetch CUR1 INTO:C1,:C2,:C3;
if (sqlca.sqlcode==1403) break;
....
}while (1);
exec SQL Close cur1;

Proc Explanation of other situations

Pro*c/c++ Programming Explanation:

1, the declaration of the host variable

In proc, variables used in SQL statements are called host variables. They should be in the Exec SQL BEGIN DECLARE section, with the Exec SQL EDN DECLARE section; Declaration, as shown above, you should be aware of the following when declaring a host variable:

(1) fields defined as Varchar2,varchar,char in a database table can be declared as Char in proc, but the length should be the length they are defined in the table plus 1, because the char variable in proc is used as the end.

For example: ENAME is defined in the table as ename varchar2 (10), which can be defined in proc as:

EXEC SQL BEGIN DECLARE section;

Char ename[11];

EXEC SQL END DECLARE section;

Common error Description:

If the length of the inserted string is greater than 10, such as: EXEC SQL insert INTO EMP (ename) VALUES (' 12345678901 '), the following error occurs:

ERROR:ORA-01480:STR assignment variable is missing an empty suffix.

If defined as:

EXEC SQL BEGIN DECLARE section;

Char ename[15];

EXEC SQL END DECLARE section;

When the length of the inserted string is greater than 10, less than 15 o'clock, such as: EXEC SQL insert INTO EMP (ename) VALUES (' 12345678901 '), the following error occurs:

ERROR:ORA-01401: The inserted value is too large for the column.

When inserting a string longer than 15, such as: EXEC SQL insert INTO EMP (ename) VALUES (' 12345678901234 '), the following error occurs:

ERROR:ORA-01401:STR assignment variable is missing an empty suffix.

(2) When you take the value of a field from a SQL statement to a host variable, Proc does not automatically remove the right space from the host variable. Instead, the right space is not sufficient for the length defined in the Declare section, regardless of what is defined in the table. If you are not aware of this, an error occurs when you perform a string operation in proc, such as comparing equality. Such as:

EXEC SQL BEGIN DECLARE section;

Char ename[10];

EXEC SQL END DECLARE section;

If the value of ename in the table is ' ABC ', the value taken is ' abc ';

You can use the statement exec SQL var to redefine the char type variable. This will automatically remove the right space from the host variable. 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 in the table is ' ABC ', the value taken is ' abc ';

(3) for the variable of floating-point type, in order to guarantee the precision, it is best to declare double type. Because the double type has a much higher precision than the float type.

(4) The integral type can be declared as long (for longer integers, and for platforms supported by the platform, such as on the Sun platform, can be declared as long long).

(5) Date type processing: the date type is generally declared as char (20).

When inserting DATE data into a table, the To_date () function is generally used for type conversion, and the To_char () function is generally used for type conversion when fetching a value.

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 (: ac_hiredate, ' yyyy/mm/dd hh24:mi:ss ');

2, the scope of the host variable

If the host variables are declared outside of all functions, they are global variables. Before using, be careful to initialize the value of the variable, and the host variable can also be defined inside a function. At this point they are local variables. It is generally customary to declare a host variable as a global variable.

3. Connection and disconnection of database

There are two ways to connect a database:

(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:P;

Note: On some platforms, both are available and only the first method can be used on some platforms.

In the proc program, remember to use EXEC SQL ROLLBACK work release, disconnect from the database, and release the associated database resources.

4. Handling of NULL values in proc

If a field is fetched with a value of NULL, it is reported: sqlcode=-1405, sqlerr=ora-01405: The column value read is null and the value of the corresponding host variable is not changed to the value before the SQL statement is executed. The commonly used methods for handling null values are:

(1) With the indicator variable, there will be no-1405 error, when there must be a null field has a corresponding indicator variable, if a field does not have an indicator variable, but the value is taken out of a null value, There will still be a-1405 error. When the value being fetched is NULL, the corresponding indicator variable variable is-1, which can be processed according to the value of the indicator variable.

(2) If there are more fields, the field can be in a struct and an indicator structure corresponding to that structure. As the above example defines a struct:

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;

Available memset (&str_emp,0,sizeof (str_emp)) before fetching. Empty the struct so that if it is null for the character type, it will be "", and the integer null will be 0.

Floating-point type will be 0.00. There will be no-1405 errors at this time.

(3) The NVL () function can also be used: for example:

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;

There will be no-1405 error, when the value taken is null, the value specified in NVL () is automatically replaced.

CHR (0) can also be substituted directly with "'", as follows:

SELECT EMPNO, NVL (ename, ""), NVL (To_char (HireDate, "Yyyy/mm/dd hh24:mi:ss"), ""), NVL (sal,0) from EMP;

5. Handling of Errors in proc

All SQL statements are likely to be faulted. So you have to judge, but each SQL statement after the error judgment, too cumbersome, can be a function such as sql_error () to do error handling,

Method:

(1) define the Ql_error () function.

(2) Add exec SQL whenever SQLERROR do Sql_error () at the beginning, so that when a Sqlca.sqlcode <0 error occurs, the program automatically goes to Sql_error (). Note: Errors on Sqlca.sqlcode >0, such as Sqlca.sqlcode =1403, do not go to Sql_error ().

In addition: Under UNIX, you can use Oerr to find the wrong description. For example: Ora ORA-1405 Find the description of the error number-1405.

　　

6. Methods for calling stored procedures in proc

To place the stored procedure between EXEC SQL EXECUTE and End-exec, as follows:

　　

Where: Al_empno,ac_ename is the input parameter and L_return,l_errno,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 stored procedure error, ERRNO=%LD,ERRTEXT=%SN", l_errno,c_errtext);

}

　　

7, Proc command line options: Proc Compiler has a lot of command-line options, directly under the command line without parameters run proc, will list all the command-line options, and is described.

(1) Storage process: Compile the stored procedure is to bring the user name and password

Proc Userid=scott/tiger sqlcheck=semantics ireclen=512 iname=test.cpp

(2) Parse=none does not parse non-SQL code, it also parses non-SQL code by default.

When using proc in ORACLE8.1.5 on Red HAD6.3, you will be prompted:/usr/include/stdio. H and the other. H file is in error. Can add Parse=none, just fine.

8. Note Plus: EXEC ORACLE OPTION (release_cursor = YES);

Release_cursor=yes enables proc to release the resources that are embedded in SQL after execution, ensuring that Oracle does not lock database resources, such as lock tables, after the proc program finishes executing.

If you use Oraca in proc, add the following in the program header:

EXEC ORACLE OPTION (Oraca=yes);

Original address: http://blog.sina.com.cn/s/blog_9b0604b40101kueq.html

Go Oracle's proc Usage explained