便利なサブプログラム
↑を見てみると、FUNCTION、PROCEDUREの中にサブプログラムの定義ができることが書いてありました。
実際に触ってみた。
CREATE OR REPLACE PROCEDURE PROC_TEST IS PROCEDURE SHOW(MSG IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(MSG); END; BEGIN SHOW('HELLO'); END; /
メッセージを出すだけですが、、、、
これはつかえる
ちなみにFUNCTION内でもかけます
CREATE OR REPLACE FUNCTION FUNC_TEST RETURN NUMBER IS FUNCTION ADD_100(NUM IN NUMBER) RETURN NUMBER IS BEGIN RETURN NUM + 100; END; BEGIN RETURN ADD_100(100); END; /
サブプログラムがあれば、わざわざPACKAGE宣言する必要がなくなる場面が
多くなりそう。
オラクルでメールを送ってみる
oracleのマニュアルみながらutl_smtpパッケージ使って
id:yohei-aにメールを送ってみたｗ
DECLARE c UTL_SMTP.CONNECTION; PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF); END; BEGIN c := UTL_SMTP.OPEN_CONNECTION('-----------------------'); UTL_SMTP.HELO(c, 'foo.com'); UTL_SMTP.MAIL(c, '-----------------------'); UTL_SMTP.RCPT(c, '-----------------------'); UTL_SMTP.OPEN_DATA(c); send_header('From', '"Sender" <--------------------------->'); send_header('To', '"Recipient" <--------------------------->'); send_header('Subject', 'Konnichiwa'); UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'niwa desu. utl_smtp package wo tukatte mail okutte mimasita.'); UTL_SMTP.CLOSE_DATA(c); UTL_SMTP.QUIT(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN UTL_SMTP.QUIT(c); EXCEPTION WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN NULL; -- When the SMTP server is down or unavailable, we don't have -- a connection to the server. The QUIT call will raise an -- exception that we can ignore. END; raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm); END; /
数分後にスパムメールが来たとid:youhei-aから言われましたがｗｗ
ひとまず2バイト文字はおくれませんでした。
きっとエンコードすれば良いはず
本当はutl_mail使いたかったのですが、「引数がおかしいよ？」てオラクルに怒られて
中見ようとおもったら、パッケージボディがラッピングされてて見れなかったのでひとまず放置
あとは、utl_smtpをプロシージャ内で使うときにACLではねられてしまったので調査中
あと何個か解決したら、これとjob_schedulerでがばがば送れる！
ネスト表を使ってみた
テーブルのカラムにテーブルが作れるらしいので試してみました。
下記の親子関係のテーブルをネスト表に書き換えてみます。
CREATE TABLE CUSTOMER ( ID NUMBER NOT NULL, NAME VARCHAR2(100), CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID) USING INDEX ); / CREATE TABLE ADDRESS ( ID NUMBER NOT NULL, CUSTOMER_ID NUMBER, ZIP_CODE VARCHAR2(10), CONSTRAINT PK_ADDRESS PRIMARY KEY (ID) USING INDEX, CONSTRAINT FK_ADDRESS FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID) ); / INSERT INTO CUSTOMER(ID,NAME) VALUES(1,'取引先Ａ'); / INSERT ALL INTO ADDRESS(ID,CUSTOMER_ID,ZIP_CODE) VALUES (1,1,'1234567') INTO ADDRESS(ID,CUSTOMER_ID,ZIP_CODE) VALUES (2,1,'3216543') SELECT * FROM DUAL; /
SELECTする
SELECT C.ID AS CUSTOMER_ID ,C.NAME ,A.ID AS ADDRESS_ID ,A.ZIP_CODE FROM CUSTOMER C INNER JOIN ADDRESS A ON A.CUSTOMER_ID = C.ID;
結果
| CUSTOMER_ID | NAME | ADDRESS_ID | ZIP_CODE |
| 1 | 取引先Ａ | 1 | 1234567 |
| 1 | 取引先Ａ | 2 | 3216543 |
これを、ネスト表で表現してみる。
まずADDRESSテーブルと同様のTYPEと、その作成したTYPEでTABLEのTYPEを作成
CREATE TYPE NEST_ADDRESS AS OBJECT (ZIP_CODE VARCHAR(10)); / CREATE OR REPLACE TYPE NEST_ADDRESS_LIST AS TABLE OF NEST_ADDRESS; /
次にCUSTOMERテーブル作成
ADDRESS_LIST カラムのTYPE をさっきつくったNEST_ADDRESS_LISTにします
CREATE TABLE NEST_CUSTOMER ( ID NUMBER, NAME VARCHAR2(100), ADDRESS_LIST NEST_ADDRESS_LIST ) NESTED TABLE ADDRESS_LIST STORE AS ADDRESS_NTAB; /
これで完成です。
次にデータをINSERTしてみます。
NEST_ADDRESS_LIST TYPE に、 NEST_ADDRESS TYPE のデータを複数いれます。
INSERT INTO NEST_CUSTOMER(ID,NAME,ADDRESS_LIST) VALUES(1,'取引先Ａ', NEST_ADDRESS_LIST( NEST_ADDRESS('1234567') ,NEST_ADDRESS('3216543') ) );
で、SELECTします。
TABLE(ネストカラム)で取得します。
SELECT N.ID ,N.NAME ,A.ZIP_CODE FROM NEST_CUSTOMER N ,TABLE(N.ADDRESS_LIST) A
結果、ちゃんとADDRESS_LIST に2レコード入ってることがわかります。
| ID | NAME | ZIP_CODE |
| 1 | 取引先Ａ | 1234567 |
| 1 | 取引先Ａ | 3216543 |
ADDRESS_LIST の データをいじりたいときはどうすれば良いのか調べて見たところ
下記のように一度レコードをサブクエリで指定してあげてから操作するようです。
INSERT
INSERT INTO TABLE(SELECT ADDRESS_LIST FROM NEST_CUSTOMER WHERE ID = 1) VALUES(NEST_ADDRESS('987654321'));
| ID | NAME | ZIP_CODE |
| 1 | 取引先Ａ | 1234567 |
| 1 | 取引先Ａ | 3216543 |
| 1 | 取引先Ａ | 987654321 |
UPDATE
UPDATE TABLE (SELECT ADDRESS_LIST FROM NEST_CUSTOMER WHERE ID = 1) NC SET VALUE(NC) = NEST_ADDRESS('111111') WHERE NC.ZIP_CODE = '987654321';
| ID | NAME | ZIP_CODE |
| 1 | 取引先Ａ | 1234567 |
| 1 | 取引先Ａ | 3216543 |
| 1 | 取引先Ａ | 111111 |
DELETE
DELETE FROM TABLE(SELECT ADDRESS_LIST FROM NEST_CUSTOMER WHERE ID = 1) NC WHERE NC.ZIP_CODE = '111111';
| ID | NAME | ZIP_CODE |
| 1 | 取引先Ａ | 1234567 |
| 1 | 取引先Ａ | 3216543 |
ひとまず今日はここまで。。。
難しかった上にあまり使い道が思いつかないですね。
11gで追加された一時表領域の縮小 alter tablespace 〜 shrink space keep 〜M
10gまでは一時表領域が大変なことになったときには
新しく一時表領域のデータファイル追加して、スイッチして消して、、、、
みたいなことをしてましたが、
11gから一時表領域の縮小コマンドが追加されました。
alter tablespace [一時表領域名] shrink space keep [最低限空けたいサイズ]
細かいところは下記のドキュメントを参照してください
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tspaces007.htm
11gで追加された仮想列をつかってみた
会社のPCに11gインストしたのでいろいろ試してみる。、
今回試したのは仮想列（VirtualColumn）
別カラムの内容を参照して加工できる、そして実体はなし！
金額と消費税のテーブルで仮想列で税込金額をつくってみた。
CREATE TABLE VIRTUAL_COLUMN( AMOUNT NUMBER ,TAX NUMBER ,TAXINAMOUNT AS (AMOUNT+TAX) )
taxamountカラムが仮想列になってます。
そんでデータInsert
INSERT INTO VIRTUAL_COLUMN(AMOUNT,TAX) VALUES(100,5)
で、検索
SELECT * FROM VIRTUAL_COLUMN
結果
| AMOUNT | TAX | TAXINAMOUNT |
| 100 | 5 | 105 |
おぉ、ちゃんと計算されてでてますね。
ちなみに仮想列に対してInsertとかUpdateするとORA-54013が発生します。
あと、この仮想列に対して索引を付けることが可能
もちろんFunction索引らしい
でもよくよく考えたらTableの定義にロジック入ってるってかなりきもいので
あんま使う機会なさそう
11gで追加されたConnectionPoolを試してみた
DRCPというらしいです
http://www.ujp.jp/modules/d3blog/details.php?bid=476&cid=47
http://www.oracle.com/technology/global/jp/tech/oci/pdf/oracledrcp11g.pdf
設定は簡単です
DBにsysで入って
dbms_connection_pool.start_pool();
って打つと起動します。
あとはクライアント側のtnsnames.oraの
SERVER = DEDICATEとかなってるとこを SERVER = POOLED に変更すればok
PHPとApacheのabコマンド使って計測してみた
PHPのソース（ただつないで切ってるだけ）
<?php $res = oci_connect("ユーザ","パスワード","接続識別子"); oci_close($res); ?>
これを ab コマンドで叩きまくる。
ab -n 1000 -c 10 http://localhost/connect_test.php
そして結果
まずは通常の専用サーバのみ
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking localhost (be patient)
Server Software: Apache/2.2.14
Server Hostname: localhost
Server Port: 80
Document Path: /connect_test.php
Document Length: 0 bytes
Concurrency Level: 10
Time taken for tests: 58.575 seconds
Complete requests: 1000
Failed requests: 0
Write errors: 0
Total transferred: 241000 bytes
HTML transferred: 0 bytes
Requests per second: 17.07 [#/sec] (mean)
Time per request: 585.751 [ms] (mean)
Time per request: 58.575 [ms] (mean, across all concurrent requests)
Transfer rate: 4.02 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 2.1 0 16
Processing: 109 583 103.8 578 1312
Waiting: 94 583 103.9 578 1312
Total: 109 583 103.8 578 1312
Percentage of the requests served within a certain time (ms)
50% 578
66% 594
75% 594
80% 609
90% 641
95% 750
98% 906
99% 1125
100% 1312 (longest request)次にプーリングした専用サーバ
This is ApacheBench, Version 2.3 <$Revision: 655654 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking localhost (be patient)
Server Software: Apache/2.2.14
Server Hostname: localhost
Server Port: 80
Document Path: /connect_test.php
Document Length: 0 bytes
Concurrency Level: 10
Time taken for tests: 36.467 seconds
Complete requests: 1000
Failed requests: 49
(Connect: 0, Receive: 0, Length: 49, Exceptions: 0)
Write errors: 0
Total transferred: 259130 bytes
HTML transferred: 18032 bytes
Requests per second: 27.42 [#/sec] (mean)
Time per request: 364.669 [ms] (mean)
Time per request: 36.467 [ms] (mean, across all concurrent requests)
Transfer rate: 6.94 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 1.4 0 16
Processing: 16 362 238.9 297 1797
Waiting: 16 362 238.8 297 1797
Total: 16 363 239.2 297 1797
Percentage of the requests served within a certain time (ms)
50% 297
66% 344
75% 359
80% 375
90% 453
95% 1141
98% 1203
99% 1656
100% 1797 (longest request)おぉ、まぁまぁ早くなりましたね。
今回の環境はwindowsでおこないました。
次は、コネクションプールにたまってるセッションがどういう状態なのか調査します。
OracleMaster11g Bronzeを取得しました
先週の土曜日に「Bronze DBA11g」をゲットしBronzeになれました。
6年前くらいに旧制度のOracle9iSilverを取得していたので
9iFellowを申請して、11gのDBAをとって11gBronzeとしました。
久しぶりにOracleMasterの勉強をしていて思ったのは、、、
やはり問題の意味が分かり難い、というか引っかけ問題が多いのかもしれないけど
色々出題者の思い通りに引っかかった結果正解率68%という微妙な結果になりました。
まぁ受かったから良いのですが、ちょっと納得いかない結果かなぁというのが正直な所です。
でも以前受けたときに比べて試験問題内に「〜をすべて選びなさい」って問題が少なかったのが印象的です。
多少ゆるくなってるのだろうか。
しかし48問で75分の試験時間は選択式にしては長すぎではないかとおもいました。
実際30分くらいで終わったし
次はSilverを受ける予定です。
