oracle c 教程,oracle pro*c的一个简单例子

一、环境

创建例子用到的视图：

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", (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; /* :rk.3:erk. */

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

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 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