实验六 嵌入式SQL应用

已于 2023-05-07 20:04:13 修改
阅读量808 收藏 14
点赞数 6
分类专栏： 数据库 文章标签： 数据库 服务器 sqlserver
于 2023-04-09 21:27:25 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_56101688/article/details/130048224
版权
  1. 先把文件放到合适的位置（c盘根目录）  
  2. 在SQL server里面把数据库c放入Windows服务器的服务器中
  3. 在SQLserver上新建一个服务器，对c数据库有访问权限。
  4. 通过SQL server进行身份验证 登录名：root 密码：root (自己设置的)
  5. 由于系统不兼容，先对main.exe进行设置如下  （1）、兼容模式： Windows 8/7 （2）、选中：以管理员身份运行此程序 （3）更改所有用户的设置-> 兼容模式： Windows 8/7 其次是安全： 编辑->安全：设置组或用户名 允许完全控制。
  6.  通过win+R打开命令面板，进行如下输入（cd\esqlc -> setenv ->run main -> main）

7. 输入SQL server服务器名称、ID和密码进行登录操作

  • 实验内容

1.模拟create_student_table()实现创建Teacher表。即实现create_teacher_table()子程序的功能。

int create_teacher_table()

{

    char yn[2];

    EXEC SQL BEGIN DECLARE SECTION;

      char tname[21]="xxxxxxxxxxx";

    EXEC SQL END DECLARE SECTION;

    EXEC SQL SELECT name into :tname FROM sysobjects

         WHERE (xtype = 'U' and name='teacher');

    if (SQLCODE == 0||strcmp(tname,"teacher")==0)

    {

       printf("The teacher 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 teacher;

          if (SQLCODE == 0)

          {

             printf("Drop table teacher successfully!%d\n\n",SQLCODE);

          }

          else

          {

             printf("ERROR: drop table teacher %d\n\n",SQLCODE);

          }

       }

       else return -1;

    }

    EXEC SQL CREATE TABLE teacher (

        tno char(5) NOT null primary key,

        ttname char(6) null ,

tsex char(2) null ,

tage int null ,

tdept char(2) null) ;

    if (SQLCODE == 0)

    {

       printf("Success to create table teacher!%d\n\n",SQLCODE);

    }

    else

    {

       printf("ERROR: create table teacher %d\n",SQLCODE);

    }

    EXEC SQL begin transaction

       insert into teacher values("t5001", "李斌", "男",16, "CS")

       insert into teacher values("t5002", "赵霞", "女",18, "IS")

       insert into teacher values("t5003", "周淘", "男",17, "CS")

       insert into teacher values("t5004", "钱乐", "女",18, "IS")

       insert into teacher values("t5005", "孙力", "男",16, "MA")

       commit transaction;

    if (SQLCODE == 0)

    {

       printf("Success to insert rows to teacher table!%d\n\n",SQLCODE);

    }

    else

    {

       printf("ERROR: insert rows %d\n\n",SQLCODE);

    }

    return(0);

}

2、模拟insert_rows_into_student_table()实现对Teacher表的记录添加。即实现insert_rows_into_teacher_table()子程序的功能。

int insert_rows_into_teacher_table()//已改

{

EXEC SQL BEGIN DECLARE SECTION;

int itage=18;

int itageind=0;

char itname[]="xxxxxx";

int itnameind=0;

char itsex[]="男";

int itsexind=0;

char itno[]="t5001";

char itdept[]="CS";

int itdeptind=0;

EXEC SQL END DECLARE SECTION;

char yn[2];

while(1)

{

  printf("Please input tno(eg:t5001):");

      scanf("%s",itno);

  printf("Please input name(eg:XXXX):");

      scanf("%s",itname);

      printf("Please input name indicator(<0 to set null):");

      scanf("%d",&itnameind);

      printf("Please input age(eg:18):");

      scanf("%d",&itage);

      printf("Please input age indicator(<0 to set null):");

      scanf("%d",&itageind);

      printf("Please input sex(eg:男):");

      scanf("%s",itsex);

      printf("Please input sex indicator(<0 to set null):");

      scanf("%d",&itsexind);

      printf("Please input dept(eg:CS、IS、MA...):");

      scanf("%s",itdept);

      printf("Please input dept indicator(<0 to set null):");

      scanf("%d",&itdeptind);

      EXEC SQL insert into teacher(tno,tage,tsex,ttname,tdept)

                values(:itno,:itage:itageind,:itsex:itsexind,

                       :itname:itnameind,:itdept:itdeptind);

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

}

3、模拟current_of_update_for_student()实现对Teacher表的记录修改。即实现current_of_update_for_teacher()子程序的功能。

int current_of_update_for_teacher()//已改

{

char yn[2];

    EXEC SQL BEGIN DECLARE SECTION;

      char deptname[3];

      char htno[6];

      char htname[7];

      char htsex[3];

      char htdept[3];

      float htage;

      int ihtdept=0;

      int ihtname=0;

      int ihtsex=0;

      int ihtage=0;

      float itage = 38;

      int   itageind = 0;

      char  itsex[3] = "男";

      int   itsexind = 0;

      char  itname[7] = "xxxxxx";

      int   itnameind = 0;

      char  itdept[3] = "CS";

      int   itdeptind = 0;

    EXEC SQL END DECLARE SECTION;

    EXEC SQL SET CURSORTYPE CUR_BROWSE;

    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 tx2 CURSOR FOR

        SELECT tno,ttname,tsex,tage,tdept

        FROM teacher

        where tdept like :deptname

        for update of tname,tsex,tage,tdept;

    EXEC SQL OPEN tx2;

    while( SQLCODE == 0)

    {

        EXEC SQL FETCH tx2 INTO :htno,:htname:ihtname,:htsex:ihtsex,

               :htage:ihtage,:htdept:ihtdept;

        if (SQLCODE!=0) continue;

        printf( "%s\n", "tno  tname  tsex  tage  tdept");

        printf("%s",htno);

        if (ihtname==0) printf(" %s",htname);

        else printf(" null");

        if (ihtsex==0) printf("  %s",htsex);

        else printf(" null");

        if (ihtage==0) printf("   %3.0f",htage);

        else printf(" null");

        if (ihtdept==0) printf("  %s\n",htdept);

        else printf(" null\n");

        printf("UPDATE ?(y/n/0,y--yes,n--no,0--exit)");

        scanf("%s",&yn);

        if (yn[0]=='y' || yn[0]=='Y')

        {

           printf("Please input new name(eg:XXXX):");

           scanf("%s",itname);

           printf("Please input name indicator(<0 to set null,9 no change):");

           scanf("%d",&itnameind);

           if (itnameind==9) {

               if (ihtname<0) itnameind=-1;

               else strcpy(itname,htname);

           }

           printf("Please input new age(eg:18):");

           scanf("%f",&itage);

           printf("Please input age indicator(<0 to set null,9 no change):");

           scanf("%d",&itageind);

           if (itageind==9) {

               if (ihtage<0) itageind=-1;

               else itage=htage;

           }

           printf("Please input new sex(eg:男):");

           scanf("%s",itsex);

           printf("Please input sex indicator(<0 to set null,9 no change):");

           scanf("%d",&itsexind);

           if (itsexind==9) {

               if (ihtsex<0) itsexind=-1;

               else strcpy(itsex,htsex);

           }

           printf("Please input new dept(eg:CS、IS、MA...):");

           scanf("%s",itdept);

           printf("Please input dept indicator(<0 to set null,9 no change):");

           scanf("%d",&itdeptind);

           if (itdeptind==9) {

               if (ihtdept<0) itdeptind=-1;

               else strcpy(itdept,htdept);

           }

           EXEC SQL UPDATE teacher set tage=:itage:itageind,

                ttname=:itname:itnameind,tsex=:itsex:itsexind,

                tdept=:itdept:itdeptind where current of tx2;

        };

if (yn[0]=='0') break;

    };

    EXEC SQL CLOSE tx2;

    return (0);

}

4、模拟current_of_delete_for_student()实现对Teacher表的记录删除。即实现current_of_delete_for_teacher()子程序的功能。

int current_of_delete_for_teacher()//已改

{

char yn[2];

    EXEC SQL BEGIN DECLARE SECTION;

      char deptname[3];

      char htno[6];

      char htname[7];

      char htsex[3];

      float htage;

      int ihtdept=0;

      int ihtname=0;

      int ihtsex=0;

      int ihtage=0;

    EXEC SQL END DECLARE SECTION;

    EXEC SQL SET CURSORTYPE CUR_STANDARD;

    printf("Please input deptname(CS、IS、MA...,**--All):\n");

    scanf("%s",deptname);

    if (strcmp(deptname,"*")==0||strcmp(deptname,"**")==0) strcpy(deptname,"%");

    EXEC SQL DECLARE tx CURSOR FOR

        SELECT tno,ttname,tsex,tage,tdept

        FROM teacher

        where tdept like :deptname

        for update of ttname,tsex,tage,tdept;

    EXEC SQL OPEN tx;

    while( SQLCODE == 0)

    {

        EXEC SQL FETCH tx INTO :htno,:htname:ihtname,

              :htsex:ihtsex,:htage:ihtage,:deptname:ihtdept;

        if (SQLCODE!=0) continue;

        printf( "%s %5s %s %s  %s\n", "sno  ","tname","tsex","tage","tdept");

        printf(" %s",htno);

        if (ihtname==0) printf(" %s",htname);

        else printf(" null");

        if (ihtsex==0) printf(" %s",htsex);

        else printf(" null");

        if (ihtage==0) printf(" %f",htage);

        else printf(" null");

        if (ihtdept==0) printf(" %s\n",deptname);

        else printf(" null\n");

        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 teacher where current of tx;

        };

if (yn[0]=='0') break;

    };

    EXEC SQL CLOSE tx;

    return (0);

}

5、模拟using_cursor_to_list_student()实现对Teacher表的记录查询。即实现using_cursor_to_list_teacher()子程序的功能。

int using_cursor_to_list_teacher()

