Oracle Code Tokyo 2017 ダウンロード資料

(1)

今日から使える! DBアプリ開発の

負担を減らせる小技集。

ブラウザだけで最新SQLを試せます!

井上克己

Principal Engineer

http://blogs.oracle.com/LetTheSunshineIn/

http://github.com/inoue-katsumi/

クラウド・テクノロジー事業統括

Database & Exadataプロダクトマネジメント本部

データベーステクノロジー部

(2)

本日の内容

LiveSQL 基本

JSON

Ruby, Java, Python

WITH句、WINDOWなど

SQLcl

1

2

3

4

5

(3)

Oracle LiveSQL

基本編

(4)

Oracle Live SQL とは

• SQL学習用途向けに

Oracle Database が何の準備もなしに

無料で使用できるWebサービス

• コミュニティが作成したSQLを検索できる

• 自分の作成したSQLを共有できる

• 一時データを作成できる

• 2015年10月にリリース

https://livesql.oracle.com/

(5)

(6)

• ログイン前（右図）

–

インクリメンタル・サーチで

「range list partition」に

• ログイン後

–

通常の部分文字列検索

• Google 検索

–

サイト指定検索

3種類の検索方法

あのSQLってどうやって書くんだっけ?

(7)

Composite Range-List Partitioning 例

誰がいつ作ったか

補足のコメント

次のSQL文。

ここではUSER_TAB_PARTITIONSを

確認している

実行結果

(8)

• 左ペインのSchemaを見ると、

パーティション表のサンプルが

自分のスキーマにできている

• 表などのオブジェクトと

データは

タイムアウトすると消える

• データは10MBまで

使用率は SQL Session > Utilization

Schemaをクリックすると

(9)

(10)

JSON

(11)

"

SQL:2016" 標準に沿った実装

Oracle 12c JSON ドキュメント・ストア

Oracle Database 12c

SQL

JSON

SODA APIを使用

したアプリケー

ション開発

Oracle Databaseによる

JSONドキュメントの

保存と管理

SQLを使用した

JSONドキュメントの

レポーティングと分析

(12)

デモ: JSON

• セッション一覧

データを使用

–

タイトル("title")

–

会社

("company")

(13)

JSON "Universal" 検索索引の機能強化

• 12.2にて検索索引を作成するコマンドが追加

• 検索索引の実体はOracle Text索引で12.1と変わらないが、12.1にて作成

した検索索引はドロップして、CREATE SEARCH INDEXにて再作成が推奨

• 数値、日付、時刻要素についての範囲検索時にも参照される

• 指定した文字列要素の全文検索が可能

–

ステミング、あいまい検索など

JSON検索索引を作成するコマンド

(14)

新しいPL/SQLオブジェクト・タイプ

• parse()

–

JSONデータを保持している変数またはカラムから、JSON_ELEMENT_Tオブジェクトを取り出す。

• is_Array(), is_Object(), is_String() ,etc.

–

キー・値・ペアの、値の型を確認する。

• get, put

–

キー・値・ペアの値を、オブジェクトまたは配列としてアクセスする。

• get_String, get_Number:

–

キー・値・ペアの値を、スカラー値としてアクセスする。

• stringify, to_string

–

PL/SQL JSONデータをテキスト表現に戻す。

主なメソッド

(15)

Ruby, Java, Python

(16)

Oracle Database 12c for the Developer

言語

ドライバー

C

OCI

C++

OCCI

Java

JDBC

.NET

ODP.NET

Node.js

node-oracledb

Python

cx_Oracle

PHP

OCI8

R

ROracle

Ruby

ruby-oci8

メジャーな開発環境、プログラミング言語をサポート

ODBC, OLE DB, Pro*C,

Pro*COBOL,

サード・パーティー製ドライバー

Open Source ドライバー

(Oracle 社が主に貢献)

Oracle 社製ドライバー

(17)

LiveSQL スニペット

