数据库procc实践通过在过程化编程语言中嵌入SQL语句而开发出的应用程序

C/C++SQL语句而开发出的应用程序SQLSQLSQLSQL的具体实现不同Pro*C/C++程序C/C++SQLPro*C/C++目的：使c/c++这种高效率语言成为数据库的工具嵌入式SQL的载体是宿主语言 Pro程序 （1）用SQL*Plus，它有SQL命令以交互的应用程序数据库（2）用第四代语言应用开发工具开发的应用程序数据库，这些工具 （3）利用在第三代语言内嵌入的SQL语言或ORACLE库函数调用来oracle数据库的方法ThetermembeddedSQLreferstoSQLstatementscedwithinanapplicationprogram,andthelanguageinwhichitiswritteniscalledthehostlanguage.Forexample,Pro*C/C++providestheabilitytoembedcertainSQLstatementsinaCorC++host1开档路径：F:/E11882_01_oracle文档/2 pilerConcepts/KeyConceptsofEmbeddedSQLProgramming/2SQL书路径：左上角booklistSQLDeveloperUser'sGuideSQLLanguageQuickReferenceSQLLanguagePro*C程序实例#include<stdio.h>#includestring.h>#includestdlib.h>#include"sqlca.h"EXECSQLBEGINDECLAREcharusername[32];charpassword[32];chardname[25];EXECSQLENDDECLARESECTION;EXECSQLINCLUDEsqlca;intmain(void){EXECSQLWHENEVERSQLERRORDO EXECSQLCONNECT:usernameIDENTIFIEDBY:password;EXECSQLselectdnameinto:dnamefromdeptwhereid=10;printf(“dname:%s\n”,dname);return}void{EXECSQLWHENEVERSQLERROR oracleerrorEXECSQLROLLBACKWORKRELEASE;}Pro*CInclude(c/c++and连结数据库：SQL操作语句 EXEC exceptionEXECSQLCOMMIT/ROLLBACKWORKpilePro*CCprociname=filenameINAME=pathandfilename(nameoftheinputfile)1.pcONAME=pathandfilename(nameoftheoutputfile)1.c --INCLUDEINCLUDE1,PARSE=FULL|PARTIA|NONE(defaultFULLforC,OthersforC++)CODE=ANSI_C|CPP(defaultansi_c) 按照c++规范解析文件dm02_o.pc procdm02_o.pcparse=nonecode=cpponame=dm02_1、procPro*C/C++:Release11.2.0.1.0-ProductiononThuSep1107:59:372014Copyright(c)1982,2009,Oracleand/orits All OptionName CurrentValue Allowautomaticconnectiontoops$ Mapofcharacterarraysandstrings CloseallcursorsonCOMMIT CMAXValueforconnection CMINValueforconnection CINCRValueforconnection CTIMEOUTValueforconnection CNOWAITValueforconnection ParseusingCommonSQLFrontEnd Thetypeofcodetobegeneratedcomp_charsetmulti_byte ThecharactersettypetheCcompilersupports Overridesystemconfigurationfilewithanother Supportconnectionpooling OverridethedefaultC++filenamesuffix SupportDB2arrayinsert/selectsyntax v6/v7/v8compatibilitymodedef_sqlcodeno Generate'#defineSQLCODEsqlca.sqlcode'macro x86_64Defineapreprocessorsymbol Setpindurationforobjectsinthe SpecifyOracleorANSIDynamicSQLSemantics Whethererrormessagesaresenttotheterminal *none* Nameofthelistfileforintypefileerrors Supportpublish-subscribeeventnotifications FIPSflaggingofANSI pliantusage Specifyfileextensionfor piledHeaders Controlholdingofcursorsinthecursorcache Implicitsavepointpriortobufferedinsert Thenameoftheinputfile Directorypathsforincluded Thenameoftheinputfilefortypeinformation Add#linedirectivestothegeneratedcode Overridedefaultlistfile Theamountofdatageneratedinthelistfile umlengthofageneratedstring umnumberofcachedopenmax_row_insert0 umnumberofrowstobufferoninsert CodeconformancetoOracleorANSIrules Supportfornative SpecifyNationalLanguagecharactervariables ControlhowNLScharactersemanticsaredone Supportobjecttypes Thenameoftheoutputfile Controltheuseofthe CategoryinwhichOutlinesare Outlinename Thepagelengthofthelist Controlwhichnon-SQLcodeis Numberofrowspre-fetchedatcursorOPENrelease_cursor Controlreleaseofcursorsfromcursor Ifyes,createOutlinesinthe Controlflaggingofselect Amountofcompile-timeSQLchecking Sizeofstatementcache /usr/lib/gcc/x8Directorywheresystemheaderfilesare Indicatesamulti-threaded UseOracleorANSItypecodesforDynamicSQL AllowaNULLfetchwithoutindicatorvariable Ausername/password[@dbname]connectstring ThecharactersetformusedbyUTF16variables no Allowtheuseofimplicitvarcharstructures WhichversionofanobjectistobePCC-F-02135,CMD- UseraskedforPro*CPro*C++//1//2//3///////通过嵌入式语言定义接EXECSQLDECLARElink1EXECSQLCONNECT{:userIDENTIFIEDBY:oldpswd|:usr_psw}[[AT{dbname|:host_variable}]USING:connect_string[{ALTERAUTHORIZATION:newpswd|IN{SYSDBA|SYSOPER}MODE}]方法 EXECSQLCONNECT方法 EXECSQLCONNECT:usernameIDENTIFIEDBY:password/*connecttothenondefaultdatabaseusingdb_name*/EXECSQLCONNECT:usernameIDENTIFIEDBY:passwordAT:db_nameUSING/*declareneededhostvariables*/charusername[10]="scott";charpassword[10]="tiger";chardb_string[20]=/*givethedatabaseconnectionauniquename*/EXECSQLDECLAREDB_NAMEDATABASE;/*connecttothenondefaultdatabaseEXECSQLCONNECT:usernameIDENTIFIEDBY:passwordATDB_NAMEUSING:db_string;EXECSQLBEGINDECLARESECTION; *usrname= *passwd= *link1="link1"; *serverid= *usrname2= *passwd2= *link2= *serverid2=EXECSQLENDDECLAREint{intret=EXECSQLCONNECT:usrnameIDENTIFIEDBY:passwdAT:link1USING:serverid;if(sqlca.sqlcode!=0){ret=printf("sqlca.sqlcode:err:%dreturn}elseprintf("connect}EXECSQLCONNECT:usrname2IDENTIFIEDBY:passwd2AT:link2USING:serverid2;if(sqlca.sqlcode!=0){ret=printf("sqlca.sqlcode:err:%dreturn}elseprintf("connect}EXECSQLAT:link1COMMITif(sqlca.sqlcode!=0)ret=printf("1sqlca.sqlcode:err:%dreturn}elseprintf("1RELEASE}EXECSQLAT:link2COMMITif(sqlca.sqlcode!=0)ret=printf("sqlca.sqlcode:err:%dreturn}elseprintf("第二个用户断开数据库成功RELEASE}returnret}//使用oracle，通过程序连接多个数据 EXECSQLDECLARElink1EXECSQLBEGINDECLARE *usrname= *passwd= *link1= *serverid= *usrname2= *passwd2= *link2= *serverid2=EXECSQLENDDECLAREint{intret=EXECSQLDECLARElink1EXECSQLDECLARElink2EXECSQLCONNECT:usrnameIDENTIFIEDBY:passwdATlink1if(sqlca.sqlcode!=0)ret=printf("sqlca.sqlcode:err:%dreturn}elseprintf("connect}EXECSQLCONNECT:usrname2IDENTIFIEDBY:passwd2ATlink2;if(sqlca.sqlcode!=0)ret=printf("sqlca.sqlcode:err:%dreturn}elseprintf("connect}EXECSQLATlink1COMMITif(sqlca.sqlcode!=0)ret=printf("1sqlca.sqlcode:err:%dreturn}elseprintf("1RELEASE}EXECSQLATlink2COMMITif(sqlca.sqlcode!=0)ret=printf("2sqlca.sqlcode:err:%dreturn}elseprintf("2RELEASE}returnret}pro*c语言变量,oraclePro*C程序中，既可在SQL语句中，也可在C语句中的变量称为SQL变量 N cin>>salary;EXECSQLupdateempsetsal=:salarywhereempno=floatv_salary;charv_job;EXECSQLselectsal,jobINTO:v_salary,:v_jobfromempwhereempno=7788;其语法与普通C变量一致，但在CODE=CPP或时变量必须放在区pointerDDLchartable_name[30];EXECSQLDROPTABLECHAR_MAP=CHARZ默认设置0’结尾，定长，空格补齐。CHAR_MAP=CHARF|VARCHAR2：定长，空格补齐。CHAR_MAP=STRING：‘\0’变长,不是‘\0’structunsignedshortlen;unsignedchararr[}NULL 主要用在输出,即当宿主变量用于接收数据库的返回数据时.在宿主变量后使用指示变量,NULL.=0：OracleEXECSQLBEGINDECLARE EXECSQLENDDECLARE…EXECSQLSELECTemp_descINTO:desc:ind_descFROMempWHEREIf(ind_desc<cout<<desc<<ORACEsqlC语言使用CORACLE的数据类型，在数据传递时有一个数据类型转换的ORACLE的内部数据类型Oracle数据就是Oracle数据 的数据类型VARCHAR24000字节(Oracle92k)CHAR2000字节NUMBER(p,s)：数字类型，p精度，sRAW2000LONG2GLONGRAW2GBFILE：OS文件数据ORACLEOracle外部数据类型来定义宿主变量。 STRING：以NULL终止的变长字符串 CHARZ：NULL终止定长字符CHARF：等价CHAR的字符数据类 SQL数据类型char[32]OracleOracle1。EXECSQLVARhost_variableIStype_name[(length)]; EXECSQLVARemp_nameIS2EXECSQLTYPEuser_typeIStype_name[(length)];typedefstruct{ }EXECSQLTYPEgraphicsISVARRAW(4000);typedefchardnameType[20];typedefcharlocType[20];EXECSQLBEGINDECLAREEXECSQLTYPEdnameTypeisstring(20);EXECSQLTYPElocTypeisstring(20); *usrname= *passwd= *serverid= EXECSQLENDDECLAREEXECSQLBEGINDECLARE *usrname="scott"; *passwd="tiger"; *serverid= dname2[100][20];varcharchar EXECSQLENDDECLAREPro*CPro*CPro*C32767EXECSQLBEGINDECLARESECTIONintA[100];charcharEXECSQLENDDECLARESQLEXECSQLBEGINDECLAREintcharemp_name[100][15];EXECSQLENDDECLARESECTION;EXECSQLSELECTnumber,nameINTO:emp_number,:emp_nameFROMfor(inti=0;i<100;i++)EXECSQLSELECTnumber,nameINTO:emp_number[i],}EXECSQLBEGINDECLAREintcharemp_name[100][15];shortEXECSQLENDDECLARE…EXECSQLSELECTnumber,nameINTO第一个SQLCAORACLESQLCA实际上是一个结构变量，其目的是为了诊断错误和处理结果struct{//sqlca,SQLchar//SQLlong//最近执行的SQL语句的状态 //<0:数据库,系统,网络故障,SQLlongstructsqlerrmcunsignedshort//sqlcode//只有当sqlcode<0才能,否则是上次错误信息charsqlerrmc[70];charsqlerrp[8];],],//sqlerrd[2]SQL语句处理的行数,如果SQL执行失败,longsqlerrd[6];////sqlwarn[4]wheredelete,updatecharsqlwarn[8];char0，1，3，5 －当前SQL －保存相对位移，在什么地方出现语法错sqlcode用于保存最近一次运行SQL 4－UPDATEDELETESQLCAint{…EXECSQLINCLUDE…EXECSQLWHENEVERSQLERRORDo…}voiderr_report(structsqlcasqlca){if(sqlca.sqlcode<0)printf("\n%d-%s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);}第二个当需要更进一步的信息时，ORACAORACASQLCA的 oraca/*text*/char /* *//*ub4*/int /* *//*ub4*/int /*<>0if"checkcurcache /*ub4*/int /*<>0if"doDEBUGmode /*ub4*/int /*<>0if"doHeapconsistency /*ub4*/intorastxtf; /*SQLstmttextflag struct{/*ub2*/unsignedshort/*text*/char}orastxt; /*textoflastSQLstmt struct{/*ub2 unsignedshort/*text } /*nameoffilecontainingSQL /*ub4*/ /*linenr-within-fileofSQL /*ub4*/ /*highestmaxopenOraCursrequested/*ub4*/ /*maxopenOraCursors /*ub4*/ /*currentOraCursors /*ub4*/ /*nrofOraCursorre- /*ub4*/ /*nrof /*ub4*/ /*nrof oracaid[8]ORACAoracabcORACAoradbgf调试标志，0－关闭，1oracchforadbgf＝1oracchf0－光标缓冲一致性检orahchforadbgf＝1oracchf0－堆缓冲一致性检orastxtf0－不保存SQL1SQLERROR保存SQL2SQLERRORSQLWARNING保存SQL3－总是保存SQLorastxt.orastxtl当前SQLorastxtorasfnmc[70]SQL70orasfnm.orasfnmc70oraslnr标识当前SQLorahocoramocoracocoranororanprSQLoranexSQLORACAORACAPro*CEXECSQLINCLUDEEXECORACLE//EXECSQLEXECSQLWHENEVER<condition><aciton>NOTFOUND MODE=ORACLEsqlca.sqlcode=1403s编译选项MODE=ANSIsqlca.sqlcode=100手册参考EXECSQLWHENEVERSQLERRORDO //sqlerrEXECSQLWHENEVERNOTFOUNDEXECSQLWHENEVERSQLERRORGOTOEXECSQLWHENEVERSQLERRORCONTINUE;EXECSQLROLLBACKRELEASE;EXECSQLWHENEVERSQLERRORGOTOEXECSQLWHENEVERSQLERRORCONTINUE;EXECSQLROLLBACKRELEASE;printf("\nInvalidusername/password\n");sqlplussqlplus通过.bash_profileNLS_LANGexportNLS_LANG=”SIMPLIFIED_exportNLS_LANG=”SIMPLIFIED_sqlplusoracleserverSQL>selectuserenv('language')fromdualumaskexportviUEUE的文件/转换/ASCIIUTF-8(Unicode编辑[oracle@localhost02procerr]$oerrora-procrhel6.4oracleprocstddef.h[oracle@localhostadmin]$catpcscfg.cfg define= [oracle@localhostadmin]$locategcc版本gcc–v,最接近的.[oracle@localhostadmin]$Pro*C/C++:Release11.2.0.1.0-ProductiononWedSep1020:08:132014Copyright(c)1982,2009,Oracleand/orits Allrights Erroratline34,column11infile/usr/include/stdio.h#include<stddef.h> PCC-S-02015,unabletoopenincludeErroratline15,column10infile/usr/include/_G_config.h#include<stddef.h> PCC-S-02015,unabletoopenincludeErroratline52,column11infile/usr/include/wchar.h#include<stddef.h> PCC-S-02015,unabletoopenincludeErroratline53,column11infile/usr/include/libio.h#include<stdarg.h> SQLCommit,rollbackandsavepoint.EXECSQLcommit;EXECSQLcommitworkEXECSQLrollbackworkEXECSQLCREATTABLEt1(colaint);EXECSQLDROPTABLEt1;EXECSQLALTERTABLEt1add(faxno对象名,列名不能用宿主变量Alter:altertableempadd(faxnonumber);yze:yzetableEXECSQLSelect…….;EXECSQLInsert…….;EXECSQLupdateEXECSQLDelete用多个变量或结构（struct）（array）（cursor）charename[35];intempno=20;EXECSQLselectemp_nameINTO:enamefromempwhereemp_no=:empno;cout<<“thenameis“<<charv_name[31],charv_job[21];floatv_salary;intEXECSQLselectemp_name,job,salaryINTO:v_name,:v_job,:v_salaryFROMWHERECstructintcharname[10];intsalary;}输入：EXECSQLINSERTINTOemp(empno,ename,sal)VALUES(:emp_record);输出：EXECSQLSELECTempno,ename,salINTO:emp_recordWHERErownum=1;SQLC联合structintcharname[10];intsalary;}emp_record=(structemp_record*)malloc(sizeof(structemp_record));EXECSQLSELECTempno,ename,salINTO:emp_recordWHERErownum=1;INSERTintnum[100];floatsalary[100];char/**ARRAYEXECSQLINSERTINTOemp(empno,ename,sal)VALUES(:num,:name,UPDATEDELETECharname[100];float/**ARRAYEXECSQLUPDATEEMPSETsal=:salaryWHEREChar/**ARRAYEXECSQLDELETEFROMempWHERESELECTCharEXECSQLSELECTenameINTO:nameFROMempWHEREdept_num=2;For(intj=0;j<sqlca.sqlerrd[2];j++)CHARARCHARINDEXSELECTWHEREintmain_01(){intret=0;inti=0;//EXECSQLWHENEVERSQLERRORDO//EXECSQLWHENEVERNOTFOUNDDOprintf("\npleaseenterdeptno:");scanf("%d",&deptno);EXECSQLselectdeptno,dname,locinto:deptno,:dname:dname_ind,:loc:loc_indfromdeptwhereif(sqlca.sqlcode!=0){ret=sqlca.sqlcode;printf("sqlca.sqlcode:err:%d\n",sqlca.sqlcode);return;}if(dname_ind==-1)printf("dname为空}if(loc_ind==-1)printf("loc为空}printf("deptno:%d,dname:%s,loc:%s\n",deptno,dname.arr,loc.arr);loc_ind=-1;EXECSQLupdatedeptsetloc=:loc:loc_indwheredeptno=:deptno;if(sqlca.sqlcode!=0){ret=printf("sqlca.sqlcode:err:%d\n",sqlca.sqlcode);return;}EXECSQLCOMMITWORK//dept2returnret}EXECSQLDECLAREemp_cursorCURSORselectempno,ename,salfromempwhereEXECSQLOPENemp_cursor(Opencursor:puttheselectresultsintoamemoryce,andthecursorpointerpointstothefirstrowdata.)fetchEXECSQLFETCHemp_cursorINTO/**在此处理数据处理(Afterfetch,cursorpointermovesdownoneline.Useloopformultiplerows)EXECSQLCLOSEOpencursor:puttheresultsintoamemoryce,andthecursorpointerpointstothefirstrowtypedefchardnameType[20];typedefcharlocType[20];EXECSQLBEGINDECLAREEXECSQLTYPEdnameTypeisstring(20);EXECSQLTYPElocTypeisstring(20);char*usrname="myscott";char*passwd="22";char*serverid="orcl"; EXECSQLENDDECLAREint{intret=0;inti=0;EXECSQLWHENEVERSQLERRORDO//EXECSQLWHENEVERNOTFOUNDDO//1定义游标declarecursorEXECSQLDECLAREdept_cursorCURSORFORselectdeptno,dname,locfromdept;//2打开游标opencursorEXECSQLOPENdept_cursor;//3fetchEXECSQLWHENEVERNOTFOUNDDOwhile{EXECSQLFETCHdept_cursorINTO:deptno,:dname:dname_ind,printf("条目数:%d\t",printf("%d\t%s\t%s",deptno,dname,loc);if(dname_ind==-1){printf("dnameisnull}if(loc_ind==-1)printf("locisnull}}//4关闭游标closedataEXECSQLCLOSEdept_cursor;EXECSQLCOMMITWORKreturnret}#include<stdio.h>#include<string.h>#include<stdlib.h>#include"sqlca.h"//externsqlgls(char*,size_t*,size_t*externsqlglmt(void*,char*,size_t*,size_t*);voidconnet(void);voidsqlerr02(void){charunsignedintret=0;//出错时,可以把错误SQLEXECSQLWHENEVERSQLERRORret=sqlgls(stm,&stmlen,printf("SQL:%.*s\n"stmlen,,//printf("出错原因:%.70s\n"sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);EXECSQLROLLBACKWORKRELEASE;}void{intret=if(sqlca.sqlcode!=0){ret=printf("sqlca.sqlcode:err:%d\n",sqlca.sqlcode);return;}}typedefchardnameType[20];typedefcharlocType[20];EXECSQLBEGINDECLAREEXECSQLTYPEdnameTypeisstring(20);EXECSQLTYPElocTypeisstring(20);char*usrname="myscott";char*passwd="22";char*serverid="orcl"; dname;string EXECSQLENDDECLAREint{intret=0;inti=0;EXECSQLWHENEVERSQLERRORDO//EXECSQLWHENEVERNOTFOUNDDO//1定义游标declarecursorEXECSQLDECLAREdept_cursorSCROLLCURSORselectdeptno,dname,locfrom//2打开游标opencursorEXECSQLOPENdept_cursor;//3fetch//while(1)//EXECSQLWHENEVERNOTFOUNDDO//EXECSQLFETCHLASTdept_cursorINTO:deptno,:dname:dname_ind,printf("%d\t%s\t%s\n",deptno,dname,loc//EXECSQLFETCHFIRSTdept_cursorINTO:deptno,:dname:dname_ind,printf("%d\t%s\t%s\n",deptno,dname,loc//[1234567 //3EXECSQLFETCHABSOLUTE3dept_cursorINTO:deptno,:dname:dname_ind,printf("%d\t%s\t%s\n",deptno,dname,loc//36EXECSQLFETCHRELATIVE3dept_cursorINTO:deptno,:dname:dname_ind,printf("%d\t%s\t%s\n",deptno,dname,loc//EXECSQLFETCHNEXTdept_cursorINTO:deptno,:dname:dname_ind,printf("%d\t%s\t%s\n",deptno,dname,loc//EXECSQLFETCHPRIORdept_cursorINTO:deptno,:dname:dname_ind,printf("%d\t%s\t%s\n",deptno,dname,loc}//4关闭游标closedataEXECSQLCLOSEdept_cursor;EXECSQLCOMMITWORKreturnret}void{intret=//EXECSQLCONNECT:usrnameIDENTIFIEDBY:passwdUSING:serverid;if(sqlca.sqlcode!=0){ret=printf("sqlca.sqlcode:err:%d\n",sqlca.sqlcode);return;}else}}SQL动态动态SQLpro*c/c++SQL比较静态SQL和动态静态 在编写应用程序时，使用EXECSQL关键字直接嵌入的SQL语句；在proc编译应用动态 在运行应用程序时由用户动态输入的SQL语句（凡是像在sqlplus工具中输入的什么时候使用动态SQLSQL语句的文本（命令，子句等）的数据库对象（列，表等）■TextoftheSQLstatement(commands,clauses,andso■Thenumberofhost■Thedatatypesofhost■Referencestodatabaseobjectssuchascolumns,indexes,sequences,tables,usernames,and动态SQL1—语法：EXECSQLEXECUTEIMMEDIATE：host_stringhost_string字符串//select语言,//在sqlplus上运行令，都可以拿过来来执int{ ret= i= memset(mySql,0,sizeof(mySql));pSql=NULL;EXECSQLWHENEVERSQLERRORDOpSql=mySql;//循环处理sqlfor(;;)printf("\npleaseentersql(notselect):");//scanf("%s",mySql);--空格截断printf("mysql:%s\n",mySql); EXECSQLEXECUTEIMMEDIATEEXECSQLif(choosechar=='n'||choosechar=='N'){}}EXECSQLCOMMITWORK returnret}selectDeletefromempwhereempno=:a;Deletefromempwhereempno=1111;Selecten