数据库shell procc编程实践v1.0

1、Pro*C/C+简1 Pro*C/C+简1 在SQL标准中定义了多种语言各个厂商SQL的具体实现不 目的：使c/c+这种高效率语言成为SQL宿主语Pro程（1）用SQL*Plus，它有SQL命令以交互的应用程（2）用第四代语言应用开发工具开发的应用程数据库，这些工具（3）利用在第三代语言内嵌入的SQL语言或ORACLE库函数调用 2 Pro*C 程序实例 #include #includestring.h #includestdlib.h #include2 Pro*C 程序实例 #include #includestring.h #includestdlib.h #includesqlca.

2、hEXECSQLBEGINDECLAREcharusername32; charpassword32; char dname25;EXECSQLENDDECLARESECTION; EXEC SQL INCLUDE sqlca;文解The term embedded SQLrefers to SQLs ements placedwithin an application program. Because it houses the SQLs ements, the application program is called a host program, and the language in

3、 which it is written is called the host language. For ex le, Pro*C/C+ provides the ability to embed certain SQL s ements in a C or C+ host 3 3 EXECSQLWHENEVERSQLERRORDO EXEC SQL CONNECT:username IDENTIFIED BY:password; EXEC SQL select dnameo:dnamefromdeptwhereid=10; f(“dname:%s n”, dname);voidEXECSQ

4、LWHENEVERSQLERRORoracleerrorEXEC SQL ROLLBACK WORK RELEASE;Pro*C Include (c/candSQL 操作语句EXECexceptionEXECSQLCOMMIT/ROLLBACKWORKPROCpile 功能：完成Pro*CPROCpile 功能：完成Pro*CCPROCINAME=filenamend filename (name of the input file) ndfilenamenameoftheoutputfile) -INCLUDE =路径名 或 INCLUDE =(1,2) PARSE=FULL|PARTIA

5、|NONEdefaultFULLforCOthersforC+) CODE=ANSI_C | CPP (default ansi_c)1、proc2、为什么可以直接找到，oracle用户下,bash_profilePro*C/C+:Release11.2.0.1.0-ProductiononThuSep1107:59:37Copyright(c)1982,Copyright(c)1982,2009,Oracleand/oritsOptionAllowautomaticconnectiontoops$account Mapof character arrays and stringsClose

6、all cursors on COMMIT CMAXValueforconnectionpoolCMINValueforconnectionCINCRValueforconnection ParseusingCommonSQLFrontEnd The type of code to be generatedcomp_charset multi_byteThecharactersettypetheCcompilersupports Overridesystemconfigurationfilewi nother Support connection poolingOverridethedefau

7、ltC+filenamesuffix SupportDB2arrayinsert/selectsyntax v6/v7/v8 compatibility modedef_sqlcode noGenerate#defineSQLCODEsqlca.sqlcodemacro x86_64 Definea prepro sor symbolSet pin duration for objectshecache Specify Oracle or ANSI Dynamic SQL SemWhethererrormessagesaresenttotheterminal *none*Name of the

8、 list file forype file errorsSupport publish-subscribe event notifications FIPS flagging of ANSIpliant usage Specify file extenpiledHeaders Control holding of cursorshe cursor cache Implicitsavepoprior to buffered insertThe name of the input file DirectorypathsforincludedfilesThenameoftheinputfilefo

9、rtypeinformation Add #line directives to terated code Override default list file nameThe amount of data generatedhe list file umlengthofageneratedstringumnumberofcachedopenmax_row_insert 0umnumbermax_row_insert 0umnumberofrowstobufferoninsert Code conformance to Oracle or ANSI rules Support for nati

10、ve float/doubleSpecifyNationalLanguagecharactervariables ControlhowNLScharactersem s aredone Support object typesThename of theoutputfile ControltheuseoftheCategoryinwhichOutlinesarecreated yes/no/OutlinenameThepagelengthofthelistControlwhichnon-SQLcodeisNumberofrowspre-fetchedatcursorOPEN Controlre

