一、环境
创建例子用到的视图：
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);
/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s/n", 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("连接成功\n\n");
else{
printf("连接失败,exit(0)");
getchar();
exit(0);
}
EXEC SQL DECLARE c2 CURSOR FOR
SELECT SNO,SNAME FROM STUDENT;
printf( "%-13s%-8s\n","学号","学生姓名");
printf( "%-13s%-8s\n","--------","------");
EXEC SQL OPEN c2;
do{
EXEC SQL FETCH c2 INTO :sno,:sname; /* :rk.3:erk. */
if (sqlca.sqlcode!= 0) break;
printf( "%-13s%-8s\n",sno, sname);
}while(1);
printf("查询学生成绩，请输入学生号:\n");
scanf("%7s",&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\n", "学生姓名","课程名","成绩");
printf( "%-13s%-15s%-6s\n", "--------","----------", "-----");
do{
EXEC SQL FETCH c1 INTO :sname,:cname,:grade;
if(sqlca.sqlcode != 0) break;
printf( "%-13s%-15s%-6.2f\n", sname, cname, grade);
} while(1);
return 0;
}
Makefile:
TARGET = stu_query
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): $(SRCS) $(OBJS)
$(CC) -o $@ $(OBJS) $(CFLAGS) $(COMMLIB)
clean :
$(RM) *.lis $(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 08:27:43 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 马娟
查询学生成绩，请输入学生号:
9302203
学生姓名 课程名 成绩
-------- ---------- -----
马志元 数学分析 98.00
马志元 数据库设 89.00
马志元 数据库原 99.00
