SQL 示例

可以按照下列任何方式来访问 SQL 数据库:
在每一种情况下,都可以使用 SQL 记录来作为内存区并提供一种简单的方法来测试操作是否成功。本节中的示例假定在 EGL 文件中声明了一个记录部件,并且在该文件中的程序中声明了一条基于该部件的记录:

有关 SQL 记录和隐式语句的更多详细信息,请参阅 SQL 支持

编写 SQL 语句

要准备编写 SQL 语句,请声明变量:
  empnum decimal(6,0);
  empname char(40);

向 SQL 表添加行

要准备添加行,请对变量赋值:
  empnum = 1;    empname = "John";
要添加行,请将 EGL execute 语句与 SQL INSERT 语句相关联,如下所示:
  try
    execute
      #sql{
        insert into employee (empnum, empname)
        values (:empnum, :empname)
      };
  onException
    myErrorHandler(8);
  end

  

从 SQL 表读取一组行

要准备从 SQL 表读取一组行,请标识记录键:
  empnum = 1;  
要获取数据,请编写一系列 EGL 语句:
  • 要选择结果集,请运行 EGL open 语句:
      open selectEmp
        with #sql{
          select empnum, empname
          from employee
          where empnum >= :empnum
          for update of empname
        }
        into empnum, empname;
  • 要访问结果集的下一行,请运行 EGL get next 语句:
      get next from selectEmp;
    如果在 open 语句中未指定 into 子句,则需要在 get next 语句中指定 into 子句;并且,如果在两个位置都指定了 into 子句,则 get next 语句中的该子句具有优先权:
      get next from selectEmp
        into empnum, empname;

    在从结果集中读取了最后一条记录之后,游标将自动关闭。

下面的代码是较完整的示例,它将更新一组行:
  VGVar.handleHardIOErrors  = 1;

  try
    open selectEmp
      with #sql{
        select empnum, empname
        from employee
        where empnum >= :empnum
        for update of empname
      }
      into empnum, empname;
  onException
    myErrorHandler(6);    // exits program
  end

  
  try
    get next from selectEmp;
  onException
    if (sqlcode != 100)
      myErrorHandler(8);  // exits program
    end

    end

  
  while (sqlcode != 100)
    empname = empname + " " + "III";

    try
      execute
        #sql{
          update employee
          set empname = :empname
          where current of selectEmp
        };
    onException
      myErrorHandler(10); // exits program
    end

  
    try
      get next from selectEmp;
    onException
      if (sqlcode != 100)
        myErrorHandler(8);  // exits program
      end

      end

    end  // end while; cursor is closed automatically
       // when the last row in the result set is read

  sysLib.commit();
可使用 forEach 语句来对结果集中的每一行执行一个语句块,而不是编写 get next 和 while 语句:
  VGVar.handleHardIOErrors  = 1;

  try
    open selectEmp
      with #sql{
        select empnum, empname
        from employee
        where empnum >= :empnum
        for update of empname
      }
      into empnum, empname;
  onException
    myErrorHandler(6);    // exits program
  end

  
  try
    forEach (from selectEmp)
      empname = empname + " " + "III";
    
      try
        execute
          #sql{
            update employee
            set empname = :empname
          where current of selectEmp
          };
      onException
        myErrorHandler(10); // exits program
      end

      end  // end forEach; cursor is closed automatically
         // when the last row in the result set is read

  onException
    // the exception block related to forEach is not run if the condition 
    // is "sqlcode = 100", so avoid the test "if (sqlcode != 100)"
    myErrorHandler(8);  // exits program
  end

  
  sysLib.commit();

将 SQL 记录与隐式 SQL 语句配合使用

要开始使用 EGL SQL 记录,请声明 SQL 记录部件:
  Record Employee type sqlRecord
    { 
      tableNames = [["employee"]],
      keyItems = ["empnum"],
      defaultSelectCondition =
        #sqlCondition{
          aTableColumn = 4 -- start each SQL comment
                           -- with a double hyphen
        }
    }

    empnum  decimal(6,0) {isReadonly=yes};
    empname char(40);
  end
声明基于该记录部件的记录:
  emp Employee;

向 SQL 表添加行

要准备向 SQL 表添加行,请在 EGL 记录中指定值:
  emp.empnum = 1;    emp.empname = "John";
通过指定 EGL add 语句来将职员添加到表中:
  try
    add emp;
  onException
    myErrorHandler(8);
  end

  

从 SQL 表中读取行

要准备从 SQL 表中读取行,请标识记录键:
  emp.empnum = 1;  
通过下列任何一种方式获取一行:
  • 按照生成一系列语句(DECLARE cursor、OPEN cursor、FETCH row 以及 CLOSE cursor(如果不存在 forUpdate 的话))的方式指定 EGL get 语句:
      try
        get emp;
      onException
        myErrorHandler(8);
      end
  • 按照生成单一 SELECT 语句的方式指定 EGL get 语句:
      try
        get emp singleRow;
      onException
        myErrorHandler(8);
      end
    
      