{

EXEC SQL BEGIN DECLARE SECTION;

      char ctno[6];

      char ctname[7];

      char ctsex[3];

      double ctage;

      char ctdept[3];

      int ctnamenull=0;

      int ctsexnull=0;

      int ctagenull=0;

      int ctdeptnull=0;

    EXEC SQL END DECLARE SECTION;

    EXEC SQL declare teachercursor cursor

         for select *

             from teacher

             order by tno

         for read only;

    EXEC SQL open teachercursor;

    if (SQLCODE == 0)

    {

        printf("Open successfully!%d\n",SQLCODE);

    }

    else

    {

        printf("ERROR: open %d\n",SQLCODE);

    }

    printf("\n");

    printf("tno tname tsex tage tdept \n");

    while (SQLCODE == 0){

      EXEC SQL FETCH NEXT teachercursor  

               INTO :ctno,

                    :ctname:ctnamenull,

                    :ctsex:ctsexnull,

                    :ctage:ctagenull,

                    :ctdept:ctdeptnull;

      if (SQLCODE == 0)

      {

          printf("%s",ctno);

          if (ctnamenull==0) printf(" %s",ctname);

          else printf(" null");

          if (ctsexnull==0) printf("  %s",ctsex);

          else printf(" null");

          if (ctagenull==0) printf("   %3.0f",ctage);

          else printf(" null");

          if (ctdeptnull==0) printf("  %s\n",ctdept);

          else printf(" null\n");

      }

      else

      {

         printf("ERROR: fetch %d\n",SQLCODE);

      }

    }

    printf("\n");

    EXEC SQL close teachercursor;

    return (0);

}

附件：main.sqc完整代码

int create_student_table();
int system_tables_reset();
int system_tables_reset_2();
int create_teacher_table();
int create_sc_table();
int create_course_table();
int insert_rows_into_student_table();
int insert_rows_into_sc_table();
int insert_rows_into_course_table();
int current_of_delete_for_student();
int current_of_delete_for_course();
int current_of_delete_for_sc();
int sel_student_total_grade_by_sno();
int using_cursor_to_total_s_sc();
int using_cursor_to_total_c_sc();
int using_cursor_to_total_ty();
int current_of_update_for_student();
int current_of_update_for_course();
int current_of_update_for_sc();
int sel_student_by_sno();
int using_cursor_to_list_s_sc_c();
int using_cursor_to_list_table_names();
int using_cursor_to_list_course();
int using_cursor_to_list_sc();
int using_cursor_to_list_student();
int dynamic_exec_sql_command();
int check_username_password();
void ErrorHandler(void);
void pause();

#include <stdlib.h>
#include <stddef.h>  // standard C run-time header
#include <stdio.h>   // standard C run-time header
#include "gcutil.h"  // utility header

struct student
{ int   xh;
  char  xm[9];
  int   cj;
  char  dj[10];
} stu[30];

main(int argc, char** argv,char** envp)
{
  int num=0,nRet;
  char fu[2];

  EXEC SQL BEGIN DECLARE SECTION;
    // for CONNECT TO
    char szServerDatabase[(SQLID_MAX * 2)+2] = "";
    char szLoginPassword[(SQLID_MAX * 2)+2] = "";
  EXEC SQL END DECLARE SECTION;

  // install Embedded SQL for C error handler
  EXEC SQL WHENEVER SQLERROR CALL ErrorHandler();
  // set Embedded SQL for C options
  EXEC SQL SET OPTION LOGINTIME 10;
  EXEC SQL SET OPTION QUERYTIME 100;
  // display logo
  printf("Sample Embedded SQL for C application\n");
  // get info for CONNECT TO statement
  nRet = GetConnectToInfo(argc, argv,
     szServerDatabase,
     szLoginPassword);
   if (!nRet)
   {
     return (1);
   }
  // attempt connection to SQL Server
  EXEC SQL CONNECT TO :szServerDatabase
     USER :szLoginPassword;
  if (SQLCODE == 0)
  {
     printf("Connection to SQL Server established\n");
  }
  else
  {
     // problem connecting to SQL Server
     printf("ERROR: Connection to SQL Server failed\n");
     return (1);
  }
  if (check_username_password()==0){
    for(;;){
      printf("Please select one function to execute:\n\n");
      printf("  0--exit.\n");
      printf("  1--创建学生表     7--修改学生记录   d--按学号查学生     i--统计某学生成绩 \n");
      printf("  2--创建课程表     8--修改课程记录   e--显示学生记录     j--学生成绩统计表 \n");
      printf("  3--创建成绩表     9--修改成绩记录   f--显示课程记录     k--课程成绩统计表 \n");
      printf("  4--添加学生记录   a--删除学生记录   g--显示成绩记录     l--通用统计功能   \n");
      printf("  5--添加课程记录   b--删除课程记录   h--学生课程成绩表   m--数据库用户表名 \n");
      printf("  6--添加成绩记录   c--删除成绩记录                       n--动态执行SQL命令\n");
      printf("  o--添加教师记录   r--系统功能重置   s--系统功能重置2 \n");
      printf("\n");
      fu[0]='0';
      scanf("%s",&fu);
      if (fu[0]=='0') exit(0);
      if (fu[0]=='1') create_student_table();
      if (fu[0]=='2') create_course_table();
      if (fu[0]=='3') create_sc_table();
      if (fu[0]=='4') insert_rows_into_student_table();
      if (fu[0]=='5') insert_rows_into_course_table();
      if (fu[0]=='6') insert_rows_into_sc_table();
      if (fu[0]=='7') current_of_update_for_student();
      if (fu[0]=='8') current_of_update_for_course();
      if (fu[0]=='9') current_of_update_for_sc();
      if (fu[0]=='a') current_of_delete_for_student();
      if (fu[0]=='b') current_of_delete_for_course();
      if (fu[0]=='c') current_of_delete_for_sc();
      if (fu[0]=='d') sel_student_by_sno();
      if (fu[0]=='e') using_cursor_to_list_student();
      if (fu[0]=='f') using_cursor_to_list_course();
      if (fu[0]=='g') using_cursor_to_list_sc();
      if (fu[0]=='h') using_cursor_to_list_s_sc_c();
      if (fu[0]=='i') sel_student_total_grade_by_sno();
      if (fu[0]=='j') using_cursor_to_total_s_sc();
      if (fu[0]=='k') using_cursor_to_total_c_sc();
      if (fu[0]=='l') using_cursor_to_total_ty();
      if (fu[0]=='m') using_cursor_to_list_table_names();
      if (fu[0]=='n') dynamic_exec_sql_command();
      if (fu[0]=='o') create_teacher_table();
      if (fu[0]=='r') system_tables_reset();
      if (fu[0]=='s') system_tables_reset_2();
      pause();
    }
  }
  else printf("Your name or password is error,you can not be logined in the system!");
  // disconnect from SQL Server
  EXEC SQL DISCONNECT ALL;
  return 0;
}

int sel_student_by_sno()
{
    EXEC SQL BEGIN DECLARE SECTION;
      double isage = 18;
      int  isageind = 0;
      char issex[] = "男";
      int  issexind = 0;
      char isno[] = "95002";
      char isname[] = "xxxxxx";
      int  isnameind = 0;
      char isdept[] = "CS";
      int  isdeptind = 0;
    EXEC SQL END DECLARE SECTION;
    printf("Please input sno to be selected:");
    scanf("%s",isno);
    EXEC SQL select sno,sname,sage,ssex,sdept
             into :isno,:isname:isnameind,:isage :isageind,
                  :issex :issexind,:isdept :isdeptind
             from student
             where sno=:isno;
    if (SQLCODE == 0)
    {
        printf("Success to select:!%d\n\n",SQLCODE);
        printf("sno  sname ssex  sage sdept \n");
        printf("%s",isno);
        if (isnameind==0) printf("  %s",isname);
        else printf("  null");
        if (issexind==0) printf("  %s",issex);
        else printf("  null");
        if (isageind==0) printf("  %3.0f",isage);
        else printf("  null");
        if (isdeptind==0) printf("  %s\n",isdept);
        else printf("  null\n");
    }
    else
    {
        // problem connecting to SQL Server
        printf("ERROR: Select student by sno.%d\n",SQLCODE);
    }
    return (0);
}


//1、模拟create_student_table()实现创建Teacher表。即实现create_teacher_table()子程序的功能。
int create_teacher_table();
int create_teacher_table()//已改
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects
         WHERE (xtype = 'U' and name='teacher');
    if (SQLCODE == 0||strcmp(tname,"teacher")==0)
    {
       printf("The teacher 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 teacher;
          if (SQLCODE == 0)
          {
             printf("Drop table teacher successfully!%d\n\n",SQLCODE);
          }
          else
          {
             printf("ERROR: drop table teacher %d\n\n",SQLCODE);
          }
       }
       else return -1;
    }
    EXEC SQL CREATE TABLE teacher (
        tno char(5) NOT null primary key,
        ttname char(6) null ,
    tsex char(2) null ,
    tage int null ,
    tdept char(2) null) ;
    if (SQLCODE == 0)
    {
       printf("Success to create table teacher!%d\n\n",SQLCODE);
    }
    else
    {
       printf("ERROR: create table teacher %d\n",SQLCODE);
    }
    EXEC SQL begin transaction
       insert into teacher values("t5001", "李斌", "男",16, "CS")
       insert into teacher values("t5002", "赵霞", "女",18, "IS")
       insert into teacher values("t5003", "周淘", "男",17, "CS")
       insert into teacher values("t5004", "钱乐", "女",18, "IS")
       insert into teacher values("t5005", "孙力", "男",16, "MA")
       commit transaction;
    if (SQLCODE == 0)
    {
       printf("Success to insert rows to teacher table!%d\n\n",SQLCODE);
    }
    else
    {
       printf("ERROR: insert rows %d\n\n",SQLCODE);
    }
    return(0);
}

