Proc dynamic SQL example (method 1, 2, 3)

The following is a complete example of the first three dynamic SQL statements of Proc.

(1) Dynamic sql1: it cannot be a query (select) Statement, and there is no host variable.
Usage: spell a dynamic SQL statement and execute it with execute immediate, for example:
 
Exec SQL execute immediate CREATE TABLE test (test_col varchar2 (4 ));
Exec SQL execute immediate insert into Table Test ('aaa ');
Exec SQL execute immediate delete test where test_col = 'aaa ';

(2) Dynamic sql2: it cannot be a SELECT statement, and the number of input host variables is known,
Usage: spell a dynamic SQL statement and run it with prepare and execute statements.
Strcpy (sqlstring, "delete from test where test_col = :? ");
Exec SQL prepare sqlproc from: sqlstring;
Exec SQL execute sqlproc using: emp_number;
 
Most of the examples below use dynamic sql2.
 
(3) Dynamic sql3: used to create a dynamic query, and the fields to be queried and the number of input host variables are known.
Usage: spell a dynamic SQL statement, use prepare to analyze the statement, and define a cursor for Value
For example, if the data to be queried is in multiple tables and select user_name from, dynamic sql3 can be used for query.
Strcpy (SQL, "select user_name from ");
Strcat (SQL, "Table1"); // Table2, table3, table4
Exec SQL prepare sqlproc from: SQL;
Exec SQL declare cur_user_name cursor for sqlproc;
Exec SQL open cur_user_name;
While (1)
{
Exec SQL fetch cur_user_name into: ora_id;
If (sqlca. sqlcode <0)
{
/* Fetch cursor failed */
Printf ("Fetch cursor fail, sqlcode = % lD, sqlserr = % s", sqlca. sqlcode, sqlca. sqlerrm. sqlerrmc );
}
If (sqlca. sqlcode = sqlnotfound)
{
Break;
}
}
Exec SQL close cur_user_name;

In the following example, case5 also uses this method.

// Proc example

# Include <stdio. h>
# Include <string. h>
# Include <math. h>
# Include <stdio. h>
# Include <stdlib. h>
# Include "sqlca. H"
# Include <ctype. h>

// Variable, process pre-declaration
Int I;
Char screen [1];
Char cmd [1];

//************************************** ********************
// Case corresponds to the switch of db_selectop
Exec SQL begin declare section;

Varchar oraname [30]; // Case 1, 2, 3

Varchar oravalue [20]; // Case 1, 2, 3, 5
Int oracount; // Case 1, 2, 3, 4, 5
Varchar orasql [30], oratable [20]; // case 4, 5

Varchar orafield [10]; // case 5
Varchar oracountsql [30]; // case 5

Varchar oracode [10]; // case 6
Varchar oracontent [10]; // case 6

Exec SQL end declare section;
//************************************** ********************

Int db_connect ();
Int db_selectop ();

// Void dy_tablecount ();
// Void dy_tablefield ();
Void view_tabledata ();

Void pause ();
Void SQL _error (char *);

// Main Function
Void main ()
{
Exec SQL include sqlca;
Exec Oracle option (release_cursor = yes );
Exec SQL whenever sqlerror do SQL _error ("<error> ");

If (db_connect () = 0)
{
Db_selectop ();
}
}

// Open the data connection
Int db_connect ()
{

Exec SQL begin declare section;
Varchar limit N [30];
Exec SQL end declare section;

Printf ("----------------------------------");
Printf ("\ n [examples with Oracle DB] \ n ");
Printf ("----------------------------------");
Printf ("\ n designed by Liwei 2005 \ n ");
CMD [0] = 'a ';
While (CMD [0]! = '0' & cmd [0]! = '1 ')
{

Printf ("\ n confirm dB Source :");
Printf ("\ n 1: workflow/workflow @ if ");
Printf ("\ n 0: exit ;");
Printf ("\ n choose :");

Gets (CMD );
Switch (CMD [0])
{
Case '1 ':
Strcpy (Limit N. Arr, "workflow/workflow @ if ");
Limit N. Len = strlen (Limit N. Arr );
Limit N. Arr [limit N. Len] = '\ 0 ';

// Exec SQL whenever sqlerror goto cnerror;
Exec SQL CONNECT: Limit N;

Printf ("\ n [OK connected!] ");
Return 0;

Break;
Case '0 ':
Break;
Default:
Printf ("\ n [error Input!] \ N ");
Break;
}
 
}

 
Exit (0 );

// Cnerror:
// Printf ("\ n [error Oracle connected!] ");
// Return 1;
}