通过下列任一方式处理多行:
  • 使用 EGL open、get next 和 while 语句:
      VGVar.handleHardIOErrors  = 1;
    
      try
        open selectEmp forUpdate for emp;
      onException
        myErrorHandler(6);    // exits program
      end
    
      
      try
        get next emp;
      onException
        if (emp not noRecordFound)
          myErrorHandler(8);  // exit the program
        end
    
        end
    
      
      while (emp not noRecordFound)
        myRecord.empname = myRecord.empname + " " + "III";
    
        try
          replace emp;
        onException
          myErrorHandler(10); // exits program
        end
    
      
        try
          get next emp;
        onException
          if (emp not noRecordFound)
            myErrorHandler(8);  // exits program
          end
    
          end
    
        end  // end while; cursor is closed automatically
           // when the last row in the result set is read
    
      sysLib.commit();
  • 使用 EGL open 和 forEach 语句:
      VGVar.handleHardIOErrors  = 1;
    
      try
        open selectEmp forUpdate for emp;
      onException
        myErrorHandler(6);    // exits program
      end
    
      
      try
        forEach (from selectEmp)
          myRecord.empname = myRecord.empname + " " + "III";
         
          try
            replace emp;
          onException
            myErrorHandler(10); // exits program
          end
    
          end  // end forEach; cursor is closed automatically
             // when the last row in the result set is read
    
      onException
    
        // the exception block related to forEach is not run if the condition 
        // is noRecordFound, so avoid the test "if (not noRecordFound)" 
        myErrorHandler(8);  // exit the program
      end
    
      
      sysLib.commit();

将 SQL 记录与显式 SQL 语句配合使用

在将 SQL 记录与显式 SQL 语句配合使用之前,请声明 SQL 记录部件。此部件与 SQL 项属性语法中的以及计算的值的使用中的上一个部件不同:
  Record Employee type sqlRecord
    {
      tableNameVariables = [["empTable"]], 
                   // use of a table-name variable
                   // means that the table is specified
                   // at run time
      keyItems = ["empnum"]
    }
    empnum decimal(6,0) { isReadonly = yes };
    empname char(40);

    // specify properties of a calculated column
    aValue decimal(6,0)
      { isReadonly = yes,
        column = "(empnum + 1) as NEWNUM" };
  end

  
声明变量:
  emp Employee;
  empTable char(40);

向 SQL 表添加行

要准备向 SQL 表添加行,请在 EGL 记录中以及在表名变量中指定值:
  emp.empnum = 1;    emp.empname = "John";
  empTable = "Employee";
通过指定 EGL add 语句并修改 SQL 语句来将职员添加到表中:
  // a colon does not precede a table name variable
  try
    add emp
      with #sql{
        insert into empTable (empnum, empname)
        values (:empnum, :empname || ' ' || 'Smith')
      }

  onException
    myErrorHandler(8);
  end

  

从 SQL 表中读取行

要准备从 SQL 表中读取行,请标识记录键:
  emp.empnum = 1;  
通过下列任何一种方式获取一行:
  • 按照生成一系列语句(DECLARE cursor、OPEN cursor、FETCH row 以及 CLOSE cursor)的方式指定 EGL get 语句:
      try
        get emp into empname   // The into clause is optional. (It
                               // cannot be in the SELECT statement.)
        with #sql{
          select empname
          from empTable
          where empum = :empnum + 1
        }
      onException
        myErrorHandler(8);
      end
  • 按照生成单一 SELECT 语句的方式指定 EGL get 语句:
      try
        get emp singleRow // The into clause is derived
                          // from the SQL record and is based
                          // on the columns in the select clause
        with #sql{
          select empname
          from empTable
          where empnum = :empnum + 1
        }
      onException
        myErrorHandler(8);
      end
    
      
通过下列任一方式处理多行:
  • 使用 EGL open、get next 和 while 语句:
      try
    
      // The into clause is derived
      // from the SQL record and is based
      // on the columns in the select clause
        open selectEmp forUpdate 
          with #sql{
            select empnum, empname
            from empTable
            where empnum >= :empnum
            order by NEWNUM       -- uses the calculated value
            for update of empname
          } for emp;
      onException
        myErrorHandler(8);    // exits the program
      end
    
         
      try
        get next emp;
      onException
        myErrorHandler(9);  // exits the program
      end
    
      
      while (emp not noRecordFound)
        try
          replace emp
          with #sql{
            update :empTable
            set empname = :empname || ' ' || 'III'
          } from selectEmp;
    
        onException
          myErrorHandler(10);   // exits the program
        end
    
        
        try
          get next emp;
        onException
          myErrorHandler(9);  // exits the program
        end
    
        end  // end while
    
      // no need to say "close emp;" because emp
      // is closed automatically when the last
      // record is read from the result set or
      // (in case of an exception) when the program ends
    
      sysLib.commit();
  • 使用 EGL open 和 forEach 语句:
      try
    
        // The into clause is derived
        // from the SQL record and is based
        // on the columns in the select clause
        open selectEmp forUpdate 
          with #sql{
            select empnum, empname
            from empTable
            where empnum >= :empnum
            order by NEWNUM       -- uses the calculated value
            for update of empname
          } for emp;
    
      onException
        myErrorHandler(8);    // exits the program
      end
    
         
      try
        forEach (from selectEmp) 
          
          try
            replace emp
            with #sql{
              update :empTable
              set empname = :empname || ' ' || 'III'
            } from selectEmp;
         
          onException
            myErrorHandler(9);  // exits program
          end
    
      
        end   // end forEach statement, and there is
              // no need to say "close emp;" because emp
              // is closed automatically when the last
              // record is read from the result set or
              // (in case of an exception) when the program ends
    
      onException
        // the exception block related to forEach is not run if the condition 
        // is noRecordFound, so avoid the test "if (not noRecordFound)" 
        myErrorHandler(9);  // exits program
      end
    
      
      sysLib.commit();