11、leaseofcursorsfromcursorIfyes,createheControl flagging of select errors Amountofcompile-timeSQLchecking Size of sement cache Indicates a multi-threaded application UseOracleorANSItypecodesforDynamicSQL Allow a NULL fetch without indicator variableAusername/passworddbnameconnectstring The character s

12、et form used by UTF16 variables Allow the use of implicit varchar structuresWhich verofanobjectistobereturned PCC-F-02135, CMD-LINE:User asked for help Pro*C/C+开发基知识点 Pro*C/C+开发基知识点/1 演示连接数据库的几种方/2 演示通过程序修改用/3 演示连接多个数据连/通接EXECSQL DECLARElink1EXECSQLCONNECT :userIDENTIFIED BY :oldpswd |:usr_pswAT dbn

13、ame| :host_variable AT dbname| :host_variable USING :connect_string ALTER AUTHORIZATION:newpswd | IN SYSDBA| SYSOPER MODE 方法EXECSQLCONNECT方法EXECSQLCONNECT:username IDENTIFIEDBY:password 方法/* connect to the nondefauatabaseusingdb_name*/ EXEC SQL CONNECT :username IDENTIFIED BY :passwordAT:db_name USI

14、NG/*declareneededhostvariables*/ char username10 = scott; char password10 = tiger; char db_string20 = NYNON;/*givethedatabaseconnectionauniquename*/ EXEC SQL DECLARE DB_NAME DATABASE;/* connect totheatabase EXECSQLCONNECT:usernameIDENTIFIEDBY:password AT DB_NAME USING :db_string;EXECEXECSQLBEGINDECL

15、ARESECTION;*usrname= *passwd =*link1link1; /通过宿主变量指定连接名*serverid= *usrname2= *passwd2= *link2 =*serverid2=orcl; EXEC SQL END DECLARE SECTION;ret =EXEC SQL CONNECT:usrname IDENTIFIED BY:passwdink1 USING:serverid ; if (sqlca.sqlcode != 0)ret =f(第一个用户连接数据库败sqlca.sqlcodeerr:%dreturn f(第一个用户连接数据库功connect

16、EXEC SQL CONNECT:usrname2 IDENTIFIED BY:passwd2ink2 EXEC SQL CONNECT:usrname2 IDENTIFIED BY:passwd2ink2 USING:serverid2 ; if (sqlca.sqlcode != 0)ret =f(第二个用户连接数据库败sqlca.sqlcodeerr:%dreturn f(第二个用户连接数据库功connectEXECink1COMMIT if (sqlca.sqlcode!=ret =f(1据库败sqlca.sqlcode: err:%dreturn f(1据库功RELEASEEXECi

17、nk2COMMITif (sqlca.sqlcode!=ret =f(第二个用户断开数据库败sqlca.sqlcodeerr:%dreturn f(第二个用户断开数据库功RELEASEreturn ret /演示使/演示使用连接EXEC SQL DECLARE link1EXECSQLBEGINDECLARESECTION;*usrname= *passwd =*link1 =*serverid= *usrname2= *passwd2= *link2 =*serverid2=orcl; EXEC SQL END DECLARE SECTION;ret =EXECSQLDECLARElink1

18、DATABASE; EXECSQLDECLARElink2 EXEC SQL CONNECT:usrname IDENTIFIED BY:passwdink1 USING:serverid ; if (sqlca.sqlcode != 0)ret =f(第一个用户连接数据库败sqlca.sqlcodeerr:%dreturn f(第一个用户连接数据库功connectEXEC SQL CONNECT:usrname2 IDENTIFIED BY:passwd2ink2 USING:serverid2 ; if (sqlca.sqlcode != 0)ret =ret =f(第二个用户连接数据库败

19、sqlca.sqlcodeerr:%dreturn f(第二个用户连接数据库功connectEXECink1COMMITif (sqlca.sqlcode!=ret =f(1据库败sqlca.sqlcode: err:%dreturn f(1据库功RELEASEEXECink2COMMITif (sqlca.sqlcode!=ret =f(2据库败sqlca.sqlcode: err:%dreturn f(2据库功RELEASEreturn ret pro*c语言变量, oraclepro*c语言变量, oraclePro*C程序中，既可在SQL语句，也可在C语句的变量称为SQL变量N个定长字

20、符数组 将应用程序的数据传递到数据库中cinsalary;EXECSQLupdateempsetsal=:salarywhereempno= 将数据库的数据传递到应用程序中floatv_salary; char v_job;EXEC SQL select sal,jobO:v_salary,:v_jobfromempwhereempno=7788; CCODE=CPP可使用er 作为宿主变量，使用前分配空间 chartable_name30; EXECSQLDROPTABLE预编译选项 CHAR_MAP=CHARZ默认设置0结尾，定长，空格补齐。 CHAR_MAP=CHARF | VARCHA

21、R2：定长，空格补齐。CHAR_MAP=STRING： 01。变长, 不是0ORACEsqlC语言使用ORACEsqlC语言使用C ORACLE 的数据类型，在数据传递时有一个数据类型转换的过ORACLE数据类2unsignedshortlen; unsignedchararr 主要用在输出, 即当宿主变量用于接收数据库的返回数据时.通过在宿主变量后用指示变量, NULL. 0Oracle 将数据库表列值截断后赋给指示器变量相关联的输出宿主变量，指示器变量值EXECSQLBEGINDECLAREEXECSQLENDDECLAREEXECSQLSELECTO:desc:ind_descFROMe

22、mpWHEREIf(ind_desccoutdescOracle数据就是Oracle数据 CHAR：定长字符串，最大2000 字节 ,s) ：数字类型，pOracle数据就是Oracle数据 CHAR：定长字符串，最大2000 字节 ,s) ：数字类型，p精度，s标度 DATE：日期时间数据，7 字节 BFILE：OS 文件数据ORACLE1234567年月日时分秒Oracle 外部数据类型是宿主程序所 的数据类型，在运行 Pro*C/C+Oracle 外部数据类型是宿主程序所 的数据类型，在运行 Pro*C/C+程序的时候，Oracle会根据需要将宿主变量的数据类型 成 OraclePro*

23、C/C+程序的时Oracle 外部数据类型来定义宿主变量。STRINGNULL终止的变长字符串 LONG VARRAW：变长二进制 CHARZ：NULL 终止定长字符串 CHARFCHAR的字符数据类型 SQL数据类型char32SQL数据类型char32 1。EXECSQLVARhost_variableIStype_name(length); EXECSQLVARemp_nameISEXECSQLTYPEuser_typeIStype_name(length); typedef struct EXECSQLTYPEgraphicsISVARRAW(4000); typedefchardna

24、meType20; typedef char locType20;Pro*CPro*CPro*CPro*C EXECSQLBEGINDECLARESECTION char B100; EXECSQLENDDECLARE EXECSQLBEGINDECLAREEXECSQLTYPEdnameTypeisstring(20); EXEC SQL TYPE locType is string(20); *usrname = myscott;*passwd = 22; *serverid=EXECSQLENDDECLAREEXECSQLBEGINDECLARE*usrname=scott; *pass

25、wd = tiger; *serverid = orcl;dname210020varchar char EXECSQLENDDECLAREEXECSQLBEGINDECLAREEXECSQLBEGINDECLAREEXECSQLENDDECLAREEXECSQLSELECTO:emp_number,:emp_nameFROMi=0;i100; iEXECSQLSELECTO:emp_numberi,EXECSQLBEGINDECLAREshortind_emp_number100; EXEC SQL END DECLARE SECTION;EXECSQLSELECTO/EXECSQL /EX

26、ECSQL EXECSQLWHENEVER 第一个NOT编译选MODE=ORACLE第一个NOT编译选MODE=ORACLEsqlca.sqlcode=编译选MODE=ANSI sqlca.sqlcode=GOTOlabel_name EXECSQLWHENEVERSQLERROR DOsqlerrEXEC SQL WHENEVER NOT FOUND EXEC SQL WHENEVER OTO EXECSQLWHENEVERSQLERRORCONTINUE; EXEC SQL ROLLBACK RELEASE;EXEC SQL WHENEVER OTO EXECSQLWHENEVERSQLE

27、RRORCONTINUE; EXEC SQL ROLLBACK RELEASE;f(nInvalidusername/passwordn); SQLCA 是ORACLE 提供的两个通信区之一SQLCA 是ORACLE 提供的两个通信区之一 struct /被初始化唯sqlca,标识SQLchar /SQL 通讯区的长long /最近执行的SQL longsqlcode; sqlerrmc数组的实际文本长unsignedshort/与sqlcode一致的对应的错误信息文/只有当sqlcode0 才能char sqlerrmc70;char sqlerrp8;/sqlerrd2SQL语句处理的行数

28、,如果SQL执行失败,longsqlerrd6;被没有where子句的delete,update处理的时候被设char char structsqlca0，1 ，3，50，1 ，3，5当前SQL指令处理的行sqlcode用于保存最近一次运行SQL正4UPDATE和DELETE 例SQLCA应用示voidEXECSQLINCLUDEEXECSQLWHENEVERSQLERRORDoerr_report(structsqlcasqlca) if (sqlca.sqlcode 0)pr f(n%d-%snn,sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc)

29、; 当需要更进一步的信息时，ORACA将帮达成愿望，所以ORACA也可以看作时SQLCA的补充和辅/*text*/char/*ub4 /*ub4/* 0if checkcurcache/*ub4/*0if do DEBUGmode/*ub4/*0if doHeapconsistencycheck/* ub4 */* SQL stmt text flag struct /*ub2 */unsignedshort/*text*/ char orastxt;/* text of last SQL stmt structorastxt;/* text of last SQL stmt struct/*

30、ub2 unsignedshort/*text/*nameoffilecontainingSQL/*ub4/*linenr-within-fileofSQL /*ub4/*highestmaxopenOraCursrequested /*ub4/*maxopenOraCursors/*ub4 /*ub4/*nrofOraCursorre-/*ub4/*nrof /*ub4/*nr of oracaid8 标识一个ORACAoracabc用于保存ORACA orahchf如果oradbgf1，那oracchforastxtf可以是以下各值0不保存SQL 文1仅对SQLERROR保存SQL文2仅对

31、SQLERROR和SQLWARNING保存SQL文3总是保存SQL文orastxt.orastxtl当前SQLorastxtorasfnmc70SQL70字 orasfnm.orasfnmc70字oraslnr标识当前SQLorahoc运行时最多能打开的光标 oracoc当前打开的光标SQL语句分析次SQL语句执行的次要使用ORACA，那么就要显式的加载ORACA结构到Pro*C程序中。可通过语EXECSQLINCLUDEEXECORACLEoracleUE vi注意在oracleUE vi注意在sqlplus通过.bash_profileNLS_LANG sqlplusoracleserve

32、r SQLselectuserenv(language)fromumaskNLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANGProc rhel6.4Proc rhel6.4oracleproc工具预编译时找不到stddef.h oraclelocalhost admin$ cat pcscfg.cfg UEUE的文件/转换/ASCIIUTF-8(Unicode 编辑 define= gccgccv,最接近的oraclelocalhost admin$Pro*C/C+:Release11.2.0.1.0-ProductiononWedSep10

33、20:08:132014 Copyright(c)1982,2009,Oracleand/oritsine34,column11infile/usr/include/stdio.h # include PCC-S-02015,unabletoopenincludeine15,column10infile/usr/include/_G_config.h #include PCC-S-02015,unabletoopenincludeine52,column11infile/usr/include/wchar.h # include PCC-S-02015,unabletoopenincludei

34、ne53,column11infile/usr/include/libio.h # include Pro*C/C+操作数SQL用多个变量或结构（struct ）Pro*C/C+操作数SQL用多个变量或结构（struct ）（array）（cursor）charename35; EXECSQLselectO:enamefromCommit, rollback and savepo EXEC SQL commit;EXECSQLcommitworkrelease; EXECSQLrollbackwork数据定义语句（DDL):create,alter,drop EXEC SQL CREAT TA

35、BLE t1(colaEXEC SQL DROP TABLE t1;对象名, 列名不能用宿主变量Alter:altertableempadd(faxnonumber); yze tableEXEC SQL Select .; EXEC SQL Insert .; EXECSQLupdate.; EXEC SQLDelete .;where emp_no=:empno; cout“thenameis“where emp_no=:empno; cout“thenameis“ename;charv_name31,charv_job21; float v_salary;EXECSQLselectemp

36、_name,job,salary O :v_name,:v_job,:v_salaryFROMWHERE 用结构（struct ） charname10; 输入：EXEC SQL INSERTOemp(empno,ename,sal) VALUES (:emp_record);输出： EXEC SQL SELECT empno,ename,salWHERE rownum=1;用结构（struct ）操作单行多列可用结构 C 联合charname10; emp_record=(structemp_record*)malloc(sizeof(structemp_record); EXEC SQL

37、SELECT empno,ename,salO :emp_recordWHEREret=ret=i=/EXECSQLWHENEVERSQLERRORDO/EXECSQLWHENEVERNOTFOUNDDO（array）INSERT num100;floatsalary100;char/* ARRAYEXECSQLOemp(empno,ename,sal)VALUES(:num,:name,UPDATEDELETE Charname100;float* ARRAYEXECSQLUPDATEEMPSETsal=:salaryWHEREename=:name; Char name100;* ARRA

38、YEXECSQLDELETEFROMempWHERESELECT CharEXEC SQL SELECT enameO:nameFROMemp WHERE dept_num=2;CHAR ARCHAR INDEXSELECTWHEREpr f(npleaseenterpr f(npleaseenterdeptno:); scanf(%d,&deptno);EXEC SQL select deptno, dname, loco :deptno, :dname:dname_ind, :loc:loc_ind from dept where if(sqlca.sqlcode!=ret=pr f(sq

39、lca.sqlcode:err:%dn,sqlca.sqlcode); return ;if(dname_ind=-pr f(dname 为空if(loc_ind= -pr f(loc为空pr f(deptno:%d,dname:%s,loc:%sn,deptno,dname.arr,loc.arr); loc_ind = -1;EXECSQLupdatedeptsetloc=:loc:loc_indwheredeptno=:deptno; if (sqlca.sqlcode != 0)ret=pr f(sqlca.sqlcode:err:%dn,sqlca.sqlcode); return

40、;EXECSQLCOMMIT WORKpr f(return/向表dept2 多条数returnretEXECSQLEXECSQLDECLAREemp_cursorCURSORselectempno,ename,salfromempwhere打开游EXECSQL OPENemp_cursor(Open cursor: put the select resultsoamemoryplace, and the cursor poo s to therowdata.)提取数据EXECSQLFETCHO /* 在此处理数据处理(Afterfetch,cursorpo ermovesdownonelin

41、e. Useloopfor multiplerows)EXECSQLCLOSEOpencursor:puttheoamemory place,andthecursoro storowtypedefchardnameType20; typedef char locType20;EXECSQLBEGIN DECLAREEXECSQLTYPEdnameTypeisstring(20); EXEC SQL TYPE locType is string(20);char *usrname=myscott; char *passwd = 22;char *serverid= EXECSQLENDDECLA

42、REret=i=EXECSQLWHENEVER SQLERRORDO/EXECSQLWHENEVERNOTFOUNDDO/1 定义游标 declare cursor #include #include #include #include sqlca.h#include #include #include #include sqlca.hEXECSQLDECLAREdept_cursorCURSORFOR select deptno, dname, loc from dept;/2 打开游标 open cursor EXECSQLOPENdept_cursor;/3 获取数据fetchEXECS

43、QLWHENEVERNOTFOUNDDOwhileEXECSQLFETCHO:deptno,:dname:dname_ind,pr f(条目数:%dt, pr f(%dt%st%s,deptno,dname,loc); if (dname_ind = -1)pr f(dnameisnull if(loc_ind= -pr f(locisnullpr /4 关闭游标 close data EXECSQLCLOSEdept_cursor;EXECSQLCOMMIT WORKpr f(returnreturnretexternsqlgls(charexternsqlgls(char*,size_t*

44、,size_t*externsqlglmt(void*,char*,size_t*,size_t*); voidconnet();voidsqlerr02(); voidchar ret = 0;/出错时,可以把错误SQL语言给打印出EXECSQLWHENEVERSQLERRORret=sqlgls(stm,&stmlen,pr f(出错的SQL:%.*snstmlen,pr f(出错原因:%.*snsqlca.sqlerrm.sqlerrml/pr f(出错原因:%.70snsqlca.sqlerrm.sqlerrmlsqlca.sqlerrm.sqlerrmc); EXEC SQL ROL

45、LBACK WORK RELEASE;voidret=pr f(没有发现数据n); if (sqlca.sqlcode != 0)ret=pr f(sqlca.sqlcode:err:%dn,sqlca.sqlcode); return ;typedefchardnameType20; typedef char locType20;EXECSQLBEGIN DECLAREEXECSQLTYPEdnameTypeisstring(20); EXEC SQL TYPE locType is string(20);char *usrname=myscott; char *passwd = 22;ch

46、ar *serverid= dname;dname;string EXECSQLENDDECLAREret=i=EXECSQLWHENEVER SQLERRORDO/EXECSQLWHENEVERNOTFOUNDDO/1 定义游标 declare cursor EXECSQLDECLAREdept_cursorSCROLL CURSORselectdeptno,dname,locfrom/2 打开游标 open cursor EXECSQLOPENdept_cursor;/3 获取数据fetch/while/EXECSQLWHENEVERNOTFOUNDDOEXECSQLFETCHLASTO:

47、deptno,:dname:dname_ind,/pr f(条目数:%dtpr f(%dt%st%sn,deptno,dname,locEXECSQL O:deptno,:dname:dname_ind,/pr f(条目数:%dtpr f(%dt%st%sn,deptno,dname,loc/ 1 2 3 4 5 6 7/查询第3 条数EXECSQLFETCHABSOLUTE3O:deptno,:dname:dname_ind,/pr f(条目数:%dtpr f(%dt%st%sn,deptno,dname,loc/查询相对/查询相对第3 条数据就是第6 EXECSQLFETCHRELATIV

48、E3O:deptno,:dname:dname_ind,/pr f(条目数:%dtpr f(%dt%st%sn,deptno,dname,locEXECSQLFETCHNEXTO:deptno,:dname:dname_ind,/pr f(条目数:%dtpr f(%dt%st%sn,deptno,dname,locEXECSQL FETCHPRIOR O:deptno,:dname:dname_ind,/pr f(条目数:%dtpr f(%dt%st%sn,deptno,dname,loc/4 关闭游标 close data EXECSQLCLOSEdept_cursor;EXECSQLCOM

49、MIT WORKpr f(returnreturnretvoidret=EXECSQLCONNECT:usrnameIDENTIFIEDBY:passwdUSING:serverid; if (sqlca.sqlcode != 0)ret=pr f(sqlca.sqlcode:err:%dn,sqlca.sqlcode); return ;pr f(connectPro*C/C+动态 SQL Pro*C/C+动态 SQL 动态SQL方法1概语法：EXECSQLEXECUTEIMMEDIATE ：host_string host_string 字符串/非select 语言,/在sqlplus 上

50、运ret=i =动态动态SQL是指在运行pro*c/c+应用程序时，动态输入的SQL比较静态SQL 和动态静态在编写应用程序时，使用EXEC SQL 关键字直接嵌入的SQL 语句；在proc编译应用程生成c 语言的时，都已经确动态在运行应用程序时，由用户动态输入的SQL语句什么时候使用动态SQL语 TextoftheSQLs ementds,clauses, andsoThenumberofhostThe ypesofhostto database objectch as columns, indexes, sequen,tables, usernames, and views, 0, sizeof( pSql = NULL;EXEC, 0, sizeof( pSql = NULL;EXECSQLWHENEVER SQLERRORDO pSql=/循环处理sql 语pr f(npleaseentersql(notselect):); 空格截pr pr f(任意键继EXECSQLEXECUTEIMMEDIATEEXECSQLpr f(继续执行吗?n); scanf(%c&choosechar); if(choosechar=n|EXECSQLCOMMIT WORKpr f(returnreturnret仅适用于非select 语Delete from emp where e