//2、模拟insert_rows_into_student_table()实现对Teacher表的记录添加。即实现insert_rows_into_teacher_table()子程序的功能。
int insert_rows_into_teacher_table()//已改
{
    EXEC SQL BEGIN DECLARE SECTION;
    int itage=18;
    int itageind=0;
    char itname[]="xxxxxx";
    int itnameind=0;
    char itsex[]="男";
    int itsexind=0;
    char itno[]="t5001";
    char itdept[]="CS";
    int itdeptind=0;
    EXEC SQL END DECLARE SECTION;
    char yn[2];
    while(1)
    {
      printf("Please input tno(eg:t5001):");
      scanf("%s",itno);
      printf("Please input name(eg:XXXX):");
      scanf("%s",itname);
      printf("Please input name indicator(<0 to set null):");
      scanf("%d",&itnameind);
      printf("Please input age(eg:18):");
      scanf("%d",&itage);
      printf("Please input age indicator(<0 to set null):");
      scanf("%d",&itageind);
      printf("Please input sex(eg:男):");
      scanf("%s",itsex);
      printf("Please input sex indicator(<0 to set null):");
      scanf("%d",&itsexind);
      printf("Please input dept(eg:CS、IS、MA...):");
      scanf("%s",itdept);
      printf("Please input dept indicator(<0 to set null):");
      scanf("%d",&itdeptind);
      EXEC SQL insert into teacher(tno,tage,tsex,ttname,tdept)
                values(:itno,:itage:itageind,:itsex:itsexind,
                       :itname:itnameind,:itdept:itdeptind);
      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);
}

//3、模拟current_of_update_for_student()实现对Teacher表的记录修改。即实现current_of_update_for_teacher()子程序的功能。
int current_of_update_for_teacher()//已改
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char deptname[3];
      char htno[6];
      char htname[7];
      char htsex[3];
      char htdept[3];
      float htage;
      int ihtdept=0;
      int ihtname=0;
      int ihtsex=0;
      int ihtage=0;
      float itage = 38;
      int   itageind = 0;
      char  itsex[3] = "男";
      int   itsexind = 0;
      char  itname[7] = "xxxxxx";
      int   itnameind = 0;
      char  itdept[3] = "CS";
      int   itdeptind = 0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_BROWSE;
    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 tx2 CURSOR FOR
        SELECT tno,ttname,tsex,tage,tdept
        FROM teacher
        where tdept like :deptname
        for update of tname,tsex,tage,tdept;
    EXEC SQL OPEN tx2;
    while( SQLCODE == 0)
    {
        EXEC SQL FETCH tx2 INTO :htno,:htname:ihtname,:htsex:ihtsex,
               :htage:ihtage,:htdept:ihtdept;
        if (SQLCODE!=0) continue;
        printf( "%s\n", "tno  tname  tsex  tage  tdept");
        printf("%s",htno);
        if (ihtname==0) printf(" %s",htname);
        else printf(" null");
        if (ihtsex==0) printf("  %s",htsex);
        else printf(" null");
        if (ihtage==0) printf("   %3.0f",htage);
        else printf(" null");
        if (ihtdept==0) printf("  %s\n",htdept);
        else printf(" null\n");
        printf("UPDATE ?(y/n/0,y--yes,n--no,0--exit)");
        scanf("%s",&yn);
        if (yn[0]=='y' || yn[0]=='Y')
        {
           printf("Please input new name(eg:XXXX):");
           scanf("%s",itname);
           printf("Please input name indicator(<0 to set null,9 no change):");
           scanf("%d",&itnameind);
           if (itnameind==9) {
               if (ihtname<0) itnameind=-1;
               else strcpy(itname,htname);
           }
           printf("Please input new age(eg:18):");
           scanf("%f",&itage);
           printf("Please input age indicator(<0 to set null,9 no change):");
           scanf("%d",&itageind);
           if (itageind==9) {
               if (ihtage<0) itageind=-1;
               else itage=htage;
           }
           printf("Please input new sex(eg:男):");
           scanf("%s",itsex);
           printf("Please input sex indicator(<0 to set null,9 no change):");
           scanf("%d",&itsexind);
           if (itsexind==9) {
               if (ihtsex<0) itsexind=-1;
               else strcpy(itsex,htsex);
           }
           printf("Please input new dept(eg:CS、IS、MA...):");
           scanf("%s",itdept);
           printf("Please input dept indicator(<0 to set null,9 no change):");
           scanf("%d",&itdeptind);
           if (itdeptind==9) {
               if (ihtdept<0) itdeptind=-1;
               else strcpy(itdept,htdept);
           }
           EXEC SQL UPDATE teacher set tage=:itage:itageind,
                ttname=:itname:itnameind,tsex=:itsex:itsexind,
                tdept=:itdept:itdeptind where current of tx2;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE tx2;
    return (0);
}


//4、模拟current_of_delete_for_student()实现对Teacher表的记录删除。即实现current_of_delete_for_teacher()子程序的功能。

int current_of_delete_for_teacher()//已改
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char deptname[3];
      char htno[6];
      char htname[7];
      char htsex[3];
      float htage;
      int ihtdept=0;
      int ihtname=0;
      int ihtsex=0;
      int ihtage=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_STANDARD;
    printf("Please input deptname(CS、IS、MA...,**--All):\n");
    scanf("%s",deptname);
    if (strcmp(deptname,"*")==0||strcmp(deptname,"**")==0) strcpy(deptname,"%");
    EXEC SQL DECLARE tx CURSOR FOR
        SELECT tno,ttname,tsex,tage,tdept
        FROM teacher
        where tdept like :deptname
        for update of ttname,tsex,tage,tdept;
    EXEC SQL OPEN tx;
    while( SQLCODE == 0)
    {
        EXEC SQL FETCH tx INTO :htno,:htname:ihtname,
              :htsex:ihtsex,:htage:ihtage,:deptname:ihtdept;
        if (SQLCODE!=0) continue;
        printf( "%s %5s %s %s  %s\n", "sno  ","tname","tsex","tage","tdept");
        printf(" %s",htno);
        if (ihtname==0) printf(" %s",htname);
        else printf(" null");
        if (ihtsex==0) printf(" %s",htsex);
        else printf(" null");
        if (ihtage==0) printf(" %f",htage);
        else printf(" null");
        if (ihtdept==0) printf(" %s\n",deptname);
        else printf(" null\n");
        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 teacher where current of tx;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE tx;
    return (0);
}


//5、模拟using_cursor_to_list_student()实现对Teacher表的记录查询。即实现using_cursor_to_list_teacher()子程序的功能。
int using_cursor_to_list_teacher()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char ctno[6];
      char ctname[7];
      char ctsex[3];
      double ctage;
      char ctdept[3];
      int ctnamenull=0;
      int ctsexnull=0;
      int ctagenull=0;
      int ctdeptnull=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare teachercursor cursor
         for select *
             from teacher
             order by tno
         for read only;
    EXEC SQL open teachercursor;
    if (SQLCODE == 0)
    {
        printf("Open successfully!%d\n",SQLCODE);
    }
    else
    {
        printf("ERROR: open %d\n",SQLCODE);
    }
    printf("\n");
    printf("tno tname tsex tage tdept \n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT teachercursor  
               INTO :ctno,
                    :ctname:ctnamenull,
                    :ctsex:ctsexnull,
                    :ctage:ctagenull,
                    :ctdept:ctdeptnull;
      if (SQLCODE == 0)
      {
          printf("%s",ctno);
          if (ctnamenull==0) printf(" %s",ctname);
          else printf(" null");
          if (ctsexnull==0) printf("  %s",ctsex);
          else printf(" null");
          if (ctagenull==0) printf("   %3.0f",ctage);
          else printf(" null");
          if (ctdeptnull==0) printf("  %s\n",ctdept);
          else printf(" null\n");
      }
      else
      {
         printf("ERROR: fetch %d\n",SQLCODE);
      }
    }
    printf("\n");
    EXEC SQL close teachercursor;
    return (0);
}


int sel_student_total_grade_by_sno()
{
    EXEC SQL BEGIN DECLARE SECTION;
      double isum = 18;
      int    icnt = 18;
      double iavg = 18;
      double imin = 18;
      double imax = 18;
      int isumi = 0;
      int icnti = 0;
      int iavgi = 0;
      int imini = 0;
      int imaxi = 0;
      char isno[] = "95002";
      char isname[] = "xxxxxx";
      int  isnameind = 0;
    EXEC SQL END DECLARE SECTION;
    printf("Please input sno to be selected:");
    scanf("%s",isno);
    EXEC SQL select sname,count(grade),sum(grade),avg(grade),MIN(grade),MAX(grade)
          into :isname:isnameind,:icnt:icnti,:isum:isumi,
               :iavg:iavgi,:imin:imini,:imax:imaxi           from student,sc
          where student.sno=sc.sno and student.sno=:isno
          group by sname;
    if (SQLCODE == 0)
    {
        printf("Success to total grade:!%d\n\n",SQLCODE);
        printf("sno  sname  count sum  avg  min  max \n");
        printf("%s",isno);
        if (isnameind==0) printf("  %s",isname);
        else printf("  null");
        if (icnti==0) printf("  %d",icnt);
        else printf("  null");
        if (isumi==0) printf("  %3.0f",isum);
        else printf("  null");
        if (iavgi==0) printf("  %3.0f",iavg);
        else printf("  null");
        if (imini==0) printf("  %3.0f",imin);
        else printf("  null");
        if (imaxi==0) printf("  %3.0f\n",imax);
        else printf("  null\n");
    }
    else
    {
        printf("ERROR: total grade by sno %d\n",SQLCODE);
    }
    return (0);
}

int insert_rows_into_student_table()
{
    EXEC SQL BEGIN DECLARE SECTION;
      int  isage = 18;
      int  isageind = 0;
      char issex[] = "男";
      int  issexind = 0;
      char isno[] = "95002";
      char isname[] = "xxxxxx";
      int  isnameind = 0;
      char isdept[] = "CS";
      int  isdeptind = 0;
    EXEC SQL END DECLARE SECTION;
    char yn[2];
    while(1){
      printf("Please input sno(eg:95001):");
      scanf("%s",isno);
      printf("Please input name(eg:XXXX):");
      scanf("%s",isname);
      printf("Please input name indicator(<0 to set null):");
      scanf("%d",&isnameind);
      printf("Please input age(eg:18):");
      scanf("%d",&isage);
      printf("Please input age indicator(<0 to set null):");
      scanf("%d",&isageind);
      printf("Please input sex(eg:男):");
      scanf("%s",issex);
      printf("Please input sex indicator(<0 to set null):");
      scanf("%d",&issexind);
      printf("Please input dept(eg:CS、IS、MA...):");
      scanf("%s",isdept);
      printf("Please input dept indicator(<0 to set null):");
      scanf("%d",&isdeptind);
      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);
}

