Oracle Database 10gの新しいSQL機能

Transcription

1 Oracle Database 10g SQL

2 Oracle Database 10g SQL... 4 Oracle Regular Expression Oracle Regular Expression Oracle DDL PL/SQL SQL OCI - SQLT_BFLOAT SQLT_BDOUBLE Oracle LOB LOB LOB LOB LOB PL/SQL JDBC OCI.. 19 CLOB NCLOB BFILE CLOB/NCLOB LOB VARRAY Oracle Database 10g SQL 2 Oracle Corporation New SQL Capabilities in Oracle 10g

3 ANSI SQL MULTISET MULTISET Oracle Database 10g SQL 3 Oracle Corporation New SQL Capabilities in Oracle 10g

4 Oracle Database 10g SQL Oracle SQL Oracle API Oracle SQL Oracle Database 10g SQL Oracle 1 Oracle Database 10g SQL Oracle 1 Oracle Database 10g SQL LOB SQL E-Business Oracle Database 10g SQL IEEE/POSIX IEEE 754 Standard for Binary Floating-Point Arithmetic IEEE754 Oracle Database 10g XML Java LOB Oracle Database 10g SQL API LOB LOB 4 ANSI SQL Multiset XML Java Oracle Database 10g SQL 4 Oracle Corporation New SQL Capabilities in Oracle 10g

5 Oracle Regular Expression 3 Web DNA Oracle Regular Expression Oracle Regular Expression SQL PL/SQL SQL PL/SQL Oracle Regular Expression Oracle Regular Expression Web XXXXXXXXXX 10 CHAR 10 XXX XXX-XXXX DBA SQL*Plus Oracle Database 10g SQL 5 Oracle Corporation New SQL Capabilities in Oracle 10g

6 REPLACE REGEXP_REPLACE LIKE REGEXP_LIKE Oracle Database 10g Oracle Database 10g UPDATE PL/SQL Oracle Database 10g SQL 6 Oracle Corporation New SQL Capabilities in Oracle 10g

7 Oracle Regular Expression SQL Oracle Regular Expression Oracle Regular Expression POSIX ERE Oracle Regular Expression POSIX BRE ERE UNIX 2 Oracle Regular Expression POSIX ERE Oracle Regular Expression Oracle Regular Expression SQL PL/SQL SQL REGEXP_LIKE REGEXP_SUBSTR REGEXP_INSTR REGEXP_REPLACE SQL SQL C. a? a 0 1 a* a 0 a+ a 1 Dot Quantifier Quantifier Quantifier a b a{m} a{m,} a{m,n} [abc] ( ) a b a m a m a m n ab c Alternation Quantifier Quantifier Quantifier Bracket Expression Subexpression Oracle Database 10g SQL 7 Oracle Corporation New SQL Capabilities in Oracle 10g

8 \n n Backreference [:cc:] [.ce.] [=ec=] Character Class Collation Element Equivalence Class POSIX Oracle Oracle Regular Expression Oracle Regular Expression /. 1 1 Oracle Oracle Regular Expression CHAR VARCHAR2 CLOB NCHAR NVARCHAR NCLOB REGEXP_LIKE REGEXP_LIKE REGEXP fly fly flying flewflown flies SELECT c1 FROM t1 WHERE REGEXP_LIKE(c1, fl(y(ing)? (ew) (own) (ies)) ); Oracle Database 10g SQL 8 Oracle Corporation New SQL Capabilities in Oracle 10g

9 REGEXP_SUBSTR REGEXP_INSTR SELECT REGEXP_SUBSTR( the bird flew over the river, fl(y(ing)? (ew) (own) (ies)) ) FROM dual; flew REGEXP_INSTR REGEXP_INSTR INSTR REGEXP_INSTR SQL REGEXP_REPLACE Oracle Regular Expression HTML REGEXP_REPLACE HTML SELECT REGEXP_REPLACE (c1, <[^>]+> ) FROM t1; \n n n REPLACE \ \\ Oracle Database 10g SQL 9 Oracle Corporation New SQL Capabilities in Oracle 10g

