Pro*c中嵌入PL/SQL块

主要功能是：程序提示用户输入 部门号，然后显示该部门的名字，地址及职员人数。

Pro*C中 samplePL_SQL.pc 代码如下：

1 #include <string.h> 2 #include <stdio.h> 3 #include <sqlca.h> 4 5 typedef char asciz; 6 exec sql begin declare section; 7 8 exec sql type asciz is string(20); 9 asciz username[20]; //用户名 10 asciz password[20]; //密码 11 12 int dept_number; 13 varchar dept_name[50]; 14 varchar location[50]; 15 varchar create_date[25]; 16 int emp_sum_number; 17 exec sql end declare section; 18 void getConn(); /* 建立数据库的连接 */ 19 void sql_error(char *); /* 显示出错信息 */ 20 void main(void) 21 { 22 getConn(); 23 while(1) 24 { 25 printf("/nEnter department Number(0 to quit):"); 26 scanf("%d",&dept_number); 27 if( dept_number == 0 ) 28 { 29 exec sql commit work release; 30 printf("Exiting program/n"); 31 exit(0); 32 }//End if; 33 exec sql whenever not found do continue; //PL/SQL块 34 exec sql execute 35 begin 36 select nvl(dname,'NULL'),nvl(loc,'NULL'), 37 nvl(cdate,sysdate),nvl(dnumber,'0') 38 into :dept_name,:location,:create_date,:emp_sum_number 39 from xtn_dept 40 where deptno = :dept_number; 41 end; 42 end-exec; 43 printf("/nDept No.|Dept Name|Location|Create Date|Emp Samary"); 44 printf("/n-------- --------- -------- ----------- ----------/n"); 45 printf("%d /t%s %s %s/t%d/n",dept_number,dept_name.arr, 46 location.arr,create_date.arr,emp_sum_number); 47 }//End while 48 exec sql commit work release; 49 exit(0); 50 } 51 void getConn() 52 { 53 strcpy(username,"scdb"); 54 strcpy(password,"scdb"); 55 exec sql whenever sqlerror do sql_error("Connected to oracle error."); 56 exec sql connect :username identified by :password; 57 }//End getConn(); 58 void sql_error(char *msg) 59 { 60 char err_msg[512]; 61 int buf_size,msg_len; 62 exec sql whenever sqlerror continue; 63 printf("/n%s/n",msg); 64 buf_size = sizeof( err_msg ); 65 sqlglm( err_msg ,&buf_size,&msg_len ); 66 printf("%.*s/n",msg_len,err_msg); 67 exec sql rollback work release; 68 exit(-1); 69 }

在这里要注意得是

exec sql execute

    begin

    .....

    end;

end-exec; -->这个地方千要注意，不能写能 end_exec,这样会出错。

我遇到下面错误：

PCC-S-02022, Found end of file while scanning a SQL statement

将end_exec 改为 end-exec 这个错误就好了。

相应的表：

create table xtn_dept
(
       deptno number(8) not null,
       dname varchar2(40) default '部门名称',
       loc   varchar2(40) default '本地location',
       cdate date default sysdate,
       dnumber number(8) default '0'
);

makefile:

  1 include ${ORACLE_HOME}/rdbms/lib/env_rdbms.mk
  2 ORAINC=${ORACLE_HOME}/precomp/public
  3 ORALIB=${ORACLE_HOME}/lib
  4 ORAPUBLIC=${ORACLE_HOME}/rdbms/public
  5
  6 PROC = proc
  7 PROCINFO = /
  8            userid=scdb/scdb /
  9            include=$(ORAINC) /
 10            include=$(ORAPUBLIC) /
 11            lines=true /
 12            parse=full /
 13            sqlCheck=semantics
 14
 15 CC = gcc -ggdb3 -Wall
 16
 17 CFLAGS = -I $(ORAINC) -I $(ORAPUBLIC)

 98 obj_PL_SQL = samplePL_SQL.o
 99 samplePL_SQL:$(obj_PL_SQL)
100     $(CC) $(CFLAGS) $(obj_PL_SQL) -o samplePL_SQL -L $(ORALIB) $(PROLDLIBS) -lm
101 samplePL_SQL.c:samplePL_SQL.pc
102     $(PROC) $(PROCINFO) INAME=samplePL_SQL.pc ONAME=samplePL_SQL.c
103
104 .PHONY:clean
105 clean:
106     rm -f samplePL_SQL *.o *.c