int insert_rows_into_course_table()
{
    EXEC SQL BEGIN DECLARE SECTION;
      int  iccredit = 5;
      int  iccreditind = 0;
      char icpno[] = "1 ";
      int  icpnoind = 0;
      char icno[] = "1 ";
      char icname[] = "xxxxxxxxxx";
      int  icnameind = 0;
    EXEC SQL END DECLARE SECTION;
    char yn[2];
    while(1){
      printf("Please input cno(eg:1,2,3,...):");
      scanf("%s",icno);
      printf("Please input course name(eg:XXXXXXXXXX):");
      scanf("%s",icname);
      printf("Please input cname indicator(<0 to set null):");
      scanf("%d",&icnameind);
      printf("Please input cpno(eg:1,2,3...):");
      scanf("%s",icpno);
      printf("Please input cpno indicator(<0 to set null):");
      scanf("%d",&icpnoind);
      printf("Please input ccredit(eg:3):");
      scanf("%d",&iccredit);
      printf("Please input ccredit indicator(<0 to set null):");
      scanf("%d",&iccreditind);
      EXEC SQL insert into course(cno,cname,cpno,ccredit)
                values(:icno,:icname:icnameind,:icpno:icpnoind,
                       :iccredit:iccreditind);
      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);
}

int insert_rows_into_sc_table()
{
    EXEC SQL BEGIN DECLARE SECTION;
      int igrade = 80;
      int igradeind = 0;
      char icno[] = "1 ";
      char isno[] = "95001";
    EXEC SQL END DECLARE SECTION;
    char yn[2];
    while(1){
      printf("Please input sno(eg:95001,...):");
      scanf("%s",isno);
      printf("Please input cno(eg:1,2,3,...):");
      scanf("%s",icno);
      printf("Please input grade(eg:3):");
      scanf("%d",&igrade);
      printf("Please input grade indicator(<0 to set null):");
      scanf("%d",&igradeind);
      EXEC SQL insert into sc(sno,cno,grade)
                values(:isno,:icno,:igrade:igradeind);
      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);
}

int current_of_update_for_student()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char deptname[3];
      char hsno[6];
      char hsname[7];
      char hssex[3];
      char hsdept[3];
      float hsage;
      int ihsdept=0;
      int ihsname=0;
      int ihssex=0;
      int ihsage=0;
      float isage = 38;
      int   isageind = 0;
      char  issex[3] = "男";
      int   issexind = 0;
      char  isname[7] = "xxxxxx";
      int   isnameind = 0;
      char  isdept[3] = "CS";
      int   isdeptind = 0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_BROWSE;
    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 :deptname
        for 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("%s",hsno);
        if (ihsname==0) printf(" %s",hsname);
        else printf(" null");
        if (ihssex==0) printf("  %s",hssex);
        else printf(" null");
        if (ihsage==0) printf("   %3.0f",hsage);
        else printf(" null");
        if (ihsdept==0) printf("  %s\n",hsdept);
        else printf(" null\n");
        printf("UPDATE ?(y/n/0,y--yes,n--no,0--exit)");
        scanf("%s",&yn);
        if (yn[0]=='y' || yn[0]=='Y')
        {
           printf("Please input new name(eg:XXXX):");
           scanf("%s",isname);
           printf("Please input name indicator(<0 to set null,9 no change):");
           scanf("%d",&isnameind);
           if (isnameind==9) {
               if (ihsname<0) isnameind=-1;
               else strcpy(isname,hsname);
           }
           printf("Please input new age(eg:18):");
           scanf("%f",&isage);
           printf("Please input age indicator(<0 to set null,9 no change):");
           scanf("%d",&isageind);
           if (isageind==9) {
               if (ihsage<0) isageind=-1;
               else isage=hsage;
           }
           printf("Please input new sex(eg:男):");
           scanf("%s",issex);
           printf("Please input sex indicator(<0 to set null,9 no change):");
           scanf("%d",&issexind);
           if (issexind==9) {
               if (ihssex<0) issexind=-1;
               else strcpy(issex,hssex);
           }
           printf("Please input new dept(eg:CS、IS、MA...):");
           scanf("%s",isdept);
           printf("Please input dept indicator(<0 to set null,9 no change):");
           scanf("%d",&isdeptind);
           if (isdeptind==9) {
               if (ihsdept<0) isdeptind=-1;
               else strcpy(isdept,hsdept);
           }
           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);
}

int current_of_update_for_sc()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char  ssno[6];
      char  hsno[6];
      char  hcno[2];
      float hgrade;
      int   ihgrade=0;
      float igrade = 88;
      int   igradeind = 0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_BROWSE;
    printf("Please input sno to be updated(95001...,*****--All):\n");
    scanf("%s",ssno);
    if (strcmp(ssno,"*****")==0) strcpy(ssno,"%");
    EXEC SQL DECLARE scx2 CURSOR FOR
        SELECT sno,cno,grade
        FROM sc
        where sno like :ssno
        for update of grade;
    EXEC SQL OPEN scx2;
    while( SQLCODE == 0)
    {
        EXEC SQL FETCH scx2 INTO :hsno,:hcno,:hgrade:ihgrade;
        if (SQLCODE!=0) continue;
        printf( "%s\n", "sno  cno   grade");
        printf("%s  ",hsno);
        printf("%s  ",hcno);
        if (ihgrade==0) printf(" %f\n",hgrade);
        else printf(" null\n");
        printf("UPDATE grade?(y/n/0,y--yes,n--no,0--exit)");
        scanf("%s",&yn);
        if (yn[0]=='y' || yn[0]=='Y')
        {
           printf("Please input new grade(eg:88):");
           scanf("%f",&igrade);
           printf("Please input grade indicator(<0 to set null,9 no change):");
           scanf("%d",&igradeind);
           if (igradeind==9) {
               if (ihgrade<0) igradeind=-1;
               else igrade=hgrade;
           }
           EXEC SQL UPDATE sc set grade=:igrade:igradeind where current of scx2;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE scx2;
    return (0);
}

int current_of_delete_for_sc()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char  ssno[6];
      char  hsno[6];
      char  hcno[2];
      float hgrade;
      int   ihgrade=0;
      float igrade = 88;
      int   igradeind = 0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_STANDARD;
    printf("Please input sno to be deleted(95001...,*****--All):\n");
    scanf("%s",ssno);
    if (strcmp(ssno,"*****")==0) strcpy(ssno,"%");
    EXEC SQL DECLARE scdx2 CURSOR FOR
        SELECT sno,cno,grade
        FROM sc
        where sno like :ssno
        for update of grade;
    EXEC SQL OPEN scdx2;
    while( SQLCODE == 0)
    {
        EXEC SQL FETCH scdx2 INTO :hsno,:hcno,:hgrade:ihgrade;
        if (SQLCODE!=0) continue;
        printf( "%s\n", "sno  cno   grade");
        printf("%s  ",hsno);
        printf("%s  ",hcno);
        if (ihgrade==0) printf(" %f\n",hgrade);
        else printf(" null\n");
        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 sc where current of scdx2;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE scdx2;
    return (0);
}

int current_of_update_for_course()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char ccno[3];
      char hcno[3];
      char hcname[11];
      int  ihcname=0;
      char hcpno[3];
      int  ihcpno=0;
      int  hccredit;
      int  ihccredit=0;
      char icname[11];
      int  icnameind=0;
      char icpno[3];
      int  icpnoind=0;
      int  iccredit;
      int  iccreditind=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_BROWSE;
    printf("Please input cno to be updated(1、2...,**--All):\n");
    scanf("%s",ccno);
    if (strcmp(ccno,"**")==0) strcpy(ccno,"%");
    EXEC SQL DECLARE coursex2 CURSOR FOR
        SELECT cno,cname,cpno,ccredit  
        FROM course
        where cno like :ccno
        for update of cname,cpno,ccredit;
    EXEC SQL OPEN coursex2;
    while( SQLCODE == 0)
    {
        EXEC SQL FETCH coursex2 INTO :hcno,:hcname:ihcname,:hcpno:ihcpno,
                                :hccredit:ihccredit;
        if (SQLCODE!=0) continue;
        printf( "%s\n", "cno  cname      cpno  ccredit");
        printf("%s   ",hcno);
        if (ihcname==0) printf("  %s ",hcname);
        else printf("   null ");
        if (ihcpno==0) printf(" %s",hcpno);
        else printf(" null");
        if (ihccredit==0) printf(" %d\n",hccredit);
        else printf(" null\n");

        printf("UPDATE ?(y/n/0,y--yes,n--no,0--exit)");
        scanf("%s",&yn);
        if (yn[0]=='y' || yn[0]=='Y')
        {
           printf("Please input new cname(eg:数据库):");
           scanf("%s",icname);
           printf("Please input cname indicator(<0 to set null,9 no change):");
           scanf("%d",&icnameind);
           if (icnameind==9) {
               if (ihcname<0) icnameind=-1;
               else strcpy(icname,hcname);
           }
           printf("Please input new cpno(eg:1,2,...):");
           scanf("%s",icpno);
           printf("Please input cpno indicator(<0 to set null,9 no change):");
           scanf("%d",&icpnoind);
           if (icpnoind==9) {
               if (ihcpno<0) icpnoind=-1;
               else strcpy(icpno,hcpno);
           }
           printf("Please input new ccredit(eg:3):");
           scanf("%d",&iccredit);
           printf("Please input ccredit indicator(<0 to set null,9 no change):");
           scanf("%d",&iccreditind);
           if (iccreditind==9) {
               if (ihccredit<0) iccreditind=-1;
               else iccredit=hccredit;
           }

           EXEC SQL UPDATE course set cname=:icname:icnameind,
                         cpno=:icpno:icpnoind,ccredit=:iccredit:iccreditind  
                where current of coursex2;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE coursex2;
    return (0);
}

int current_of_delete_for_course()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char ccno[3];
      char hcno[3];
      char hcname[11];
      int ihcname=0;
      char hcpno[3];
      int ihcpno=0;
      int hccredit;
      int ihccredit=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_STANDARD;
    printf("Please input cno to be deleted(1、2...,**--All):\n");
    scanf("%s",ccno);
    if (strcmp(ccno,"**")==0) strcpy(ccno,"%");
    EXEC SQL DECLARE coursedx2 CURSOR FOR
        SELECT cno,cname,cpno,ccredit  
        FROM course
        where cno like :ccno
        for update of cname,cpno,ccredit;
    EXEC SQL OPEN coursedx2;
    while( SQLCODE == 0)
    {
        EXEC SQL FETCH coursedx2 INTO :hcno,:hcname:ihcname,
                                :hcpno:ihcpno,:hccredit:ihccredit;
        if (SQLCODE!=0) continue;
        printf( "%s\n", "cno  cname      cpno  ccredit");
        printf("%s   ",hcno);
        if (ihcname==0) printf("  %s ",hcname);
        else printf("   null ");
        if (ihcpno==0) printf(" %s",hcpno);
        else printf(" null");
        if (ihccredit==0) printf(" %d\n",hccredit);
        else printf(" null\n");
        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 course where current of coursedx2;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE coursedx2;
    return (0);
}

int current_of_delete_for_student()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char deptname[3];
      char hsno[6];
      char hsname[7];
      char hssex[3];
      float hsage;
      int ihsdept=0;
      int ihsname=0;
      int ihssex=0;
      int ihsage=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SET CURSORTYPE CUR_STANDARD;
    printf("Please input deptname(CS、IS、MA...,**--All):\n");
    scanf("%s",deptname);
    if (strcmp(deptname,"*")==0||strcmp(deptname,"**")==0) strcpy(deptname,"%");
    EXEC SQL DECLARE sx CURSOR FOR
        SELECT sno,sname,ssex,sage,sdept
        FROM student
        where sdept like :deptname
        for 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,:deptname:ihsdept;
        if (SQLCODE!=0) continue;
        printf( "%s %5s %s %s  %s\n", "sno  ","sname","ssex","sage","sdept");
        printf(" %s",hsno);
        if (ihsname==0) printf(" %s",hsname);
        else printf(" null");
        if (ihssex==0) printf(" %s",hssex);
        else printf(" null");
        if (ihsage==0) printf(" %f",hsage);
        else printf(" null");
        if (ihsdept==0) printf(" %s\n",deptname);
        else printf(" null\n");
        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 student where current of sx;
        };
    if (yn[0]=='0') break;
    };
    EXEC SQL CLOSE sx;
    return (0);
}

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();
    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、UPDATE、INSERT command.\n");
       return(-1);
    }
    printf("%s\n",cmd);
      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);
}

