//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: TbMod.sqlj
//
// SAMPLE: How to modify table data
//
// SQL Statements USED:
// SELECT
// UPDATE
// DELETE
// ROLLBACK
//
// Classes used from Util.sqlj are:
// Db
// Data
// SqljException
//
//
// Output will vary depending on the JDBC driver connectivity used.
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
// http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/
import java.lang.*;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import sqlj.runtime.ForUpdate;
#sql iterator TbMod_Cursor1(Integer, String, Integer, String,
Integer, Double, Double);
#sql iterator TbMod_Cursor2(String, Double, String);
#sql iterator TbMod_PosCursor implements ForUpdate(String, int);
class TbMod
{
public static void main(String argv[])
{
try
{
Db db = new Db(argv);
System.out.println();
System.out.println("THIS SAMPLE SHOWS HOW TO MODIFY TABLE DATA.");
// connect to the 'sample' database
db.getDefaultContext();
// different ways to INSERT table data
insertUsingValues();
insertUsingFullselect();
// different ways to UPDATE table data
updateWithoutSubqueries();
updateUsingSubqueryInSetClause();
updateUsingSubqueryInWhereClause();
updateUsingCorrelatedSubqueryInSetClause();
updateUsingCorrelatedSubqueryInWhereClause();
positionedUpdateWithoutSubqueries();
positionedUpdateUsingSubqueryInSetClause();
positionedUpdateUsingCorrelatedSubqueryInSetClause();
// different ways to DELETE table data
deleteWithoutSubqueries();
deleteUsingSubqueryInWhereClause();
deleteUsingCorrelatedSubqueryInWhereClause();
positionedDelete();
// disconnect from the 'sample' database
db.disconnect();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // main
// helping function: Display the content of the 'staff' table
static void staffTbContentDisplay()
{
try
{
TbMod_Cursor1 cur1;
Integer id = new Integer(0);
Integer years = new Integer(0);
Integer dept = new Integer(0);
String name = null;
String job = null;
Double salary = new Double(0.0);
Double comm = new Double(0.0);
System.out.println();
System.out.println(
" SELECT * FROM staff WHERE id >= 310\n" +
" ID NAME DEPT JOB YEARS SALARY COMM\n" +
" --- -------- ---- ----- ----- -------- --------");
#sql cur1 = {SELECT * FROM staff WHERE id >= 310};
while (true)
{
#sql {FETCH :cur1
INTO :id, :name, :dept, :job, :years, :salary, :comm};
if (cur1.endFetch())
{
break;
}
System.out.print(" "+Data.format(id, 3) +
" " + Data.format(name, 8) +
" " + Data.format(dept, 4));
if (job != null)
{
System.out.print(" " + Data.format(job, 5));
}
else
{
System.out.print(" -");
}
if (years != null)
{
System.out.print(" " + Data.format(years, 5));
}
else
{
System.out.print(" -");
}
System.out.print(" " + Data.format(salary, 7, 2));
if (comm != null)
{
System.out.print(" " + Data.format(comm, 7, 2));
}
else
{
System.out.print(" -");
}
System.out.println();
}
// close the cursor
cur1.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // staffTbContentDisplay
// helping function: Display part of the content of the 'employee' table
static void employeeTbPartialContentDisplay()
{
try
{
TbMod_Cursor2 cur2;
String empno = null;
Double salary = new Double(0.0);
String workdept = null;
System.out.println();
System.out.println(" SELECT empno, salary, workdept\n" +
" FROM employee\n" +
" WHERE workdept = 'E11'\n" +
" EMPNO SALARY WORKDEPT\n" +
" ------ ---------- --------");
#sql cur2 = {SELECT empno, salary, workdept
FROM employee
WHERE workdept = 'E11'};
while (true)
{
#sql {FETCH :cur2 INTO :empno, :salary, :workdept};
if (cur2.endFetch())
{
break;
}
System.out.println(" "+Data.format(empno, 6) +
" " + Data.format(salary, 9, 2) +
" " + Data.format(workdept, 8));
}
System.out.println();
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // employeeTbPartialContentDisplay
// This function demonstrates how to insert table data using VALUES
static void insertUsingValues()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO INSERT TABLE DATA USING VALUES.");
// display the initial content of the 'staff' table
staffTbContentDisplay();
// INSERT data INTO a table using VALUES
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" INSERT INTO staff(id, name, dept, job, salary)\n" +
" VALUES(380, 'Pearce', 38, 'Clerk', 13217.50),\n" +
" (390, 'Hachey', 38, 'Mgr', 21270.00),\n" +
" (400, 'Wagland', 38, 'Clerk', 14575.00)");
#sql {INSERT INTO staff(id, name, dept, job, salary)
VALUES(380, 'Pearce', 38, 'Clerk', 13217.50),
(390, 'Hachey', 38, 'Mgr', 21270.00),
(400, 'Wagland', 38, 'Clerk', 14575.00)};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // insertUsingValues
// This function demonstrates how to insert table data using
// FULLSELECT
static void insertUsingFullselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" INSERT\n" +
"TO INSERT TABLE DATA USING FULLSELECT.");
// display the initial content of the 'staff' table
staffTbContentDisplay();
// INSERT data INTO a table using FULLSELECT
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" INSERT INTO staff(id, name, dept, salary)\n" +
" SELECT INTEGER(empno)+100, lastname, 77, salary\n" +
" FROM employee\n" +
" WHERE INTEGER(empno) >= 310");
#sql {INSERT INTO staff(id, name, dept, salary)
SELECT INTEGER(empno) + 100, lastname, 77, salary
FROM employee
WHERE INTEGER(empno) >= 310};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // insertUsingFullselect
// This function demonstrates how to update table data
static void updateWithoutSubqueries()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" UPDATE\n" +
"TO UPDATE TABLE DATA.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// update table data
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" UPDATE staff\n" +
" SET salary = salary + 1000\n" +
" WHERE id >= 310 AND dept = 84");
#sql {UPDATE staff
SET salary = salary + 1000
WHERE id >= 310 AND dept = 84};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // updateWithoutSubqueries
// This function demonstrates how to update table data using a
// subquery in the SET clause
static void updateUsingSubqueryInSetClause()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" UPDATE\n" +
"TO UPDATE TABLE DATA USING A SUBQUERY IN THE 'SET' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// update data of the table 'staff' by using a subquery in the SET
// clause
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" UPDATE staff\n" +
" SET salary = (SELECT MIN(salary)\n" +
" FROM staff\n" +
" WHERE id >= 310)\n" +
" WHERE id = 350");
#sql {UPDATE staff
SET salary = (SELECT MIN(salary)
FROM staff
WHERE id >= 310)
WHERE id = 350};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // updateUsingSubqueryInSetClause
// This function demonstrates how to update table data using a subquery
// in the WHERE clause.
static void updateUsingSubqueryInWhereClause()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" UPDATE\n" +
"TO UPDATE TABLE DATA\n" +
"USING A SUBQUERY IN THE 'WHERE' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// update table using subquery in WHERE clause
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" UPDATE staff\n" +
" SET comm = 250.00\n" +
" WHERE dept = 84 AND\n" +
" salary < (SELECT AVG(salary)\n" +
" FROM staff\n" +
" WHERE id >= 310 AND dept = 84)");
#sql {UPDATE staff
SET comm = 250.00
WHERE dept = 84 AND
salary < (SELECT AVG(salary)
FROM staff
WHERE id >= 310 AND dept = 84)};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // updateUsingSubqueryInWhereClause
// This function demonstrates how to update table data using a
// correlated subquery in the 'SET' clause.
static void updateUsingCorrelatedSubqueryInSetClause()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" UPDATE\n" +
"TO UPDATE TABLE DATA\n" +
"USING A CORRELATED SUBQUERY IN THE 'SET' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// update data of the 'staff' table using a correlated subquery in
// the 'SET' clause
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" UPDATE staff s1\n" +
" SET comm = 0.01 * (SELECT MIN(salary)\n" +
" FROM staff s2\n" +
" WHERE id >= 310 AND\n" +
" s2.dept = s1.dept)\n" +
" WHERE id >= 340");
#sql {UPDATE staff s1
SET comm = 0.01 * (SELECT MIN(salary)
FROM staff s2
WHERE id >= 310 AND
s2.dept = s1.dept)
WHERE id >= 340};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // updateUsingCorrelatedSubqueryInSetClause
// This function demonstrates how to update table data using a
// correlated subquery in the 'WHERE' clause.
static void updateUsingCorrelatedSubqueryInWhereClause()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" UPDATE\n" +
"TO UPDATE TABLE DATA\n" +
"USING A CORRELATED SUBQUERY IN THE 'WHERE' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// update data of the 'staff' table using a correlated subquery in the
// 'WHERE' clause
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" UPDATE staff s1\n" +
" SET comm = 700\n" +
" WHERE id >= 340 AND\n" +
" salary < (SELECT AVG(salary)\n" +
" FROM staff s2\n" +
" WHERE id >= 310 AND\n" +
" s2.dept = s1.dept)");
#sql {UPDATE staff s1
SET comm = 700
WHERE id >= 340 AND
salary < (SELECT AVG(salary)
FROM staff s2
WHERE id >= 310 AND
s2.dept = s1.dept)};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // updateUsingCorrelatedSubqueryInWhereClause
// This function demonstrates how to perform a positioned update on a table
static void positionedUpdateWithoutSubqueries()
{
try
{
TbMod_PosCursor posCur;
int dept = 0;
String name = null;
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" UPDATE\n" +
"TO PERFORM A POSITIONED UPDATE ON A TABLE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
System.out.println();
System.out.println(
" Invoke the statements:\n" +
" DECLARE posCur CURSOR FOR\n" +
" SELECT name, dept FROM staff WHERE id >= 310\n" +
"\n" +
" FETCH :posCur INTO :name, :dept\n" +
" while (successful fetch)\n" +
" {\n" +
" if (dept != 84)\n" +
" {\n" +
" UPDATE staff SET comm = NULL WHERE CURRENT OF :posCur\n" +
" }\n" +
" FETCH :posCur INTO :name, :dept\n" +
" }");
// declare a cursor
#sql posCur = {SELECT name, dept FROM staff WHERE id >= 310};
while (true)
{
// fetch the cursor
#sql {FETCH :posCur INTO :name, :dept};
if (posCur.endFetch())
{
break;
}
if (dept != 84)
{
#sql {UPDATE staff SET comm = NULL WHERE CURRENT OF :posCur};
}
}
// close the cursor
posCur.close();
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // positionedUpdateWithoutSubqueries
// This function demonstrates how to perform a positioned update on a table
// using subquery in the 'SET' clause.
static void positionedUpdateUsingSubqueryInSetClause()
{
try
{
TbMod_PosCursor posCur;
int dept = 0;
String name = null;
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" UPDATE\n" +
"TO PERFORM A POSITIONED UPDATE ON A TABLE\n" +
"USING A SUBQUERY IN THE 'SET' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
System.out.println();
System.out.println(
" Invoke the statements:\n" +
" DECLARE posCur CURSOR FOR\n" +
" SELECT name, dept FROM staff WHERE id >= 310\n" +
"\n" +
" FETCH :posCur INTO :name, :dept\n" +
" while (successful fetch)\n" +
" {\n" +
" if (dept != 84)\n" +
" {\n" +
" UPDATE staff\n" +
" SET comm = 0.01 * (SELECT AVG(salary)\n"+
" FROM staff\n" +
" WHERE id >= 310)\n" +
" WHERE CURRENT OF :posCur\n" +
" }\n" +
" FETCH :posCur INTO :name, :dept\n" +
" }");
// declare a cursor
#sql posCur = {SELECT name, dept FROM staff WHERE id >= 310};
while (true)
{
// fetch the cursor
#sql {FETCH :posCur INTO :name, :dept};
if (posCur.endFetch())
{
break;
}
if (dept != 84)
{
#sql {UPDATE staff
SET comm = 0.01 * (SELECT AVG(salary)
FROM staff
WHERE id >= 310)
WHERE CURRENT OF :posCur};
}
}
// close the cursor
posCur.close();
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // positionedUpdateUsingSubqueryInSetClause
// This function demonstrates how to perform a positioned update on a table
// using a correlated subquery in the 'SET' clause
static void positionedUpdateUsingCorrelatedSubqueryInSetClause()
{
try
{
TbMod_PosCursor posCur;
int dept = 0;
String name = null;
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" UPDATE\n" +
"TO PERFORM A POSITIONED UPDATE ON A TABLE\n" +
"USING A CORRELATED SUBQUERY IN THE 'SET' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
System.out.println();
System.out.println(
" Invoke the statements:\n" +
" DECLARE posCur CURSOR FOR\n" +
" SELECT name, dept FROM staff WHERE id >= 310\n" +
"\n" +
" FETCH :posCur INTO :name, :dept\n" +
" while (successful fetch)\n" +
" {\n" +
" if (dept != 84)\n" +
" {\n" +
" UPDATE staff s1\n" +
" SET comm = 0.01 * (SELECT AVG(salary)\n" +
" FROM staff s2\n" +
" WHERE id >= 310 AND\n"+
" s2.dept = s1.dept)\n" +
" WHERE CURRENT OF :posCur\n" +
" }\n" +
" FETCH :posCur INTO :name, :dept\n" +
" }");
// declare a cursor
#sql posCur = {SELECT name, dept FROM staff WHERE id >= 310};
while (true)
{
// fetch the cursor
#sql {FETCH :posCur INTO :name, :dept};
if (posCur.endFetch())
{
break;
}
if (dept != 84)
{
#sql {UPDATE staff s1
SET comm = 0.01 * (SELECT AVG(salary)
FROM staff s2
WHERE id >= 310 AND
s2.dept = s1.dept)
WHERE CURRENT OF :posCur};
}
}
// close the cursor
posCur.close();
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // updateUsingCorrelatedSubqueryInSetClause
// This function demonstrates how to delete table data
static void deleteWithoutSubqueries()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" DELETE\n" +
"TO DELETE TABLE DATA.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// delete data from the 'staff' table without subqueries
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" DELETE FROM staff WHERE id >= 310 AND salary > 20000");
#sql {DELETE FROM staff WHERE id >= 310 AND salary > 20000};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // deleteWithoutSubqueries
// This function demonstrates how to delete table data using a
// subquery in the 'WHERE' clause.
static void deleteUsingSubqueryInWhereClause()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" DELETE\n" +
"TO DELETE TABLE DATA\n" +
"USING A SUBQUERY IN THE 'WHERE' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// display a partial content of the 'employee' table
employeeTbPartialContentDisplay();
// delete data from the 'staff' table using a subquery in the 'WHERE'
// clause
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" DELETE FROM staff\n" +
" WHERE id >= 310 AND\n" +
" salary > (SELECT AVG(salary)\n" +
" FROM employee\n" +
" WHERE workdept = 'E11')");
#sql {DELETE FROM staff
WHERE id >= 310 AND
salary > (SELECT AVG(salary)
FROM employee
WHERE workdept = 'E11')};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // deleteUsingSubqueryInWhereClause
// This function demonstrates how to delete table data using a
// correlated subquery in the 'WHERE' clause.
static void deleteUsingCorrelatedSubqueryInWhereClause()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT:\n" +
" DELETE\n" +
"TO DELETE TABLE DATA\n" +
"USING A CORRELATED SUBQUERY IN THE 'WHERE' CLAUSE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
// delete data from the 'staff' table using a correlated subquery in the
// 'WHERE' clause
System.out.println();
System.out.println(
" Invoke the statement:\n" +
" DELETE FROM staff s1\n" +
" WHERE id >= 310 AND\n" +
" salary < (SELECT AVG(salary)\n" +
" FROM staff s2\n" +
" WHERE s2.dept = s1.dept)");
#sql {DELETE FROM staff s1
WHERE id >= 310 AND
salary < (SELECT AVG(salary)
FROM staff s2
WHERE s2.dept = s1.dept)};
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // TbDeleteUsingCorrelatedSubqueryInWhereClause
// This function demonstrates how to perform a positioned delete on a table
static void positionedDelete()
{
try
{
TbMod_PosCursor posCur;
int dept = 0;
String name = null;
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" DELETE\n" +
"TO PERFORM A POSITIONED DELETE ON A TABLE.");
// display the final content of the 'staff' table
staffTbContentDisplay();
System.out.println();
System.out.println(
" Invoke the statements:\n" +
" DECLARE posCur CURSOR FOR\n" +
" SELECT name, dept FROM staff WHERE id >= 310\n" +
"\n" +
" FETCH :posCur INTO :name, :dept\n" +
" while (successful fetch)\n" +
" {\n" +
" if (dept != 84)\n" +
" {\n" +
" DELETE FROM staff WHERE CURRENT OF :posCur\n" +
" }\n" +
" FETCH :posCur INTO :name, :dept\n" +
" }");
// declare a cursor
#sql posCur = {SELECT name, dept FROM staff WHERE id >= 310};
while (true)
{
// fetch the cursor
#sql {FETCH :posCur INTO :name, :dept};
if (posCur.endFetch())
{
break;
}
if (dept != 84)
{
#sql {DELETE FROM staff WHERE CURRENT OF :posCur};
}
}
// close the cursor
posCur.close();
// display the final content of the 'staff' table
staffTbContentDisplay();
// roll back the transaction
System.out.println();
System.out.println(" Roll back the transaction");
#sql {ROLLBACK};
System.out.println(" Rollback Done.");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // positionedDelete
} // TbMod