SQLiteのメリッド

ちまたで多く使われているデータベースでは、MySQLやPostgreSQL（ポスグレ）なんでしょうけど、私は、SQLiteが好きだ。そして、私のように専用サーバを持たず、レンタルサーバのみの環境の方には、MySQL、PostgreSQLよりもSQLiteのほうが、絶対に幸せになれると思う。

データがファイルひとつなので、バックアップが超簡単！
SQLiteは、サーバー型でないため、データはたったひとつのファイルにまとめられている。バックアップするには、それをFTPでダウンロードするか、そのサーバー上でコピーするだけだ。MySQLでも、ツールを使えばバックアップはできるけど、ファイルのコピーだけで完結する簡潔さには、かなううまい。なお、ファイル名もなんでもよく拡張子もあってもなくてもいい。でも、バージョン２は.sqliteバージョン３は.sqlite3としている人が多いのかな。USBにデータファイルをコピーして、外出先のローカルサーバーで、すぐにSQLiteを使うなんて、当たり前にできる。MySQLでやろうとしたら気が遠くなる。-----DBのテーブル構成を変えたい？そう、なら、サーバーからダウンロードして、デスクトップのツールで変更して、ついでに入らなくなったデータ消去して、新たな更新データもいれておいて、アップロードすれば終わり。ローカルとリモートの同期が超簡単なのもDBがファイルだから。
多くのレンタルサーバMySQLよりも高速に作動する
レンタルサーバのMySQLの遅さに参っている人は多い。もっともこれはMySQLが悪いのでなく、レンタルサーバー業者が多くのアカウントをひとつのMySQLサーバーに押し込んでいるだけ。データベースサーバはサーバに大きな負担をかけるので、せっかくの早いと言われるMySQLが劇遅になってしまう例も見かけられる。それに対し、SQLiteはサーバー上の自分のスペースに置くだけなので、借りているWEBサーバさえまともならば、快適に使える。もちろん、WEBサーバも、共用であれば、MySQLのような副作用もあり得るわけであるが、SQLiteがサーバーに優しい動作であるので、多くの利点がある。なお、同じ環境では、MySQLと同じか、少し早いとのこと。WEBで検索すると書込が遅いという記事があるが、それはテスト方法を間違っているだけ（トランザクションを適切に使っていない）。
SQL機能的に問題ない
基本的なSQL文はすべて使える(SQL92準拠)。ここが(SQLite が認識できる SQL )詳しい。ビュー、トリガーもOK。check制約などが使えないが、それがないのがSQLiteなのだ。プロ中のプロが大規模サイトで使うには非力であっても、通常*1の使い方で問題となる面はでてこないであろう。
設定が容易。
パスワード設定がいらないので、MySQLを使ったことのある人ならば、「えっ、もう繋がるの？」とびっくりするだろう。
納入が容易。
私は関係ないが商売として、簡易的なデータベースシステムを使ってWEBシステムを構築しているのであれば、納入の容易さは、大きな利点となるだろう。なんせ、HTMLと同じコピペで納入できるのだから。ライセンスも心配ない。
PHP5では、SQLite3が標準で使える。PHP4でもSQLite2が標準で使える。
何もしなくても、いきなりプログラムを書けば自動的にデータベースファイルが作成される。標準バンドルはとてもありがたい。
データ型の概念が希薄であり、長さも柔軟、PHPと、とても相性がよい。
多くのデータベースサーバは、テキストの長さを決めなくてはいけなかったりする。（もちろんそうでない型もあるが、基本としてそうなっている。）SQLiteではchr(20)なんて必要ない。
無料であり、なんとコアソースコード著作権を主張していない。ライセンスの心配は一切いらない。
神様のような存在である。
容量が少なくて済む。コアは225Kbしかない。
rubyでもSQLiteが標準になった（そうだ）
rubyは使ったことないので、よく知らないけど。
（追記：ruby単体でなく、ruby on railsのデフォルトDBとして採用されたそうです。）
このように、多くの利点があるSQLite、使わない手はない。


私は特に　１　の利点は、ものすごく大きいと思う。外部サーバーから、FTPでダウンロードしてデータベースの中身をちょちょっと修正して、すぐにアップロードできる。なんて、MySQLでは絶対にできないでしょう。


