数据库编程-补充材料-ORACLEproc.ppt

1,说明SQL通讯区,在程序头加上 EXEC SQL INCLUDE SQLCA; 其中包括许多变量，如返回码等。 与SQL连接语句（CONNECT） EXEC SQL CONNECT :用户名变量 IDENTIFIED BY ：口令变量 或 EXEC SQL CONNECT :用户名变量 /：口令变量,2,伪类型VARCHAR,只有DECLARE部分引用，可认为是C的扩充类型。描述如下 EXEC SQL BEGIN DECLARE SECTION; VARCHAR job20; EXEC SQL END DECLARE SECTION; 等价于： Struct unsigned/*2bytes*/ short int len; unsigned char arr40 job;,3,在ORACLE上注册和注销,说明：这是以SCOTT/TIGER为用户名和口令登陆进入数据库的PRO*C程序例。 #include EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid20; VARCHAR pwd20; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA;,4,在ORACLE上注册和注销,Main() strcpy(uid.arr,”SCOTT”); uid.len=strlen(uid.arr); strcpy(pwd.arr,”TIGER”); pwd.len=strlen(pwd.arr); EXEC SQL CONNECT :uid IDENTIFIED BY ：pwd Printf(“connect ed to oracle user%sn”,uid.arr); EXEC SQL COMMIT WORK RELEASE; Exit(0);,5,创建一个表,#include EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid20; VARCHAR pwd20; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; Main() strcpy(uid.arr,”SCOTT”); uid.len=strlen(uid.arr); strcpy(pwd.arr,”TIGER”); pwd.len=strlen(pwd.arr);,6,EXEC SQL CONNECT :uid IDENTIFIED BY ：pwd Printf(“connected to oracle as user%sn”,uid.arr); EXEC SQL CREATE TABLE emp (empno number, ename char(15), job char(10), mgr number, hiredate date, sal number, deptno number); Printf(“table emp created! n”); EXEC SQL COMMIT WORK RELEASE; Exit(0);,7,1.EXECUTE IMMEDIATE 预编译任何SQL语句（除SELECT）并执行之。 SQL语句可以是文字量，但它可以不包括任何主变量（既不包括输入，也不包括输出主变量） EXECUTE IMMEDIATE只能带一个参数，执行一次 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.EXECUTE IMMEDIATE 立即执行方式,A.SQL语句不能是SELECT语句，只能是U,D,I语句 B. SQL语句不能包含主变量 例：EXEC SQL BEGIN DECLARE SECTION; VARCHAR dstring80; EXEC SQL END DECLARE SECTION; scanf(“%s”,dstring); EXEC SQL EXECUTE IMMEDIATE :dstring;,10,EXECUTE IMMEDIATE 实例,这是一个提交修改语句的pro*c实例。 #include EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid20; VARCHAR pwd20; char select132; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; Main() char where80; int scode;,11,strcpy(uid.arr,”SCOTT”); uid.len=strlen(uid.arr); strcpy(pwd.arr,”TIGER”); pwd.len=strlen(pwd.arr); EXEC SQL WHENEVER SQLERROR STOP; EXEC SQL CONNECT :uid IDENTIFIED BY ：pwd Printf(“connected to oracle as user%sn”,uid.arr); strcpy(select,”UPDATE EMP SET COMM=100 WHERE”); Printf(“please enter where clause for the following:n”); Printf(“%s”,select); Scode=scanf(“%s”,where); If(scode=EOF|scode=0),12,Printf(“invalid entry!n”); exit(1); Strcat(select,where); EXEC SQL EXECUTE IMMEDIATE:select; Printf(“%d records updated.n”,sqlca.sqlerrd2); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL COMMIT WORK RELEASE; Exit(0); ,13,2. PREPARE 和EXECUTE语句 准备执行方式,A.SQL语句不能是SELECT语句，只能是U,D,I语句 B. SQL语句可包含主变量，可多次使用。 例：要执行delete from emp where empno=:pempno scanf(“%s”,dstring); EXEC SQL PREPARE s1 FROM : dstring; scanf(“%d”, 可以USING :VAR1, :VAR2,14,PREPARE 和EXECUTE实例,这是一个提交修改语句的pro*c实例。 #include EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid20; VARCHAR pwd20; char select132; float comm; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; Main() char where80; int scode;,15,strcpy(uid.arr,”SCOTT”); uid.len=strlen(uid.arr); strcpy(pwd.arr,”TIGER”); pwd.len=strlen(pwd.arr); EXEC SQL WHENEVER SQLERROR GOTO errpt; EXEC SQL CONNECT :uid IDENTIFIED BY ：pwd Printf(“connected to oracle as user%sn”,uid.arr); strcpy(select,”UPDATE EMP SET COMM=:comm WHERE”); Printf(“please enter where clause for the following:n”); Printf(“%s”,select); Scode=scanf(“%s”,where); If(scode=EOF|scode=0),16,Printf(“invalid entry!n”); exit(1); Strcat(select,where); EXEC SQL PREPARE S1 FROM :select; Printf(“please enter commission:”); Scanf(“%f”, ,17,3. PREPARE 和FETCH语句， 针对SELECT语句 引用方式用以下五步：,A. PREPARE FROM B. DECLARE FOR C.OPEN USING:变量名1， :变量名2, D.FETCH INTO :变量名1， :变量名2, E.CLOSE ,18,PREPARE 和EXECUTE实例,这是一个在查询中使用WHERE语句的pro*c实例。 #include EXEC SQL BEGIN DECLARE SECTION; VARCHAR uid20; VARCHAR pwd20; char select132; float sal; int depno; char ename10 EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; Main() char where80; int scode; int i; strcpy(uid.arr,”SCOTT”); uid.len=strlen(uid.arr); strcpy(pwd.arr,”TIGER”); pwd.len=strlen(pwd.arr);,19,EXEC SQL WHENEVER SQLERROR GOTO errpt; EXEC SQL CONNECT :uid IDENTIFIED BY ：pwd Printf(“connected to oracle as user%sn”,uid.arr); strcpy(select,”select ename,sal from EMP”); Printf(“please enter where clause for the following:n”); Printf(“%s”,select); Scode=scanf(“%s”,where); If(scode=EOF|scode=0) Printf(“invalid entry!n”); exit(1); Strcat(select,where); EXEC SQL PREPARE S1 FROM :select;,20,EXEC SQL DECLARE C1 CURSOR FOR S1; EXEC SQL OPEN C1; EXEC SQL WHENEVER NOT FOUND GOTO endloop; For (i=1;i+) EXEC SQ