SQL语句PART7

来源：互联网发布：虚拟代理软件编辑：程序博客网时间：2024/05/01 02:56

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    督察督办     SUPERVISAL
6    2    值班快报     DAILYREPORT
7    2    ABC           NOTIFY
merge into temp_t2 t2
USING temp_t1 t1
ON (t1.ndocsortid=t2.ndocsortid)
WHEN MATCHED THEN
UPDATE SET
t2.cname = t1.cname
WHEN NOT MATCHED THEN
INSERT VALUES(t1.ndocsortid,t1.nmoduleinfoid,t1.cname,t1.key)
** results: 9 rows merged. and the result of temp_t2 is the same as temp_t1.

Tracking changes in Data
E.G.: table temp_t1:
select * from temp_t1 where ndocsortid = 7;
7    2    信访快报     NOTIFY
update temp_t1 set cname='abc' where ndocsortid = 7;
commit;
update temp_t1 set cname='我的事情‘ where ndocsortid = 7;
commit;
select * from temp_t1 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE where ndocsortid = 7;
7    2    我的事情    NOTIFY
7    2    ABC      NOTIFY
7    2    信访快报     NOTIFY

TimeZone may be set to : 1) An absolute offset 2) Database time zone 3) OS local time zone 4) A named region
CURRENT_TIME:               1) returns the current date from the user session. 2) has a data type of DATE
CURRENT_TIMESTAMP:   1) returns the current date and time from the user session. 2) has a data type of                                                        TIMESTAMP WITH TIME ZONE.
LOCALTIMESTAMP:           1) returns the current date and time from the user session. 2) has a data type of                                                     TIMESTAMP
e.g.:
SELECT DBTIMEZONE FROM DUAL;    // value of database timezone
select sessiontimezone from dual;    //value of session time zone
** timestamp with time zone,
** timestamp with local time zone;

Interval Data Types:
1. INTERVAL data types are used to store the difference between two datetime values.
2. There are two classes of intervals: 1) Year-month 2) Day-time
3. The precision of the interval is: 1) The actual subset of fields that constitutes an interval 2)Specified in the interval qualifier
INTERVAL DAY TO SECOND: Days, Hour, Minute, Second with fractional seconds
INTERVAL YEAR TO MONTH: Year, Month
e.g.:
create table temp_t1(nid number, currt interval year(3) to month)
INSERT INTO temp_t1 VALUES (123, INTERVAL '8' MONTH);
INSERT INTO temp_t1 VALUES (155, INTERVAL '200' YEAR(3));
INSERT INTO temp_t1 VALUES (678, '200-11');
SELECT * FROM temp_t1
** results:
123    +00-08
144   +200-00
678    +200-11
select currt,extract(year from currt)||' years,'||extract(month from currt)||' monthes ' duration from temp_t1;
** results:
+00-08        0 years, 8 monthes
+200-00    200 years, 0 monthes
+200-11    200 years, 11 monthes
e.g.:
CREATE TABLE temp_t1(tid number, test_time INTERVAL DAY(2) TO SECOND);
INSERT INTO temp_t1 VALUES (100012, '90 00:00:00');
INSERT INTO temp_t1 VALUES (56098, INTERVAL '6 03:30:16' DAY TO SECOND);
select * from temp_t1
results:
100012    +90 00:00:00.000000
56098      +06 03:30:16.000000
select
       test_time,
       extract(day from test_time)||' days,'||extract(hour from test_time)||' hours,'
       ||extract(minute from test_time)||' minutes,'
       ||extract(second from test_time)||' seconds ' duration from temp_t1;
** results:
+90 00:00:00.000000    90 days, 0 hours, 0 minutes, 0 seconds
+06 03:30:16.000000    6 days, 3 hours, 30 minutes, 16 seconds

---------------------------------------------------------------------------------------------------------------------------------------------------

在SQL和PL/SQL中，你都可以用时间间隔型数据，它们都是用同一种方式规定的：

INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

对于精确数值，规定有缺省值：年和日是两位数，若干分之一秒是六位数。

时间间隔的大小由INTERVAL来表示，后面紧接一个放在单引号中的表达式，以及用来解释该表达式的文字。用YEAR TO MONTH表示时间间隔大小时要在年和月之间用一个连字符(-) 连接。而DAY TO SECOND表示时间间隔大小时要在日和时间之间用一个空格连接。举个例子来说，下面是2年6个月的时间间隔的表示方法：