10 DDL Oracle Regular Expression Oracle Regular Expression VARCHAR2 CREATE TABLE t1 (c1 VARCHAR2(20), CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$'))); INSERT INTO t1 VALUES ('newuser'); 1 row created. INSERT INTO t1 VALUES ('newuser1'); ORA-02290:check constraint violated INSERT INTO t1 VALUES ('new-user'); ORA-02290:check constraint violated Oracle Regular Expression 1 1 functional index CREATE INDEX t1_ind ON t1 (REGEXP_SUBSTR(c1, 'a')); SELECT c1 FROM t1 WHERE REGEXP_SUBSTR(c1, 'a') = 'a'; 1 REGEXP_REPLACE CREATE VIEW v1 AS SELECT empno, REGEXP_REPLACE( , '(.)', '\1 ) FROM emp; Oracle Database 10g SQL 10 Oracle Corporation New SQL Capabilities in Oracle 10g

11 SELECT FROM v1 WHERE empno = 7369; j d o s o m e w h e r e. c o m PL/SQL Oracle Regular Expression SQL PL/SQL Oracle Regular Expression SQL PL/SQL 1 src := REGEXP_REPLACE (src, <regexp_1> ); src := REGEXP_REPLACE (src, <regexp_2> ); src := REGEXP_REPLACE (src, <regexp_3> ); PL/SQL n PL/SQL CREATE FUNCTION regexp_subx ( input VARCHAR2, regx VARCHAR2, subx NUMBER) RETURN VARCHAR2 IS ret VARCHAR2(4000); BEGIN ret := REGEXP_SUBSTR (input, regx); ret := REGEXP_REPLACE (ret, regx, \ subx); RETURN (ret); END regexp_subx; / LIKE SQL Oracle Database 10g SQL 11 Oracle Corporation New SQL Capabilities in Oracle 10g

12 REGEXP_LIKE REGEXP_LIKE Oracle Regular Expression LIKE PL/SQL Oracle Database 10g IEEE Oracle Database 10g 2 BINARY_FLOAT BINARY_DOUBLE 32 IEEE IEEE 754 Oracle Number BINARY_FLOAT BINARY_DOUBLE BINARY_FLOAT BINARY_DOUBLE BINARY_FLOAT BINARY_DOUBLE IEEE 754 binary_float binary_double binary_float binary_double binary_float binary_double binary_float binary_double binary_float binary_double order by group by binary_float binary_double Oracle RDBMS Oracle Number Oracle Number Oracle Database 10g SQL 12 Oracle Corporation New SQL Capabilities in Oracle 10g

13 RDBMS 1 Oracle Number Oracle Number Java XML IEEE 754 Standard for Binary Floating-Point Arithmetic IEEE754 OLAP BINARY_FLOAT BINARY_DOUBLE Oracle Number Oracle Number 1 binary_float binary_double RDBMS Java XML IEEE 754 Oracle Number Oracle Number Oracle Number 10 binary_float binary_double 2 Oracle Number binary_float binary_double binary_float binary_double Oracle Number 5 10 binary_float binary_double binary_float 5 (binary_double 9 ) Oracle Number 1 22 IEEE 754 Oracle Number sign exponent significand 3 base ( 1) sign. significand. base exponent 3 Oracle Database 10g SQL 13 Oracle Corporation New SQL Capabilities in Oracle 10g

14 IEEE 754 Oracle Number IEEE Oracle Number e e e e e-308 1e e e e e equal to not equal to greater than greater than or equal to less than less than or equal to unordered NaN Not a Number not equal to false 1 NaN Not a Number not equal to true Java IEEE 754 IEEE 754 Oracle Database 10g SQL 14 Oracle Corporation New SQL Capabilities in Oracle 10g

15 IA-32 IA-64 NaN expr IS NAN true 10 float double double float float double decimal decimal float double float double float double SQL ABS ACOS binary_float binary_double SQL> select ceil( e2f) from dual; CEIL( E2F) E+002 ORDER BY HAVING SELECT GROUP BY Oracle GROUP BY GROUP BY GROUP BY 1 Oracle AVG CORR MAX MIN STDDEV AVG CORR MAX MIN STDDEV Oracle Database 10g SQL 15 Oracle Corporation New SQL Capabilities in Oracle 10g

