数据库嵌入式SQL案例

数据库嵌入式SQL案例1. 数据库及表结构的创建student(sno,smame,ssex,sage,sdept)course(cno,cname,cpno,ccredit)sc(sno,cno,grade)users(uno,uname,upassword,uclass)CREATE DATABASE XXGL;goUSE XXGL;CREATE TABLE student (sno char(5) NOT null primary key,sname char(6) null,ssex char(2) null DEFAULT …男‟ CHECK(ssex=‟男‟ or ssex= …女‟),sage int null,sdept char(2) null);CREATE TABLE course (cno char(1) NOT null primary key,cname char(10) null,cpno char(1) null,ccredit int null DEFAULT 2 CHECK (ccredit>=0 and ccredit <=50));CREATE TABLE sc(sno char(5) NOT null,cno char(1) NOT null,grade int null,CONSTRAINT FK_sc_course FOREIGN KEY(cno) REFERENCES course (cno), CONSTRAINT FK_sc_student FOREIGN KEY(sno) REFERENCES student (sno) );CREATE TABLE users(uno char(6) NOT NULL PRIMARY KEY CLUSTERED (uno),uname char(10) NOT NULL,upassword varchar(10) NULL,uclass char(1) NULL DEFAULT …A‟);2.数据库的连接（connection）在main()主程序中main(int argc, char ** argv, char ** envp){//各变量定义略//SQL Server 支持的嵌入式C程序，先说明变量与主变量EXEC SQL BEGIN DECLARE SECTION;//用于连接的主变量说明char szServerDatabase[(SQLID_MAX*2)+2=]””; //放数据库服务器与数据库名char szLonginPassword[(SQLID_MAX*2)+2]=””; //放登录用户名与口令EXEC SQL END DECLARE SECTION;//接着是连接的相关设置与错误处理设置EXEC SQL WHENEVER SQLERROR CALL ErrorHandler();EXEC SQL SET OPTION LOGINTIME 10;EXEC SQL SET OPTION QUERYTIME 100;……//GetConnecToInfo()实现衔接信息的获取nRet=GetConnectToInfo(argc,argv,szServerDatabase,szLonginPassword);if (!nRet){teturn (1);}EXEC SQL CONNECT TO: szServerDatabase USER : szLonginPassword;//真正与SQL SERVER 连接If (SQLCODE==0){printf(“Connection to SQL Server established\n”);}else { printf(“ERROR:Connection to SQL Server FAILED\n”);return(1);}……//主体程序含各种嵌入式SQL命令//main()结束时断开SQL Server的连接EXEC SQL DESCONNECT ALL;//}//end of main()3. 表的初始创建程序如下：int create_student_table(){ char yn[2];EXEC SQL BEGIN DECLARE SECTION;char tname[21]=”××××××××××××××”;EXEC SQL END DECLARE SECTION;EXEC SQL select name into:tname from sysobjectswhere (xtype=‟U‟and name=‟student‟);if (SQLCODE==0||strcmp(tname, “student”)==0){printf(“The student table already exists,Do you want to delete it?\n”,SQLCODE);printf(“Delete the table?(y—yes,n--no):”);scanf(“%s”,&yn);if (yn[0]==‟y‟||yn[0]== ‟Y‟){EXEC SQL drop table student;}if (SQLCODE==0){printf(“Drop table student successfully!%d\n\n”,SQLCODE);}else { printf(“ERROR:drop table student %d\n\n”,SQLCODE);}}else return -1;}EXEC SQL create table student(sno char(5) NOT null primary key,sname char(6) null,ssex char(2) null DEFAULT …男‟CHECK（ssex=‟男‟ or ssex= …女‟）,sage int null,sdept char(2) null);if (SQLCODE==0){printf(“Success to create table student!%d\n\n”,SQLCODE);}else{ printf(“ERROR:create table student %d\n”,SQLCODE);}EXEC SQL begin transactioninsert into student values(…95001‟,‟李斌‟,‟男‟,16,‟CS‟)//其它插入语句略commit transaction;if (SQLCODE==0){printf(“Success to insert rows to student table!%d\n\n”,SQLCODE);}else{ printf(“ERROR: insert rows %d\n\n”,SQLCODE);}return(0);}4. 表记录的插入int insert_rows_into_student_table(){ EXEC SQL BEGIN DECLARE SECTION;char isno[]=”95002”;char isnane[]=”××××××”;char issex[]=”男” ;int isage=18;char isdept[]=”CS”;int isnameind=0;//其他指示标量定义略EXEC SQL BEGIN DECLARE SECTION;char yn[2];while(1){printf(“Please input sno(eg:95001):”);scanf(“%s”,isno);//其他变量与指示变量输入代码类似（略）EXEC SQL insert into student(sno,sage,ssex,sname,sdept)values(:isno,:isage:isageind,:issex:issexind,:isname:isnameind,:isdept:isdeptind);if(SQLCODE==0){printf(“execute successfully!%d\n\n”,SQLCODE);}else{ printf(“ERROR: execute %d\n”,SQLCODE);}printf(“Insert again?(y—yes,n—no):”);scanf(“%s”,&yn);if (yn[0]==‟y‟||yn[0]== ‟Y‟){continue;}else break;}return(0);}5. 表记录的修改int current_of_update_for_student(){ char yn[2];…//变量定义略EXEC SQL SET CURSORTYPE CUR_STANDARD;printf(“Please input deptname to be updated(CS、IS、MA...,**--ALL):\n”);scanf(“%s”,deptname);if(strcmp(deptname,”*”)==0||strcmp(deptname,”**”)==0) strcpy(deptname,”%”);EXEC SQL DECLARE sx2 CURSOR FOR SELECT sno,sname,ssex,sage,sdept FROM student where sdept like:deptnamefor update of sname,ssex,sage,sdept;EXEC SQL OPEN sx2;while(SQLCODE==0){ EXEC SQL FETCH sx2 INTO:hsno,:hsname:ihsname,:hssex:ihssex,:hsage:ihsage,:hsdept:ihsdept;if(SQLCODE!=0) continue;printf(“%s\n”,”sno sname ssex sage sdept”);//学生记录的显示代码略printf(“UPDATE?(y/n/0,y--yes,n--no,0--exit)”);scanf(“%s”,&yn);if(yn[0]==‟y‟||yn[0]== ‟Y‟){//输入新的学生记录值到主变量中代码略EXEC SQL UPDATE student set sage=:isage:isageind,sname=:isname:isnameind,ssex=:issex:issexind,sdept=:isdept:isdeptind where current of sx2;};if(yn[0]==”0”) break;};EXEC SQL CLOSE sx2;return(0);}6. 表记录的删除int current_of_delete_for_student(){ char yn[2];…//变量定义略EXEC SQL SET CURSORTYPE CUR_STANDARD;printf(“Please input deptname to be updated(CS、IS、MA...,**--ALL):\n”);scanf(“%s”,deptname);if(strcmp(deptname,”*”)==0||strcmp(deptname,”**”)==0) strcpy(deptname,”%”);EXEC SQL DECLARE sx CURSOR FORSELECT sno,sname,ssex,sage,sdept FROM studentwhere sdept like:deptnamefor update of sname,ssex,sage,sdept;EXEC SQL OPEN sx;while(SQLCODE==0){ EXEC SQL FETCH sx INTO:hsno,:hsname:ihsname,:hssex:ihssex,:hsage:ihsage,:hsdept:ihsdept;if(SQLCODE!=0) continue;printf(“%s %5s %s %s\n”,”sno “,”sname “,” ssex”,” sage”,”sdept”);//学生记录的显示代码略printf(“DELETE?(y/n/0,y--yes,n--no,0--exit)”);scanf(“%s”,&yn);if(yn[0]==‟y‟||yn[0]== ‟Y‟){//输入新的学生记录值到主变量中代码略EXEC SQL delete from where current of sx;};if(yn[0]==”0”) break;};EXEC SQL CLOSE sx;return(0);}7. 表记录的查询int using_cursor_to_list_student(){ …//变量定义略EXEC SQL declare studentcursor cursorfor select* from student order by sno for read only;EXEC SQL open studentcursor;if(SQLCODE==0){ printf(“Open successfully! %d\n”,SQLCODE);}else { printf(“ERROR:Open %d\n”,SQLCODE);}printf(“\n”);printf(“sno sname ssex sage sdept \n”);while(SQLCODE==0){ EXEC SQL FETCH NEXT studentcursor INTO:csno,:csname:csnamenull,:cssex:cssexnull,:csage:csagenull,:csdept:csdeptnull;if(SQLCODE!=0){ //学生记录的显示代码略}}printf(“\n”);EXEC SQL CLOSE studentcursor;return(0);}8. 实现统计功能int using_cursor_to_total_s_sc(){ …//变量定义略EXEC SQL declare totalssc cursorfor select student.sno,sname,count(grade),sum(grade),avg(grade),MIN(grade),MAX(grade) from student sc where student.sno=sc.snogroup by student.sno,sname for read only;EXEC SQL open totalssc;if(SQLCODE==0){ printf(“Open successfully! %d\n”,SQLCODE);}else { printf(“ERROR:Open %d\n”,SQLCODE);}printf(“\n”);printf(“Success to total grade:! %d\n\n”,SQLCODE);printf(“sno sname count sum avg min max \n”);while(SQLCODE==0){ EXEC SQL FETCH NEXT totalssc INTO:isno,:isname:isnameind,:icnt:icnti,:isum:isumi,:iavg:iavgi, :imin:imini, :imax:imaxi;if(SQLCODE!=0){ //统计结果的显示代码略}else { printf(“ERROR:end of result %d\n”,SQLCODE);}}printf(“\n”);EXEC SQL CLOSE totalssc;return(0);}9. SQL的动态执行int dynamic_exec_sql_command(){ EXEC SQL BEGIN DECLARE SECTION;char cmd[81];EXEC SQL END DECLARE SECTION;char c,str[7];printf(“Please input a sql command(DELETE、UPDATE、INSERT):\n”);c=getchar(0);gets(cmd);if(strlen(cmd)>=6) strncpy(str,cmd,7);else {printf(“Please input correct command. \n”); return(-1);}if(strcmp(str,”select ”)==0){ printf(“Please input only DELETE、UPDA TE、INSERT command. \n”);return(-1);}EXEC SQL execute immediate:cmd;if(SQLCODE==0){ printf(“The sql command is executed successfully! %d\n”,SQLCODE);}else { printf(“ERROR: execute the sql command .%d\n”,SQLCODE);}return(0);}10. 通用统计功能int using_cursor_to_total_ty(){//变量定义略get(cmd);//输入动态统计SQL命令//判断cmd 中统计命令是否有效程序略EXEC SQL declare total_ty_cur cursor for total_ty;//定义游标EXEC SQL prepare total_ty from :cmd;//准备动态命令EXEC SQL SET CURSORTYPE CUR_STANDARD;//设置游标类型EXEC SQL open total_ty_cur; //打开游标if(SQLCODE==0){ printf(“Open successfully! %d\n”,SQLCODE);}else { printf(“ERROR:Open %d\n”,SQLCODE);}printf(“分组字段名统计值\n”);//以下程序组织输出结果while(SQLCODE==0){//取游标当前记录并显示EXEC SQL FETCH NEXT total_ty_cur INTO: con,:icnt:icnti;if(SQLCODE!=0){ printf(“%s”, con);if(icnti==0) printf(“%f\n”,icnt);else printf(“null\n”);}else { printf(“ERROR:end of the results%d\n”,SQLCODE);}}EXEC SQL close total_ty_cur;return(0);}准备实验环境：(1)新建C:\esqlc目录，SQL SERVER7.0（也可以是SQL Server2000）安装盘上的\devtools\include 目录、\devtools\x86lib目录、\x86\binn目录与\devtools\samples\esqlc中的例子复制到c:\esqlc目录中，嵌入了SQL的C语言程序也放于此目录中。