动态方法1 动态方法1的处理过程是先构造一个动态SQL语句然后用EXECUTE IMMEDIATE来执行,EXECUTE IMMEDIATE的功能是分析动态的语句的文本,检查是否有错误, 如果SQL 语句仅执行一次动态方法1的效率很高,动态方法1一定不是SELECT 语句 1 EXEC SQL EXECUTE IMMEDIATE CREATE TABLE .............; 2 sprintf(host_string,""); EXEC SQL EXECUTE IMMEDIATE :host_string ; 动态方法2 方法2与方法1 类似也是能含有SELECT语句,方法2中含义虚拟输入宿主变量,比方法1 多了一步SQL语句的语法分析, 处理方式分为3步: 构造一个动态SQL 用PREPARE分析和命名该SQL 用EXECUTE来执行它 用法: sprintf(host_string,"DELETE FROM table_name WHERE no=:v1 AND name=:name");(拼带有输入宿主变量的SQL) EXEC SQL PREPARE sql_name FROM :host_string;(分析语法) EXEC SQL EXECUTE IMMEDIATE sql_name USING :v1,:name;(传递实际变量) 动态方法3 方法就是使用游标,查询并返回多行,如果在方法1和方法2中的SELECT 语句查询返回一行的话,也可以使用SELECT 语句, 方法为 SELECT column1,column2...INTO:variale1,variable2; 方法3是专门解决一次返回多行的,使用方法: 1 拼成一个 SQL sprintf(host_string,"") 2 用EXEC SQL PREPARE name FROM :host_string 来分析其语法 3 用EXEC SQL DECLARE c_name CURSOR FOR : name 来声明游标 4 用EXEC SQL OPEN c_name ; 5 用EXEC SQL FETCH c_name INTO:variable1,:variable2;取出游标中的数据 6 EXEC SQL CLOSE c_name;关闭游标 三种动态SQL方法的特点都是先在C语言中拼成所需要的SQL 文本串,然后用EXECUTE IMMEDIATE 来执行
下面是我从网上收集的可以和我的对照,ProC前三种动态SQL的完整示例。
ProC动态SQL示例(第1,2,3种方法)
草木瓜 2006-2-1
下面是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 #include #include #include #include #include "sqlca.h" #include
//变量,过程预声明 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(" ");
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,""); 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 "); 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 "); 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 "); 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 "); 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
"); 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 "); 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 "); 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(); }
阅读(4567) | 评论(0) | 转发(0) |