|
下面是ProC前三种动态SQL的完整示例。
(1)动态SQL1: 不能是查询(SELECT)语句,并且没有宿主变量.
用法:拼一句动态SQL语句,并用EXECUTE IMMEDIATE执行,如:
EXEC SQL EXECUTE IMMEDIATE CREATE TABLE test (test_col VARCHAR2(4));
EXEC SQL EXECUTE IMMEDIATE INSERT INTO TABLE test ('AAAA');
EXEC SQL EXECUTE IMMEDIATE DELETE test WHERE test_col='AAAA';
(2)动态SQL2: 不能是查询(SELECT)语句,并且输入的宿主变量数目是知道的,
用法:拼一句动态SQL语句,用PREPARE,EXECUTE语句执行.
strcpy(sqlstring, "DELETE FROM test WHERE test_col = :?");
EXEC SQL PREPARE sqlproc FROM :sqlstring;
EXEC SQL EXECUTE sqlproc USING :emp_number;
下文示例中大多数是采用动态SQL2.
(3)动态SQL3: 用于创建动态查询, 并且要查询的字段以及输入的宿主变量数目是知道的
用法: 拼一句动态SQL语句,用PREPARE分析该语句,并要定义一个CURSOR进行取值
如:要查询的数据在多张表中，select user_name from，可采用动态SQL3来进行查询
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失败*/
printf("fetch cursor fail,sqlcode=%ld,sqlserr=%s",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
}
if( sqlca.sqlcode == SQLNOTFOUND)
{
break;
}
}
EXEC SQL CLOSE cur_user_name;
下文示例中Case5也是采用这种方法.
//Proc 示例
#include <stdio.h>
#include <string.h>
#include <math.h>
#include <stdio.h>
#include <stdlib.h>
#include "sqlca.h"
#include <ctype.h>
//变量，过程预声明
int i;
char screen[1];
char cmd[1];
//**********************************************************
//CASE对应与db_selectop的switch
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 *);
//主函数
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();
}
}
//打开数据连接
int db_connect()
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oraCN[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(oraCN.arr,"workflow/workflow@if");
oraCN.len = strlen(oraCN.arr);
oraCN.arr[oraCN.len]='\0';
//EXEC SQL WHENEVER SQLERROR GOTO cnError;
EXEC SQL CONNECT :oraCN;
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;
}
//选择数据操作
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\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='请假';
EXEC SQL SELECT COUNT(DISTINCT FLOW_NAME) INTO :oraCount FROM CLASS_FLOW WHERE FLOW_CLASS='请假';
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("%-8s",oraValue.arr);
printf("%-20s",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;
//接受屏幕数据
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语句
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");
//读取内容
EXEC SQL PREPARE sqlDy FROM :oraSql;
EXEC SQL DECLARE curDyField CURSOR FOR sqlDy;
EXEC SQL OPEN curDyField;
//组合SELECT COUNT语句
strcpy(oraCountSql.arr,"SELECT COUNT(*) FROM ");
strcat(oraCountSql.arr,oraTable.arr);
oraCountSql.len=strlen(oraCountSql.arr);
oraCountSql.arr[oraCountSql.len]='\0';
//读取数
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\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("%-8s","CODE");
printf("%-20s","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("%-8s",oraValue.arr);
printf("%-20s",oraName.arr);
}
printf("\n--------------------");
EXEC SQL CLOSE curTable;
}
//暂停屏幕
void pause()
{
printf("\n\n--Press Enter To Continue--");
gets(screen);
}
//显示意外错误
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();
}
|
