oracle pro*c的一个简单例子

一、环境

参见unbuntu下Docker安装oracle和mysql，sqlserver。

创建例子用到的视图：

SQL> create view mysc as select sc.sno,sc.cno,sc.grade,student.sname,course.cname from sc,student,course where sc.sno=student.sno and sc.cno=course.cno;

二、代码

stu_query.pc

#include 
#include 
#include 
#include 
#include 

void sql_error(msg)
char *msg;
{
    char err_msg[512];
    size_t buf_len, msg_len;

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("/n%s/n", msg);

    
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s/n", (int)msg_len, err_msg);

    EXEC SQL ROLLBACK RELEASE;
    exit(EXIT_FAILURE);
}


EXEC SQL BEGIN DECLARE SECTION;
    char *userid="mymotif";
    char *passwd="wxwpxh";
    char *sid="XE";
    char sno[8];
    char sname[10];
    char cname[13];
    float grade;
    short dept;
    short ind1;
    char qstring[80];
EXEC SQL END DECLARE SECTION;





int main()
  {
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");
    EXEC SQL CONNECT :userid IDENTIFIED BY :passwd USING :sid;
    if (sqlca.sqlcode == 0)
      printf("连接成功

");
    else{
      printf("连接失败,exit(0)");
      getchar();
      exit(0);
    }
    EXEC SQL DECLARE c2 CURSOR FOR
      SELECT SNO,SNAME FROM STUDENT;

    printf( "%-13s%-8s
","学号","学生姓名");
    printf( "%-13s%-8s
","--------","------");
    EXEC SQL OPEN c2;
    do{
      EXEC SQL FETCH c2 INTO :sno,:sname; 
      if (sqlca.sqlcode!= 0) break;
      printf( "%-13s%-8s
",sno, sname);
    }while(1);


    printf("查询学生成绩，请输入学生号:
");
    scanf("%s",sno);
    strcpy(qstring,"SELECT sname,cname,grade FROM MYSC WHERE SNO=:sno");

    EXEC SQL PREPARE q1 FROM :qstring;
    EXEC SQL DECLARE c1 CURSOR FOR q1;
    EXEC SQL OPEN c1 USING :sno;
    printf( "%-13s%-15s%-6s
", "学生姓名","课程名","成绩");
    printf( "%-13s%-15s%-6s
", "--------","----------", "-----");
    do{
     EXEC SQL FETCH c1 INTO :sname,:cname,:grade;
     if(sqlca.sqlcode != 0) break;
     printf( "%-13s%-15s%-6.2f
", sname, cname, grade);
    } while(1);
    return 0;

}

Makefile:

TARGET  = stu_query
class="lazy" data-srcS    = stu_query.c
OBJS    = stu_query.o
COMMLIB = -lpthread -lclntsh
 
LINKER  = $(CC)
RM      = /bin/rm -f
PROC    = proc
 
PROCFLAGS = code=ANSI_C ireclen=2048 oreclen=2048 parse=none include=/usr/include
     include=/usr/include/linux
     include=/usr/local/include
 
CFLAGS  = -I$(ORACLE_HOME)/sdk/include
            -L$(ORACLE_HOME)/lib
 
 .SUFFIXES: .o .c .pc
 
 .c.o:
	$(CC) -c $(CFLAGS) $<
 
 .pc.c:
	@echo "PROC ."
	$(PROC) iname=$*.pc $(PROCFLAGS)
 
$(TARGET): $(class="lazy" data-srcS) $(OBJS)
	$(CC) -o $@ $(OBJS) $(CFLAGS) $(COMMLIB)
 
clean :
	$(RM) *.lis $(class="lazy" data-srcS) $(TARGET) $(OBJS) $(TARGET:%=%.c)

三、编译运行：

$ make
PROC .
proc iname=stu_query.pc code=ANSI_C ireclen=2048 oreclen=2048 parse=none include=/usr/include include=/usr/include/linux include=/usr/local/include

Pro*C/C++: Release 11.2.0.4.0 - Production on 星期日 7月 19 13:47:49 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

系统默认选项值取自于:  /opt/ora11g/instantclient_11_2/precomp/admin/pcscfg.cfg

cc -c -I/opt/ora11g/instantclient_11_2/sdk/include -L/opt/ora11g/instantclient_11_2/lib stu_query.c
cc -o stu_query stu_query.o -I/opt/ora11g/instantclient_11_2/sdk/include -L/opt/ora11g/instantclient_11_2/lib -lpthread -lclntsh
$ ./stu_query 
连接成功

学号       学生姓名
--------     ------  
9302203      马志元
9302303      马元   
9309203      王海滨
9402203      金力标
9402208      马娟   
查询学生成绩，请输入学生号:
9402203
学生姓名 课程名      成绩
--------     ----------     ----- 
金力标    数学分析   99.00 
金力标    数据结构   88.00 
金力标    分析力学   86.00 
金力标    高等分析   98.00