int using_cursor_to_list_student()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char csno[6];
      char csname[7];
      char cssex[3];
      double csage;
      char csdept[3];
      int csnamenull=0;
      int cssexnull=0;
      int csagenull=0;
      int csdeptnull=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare studentcursor cursor
         for 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("%s",csno);
          if (csnamenull==0) printf(" %s",csname);
          else printf(" null");
          if (cssexnull==0) printf("  %s",cssex);
          else printf(" null");
          if (csagenull==0) printf("   %3.0f",csage);
          else printf(" null");
          if (csdeptnull==0) printf("  %s\n",csdept);
          else printf(" null\n");
      }
      else
      {
         printf("ERROR: fetch %d\n",SQLCODE);
      }
    }
    printf("\n");
    EXEC SQL close studentcursor;
    return (0);
}

int using_cursor_to_list_course()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char ccno[2];
      char ccname[11];
      char ccpno[2];
      double cccredit;
      int ccnamenull=0;
      int ccpnonull=0;
      int cccreditnull=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare coursecursor cursor
         for select *
             from course
             order by cno
         for read only;
    EXEC SQL open coursecursor;
    if (SQLCODE == 0)
    {
        printf("Open successfully!%d\n",SQLCODE);
    }
    else
    {
        printf("ERROR: open %d\n",SQLCODE);
    }
    printf("\n");
    printf("cno cname cpno ccredit \n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT coursecursor  
               INTO :ccno,
                    :ccname:ccnamenull,
                    :ccpno:ccpnonull,
                    :cccredit:cccreditnull;
      if (SQLCODE == 0)
      {
          printf("%s",ccno);
          if (ccnamenull==0) printf(" %s",ccname);
          else printf(" null");
          if (ccpnonull==0) printf("  %s",ccpno);
          else printf(" null");
          if (cccreditnull==0) printf("   %3.0f\n",cccredit);
          else printf(" null\n");
      }
    }
    printf("\n");
    EXEC SQL close coursecursor;
    return (0);
}

int using_cursor_to_list_sc()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char csno[6];
      char ccno[2];
      double cgrade;
      int cgradenull=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare sccursor cursor
         for select *
             from sc
             order by sno,cno
         for read only;
    EXEC SQL open sccursor;
    if (SQLCODE == 0)
    {
        printf("Open successfully!%d\n",SQLCODE);
    }
    else
    {
        printf("ERROR: open %d\n",SQLCODE);
    }
    printf("\n");
    printf("sno   cno  grade \n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT sccursor  
               INTO :csno,
                    :ccno,
                    :cgrade:cgradenull;
      if (SQLCODE == 0)
      {
          printf("%s",csno);
          printf("  %s",ccno);
          if (cgradenull==0) printf("  %5.0f\n",cgrade);
          else printf(" null\n");
      }
    }
    printf("\n");
    EXEC SQL close sccursor;
    return (0);
}

int using_cursor_to_list_s_sc_c()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char csno[6];
      char csname[7];
      char cssex[3];
      double csage;
      char csdept[3];
      char ccno[2];
      char ccname[11];
      double cgrade;
      int ccnamenull=0;
      int cgradenull=0;
      int csnamenull=0;
      int cssexnull=0;
      int csagenull=0;
      int csdeptnull=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare sscccursor cursor
         for select student.sno,sname,ssex,sage,sdept,sc.cno,cname,grade
             from student,sc,course
             where student.sno=sc.sno and sc.cno=course.cno
             order by student.sno
         for read only;
    EXEC SQL open sscccursor;
    if (SQLCODE == 0)
    {
       printf("Open successfully!%d\n",SQLCODE);
    }
    else
    {
       printf("ERROR: open %d\n",SQLCODE);
    }
    printf("\n");
    printf("sno  sname ssex  sage sdept cno  cname    grade \n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT sscccursor  
               INTO :csno,
                :csname:csnamenull,
            :cssex:cssexnull,
            :csage:csagenull,
            :csdept:csdeptnull,
            :ccno,
                    :ccname:ccnamenull,
            :cgrade:cgradenull;
      if (SQLCODE == 0)
      {
          printf("%s",csno);
          if (csnamenull==0) printf("  %s",csname);
          else printf("  null");
          if (cssexnull==0) printf("  %s",cssex);
          else printf("  null");
          if (csagenull==0) printf("  %3.0f",csage);
          else printf("  null");
          if (csdeptnull==0) printf("  %s",csdept);
          else printf("  null");
          printf("  %s",ccno);
          if (ccnamenull==0) printf("  %s",ccname);
          else printf("  null");
          if (cgradenull==0) printf("  %3.0f\n",cgrade);
          else printf("  null\n");
      }
    }
    printf("\n");
    EXEC SQL close sscccursor;
    return (0);
}

int using_cursor_to_total_s_sc()
{
    EXEC SQL BEGIN DECLARE SECTION;
      double isum = 18;
      int icnt = 18;
      double iavg = 18;
      double imin = 18;
      double imax = 18;
      int isumi = 0;
      int icnti = 0;
      int iavgi = 0;
      int imini = 0;
      int imaxi = 0;
      char isno[]="95001";
      char isname[] = "xxxxxx";
      int isnameind = 0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare totalssc cursor
         for select student.sno,sname,count(grade),sum(grade),
                 avg(grade),MIN(grade),MAX(grade)
             from student,sc
             where student.sno=sc.sno
             group 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)
      {
          printf("%s",isno);
          if (isnameind==0) printf("  %s",isname);
          else printf("  null");
          if (icnti==0 ) printf("  %d",icnt);
          else printf("  null");
          if (isumi==0 ) printf("  %3.0f",isum);
          else printf("  null");
          if (iavgi==0 ) printf("  %3.0f",iavg);
          else printf("  null");
          if (imini==0 ) printf("  %3.0f",imin);
          else printf("  null");
          if (imaxi==0 ) printf("  %3.0f\n",imax);
          else printf("  null\n");
      }
      else
      {
          printf("ERROR: end of results %d\n",SQLCODE);
      }
    }
    printf("\n");
    EXEC SQL close totalssc;
    return (0);
}

int using_cursor_to_total_c_sc()
{
    EXEC SQL BEGIN DECLARE SECTION;
      double isum = 18;
      int icnt = 18;
      double iavg = 18;
      double imin = 18;
      double imax = 18;
      int isumi = 0;
      int icnti = 0;
      int iavgi = 0;
      int imini = 0;
      int imaxi = 0;
      char icno[]="1";
      char icname[] = "xxxxxxxxxx";
      int icnameind = 0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare totalcsc cursor
         for select course.cno,cname,count(grade),sum(grade),
                 avg(grade) aa3,MIN(grade),MAX(grade)  
               from course,sc
               where course.cno=sc.cno
               group by course.cno,cname
               for read only;
    EXEC SQL open totalcsc;
    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("cno  cname  count sum  avg  min  max \n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT totalcsc  
               into :icno,:icname:icnameind,:icnt:icnti,:isum:isumi,
                    :iavg:iavgi,:imin:imini,:imax:imaxi;
      if (SQLCODE == 0)
      {
          printf("%s",icno);
          if (icnameind==0) printf("  %s",icname);
          else printf("  null");
          if (icnti==0 ) printf("  %d",icnt);
          else printf("  null");
          if (isumi==0 ) printf("  %3.0f",isum);
          else printf("  null");
          if (iavgi==0 ) printf("  %3.0f",iavg);
          else printf("  null");
          if (imini==0 ) printf("  %3.0f",imin);
          else printf("  null");
          if (imaxi==0 ) printf("  %3.0f\n",imax);
          else printf("  null\n");
      }
      else
      {
          printf("ERROR: end of results %d\n",SQLCODE);
      }
    }
    printf("\n");
    EXEC SQL close totalcsc;
    return (0);
}

