1、Update Object Table
UPDATE
SET VALUE () = (
SET VALUE (
CREATE TYPE people_typ AS OBJECT (
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2));
/
eg:
last_name VARCHAR2(25),
department_id NUMBER(4),
salary NUMBER(8,2));
/
eg:
CREATE TABLE people_demo1 OF people_typ;
desc people_demo1
CREATE TABLE people_demo2 OF people_typ;
desc people_demo2
INSERT INTO people_demo1
VALUES (people_typ('Morgan', 10, 100000));
INSERT INTO people_demo2
VALUES (people_typ('Morgan', 10, 150000));
UPDATE people_demo1 p
SET VALUE(p) = (
SELECT VALUE(q) FROM people_demo2 q
WHERE p.department_id = q.department_id)
WHERE p.department_id = 10;
SELECT * FROM people_demo1;
2、Record Update
UPDATE
SET ROW =
WHERE ;
SET ROW =
WHERE
eg:
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;
SELECT DISTINCT tablespace_name
FROM t;
DECLARE
trec t%ROWTYPE;
BEGIN
trec.table_name := 'DUAL';
trec.tablespace_name := 'NEW_TBSP';
UPDATE t
SET ROW = trec
WHERE table_name = 'DUAL';
COMMIT;
END;
/
SELECT DISTINCT tablespace_name
FROM t;
SELECT table_name, tablespace_name
FROM all_tables;
SELECT DISTINCT tablespace_name
FROM t;
DECLARE
trec t%ROWTYPE;
BEGIN
trec.table_name := 'DUAL';
trec.tablespace_name := 'NEW_TBSP';
UPDATE t
SET ROW = trec
WHERE table_name = 'DUAL';
COMMIT;
END;
/
SELECT DISTINCT tablespace_name
FROM t;
3、Update Partitioned Table
UPDATE PARTITION ()
SET =
WHERE ;
SET
WHERE
eg:
conn sh/sh
UPDATE sales PARTITION (sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;
UPDATE sales PARTITION (sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-691878/，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
