文章目录
一.实验内容:
1.pc程序预编译编译为.c文件
2.Pro*C编程练习
1.查看各个系别学生信息
2.查看个人成绩
二.实验步骤:
1.pc程序预编译编译为.c文件
关于这次的实验， 相信很多同学都遇到了下面这样的情况
九几年的vc6实在是太老旧了，不支持64位， 我们安装的Oracle大都是64位的
我是用的IDE是小熊猫DEVC++
Ⅰ.在cmd中studentAgeQuery.pc所处目录下预编译为example.c
proc studentAgeQuery.pc example.c
Ⅱ.在IDE中新建空项目并导入example.c
Ⅲ.配置关联本地Oracle的链接器和目录文件
Ⅳ.编译运行
效果图:
2.Pro*C编程练习
Ⅰ.建立基本表,插入相关数据
/**建立三张表，并插入数据 */
set feedback off;
show user;
drop table sc;
drop table s;
drop table c;
create table s(
sno char(4) not null primary key,
sname varchar2(10),
sex char(3),
dept varchar2(4),
age int
);
insert into s values('801','金贤重','男','CS',23);
insert into s values('802','李敏镐','男','MA',25);
insert into s values('803','那英','女','CS',41);
insert into s values('804','金秀贤','女','MA',24);
insert into s values('805','刘德华','男',null,52);
insert into s values('806','文章','男','CS',27);
insert into s values('807','刘欢','男','MA',51);
insert into s values('808','马伊琍','女','CS',36);
insert into s values('809','姚笛','女','MA',23);
insert into s values('810','周杰伦','男',null,39);
create table c(
cno char(3) primary key,
cname varchar2(20),
credit int
);
insert into c values('01','数据库概论',3);
insert into c values('02','OS',4);
insert into c values('03','OOP',2);
insert into c values('04','Java',3);
insert into c values('05','Python',3);
insert into c values('06','D语言讲座',2);
create table sc(
sno char(4),
cno char(3),
grade numeric(3),
constraint sc_pk primary key(sno,cno),
constraint sc_fk1 foreign key(sno) references s(sno),
constraint sc_fk2 foreign key(cno) references c(cno),
constraint sc_grade check (grade between 0 and 100 or grade is null)
);
insert into sc values('801','01',81);
insert into sc values('802','01',59);
insert into sc values('803','01',0);
insert into sc values('804','01',60);
insert into sc values('805','01',30);
insert into sc values('806','01',90);
insert into sc values('807','01',60);
insert into sc values('808','01',null);
insert into sc values('801','02',null);
insert into sc values('802','02',30);
insert into sc values('803','02',45);
insert into sc values('804','02',80);
insert into sc values('805','02',70);
insert into sc values('806','02',60);
insert into sc values('801','03',90);
insert into sc values('802','03',60);
insert into sc values('803','03',40);
insert into sc values('804','03',null);
insert into sc values('805','03',50);
insert into sc values('806','03',70);
insert into sc values('807','03',70);
insert into sc values('808','03',80);
insert into sc values('801','04',40);
insert into sc values('802','04',0);
insert into sc values('803','04',null);
insert into sc values('804','04',80);
insert into sc values('805','04',90);
insert into sc values('806','04',60);
insert into sc values('807','04',70);
insert into sc values('808','04',50);
insert into sc values('809','05',0);
commit;
select * from s;
select * from c;
select * from sc;
Ⅱ.编辑studentAgeQuery.pc, 实现两个功能
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>
#include <sqlcpr.h>
void sql_error();//异常处理函数
int main() {
exec sql include sqlca;
exec sql begin declare section;
char user_name[20];
char user_pwd[20];
char db_name[20];
char ip[20];
char port[20];
char url[100];
char s_no[6];
char c_no[5];
char s_name[10];
char s_sex[4];
char c_name[20];
int i;
int grade;
int effect_gradeNum; //有效成绩的数量
int s_age;
int op;
float totalScore;
char s_dept[4];
exec sql end declare section;
/**输入登陆信息如下所示 */
exec sql whenever SQLERROR do sql_error(); //异常处理
printf("\n\n\b 欢迎登录学生年龄查询功能\n\n");
printf("\b\b IP地址：");
gets(ip);
printf("\b\b Port号：");
gets(port);
printf("\b\b 数据库服务器名称：");
gets(db_name);
printf("\b\b 用户名：");
gets(user_name);
printf("\b\b 密码：");
gets(user_pwd);
strcpy(url, ip);
strcat(url, ":");
strcat(url, port);
strcat(url, "/");
strcat(url, db_name);
exec sql connect :user_name
identified by :user_pwd using :url;
printf("\n\n 用户:%s 密码:%s,哈哈，登陆成功.\n", user_name, user_pwd);
system("pause");
while (1) {
system("cls");
printf("\n1.查看各个系别学生信息 2.查看个人成绩: ");
scanf("%d", &op);
exec sql declare sx cursor for
select sname,sex,age from s where dept=:s_dept;
exec sql declare sy cursor for
select cno, nvl(grade, -1) from sc
where sno=:s_no;
if (op == 1) {
printf("\n 输入系别代号: ");
scanf("%s", s_dept);
system("cls");
printf("\n\t\t%s系学生信息表", s_dept);
printf("\n\t---------------------------------------------");
printf("\n\t序号\t姓名\t性别\t年龄\t备注");
printf("\n\t---------------------------------------------");
i = 0;
exec sql open sx;
while (1) {
exec sql fetch sx into :s_name,:s_sex,:s_age;
if (sqlca.sqlcode != 0) break;
i ++;
printf("\n\t %d\t%4s%3s\t%d", i, s_name, s_sex, s_age);
printf("\n\t---------------------------------------------");
}
if (i != 0) printf("\n\t\t\t\t共%d人\n", i);
else printf("\n\t\t\t未找到系别为%s学生!\n", s_dept);
system("pause");
} else if (op == 2) {
printf("\n 输入学号: ");
scanf("%s", s_no);
exec sql select sname into :s_name from s where sno = :s_no;
system("cls");
printf("\n\t\t%s成绩明细表(-1表示未记录)", s_name);
printf("\n\t-------------------------------------");
printf("\n\t序号\t课程名 \t成绩");
printf("\n\t-------------------------------------");
i = 0;
totalScore = 0;
effect_gradeNum = 0;
exec sql open sy;
while (1) {
exec sql fetch sy into :c_no,:grade;
if (sqlca.sqlcode != 0) break;
exec sql select cname into :c_name from c where cno = :c_no;
i ++;
if (grade >= 0) {
totalScore += grade;
effect_gradeNum ++;
}
printf("\n\t %d\t%6s%d", i, c_name, grade);
printf("\n\t-------------------------------------");
}
if (i != 0) printf("\n\t\t\t\t平均成绩为: %.2f\n", totalScore / effect_gradeNum);
else printf("\n\t\t\t未找到该学生!\n");
system("pause");
} else {
break ;
}
}
system("pause");
exec sql close sx;
exec sql close sy;
exec sql commit release;
return 0;
}
void sql_error() {
printf("SQL语句错误:错误代号：%d 错误描述：%s\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
system("pause");
exit(0);
}
我用的是notepad++(解决一切编码问题的神器)
为两个功能分别声明游标sx, sy
exec sql declare sx cursor for
select sname,sex,age from s where dept=:s_dept;
exec sql declare sy cursor for
select cno, nvl(grade, -1) from sc
where sno=:s_no;
sy中成绩若为null, 则设为-1, 方便下面算平均
功能1
输入系别->打开游标->推进游标, 逐行输出结果
功能2
输入学号-> 1.利用学号sql查找姓名->2.推进游标, 记录cno, grade
->3.利用cno,sql查找课程名cname->输出结果,计算平均数并输出
totalScore / effect_gradeNum
>注意
有的成绩为null, 在定义游标时我使用了nvl(grade, -1), 所以累加总成绩(totalScore ,float型,)时需要辨别grade是否非负, 且需要累加effect_gradeNum(即有效成绩的数量)
关闭游标
exec sql close sx;
exec sql close sy;
Ⅲ.预编译为test.c, 导入项目
proc studentAgeQuery.pc test.c
(将前面的example.c注释掉)
Ⅳ.编译运行
效果图:
三.实验总结:
简单实现了基于c语言的嵌入式sql, 了解了一点编译的步骤
文章评论