// Select data operations
Int db_selectop ()
{
Char order [1];

 

CMD [0] = 'a ';
// Order [0] = 'a ';

While (CMD [0]! = '0 ')
{
Printf ("\ n ");
Printf ("\ n select dB method :");
Printf ("\ n -------------------------------------------");
Printf ("\ n 1: gettablecount static [class_flow]");
Printf ("\ n 2: gettablefield one static [class_flow]");
Printf ("\ n 3: gettablefield Muti static [use_power]");
Printf ("\ n ");
Printf ("\ n 4: gettablecount dynamic ");
Printf ("\ n 5: gettablefield one dynamic ");
Printf ("\ n ");
Printf ("\ N 6: edittable use_dept ");
Printf ("\ n -------------------------------------------");
Printf ("\ n 0: Exit ");
Printf ("\ n enter :");

Gets (CMD );

Switch (CMD [0])
{
Case '1 ':

Exec SQL select nvl (count (*), 0) into: oracount from class_flow;

Printf ("\ n <The table count> ");
Printf ("% d", oracount );
Pause ();
Break;

Case '2 ':

Exec SQL declare curone cursor for select distinct flow_name from class_flow where flow_class = 'offset ';
Exec SQL select count (distinct flow_name) into: oracount from class_flow where flow_class = 'offset ';
Exec SQL open curone;

For (I = 1; I <= oracount; I ++)
{
Exec SQL fetch curone into: oraname;
Oraname. Arr [oraname. Len] = '\ 0 ';
Printf ("\ n <Field List> ");
Printf ("% s", oraname. Arr );
}
Exec SQL close curone;
Pause ();
Break;

Case '3 ':

Exec SQL declare curmuti cursor for select power_id, power_name from use_power order by power_id ASC;
Exec SQL select count (*) into: oracount from use_power;
Exec SQL open curmuti;

For (I = 1; I <= oracount; I ++)
{
Exec SQL fetch curmuti into: oravalue,: oraname;
Oravalue. Arr [oravalue. Len] = '\ 0 ';
Oraname. Arr [oraname. Len] = '\ 0 ';
Printf ("\ n <fields list> ");
Printf ("%-8 s", oravalue. Arr );
Printf ("%-20 s", oraname. Arr );
}

Exec SQL close curmuti;

Pause ();
Break;

Case '4 ':

// Exec SQL begin declare section;
// Varchar orasql [30], oratable [20];
// Int oracount;
// Exec SQL end declare section;

Printf ("\ n custom table ");
Printf ("\ n -----------------------");
Printf ("\ N input table name :");
Gets (oratable. Arr );

Oratable. Len = strlen (oratable. Arr );
Oratable. Arr [oratable. Len] = '\ 0 ';

Strcpy (orasql. Arr, "select count (*) from ");
Strcat (orasql. Arr, oratable. Arr );
Orasql. Len = strlen (orasql. Arr );
Orasql. Arr [orasql. Len] = '\ 0 ';

Printf ("\ n <SQL State> ");
Printf (orasql. Arr );
Printf ("\ n ");

Exec SQL prepare sqldycount from: orasql;
Exec SQL declare curdycount cursor for sqldycount;
Exec SQL open curdycount;
Exec SQL fetch curdycount into: oracount;
Exec SQL close curdycount;

Printf ("\ n <Table count> ");
Printf ("% d", oracount );
// Dy_tablecount ();
Pause ();
Break;

Case '5 ':

// Exec SQL begin declare section;
// Varchar orasql [30], oratable [10], orafield [10], oravalue [20];
// Varchar oracountsql [30];
// Int oracount;
// Exec SQL end declare section;

// Accept screen data
Printf ("\ n custom table and field ");
Printf ("\ n -----------------------");
Printf ("\ N input table name :");
Gets (oratable. Arr );
Oratable. Len = strlen (oratable. Arr );
Oratable. Arr [oratable. Len] = '\ 0 ';
Printf ("input field name :");
Gets (orafield. Arr );
Orafield. Len = strlen (orafield. Arr );
Orafield. Arr [orafield. Len] = '\ 0 ';

// SELECT statement combination
Strcpy (orasql. Arr, "select ");
Strcat (orasql. Arr, orafield. Arr );
Strcat (orasql. Arr, "from ");
Strcat (orasql. Arr, oratable. Arr );
Orasql. Len = strlen (orasql. Arr );
Orasql. Arr [orasql. Len] = '\ 0 ';
Printf ("\ n <SQL State> ");
Printf (orasql. Arr );
Printf ("\ n ");
// Read content
Exec SQL prepare sqldy from: orasql;
Exec SQL declare curdyfield cursor for sqldy;
Exec SQL open curdyfield;

// Combine the select count statement
Strcpy (oracountsql. Arr, "select count (*) from ");
Strcat (oracountsql. Arr, oratable. Arr );
Oracountsql. Len = strlen (oracountsql. Arr );
Oracountsql. Arr [oracountsql. Len] = '\ 0 ';
// Read count
Exec SQL prepare sqldycount from: oracountsql;
Exec SQL declare curdyfieldcount cursor for sqldycount;
Exec SQL open curdyfieldcount;
Exec SQL fetch curdyfieldcount into: oracount;

For (I = 1; I <= oracount; I ++)
{
Exec SQL fetch curdyfield into: oravalue;
Oravalue. Arr [oravalue. Len] = '\ 0 ';
Printf ("\ n <Field List> ");
Printf ("% s", oravalue. Arr );
}
Exec SQL close curdyfieldcount;
Exec SQL close curdyfield;
// Dy_tablefield ();
Pause ();
Break;

Case '6 ':

Order [0] = 'a ';
While (Order [0]! = '0 ')
{
Printf ("\ n ");
Printf ("\ n edit table ");
Printf ("\ n -------------");
Printf ("\ n 1: View ");
Printf ("\ n 2: Insert ");
Printf ("\ n 3: delete ");
Printf ("\ n 4: Update ");
Printf ("\ n -------------");
Printf ("\ n 0: Exit ");
Printf ("\ n enter :");
Gets (order );

Switch (Order [0])
{
Case '1 ':
View_tabledata ();
Pause ();
Break;
Case '2 ':
// Insert
Printf ("\ n Insert ");
Printf ("\ n -----------------------");
Printf ("\ n enter code :");
Gets (oracode. Arr );
Oracode. Len = strlen (oracode. Arr );
Oracode. Arr [oracode. Len] = '\ 0 ';
Printf ("Enter content :");
Gets (oracontent. Arr );
Oracontent. Len = strlen (oracontent. Arr );
Oracontent. Arr [oracontent. Len] = '\ 0 ';

Exec SQL insert into use_dept values (: oracode,: oracontent );
Exec SQL commit;
Pause ();
Break;
Case '3 ':
View_tabledata ();
// Delete
Printf ("\ n Delete ");
Printf ("\ n -----------------------");
Printf ("\ n enter code :");
Gets (oracode. Arr );
Oracode. Len = strlen (oracode. Arr );
Oracode. Arr [oracode. Len] = '\ 0 ';
Exec SQL Delete use_dept where dept_id =: oracode;
Exec SQL commit;
// Strcpy (c_ SQL, "delete from EMP where empno = :? ");
// Exec SQL prepare SQL _stmt from: c_ SQL;
// Exec SQL execute SQL _stmt using: emp_number;
Pause ();
Break;
Case '4 ':
View_tabledata ();
// Update
Printf ("\ n Update ");
Printf ("\ n -----------------------");
Printf ("\ n enter code :");
Gets (oracode. Arr );
Oracode. Len = strlen (oracode. Arr );
Oracode. Arr [oracode. Len] = '\ 0 ';
Printf ("Enter content :");
Gets (oracontent. Arr );
Oracontent. Len = strlen (oracontent. Arr );
Oracontent. Arr [oracontent. Len] = '\ 0 ';

Exec SQL update use_dept set dept_name =: oracontent where dept_id =: oracode;
Exec SQL commit;

Pause ();
Break;
Default:
Break;
}
}
CMD [0] = '6 ';
Break;

Default:
Break;
}

 
}
Return 0;

}

 

