Oracle WMSYS Functions

風靈使發表於2019-04-06
OracleFunction
Oracle WMSYS Functions
Version 11.2
GENERAL
Data Types CREATE OR REPLACE TYPE wm_period AS OBJECT (validfrom TIMESTAMP WITH TIME ZONE,validtill TIMESTAMP WITH TIME ZONE);
Security conn / as sysdba
ALTER USER wmsys ACCOUNT UNLOCK IDENTIFIED BY wmsys;
WM_CONCAT
交叉表逗號分隔的列表 WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2
CREATE TABLE t (
col1 VARCHAR2(5),
col2 VARCHAR2(20));
INSERT INTO t VALUES (111, ‘This’);
INSERT INTO t VALUES (111, ‘is’);
INSERT INTO t VALUES (111, ‘a’);
INSERT INTO t VALUES (111, ‘test’);
INSERT INTO t VALUES (222, ‘This is not’);
SELECT * FROM t;
col concat format a40
SELECT col1, wmsys.wm_concat(col2) CONCAT
FROM t
GROUP BY col1;
SELECT col1, TRANSLATE(wmsys.wm_concat(col2), ‘A,’, 'A ') CONCAT
FROM t
GROUP BY col1;
WM_CONTAINS
檢查第一個日期期間是否包含第二日期個期間 WM_CONTAINS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_CONTAINS’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_CONTAINS’) FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘05-JAN-2009’);
p2b DATE := TO_DATE(‘01-JAN-2009’);
p2e DATE := TO_DATE(‘03-JAN-2009’);
BEGIN
SELECT WM_CONTAINS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE(‘02-JAN-2009’);
p1e := TO_DATE(‘06-JAN-2009’);
SELECT WM_CONTAINS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_EQUALS
檢查兩個日期段是否相等（即它們的開始和結束時間是否相同） WM_EQUALS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_EQUALS’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_EQUALS’) FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘03-JAN-2009’);
p2b DATE := TO_DATE(‘01-JAN-2009’);
p2e DATE := TO_DATE(‘03-JAN-2009’);
BEGIN
SELECT WM_EQUALS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE(‘01-JAN-2009’);
p1e := TO_DATE(‘04-JAN-2009’);
SELECT WM_EQUALS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_GREATERTHAN
檢查第一個週期的開始是否大於（即晚於）第二個週期的結束 WM_GREATERTHAN(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_GREATERTHAN’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_GREATERTHAN’) FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE(‘01-FEB-2009’);
p1e DATE := TO_DATE(‘03-FEB-2009’);
p2b DATE := TO_DATE(‘01-JAN-2009’);
p2e DATE := TO_DATE(‘31-JAN-2009’);
BEGIN
SELECT WM_GREATERTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p2b := TO_DATE(‘02-FEB-2009’);
p2e := TO_DATE(‘31-DEC-2009’);
SELECT WM_GREATERTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_INTERSECTION
返回兩個日期的交集，即兩個範圍共有的時間範圍 WM_INTERSECTION(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_INTERSECTION’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_INTERSECTION’) FROM dual;
set serveroutput on
DECLARE
retval WM_PERIOD;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘10-JAN-2009’);
p2b DATE := TO_DATE(‘08-JAN-2009’);
p2e DATE := TO_DATE(‘12-JAN-2009’);
BEGIN
SELECT WM_INTERSECTION(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval.validfrom);
dbms_output.put_line(retval.validtill);
END;
/
WM_LDIFF
返回左側兩個時間段之間的差異（即，更早的時間） WM_LDIFF(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_LDIFF’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_LDIFF’) FROM dual;
set serveroutput on
DECLARE
retval WM_PERIOD;
p1b DATE := TO_DATE(‘01-NOV-2008’);
p1e DATE := TO_DATE(‘31-DEC-2008’);
p2b DATE := TO_DATE(‘29-NOV-2008’);
p2e DATE := TO_DATE(‘01-DEC-2008’);
BEGIN
SELECT WM_LDIFF(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval.validfrom);
dbms_output.put_line(retval.validtill);
END;
/
WM_LESSTHAN
檢查第一個週期的結束是否小於（或 早於）第二個週期的開始 WM_LESSTHAN(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_LESSTHAN’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_LESSTHAN’) FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘02-JAN-2009’);
p2b DATE := TO_DATE(‘03-JAN-2009’);
p2e DATE := TO_DATE(‘16-JAN-2009’);
BEGIN
SELECT WM_LESSTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE(‘04-JAN-2009’);
p1e := TO_DATE(‘10-JAN-2009’);
SELECT WM_LESSTHAN(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_MEETS
檢查第一個期間的結束是否是第二個期間的開始 WM_MEETS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN INTEGER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_MEETS’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_OVERLAPS’) FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘03-JAN-2009’);
p2b DATE := TO_DATE(‘03-JAN-2009’);
p2e DATE := TO_DATE(‘06-JAN-2009’);
BEGIN
SELECT WM_MEETS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE(‘01-JAN-2009’);
p1e := TO_DATE(‘04-JAN-2009’);
SELECT WM_MEETS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_OVERLAPS
檢查兩個時段是否重疊 WM_OVERLAPS(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN NUMBER;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_OVERLAPS’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_OVERLAPS’) FROM dual;
set serveroutput on
DECLARE
retval INTEGER;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘31-JAN-2009’);
p2b DATE := TO_DATE(‘31-DEC-2008’);
p2e DATE := TO_DATE(‘02-JAN-2009’);
BEGIN
SELECT WM_OVERLAPS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
p1b := TO_DATE(‘03-JAN-2009’);
p1e := TO_DATE(‘31-JAN-2009’);
SELECT WM_OVERLAPS(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval);
END;
/
WM_RDIFF
返回右側兩個時間段之間的差異（即,更晚的時間） WM_RDIFF(p1 IN WM_PERIOD, p2 IN WM_PERIOD) RETURN WM_PERIOD;
conn wmsys/wmsys
SELECT number_of_binds
FROM user_operators
WHERE owner = ‘WMSYS’
AND operator_name = ‘WM_RDIFF’;
set long 1000000
SELECT dbms_metadata.get_ddl(‘OPERATOR’, ‘WM_RDIFF’) FROM dual;
set serveroutput on
DECLARE
retval WM_PERIOD;
p1b DATE := TO_DATE(‘01-JAN-2009’);
p1e DATE := TO_DATE(‘31-DEC-2009’);
p2b DATE := TO_DATE(‘12-JAN-2008’);
p2e DATE := TO_DATE(‘16-JAN-2009’);
BEGIN
SELECT WM_RDIFF(WM_PERIOD(p1b, p1e), WM_PERIOD(p2b, p2e))
INTO retval
FROM dual;
dbms_output.put_line(retval.validfrom);
dbms_output.put_line(retval.validtill);
END;
/

相關文章