int using_cursor_to_total_ty()
{
    char c;
    EXEC SQL BEGIN DECLARE SECTION;
      double icnt = 18;
      int icnti = 0;
      char icno[21];
      int i=0;
      char cmd[150];
    EXEC SQL END DECLARE SECTION;
    printf("Example:   select sc.sno,avg(grade)\n");
    printf("           from student,sc \n");
    printf("           group by sc.sno \n");
    printf("Please input total sql statement according to the example.\n");
    c=getchar();
    gets(cmd);
    if ((int)strstr(cmd,"elect")>=1) i++;
    if ((int)strstr(cmd,"avg")>=1) i++;
    if ((int)strstr(cmd,"count")>=1) i++;
    if ((int)strstr(cmd,"sum")>=1) i++;
    if ((int)strstr(cmd,"min")>=1) i++;
    if ((int)strstr(cmd,"max")>=1) i++;
    if ((int)strstr(cmd,",")>=1) i++;
    if (!(i==2||i==3)) {
       printf("Please input correct sql statement.\n");
       return(-1);      
    }
    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");
    printf("Success to total grade:!%d\n\n",SQLCODE);
    printf("   分组字段名            统计值 \n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT total_ty_cur  
               into :icno,:icnt:icnti;
      if (SQLCODE == 0)
      {
         printf("%s",icno);
         if (icnti==0 ) printf("  %f\n",icnt);
         else printf("  null\n");
      }
      else
      {
         printf("ERROR: end of results %d\n",SQLCODE);
      }
    }
    printf("\n");
    EXEC SQL close total_ty_cur;
    return (0);
}

int using_cursor_to_list_table_names()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char ctname[21];
      int i=0;
    EXEC SQL END DECLARE SECTION;
    EXEC SQL declare tnamecursor cursor
         for SELECT name
             FROM sysobjects
             WHERE (xtype = 'U')
         for read only;
    EXEC SQL open tnamecursor;
    if (SQLCODE == 0)
    {
       printf("Open successfully!%d\n",SQLCODE);
    }
    else
    {
       printf("ERROR: open %d\n",SQLCODE);
    }
    printf("\n");
    printf("There are below table names.\n");
    while (SQLCODE == 0){
      EXEC SQL FETCH NEXT tnamecursor  
               INTO :ctname;
      if (SQLCODE == 0)
      {
          i++;
          if (i%3==0) printf("  %s\n",ctname);
          else  printf("  %s",ctname);
      }
    }
    printf("\n");
    EXEC SQL close tnamecursor;
    return (0);
}

int create_student_table()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects
         WHERE (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 ,
    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 transaction
       insert into student values("95001", "李斌", "男",16, "CS")
       insert into student values("95002", "赵霞", "女",18, "IS")
       insert into student values("95003", "周淘", "男",17, "CS")
       insert into student values("95004", "钱乐", "女",18, "IS")
       insert into student values("95005", "孙力", "男",16, "MA")
       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);
}

int system_tables_reset()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects WHERE (xtype = 'U' and name='sc');
    if (SQLCODE == 0||strcmp(tname,"sc")==0)
    {
          EXEC SQL drop table sc;
          if (SQLCODE == 0)
          {
             printf("Drop table sc successfully!%d\n",SQLCODE);
          }
          else
          {
             printf("ERROR: Drop table sc %d\n",SQLCODE);
          }
    }
    EXEC SQL SELECT name into :tname FROM sysobjects WHERE (xtype = 'U' and name='student');
    if (SQLCODE == 0||strcmp(tname,"student")==0)
    {
          EXEC SQL drop table student;
          if (SQLCODE == 0)
          {
             printf("Drop table student successfully!%d\n",SQLCODE);
          }
          else
          {
             printf("ERROR: Drop table student %d\n",SQLCODE);
          }
    }
    EXEC SQL SELECT name into :tname FROM sysobjects WHERE (xtype = 'U' and name='course');
    if (SQLCODE == 0||strcmp(tname,"course")==0)
    {
          EXEC SQL drop table course;
          if (SQLCODE == 0)
          {
             printf("Drop table course successfully!%d\n",SQLCODE);
          }
          else
          {
             printf("ERROR: Drop table course %d\n",SQLCODE);
          }
    }

    EXEC SQL CREATE TABLE student (
        sno char(5) NOT null primary key,
        sname char(6) null ,
    ssex char(2) null ,
    sage int null ,
    sdept char(2) null) ;
    if (SQLCODE == 0)
    {
       printf("Success to create table student!%d\n",SQLCODE);
    }
    else
    {
       printf("ERROR: Create table student %d\n",SQLCODE);
    }
    EXEC SQL begin transaction
       insert into student values("95001", "李斌", "男",16, "CS")
       insert into student values("95002", "赵霞", "女",18, "IS")
       insert into student values("95003", "周淘", "男",17, "CS")
       insert into student values("95004", "钱乐", "女",18, "IS")
       insert into student values("95005", "孙力", "男",16, "MA")
       commit transaction;
    if (SQLCODE == 0)
    {
       printf("Success to insert rows to student table!%d\n",SQLCODE);
    }
    else
    {
       printf("ERROR: Insert rows %d\n",SQLCODE);
    }
    EXEC SQL CREATE TABLE course (
    cno char(1) NOT null primary key,
    cname char(10) null ,
    cpno char(1) null ,
    ccredit int null);
    if (SQLCODE == 0)
    {
        printf("Success to create table course!%d\n",SQLCODE);
    }
    else
    {
        printf("ERROR: Create table course %d\n",SQLCODE);
    }
    EXEC SQL begin transaction
        insert into course values("1", "C语言", "",3)
        insert into course values("2", "数据库", "1",4)
        insert into course values("3", "编译原理", "2",5)
        insert into course values("4", "数据结构", "3",2)
        insert into course values("5", "操作系统", "4",3)
        commit transaction;
    if (SQLCODE == 0)
    {
        printf("Success to insert rows to course table!%d\n",SQLCODE);
    }
    else
    {
        printf("ERROR: Insert rows %d\n",SQLCODE);
    }
    EXEC SQL CREATE TABLE sc (
        sno char(5) NOT null ,
    cno char(1) NOT null ,
    grade int null ,
    primary key(sno,cno),
        foreign key(sno) references student(sno),
        foreign key(cno) references course(cno));
    if (SQLCODE == 0)
    {
       printf("Success to create table sc!%d\n",SQLCODE);
    }
    else
    {
       printf("ERROR: Create table sc %d\n",SQLCODE);
    }
    EXEC SQL begin transaction
        insert into sc values("95001", "1", 66)
        insert into sc values("95001", "2", 66)
        insert into sc values("95001", "3", 66)
        insert into sc values("95002", "2", 0)
        insert into sc values("95002", "3", 97)
        insert into sc values("95002", "4", 0)
        insert into sc values("95003", "1", 88)
        insert into sc values("95003", "2", 68)
        insert into sc values("95003", "3", 88)
        insert into sc values("95004", "2", 76)
        insert into sc values("95004", "3", 76)
        insert into sc values("95005", "1", 87)
        insert into sc(sno,cno) values("95005", "2")
        commit transaction;
    if (SQLCODE == 0)
    {
       printf("Success to insert rows to sc table!%d\n",SQLCODE);
    }
    else
    {
       printf("ERROR: Insert rows %d\n",SQLCODE);
    }
    using_cursor_to_list_student();
    using_cursor_to_list_course();
    using_cursor_to_list_sc();
    return(0);
}


/*
  CREATE/ALTER procedure [dbo].[system_tables_reset] as begin
        drop table sc
        drop table student
        drop table course

       CREATE TABLE student (
        sno char(5) NOT null primary key,
        sname char(6) null ,
        ssex char(2) null ,
        sage int null ,
        sdept char(2) null)
       insert into student values('95001', '李斌', '男',16, 'CS')
       insert into student values('95002', '赵霞', '女',18, 'IS')
       insert into student values('95003', '周淘', '男',17, 'CS')
       insert into student values('95004', '钱乐', '女',18, 'IS')
       insert into student values('95005', '孙力', '男',16, 'MA')  
      CREATE TABLE course (
        cno char(1) NOT null primary key,
        cname char(10) null ,
        cpno char(1) null ,
        ccredit int null)
       insert into course values('1', 'C语言', '',3)
       insert into course values('2', '数据库', '1',4)
       insert into course values('3', '编译原理', '2',5)
       insert into course values('4', '数据结构', '3',2)
       insert into course values('5', '操作系统', '4',3)
      CREATE TABLE sc (
        sno char(5) NOT null ,
        cno char(1) NOT null ,
        grade int null ,
        primary key(sno,cno),
        foreign key(sno) references student(sno),
        foreign key(cno) references course(cno))
        insert into sc values('95001', '1', 66)
        insert into sc values('95001', '2', 66)
        insert into sc values('95001', '3', 66)
        insert into sc values('95002', '2', 0)
        insert into sc values('95002', '3', 97)
        insert into sc values('95002', '4', 0)
        insert into sc values('95003', '1', 88)
        insert into sc values('95003', '2', 68)
        insert into sc values('95003', '3', 88)
        insert into sc values('95004', '2', 76)
        insert into sc values('95004', '3', 76)
        insert into sc values('95005', '1', 87)
        insert into sc(sno,cno) values('95005', '2')         
  end
*/


int system_tables_reset_2()
{
    EXEC SQL execute system_tables_reset;

    using_cursor_to_list_student();
    using_cursor_to_list_course();
    using_cursor_to_list_sc();
    return(0);
}