使用 EGL prepare 语句

在编写 EGL prepare 语句时,您可以选择使用 SQL 记录部件。请声明以下部件:
  Record Employee type sqlRecord
    { 
      tableNames = [["employee"]],
      keyItems = ["empnum"],
      defaultSelectCondition =
        #sqlCondition{
          aTableColumn = 4 -- start each SQL comment
                            -- with a double hyphen
        }
    }

    empnum  decimal(6,0) {isReadonly=yes};
    empname char(40);
  end
声明变量:
  emp Employee;
  empnum02 decimal(6,0);
  empname02 char(40);
  myString char(120);

向 SQL 表添加行

在添加行之前,请对变量赋值:
  emp.empnum = 1;    emp.empname = "John";
  empnum02 = 2;
  empname02 = "Jane";
开发 SQL 语句:
  • 编写 EGL prepare 语句并引用 SQL 记录,该记录提供了可以定制的 SQL 语句:
      prepare myPrep
        from "insert into employee (empnum, empname) " +
        "values (?, ?)" for emp;
      
      // you can use the SQL record
      // to test the result of the operation
      if (emp is error)
        myErrorHandler(8);
      end
    
      
  • 另外,可以在不引用 SQL 记录的情况下编写 EGL prepare 语句:
      myString = "insert into employee (empnum, empname) " +
        "values (?, ?)";  
    
      try
        prepare addEmployee from myString;
      onException
        myErrorHandler(8);
      end
    
      
在前面的每个例子中,EGL prepare 语句都包含将由 EGL execute 语句提供的数据的占位符。下面是 execute 语句的两个示例:
  • 可以从记录(SQL 记录或别的记录)中提供值:
      execute addEmployee using emp.empnum, emp.empname;
  • 可以从各个项中提供值:
      execute addEmployee using empnum02, empname02;

从 SQL 表中读取行

要准备从 SQL 表中读取行,请标识记录键:
  empnum02 = 2;
通过下列任一方式替换多行:
  • 使用 EGL open、while 和 get next 语句:
      myString = "select empnum, empname from employee " + 
                 "where empnum >= ? for update of empname";
    
      try
        prepare selectEmployee from myString for emp;
      onException
        myErrorHandler(8);    // exits the program
      end
    
       
      try
        open selectEmp with selectEmployee
          using empnum02
          into emp.empnum, emp.empname;
      onException
        myErrorHandler(9);  // exits the program
      end
    
        
      try
        get next from selectEmp;
      onException
        myErrorHandler(10);   // exits the program
      end
    
      
      while (emp not noRecordFound)
                                        
        emp.empname = emp.empname + " " + "III";
    
        try
          replace emp
            with #sql{
              update employee
              set empname = :empname
            }
          from selectEmp;
        onException
          myErrorHandler(11); // exits the program
        end
    
      
        try
          get next from selectEmp;
        onException
          myErrorHandler(12); // exits the program
        end
    
        end  // end while; close is automatic when last row is read
      sysLib.commit();
  • 使用 EGL open 和 forEach 语句:
      myString = "select empnum, empname from employee " + 
                 "where empnum >= ? for update of empname";
    
      try
        prepare selectEmployee from myString for emp;
      onException
        myErrorHandler(8);    // exits the program
      end
    
       
      try
        open selectEmp with selectEmployee
          using empnum02
          into emp.empnum, emp.empname;
      onException
        myErrorHandler(9);  // exits the program
      end
    
        
      try
        forEach (from selectEmp)
          emp.empname = emp.empname + " " + "III";
         
          try
            replace emp
              with #sql{
                update employee
                set empname = :empname
              }
            from selectEmp;
          onException
            myErrorHandler(11); // exits the program
          end
    
          end // end forEach; close is automatic when last row is read
      onException
    
        // the exception block related to forEach is not run if the condition 
        // is noRecordFound, so avoid the test "if (not noRecordFound)" 
        myErrorHandler(12); // exits the program
      end
    
      
      sysLib.commit();
使用条款 | 反馈
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.