SQLÓï¾äPART7
Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in data warehousing applications.
syntax:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col1_val,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
e.g.: table: temp_t1:
results:
1 1 ÊÕÎÄ SHOUWEN
2 1 ·¢ÎÄ FAWEN
3 1 ½»°ì JIAOBAN
4 2 Öµ°à±¨¸æ REPORT
5 3 ¶½²ì¶½°ì SUPERVISAL
6 2 Öµ°à¿ì±¨ DAILYREPORT
7 2 ÎÒµÄÊÂÇé NOTIFY
8 4 Ìá°¸ RESOLUTION
9 4 ½¨Òé SUGGESTION
e.g.: table: temp_t2£º
results:
1 1 ÊÕÎÄ SHOUWEN
2 1 ·¢ÎÄ FAWEN
3 1 ½»°ì JIAOBAN
4 2 Öµ°à±¨¸æ REPORT
5 3 ¶½²ì¶½°ì  
Ïà¹ØÎĵµ£º
µÚ¶þÊ®Ì⣺
ÔõôÑù³éÈ¡ÖØ¸´¼Ç¼
񡜧
id name
--------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6
²é³öËùÓÐÓÐÖØ¸´¼Ç¼µÄÊý¾Ý£¬ÓÃÒ»¾äsql À´ÊµÏÖ
create table D(
id varchar (20),
name varchar (20)
)
insert into D values('1','test1')
insert into D v ......
PL/SQL: OracleÖеÄSQL¹ý³Ì»¯±à³ÌÓïÑÔ
1. PL/SQL³ÌÐòÊÇÓÉ¿é½á¹¹¹¹³É£¬¸ñʽÈçÏ£º
[DECLARE
--ÉùÃ÷²¿·Ö
¡¡]
BEGIN
-- Ö÷Ìå
[EXCEPTION
-- Òì³£´¦Àí¿é
]
END;
/ --¿éµÄ½áÊø
2.±äÁ¿
¼òµ¥±äÁ¿£º ......
4¡¢¶ÔÏóÒÀÀµÐÔ
CREATE OR REPLACE TYPE Obj1 AS OBJECT (
f1 NUMBER,
f2 VARCHAR2(10),
f3 DATE
);
/
CREATE OR REPLACE TYPE Obj2 AS OBJECT (
f1 DATE,
f2 CHAR(1)
);
/
CREATE OR REPLACE TYPE Obj3 AS OBJECT (
a Obj1,
b Obj2
);
/
OBJ3ÒÀÀµÓÚOBJ ......
7¡¢¶ÔÏóÀàÐͼ̳Ð
¶ÔÏóÀàÐÍʵÏÖÔÊÐíÎÒÃÇ´´½¨Ò»¸ö»ùÀàÐÍ£¬»ò½Ð¸¸ÀàÐÍ£¬ÕâÖÖÀàÐ͵ÄÊôÐÔ»ò·½·¨¿ÉÒÔ±»ÁíÒ»¸ö¶ÔÏóÀàÐͼ̳С£È»ºó¿ÉÒÔ´´½¨Ò»¸ö×ÓÀàÐÍ£¬»ò½Ðº¢×ÓÀàÐÍ£¬Ö±½ÓʹÓü̳йýÀ´µÄÊôÐÔ»ò·½·¨£¬»òÕßÓÃ×Ô¼ºµÄÊôÐԺͷ½·¨ÖØÐ´¸¸ÀàÐ͵ÄÊôÐÔ»ò·½·¨¡£
INSTANTIABLE¹Ø¼ü×Ö±íʾÎÒÃÇ¿ÉÒÔ´Ó¸ÃÀàÐÍÖÐʵÀý»¯»òÕß´´½¨¶ÔÏ ......
oracle ·½·¨ & ......