Oracle Dynamic SQL for Oracle PRC C Learning

1. I'm going to talk about a problem here.

Added a header file to the practice example

#include <oraca.h>

And then I can't compile, I always say I can't find oraca.h.

And then I changed it.

EXEC SQL INCLUDE Oraca;

This will be the successful compilation of the past.

First dynamic SQL statement: Non-query statement with no host variable

The application receives input from the outside to construct the SQL statement into a string and then calls the
The Execute IMMEDIATE command executes this SQL statement. This SQL statement cannot be a SELECT statement and does not
Placeholders that can contain input variables.

5.pc#include <stdio.h> #include <string.h>

EXEC SQL INCLUDE Sqlca; EXEC SQL INCLUDE Oraca; EXEC ORACLE OPTION (Oraca=yes); EXEC ORACLE OPTION (Release_cursor=yes);

int main (int argc, char *argv[]) {EXEC SQL BEGIN DECLARE section;    Char *username = "Scott";    Char *passwd = "XX";    Char *DYNSTMT1;    Char dynstmt2[10];    VARCHAR dynstmt3[80]; Char dynstmt4[80]; EXEC SQL END DECLARE section;

ORACA.ORASTXTF = Orastferr;

EXEC SQL Connect:username identified by:p asswd;    Puts ("CREATE TABLE dyn1 (col1 VARCHAR2 (4))"); EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE dyn1 (col1 VARCHAR2 (4))";

DYNSTMT1 = "INSERT into dyn1 VALUES (' TEST ')";    Puts (DYNSTMT1); EXEC SQL EXECUTE IMMEDIATE:d ynstmt1;

strncpy (DYNSTMT2, "COMMIT", 10);    printf ("%.10s\n", dynstmt2); EXEC SQL EXECUTE IMMEDIATE:d ynstmt2;

EXEC SQL COMMIT;

/* Here I myself completely input a DROP TABLE dyn1 this string, to try */gets (DYNSTMT4);    EXEC SQL EXECUTE IMMEDIATE:d ynstmt4; EXEC SQL COMMIT work RELEASE;

return 0;}

The second type: non-query statements with the number of input variables known

The application accepts or builds an SQL statement and uses the prepare and execute language
Sentence to execute this SQL. Cannot be a query statement. The number of placeholders prepared for the input host variable and the input host variable
Type must be known

6.pc#include <stdio.h> #include <string.h>

EXEC SQL BEGIN DECLARE section; #define USERNAME "Scott" #define PASSWD "XX"

EXEC SQL INCLUDE Sqlca; EXEC SQL INCLUDE Oraca; EXEC ORACLE OPTION (oraca=yes); char *username = Username;char *passwd = passwd; VARCHAR dynstmt[80];int empno = 1234;int Deptno1 = 20;int Deptno2 = 30; EXEC SQL END DECLARE section;

int main (int argc, char *argv[]) {ORACA.ORASTXTF = Orastferr;

EXEC SQL Connect:username identified by:p asswd;

strcpy (Dynstmt.arr, "INSERT into EMP (EMPNO, DEPTNO) VALUES (: v1,: V2)"); Dynstmt.len = strlen (Dynstmt.arr);

Puts ((char *) dynstmt.arr);    printf ("V1 =%d, V2 =%d\n", empno, Deptno1);    EXEC SQL PREPARE insert_sql from:d ynstmt;    EXEC SQL EXECUTE insert_sql using:empno,:d eptno1; EXEC SQL COMMIT;

Empno + +;    EXEC SQL EXECUTE insert_sql using:empno,:d Eptno2;    printf ("V1 =%d, V2 =%d\n", empno, Deptno2); EXEC SQL COMMIT;

strcpy (Dynstmt.arr, "DELETE from EMP WHERE EMPNO =: v1");    Dynstmt.len = strlen (Dynstmt.arr);/* Here I still do this by entering a value from the terminal */char temp[10];    Gets (temp); Empno = atoi (temp);

EXEC SQL PREPARE delete_sql from:d ynstmt;    EXEC SQL EXECUTE delete_sql using:empno; EXEC SQL COMMIT work RELEASE;

return 0;}

This is the statement with the host variable, that is, we do not know at first what to delete, and then through other channels given to delete this condition.

The third type: Execute dynamic Query SQL statements with Delcare, PREPARE, and Open, FETCH, CLOSE statements by accepting or constructing SQL statements.

and access the query structure.
Columns, column types, number of input host variables, input host variable, for a known SELECT query statement query
Volume type of the case

7.pc #include <stdio.h> #include <string.h>

EXEC SQL BEGIN DECLARE section;

EXEC SQL INCLUDE Sqlca; EXEC SQL INCLUDE Oraca; EXEC ORACLE OPTION (Oraca=yes);

#define USERNAME "Scott" #define PASSWD "XX"

Char *username = Username;char *passwd = passwd; VARCHAR dynstmt[80]; VARCHAR ENAME[10];

EXEC SQL END DECLARE section;

int main (int argc, char *argv[]) {EXEC SQL BEGIN DECLARE section;    int deptno = 10; EXEC SQL END DECLARE section;

ORACA.ORASTXTF = Orastferr;

EXEC SQL Connect:username identified by:p asswd;

strcpy (Dynstmt.arr, "select ename from EMP WHERE deptno =: v1"); Dynstmt.len = strlen (Dynstmt.arr);

EXEC SQL PREPARE select_sql from:d ynstmt;  EXEC SQL DECLARE select_cur CURSOR for Select_sql;    /* Enter the conditions you are looking for to find */char temp[10];    Gets (temp);     Deptno = atoi (temp); EXEC SQL OPEN select_cur USING:d eptno;

EXEC SQL whenever not FOUND does break;

while (1) {EXEC SQL FETCH select_cur into:ename;        Ename.arr[ename.len] = ' + ';    Puts ((char *) ename.arr); } printf ("\nquery returned%d row%s.\n\n", Sqlca.sqlerrd[2], (sqlca.sqlerrd[2] = = 1)? "": "S");

EXEC SQL CLOSE select_cur; EXEC SQL COMMIT work RELEASE;

return 0;}

Oracle Dynamic SQL for Oracle PRC C Learning