A JDBC tutorial for executing basic SQL statements like INSERT, SELECT,UPDATE and DELETE.
Gets the number of rows in a result. For INSERT, UPDATE and DELETE statements odbc_num_rows() returns the number of rows affected. For a SELECT clause this can be.
An SQL statement can be executed in two ways. One way is to use the java.sql.Statement interface. The Statement object can be reused to execute completely. Basic Syntax. Examples in this section demonstrate the basic functionality of the UPDATE statement using the minimum required syntax. A. Using a simple UPDATE statement.
Tuning PL/SQL Applications for Performance. PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the results to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL features that are known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches, rather than one at a time. The BULKCOLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, bulk SQL can improve performance considerably.
Assigning values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into these categories: Bulk SQL uses PL/SQL collections to pass large amounts of data back and forth in single operations. This process is called bulk binding. If the collection has n elements, bulk binding uses a single operation to perform the equivalent of n. SELECTINTO, INSERT, UPDATE, or DELETE statements.
A query that uses bulk binding can return any number of rows, without requiring a FETCH statement for each one. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a LOOP statement.
To speed up SELECTINTO statements, include the BULKCOLLECT clause. Running One DML Statement Multiple Times (FORALL Statement)The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general- purpose FOR loop. For full syntax and restrictions, see FORALL Statement. The SQL statement can reference more than one collection, but FORALL only improves performance where the index value is used as a subscript.
Usually, the bounds specify a range of consecutive index numbers. If the index numbers are not consecutive, such as after you delete collection elements, you can use the INDICESOF or VALUESOF clause to iterate over just those index values that really exist.
The INDICESOF clause iterates over all of the index values in the specified collection, or only those between a lower and upper bound. The VALUESOF clause refers to a collection that is indexed by PLS_INTEGER and whose elements are of type PLS_INTEGER. The FORALL statement iterates over the index values specified by the elements of this collection. The FORALL statement in Example 1.
DELETE statements to the SQL engine at once. Example 1. 2- 2 Issuing DELETE Statements in a Loop. CREATE TABLE employees_temp AS SELECT * FROM employees. TYPE Num. List IS VARRAY(2. OF NUMBER. depts Num. List : = Num. List(1. FORALL i IN depts.
FIRST. depts. LAST. DELETE FROM employees_temp WHERE department_id = depts(i). Example 1. 2- 3 loads some data into PL/SQL collections. Then it inserts the collection elements into a database table twice: first using a FOR loop, then using a FORALL statement.
The FORALL version is much faster. Example 1. 2- 3 Issuing INSERT Statements in a Loop.
CREATE TABLE parts. INTEGER, pname VARCHAR2(1. CREATE TABLE parts. INTEGER, pname VARCHAR2(1. TYPE Num. Tab IS TABLE OF parts. TYPE INDEX BY PLS_INTEGER.
TYPE Name. Tab IS TABLE OF parts. TYPE INDEX BY PLS_INTEGER. Num. Tab. pnames Name. Tab. iterations CONSTANT PLS_INTEGER : = 5. FOR j IN 1. iterations LOOP - - load index- by tables. Part No. ' || TO_CHAR(j).
DBMS_UTILITY. get_time. FOR i IN 1. iterations LOOP - - use FOR loop. INSERT INTO parts. VALUES (pnums(i), pnames(i)). DBMS_UTILITY. get_time. FORALL i IN 1. iterations - - use FORALL statement. INSERT INTO parts.
VALUES (pnums(i), pnames(i)). DBMS_UTILITY. get_time. DBMS_OUTPUT. PUT_LINE('Execution Time (secs)'). DBMS_OUTPUT. PUT_LINE('- -- -- -- -- -- -- -- -- -- -- '). DBMS_OUTPUT. PUT_LINE('FOR loop: ' || TO_CHAR((t. DBMS_OUTPUT. PUT_LINE('FORALL: ' || TO_CHAR((t. Executing this block shows that the loop using FORALL is much faster.
The bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements, as shown in Example 1. Example 1. 2- 4 Using FORALL with Part of a Collection. CREATE TABLE employees_temp AS SELECT * FROM employees. TYPE Num. List IS VARRAY(1.
OF NUMBER. depts Num. List : = Num. List(5,1. FORALL j IN 4. 7 - - use only part of varray. DELETE FROM employees_temp WHERE department_id = depts(j). You might need to delete some elements from a collection before using the collection in a FORALL statement.
The INDICESOF clause processes sparse collections by iterating through only the remaining elements. You might also want to leave the original collection alone, but process only some elements, process the elements in a different order, or process some elements more than once. Instead of copying the entire elements into new collections, which might use up substantial amounts of memory, the VALUESOF clause lets you set up simple collections whose elements serve as pointers to elements in the original collection. Example 1. 2- 5 creates a collection holding some arbitrary data, a set of table names. Deleting some of the elements makes it a sparse collection that does not work in a default FORALL statement. The program uses a FORALL statement with the INDICESOF clause to insert the data into a table. It then sets up two more collections, pointing to certain elements from the original collection.
The program stores each set of names in a different database table using FORALL statements with the VALUESOF clause. Example 1. 2- 5 Using FORALL with Nonconsecutive Index Values. Create empty tables to hold order details. CREATE TABLE valid_orders (cust_name VARCHAR2(3. NUMBER(1. 0,2)). CREATE TABLE big_orders AS SELECT * FROM valid_orders. WHERE 1 = 0. CREATE TABLE rejected_orders AS SELECT * FROM valid_orders. Collections for set of customer names & order amounts.
SUBTYPE cust_name IS valid_orders. TYPE. TYPE cust_typ IS TABLe OF cust_name. SUBTYPE order_amount IS valid_orders.
TYPE. TYPE amount_typ IS TABLE OF NUMBER. Collections to point into CUST_TAB collection. TYPE index_pointer_t IS TABLE OF PLS_INTEGER. PROCEDURE setup_data IS BEGIN. Set up sample order data. Company. 1','Company.
Company. 3','Company. Company. 5'). amount_tab : = amount_typ(5. NULL). setup_data().
DBMS_OUTPUT. PUT_LINE. Original order data - -- '). FOR i IN 1. cust_tab. LAST LOOP. DBMS_OUTPUT.
PUT_LINE. ('Customer #' || i || ', ' || cust_tab(i) || ': $' ||. Delete invalid orders (where amount is null or 0). FOR i IN 1. cust_tab. LAST LOOP. IF amount_tab(i) is null or amount_tab(i) = 0 THEN.
DBMS_OUTPUT. PUT_LINE. Data with invalid orders deleted - -- '). FOR i IN 1. cust_tab. LAST LOOP. IF cust_tab. EXISTS(i) THEN. DBMS_OUTPUT. PUT_LINE('Customer #' || i || ', ' ||. Because subscripts of collections are not consecutive.
FORALL.. INDICES OF to iterate through actual subscripts. COUNT. FORALL i IN INDICES OF cust_tab. INSERT INTO valid_orders(cust_name, amount). VALUES(cust_tab(i), amount_tab(i)). Now process the order data differently. Extract 2 subsets and store each subset in a different table. Initialize the CUST_TAB and AMOUNT_TAB collections again.
FOR i IN cust_tab. FIRST . cust_tab. LAST LOOP. IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN.
Add a new element to this collection. EXTEND. - - Record the subscript from the original collection.
LAST) : = i. IF amount_tab(i) > 2. THEN. - - Add a new element to this collection. EXTEND. - - Record the subscript from the original collection.
LAST) : = i. - - Now it's easy to run one DML statement. DML statement on a different subset.
FORALL i IN VALUES OF rejected_order_tab. INSERT INTO rejected_orders. VALUES (cust_tab(i), amount_tab(i)). FORALL i IN VALUES OF big_order_tab. INSERT INTO big_orders.
VALUES (cust_tab(i), amount_tab(i)). Verify that the correct order details were stored. SELECT cust_name "Customer". Valid order amount" FROM valid_orders. SELECT cust_name "Customer". Big order amount" FROM big_orders. SELECT cust_name "Customer".
Rejected order amount" FROM rejected_orders. Topics: How FORALL Affects Rollbacks.
In a FORALL statement, if any execution of the SQL statement raises an unhandled exception, all database changes made during previous executions are rolled back. However, if a raised exception is caught and handled, changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back. For example, suppose you create a database table that stores department numbers and job titles, as shown in Example 1. Then, you change the job titles so that they are longer.
The second UPDATE fails because the new value is too long for the column.