使用嵌入式SQL对学生-课程数据库中的表完成下述功能
(1)查询某一门课程的信息。要查询的课程由用户在程序运行过程中指定,放在主变量中。
(2)查询选修某一门课程的选课信息,要查询的课程号由用户在程序运行过程中指定,放在主变量中,然后根据用户的要求修改其中某些记录的成绩字段。
(1)
EXEC SQL SELECT Cno,Cname,Cpno,Credit
INTO :Hcno,:Hcname,:Hcpno,:Hcredit
FORM Course
WHERE Cname=:givencname;
(2)
//Wirten By Tanxin-ouc
EXEC SQL BEGIN DECLARE SECTION;//主变量说明开始
char Hsno[64];
char Hcno[64];
int Hgrade;
int NEWGRADE;
EXEC SQL END DECLARE SECTION;//主变量说明结束
long SQLCODE;
EXEC SQL INCLUDE sqlca;//定义SQL通信区
int main(void)
{
int count = 0;
char yn; //变量yn代表yes或no
printf("Please input the calss number:");
scanf("%s", &classno);
EXEC SQL CONNECT TO TEST@ localhost:54321 USER "SYSTEM" / "MANAGER";//链接数据库TEST
EXEC SQL DECLARE SX CURSOR FOR//定义游标SX
SELECT Sno, Cno, Grade
FROM SC
WHERE Cno = :classno;
EXEC SQL OPEN SX;
for (;;)
{
EXEC SQL FETCH SX INTO : HSno, : Hcno, : Hgrade;
if (sqlca.sqlcode != 0)
break;
if (count++ == 0)
printf("\n%-10s%-20s%-10s\n", "Sno", "Cno", "Grade");
printf("%-10s%-20s%-10d\n", HSno, Hcno, Hgrade);
printf("UPDATE GRADE(y/n)?");
do {
scanf("%c", &yn);
} while (yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
if (yn == 'y' || yn == 'Y')
{
printf("INPUT NEW GRADE:");
scanf("%d", &NEWGRADE);
EXEC SQL UPDATE SC//嵌入式SQL更新语句
SET Grade = :NEWGRADE
WHERE CURRENT OF SX;
}
}
EXEC SQL CLOSE SX;//关闭游标SX
EXEC SQL COMMIT WORK;//提交更新
EXEC SQL DISCONNECT TEST;//断开数据库连接
}