Void view_tabledata ()
{
// View
Exec SQL declare curtable cursor for select dept_id, dept_name from use_dept order by dept_id ASC;
Exec SQL select count (*) into: oracount from use_dept;
Exec SQL open curtable;

Printf ("\ n ");
Printf ("%-8 s", "Code ");
Printf ("%-20 s", "content ");
Printf ("\ n --------------------");

For (I = 1; I <= oracount; I ++)
{
Exec SQL fetch curtable into: oravalue,: oraname;
Oravalue. Arr [oravalue. Len] = '\ 0 ';
Oraname. Arr [oraname. Len] = '\ 0 ';
Printf ("\ n ");
Printf ("%-8 s", oravalue. Arr );
Printf ("%-20 s", oraname. Arr );
}

Printf ("\ n --------------------");

Exec SQL close curtable;
}
// Pause the screen
Void pause ()
{
Printf ("\ n -- press enter to continue --");
Gets (screen );

}
// Display unexpected errors
Void SQL _error (char * MSG)
{
// Printf ("\ n % S % LD % s \ n", MSG, sqlca. sqlcode, (char *) sqlca. sqlerrm. sqlerrmc );
Printf ("\ n % S % s \ n", MSG, (char *) sqlca. sqlerrm. sqlerrmc );
// Exec SQL rollback release;
Db_selectop ();
}

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 759963