--PROC Programming for Oracle database programming

I. The concept of transactions in Oracle
In Oracle, all modifications to the table are in one transaction (the transaction is like a buffer of memory)
Statement that commits the transaction
commit;
Discard Transactions
Rollback;
Principles:

All modifications to the table by Oracle are in the transaction and are not directly modified to the table. Only commit or rollback is executed or discarded.

For the SQL language, all syntax formats are standard, and most of the keywords are generic, but each database system does a lot
expanded, so the SQL language is not 100% generic.
The focus of learning Oracle development is to learn how to invoke Oracle's related functions in the C + + language, to make additions and deletions to the database, and to search for changes.




ii. Embedded SQL statements
2.1 In Proc, the variables used in all Embedded SQL must be in the Exec SQL BEGIN DECLARE section;
declaration between the exec SQL END DECLARE section;
EXEC SQL BEGIN DECLARE section;
Sql_context PContext;
Long sqlcode;//variable type and name can not be changed
EXEC SQL END DECLARE section;

If you add Threads=yes (in the makefile file) when Proc is compiled, you must first execute it in the PC file
ENABLE Threads Statements
EXEC SQL ENABLE THREADS


2.2 Initializing an embedded statement for Oracle
EXEC SQL CONTEXT ALLOCATE:p context;//allocates memory for PContext
EXEC SQL CONTEXT use:p context;//using PContext


2.3 Embedded SQL connected to Orcle
EXEC SQL Connect:suser identified by:spasswd using:sdbname;
Suser is the input variable that represents the connection database user name
SPASSWD is the input variable that represents the connection database password
Sdbname is the input variable, which represents the database name

2.4 Disconnected Embedded SQL
EXEC SQL ROLLBACK Work release;//disconnects, and discards unsaved transactions
EXEC SQL Commit work release;//disconnects and commits unsaved transactions


2.5 Releasing embedded SQL for related resources
EXEC SQL Context free:p context;


2.6 Committing transactions Embedded SQL
EXEC SQL COMMIT work;


2.7 Rolling back transactional Embedded SQL
EXEC SQL ROLLBACK work;


2.8 Dynamic SQL1
strcpy (SQL, "INSERT INTO table1 (ID, name) VALUES (0, ' Tom ')");
EXEC SQL Execute immediate:sql;//Execute SQL statement


2.9 Dynamic SQL2
strcpy (SQL, "INSERT INTO table1 (ID, name) VALUES (: ID,: Name)");
Specify a placeholder in the SQL language by a colon, and the value of the placeholder is assigned by the following code, and the SQL statement is executed
EXEC SQL PREPARE stat from:sql;//preparing to execute an SQL statement,   EXEC SQL Execute stat using:id,: name;//executes SQL based on the value of the input host variable

2.10 Dynamic SQL3
strcpy (SQL, "SELECT ID, name from table1 where id =: n");
EXEC SQL PREPARE Stat from:sql;//is ready to execute dynamic SQL.
EXEC SQL DECLARE C1 CURSOR for stat;//defines a light table named C1
EXEC SQL OPEN C1 using:n;//using input host variable n in cursor C1
EXEC SQL open c1;//cursor C1
The EXEC SQL whenever not FOUND does break;//loop reads the cursor C1, reads each row in the table until there is no data at the end, looping the break
while (1)
{
EXEC SQL FETCH C1 into:id,: name;//Put query results into output variable id,name
printf ("id =%d, name =%s\n", ID, name);
}
2.11 Dynamic SQL4
EXEC SQL BEGIN DECLARE section;
int I, Ioutput_count, ioccurs, IType, Ilen;
Short IInd;
Char sdata[1024];//returns buffer that holds the select query data
Char soutput[64];
Char sinput[64];
const char *sql;

EXEC SQL END DECLARE section;

SQLCODE = 0;
Ilen = sizeof (sData);//indicates buffer size
IType = 12;//All the data sets returned by the select are handled according to the VARCHAR2 type
SQL = Dysql;
sprintf (soutput, "output%p", pContext);//Just to dynamically generate a non-repeating string in a system
sprintf (SInput, "input%p", pContext);//Just to dynamically generate a non-repeating string in a system
EXEC SQL ALLOCATE descriptor:soutput;//Allocation SELECT statement query output result buffer
EXEC SQL ALLOCATE descriptor:sinput;

EXEC SQL PREPARE S from:sql;//ready to execute corresponding SQL statement

if (SQLCODE! = 0)
{
Sql_error ();
EXEC SQL deallocate descriptor:soutput;//Release SELECT statement query output result buffer
EXEC SQL deallocate descriptor:sinput;
return 1;
}

EXEC SQL DECLARE C CURSOR for S;
EXEC SQL Open C using descriptor:sinput;//opens a cursor with an input buffer
Select an output buffer
EXEC SQL DESCRIBE OUTPUT S USING descriptor:soutput;
How many columns are returned by the SELECT statement
EXEC SQL GET DESCRIPTOR:sOutput:iOutput_count = count;

for (i=0;i<ioutput_count;i++)
{
Ioccurs = i + 1;
EXEC SQL SET descriptor:soutput
Value:ioccurs TYPE =: iType, LENGTH =: Ilen;
}
EXEC SQL whenever not FOUND does break;
while (1)
{
EXEC SQL FETCH C into descriptor:soutput;//get each row
for (i = 0;i<ioutput_count;i++)//Get each column in each row
{
Ioccurs = i +1;
memset (sData, 0, sizeof (sData));
EXEC SQL GET descriptor:soutput
VALUE:iOccurs:sData = DATA,: IInd = INDICATOR;
if (IInd = =-1)//No data
{
printf ("%s\t", "NULL");
}else
{
printf ("%s\t", sData);
}
}
printf ("\ n");
}

EXEC SQL CLOSE C;
EXEC SQL deallocate descriptor:soutput;//Release SELECT statement query output result buffer
EXEC SQL deallocate descriptor:sinput;

--PROC Programming for Oracle database programming