PRO*C中使用动态游标的四种方法
? PRO*C中使用动态游标的四种方法
动态方法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)动态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的switchEXEC SQL BEGIN DECLARE SECTION;VARCHAR oraName[30]; //CASE 1,2,3VARCHAR oraValue[20]; //CASE 1,2,3,5int oraCount; //CASE 1,2,3,4,5VARCHAR oraSql[30],oraTable[20]; //CASE 4,5VARCHAR oraField[10]; //CASE 5VARCHAR oraCountSql[30]; //CASE 5VARCHAR oraCode[10]; //CASE 6VARCHAR oraContent[10]; //CASE 6EXEC 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(); }本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/depositpei/archive/2009/02/09/3870424.aspx?
?
?
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/depositpei/archive/2009/02/09/3870424.aspx