我用下面的语句 update之后UPDATE (
select cn_position_number from tdm_links_00376 E where E.rowid >
(
Select MIN(X.rowid) from SOXI200610.tdm_links_00376 X where
(X.cn_position_number = E.cn_position_number) and (X.Object_Id1 = E.Object_Id1)
and (X.TDM_PROD_VIEW_CNTXT = E.TDM_PROD_VIEW_CNTXT)
)
)
SET CN_POSITION_NUMBER = null再用 select 查询 查出来的记录为0 为什么?
select cn_position_number from tdm_links_00376 t
where t.cn_position_number is null
请各位高手帮帮忙
select cn_position_number from tdm_links_00376 E where E.rowid >
(
Select MIN(X.rowid) from SOXI200610.tdm_links_00376 X where
(X.cn_position_number = E.cn_position_number) and (X.Object_Id1 = E.Object_Id1)
and (X.TDM_PROD_VIEW_CNTXT = E.TDM_PROD_VIEW_CNTXT)
)
)
SET CN_POSITION_NUMBER = null再用 select 查询 查出来的记录为0 为什么?
select cn_position_number from tdm_links_00376 t
where t.cn_position_number is null
请各位高手帮帮忙
1. Using UPDATE with Columns
SQL> --Using UPDATE with TYPEed Columns
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5));
6 /Type created.SQL>
SQL> CREATE TABLE emp (empno NUMBER(3),
2 name VARCHAR2(20),
3 address ADDRESS_OBJ);Table created.SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, 'Adam', ADDRESS_OBJ('1 A St.','Mobile','AL','36608'));1 row created.SQL>
SQL> COLUMN name FORMAT a9
SQL> COLUMN empno FORMAT 999999
SQL> COLUMN address FORMAT a50
SQL>
SQL> UPDATE emp e
2 SET e.address.zip = '34210'
3 WHERE e.address.city LIKE 'Mobile%';1 row updated.SQL>
SQL>
SQL>
SQL> SELECT name, e.address.city FROM emp e WHERE e.address.state = 'AL';NAME ADDRESS.CITY
--------- --------------------
Adam MobileSQL>
SQL> drop table emp;Table dropped.SQL>
SQL>2. UPDATE Data in a Table of Row Objects
SQL> -- UPDATE Data in a Table of Row Objects
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5));
6 /Type created.SQL>
SQL> CREATE TABLE address_table OF ADDRESS_OBJ;Table created.SQL>
SQL> INSERT INTO address_table VALUES ('4 D St.', 'Gulf Breeze','FL','32563');1 row created.SQL>
SQL> SELECT *
2 FROM address_table;STREET CITY ST ZIP
-------------------- -------------------- -- -----
4 D St. Gulf Breeze FL 32563
SQL>
SQL>
SQL>
SQL> UPDATE address_table
2 SET zip = 32514
3 WHERE zip = 32504;0 rows updated.SQL>
SQL> UPDATE address_table
2 SET street = '11 A Dr'
3 WHERE city LIKE 'Gulf%';1 row updated.SQL>
SQL>
SQL> SELECT *
2 FROM address_table;STREET CITY ST ZIP
-------------------- -------------------- -- -----
11 A Dr Gulf Breeze FL 32563
SQL>
SQL>
SQL> drop table address_table;Table dropped.SQL>
SQL>3. UPDATE a Table that Contains Row Objects (TCRO) SQL> -- UPDATE a Table that Contains Row Objects (TCRO)
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address_obj as OBJECT(
2 street VARCHAR2(20),
3 city VARCHAR2(20),
4 state CHAR(2),
5 zip CHAR(5));
6 /Type created.SQL>
SQL> CREATE TABLE address_table OF ADDRESS_OBJ;Table created.SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
2 address REF address_obj scope is address_table);Table created.SQL>
SQL>
SQL>
SQL> INSERT INTO client VALUES ('Jones',null);1 row created.SQL>
SQL>
SQL> UPDATE client SET address =
2 (SELECT REF(aa)
3 FROM address_table aa
4 WHERE aa.city LIKE 'Mob%')
5 WHERE name = 'Jones';1 row updated.SQL>
SQL>
SQL> SELECT * FROM client;NAME ADDRESS
--------- --------------------------------------------------
JonesSQL>
SQL>
SQL>
SQL> drop table client;Table dropped.SQL>
SQL> drop table address_table;Table dropped.SQL>
SQL>
SQL>