ORACLEݿPROC̾

촺  
02-1-18  02:32:06

--------------------------------------------------------------------------------
 

PROCORACLEݿṩı̽ӿ֮һӦʮֵĹ㷺ͨһӣPROC̵һЩ鼰Ӧעĵط 
 
ӳ 
#include <stdio.h> 
#include <string.h> 
#include <stdlib.h> 
#include <sqlda.h> 
#include <sqlcpr.h> 
 
 
EXEC SQL INCLUDE sqlca; 
/*RELEASE_CURSOR=YES ʹPROC ִͷǶSQLйԴ*/ 
EXEC ORACLE OPTION (RELEASE_CURSOR = YES); 
 
EXEC SQL BEGIN DECLARE SECTION; 
varchar vc_user[20]; 
long al_empno=0; 
char ac_ename[11]=""; 
char ac_hiredate[20]=""; 
double af_sal=0; 
 
EXEC SQL VAR ac_ename IS STRING(11); 
EXEC SQL VAR ac_hiredate IS STRING(20); 
 
EXEC SQL END DECLARE SECTION; 
 
 
/**/ 
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/tiger@DEMO"); 
vc_user.len=16; 
exec sql connect :vc_user; 
 
EXEC SQL DECLARE cur_emp CURSOR FOR  
SELECT EMPNO, ENAME,to_char(HIREDATE,'yyyy/mm/dd hh24:mi:ss'),SAL FROM EMP; 
 
EXEC SQL OPEN cur_emp; 
while(1) 
{ 
al_empno=0; 
strcpy(ac_ename,""); 
strcpy(ac_hiredate,""); 
af_sal=0; 
EXEC SQL FETCH cur_emp INTO :al_empno, :ac_ename:ename_ind, :ac_hiredate:hiredate_ind, :af_sal:sal_ind; 
if( sqlca.sqlcode == 1403) 
{ 
break; 
} 
printf("empno=%ld,ename=%s,hiredate=%s,sal=%lf\n",al_empno,ac_ename,ac_hiredate,af_sal);  
}  
EXEC SQL CLOSE cur_emp; 
EXEC SQL ROLLBACK WORK RELEASE; 
} 
 
 
 
1 
PROC,SQLõıΪӦEXEC SQL BEGIN DECLARE SECTION;EXEC SQL EDN DECLARE SECTION; 
֮ʾ.ʱӦע¼㣺 
(1) ݿжΪVARCHAR2VARCHARCHARֶΣPROCпΪCHARӦΪڱжĳȼ1ΪPROC 
CHARͱ\0β 
 
磺ENAMEڱеĶΪename varchar2(10)PROCпɶΪ 
EXEC SQL BEGIN DECLARE SECTION; 
char ename[11]; 
EXEC SQL END DECLARE SECTION; 
˵: 
ַȴ10磺EXEC SQL INSERT INTO EMP(ENAME) VALUES('12345678901');´: 
error:ORA-01480: STR ֵȱٿպ׺ 
 
Ϊ: 
EXEC SQL BEGIN DECLARE SECTION; 
char ename[15]; 
EXEC SQL END DECLARE SECTION; 
 
ַȴ10С15ʱ,磺EXEC SQL INSERT INTO EMP(ENAME) VALUES('12345678901');´: 
error:ORA-01401: ֵй 
ַȴ15磺EXEC SQL INSERT INTO EMP(ENAME) VALUES('12345678901234');´: 
error:ORA-01401:STR ֵȱٿպ׺ 
 
(2) SQLȡֶεֵʱPROCԶȥҿո񡣶DECLARE SECTION жĳΪ׼( ж޹)㲹ҿո.עһ㣬PROCнַʱȽȣ: 
EXEC SQL BEGIN DECLARE SECTION; 
char ename[10]; 
EXEC SQL END DECLARE SECTION;  
ENAMEڱеֵΪ'abc',ȡֵΪ'abc '; 
 
