Oracle WMSYS Functions

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;
	/