2.pc

来自「写一个Proc*程序,执行查询： Select * from scott.e」· PC 代码 · 共 59 行

#include <stdlib.h>
#include <stdio.h>
#include <string.h> 
EXEC ORACLE OPTION (RELEASE_CURSOR = YES); 
EXEC SQL BEGIN DECLARE SECTION; 
varchar fieldName[20];
Varchar fieldType[20];
varchar vc_user[20]; 
int empno;
VARCHAR ename[15];
VARCHAR job[15];
int mar;
VARCHAR hiredate[20];
float sal;
float comm;
int deptno;
EXEC SQL END DECLARE SECTION; 

EXEC SQL INCLUDE "SQLCA.H";
/*错误处理函数*/ 
void sql_error(char *msg) 
{ 
printf("\n%s,%ld,%s\n", msg,sqlca.sqlcode,(char *)sqlca.sqlerrm.sqlerrmc); 
EXEC SQL ROLLBACK RELEASE; 
exit(-1); 
} 
main() 
{ 
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE ERROR: "); 
/*连接数据库*/ 
strcpy(vc_user.arr,"scott/ljh@orcl"); 
vc_user.len=16; 
exec sql connect :vc_user; 
EXEC SQL DECLARE EMP_CURSOR CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMP';
EXEC SQL DECLARE cur_emp CURSOR FOR
	SELECT EMPNO,ENAME,JOB, MGR,TO_CHAR(HIREDATE), SAL,NVL(COMM,0), DEPTNO FROM EMP;
//输出表EMP的字段名和类型
EXEC SQL OPEN EMP_CURSOR;
EXEC SQL FETCH EMP_CURSOR INTO :fieldName, :fieldType; 
while(sqlca.sqlcode==0) {		fieldName.arr[fieldName.len]='\0';
		fieldType.arr[fieldType.len]='\0';
		printf("%s-%s\n",fieldName.arr,fieldType.arr);		EXEC SQL FETCH EMP_CURSOR INTO :fieldName, :fieldType;	}
	EXEC SQL CLOSE EMP_CURSOR;
//输出表的查询结果
EXEC SQL OPEN cur_emp; 
while(1) 
{ 
EXEC SQL FETCH cur_emp INTO :empno, :ename, :job,:mar,:hiredate,:sal,:comm,:deptno;
if( sqlca.sqlcode == 1403) 
{ 
break; 
} 
printf("%d %-10s %-10s %5d %-15s %10f %10f %5d\n",empno,ename.arr,job.arr,mar,hiredate.arr,sal,comm,deptno); 
} 
EXEC SQL CLOSE cur_emp; 
EXEC SQL ROLLBACK WORK RELEASE; 
}