一方で、こんなデメリットは知っておくべきだろう。
パスワード設定がない
ファイル形式であり、ユーザー管理の概念がないため、SQLite自身には、セキュリティ機能はない。自分で、ファイルへのアクセス権限をしっかりとコントロールする必要がある。サーバーのドキュメントルート以下に置く場合は、htaccessの設定が必須だろう。ドキュメントルートより上に置けば、外部の第三者からのセキュリティに問題があることはほとんどないだろう。
書込がダブると書込エラーになる
サーバー型でないので、複数の書込を順次処理することができない。最初の人が書込をしている間に、次の人が書込をしようとするとエラーになるので、そのケアーをする必要があるシステムもあるかもしれない。個人やSOHOのCMS（コンテンツマネジメントシステム）やブログ程度であれば、問題にあることはないだろう。読み出しはダブってもOK。頻繁に不特定多数がデータベースに書き込むような処理は苦手だろう。
バージョン２と３でデータベースの互換性がない。
２と３は別物と考えたほうがいい。PHPからのアクセスに限って言えば、SQLite2は、通常のsqlite_～関数を使ってアクセスするが、SQLite3は、PHP5.1以上で、かつ、PDOというデータベースドライバを通じてしか操作できないので、２->３のアップグレードは注意を要する。（そんなに難しくはないけど）。なお、PHP4,5でPDOを使わずにSQLite3を使う方法もあるようだが、PHP本体のリビルドが必要なようなことと、windouws環境が揃っていないことから、現時点(2008/1)ではお薦めできない。もちろんPDOを使えばアクセスできるので、皆さんPDOを使いましょう。
管理ツールでは、phpMyadminに劣るものしかない。
別記事で私の使っているツールをあげておきます。phpMyadminには及ばないが、しかし十分である。
日本語情報は、MySQLに比べて少ない。
皆さんで盛り上げていきましょう。ちなみに、SQLiteをやってみようは、よくまとまっておおられます。

SQLiteの基礎

コマンドラインツールでの入力方法
コマンドプロンプトから次のように実行するとSQLiteのコマンドラインツールが起動し、指定したデータベースに接続します。
sqlite3 データベース名
コマンドラインツール上では、SQL文を実行したり、コマンドラインツールに関する設定を行うようなSQLiteコマンドなどを実行することができます。
例えばSQLiteコマンドの「.show」を実行してみます。
今度はSQL文を実行してみます。

コマンドであればコマンドに加えて必要に応じて引数を入力してEntereキーを押すと実行されます。またSQL文であれば最後に「;」を入力されるまでが一つのSQL文として扱われますので、「;」まで入力を行ってからEnterキーを押す事で実行されます。

ただ特にSQL文では一つのSQL文が非常に長くなることがあります。全ての文を一度に入力しようとすると入力し難かったり、どこまで入力したか分かりにくい場合があります。

このような場合、コマンドラインツールでは分割して入力することができます。途中まで入力した時点でEnterキーを押して下さい。

SQL文は最後に「;」が現れるまでが一つの文のため、まだ入力の途中だと判断されると「...>」と表示されて続けて入力を行うことができるようになります。

では残りの部分を入力していきます。

この時、空白を一つあけて入力する必要はありません。コマンドの途中でEnterキーを押すと自動的に前と後の文は別の単語として扱われます。

SQL文の場合は「;」が含まれる文が入力されてEnterが押された時点で文の入力が完了したと判断されて実行されます。

まとめて入力しても分割して入力しても結果は同じなので場合に応じて使い分けて下さい。

SQLコマンドの場合

なお、SQLコマンドの場合は分割して入力することはできません。例えば「.mode」コマンドは引数を一つ指定しますが、引数を指定しない時点でEnterキーを押すとエラーとなります。

SQLコマンドの場合は分割して入力を行わずに最後まで入力を行ってから実行して下さい。


SQLiteでは多くのキーワードが定義されています。例えばTABLEやSELECTといった言葉はキーワードです。キーワードは予約語とも呼ぶ場合があります。

テーブル名やデータベース名は識別子と呼ばれます。識別子にはアルファベットや数字などを組み合わせて付けることができます。(例えば「booktable」や「name」など)。ただしキーワードはそのままでは識別子として使用することができません。


識別子とキーワード

キーワードを識別子と使用したい場合には、次の4つのいずれかの方法を使います。