• 注意点:

LiveSQLのDBはブラウザーのみから

アクセス可能。Oracle Netでのアクセスは

別途DBを用意する必要あり!

(18)

Java(JDBC)デモ: NetBeans IDE環境

• または

Eclipse,

Jdeveloper,

Visual Studio Code

とか

• 自動補完

(19)

デモ: Python, Ruby

環境、および、Oracle DBアクセスドライバーアドオン（プラグイン）情報

$

gem list --local | grep oci

ruby-oci8 (2.2.1)

$

pip list | grep Oracle

cx-Oracle (5.3)

$

ruby -v

ruby 2.2.5p319 (2016-04-26 revision 54774) [x86_64-cygwin]

#

rpm -qi cx_Oracle | tail -2

Description :

(20)

Ruby Oracle DB アクセスドライバー

• DML配列

バインド

テスト

スクリプト

(21)

WITH句、WINDOWなど

(22)

O/Rマッパー (Object Relational Mapper, ORM)

• JPA

• Hibernate

• LINQ

• Django

• ActiveRecord

• MyBatis…

• jOOQ

ORMを使えばSQLを知らなくてもよい?

(23)

デモ: jOOQ開発者のSQLセッション資料より - その1

• WITH句使用

–

CTE:

Common Table

Expression

• 再帰的にデータ生成

• 右は PostgreSQL用

出典: https://www.slideshare.net/LukasEder1/10-sql-tricks-that-you-didnt-think-were-possible

(24)

デモ:Qiitaでの類似例

• 右記のSQLをLiveSQLへコピペ

し編集して実行

Qiita

には日本オラクル

organization

リンクも

あります

(25)

デモ: jOOQ開発者のSQLセッション資料より - その2

• WINDOW機能で

累積合計を求める

出典: https://blog.jooq.org/2014/04/29/nosql-no-sql-how-to-calculate-running-totals/

SELECT

**t.*,**

t.current_balance - NVL(

SUM(t.amount) OVER (

PARTITION BY t.account_id

ORDER BY t.value_date DESC,

t.id DESC

ROWS BETWEEN UNBOUNDED PRECEDING

AND 1 PRECEDING

)

,

0) AS balance

FROM v_transactions t

WHERE t.account_id = 1

回答例

(26)

SQLcl

(27)

SQLclとは

• 無料で使用できる高機能版SQL*Plus

• JVMで動作

• Oracle Technology Network (OTN) から誰でもダウンロード可能

• 12c R2 からデフォルトで $ORACLE_HOMEディレクトリー配下にインストー

ルされる

–

ただし旧バージョン

(28)

SQL> help

For help on a topic type help

<topic>

List of Help topics available:

/

@

@@

ACCEPT

ALIAS

APEX

APPEND

ARCHIVE LOG

BREAK

BRIDGE

CD

CHANGE

CLEAR

COLUMN

CTAS

DDL

DEFINE

DEL

DESCRIBE

DISCONNECT

EDIT

EXECUTE

EXIT

FORMAT

GET

HISTORY

HOST

INFORMATION

INPUT

LIST

LOAD

NET

OERR

PASSWORD

PAUSE

PRINT

REMARK

REPEAT

RESERVED WORDS

RUN

SAVE

SET

SHOW

SHUTDOWN

SPOOL

SSHTUNNEL

START

STARTUP

STORE

TIMING

TNSPING

UNDEFINE

VARIABLE

WHENEVER

XQUERY

SQLclはSQL*Plusと何が違うのか

(29)

INFORMATION+

SQL> INFO+ emp

……

Columns

NAME DATA TYPE NULL DEFAULT

LOW_VALUE HIGH_VALUE NUM_DISTINCT HISTOGRAM

*EMPNO NUMBER(4,0) No 7369 7934 14 NONE

ENAME VARCHAR2(10 BYTE) Yes ADAMS WARD 14 NONE