INTERVAL '2-6' YEAR TO MONTH

下面的例子表示3天12个小时30分钟6.7秒：

INTERVAL '3 12:30:06.7' DAY TO SECOND(1)

时间间隔可以为正，也可以为负。它们可以从各种TIMESTAMP数据类型中加上或者减去，从而得到一个新的TIMESTAMP数据类型。它们之间也可以做加减运算得到新的时间间隔。

列表A说明了怎样创建一个表格来记录一个事件的开始时间和持续时间，如实验等。数据被收集以后，SQL中内置的摘要函数不需要与原始单位秒进行相互转换，就可以报告总的持续时间和平均持续时间。

列表A

CREATE TABLE experiment
(experiment_id        NUMBER(9),
   experiment_desc      VARCHAR2(80),
   experiment_start     TIMESTAMP,
   experiment_duration INTERVAL DAY(1) TO SECOND(4)
);

Table created.

INSERT INTO experiment
VALUES (
  1, 'Busted urban myth', '01-JUN-2006 02:00:00 PM',
INTERVAL '1 2:31:15.1250' DAY(1) TO SECOND(4)
);

1 row created.

col experiment_desc format a40
col experiment_start format a30
col experiment_duration format a20

SELECT * FROM experiment;

EXPERIMENT_ID EXPERIMENT_DESC                                                   ------------- ----------------------------------------                          EXPERIMENT_START               EXPERIMENT_DURATION                              ------------------------------ --------------------                                         1 Busted urban myth                                                 01-JUN-06 02.00.00.000000 PM   +1 02:31:15.1250
-- Now compute the experiment's ending time

SELECT experiment_id, experiment_start,
       experiment_start + experiment_durationexperiment_end
FROM experiment;

EXPERIMENT_ID EXPERIMENT_START                                                  ------------- ------------------------------                                    EXPERIMENT_END                                                                  ---------------------------------------------------------------------------                 1 01-JUN-06 02.00.00.000000 PM                                      02-JUN-06 04.31.15.125000000 PM

但遗憾的是， TO_CHAR函数中没有包括任何能够映射到各个时间间隔数据类型片段的格式模型。但是，你可以用新的EXTRACT函数来提取和合并这些片段。格式如下：

EXTRACT(timepart FROM interval_expression)

列表B给出了一个运用这种方法的例子。

列表B

SELECT EXTRACT(DAY FROM experiment_duration) ||
     ' days, ' || EXTRACT (HOUR FROM experiment_duration) ||
     ' hours, ' || EXTRACT (MINUTE FROM experiment_duration) ||
     ' minutes' Duration
FROM experiment;

DURATION
--------------------------------------------------------------------------------
1 days, 2 hours, 31 minutes                                               首先，从experiment_duration列中将天数提取出来，文字“Days”是与之相联的。对于实验持续时间中的小时和分钟部分，操作与上述方法一样

TIME FUNCTIONS: EXTRACT, TZ_OFFSET, FROM_TZ,TO_TIMESTAMP, TO_YMINTERVAL, TO_DSINTERVAL
e.g.:
SELECT TZ_OFFSET('US/Eastern'),TZ_OFFSET('Canada/Yukon'),TZ_OFFSET('Europe/London') FROM DUAL;
results:
-04:00    -07:00    +01:00
SELECT FROM_TZ(TIMESTAMP '2000-07-12 08:00:00', 'Australia/North') FROM DUAL;
results:
2000-7-12 8:00:00.000000000 +09:30
Display the TIMESTAMP value '2000-03-28 08:00:00' as a TIMESTAMP WITH TIME ZONE value for the 'Australia/North' time zone region.
SELECT TO_TIMESTAMP ('2007-03-06 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
results:
2007-3-6 11:00:00.000000000
select dwrite, dwrite + to_yminterval('01-01') as currvalue from wf_msg where nmsgid=2251
results:
2008-5-9 17:53:04        2009-6-9 17:53:04
select nmsgid,to_char(dwrite,'yyyy-MM-dd hh24:mi:ss') as dw,to_char(dwrite + to_dsinterval('100 10:00:00'),'yyyy-MM-dd hh24:mi:ss') as d1 from wf_msg where nmsgid=2251
results:
2251    2008-05-09 17:53:04        2008-08-18 03:53:04