16 Window Sliding Window ORDER BY WHERE GROUP BY HAVING ORDER BY AVG CORR MAX MIN STDDEV Oracle Database 10g 6 1 Oracle Database 10g 6 NOT NULL NULL NULL 1 NOT NULL NULL CHECK REF REF REF REF SQL> create table floating_point_table1 (fltnnull binary_float constraint flt_null not null, dblnnull binary_double constraint dbl_null not null, fltunq binary_float constraint flt_unq unique, dblunq binary_double constraint dbl_unq Oracle Database 10g SQL 16 Oracle Corporation New SQL Capabilities in Oracle 10g

17 unique, fltchk binary_float constraint flt_chk check ( fltchk is not nan ), dblchk binary_double constraint dbl_chk check ( dblchk is not infinite), fltprm binary_float constraint flt_prm primary key); Table created. SQL> create table floating_point_table2 ( dblprm binary_double constraint dbl_prm primary key, fltfrn binary_float constraint flt_frn references floating_point_table1(fltprm) on delete cascade); Table created. SQL PL/SQL OCI OCCI Pro*C/C++ JDBC XML XML SQL SQL BINARY_FLOAT BINARY_DOUBLE SQL SQL BINARY_FLOAT BINARY_DOUBLE SQL expr OCI - SQLT_BFLOAT SQLT_BDOUBLE Oracle Call Interface OCI IEEE 754 SQLT_BFLOAT SQLT_BDOUBLE IEEE 754 C float double OCI SQLT_BFLOAT SQLT_BDOUBLE BINARY_FLOAT BINARY_DOUBLE Oracle SQL BINARY_FLOAT BINARY_DOUBLE Oracle Oracle Database 10g SQL 17 Oracle Corporation New SQL Capabilities in Oracle 10g

18 LOB LOB Oracle Database 10g Reference on Read and Copy on Write LOB Oracle Database 10g Reference on Read and Copy on Write LOB LOB LOB LOB LOB 1 LOB 1 0 LOB LOB LOB LOB LOB LOB 1 LOB LOB LOB BLOB CLOB NCLOB 4 UB (2^32-1) LOB 4-1 * db_block_size LOB LOB 4 LOB DBMS_LOB PL/SQL JDBC Java Database Connectivity Java OCI Oracle Call Interface C LOB LOB DB_BLOCK_SIZE DB_BLOCK_SIZE LOB LOB CLOB NCLOB BLOB Oracle Database 10g SQL 18 Oracle Corporation New SQL Capabilities in Oracle 10g

19 LOB PL/SQL JDBC OCI LOB DBMS_LOB PL/SQL API DBMS_LOB.GET_STORAGE_LIMIT Oracle JDBC JDBC LOB API LOB Oracle Call Interface API LOB LOB OCILobRead() OCILobWriteAppend() OCILobWrite() OCI OCI 4GB LOB OCILobRead2() OCIWriteAppend2() OCILobWrite2() oraub8 CLOB NCLOB Unicode CLOB NCLOB TO_CLOB TO_NCLOB SQL PL/SQL Oracle Database 10g DML SQL IN OUT PL/SQL PL/SQL 1 CREATE TABLE my_table (nclob_col NCLOB); DECLARE clob_var CLOB; nclob_var NCLOB; BEGIN clob_var := 'clob data'; -- initialize the CLOB value; -- Bind a CLOB into an NCLOB column INSERT INTO my_table VALUES (clob_var); SELECT nclob_col INTO clob_var FROM my_table; -- Define an NCLOB column as a CLOB var END; / Oracle Database 10g SQL 19 Oracle Corporation New SQL Capabilities in Oracle 10g

20 2 CREATE FUNCTION TRY_ME (a IN CLOB) RETURN NCLOB is BEGIN RETURN a; END; / DECLARE clob_var CLOB; nclob_var NCLOB; BEGIN nclob_var:= 'nclob data'; /* Pass an NCLOB into a function which takes a CLOB Return an NCLOB variable to a CLOB variable.*/ clob_var:=try_me(nclob_var); end; / BFILE CLOB/NCLOB BFILE LOB LOADFROMFILE2() CLOB NCLOB DBMS_LOB OCI LOADFROMFILE2() csid ID BFILE CLOB NCLOB BFILE CLOB NCLOB BFILE LOB UCS2 2 Unicode UCS2 CLOB csid NCLOB csid : BFILE CharacterSet1 : LOB CharacterSet2 cs1 cs2 if (cs1!=cs2) cs1 cs2 cs1 UCS2 cs1 UCS2 Oracle Database 10g SQL 20 Oracle Corporation New SQL Capabilities in Oracle 10g