JOB VARCHAR2(9 BYTE) Yes ANALYST SALESMAN 5 NONE

MGR NUMBER(4,0) Yes 7566 7902 6 NONE

HIREDATE DATE Yes 1980.12.17.00.00.00 1987.05.23.00.00.00 13 NONE

SAL NUMBER(7,2) Yes 800 5000 12 NONE

COMM NUMBER(7,2) Yes 0 1400 4 NONE

DEPTNO NUMBER(2,0) Yes 10 30 3 FREQUENCY

……

(30)

SQL> DDL emp

CREATE TABLE "SCOTT"."EMP"

( "EMPNO" NUMBER(4,0),

"ENAME" VARCHAR2(10),

"JOB" VARCHAR2(9),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,

"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0),

CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ENABLE,

CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

SQL> --DDLはバッファにも入っている

2 EDIT

(31)

• SQL*Plusの場合

SQL> SELECT name, value FROM v$sysstat

2 WHERE NAME = 'physical reads';

NAME VALUE

physical reads 21,861

SQL> / -- 手動で何度も実行する……

NAME VALUE

physical reads 23,282

• SQLclの場合

SQL> SELECT name, value FROM v$sysstat

2 WHERE NAME = 'physical reads';

SQL> -- 2秒間隔で100回繰返し

2 REPEAT 100 2

Running 5 of 100 @ 2:5:27 with a delay of 2.0s

NAME VALUE

physical reads 23,282

REPEAT

(32)

• JVMベースの言語で書かれたスク

リプトを実行

• Java version 8 以上

• ファイルの拡張子によりどの言語

かを判断

• Javascript

–

JDK8+で同梱されるNashornエンジン使

用

• Python

• Ruby

–

JRuby

• DB操作を少ない行数で書ける

–

Connectionオブジェクトをスクリプト中

で生成する必要が無い

script コマンド

(33)

本日お伝えした内容

LiveSQL 基本

JSON

Ruby, Java, Python

WITH句、WINDOWなど

SQLcl

1

2

3

4

5 Happy SQL Life!

(34)

(35)

Oracle Code Tokyo 2017 ダウンロード資料

今日から使える! DBアプリ開発の

負担を減らせる小技集。

ブラウザだけで最新SQLを試せます!

井上 克己

Principal Engineer

http://blogs.oracle.com/LetTheSunshineIn/

http://github.com/inoue-katsumi/

クラウド・テクノロジー事業統括

Database & Exadataプロダクトマネジメント本部

データベーステクノロジー部

本日の内容

LiveSQL 基本

JSON

Ruby, Java, Python

WITH句、WINDOWなど

SQLcl

1

2

3

4

5

Oracle LiveSQL

基本編

Oracle Live SQL とは

•

SQL学習用途向けに

Oracle Database が何の準備もなしに

無料で使用できるWebサービス

•

コミュニティが作成したSQLを検索できる

•

自分の作成したSQLを共有できる

•

一時データを作成できる

•

2015年10月にリリース

https://livesql.oracle.com/

•

ログイン前（右図）

–

インクリメンタル・サーチで

「range list partition」に

関連するSQLが表示される

•

ログイン後

–

通常の部分文字列検索

•

Google 検索

–

サイト指定検索

3種類の検索方法

あのSQLってどうやって書くんだっけ?

Composite Range-List Partitioning 例

誰がいつ作ったか

補足のコメント

次のSQL文。

次のSQL文。

ここではUSER_TAB_PARTITIONSを

確認している

実行結果

•

左ペインのSchemaを見ると、

パーティション表のサンプルが

自分のスキーマにできている

•

表などのオブジェクトと

データは

タイムアウトすると消える

•

データは10MBまで

使用率は SQL Session > Utilization

Schemaをクリックすると

JSON

"

SQL:2016" 標準に沿った実装

Oracle 12c JSON ドキュメント・ストア

Oracle Database 12c

SQL

井上克己