int system_tables_reset_22()
{
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects WHERE (xtype = 'P' and name='system_tables_reset1');
    if (SQLCODE == 0||strcmp(tname,"system_tables_reset1")==0)
    {
          EXEC SQL drop procedure system_tables_reset1;
          if (SQLCODE == 0)
          {
             printf("Drop procedure system_tables_reset1 successfully!%d\n",SQLCODE);
          }
          else
          {
             printf("ERROR: Drop procedure system_tables_reset1 %d\n",SQLCODE);
          }
    }
    EXEC SQL create procedure system_tables_reset1 as begin
        drop table sc
        drop table student
        drop table course
       CREATE TABLE student (
        sno char(5) NOT null primary key,
        sname char(6) null ,
        ssex char(2) null ,
        sage int null ,
        sdept char(2) null)
       insert into student values("95001", "李斌", "男",16, "CS")
       insert into student values("95002", "赵霞", "女",18, "IS")
       insert into student values("95003", "周淘", "男",17, "CS")
       insert into student values("95004", "钱乐", "女",18, "IS")
       insert into student values("95005", "孙力", "男",16, "MA") end;
    EXEC SQL create procedure system_tables_reset2 as begin
      CREATE TABLE course (
        cno char(1) NOT null primary key,
        cname char(10) null ,
        cpno char(1) null ,
        ccredit int null)
       insert into course values("1", "C语言", "",3)
       insert into course values("2", "数据库", "1",4)
       insert into course values("3", "编译原理", "2",5)
       insert into course values("4", "数据结构", "3",2)
       insert into course values("5", "操作系统", "4",3)  end;
    EXEC SQL create procedure system_tables_reset3 as begin
      CREATE TABLE sc (
        sno char(5) NOT null ,
        cno char(1) NOT null ,
        grade int null ,
        primary key(sno,cno),
        foreign key(sno) references student(sno),
        foreign key(cno) references course(cno))
        insert into sc values("95001", "1", 66)
        insert into sc values("95001", "2", 66)
        insert into sc values("95001", "3", 66)
        insert into sc values("95002", "2", 0)
        insert into sc values("95002", "3", 97)
        insert into sc values("95002", "4", 0)
        insert into sc values("95003", "1", 88)
        insert into sc values("95003", "2", 68)
        insert into sc values("95003", "3", 88)
        insert into sc values("95004", "2", 76)
        insert into sc values("95004", "3", 76)
        insert into sc values("95005", "1", 87)
        insert into sc(sno,cno) values("95005", "2") end;

     EXEC SQL execute system_tables_reset;
     if (SQLCODE == 0)
     {
        printf("Execute system_tables_reset successfully!%d\n",SQLCODE);
     }
     else
     {
        printf("ERROR: Execute system_tables_resete %d\n",SQLCODE);
     }     
     EXEC SQL execute system_tables_reset2;
     if (SQLCODE == 0)
     {
        printf("Execute system_tables_reset successfully!%d\n",SQLCODE);
     }
     else
     {
        printf("ERROR: Execute system_tables_resete %d\n",SQLCODE);
     }
     EXEC SQL execute system_tables_reset3;
     if (SQLCODE == 0)
     {
        printf("Execute system_tables_reset successfully!%d\n",SQLCODE);
     }
     else
     {
        printf("ERROR: Execute system_tables_resete %d\n",SQLCODE);
     }   
    using_cursor_to_list_student();
    using_cursor_to_list_course();
    using_cursor_to_list_sc();
    return(0);
}

int create_teacher_table()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects
         WHERE (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 ,
    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 transaction
       insert into student values("t5001", "李斌", "男",16, "CS")
       insert into student values("t5002", "赵霞", "女",18, "IS")
       insert into student values("t5003", "周淘", "男",17, "CS")
       insert into student values("t5004", "钱乐", "女",18, "IS")
       insert into student values("t5005", "孙力", "男",16, "MA")
       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);
}

int create_sc_table()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects
         WHERE (xtype = 'U' and name='sc');
    if (SQLCODE == 0||strcmp(tname,"sc")==0)
    {
       printf("The sc 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 sc;
          if (SQLCODE == 0)
          {
             printf("Drop table sc successfully!%d\n\n",SQLCODE);
          }
          else
          {
             printf("ERROR: drop table sc %d\n\n",SQLCODE);
          }
       }
       else return -1;
    }
    EXEC SQL CREATE TABLE sc (
        sno char(5) NOT null ,
    cno char(1) NOT null ,
    grade int null ,
    primary key(sno,cno),
        foreign key(sno) references student(sno),
        foreign key(cno) references course(cno));
    if (SQLCODE == 0)
    {
       printf("Success to create table sc!%d\n\n",SQLCODE);
    }
    else
    {
       printf("ERROR: create table sc %d\n\n",SQLCODE);
    }

//    EXEC SQL exec insert_to_sc "95001","1",66;
//    EXEC SQL exec insert_to_sc "95001","2",77;
//    EXEC SQL exec insert_to_sc "95001","1",66;
//    EXEC SQL exec insert_to_sc "95001","2",66;
//    EXEC SQL exec insert_to_sc "95001","3",66;
//    EXEC SQL exec insert_to_sc "95002","2",0;
//    EXEC SQL exec insert_to_sc "95002","3",97;
//    EXEC SQL exec insert_to_sc "95002","4",0;
//    EXEC SQL exec insert_to_sc "95003","1",88;
//    EXEC SQL exec insert_to_sc "95003","2",68;
//    EXEC SQL exec insert_to_sc "95003","3",88;
//    EXEC SQL exec insert_to_sc "95004","2",76;
//    EXEC SQL exec insert_to_sc "95004","3",76;
//    EXEC SQL exec insert_to_sc "95005","1",87;
//    EXEC SQL exec insert_to_sc "95005","2",NULL;
//CREATE PROCEDURE insert_to_sc @sno char(5),@cno char(1),@grade int
//as insert into sc values(@sno,@cno,@grade)  

   EXEC SQL begin transaction
        insert into sc values("95001", "1", 66)
        insert into sc values("95001", "2", 66)
        insert into sc values("95001", "3", 66)
        insert into sc values("95002", "2", 0)
        insert into sc values("95002", "3", 97)
        insert into sc values("95002", "4", 0)
        insert into sc values("95003", "1", 88)
        insert into sc values("95003", "2", 68)
        insert into sc values("95003", "3", 88)
        insert into sc values("95004", "2", 76)
        insert into sc values("95004", "3", 76)
        insert into sc values("95005", "1", 87)
        insert into sc(sno,cno) values("95005", "2")
        commit transaction;
    if (SQLCODE == 0)
    {
       printf("Success to insert rows to sc table!%d\n\n",SQLCODE);
    }
    else
    {
       // problem connecting to SQL Server
       printf("ERROR: insert rows %d\n",SQLCODE);
    }
    return(0);
}

int create_course_table()
{
    char yn[2];
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects
         WHERE (xtype = 'U' and name='course');
    if (SQLCODE == 0||strcmp(tname,"course")==0)
    {
       printf("The course 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 course;
          if (SQLCODE == 0)
          {
             printf("Drop table course successfully!%d\n\n",SQLCODE);
          }
          else
          {
             printf("ERROR: drop table course %d\n\n",SQLCODE);
          }
       }
       else return -1;
    }
    EXEC SQL CREATE TABLE course (
    cno char(1) NOT null primary key,
    cname char(10) null ,
    cpno char(1) null ,
    ccredit int null);
    if (SQLCODE == 0)
    {
        printf("Success to create table course!%d\n\n",SQLCODE);
    }
    else
    {
        printf("ERROR: create table course %d\n",SQLCODE);
    }
    EXEC SQL begin transaction
        insert into course values("1", "C语言", "",3)
        insert into course values("2", "数据库", "1",4)
        insert into course values("3", "编译原理", "2",5)
        insert into course values("4", "数据结构", "3",2)
        insert into course values("5", "操作系统", "4",3)
        commit transaction;
    if (SQLCODE == 0)
    {
        printf("Success to insert rows to course table!%d\n\n",SQLCODE);
    }
    else
    {
        printf("ERROR: insert rows %d\n\n",SQLCODE);
    }
    return(0);
}

int check_username_password()
{
    int num;
    EXEC SQL BEGIN DECLARE SECTION;
      char tname[21]="xxxxxxxxxxx";
      char sname[11] = "guest";
      char spass[11] = "guest";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL SELECT name into :tname FROM sysobjects
         WHERE (xtype = 'U' and name='users');
    if (SQLCODE == 0||strcmp(tname,"users")==0)
    {
    }
    else{ // 若不存在users表,则创建之.
       EXEC SQL 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');
       if (SQLCODE == 0)
       {
          printf("Success to create table user!%d\n\n",SQLCODE);
       }
       else
       {
          printf("ERROR: create table user %d\n",SQLCODE);
       }
       // 添加缺省用户
       EXEC SQL begin transaction
       insert into [users] values('000001','admin','admin', 'Z')
       insert into [users] values('999999','guest','guest','A')
       commit transaction;
       if (SQLCODE == 0)
       {
          printf("Success to insert rows to user table!%d\n\n",SQLCODE);
       }
       else
       {
          printf("ERROR: insert rows %d\n\n",SQLCODE);
       }
    }
    num=0;
    while (num<3){  // 最多三次登录尝试
         printf("Please input user name(eg:guest):");
         scanf("%s",sname);
         printf("Please input user password(eg:guest):");
         scanf("%s",spass);
         EXEC SQL SELECT uclass into :tname FROM [users] WHERE uname =:sname and upassword=:spass;
         if (SQLCODE == 0||strcmp(tname,"A")==0||strcmp(tname,"Z")==0)
         {
            return(0);
         }
         num++;
    }
    return(-1);
}

void ErrorHandler (void)
{
    // display error information from SQLCA
    printf("\nError Handler called:\n");
    printf("SQL Code = %li\n", SQLCODE);
    printf("SQL Server Message %li: %Fs\n\n", SQLERRD1, SQLERRMC);
}

void pause()
{
    char junk[80],c;
    printf("Press any key to continue!");
    c=getchar();
    gets(junk);
}

