第八章数据库编程-补充材料-ORACLE proc.ppt

1 说明SQL通讯区 在程序头加上EXECSQLINCLUDESQLCA 其中包括许多变量 如返回码等 与SQL连接语句 CONNECT EXECSQLCONNECT 用户名变量IDENTIFIEDBY 口令变量或EXECSQLCONNECT 用户名变量 口令变量 2 伪类型VARCHAR 只有DECLARE部分引用 可认为是C的扩充类型 描述如下EXECSQLBEGINDECLARESECTION VARCHARjob 20 EXECSQLENDDECLARESECTION 等价于 Struct unsigned 2bytes shortintlen unsignedchararr 40 job 3 在ORACLE上注册和注销 说明 这是以SCOTT TIGER为用户名和口令登陆进入数据库的PRO C程序例 includeEXECSQLBEGINDECLARESECTION VARCHARuid 20 VARCHARpwd 20 EXECSQLENDDECLARESECTION EXECSQLINCLUDESQLCA 4 在ORACLE上注册和注销 Main strcpy uid arr SCOTT uid len strlen uid arr strcpy pwd arr TIGER pwd len strlen pwd arr EXECSQLCONNECT uidIDENTIFIEDBY pwdPrintf connectedtooracleuser s n uid arr EXECSQLCOMMITWORKRELEASE Exit 0 5 创建一个表 includeEXECSQLBEGINDECLARESECTION VARCHARuid 20 VARCHARpwd 20 EXECSQLENDDECLARESECTION EXECSQLINCLUDESQLCA Main strcpy uid arr SCOTT uid len strlen uid arr strcpy pwd arr TIGER pwd len strlen pwd arr 6 EXECSQLCONNECT uidIDENTIFIEDBY pwdPrintf connectedtooracleasuser s n uid arr EXECSQLCREATETABLEemp empnonumber enamechar 15 jobchar 10 mgrnumber hiredatedate salnumber deptnonumber Printf tableempcreated n EXECSQLCOMMITWORKRELEASE Exit 0 7 1 EXECUTEIMMEDIATE预编译任何SQL语句 除SELECT 并执行之 SQL语句可以是文字量 但它可以不包括任何主变量 既不包括输入 也不包括输出主变量 EXECUTEIMMEDIATE只能带一个参数 执行一次2 PREPARE和EXECUTE语句预编译任何SQL语句 除SELECT 并执行之 语句可以包括输入或输出主变量 它的SQL语句仅被语法分析一次 可带多个参数 对任何SQL语句可多次使用 但必须保证变量的数目和相应的数据类型必须相同 PREPARE完成 预编译 分析 该SQL语句 并为其提供一个语句名 EXECUTE执行刚预编译的语句 使用USING提供的值 动态SQL语句四种方式 8 3 PREPARE和FETCH语句允许使用SELECT语句 语句可以包括输入或输出主变量 这种方式的顺序是PREPARE DECLEAR OPEN和FETCH 变量的数目和相应的数据类型必须相同 4 使用赋值和定义描述符允许使用任何SQL语句 包括查询结果为单记录或多记录的SELECT语句 9 1 EXECUTEIMMEDIATE立即执行方式 A SQL语句不能是SELECT语句 只能是U D I语句B SQL语句不能包含主变量例 EXECSQLBEGINDECLARESECTION VARCHARdstring 80 EXECSQLENDDECLARESECTION scanf s dstring EXECSQLEXECUTEIMMEDIATE dstring 10 EXECUTEIMMEDIATE实例 这是一个提交修改语句的pro c实例 includeEXECSQLBEGINDECLARESECTION VARCHARuid 20 VARCHARpwd 20 charselect 132 EXECSQLENDDECLARESECTION EXECSQLINCLUDESQLCA Main charwhere 80 intscode 11 strcpy uid arr SCOTT uid len strlen uid arr strcpy pwd arr TIGER pwd len strlen pwd arr EXECSQLWHENEVERSQLERRORSTOP EXECSQLCONNECT uidIDENTIFIEDBY pwdPrintf connectedtooracleasuser s n uid arr strcpy select UPDATEEMPSETCOMM 100WHERE Printf pleaseenterwhereclauseforthefollowing n Printf s select Scode scanf s where If scode EOF scode 0 12 Printf invalidentry n exit 1 Strcat select where EXECSQLEXECUTEIMMEDIATE select Printf drecordsupdated n sqlca sqlerrd 2 EXECSQLWHENEVERSQLERRORCONTINUE EXECSQLCOMMITWORKRELEASE Exit 0 13 2 PREPARE和EXECUTE语句准备执行方式 A SQL语句不能是SELECT语句 只能是U D I语句B SQL语句可包含主变量 可多次使用 例 要执行deletefromempwhereempno pempnoscanf s dstring EXECSQLPREPAREs1FROM dstring scanf d 可以USING VAR1 VAR2 14 PREPARE和EXECUTE实例 这是一个提交修改语句的pro c实例 includeEXECSQLBEGINDECLARESECTION VARCHARuid 20 VARCHARpwd 20 charselect 132 floatcomm EXECSQLENDDECLARESECTION EXECSQLINCLUDESQLCA Main charwhere 80 intscode 15 strcpy uid arr SCOTT uid len strlen uid arr strcpy pwd arr TIGER pwd len strlen pwd arr EXECSQLWHENEVERSQLERRORGOTOerrpt EXECSQLCONNECT uidIDENTIFIEDBY pwdPrintf connectedtooracleasuser s n uid arr strcpy select UPDATEEMPSETCOMM commWHERE Printf pleaseenterwhereclauseforthefollowing n Printf s select Scode scanf s where If scode EOF scode 0 16 Printf invalidentry n exit 1 Strcat select where EXECSQLPREPARES1FROM select Printf pleaseentercommission Scanf f 17 3 PREPARE和FETCH语句 针对SELECT语句引用方式用以下五步 A PREPAREFROMB DECLAREFORC OPEN USING 变量名1 变量名2 D FETCHINTO 变量名1 变量名2 E CLOSE 18 PREPARE和EXECUTE实例 这是一个在查询中使用WHERE语句的pro c实例 includeEXECSQLBEGINDECLARESECTION VARCHARuid 20 VARCHARpwd 20 charselect 132 floatsal intdepno charename 10 EXECSQLENDDECLARESECTION EXECSQLINCLUDESQLCA Main charwhere 80 intscode inti strcpy uid arr SCOTT uid len strlen uid arr strcpy pwd arr TIGER pwd len strlen pwd arr 19 EXECSQLWHENEVERSQLERRORGOTOerrpt EXECSQLCONNECT uidIDENTIFIEDBY pwdPrintf connectedtooracleasuser s n uid arr strcpy select selectename salfromEMP Printf pleaseenterwhereclauseforthefollowing n Printf s select Scode scanf s where If scode EOF scode 0 Printf invalidentry n exit 1 Strcat select where EXECSQLPREPARES1FROM select 20 EXECSQLDECLAREC1CURSORFORS1 EXECSQLOPENC1 EXECSQLWHENEVERNOTFOUNDGOTOendloop For i 1 i