[数据库] 一次pro*c练习总结

泥烟

已于 2022-05-14 21:24:14 修改

阅读量516

点赞数 1

分类专栏：数据库文章标签：数据库 sql oracle

于 2022-05-09 16:47:54 首次发布

本文链接：https://blog.csdn.net/qq_39391544/article/details/124669604

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

一.实验内容:

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, 了解了一点编译的步骤

泥烟

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
2
评论
[数据库] 一次pro*c练习总结

文章目录一.实验内容:1.pc程序预编译编译为.c文件2.Pro*C编程练习二.实验步骤:1.pc程序预编译编译为.c文件Ⅰ.在cmd中`studentAgeQuery.pc`所处目录下预编译为`example.c`Ⅱ.在IDE中新建空项目并导入`example.c`Ⅲ.配置关联本地Oracle的链接器和目录文件Ⅳ.编译运行2.Pro*C编程练习Ⅰ.建立基本表,插入相关数据Ⅱ.编辑`studentAgeQuery.pc`, 实现两个功能**>注意**Ⅲ.预编译为test.c, 导入项目Ⅳ.编译运行三.
复制链接

扫一扫