Last Up Date at 2019/06/02
OKLab - Oracle PL/SQL入門
http://www.oklab.org/oracle/plsql.xhtml
忘れっぽいエンジニアのＯＲＡＣＬＥ／オラクルＰＬ／ＳＱＬリファレンス
http://oracle.se-free.com/plsql/plsql.html
001 とにかく、ひとつ作ってみよう procedure
002 とにかく、ひとつ作ってみよう function
003 カーソルを使った初歩的サンプル
004 パッケージの基本
005 トランザクションの範囲
20080501 PLSQLでローカルファイルにアクセスする
20080616 コメント
20080616 変数宣言の場所
20080616 プライベート関数
20081008 縦横変換を行う ファンクションを作成してみた
エディターなどで以下のソースを作成する。
create or replace procedure test_proc2 is
lngWork1 number;
lngWork2 number;
begin
lngWork1 := 1;
lngWork2 := lngWork1 + 2;
dbms_output.put_line(lngWork2);
end;
/
|
「警告: プロシージャが作成されましたが、コンパイル・エラーがあります」が出たら、どこかに文法エラーがあるはず。そのときは、
show errors とすれば、エラーの個所が表示されます。
「プロシージャが作成されました」が表示されれば、成功。
では、実行してみましょう。
SQL*Plusから、次のように実行します。
exec test_proc2; [enter]
「PL/SQLプロシージャが正常に完了しました」が表示されれば、無事実行です。
ところで、 dbms_output.put_line(lngWork2); というのは、本来デバッグ用につかうやつで、console out みたいなものなのですが、これを見るためには、次のオマジナイが必要になりまます。
SQL*Plusから、次のように実行します。
set serveroutput on
では、もういちど、exec test_proc2; [enter] をしてみましょう
SQL> exec test_proc2;
3
PL/SQLプロシージャが正常に完了しました。
と表示されれば、大正解です。
2001/05/28
fucntion と procedure の違いは戻り値があるかどうかです。 VBでいえば、Function と Sub みたいなもの。
さっきと同様に、次のソースを登録してみましょう。
create or replace function test_func1
return varchar2
is
begin
return 'aiueo';
end;
/
|
文字列 ’aiueo’を返す関数ですが、そのまま実行したら、エラーになります。
戻り値を確認するには、次のように指定して実行します。
SQL> select test_func１ from dual;
TEST_FUNC1
-------------------------------------------------
aiueo
と、表示されます。dual というのは、ダミーの表です。ほかにも戻り値のチェックの仕方があるかもしれませんが、とりあえずの方法でした。
2001/05/28
-- カーソルのテスト
create or replace procedure test_proc3 is
-- カーソル定義
cursor C01 is
select * from test_tbl order by key1;
recTest_tbl C01%rowtype ; --行属性の参照
begin
open C01;
loop
fetch C01 into recTest_tbl;
exit when C01%notfound;
dbms_output.put_line(recTest_tbl.key1 || '-' || recTest_tbl.data1);
end loop;
close C01;
return;
end;
/
|
2001/05/29
パッケージはひとつ以上の関数群をグループ化および、隠蔽化することができる。また、宣言部と本体を別のファイルにすることができるため、メンテナンス性の向上を期待できる。
パッケージは
CREATE PACKAGEC 文とCREATE PACKAGE BODY
文から構成する。
PACKAGECが俗に言うヘッダーとなり、ここに宣言したプロシシャー、ファンクション、変数、カーソルはグローバルなものとなる。
PACKAGE BODY には、グローバルに宣言したプロシシャー、ファンクションの本体、および、ボディー内部でしか参照できないローカルなものを宣言する。
基本形
CREATE OR REPLACE PACKAGE gpacgＡＡＡ
IS
FUNCTION Ｆｕｎｃ１(inValue IN VARCHAR2 ) RETURN NUMBER;
FUNCTION Ｆｕｎｃ２(inValue IN VARCHAR2 ) RETURN NUMBER;
．．．
END;
/
SHOW ERRORS;
CREATE OR REPLACE PACKAGE BODY gpacgＡＡＡ
IS
FUNCTION Ｆｕｎｃ１(inValue IN VARCHAR2)RETURN NUMBER
IS
[変数を宣言]
BEGIN
[処理を記入]
RETURN ．．．． ;
END Ｆｕｎｃ１;
FUNCTION Ｆｕｎｃ２(inValue IN VARCHAR2 )RETURN NUMBER
IS
[変数を宣言]
BEGIN
[処理を記入]
RETURN ．．．． ;
END Ｆｕｎｃ２;
END gpacgＡＡＡ;
/
show errors;
という形になる。
ヘッダと本体は、関数の名前はもちろんのこと、引数名も同じでなければならない。
2001/07/22
超簡単な実例
CREATE OR REPLACE PACKAGE PTEST IS FUNCTION FUNC1 RETURN NUMBER; END; /CREATE OR REPLACE PACKAGE BODY PTEST IS FUNCTION FUNC1 RETURN NUMBER IS BEGIN RETURN 1; END; END; /
SQL> SELECT PTEST.FUNC1 FROM DUAL; FUNC1 ---------- 1
PL/SQLでは、BEGIN TRANの概念がなく、前回コミットした範囲からがトランザクションの範囲となる。
VBから呼ばれた時にもこの考えは適用できる。よってクライアント側の処理とPL/SQLを使用したサーバー側の処理は同一トランザクション内で構築することは、容易に可能である。
2001/07/22
ネットで調べてみると、UTL_FILE パッケージ を使えば、できるような事が書いてあった。
http://www.shift-the-oracle.com/plsql/utl_file/create-directory.html
ただし、初期化ファイルにて、やりとりするディレクトリを予め宣言しておかなければならない。指定したフォルダのみ、アクセス可能になるらしい。配下の従属フォルダは適用されない。
ALTER SYSTEM SET UTL_FILE_DIR='c:/test','c:/test/testsub' SCOPE = SPFILE ;
実行してみた。
実行しただけでは、反映されない。そもそも、変更不可とある。
UTL_FILE_DIR 初期化パラメータの変更を適用するには Oracle の再起動が必要。
とあるので、インスタンスを再起動してみる。再起動は、サービスの停止／開始にて行ってみる。
以下のSQLで、確認できるらしい
SELECT NAME, VALUE FROM V$PARAMETER2 WHERE NAME='utl_file_dir' ;
できてるじゃん！
できてるじゃん！
CREATE OR REPLACE PROCEDURE APPH.FILE_READ_TEST
IS
vHandle UTL_FILE.FILE_TYPE;
vDirName VARCHAR(250);
vFileName VARCHAR(250);
vInputBuffer VARCHAR(250);
BEGIN
vDirName := 'c:/test';
vFileName := 'test.txt';
vHandle:=UTL_FILE.FOPEN(vDirname ,vFilename,'r', 250);
BEGIN
LOOP
UTL_FILE.GET_LINE(vHandle, vInputBuffer,250);
DBMS_OUTPUT.PUT_LINE(vInputBuffer);
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('ファイルの終わりを検出しました');
END;
UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHEN OTHERS THEN
UTL_FILE.FCLOSE_ALL;
RAISE;
END;
/
読めてるじゃん
１行コメントは --
複数行コメントは /* 〜 */ である
CREATE OR REPLACE FUNCTION SHODAI.TEST RETURN NUMBER IS --変数はここで宣言する。BEGIN 内部で宣言してはいけない COUNTER NUMBER; BEGIN SELECT COUNT(*) INTO COUNTER FROM DUAL; RETURN COUNTER; END; /SQL> SELECT TEST FROM DUAL; TEST ---------- 1
CREATE OR REPLACE PACKAGE PTEST IS FUNCTION FUNC1 RETURN NUMBER; END; /CREATE OR REPLACE PACKAGE BODY PTEST IS --プロトタイプ宣言に相当する --FUNC2は、PTEST内のプライベートFUNCTION扱いになる FUNCTION FUNC2 RETURN NUMBER; FUNCTION FUNC1 RETURN NUMBER IS BEGIN --この段階で、少なくとも、FUNC2のプロトタイプ宣言に相当するものが存在しなければならない RETURN FUNC2; END FUNC1; FUNCTION FUNC2 RETURN NUMBER IS BEGIN RETURN 1; END FUNC2; END; /SQL> SELECT PTEST.FUNC1 FROM DUAL; FUNC1 ---------- 1 SQL> SELECT PTEST.FUNC2 FROM DUAL; SELECT PTEST.FUNC2 FROM DUAL * 行1でエラーが発生しました。: ORA-00904: "PTEST"."FUNC2": 無効な識別子です。
CREATE TABLE MYPROJ.実験履歴
(
ＳＥＱ VARCHAR2(10) NOT NULL,
版数 NUMBER(3,0) NOT NULL,
キー VARCHAR2(10) NOT NULL,
値 VARCHAR2(10),
CONSTRAINT 実験履歴PK PRIMARY KEY (ＳＥＱ, 版数, キー)
)
/
| ＳＥＱ | 版数 | キー | 値 |
| 0000000001 | 1 | 001 | 1-1-1 |
| 0000000001 | 1 | 002 | 1-1-2 |
| 0000000001 | 1 | 003 | 1-1-3 |
| 0000000002 | 1 | 001 | 2-1-1 |
| 0000000002 | 1 | 002 | 2-1-2 |
| 0000000002 | 1 | 003 | 2-1-3 |
| 0000000001 | 2 | 001 | 1-2-1 |
| 0000000001 | 2 | 002 | 1-2-2 |
| 0000000001 | 2 | 003 | 1-2-3 |
| 0000000002 | 2 | 001 | 2-2-1 |
| 0000000002 | 2 | 002 | 2-2-2 |
| 0000000002 | 2 | 003 | 2-2-3 |
CREATE OR REPLACE TYPE MYPROJ.実験TYPE_REC IS OBJECT( 項目１ VARCHAR2(10) ,項目２ VARCHAR2(10) ,項目３ VARCHAR2(10) ); /
CREATE OR REPLACE TYPE MYPROJ.実験TYPE_TABLE IS TABLE OF 実験TYPE_REC /
CREATE OR REPLACE FUNCTION MYPROJ.実験縦横変換
(
IN_ＳＥＱ IN VARCHAR2
,IN_版数 IN NUMBER
)
RETURN
実験TYPE_TABLE
IS
--戻り値に使う項目を設定するもの
OUT_REC 実験TYPE_REC := 実験TYPE_REC(NULL,NULL,NULL);
OUT_TABLE 実験TYPE_TABLE := 実験TYPE_TABLE(OUT_REC);
CURSOR CSR_縦横変換 IS
SELECT
実験履歴.キー
,実験履歴.値
FROM
実験履歴
WHERE
実験履歴.ＳＥＱ = IN_ＳＥＱ
AND 実験履歴.版数 = IN_版数
;
WK_キー VARCHAR2(10);
WK_値 VARCHAR2(10);
BEGIN
-- 生成時に１件出来てしまっているので、空にする
OUT_TABLE.DELETE;
OPEN CSR_縦横変換;
LOOP
FETCH CSR_縦横変換 INTO ;
--読めた
CASE WK_キー
WHEN '001' THEN OUT_REC.項目１ := WK_値;
WHEN '002' THEN OUT_REC.項目２ := WK_値;
WHEN '003' THEN OUT_REC.項目３ := WK_値;
END CASE;
EXIT WHEN CSR_縦横変換%NOTFOUND;
END LOOP;
CLOSE CSR_縦横変換;
OUT_TABLE.EXTEND; -- １行増やす
OUT_TABLE(1) := OUT_REC;
RETURN OUT_TABLE;
END;
/
SELECT *
FROM
TABLE(実験縦横変換('0000000001','2'))
| 項目１ | 項目２ | 項目３ |
| 1-2-1 | 1-2-2 | 1-2-3 |
リファクタリング その１
上記の例では、CSR_縦横変換 を 明示的に宣言し、OPENやFETCHを 手動で制御している。単純に 回したいだけなら、暗黙カーソルを使えば、ソースはシンプルになる
CREATE OR REPLACE FUNCTION MYPROJ.実験縦横変換 ( IN_ＳＥＱ IN VARCHAR2 ,IN_版数 IN NUMBER ) RETURN 実験TYPE_TABLE IS OUT_REC 実験TYPE_REC := 実験TYPE_REC(NULL,NULL,NULL); --三つの項目があるから、NULLも三つ OUT_TABLE 実験TYPE_TABLE := 実験TYPE_TABLE(OUT_REC); BEGIN -- 生成時に１件出来てしまっているので、空にする OUT_TABLE.DELETE; FOR CSR_縦横変換 IN ( SELECT 実験履歴.キー ,実験履歴.値 FROM 実験履歴 WHERE 実験履歴.ＳＥＱ = IN_ＳＥＱ AND 実験履歴.版数 = IN_版数 ) LOOP CASE CSR_縦横変換.キー WHEN '001' THEN OUT_REC.項目１ := CSR_縦横変換.値; WHEN '002' THEN OUT_REC.項目２ := CSR_縦横変換.値; WHEN '003' THEN OUT_REC.項目３ := CSR_縦横変換.値; END CASE; END LOOP; OUT_TABLE.EXTEND; -- １行増やす OUT_TABLE(1) := OUT_REC; RETURN OUT_TABLE; END; /OPEN CLOSE は、FOR IN の中で 自動的に行われている。また、FETCHも明示的に行う必要がない。
また、FETCH 文が無いため、INTO文もなく、退避する変数（WK_キー,WK_値）も 不要になる。
値を取り出す時には CSR_縦横変換.値 で可能となる。
リファクタリング その２
CREATE TYPE している 実験TYPE_REC 、実験TYPE_TABLE であるが、
FUNCTION 実験縦横変換 を宣言する時には 既に宣言されている必要があるので、外で CREATE TYPE していた。PACKAGE を 用いる事により、一つのリソースにまとめる事ができる。
いろいろ、制約による変更が発生した。
- PACKAGE の中では、OBJECT型のTYPEが定義できなかった→ RECORD型に変更した。※１
- RECORD型 は コンストラクタが不要。宣言だけでよい。※２
- RECOTD型から TABLE型への 移入がうまくできず、実行すると、型が無効と言われた。…詳細は調べていない
ネットのサンプルに従い PIPELINED 方式にする事で、TABLE へ 設定するようにした ※３CREATE OR REPLACE PACKAGE MYPROJ.実験 IS -- TYPEの定義 TYPE 実験TYPE_REC --※１ IS RECORD( 項目１ VARCHAR2(10) ,項目２ VARCHAR2(10) ,項目３ VARCHAR2(10) ); TYPE 実験TYPE_TABLE IS TABLE OF 実験TYPE_REC ; -- FUNCTIONの定義 FUNCTION 縦横変換 ( IN_ＳＥＱ IN VARCHAR2 ,IN_版数 IN NUMBER ) RETURN 実験TYPE_TABLE PIPELINED; --※３ END 実験; /CREATE OR REPLACE PACKAGE BODY MYPROJ.実験 IS FUNCTION 縦横変換 ( IN_ＳＥＱ IN VARCHAR2 ,IN_版数 IN NUMBER ) RETURN 実験TYPE_TABLE PIPELINED --※３ IS OUT_REC 実験TYPE_REC ; --※２ BEGIN FOR CSR_縦横変換 IN ( SELECT 実験履歴.キー ,実験履歴.値 FROM 実験履歴 WHERE 実験履歴.ＳＥＱ = IN_ＳＥＱ AND 実験履歴.版数 = IN_版数 ) LOOP CASE CSR_縦横変換.キー WHEN '001' THEN OUT_REC.項目１ := CSR_縦横変換.値; WHEN '002' THEN OUT_REC.項目２ := CSR_縦横変換.値; WHEN '003' THEN OUT_REC.項目３ := CSR_縦横変換.値; END CASE; END LOOP; PIPE ROW(OUT_REC); --※３ RETURN ; --※３ END 縦横変換; END 実験; /SELECT * FROM TABLE(実験.縦横変換('0000000001',1))PIPELINED についてのメモ。
プロシジャーから大量の行を含む情報を返却する際、普通に実装すると、大量のデータ分のメモリ領域を確保しなければならない。
ここで、行あたりの処理が完了できるならば、処理が完了した行から、行単位に返却すれば メモリ領域の大量に食わなくても良いし、
また、返却レスポンスも行単位に帰ってくるので、向上する。っていうことは、１行の処理が１秒づつかかっていたら、SELECT を投げた方は、１秒毎に 結果が増えることになるのかな？
PIPE ROW(OUT_REC); dbms_lock.sleep(1); PIPE ROW(OUT_REC); dbms_lock.sleep(1); PIPE ROW(OUT_REC);dbms_lock パッケージは、DBA権限を持っているユーザーでも、そのままでは参照できなかった。
SYS でログインし、
Grant execute on sys.dbms_lock to ユーザ名 をしてからでないと、無理だった。
http://oshiete1.goo.ne.jp/qa1030997.html?ans_count_asc=20
http://otn.oracle.co.jp/forum/message.jspa?messageID=8028516
→ 20行くらいやってみた所では、変化なく、一括して出力されたぁ??