'keyword'
"keyword"
[keyword]
`keyword`
シングルクオーテーション(')でキーワードを囲った場合、文字列の値として扱われます。識別子を記述すべきところにシングルクオーテーションで囲んだ文字列の値を記述すると識別子として扱われるためこの形式でも指定することができます。



ダブルクオーテーション(")、角括弧([])、グレイヴ・アクセント(`)でキーワードを囲った場合、識別子として扱われます。(なお文字列を記述すべきところにダブルクオーテーションで囲んだ識別子を記述すると文字列として扱われます)。



なお角括弧はAccessやSQL Serverで使われている方式でグレイブ・アクセントはMySQLで使われている方式です。この2つの方式はそれぞれのデータベースとの互換性を保つために用意されています。

以上のことからキーワードを識別子として使用するにはダブルクオーテーション(")で囲むようにしておけばいいかと思います。

キーワードの一覧

キーワードを識別子として使用するための方法を記載してきましたが、SQLiteでは多くのキーワードでクォートしなくても使用できるようになっています。

例えば「TEMP」はキーワードの一つですが、クォートしなくてもテーブル名として使用することができます。



ただクォートしないで使用すると分かりにくくなり不要な間違いを起こしかねません。またそれ以前の問題としてキーワードを識別子としてあまり使用しない方が望ましいと思います。

下記にキーワードの一覧を記載します。必ずクォートが必要なキーワードと、必要でないキーワードに分けて記載します。

クォートが必須のキーワード：

ADD
ALL
ALTER
AND
AS
AUTOINCREMENT
BETWEEN
CASE
CHECK
COLLATE
COMMIT
CONSTRAINT
CREATE
CROSS
DEFAULT
DEFERRABLE
DELETE
DISTINCT
DROP
ELSE
ESCAPE
EXCEPT
EXISTS
FOREIGN
FROM
FULL
GROUP
HAVING
IN
INDEX
INNER
INSERT
INTERSECT
INTO
IS
ISNULL
JOIN
LEFT
LIMIT
NATURAL
NOT
NOTNULL
NULL
ON
OR
ORDER
OUTER
PRIMARY
REFERENCES
RIGHT
ROLLBACK
SELECT
SET
TABLE
THEN
TO
TRANSACTION
UNION
UNIQUE
UPDATE
USING
VALUES
WHEN
WHERE
クォートが不要のキーワード：

ABORT
AFTER
ANALYZE
ASC
ATTACH
BEFORE
BEGIN
BY
CASCADE
CAST
COLUMN
CONFLICT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
DATABASE
DEFERRED
DESC
DETACH
EACH
END
EXCLUSIVE
EXPLAIN
FAIL
FOR
GLOB
IF
IGNORE
IMMEDIATE
INDEXED
INITIALLY
INSTEAD
KEY
LIKE
MATCH
OF
OFFSET
PLAN
PRAGMA
QUERY
RAISE
REGEXP
REINDEX
RELEASE
RENAME
REPLACE
RESTRICT
ROW
SAVEPOINT
TEMP
TEMPORARY
TRIGGER
VACUUM
VIEW
VIRTUAL
システムで使っている識別子

下記の識別子はキーワードではありませんが、SQLiteシステムが使用している識別子です。他のテーブル名などに識別子として使うことも可能なようですが、使用しないほうがいいと思われます。

_ROWID_
MAIN
OID
ROWID
SQLITE_MASTER
SQLITE_SEQUENCE
SQLITE_TEMP_MASTER
TEMP


SQL文でのコメントの記述

SQLiteでSQL文にコメントを記述するには次のいずれかの方法を使用します。

-- コメント
/* コメント */
コメントは単なるメモであり、SQL文の実行時に無視されて何も影響を与えません。

「-- コメント」の形式でコメントを記述した場合、「--」から行末までに記述された文字列をコメントとします。

「/* コメント */」の形式でコメントを記述した場合、「/*」から「*/」までに記述された文字列をコメントとします。

なおコメントを付けてINSERT文を実行しても、「.dump」コマンドでダンプした内容にはコメントは残っていません。

その為、コマンドツール上でコマンドを記述してもあまり意味はありません。

単語

欺く　あざむく
焦る　あせる
褪せる　あせる
促す　うながす
敬う　うやまう
うんざりする　
煽てる　おだてる
脅かす　おびやかす
帯びる　おぶる
嵩む　かさむ　増加
合致する　がっちする
かぶれる　皮膚が赤くはれてかゆくなる
鍛える　きたえる
食い違う　くいちがう　一致ではない
潜る　くぐる
覆す　くつがえす
マーケティング・コンサルティング
試みる　こころみる
拗れる　こじれる
誤魔化す　ごまかす
凝らす　こらす
懲りる　こりる
遮る　さえぎる
さえずる　鳥の音
冴える　冴える　　冷え込む、光・音・色などが澄み切る、
妨げる　さまたげる
障る　さわる
試験をしくじる、会社をしくじる、得意先をしくじる
慕う　したう
萎む　しぼむ
廃れる　すたれる
リファクタリング

今年のクリスマス

雪が散らしているはずクリスマスが、美しい雪がなくて、寒い風だけさ、面白くないなあ

株式会社ノアテックを設立しました

情報システムをご利用になるお客様の立場に立ち、企画・提案から運用・保守まで一貫したソリューションをご提供する。
これまでに育ってきた技術力を一層高め、常に情報リテラシーを磨き、顧客満足度を重視しながら、より良いシステム開発とソリューションの提供に全力を尽くして、可能な限りの社会貢献を果たして参ります。
よろしくお願いします。

株式会社ノアテック
www.noah-tec.com

柠檬的错觉

1.爱是世界上最甜蜜的痛,痛是为了得到世界上最甜蜜的爱.
2.其实不想写,其实不想回忆,其实我希望永远记不起. 但痛苦一天天积累,希望一天天枯萎. 脑海里浮现着但丁的神曲, 总以为那是很久远的过去,没想到那竟是熟悉的回忆. 总以为那是很漂渺的唏嘘,想不到也有现实的异域.
3.在爱的路上潇洒的我,遇到了比我更潇洒的你. 我像潇洒的风,你像潇洒的雨. 风雨之中,我发现自己潇洒地爱上了你.
4.我把你放在一滴泪里,幻想千年以后化成琥珀. 我不敢低头,怕那滴眼泪滴落,碎了千年的梦.
5.梦醒之后,注定要启程,单薄的身影,沉重的脚步,都取代来时那轻盈的步伐. 让我离开这虚假的天空,深深的脚印刻印下你给的伤痕累累. 红肿的双眼,颤抖的双肩,都证实着伤寂的泪水. 但愿他能酿一杯涩酒,在每一个想你的深夜品尝.
6.孤独,这是为自由付出的代价. 潇洒,这是孤独的外衣. 7.人生若只如初见,春风扑面桃花艳. 人生若只如初见,何事秋风悲画扇.

PL/SQL 勉強ノート

1．
declare
　type empno_array is table of varchar2(3);
　tep empno_array := empno_array('E01','E02','E03','E04','E05');

begin

　-- 配列の最後の要素を削除
　tep.trim;
　for ep_cnt in 1..tep.count loop
　　dbms_output.put_line('ep1:' || tep(ep_cnt));
　end loop;

　-- 配列の最後の要素を２つ削除
　tep.trim(2);
　for ep_cnt in 1..tep.count loop
　　dbms_output.put_line('ep2:' || tep(ep_cnt));
　end loop;
end;

Oracle PL/SQL

目次
はじめに
対象者
コメント
構造
コーディング規約
ハローワールド
データ形式
演算子
制御構造
データ処理
エラー処理
関数
API
デバック


sqlplusを使って、Helloworldから始まり、データ形式、制御構造、関数の書き方などをサラっと学習します。

今後、オラクル社のリファレンスで学習できるようにするのが目的です。

参考資料には、オラクル社へのマニュアル・リンクを用意しました。

入門書に書いてある例えなどはありません。

言語習得に必要な部分のみしか書きませんので、sqlplusでサンプルを実行しながら学習してください。

PL/SQLと他の言語を比較してポイントとなる部分は赤 マークしています。



対象者
Oracleをこれから始める方。



コメント
コメントの書き方は二つあります。


-- １行のコメントはハイフン(-)２つです。


/*
複数行のコメントはC言語などのように
スラッシュ、アスタリスクです。
*/



構造
下記の構造をPL/SQLブロックという
Begin End;のみ必須
入れ子で書くこと(=ネストブロック)も可能
サンプル


Declare
-- 宣言
Begin
-- 処理
Exception
-- エラー処理
End;


ネストブロック


Declare
Begin
Declare
-- 宣言
Begin
-- 処理
Exception
-- エラー処理
End;
Exception
End;



declare
tmp_data number := 0;
begin
/* nest block */
declare
tmp_data number;
begin
select 1/0 into tmp_data from dual;
exception
when ZERO_DIVIDE then
tmp_data :=SQLCODE;
insert into test_tbl values (tmp_data, 'testok');
commit;
end;
exception
when others then
null;
end;
/




コーディング規約
コーディング規約はない。



ハローワールド
サンプルテーブル


SQL> desc test_tbl
名前 NULL? 型
---- ----- ----------------------------
COL1 NUMBER
COL2 VARCHAR2(8)


hello.sql作成
hello.sqlで以下のPL/SQLプログラムを保存。

declare
test_var date;
begin
select sysdate into test_var from dual;
end;
/


解説

文はセミコロン(;)で終了
ブロックの最後にスラッシュ（/）が必要
大文字、小文字は関係ない
実行

SQL> @hello.sql

PL/SQLプロシージャが正常に完了しました。




データ形式
データ型(スカラー型・プリミティブ型)
NUMBER 符号付整数、固定小数点数、浮動小数点数
BINARY_INTEGER NUMBER型よりメモリ領域が少ない
PLS_INTEGER NUMBER型より早い
CHAR
VARCHAR2
LONG 互換性のためにあるLOB型を推奨
LONGROW 互換性のためにあるLOB型を推奨
NCHAR
NVARCHAR2
RAW 互換性のためにあるLOB型を推奨
UROWID ROWID
BOOLEAN TRUE | FALSE | NULL
DATE
BFILE
BLOB
CLOB
NCLOB

データ定義
変数名、データ型の順番で定義
文はセミコロン(;)で終了
初期値は:=で 指定
NOT NULL制約が可能
定数の定義はCONST
変数定義(=データ定義)


変数名　　　　型
test_number 　number(10);
test_string varchar2(100);
jpn_tax number(5,2) := 1.05;
test_number2 number(3) := 10 not null;
test_date date := sysdate;
MAX_VALUE const number := 1.05;


属性定義
PL/SQLでは、テーブル定義からデータ型を参照することが出来る。
サンプルのテーブル


create table emp_tbl (
empid number,
emp_name varchar2(20)
)
/


属性定義の仕方



declare
test_no emp_tbl.empid%type;
begin
end;
/


解説

emp_tblテーブルのempidカラムのデータ型を%typeで 取得できるのでテーブル定義が変更してもPL/SQLの実装を修正する必要がなくなる。

索引付き表（スカラー型の配列）
type 索 引付き表型名 is table of スカラー型 index by binary_integer;


declare
type test_array is table of hoge_table.fuga_txt%type index by binary_integer;
test_variable test_array;
cursor c_hoge is select hoge_id,fuga_txt from hoge_table;
begin
for wk_cursor in c_hoge loop
test_variable(wk_cursor.hoge_id) := wk_cursor.fuga_txt;
end loop;
end;
/
show errors;


レコード
type レコード型名 is record (列名 データ型,　列名 データ型, ...);


declare
-- CAUTION!!
-- A user execute this program on sqlplus who must input command that 'set
serveroutput on'.
--
-- definition
type t_rec is record (
id_record test_record_tbl.id_record%type,
record_name test_record_tbl.record_name%type
);
-- declaretion
wk_val t_rec;
cursor c_sample is select id_record, record_name from test_record_tbl;
begin
open c_sample;
loop
fetch c_sample into wk_val;
dbms_output.put_line(wk_val.record_name);
exit when c_sample%notfound;
end loop;
dbms_output.put_line('end loop');
close c_sample;
end;
/
show errors;


レコードの索引付き表(二次元配列)
type レコード型名 is record (列名 データ型,　列名 データ型, ...);
type レコードの索引付き表型名 is table of レコード型名 index by binary_integer;

declare
-- the generic program language say multi-dimensional array.
-- existed samle function is 'varray' object.
-- 1. define record
type r_sample is record (
id_record test_record_tbl.id_record%type,
record_name test_record_tbl.record_name%type
);
-- 2. define arrray
type idx_ is table of r_sample index by binary_integer;
-- 3. declaration
dimen_ary idx_;
cursor c_tmp is select id_record, record_name from test_record_tbl;
begin
for wk_emp in c_tmp loop
dimen_ary(wk_emp.id_record) := wk_emp;
end loop;
end;
/
show errors;




演算子
= ==ではないので注意
!=
<> != と同意
>
<
<=
>=
IN
NOT IN
BETWEEN x AND y
NOT BETWEEN x AND y
LIKE
NOT LIKE
IS NULL
IS NOT NULL

PL/SQLは、NULL は何と比較してもNULLである。


制御構造
分岐
if文

if 条件1 then
-- 処理
elsif 条件2 then
-- 処理
end if;



declare
test_var number := 1;
begin
if test_var = 1 then
null; -- null は何もしない時に記述
elsif test_var = 2 then
null;
end if;
end;
/


反復
for文

for カウンタ in min...max [reverse] loop
end loop;


以下の例は、グローバルなtest_var変数とfor文中のtest_var変数２つを定義している。

for文中のカウンタであるtest_var変数は暗黙で定義しているので型を書いたりしていない。

サンプルのテーブル定義


create table test_tbl (col1 number);



declare
test_var number := 100;
begin
for test_var in 1..10 loop -- ローカルとしてtest_var変数を暗黙定義.
insert into test_tbl values (test_var);
end loop;
insert into test_tbl values (test_var);
commit;
end;
/


前判定反復
while文

while 条件 loop
end loop;



declare
test_var number := 100;
begin
while test_var <= 110 loop
insert into test_tbl values (test_var);
test_var := test_var + 1; -- インクリメント演算は出来ない
end loop;

commit;
end;
/


後判定反復
loop文

loop
end loop;


PL/SQLではC言語のように、while文を反復の最後に設定できないのでif文とloop文で行う。



declare
test_var number := 100;
begin
loop
-- 処理を書く.

-- 後判定
if test_var = 100 then
exit; -- loopを抜ける
end if;
end loop;
end;
/


if文を利用してexitするのは、exit when文を使えば１ 行に出来る。


declare
test_var number := 100;
begin
loop
exit when test_var = 100;
end loop;
end;
/




データ処理
select into文
select into文は、１レコードの取得しか出来ません。
複数のレコードの場合はカーソルを使います。

select カラム名 into 変数 from テーブル [where 検索条件など]




declare
test_var date;
begin
-- dual表はテストなどダミーで使う表.
select sysdate into test_var from dual;
end;
/


カーソル
select文の結果セット（データ集合）に対して、１レコードづつ処理していく時に利用する。
PL/SQLのカーソルは戻る事が出来ない。(Javaのjava.sql.ResultSetなどは戻る事が可能)
カーソル作成手順
カーソル型の定義
カーソル用の変数定義
オープン
フェッチ(fetch)（定義した変数にデータを格納する事）
データ存在チェック
処理
クローズ

SQL> desc test_tbl
名前 NULL? 型
---- ----- --------------
COL1 NUMBER
COL2 VARCHAR2(8)



declare
/* カーソル型の定義 */
cursor c_test is select col1, col2 from test_tbl;
/* カーソル用の変数定義 */
c_test_val c_test%rowtype;
begin
open c_test;
loop
fetch c_test into c_test_val;
exit when c_test%notfound;
end loop;
close c_test;
end;
/


解説
%rowtypeでcol1の型(NUMBER)とcol2の型 (VARCHAR2(8))を取得している。%notfoundで存在チェック

カーソルforループ

for レコード型変数名 in カーソル loop
end loop;


カーソルfor loop でレコード型変数名をforスコープ内に出来る。


declare
/* cursor definition */
cursor c_test is select col1, col2 from test_tbl;
begin
for c_test_val in c_test loop
exit when c_test%notfound;
end loop;
end;
/




エラー処理
例外処理と通知
exceptionブロックに例外処理を書く
バインド変数で外部に通知する
PL/SQLではexceptionからbeginには復旧できない
例外をハンドリングしてexceptionブロックが正常に終了するとエラーがなかったようになる。
このサンプルは、sqlplusで実行するとエラーが出ます。各自、情報収集して解決してください。


declare
cursor c_tmp is select col1, col2 from test_tbl;
tmp_val c_tmp%rowtype;
begin
open c_tmp;
loop
fetch c_tmp into tmp_val;
exit when c_tmp%notfound;
end loop;
close c_tmp
:status := '0'; /* bind variable */
exception
when others then
if c_tmp%isopen then
close c_tmp;
end if;
:status := '1';
end;
/


解説
when others thenは、C言語ではswitch文の defalt句、Javaではjava.lang.Exception

サンプル2

declare
tmp_date date;
begin
insert into test_tbl values (111, 'test');
select sysdate into tmp_date from dual where 1=2;
commit;
exception
when others then
commit;
end;
/


実行結果

SQL> @test9.sql

PL/SQLプロシージャが正常に完了しました。


解説
例外処理(exception)を書いているので正常終了になる

例外ハンドラ
例外一覧

例外名 ORA-XXX SQLCODE 条件
ACCESS_INTO_NULL 6530 -6530 初期化していないオブジェクト（アトミックNULL)の属性に代入した時
COLLECTION_IS_NULL 6531 -6531
CURSOR_ALREADY_OPEN 6511 -6511
DUP_VAL_ON_INDEX 1 -1 UNIQUE索引に重複(Duplicate)データを格納した時
INVALID_CURSOR 1001 -1001 不正カーソル処理時
INVALID_NUMBER 1722 -1722 文字列から数値の置換に失敗した時
LOGIN_DENIED 1017 -1017 ログイン失敗時
NO_DATA_FOUND 1403 +100
NOT_LOGGED_ON 1012 -1012 Oracleに接続していないプログラムがデータベースコールを発行した時
PROGRAM_ERROR 6501 -6501 PL/SQL内部に問題点がある時
ROWTYPE_MISMATCH 6504 -6504
SELF_IS_NULL 30625 -30625
STORAGE_ERROR 6500 -6500 メモリ関連エラー時
SUBSCRIPT_BEYOND_COUNT 6533 -6533 コレクションの要素数より大きいインデックスでアクセスした時
SUBSCRIPT_OUTSIDE_LIMIT 6532 -6532 有効範囲外で表、varrayなどにアクセスした時
SYS_INVALID_ROWID 1410 -1410
TIMEOUT_ON_RESOURCE 51 -51
TOO_MANY_ROWS 1422 -1422 select into文が複数行を返した時
VALUE_ERROR 6502 -6502 算術、変換、切り捨て、サイズ制約などのエラー時
ZERO_DIVIDE 1476 -1476

when 例外名 then

declare
tmp_data number;
begin
select 1/0 into tmp_data from dual;
exception
when ZERO_DIVIDE then
tmp_data := 10;
end;
/



declare
tmp_data number;
begin
select col1 into tmp_data from test_tbl;
exception
when no_data_found or too_many_rows then
null;
end;
/



declare
tmp_data number := 0;
begin
select 1/0 into tmp_data from dual;
exception
when no_data_found then
null;
when too_many_rows then
null;
when zero_divide then
null;
when others then
null;
end;
/


エラーメッセージ取得
SQLCODE関数とSQLERRM(ERRor-Message)関数を利用
SQLCODEとSQLERRMは必ず変数に代入し なければならない。

declare
tmp_id number;
tmp_data test_tbl.col2%type := '';
error_msg varchar2(2000);
begin
select 1/0 into tmp_data from dual;
exception
when others then
if SQLCODE = -1476 then
tmp_id := SQLCODE;
insert into test_tbl values(tmp_id, 'test ok');
commit;
else
tmp_id := SQLCODE;
error_msg := SQLERRM;
insert into test_tbl values(tmp_id, tmp_data);
commit;
end if;
end;
/




関数
関数の種類
Function
戻り値が１つ

Procedure
戻り値が複数

構文
Function構文

Function 名前 [ ([引数名 [IN | OUT | INOUT]? データ型 [default デフォルト値]? ]* ) ]
Return データ型 is [宣言部]
Begin
[実行部]
[Exception]
End;


Procedure構文

Procedure 名前[ ([引数名 [IN | OUT | INOUT]? データ型 [default デフォルト値]? ]* ) ] is [宣言部]
Begin
[実行部]
[Exception]
End;


プロシージャのサンプル


declare
procedure test_pcd( tmp_num IN number, tmp_str IN varchar2 ) is
begin
insert into test_tbl values (tmp_num, tmp_str);
end;
begin
test_pcd(99,'99');
commit;
end;
/


function, procedureでは、引数でデータサイズなどの制約をつけられない。


declare
procedure sample1(var in varchar2(20)) is
begin
end;
begin
end;
/
show errors;



SQL> @test04.sql
procedure sample1(var in varchar2(20)) is
*
行2でエラーが発生しました。:
ORA-06550: 行2、列36:
PLS-00103: 記号"("が見つかりました。 次のうちの1つが入るとき:
:= . ) , @ % default character
記号":=" は続行のために"("に代わりました。
ORA-06550: 行4、列3:
PLS-00103: 記号"END"が見つかりました。 次のうちの1つが入るとき:
begin case declare exit for goto if loop mod null pragma
raise return select update while with


制約をつけたい場合は、subtypeを使う

subtype データ名 is データ型


declare
subtype myVChar is varchar2(20);
procedure sample1(var in myVChar) is
begin
null;
end;
begin
null;
end;
/
show errors;


共通ライブラリ化の方法
PL/SQLでは、共通ライブラリ化することを、ストアドプロ グラムと呼ぶ
他の言語では、一般的にファイルとしてライブラリとするが、PL/SQLはデータベース内に格納する
データベーススキーマのオブジェクトであるので実行権限(Execute権限)が必要
PL/SQLをコンパイルしたコードをPコードと 呼ぶ
作成時にコンパイルしているため、解析フェーズが省略される分、パフォーマンスが良い
ユーザに関係なく、システム表領域に作成される
種類 概要
ストアドプロシージャ １つのプロシージャをライブラリ化
ストアドファンクション １つのファンクションをライブラリ化
データベーストリガ イベントハンドラ
パッケージ 上記をまとめる。

ストアド化

create or replace
procedure test_pcd(
tmp_num IN number,
tmp_str IN varchar2
) is
begin
insert into test_tbl values (tmp_num, tmp_str);
commit;
end;
/
show errors;


パッケージ作成
仕様部と本体部を作成する。
仕様部が外部インタフェースになる。
仕様部と本体部は１つのファイルに纏めても、分割しても良い
仕様部を最初にコンパイルしなければならない。
仕様部

create or replace package パッケージ名 is
...
end パッケージ名;



create or replace package sample_package is
procedure sample1(wk_col1 in number);
function sample2(wk_col2 in varchar2) return varchar2;
end sample_package;
/
show errors;


本体部

create or replace package body パッ ケージ名 is
...
end パッケージ名;



create or replace package body sample_package is

procedure sample1(wk_col1 in number) is
begin
insert into test_tbl values (wk_col1, 'package');
commit;
end;

function sample2(wk_col2 in varchar2) return varchar2 is
begin
insert into test_tbl values ('11', wk_col2);
commit;
return '0';
end;

end sample_package;
/
show errors;


作ったものをテストするプログラム


declare
wk_str varchar2(100);
function test_fnc(wk_col1 in number) return number is
begin
return 0;
end;
begin
sample_package.sample1(1);
wk_str := sample_package.sample2('1');
end;
/
show errors;


解説
パッケージもオブジェクトなので、ピリオドを利用して呼び出す。 適当なファイル名をつけて実行してみてください。

トリガー
ユーザはトリガーが起動した事は分からない
DMLの前後などに設定する。
プログラムでよくあるイベントハンドラである。
トリガーは引数を持たない。
注意点
トリガーは、自動起動するため、開発者はその存在を忘れる可能性がある。 運用後の保守、デバック等が複雑になる傾向があるので、基本的にはトリガー機能を使わず、代案がない場合に利用するように心がける事

トリガーの管理

alter triger トリガー名 [enable | disable]
user_triggers



create or replace trigger トリガー名 before
[insert | update | delete] or on テーブル名
declare
begin
end;
/



create or replace trigger sample_trigger before
insert or update or delete on test_tbl
declare
begin
insert into trigger_log values (99,'trger go');
end;
/


実行

SQL> insert into test_tbl values (1, '111');

1行が作成されました。


結果

SQL> select * from trigger_log;

COL1 COL2
---------- ----------------
99 trger go




API(Application Program Interface)
Oracle9i PLSQL パッケージ・プロシージャおよびタイプ・リファレンス リリース2(9.2)がAPIのドキュメントになる。 sysユーザ権限でdesc standardを実行すれば、 standardと呼ばれる、標準APIが分かる。 以下でソースコードが読める。


select text from user_source where name= 'STANDARD';




デバック
プログラムに空白行を含めない。
PL/SQLでは、空白行は実行時に１行として含めない。つまり空白行は実行時に削除 する。 そのため、エ ラー時の行番がエディタ上の行番号とづれてしまう。 エディタでの開発の場合は、プログラムに空白行を含めない方が効率がよい。
user_errors表をつかう。
PL/SQLでは、ストアドプログラムは、プログラムにエラーが存在しても、取り合え ずデータベースに登録する。どこにエラーがあるか判断するには、user_errors表を確認する。
user_source表をつかう。
コンパイルした結果とソースコードをこの表に記録しているので、デバック時に役立つ
show errorsコマンド
user_errors表を確認するのは、手間が掛かるのでソースコードの最終行に show errorsコマンドを書いておく事でデバックが簡単になる。
他の言語よりコンパイルのサイクルを短くする。
PL/SQLをエディタで開発する場合は、ソースコードを全て記述してからコンパイル するのではなく少しコーディングしたら直ぐにコンパイルするように心がける事。PL/SQLの開発環境は粗悪なので非常に有効である。