实验六 嵌入式SQL应用

已于 2023-05-07 20:04:13 修改
阅读量1.3k 收藏 22
点赞数 8
分类专栏： 数据库 文章标签： 数据库 服务器 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——嵌入式SQL应用
m0_62484615的博客
12-28 1007
中山大学计算机学院《数据库系统原理实验》实验参考。
嵌入式SQL应用
sysuzyc的博客
10-19 1137
visual studio下嵌入数据库，并利用c/c++进行代码的编写
【数据库系统概论】实验七 嵌入式SQL查询
杨明金的博客
03-17 5643
一、实验目的 （1）了解嵌入式SQL的使用方法。 （2）设计用户界面，能对数据表进行查询。 二、实验内容 设计一个应用程序，实现对教学管理数据库中所选定的表按指定条件进行查询。用户界面如图1所示。 图1 用户界面 完成以下功能： （1）在学号、姓名、性别、年龄等输入框中输入一个或多个值，点击搜索按钮，能够查询相应记录。如果不输入任何数据，则查询学生表中所有记录。 三、实验指导 （一）Visual C# 嵌入式SQL语句 新建一个网站； 在web.config中增加配置项： <connectio
《数据库系统概论》课程之实验六 通过嵌入式SQL访问数据库
Yuhan2001的CSDN博客
11-03 1067
《数据库系统概论》课程之实验六 通过嵌入式SQL访问数据库，熟悉通过嵌入式SQL（主语言为JAVA语言）编程访问数据库
数据库实验六-通过嵌入式SQL访问数据库
qq_45755193的博客
05-14 1453
通过嵌入式SQL访问数据库
嵌入式数据库（六）
XuLujunCSDN
01-02 389
一、查询条件 查询所有年龄在20岁以下的学生姓名及其年龄  SELECT Sname, Sage  FROM Student   WHERE Sage 或：SELECT Sname, Sage  FROM Student WHERE NOT Sage >= 20  确定范围  ( BETWEEN…AND) BETWEEN … AND …      包括临
实验6-嵌入式SQL应用.pdf
最新发布
12-28
嵌入式SQL是一种将SQL（结构化查询语言）语句...总结来说，这个实验是关于嵌入式SQL在C语言中的应用，主要涉及数据库连接、SQL语句的执行、错误处理以及字符集设置等关键知识点，旨在提高学生对数据库操作的实战能力。
嵌入式sql语言实验报告.docx
01-09
实验结果展示了不同类型的DML语句在嵌入式SQL中的应用，包括不使用游标和使用游标的情况，以及在C#环境下如何进行数据库操作。通过这样的实践，学生可以深入理解嵌入式SQL的概念，掌握其在实际编程中的运用。
实验4嵌入式SQL和ODBC的使用.doc
01-09
实验4的目标是让学生熟悉ODBC的配置和使用，以及嵌入式SQL编程，进一步巩固SQL语言的基础知识。实验平台基于Windows XP操作系统，使用SQL Server 2000作为数据库管理系统，并利用Visual C++ 6.0作为开发环境。 ...
实验五嵌入式SQL和ODBC的使用.doc
06-12
### 实验五嵌入式SQL和ODBC的使用 #### 实验目的 1. **熟悉ODBC的配置和使用**：ODBC（Open Database Connectivity，开放式数据库连接）是一种标准的应用程序接口（API），用于实现应用程序与多种数据库管理系统...
嵌入式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的综合应用编程（基于QSqlTableModel实现）
share something here
06-26 1652
【数据库实验】基于嵌入SQL的综合应用编程 【数据库实验】基于嵌入SQL的综合应用编程
手把手教你北邮操作系统小学期实验六——Linux环境下嵌入式数据库mSQL的集成1
对方正在输入中...
02-02 1762
接上一章 手把手教你北邮操作系统小学期实验五——Linux环境下的Ramdisk技术及其实验2 此实验是我做的。 1．实验环境： 硬件：Intel i5 操作系统：Windows 8.1 虚拟机：VMware Workstation 12.5.0 软件：Red Hat Enterprise Linux 9 (Linux 内核 2.4.20-8) 2．实验目的： 实时嵌入式应用常常需要数据库的支持。虽然很多情况下可以用文件方式实现部分数据库功能，但是当应用程序需要执行一些比较复杂的数据操作时，文件方式就无能
第15章 嵌入式SQL的应用
Heartbestc的博客
07-31 1322
第15章 嵌入式SQL的应用 SQL在如今的数据库领域虽然应用的非常的广泛，但是，与一些高级语言相比，在某些功能上还有一定的限制。也就是说，在有些功能上，纯粹使用SQL语句还无法实现。数据库访问只是其中一个部件。有些如与用户交互、图形化显示数据等动作只能通过应用高级语言来实现。 15.1 嵌入式SQL介绍 在关系数据库中，可以将SQL语句嵌入到宿主语言中，既实现嵌入式SQL，从而完成在单独使用SQ...
用嵌入式SQL语言实现数据库的录入、修改和删除等管理功能
一只猫猫的博客
12-01 8891
目录功能分析设计源程序清单和结果open()事件：sc_management:course_management:student_management:运行结果录入：查询：修改：删除： 开发环境 软件环境：Intel® Core™ i5-7200U CPU @2.50GHz 2.70 GHz 硬件环境：Win10 开发工具：PB,SQL server 语言：PB 基于实验一的三个表采用嵌入式SQL语言实现数据库的录入、修改和删除等管理功能。或者也可以采用PB实用教程中的第4部分，完成综合应用实习的编程实
第十二章 使用嵌入式SQL（二）
yaoxin521123的博客
03-19 625
文章目录 第十二章 使用嵌入式SQL（二）嵌入式SQL代码简单的SQL语句架构名称解析文字值数据格式权限检查 第十二章 使用嵌入式SQL（二） 嵌入式SQL代码 简单的SQL语句 可以将简单的SQL语句（单个嵌入式SQL语句）用于各种操作，包括： INSERT，UPDATE，INSERT OR UPDATE和DELETE语句。 `DDL语句。 GRANT和REVOKE语句。 只返回一行的SELECT语句(或者如果只对返回的第一行感兴趣)。 简单的SQL语句也被称为非基于游标的SQL语句。本章稍后将介绍
嵌入式的SQL程序设计
dingshen2767的博客
12-04 151
嵌入式的SQL程序设计 1 --嵌入式SQL程序设计 2 --SQL2005管理员大全 3 4 5 --嵌入SQL语句的应用程序叫做宿主程序，书写该程序的语言称为宿主语言。 6 --宿主语言可以是C、C++、JAVA等。嵌入的SQL语句与交互式SQL在语法上类似 7 --但是嵌入式SQL在个别语句上有所扩充 8 9 --嵌入...
mysql 嵌入式sql_数据库系统概论 实验五 通过嵌入式SQL访问数据库
weixin_32581787的博客
02-01 1724
一、实验目的熟悉通过嵌入式SQL(主语言为C语言)编程访问数据库。二、实验平台和实验工具在KingbaseES数据库管理系统上，通过C语言编写访问数据库的应用程序来对数据库进行各种数据操作。编程工具VC++6.0另需要32位KingbaseES的lib库，后会详细说明。三、实验内容和要求熟悉RDBMS的预编译程序。通过嵌入式SQL编程访问数据库的基本步骤，对学生课程数据库中的表，完成下面功能(你也...
实验6-嵌入式SQL应用：数据库系统原理实践
该资源是中山大学计算机学院《数据库系统原理实验》的实验参考资料，重点关注的是实验6，即嵌入式SQL的应用。实验环境中使用了Windows 11和MySQL 8.0 CE作为数据库管理系统。实验内容包括配置开发环境，如在Visual ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值