Record Employee type sqlRecord { tableNames = [["employee"]], keyItems = ["empnum"], defaultSelectCondition = #sqlCondition{ // no space between #sql and the brace aTableColumn = 4 -- start each SQL comment -- with a double hyphen } } empnum decimal(6,0) {isReadonly=yes}; empname char(40); end
emp Employee;
有关 SQL 记录和隐式语句的更多详细信息,请参阅 SQL 支持。
empnum decimal(6,0); empname char(40);
empnum = 1; empname = "John";
try execute #sql{ insert into employee (empnum, empname) values (:empnum, :empname) }; onException myErrorHandler(8); end
empnum = 1;
open selectEmp with #sql{ select empnum, empname from employee where empnum >= :empnum for update of empname } into empnum, empname;
get next from selectEmp;
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();
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();
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;
emp.empnum = 1; emp.empname = "John";
try add emp; onException myErrorHandler(8); end
emp.empnum = 1;
try get emp; onException myErrorHandler(8); end
try get emp singleRow; onException myErrorHandler(8); end
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();
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();
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);
emp.empnum = 1; emp.empname = "John"; empTable = "Employee";
// 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
emp.empnum = 1;
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
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
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();
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();
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);
emp.empnum = 1; emp.empname = "John"; empnum02 = 2; empname02 = "Jane";
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
myString = "insert into employee (empnum, empname) " + "values (?, ?)"; try prepare addEmployee from myString; onException myErrorHandler(8); end
execute addEmployee using emp.empnum, emp.empname;
execute addEmployee using empnum02, empname02;
empnum02 = 2;
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();
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();