2008-05-27: UPDATE Statement (2008-05-07)

最新推荐文章于 2022-06-07 18:16:54 发布

fibbery

最新推荐文章于 2022-06-07 18:16:54 发布

阅读量634

点赞数

分类专栏： Oracle 文章标签： subquery returning table object collections variables

Oracle 专栏收录该内容

9 篇文章

订阅专栏

UPDATE Statement

The UPDATE statement changes the values of specified columns in one or more rows in a table or view. For a full description of the UPDATE statement, see Oracle Database SQL Reference.

Syntax

Description of update_statement.gif follows

Description of the illustration update_statement.gif

Keyword and Parameter Description

alias

Another (usually short) name for the referenced table or view, typically used in the WHERE clause.

column_name

The column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the column_name list. Column names need not appear in the UPDATE statement in the same order that they appear in the table or view.

returning_clause

Returns values from updated rows, eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables or host variables, or into collections or host arrays. You cannot use the RETURNING clause for remote or parallel updates. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined. For the syntax of returning_clause, see "DELETE Statement".

SET column_name = sql_expression

This clause assigns the value of sql_expression to the column identified by column_name. If sql_expression contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.

SET column_name = (subquery3)

Assigns the value retrieved from the database by subquery3 to the column identified by column_name. The subquery must return exactly one row and one column.

SET (column_name, column_name, ...) = (subquery4)

Assigns the values retrieved from the database by subquery4 to the columns in the column_name list. The subquery must return exactly one row that includes all the columns listed.

The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on.

The following example creates a table with correct employee IDs but garbled names. Then it runs an UPDATE statement with a correlated query, to retrieve the correct names from the EMPLOYEES table and fix the names in the new table.

-- Create a table with all the right IDs, but messed-up names.
CREATE TABLE e1 AS
   SELECT employee_id,
          UPPER(first_name) first_name,
          TRANSLATE(last_name,'aeiou','12345') last_name
   FROM employees;

BEGIN
-- Display the first 5 names to show they're messed up.
   FOR person IN (SELECT * FROM e1 WHERE ROWNUM < 6)
   LOOP
      dbms_output.put_line(person.first_name || ' ' || person.last_name);
   END LOOP;
   UPDATE e1 SET (first_name, last_name) =
      (SELECT first_name, last_name FROM employees
         WHERE employee_id = e1.employee_id);
   dbms_output.put_line('*** Updated ' || SQL%ROWCOUNT || ' rows. ***');
-- Display the first 5 names to show they've been fixed up. 
   FOR person IN (SELECT * FROM e1 WHERE ROWNUM < 6)
   LOOP
      dbms_output.put_line(person.first_name || ' ' || person.last_name);
   END LOOP;
END;
/

DROP TABLE e1;

sql_expression

Any valid SQL expression. For more information, see Oracle Database SQL Reference.

subquery

A SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".

table_reference

A table or view that must be accessible when you execute the UPDATE statement, and for which you must have UPDATE privileges. For the syntax of table_reference, see "DELETE Statement".

TABLE (subquery2)

The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

WHERE CURRENT OF cursor_name

Refers to the latest row processed by the FETCH statement associated with the specified cursor. The cursor must be FOR UPDATE and must be open and positioned on a row.

If the cursor is not open, the CURRENT OF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

WHERE search_condition

Chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit this clause, all rows in the table are updated.

Usage Notes

You can use the UPDATE WHERE CURRENT OF statement after a fetch from an open cursor (including fetches done by a cursor FOR loop), provided the associated query is FOR UPDATE. This statement updates the row that was just fetched.

The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an UPDATE statement.

Examples

The following example demonstrates how to update table rows based on conditions, and how to store the updated values, columns, or entire rows in PL/SQL variables:

-- Create some rows with values in all caps like (EMPLOYEES,TABLE)
-- and (EMP_JOB_IX,INDEX).
CREATE TABLE my_objects AS SELECT object_name, object_type FROM user_objects;

DECLARE
   my_name my_objects.object_name%TYPE;
   my_type my_objects.object_type%TYPE;
   TYPE name_typ IS TABLE OF my_objects.object_name%TYPE INDEX BY PLS_INTEGER;
   TYPE type_typ IS TABLE OF my_objects.object_type%TYPE INDEX BY PLS_INTEGER;
   all_names name_typ;
   all_types type_typ;
   TYPE table_typ IS TABLE OF my_objects%ROWTYPE INDEX BY PLS_INTEGER;
   all_rows table_typ;
BEGIN
-- Show the first 10 rows as they originally were.
   FOR obj IN (SELECT * FROM my_objects WHERE ROWNUM < 11)
   LOOP
      dbms_output.put_line('Name = ' || obj.object_name || ', type = ' ||
         obj.object_type);
   END LOOP;
   UPDATE my_objects SET object_name = LOWER(object_name)
       WHERE object_type = 'TABLE';
   dbms_output.put_line('*** Updated ' || SQL%ROWCOUNT || ' rows. ***');
-- Show the first 10 rows after the update.
-- Only some of the names (the table names) have been changed to lowercase.
   FOR obj IN (SELECT * FROM my_objects WHERE ROWNUM < 11)
   LOOP
      dbms_output.put_line('Name = ' || obj.object_name || ', type = ' ||
         obj.object_type);
   END LOOP;

-- Update a single row, and store the values of updated (or unchanged)
-- columns in variables.
   UPDATE my_objects SET object_name = INITCAP(object_name)
      WHERE object_name = 'employees'
      RETURNING object_name, object_type INTO my_name, my_type;

   dbms_output.put_line('*** Updated ' || SQL%ROWCOUNT || ' rows. ***');
   dbms_output.put_line('Affected this row: ' || my_name || ', ' || my_type);

-- Update many rows, storing the values of updated (or unchanged)
-- columns in collections of records. Can't use 'RETURNING *', have
-- to list the columns individually.
   UPDATE my_objects SET object_name = INITCAP(object_name)
      WHERE object_type IN ('TRIGGER','VIEW','SEQUENCE')
      RETURNING object_name, object_type BULK COLLECT INTO all_rows;

   dbms_output.put_line('*** Updated ' || SQL%ROWCOUNT || ' rows. ***');
   FOR i IN all_rows.FIRST .. all_rows.LAST
   LOOP
      dbms_output.put_line('Affected this row: ' || all_rows(i).object_name || ', ' || all_rows(i).object_type);
   END LOOP;

-- Update many rows, storing the values of updated (or unchanged)
-- columns in separate collections. (Generally less useful than using
-- collections of records as above.)
   UPDATE my_objects SET object_name = INITCAP(object_name)
      WHERE object_type IN ('INDEX','PROCEDURE')
      RETURNING object_name, object_type BULK COLLECT INTO all_names, all_types;

   dbms_output.put_line('*** Updated ' || SQL%ROWCOUNT || ' rows. ***');
   FOR i IN all_names.FIRST .. all_names.LAST
   LOOP
      dbms_output.put_line('Affected this row: ' || all_names(i) || ', ' || all_types(i));
   END LOOP;

END;
/

DROP TABLE my_objects;