EXEC SQL VARضCHARͱԶȥҿո:  
EXEC SQL BEGIN DECLARE SECTION; 
char ename[11]; 
EXEC SQL VAR ac_ename IS STRING(11); 
EXEC SQL END DECLARE SECTION; 
ENAMEڱеֵΪ'abc',ȡֵΪ'abc'; 
 
(3) Ը͵ıΪ֤ȣDOUBLE͵.ΪDOUBLE͵ľȱFLOAT͸ߺܶ. 
(4) ͿΪLONG(Խϳ,õƽֵ̨֧Ļ,SUNƽ̨,ΪLONG LONG). 
(5) DATE͵ĴDATEһΪCHAR(20) 
вDATEʱ,һTO_DATE()ת,ȡֵʱһTO_CHAR()ת. 
EXEC SQL select to_char(hiredate,'yyyy/mm/dd hh24:mi:ss') into :ac_hire_date from EMP where empno=1234; 
EXEC SQL insert into EMP(EMPNO,HIREDATE) values(123,to_date(:ac_hiredate,'yyyy/mm/dd hh24:mi:ss'); 
 
 
2÷Χ 
еĺ֮ȫֱʹ֮ǰҪעѱֵʼҲĳڲ塣 ʱǾֲһ㶼ϰ߰Ϊȫֱ 
 
3ݿϿ 
ݿַ 
(1)  
strcpy(vc_user.arr,"scott/tiger"); 
vc_user.len=11; 
exec sql connect :vc_user; 
(2) 
strcpy(user,"scott"); 
strcpy(pass,"tiger"); 
exec sql connect :user identified by :pass; 
ע⣺Щƽֶ̨,Щƽֻ̨õһַ. 
PROCУҪסEXEC SQL ROLLBACK WORK RELEASE;Ͽݿ,ͷصݿԴ 
 
 
4PROCеNULLֵĴ 
ĳһֶȡֵNULL,ᱨ:sqlcode=-1405, sqlerr=ORA-01405: ȡֵΪ NULL 
Ӧֵᱻı,ΪִиSQL֮ǰֵ. õĴNULLֵķУ 
(1)ָʾ,ʱ-1405,ΪNULLֶζӦָʾ,ĳһֶûָʾ,ȡֵ 
ΪNULLֵ,Ȼ-1405.ȡֵNULLʱӦָʾΪ-1ɸָʾֵӦĴ 
(2)ֶν϶,ȡֶεһṹмýṹӦָʾṹ.пɶṹ壺 
struct str_emp{ 
long al_empno; 
char ac_ename; 
char ac_hiredate; 
double af_sal; 
}; 
struct str_emp_ind{ 
long al_empno; 
char ac_ename; 
char ac_hiredate; 
double af_sal; 
}; 
 
struct str_emp str_emp; 
strcut str_emp_ind str_emp_ind; 
ȡ֮ǰmemset(&str_emp,0,sizeof(str_emp)).ոýṹ,ַ͵NULL,Ϊ"",͵NULLΪ0, 
͵ĻΪ0.00ʱ-1405 
(3)ҲɲNVL():£ 
EXEC SQL DECLARE authors CURSOR FOR  
SELECT EMPNO, NVL(ENAME,chr(0)),nvl(to_char(HIREDATE,'yyyy/mm/dd hh24:mi:ss'),chr(0)),NVL(SAL,0) FROM EMP; 
Ҳ-1405,ȡֵNULLʱ,ԶNVL()ֵָ. 
CHR(0)Ҳֱ'',: 
SELECT EMPNO, NVL(ENAME,''),nvl(to_char(HIREDATE,'yyyy/mm/dd hh24:mi:ss'),''),NVL(SAL,0) FROM EMP; 
 
 
5PROCеĴĴ 
еSQL䶼пܳ.ԶҪж,ÿSQL󶼼Ӵж,̫鷳,һsql_error()д, 
: 
(1)ql_error() 
(2)ڿͷEXEC SQL WHENEVER SQLERROR DO sql_error();sqlca.sqlcode <0 Ĵʱ,Զתsql_error()ִ. ע:sqlca.sqlcode >0Ĵ sqlca.sqlcode =1403 ǲתsql_error()ִе. 
:UNIX£OERR Ҵ磺 ora ORA -1405 ҴΪ-1405. 
 
 
6PROCеô洢̵ķ 
ҪѴ洢̷EXEC SQL EXECUTE  END-EXEC;֮䣬ʾ 
Уal_empno,ac_ename Ϊ,l_return,l_errno,c_errtext Ϊ 
al_empno=8888; 
strcpy(ac_ename,"ABCD"); 
EXEC SQL EXECUTE  
BEGIN 
up_db_emp(:al_empno,:ac_ename,:l_return,:l_errno,:c_errtext); 
END; 
END-EXEC; 
if (l_return != 0) 
{ 
printf("UP_PB_EMP洢̳,errno=%ld,errtext=%s\n",l_errno,c_errtext); 
} 
 
7PROCѡPROCкܶѡֱӲPROCгеѡ˵ 
(1):봢Ҫû 
proc USERID=scott/tiger sqlcheck=SEMANTICS ireclen=512 iname=test.cpp 
(2)PARSE=NONE ԷSQL벻﷨Ĭ϶ԷSQLҲ﷨. 
RED HAD6.3ϵORACLE8.1.5PROCʱ,ʾ:/USR/INCLUDE/STDIO.H .Hļд. ɰPARSE=NONE,ͺ. 
 
 
8עϣEXEC ORACLE OPTION (RELEASE_CURSOR = YES); 
RELEASE_CURSOR=YES ʹPROC ִͷǶSQLйԴ֤ڸPROCִORACLEסݿԴȡ 
PROCõORACAҪڳͷϣ 
EXEC ORACLE OPTION (ORACA=YES); 
 
9PROCеת 
һC: 
(1)ַ͵ͿATOI() ATOL(),SSCANF() 
(2),͵ַ,SPRINTF() 
(3)ַ͵ATOF(),SSCANF(),: 
 
EXEC SQL BEGIN DECLARE SECTION; 
double d_demo; 
float f_demo; 
char ac_text[20]="222"; 
EXEC SQL END DECLARE SECTION;  
 
(1)sscanf(ac_text, "%f", &d_demo); 
printf("ac_text=%s,d_demo=%f\n",ac_text,d_demo); 
 
(2)sscanf(ac_text, "%lf", &d_demo); 
printf("ac_text=%s,d_demo=%f\n",ac_text,d_demo); 
 
(3)sscanf(ac_text, "%f", &d_demo); 
printf("ac_text=%s,d_demo=%lf\n",ac_text,d_demo); 
 
(4)sscanf(ac_text, "%lf", &d_demo); 
printf("ac_text=%s,d_demo=%lf\n",ac_text,d_demo); 
 
printf("*******************\n"); 
(5)sscanf(ac_text, "%f", &f_demo); 
printf("ac_text=%s,f_demo=%f\n",ac_text,f_demo); 
 
(6)sscanf(ac_text, "%lf", &f_demo); 
printf("ac_text=%s,f_demo=%f\n",ac_text,f_demo); 
 
(7)sscanf(ac_text, "%f", &f_demo); 
printf("ac_text=%s,f_demo=%lf\n",ac_text,f_demo); 
 
(8)sscanf(ac_text, "%lf", &f_demo); 
printf("ac_text=%s,f_demo=%lf\n",ac_text,f_demo); 
 
Ľ:  
ac_text=222.00,d_demo=0.000000  
ac_text=222.00,d_demo=222.000000  
ac_text=222.00,d_demo=222.000032  
ac_text=222.00,d_demo=222.000000  
*******************  
ac_text=222.00,f_demo=222.000000  
ac_text=222.00,f_demo=0.000000  
ac_text=222.00,f_demo=222.000000  
ac_text=222.00,f_demo=0.000000  
d_demo=atof(ac_text); 
printf("ac_text=%s,atof(ac_text)=%f\n",ac_text,d_demo); 
 
d_demo=atof(ac_text); 
printf("ac_text=%s,atof(ac_text)=%lf\n",ac_text,d_demo); 
 
f_demo=atof(ac_text); 
printf("ac_text=%s,atof(ac_text)=%f\n",ac_text,f_demo); 
 
f_demo=atof(ac_text); 
printf("ac_text=%s,atof(ac_text)=%lf\n",ac_text,f_demo); 
 
Ľ:  
ac_text=222.00,atof(ac_text)=1243288.000000 
ac_text=222.00,atof(ac_text)=1243288.000000 
ac_text=222.00,atof(ac_text)=1243288.000000 
ac_text=222.00,atof(ac_text)=1243288.000000 
 
Ľɼ: 
DOUBLEӦsscanf(ac_app_capcity, "%lf", &d_app); ӡ"%lf","%f" . (2),(4)ȷ 
FLOATӦsscanf(ac_app_capcity, "%f", &d_app); ӡ"%lf","%f" . (5),(7)ȷ 
ATOF()תĽǴ,ԲҪ 
 
дӱȡʱ: 
(1)ַ֮ɲת,ĬϷʽ 
(2)ַ븡֮ɲת,ĬϷʽ 
(3)ַ֮TO_CHAR(),TO_DATE() 
 
 
10PROCе4ֶ̬SQL 
(1)̬SQL1: ǲѯ(SELECT),û.  
÷:ƴһ̬SQL,EXECUTE IMMEDIATEִ,: 
EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE dyn1 (col1 VARCHAR2(4))"; 
 
(2)̬SQL2: ǲѯ(SELECT),Ŀ֪, 
÷:ƴһ̬SQL,PREPARE,EXECUTEִ. 
strcpy(c_sql, "DELETE FROM EMP WHERE EMPNO = :?");  
EXEC SQL PREPARE sql_stmt FROM :c_sql; 
EXEC SQL EXECUTE sql_stmt USING :emp_number;  
 
(3)̬SQL3: ڴ̬ѯ, ҪѯֶμĿ֪ 
÷: ƴһ̬SQL,PREPARE,ҪһCURSORȡֵ 
:Ҫѯݰһ12·ŵ12űСΪuser_fee_1mon, user_fee_2mon,....ɲö̬SQL3вѯ 
strcpy(c_sql,"select c_user_id,c_user_name,to_char(t_date,'yyyy/mm/dd hh:mi:ss'),n_fee\n"); 
strcat(c_sql,"from USER_FEE_"); 
strcat(c_sql,ac_mon); 
strcat(c_sql," \n where c_user_id = :v1"); 
 
EXEC SQL PREPARE s FROM :c_sql; 
 
EXEC SQL DECLARE cur_user_fee CURSOR FOR s; 
 
EXEC SQL OPEN cur_user_fee USING :ac_user_id; 
 
while(1) 
{  
 
EXEC SQL FETCH cur_user_fee into :c_user_id,:c_user_name,:c_date,:n_fee); 
 
if (sqlca.sqlcode < 0) 
{  
/*FETCH CURSORʧ*/  
printf("fetch cursor cur_user_fee fail,sqlcode=%ld,sqlserr=%s",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc); 
} 
if( sqlca.sqlcode == SQLNOTFOUND) 
{ 
break; 
} 
} 
EXEC SQL CLOSE cur_user_fee;  
 
(4)̬SQL4:ҪֶμĿǲ֪,磺 
INSERT INTO EMP (<unknown>) VALUES (<unknown>) 
ӵĶ̬SQL,,ڴ˲ܡ 
 
 
11SQLCASQLORACLEһṹ壬һSQLִкһЩϢţ棬״̬ȡõ 
: 
SQLCA.sqlcode:,=0ȷ,=1403ûȡ 
SQLCA.sqlserrm.sqlerrmc: 
SQLCA.sqlerrd[3]:һSQL,䴦ʧ,ֵǲ,һCURSORз, 
ֵָѳɹ.DELETE,UPDATE,Լɾ,µЩ,  
DELETE FROM EMP WHERE DEPT='SALE'; 
ڱEMPɾ20,EMPADDRESSԼ,±ADDRESSҲɾ20,SQLCA.sqlerrd[3]=20,40 

 