21 LOB Oracle Database 10g VARRAY ALTER TYPE... MODIFY LIMIT VARRAY VARRAY VARRAY INVALIDATE CASCADE CREATE TYPE _list_typ AS OBJECT ( section_no NUMBER, s _list_arr); / CREATE TYPE _varray_typ AS VARRAY(5) OF _list_typ; / ALTER TYPE _varray_typ MODIFY LIMIT 100 CASCADE; SQL users CREATE TABLE people_tab ( people_column people_typ ) NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE users); TABLESPACE ALTER TABLE MOVE ALTER TABLE MOVE ALTER TABLE MOVE ALTER TABLE people_tab MOVE TABLESPACE users; ALTER TABLE people_column_nt MOVE TABLESPACE example; Oracle Database 10g SQL 21 Oracle Corporation New SQL Capabilities in Oracle 10g

22 people_tab users example ANSI SQL MULTISET Oracle Database 10g NESTED TABLE MULTISET Oracle Database 10g NESTED TABLE MULTISET Oracle VARRAY NESTED TABLE 2 1 = <> 2 2 CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), MAP MEMBER FUNCTION get_idno RETURN NUMBER ); / CREATE TYPE BODY person_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN RETURN idno; END; END; / Oracle Database 10g SQL 22 Oracle Corporation New SQL Capabilities in Oracle 10g

23 CREATE TYPE people_typ AS TABLE OF person_typ; / CREATE TABLE students ( graduation DATE, math_majors people_typ, chem_majors people_typ, physics_majors people_typ) NESTED TABLE math_majors STORE AS math_majors_nt NESTED TABLE chem_majors STORE AS chem_majors_nt NESTED TABLE physics_majors STORE AS physics_majors_nt; INSERT INTO students (graduation) VALUES ('01-JUN-03'); UPDATE students SET math_majors = people_typ (person_typ(12, 'Bob Jones', ' '), person_typ(31, 'Sarah Chen', ' '), person_typ(45, 'Chris Woods', ' ')), chem_majors = people_typ (person_typ(51, 'Joe Lane', ' '), person_typ(31, 'Sarah Chen', ' '), person_typ(52, 'Kim Patel', ' ')), physics_majors = people_typ (person_typ(12, 'Bob Jones', ' '), person_typ(45, 'Chris Woods', ' ')) WHERE graduation = '01-JUN-03'; SELECT p.name FROM students, TABLE(physics_majors) p WHERE math_majors = physics_majors; no rows selected person_typ IN IN NULL Oracle Database 10g SQL 23 Oracle Corporation New SQL Capabilities in Oracle 10g

24 SELECT p.idno, p.name FROM students, TABLE (physics_majors) p WHERE physics_majors IN (math_majors, chem_majors); no rows selected SUBMULTISET [OF] OF SUBMULTISET SELECT p.idno, p.name FROM students, TABLE (physics_majors) p WHERE physics_majors SUBMULTISET OF math_majors; IDNO NAME Bob Jones 45 Chris Woods MEMBER [OF] NOT MEMBER [OF] OF SELECT graduation FROM students WHERE person_typ(12, 'Bob Jones', ' ') MEMBER OF math_majors; GRADUATION JUN-03 person_typ (12, 'Bob Jones', ' ') math_majors Oracle Database 10g SQL 24 Oracle Corporation New SQL Capabilities in Oracle 10g

25 IS [NOT] EMPTY NULL NULL NULL SELECT p.idno, p.name FROM students, TABLE (physics_majors) p WHERE physics_majors IS NOT EMPTY; IDNO NAME Bob Jones 45 Chris Woods IS [NOT] A SET SELECT p.idno, p.name FROM students, TABLE (physics_majors) p WHERE physics_majors IS A SET; IDNO NAME Bob Jones 45 Chris Woods MULTISET CARDINALITY CARDINALITY VARRAY NUMBER VARRAY NULL NULL SELECT CARDINALITY(math_majors) FROM students; CARDINALITY(MATH_MAJORS) COLLECT COLLECT CAST COLLECT Oracle Database 10g SQL 25 Oracle Corporation New SQL Capabilities in Oracle 10g

