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

writtenbyC/C++SQL语句而开发出的应用程序SQLSQL的具体实现不同Pro*C/C++程序目的：使c/c++这种高效率语言成为数据库的工具嵌入式SQL的载体是宿主语言 Pro程序 （1）用SQL*Plus，它有SQL命令以交互的应用程序数据库（2）用第四代语言应用开发工具开发的应用程序数据库，这些工具 （3）利用在第三代语言内嵌入的SQL语言或ORACLE库函数调用来文档解ThetermembeddedSQLreferstoSQLstatementscedwithinanapplicationprogram.BecauseithousestheSQLstatements,theapplicationprogramiscalledahostprogram,andthelanguageinwhichitiswritteniscalledthehostlanguage.Forexample,Pro*C/C++providestheabilitytoembedcertainSQLstatementsinaCorC++Pro*C程序实例#include<stdio.h>#includestring.h>#includestdlib.h>#includesqlca.h"EXECSQLBEGINDECLAREcharusername[32];charpassword[32];chardname[25];EXECSQLENDDECLAREEXECSQLINCLUDE{EXECSQLWHENEVERSQLERRORDOEXECSQLCONNECT:usernameIDENTIFIEDBY:password;EXECSQLselectdnameinto:dnamefromdeptwhereid=10;printf(“dname:%s\n”,dname);}void{EXECSQLWHENEVERSQLERROR oracleerrorEXECSQLROLLBACKWORKRELEASE;}Pro*CInclude(c/candSQL操作语句 EXEC exceptionEXECSQLCOMMIT/ROLLBACKWORKPROCpile功能：完成Pro*CCPROCINAME=filenameINAME=pathandfilename(nameoftheinputfile)ONAME=pathandfilenamenameoftheoutputfile) --INCLUDE=路径名或INCLUDE=(1,2)PARSE=FULL|PARTIA|NONEdefaultFULLforCOthersforC++)CODE=ANSI_C|CPP(defaultansi_c)1、proc2、为什么可以直接找到，oracle用户下,bash_profilePro*C/C++:Release11.2.0.1.0-ProductiononThuSep1107:59:37Copyright(c)1982,2009,Oracleand/orits Allrights Option Allowautomaticconnectiontoops$account Mapofcharacterarraysandstrings CloseallcursorsonCOMMIT CMAXValueforconnectionpool CMINValueforconnection CINCRValueforconnection ParseusingCommonSQLFrontEnd Thetypeofcodetobegeneratedcomp_charsetmulti_byte ThecharactersettypetheCcompilersupports Overridesystemconfigurationfilewithanother Supportconnectionpooling OverridethedefaultC++filenamesuffix SupportDB2arrayinsert/selectsyntax v6/v7/v8compatibilitymodedef_sqlcodeno Generate'#defineSQLCODEsqlca.sqlcode'macro x86_64Defineapreprocessorsymbol Setpindurationforobjectsinthecache SpecifyOracleorANSIDynamicSQLSemantics Whethererrormessagesaresenttotheterminal *none* Nameofthelistfileforintypefileerrors Supportpublish-subscribeeventnotifications FIPSflaggingofANSI pliantusage Specifyfileextensionfor piledHeaders Controlholdingofcursorsinthecursorcache Implicitsavepointpriortobufferedinsert Thenameoftheinputfile Directorypathsforincludedfiles Thenameoftheinputfilefortypeinformation Add#linedirectivestothegeneratedcode Overridedefaultlistfilename Theamountofdatageneratedinthelistfile umlengthofageneratedstring umnumberofcachedopenmax_row_insert0 umnumberofrowstobufferoninsert CodeconformancetoOracleorANSIrules Supportfornativefloat/double SpecifyNationalLanguagecharactervariables ControlhowNLScharactersemanticsaredone Supportobjecttypes Thenameoftheoutputfile Controltheuseofthe CategoryinwhichOutlinesarecreated[yes/no/<categoryname>] Outlinename Thepagelengthofthelist Controlwhichnon-SQLcodeis Numberofrowspre-fetchedatcursorOPEN Controlreleaseofcursorsfromcursor Ifyes,createOutlinesinthe Controlflaggingofselecterrors Amountofcompile-timeSQLchecking Sizeofstatementcache Indicatesamulti-threadedapplication UseOracleorANSItypecodesforDynamicSQL AllowaNULLfetchwithoutindicatorvariable Ausername/password[@dbname]connectstring ThecharactersetformusedbyUTF16variables no Allowtheuseofimplicitvarcharstructures WhichversionofanobjectistobereturnedPCC-F-02135,CMD-LINE:Useraskedfor//1//2//3//通过嵌入式语言定义接EXECSQLDECLARElink1EXECSQLCONNECT{:userIDENTIFIEDBY:oldpswd|:usr_psw[[AT{dbname|:host_variable}]USING:connect_string[{ALTERAUTHORIZATION:newpswd|IN{SYSDBA|SYSOPER}MODE}]EXECSQLCONNECTEXECSQLCONNECT:usernameIDENTIFIEDBY:password/*connecttothenondefaultdatabaseusingdb_name*/EXECSQLCONNECT:usernameIDENTIFIEDBY:passwordAT:db_nameUSING/*declareneededhostvariables*/charusername[10]="scott";charpassword[10]="tiger";chardb_string[20]="NYNON";/*givethedatabaseconnectionauniquename*/EXECSQLDECLAREDB_NAMEDATABASE;/*connecttothenondefaultdatabaseEXECSQLCONNECT:usernameIDENTIFIEDBY:passwordATDB_NAMEUSING:db_string;EXECSQLBEGINDECLARE *usrname= *passwd= *link1"link1"; *serverid= *usrname2= *passwd2= *link2= *serverid2=EXECSQLENDDECLAREint{intret=EXECSQLCONNECT:usrnameIDENTIFIEDBY:passwdAT:link1;if(sqlca.sqlcode!={ret=printf(sqlca.sqlcodeerr:%dreturn}{printf(connect}EXECSQLCONNECT:usrname2IDENTIFIEDBY:passwd2AT:link2USING:serverid2;if(sqlca.sqlcode!=0){ret=printf(sqlca.sqlcodeerr:%dreturn}{printf(connect}EXECSQLAT:link1COMMITif(sqlca.sqlcode!={ret=printf(1sqlca.sqlcode:err:%dreturn}{printf(1RELEASE}EXECSQLAT:link2COMMITif(sqlca.sqlcode!={ret=printf(sqlca.sqlcodeerr:%dreturn}{printf(RELEASE}returnret}//演示使用oracle连接EXECSQLDECLARElink1EXECSQLBEGINDECLARE *usrname= *passwd= *link1= *serverid= *usrname2= *passwd2= *link2= *serverid2=EXECSQLENDDECLAREint{intret=EXECSQLDECLARElink1EXECSQLDECLARElink2EXECSQLCONNECT:usrnameIDENTIFIEDBY:passwdATlink1USING:serveridif(sqlca.sqlcode!={ret=printf(sqlca.sqlcodeerr:%dreturn}{printf(connect}EXECSQLCONNECT:usrname2IDENTIFIEDBY:passwd2ATlink2;if(sqlca.sqlcode!={ret=printf(sqlca.sqlcodeerr:%dreturn}{printf(connect}EXECSQLATlink1COMMITif(sqlca.sqlcode!={ret=printf(1sqlca.sqlcode:err:%dreturn}{printf(1RELEASE}EXECSQLATlink2COMMITif(sqlca.sqlcode!={ret=printf(2sqlca.sqlcode:err:%dreturn}{printf(2RELEASE}returnret}pro*c语言变量,oraclePro*C程序中，既可在SQL语句中，也可在C语句中的变量称为SQL变量 N intsalary,cin>>salary;EXECSQLupdateempsetsal=:salarywhereempno=floatv_salary;charv_job;EXECSQLselectsal,jobINTO:v_salary,:v_jobfromempwhereempno=7788;CCODE=CPPpointerchartable_name[30];EXECSQLDROPTABLE预编译选项CHAR_MAP=CHARZ默认设置0’结尾，定长，空格补齐。CHAR_MAP=CHARF|VARCHAR2：定长，空格补齐。CHAR_MAP=STRING：‘\0’1。变长,不是‘\0’2unsignedshortlen;unsignedchararr[主要用在输出,即当宿主变量用于接收数据库的返回数据时.通过在宿主变量后用指示变量,NULL.0Oracle将数据库表列值截断后赋给指示器变量相关联的输出宿主变量，指示器变量值EXECSQLBEGINDECLARE EXECSQLENDDECLARESECTION;…EXECSQLSELECTemp_descINTO:desc:ind_descFROMempWHEREIf(ind_desc<cout<<desc<<ORACEsqlC语言使用CORACLE的数据类型，在数据传递时有一个数据类型转换的过ORACLEOracle数据就是Oracle数据 CHAR2000字节NUMBER(p,s)：数字类型，p精度，s标度DATE：日期时间数据，7字节BFILE：OS文件数据1234567年月日时分秒ORACLEOracle外部数据类型是宿主程序所的数据类型，在运行Pro*C/C++程序的时候，OracleOracle外部数据类型来定义宿主变量。STRINGNULL终止的变长字符串LONGVARRAW：变长二进制CHARZ：NULL终止定长字符串CHARFCHAR的字符数据类型SQL数据类型char[321。EXECSQLVARhost_variableIStype_name[(length)]; EXECSQLVARemp_nameISEXECSQLTYPEuser_typeIStype_name[(length)];typedefstruct{ }EXECSQLTYPEgraphicsISVARRAW(4000);typedefchardnameType[20];typedefcharlocType[20];EXECSQLBEGINDECLAREEXECSQLTYPEdnameTypeisstring(20);EXECSQLTYPElocTypeisstring(20); *usrname="myscott"; *passwd="22"; *serverid= EXECSQLENDDECLAREEXECSQLBEGINDECLARE *usrname="scott"; *passwd="tiger"; *serverid="orcl"; dname2[100][20varcharchar EXECSQLENDDECLAREPro*CPro*CEXECSQLBEGINDECLARESECTIONintA[100];charB[100];EXECSQLENDDECLAREEXECSQLBEGINDECLAREintemp_number[100];EXECSQLENDDECLAREfor(inti=0;i<100;i{}EXECSQLBEGINDECLAREintemp_number[100];shortind_emp_number[100];EXECSQLENDDECLARESECTION;…EXECSQLSELECTnumber,nameINTO//EXECSQLEXECSQLWHENEVER<condition><aciton>NOT 编译选 MODE=ORACLEsqlca.sqlcode=编译选 MODE=ANSIsqlca.sqlcode=EXECSQLWHENEVERSQLERRORDOsqlerrEXECSQLWHENEVERNOTFOUNDEXECSQLWHENEVERSQLERRORGOTOEXECSQLWHENEVERSQLERRORCONTINUE;EXECSQLROLLBACKRELEASE;EXECSQLWHENEVERSQLERRORGOTOEXECSQLWHENEVERSQLERRORCONTINUE;EXECSQLROLLBACKRELEASE;printf("\nInvalidusername/password\n");第一个SQLCA是ORACLEstruct{//被初始化唯sqlca,SQLchar//SQLlong//最近执行的SQLlongsqlcode;{sqlerrmcunsignedshort//与sqlcode//只有当sqlcode<0才能,否则是上次错误信息charsqlerrmc[70];charsqlerrp[8];//sqlerrd[2]SQL语句处理的行数,如果SQL执行失败,longsqlerrd[6];charcharstructsqlca0，1，3，5 －当前SQL －保存相对位移，在什么地方出现语法错sqlcode用于保存最近一次运行SQL 4－UPDATE和DELETE{SQLCAvoid…EXECSQLINCLUDE…EXECSQLWHENEVERSQLERRORDo…} err_report(structsqlcasqlca){if(sqlca.sqlcode<0)printf("\n%d-%s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);}当需要更进一步的信息时，ORACA将帮助我们达成愿望，所以ORACA也可以看作时SQLCA {/*text*/char /* *//*ub4*/int /* *//*ub4*/int /*<>0if"checkcurcache/*ub4*/int /*<>0if"doDEBUGmode /*ub4*/int /*<>0if"doHeapconsistencycheck"/*ub4*/intorastxtf; /*SQLstmttextflag struct{/*ub2*/unsignedshort/*text*/char}orastxt; /*textoflastSQLstmt struct{/*ub2 unsignedshort/*text } /*nameoffilecontainingSQL /*ub4*/ /*linenr-within-fileofSQL /*ub4*/ /*highestmaxopenOraCursrequested/*ub4*/ /*maxopenOraCursors /*ub4*/ /*ub4*/ /*nrofOraCursorre- /*ub4*/ /*nrof /*ub4*/ /*nrof oracaid[8]标识一个ORACAoracabc用于保存ORACA0－光标缓冲一致性检orahchforadbgf＝1oracchf0－堆缓冲一致性检orastxtf0－不保存SQL1－仅对SQLERROR保存SQL2－仅对SQLERROR和SQLWARNING保存SQL3－总是保存SQLorastxt.orastxtl当前SQLorastxtorasfnmc[70]SQL70orasfnm.orasfnmc70oraslnr标识当前SQLorahocoracocoranpr记录SQLoranex记录SQL要使用ORACA，那么就要显式的加载ORACA结构到Pro*CEXECSQLINCLUDEEXECORACLEoraclesqlplus通过.bash_profileNLS_LANGsqlplusoracleserverselectuserenv(‘language’)fromSQL>selectuserenv(‘language’)fromdual;umaskNLS_LANG=AMERICAN_AMERICA.ZHS16GBK;exportNLS_LANGUEviUEUE的文件/转换/ASCIIUTF-8(Unicode编辑Procrhel6.4oracleproc工具预编译时找不到stddef.h[oracle@localhostadmin]$catpcscfg.cfg define= gcc版本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;EXECSQLcommitworkrelease;EXECSQLrollbackwork数据定义语句（DDLcreate,alterdropEXECSQLCREATTABLEt1(colaint);EXECSQLDROPTABLEt1;对象名,列名不能用宿主变量Alter:altertableempadd(faxnonumber);yze:yzetableEXECSQLSelect…….;EXECSQLInsert…….;EXECSQLupdateEXECSQLDelete用多个变量或结构（struct）（array）（cursor）charename[35];intempno=20;EXECSQLselectemp_nameINTO:enamefromwhereemp_no=:empno;cout<<“thenameis“<<ename;charv_name[31],charv_job[21];floatv_salary;intEXECSQLselectemp_name,job,salaryINTO:v_name,:v_job,:v_salaryFROMWHERE用结构（struct）intcharname[10];intsalary;输入：EXECSQLINSERTINTOemp(empno,ename,sal)VALUES(:emp_record);EXECSQLSELECTempno,ename,salINTOemp_recordWHERErownum=1;用结构（struct）操作单行多列C联合intcharname[10];intsalary;emp_record=(structemp_record*)malloc(sizeof(structemp_record));EXECSQLSELECTempno,ename,salINTO:emp_recordWHERE（array）INSERTintnum[100];floatsalary[100];char/**ARRAYEXECSQLINSERTINTOemp(empno,ename,sal)VALUES(:num,:name,UPDATEDELETECharname[100];float**ARRAYEXECSQLUPDATEEMPSETsal=:salaryWHEREename=:name;Charname[100];**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!={ret=printf("sqlca.sqlcode:err:%d\n",sqlca.sqlcode);return;}if(dname_ind==-{printf("dname为空}if(loc_ind==-{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= EXECSQLENDDECLAREint{intret=0;inti=0;EXECSQLWHENEVERSQLERRORDO//EXECSQLWHENEVERNOTFOUNDDO//1定义游标declarecursorEXECSQLDECLAREdept_cursorCURSORFORselectdeptno,dname,locfromdept;//2打开游标opencursorEXECSQLOPENdept_cursor;//3fetchdatawhile1){EXECSQLWHENEVERNOTFOUNDDOEXECSQLFETCHdept_cursorINTOdeptnodname:dname_indloc:loc_ind;printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\t%s\t%s",deptno,dname,locif(dname_ind==-{printf("dnameisnull}if(loc_ind==-{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_tvoidvoidsqlerr02();void{charunsignedintret=0;//出错时,可以把错误SQLEXECSQLWHENEVERSQLERRORret=sqlgls(stm,&stmlen,,,,//printf("出错原因:%.70s\n"sqlca.sqlerrm.sqlerrmlsqlca.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= dname;string EXECSQLENDDECLAREint{intret=0;inti=0;EXECSQLWHENEVERSQLERRORDO//EXECSQLWHENEVERNOTFOUNDDO//1定义游标declarecursorEXECSQLDECLAREdept_cursorSCROLLCURSORselectdeptno,dname,locfrom//2打开游标opencursorEXECSQLOPENdept_cursor;//3fetch//while{//EXECSQLWHENEVERNOTFOUNDDOEXECSQLFETCHLASTdept_cursorINTO:deptno,:dname:dname_ind,//printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\ts\tsn"deptnodname,locEXECSQLFETCHFIRSTdept_cursorINTO:deptno,:dname:dname_ind,//printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\ts\tsn"deptnodname,loc//3EXECSQLFETCHABSOLUTE3dept_cursorINTO:deptno,:dname:dname_ind,//printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\ts\tsn"deptnodname,loc//36EXECSQLFETCHRELATIVE3dept_cursorINTO:deptno,:dname:dname_ind,//printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\ts\tsn"deptnodname,locEXECSQLFETCHNEXTdept_cursorINTO:deptno,:dname:dname_ind,//printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\ts\tsn"deptnodname,locEXECSQLFETCHPRIORdept_cursorINTO:deptno,:dname:dname_ind,//printf("条目数:%d\t",sqlca.sqlerrd[2]);printf("%d\ts\tsn"deptnodname,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;}{}}SQL动态动态SQLpro*c/c++SQL比较静态SQL和动态静态 在编写应用程序时，使用EXECSQL关键字直接嵌入的SQL语句；在proc编译应用程生成c动态 在运行应用程序时，由用户动态输入的SQL语句什么时候使用动态SQL■TextoftheSQLstatement(commands,clauses,andso■Thenumberofhost■Thedatatypesofhost■Referencestodatabaseobjectssuchascolumns,indexes,sequences,tables,usernames,andviews动态SQL1—语法：EXECSQLEXECUTEIMMEDIATE：host_stringhost_string字符串//非select语言,//在sqlplus上运行令，都可以拿过来来执int{ ret= i= }memset(mySql,0,sizeof(mySql));pSql=NULL;EXECSQLWHENEVERSQLERRORDOpSql=mySql;//循环处理sql{printf("\npleaseentersql(notselect):");//scanf("%s",mySql);--空格截断printf("mysql:%s\n",mySql); EXECSQLEXECUTEIMMEDIATEEXECSQLscanf("%c&choosechar);if(choosechar=='n'||{}}EXECSQLC