tulisa dating skins - Oracle updating cursor

CREATE TABLE employees_temp AS SELECT employee_id, first_name, last_name FROM employees; DECLARE emp_id employees_temp.employee_id%TYPE; emp_first_name employees_temp.first_name%TYPE; emp_last_name employees_temp.last_name%TYPE; BEGIN INSERT INTO employees_temp VALUES(299, 'Bob', 'Henry'); UPDATE employees_temp SET first_name = 'Robert' WHERE employee_id = 299; DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name; COMMIT; DBMS_OUTPUT.

SQL SELECT * FROM employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 10000 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 10000 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 10000 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 10000 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 10000 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 10000 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 10000 New York Manager 08 James Cat 17-SEP-96 15-APR-02 10000 Vancouver Tester 8 rows selected.

This clause can also be extended to lock only the intended table in the SELECT query containing more than one table in joins using the FOR UPDATE OF clause.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows: WHERE [CURRENT OF cursor_name | search_condition] The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

When the Fetch clause is used with a cursor having a FOR UPDATE clause, the PL/SQL unit fails when we try to open the cursor with an ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error description shows that the internal mechanism for the Fetch clause uses either a DISTINCT or a GROUP BY clause which is not permitted alongside the FOR UPDATE clause.

By extending SQL, PL/SQL offers a unique combination of power and ease of use.

This clause becomes meaningless if we use a single table SELECT clause as the minimum Oracle has provided us with the WHERE CURRENT OF clause for both DELETE and UPDATE statements inside a cursor’s range to make changes to the last fetched row(s) from the cursor with an ease.

When we want to update or delete the cursor fetched row(s) from the database, we don’t have to form In the below code listing, the employees joined before the year 2000 are archived and during the cursor process, no other session is permitted to make any changes to those employees using the FOR UPDATE clause.

The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.

Another Example of WHERE CURRENT OF Go through all Maths students and set all GPA’s under 4.0 to 4.0!

For information on the use of PL/SQL records with SQL to update and insert data, see "Inserting PL/SQL Records into the Database" and "Updating the Database with PL/SQL Record Values".

Tags: , ,