26 MULTISET EXCEPT MULTISET EXCEPT ALL DISTINCT ALL ALL ntab1 MULTISET EXCEPT ALL ntab2 ntab2 ntab1 ntab1 m ntab2 n m n m n DISTINCT ntab1 ntab2 SELECT math_majors MULTISET EXCEPT physics_majors FROM students WHERE graduation = '01-JUN-03'; MATH_MAJORSMULTISETEXCEPTPHYSICS_MAJORS(IDNO, NAME, PHONE) PEOPLE_TYP(PERSON_TYP(31, 'Sarah Chen', ' ')) MULTISET INTERSECTION MULTISET INTERSECT ALL DISTINCT ALL ALL ntab1 m ntab2 n MIN m, n DISTINCT NULL SELECT math_majors MULTISET INTERSECT physics_majors FROM students WHERE graduation = '01-JUN-03'; MATH_MAJORSMULTISETINTERSECTPHYSICS_MAJORS(IDNO, NAME, PHONE) PEOPLE_TYP( PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) Oracle Database 10g SQL 26 Oracle Corporation New SQL Capabilities in Oracle 10g

27 MULTISET UNION MULTISET UNION ALL DISTINCT ALL ALL NULL ntab1 ntab2 ntab1 m ntab2 n m + n DISTINCT NULL SELECT math_majors MULTISET UNION DISTINCT physics_majors FROM students WHERE graduation = '01-JUN-03'; MATH_MAJORSMULTISETUNIONDISTINCTPHYSICS_MAJORS(IDNO, NAME, PHONE) PEOPLE_TYP( PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(31, 'Sarah Chen', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) SELECT math_majors MULTISET UNION ALL physics_majors FROM students WHERE graduation = '01-JUN-03'; MATH_MAJORSMULTISETUNIONALLPHYSICS_MAJORS(IDNO, NAME, PHONE) PEOPLE_TYP( PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(31, 'Sarah Chen', ' '), PERSON_TYP(45, 'Chris Woods', ' '), PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) POWERMULTISET POWERMULTISET POWERMULTISET 32 SELECT * FROM TABLE(POWERMULTISET( people_typ ( person_typ(12, 'Bob Jones', ' '), person_typ(31, 'Sarah Chen', ' '), person_typ(45, 'Chris Woods', ' ')))); Oracle Database 10g SQL 27 Oracle Corporation New SQL Capabilities in Oracle 10g

28 COLUMN_VALUE(IDNO, NAME, PHONE) PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', ' ')) PEOPLE_TYP(PERSON_TYP(31, 'Sarah Chen', ' ')) PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(31, 'Sarah Chen', ' ')) PEOPLE_TYP(PERSON_TYP(45, 'Chris Woods', ' ')) PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) PEOPLE_TYP(PERSON_TYP(31, 'Sarah Chen', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(31, 'Sarah Chen', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) 7 rows selected. POWERMULTISET_BY_CARDINALITY POWERMULTISET_BY_CARDINALITY POWERMULTISET_BY_CARDINALITY(x, l) TABLE(POWERMULTISET(x)) p where CARDINALITY(value(p)) = l x 1 POWERMULTISET_BY_CARDINALITY 32 SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ ( person_typ(12, 'Bob Jones', ' '), person_typ(31, 'Sarah Chen', ' '), person_typ(45, 'Chris Woods', ' ')),2)); COLUMN_VALUE(IDNO, NAME, PHONE) PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(31, 'Sarah chen', ' '), PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) PEOPLE_TYP(PERSON_TYP(31, 'Sarah Chen', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) Oracle Database 10g SQL 28 Oracle Corporation New SQL Capabilities in Oracle 10g

29 SET SET DISTINCT SELECT SET(physis_majors) FROM students WHERE graduation = '01-JUN-03'; SET(PHYSICS_MAJORS)(IDNO, NAME, PHONE) PEOPLE_TYP( PERSON_TYP(12, 'Bob Jones', ' '), PERSON_TYP(45, 'Chris Woods', ' ')) Oracle SQL Oracle Database 10g Oracle SQL LOB Oracle Database 10g Oracle SQL LOB Oracle Database 10g Grid SQL Java XML Oracle SQL PL/SQL E-Business LOB Oracle Java XML API API Java XML C/C++ Oracle Oracle Database 10g Web OLAP Oracle Oracle Database 10g SQL 29 Oracle Corporation New SQL Capabilities in Oracle 10g

30 Oracle Database 10g SQL SQL SQL SQL Oracle Database 10g SQL Oracle Database 10g SQL 30 Oracle Corporation New SQL Capabilities in Oracle 10g

31 Oracle Database 10g SQL : Geoff Lee : Peter Linsley Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. : : : Oracle Copyright 2003 Oracle Corporation All rights reserved.