究极无敌暴龙战神X
关注
  • 6
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
嵌入式SQL.doc
03-21
SQL是一种双重式语言，它既是一种用于查询和更新的交互式数据库语言，又是一种应用程序进行数据库访问时所采取的编程式数据库语言。SQL语言在这两种方式中的大部分语法是相同的。在编写访问数据库的程序时，必须从普通的编程语言开始（如C语言），再把SQL加入到程序中。所以，嵌入式SQL语言就是将SQL语句直接嵌入到程序的源代码中，与其他程序设计语言语句混合。专用的SQL预编译程序将嵌入的SQL语句转换为能被程序设计语言（如C语言）的编译器识别的函数调用。
mysql 嵌入式sql_嵌入式SQL的使用技巧
weixin_42522912的博客
02-01 1006
#MYSQL#本篇主要介绍的是嵌入式SQL的使用规则，和具体的使用方法。SQL语言可以嵌入到高级语言中，如PL1、COBOL、FORTRAN、 C,利用高级语言的过程性结构，可以弥补SQL语言在实现复杂应用方面的不足，在这种方式下使用SQL语嵌入式SQL的实现主要有两种方法：扩充宿主语言(高级语言)的编译程序，使之能处。理SQL语句和预处理方式。其中常用的是后者，即预处理方式。预处理方式的处理过程...
【数据库系统】嵌入式SQL语言查询编程
weixin_44936207的博客
01-25 849
学生表S（SNO,SNAME,SSEX,SAGE,SADDR） 要求：使用嵌入SQL对数据库进行单表精确查询、模糊查询 主语言：Java 平台：Eclipse DBMS：SQL SERVER 2019 运行说明： （1）下载JDBC，配置路径。 （2）在SQL SERVER的配置管理器中启用TCP/IP连接服务，找到IP地址为127.0.0.1，设置端口号为1433并启用。如下图。 （3）在SQL SERVER中设置用户名和密码，在服务器属性中设置身份验证方式为SQL SERVER和Windows身份验证
【SQL】一文详解嵌入式SQL（建议收藏）
Thunder Wang的博客
12-21 5730
嵌入式SQL(Embedded SQL) 是应用系统使用编程方式来访问和管理数据库中数据的主要方式之一
数据库实验六-通过嵌入式SQL访问数据库
qq_45755193的博客
05-14 1266
通过嵌入式SQL访问数据库
《数据库系统概论》课程之实验六 通过嵌入式SQL访问数据库
Yuhan2001的CSDN博客
11-03 699
《数据库系统概论》课程之实验六 通过嵌入式SQL访问数据库，熟悉通过嵌入式SQL（主语言为JAVA语言）编程访问数据库
【数据库系统概论】实验七 嵌入式SQL查询
杨明金的博客
03-17 5467
一、实验目的 （1）了解嵌入式SQL的使用方法。 （2）设计用户界面，能对数据表进行查询。 二、实验内容 设计一个应用程序，实现对教学管理数据库中所选定的表按指定条件进行查询。用户界面如图1所示。 图1 用户界面 完成以下功能： （1）在学号、姓名、性别、年龄等输入框中输入一个或多个值，点击搜索按钮，能够查询相应记录。如果不输入任何数据，则查询学生表中所有记录。 三、实验指导 （一）Visual C# 嵌入式SQL语句 新建一个网站； 在web.config中增加配置项： <connectio
嵌入式SQL
m0_56945333的博客
11-15 557
siuuuuuuuuu
第15章 嵌入式SQL的应用
Heartbestc的博客
07-31 1247
第15章 嵌入式SQL的应用 SQL在如今的数据库领域虽然应用的非常的广泛，但是，与一些高级语言相比，在某些功能上还有一定的限制。也就是说，在有些功能上，纯粹使用SQL语句还无法实现。数据库访问只是其中一个部件。有些如与用户交互、图形化显示数据等动作只能通过应用高级语言来实现。 15.1 嵌入式SQL介绍 在关系数据库中，可以将SQL语句嵌入到宿主语言中，既实现嵌入式SQL，从而完成在单独使用SQ...
实验6-嵌入式SQL应用.pdf
12-28
中山大学计算机学院《数据库系统原理实验》实验参考。 中山大学计算机学院《数据库系统原理实验》实验参考。 中山大学计算机学院《数据库系统原理实验》实验参考。 中山大学计算机学院《数据库系统原理实验》实验...
嵌入式sql实验步骤
05-06
SQL使用中遇到的基本实验步骤，包括基本的实验开始语句，怎样打开SQL、使用SQL，以及使用过程中的部分常用语句。
数据库嵌入式SQL介绍与应用
01-15
第六章 嵌入式SQL（E-SQL）简介 3 第一节 什么是嵌入SQL语言？ 3 1.1 嵌入SQL程序的组成元素 3 1.2 什么是静态SQL和动态SQL? 4 1.3 什么是SQLCA？ 4 1.4 什么是SQLDA? 5 第二节 SYBASE SQL SERVER嵌入式SQL语言 5 2.1 一个嵌入SQL语言的简单例子 5 2.2 嵌入SQL的处理过程 6 2.3 嵌入SQL语句总览 7 2.3.1 宿主变量 7 2.3.2 连接数据库 12 2.3.3 数据的查询和修改 13 2.3.4 游标的使用 13 2.3.5 SQLCA 15 2.3.6 WHENEVER 16 2.3.7 批处理 18 2.3.8 事务 18 2.4动态SQL语句 18 2.4 .1 动态修改 19 2.4.2 动态游标 20 2.4.3 SQLDA 23 2.4.4 DESCRIBE语句 27 2.5 两个例子程序 27 2.5.1 TELECOM程序 27 2.5.2 ADHOC程序 29 第三节 IBM DB2嵌入SQL语言 35 3.1 一个简单示例 35 3.2 嵌入SQL语句 37 3.2.1宿主变量 37 3.2.2单行查询 39 3.2.3多行查询 39 3.2.4插入、删除和修改操作 40 3.2.5 SQLCA 43 3.2.6事务 45 3.3 DB2的嵌入SQL程序处理过程 46 3.4 DB2的动态SQL嵌入语句 53 3.4.1 基本方法 53 3.4.2 动态游标 55 3.4.3 SQLDA 55 第四节 ORACLE数据库的嵌入SQL语言 66 4.1 基本的SQL语句 66 4.1.1宿主变量和指示符 66 4.1.2 查询 68 4.1.3 修改数据 68 4.1.4 游标 68 4.2 嵌入PL/SQL 71 4.3 动态SQL语句 72 4.3.1 ORACLE动态SQL语句的一些特点 72 4.3.2 使用动态SQL的四种方法 72 4.3.3 SQLDA 75 第五节INFORMIX的嵌入SQL/C语言 89 5.1 一个简单的入门例子 89 5.2 宿主变量 91 5.3 嵌入SQL的处理过程 96 5.4 动态SQL语言 96 5.4.1 SQLDA 97 第六节MICROSOFT SQL SERVER7嵌入式SQL语言 105 6.1 一个嵌入SQL语言的简单例子 105 6.2 嵌入SQL的处理过程 106 6.3 嵌入SQL语句 112 6.3.1 声明嵌入SQL语句中使用的C变量 112 6.3.2 连接数据库 115 6.3.3 数据的查询和修改 115 6.3.4 游标的使用 116 6.3.5 SQLCA 117 6.3.6 WHENEVER 118 6.4动态SQL语句 119 6.4 .1 动态修改 119 6.4.2 动态游标 120 6.4.3 SQLDA 122 6.4.4 DESCRIBE语句 130 6.5 API 130
sql的c语言嵌入式开发
09-17
在Visual Studio C++环境中通过ODBC实现与实验1建立的数据库StuManagement的互联，进行实验要求的各种操作，关系模式... 界面执行SQL语句操作(注：用户通过界面输入SQL语句，应用程序执行该语句，对数据库进行操作)。
数据库实验指导及源代码
05-15
数据库原理及应用的上机实验是学习本门课程的重要环节之一，实验的设置是为了配合 课堂教学，使学生熟练掌握和深入理解课堂教学内容，进一步强化对数据库原理及应用的理 解。同时，通过上机练习，也可以检验用关系...
数据库原理及应用-第8章数据库编程.pdf
06-17
数据库原理及应用 Database Principle and Application 第八章 数据库编程 第八章 数据库编程 8.1 嵌入式SQL 8.2 存储过程 8.3 ODBC编程 8.4 小结 8.1 嵌入式SQL 这两种方式细节上有差别，在程序设计的环境下， SQL...
嵌入式关系型数据库LittleD.zip
07-18
LittleD 是为嵌入式设备和传感器节点设计的关系型数据库，提供一个 SQL 前端来管理关系模型的数据。LittleD 是专为 Arduino Mega2560 这些设备所专门设计的。LittleD 支持表创建，数据插入和 SELECT-FROM-WHERE 语法...
【kettle001】访问国产达梦数据库并处理数据至execl文件
kngines
04-22 432
一直以来想写下基于kettle的系列文章，作为较火的数据ETL工具，也是日常项目开发中常用的一款工具，最近刚好挤时间梳理、总结下这块儿的知识体系。熟悉、梳理、总结下达梦（DM）关系型数据库相关知识体系。
探秘MySQL主从复制的多种实现方式
最新发布
todoitbo的博客
04-26 660
本文将深入探讨MySQL主从复制的多种实现方式，包括基于语句、基于行和混合模式等。无论您是初学者还是有经验的数据库管理员，都能通过本文全面了解MySQL主从复制技术的多样化选择，从而提高数据库的可用性和性能。
redis分布式锁到底怎么用
LinggoLing的博客
04-22 449
分布式锁到底怎么用
嵌入式sql server
06-06
嵌入式 SQL Server 指的是将 SQL Server 引擎嵌入到应用程序中，使得应用程序可以直接访问和操作 SQL Server 数据库，而无需安装和配置独立的 SQL Server 实例。这种方式可以提高应用程序的性能和可靠性，同时也可以减少系统资源的占用。 在 Microsoft SQL Server 中，可以通过 SQL Server Compact Edition 或 LocalDB 来实现嵌入式 SQL Server。SQL Server Compact Edition 是一个轻量级的数据库引擎，可以在桌面应用程序、移动设备和 Web 应用程序中使用；而 LocalDB 则是一个轻量级的 SQL Server 实例，可以在开发和测试过程中使用。 使用嵌入式 SQL Server 的好处是可以简化应用程序的部署和维护，并且可以提高应用程序的性能和安全性。但是需要注意的是，嵌入式 SQL Server 的功能和性能受限于它所嵌入的应用程序，因此需要仔细评估和规划。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值