oracle查看wmsys,Oracle WMSYS Functions

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;

Cross-tabulates

a comma delimited list

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;

Checks if the

first period contains the second period

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;

/

Checks if two

periods are equal (that is, their start and end times are the same)

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;

/

Checks if the

start of the first period is greater than (that is, later than) the end of the

second period

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;

/

Returns the

intersection of the two periods, that is, the time range common to both

periods

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;

/

Returns the

difference between the two periods on the left (that is, earlier in

time)

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;

/

Checks if the end

of the first period is less than (that is, earlier than) the start of the second

period

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;

/

Checks if the

end of the first period is the start of the second period

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;

/

Checks if two

periods overlap

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;

/

Returns the

difference between the two periods on the right (that is, later in

time)

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;

/