Oracle PL/SQL のメモ

Last Up Date at 2009/05/25


OKLab - Oracle PL/SQL入門
http://www.oklab.org/oracle/plsql.xhtml

忘れっぽいエンジニアのORACLE/オラクルPL/SQLリファレンス
http://oracle.se-free.com/plsql/plsql.html

 

001 とにかく、ひとつ作ってみよう procedure 
002 とにかく、ひとつ作ってみよう function
003 カーソルを使った初歩的サンプル
004 パッケージの基本
005 トランザクションの範囲
20080501 PLSQLでローカルファイルにアクセスする
20080616 コメント
20080616 変数宣言の場所
20080616 プライベート関数
20081008 縦横変換を行う ファンクションを作成してみた


001     とにかく、ひとつ作ってみよう procedure

エディターなどで以下のソースを作成する。

create or replace procedure test_proc2  is 
    lngWork1    number;
    lngWork2    number;
    
begin
    lngWork1 := 1;
    lngWork2 := lngWork1 + 2;
    
    dbms_output.put_line(lngWork2);
end;
/
	

これを、SQL*Plusなどから登録する。

「警告: プロシージャが作成されましたが、コンパイル・エラーがあります」が出たら、どこかに文法エラーがあるはず。そのときは、

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


002     とにかく、ひとつ作ってみよう function

fucntion と procedure の違いは戻り値があるかどうかです。 VBでいえば、Function と Sub みたいなもの。

さっきと同様に、次のソースを登録してみましょう。

create or replace function  test_func1  
    return varchar2
is

begin
    return 'aiueo';
end;
/

 文字列 ’aiueo’を返す関数ですが、そのまま実行したら、エラーになります。

戻り値を確認するには、次のように指定して実行します。

SQL> select test_func1 from dual;

TEST_FUNC1
-------------------------------------------------
aiueo

と、表示されます。dual というのは、ダミーの表です。ほかにも戻り値のチェックの仕方があるかもしれませんが、とりあえずの方法でした。

2001/05/28


003     カーソルを使った初歩的サンプル

-- カーソルのテスト 

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


004    パッケージの基本

パッケージはひとつ以上の関数群をグループ化および、隠蔽化することができる。また、宣言部と本体を別のファイルにすることができるため、メンテナンス性の向上を期待できる。

パッケージは
CREATE PACKAGEC
文とCREATE PACKAGE BODY 文から構成する。

PACKAGECが俗に言うヘッダーとなり、ここに宣言したプロシシャー、ファンクション、変数、カーソルはグローバルなものとなる。

PACKAGE BODY には、グローバルに宣言したプロシシャー、ファンクションの本体、および、ボディー内部でしか参照できないローカルなものを宣言する。

基本形

CREATE OR REPLACE PACKAGE gpacgAAA
IS

FUNCTION Func1(inValue IN VARCHAR2 ) RETURN NUMBER;
FUNCTION Func2(inValue IN VARCHAR2 ) RETURN NUMBER;
    ...
END;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY gpacgAAA
IS

FUNCTION Func1(inValue IN VARCHAR2)RETURN NUMBER
IS

[変数を宣言]

BEGIN

     [処理を記入]

    RETURN .... ;

END Func1;

FUNCTION Func2(inValue IN VARCHAR2 )RETURN NUMBER
IS

[変数を宣言]

BEGIN

     [処理を記入]

    RETURN .... ;

END Func2;


END gpacgAAA;
/
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

 

 


005   トランザクションの範囲

PL/SQLでは、BEGIN TRANの概念がなく、前回コミットした範囲からがトランザクションの範囲となる。

VBから呼ばれた時にもこの考えは適用できる。よってクライアント側の処理とPL/SQLを使用したサーバー側の処理は同一トランザクション内で構築することは、容易に可能である。

 2001/07/22


20080501 PLSQLでローカルファイルにアクセスする

ネットで調べてみると、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;
/

読めてるじゃん


20080616 コメント

1行コメントは --

複数行コメントは  /*  〜 */ である


20080616 変数宣言の場所

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

20080616 プライベート関数

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": 無効な識別子です。

 


20081008 縦横変換を行う ファンクションを作成してみた

 

CREATE TABLE MYPROJ.実験履歴
(
    SEQ                         VARCHAR2(10) NOT NULL,
    版数                           NUMBER(3,0) NOT NULL,
    キー                           VARCHAR2(10) NOT NULL,
    値                             VARCHAR2(10),
    CONSTRAINT 実験履歴PK PRIMARY KEY (SEQ, 版数, キー)
)
/
SEQ 版数 キー
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(
	 項目1 	VARCHAR2(10)
	,項目2 	VARCHAR2(10)
	,項目3 	VARCHAR2(10)
);    
/
CREATE OR REPLACE TYPE MYPROJ.実験TYPE_TABLE
IS TABLE OF 実験TYPE_REC
/
CREATE OR REPLACE FUNCTION MYPROJ.実験縦横変換
(
     IN_SEQ 	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
			実験履歴.SEQ	= IN_SEQ
		AND 実験履歴.版数 		= IN_版数
        ;

	WK_キー  VARCHAR2(10);
    WK_値	 VARCHAR2(10);

BEGIN
	
    -- 生成時に1件出来てしまっているので、空にする
	OUT_TABLE.DELETE;
    
    OPEN CSR_縦横変換;
    
    LOOP
    
	    FETCH CSR_縦横変換 INTO  ;

		--読めた
		CASE WK_キー
        	WHEN '001' THEN OUT_REC.項目1 := WK_値;
        	WHEN '002' THEN OUT_REC.項目2 := WK_値;
        	WHEN '003' THEN OUT_REC.項目3 := WK_値;
        END CASE;
        
	    EXIT WHEN CSR_縦横変換%NOTFOUND;
    END LOOP;
    
    CLOSE CSR_縦横変換;
    
    
	OUT_TABLE.EXTEND;	-- 1行増やす
    OUT_TABLE(1) := OUT_REC; 
    
    
	RETURN OUT_TABLE;

END;
/
SELECT * 
FROM
TABLE(実験縦横変換('0000000001','2'))
項目1 項目2 項目3
1-2-1 1-2-2 1-2-3

リファクタリング その1

上記の例では、CSR_縦横変換 を 明示的に宣言し、OPENやFETCHを 手動で制御している。単純に 回したいだけなら、暗黙カーソルを使えば、ソースはシンプルになる

CREATE OR REPLACE FUNCTION MYPROJ.実験縦横変換
(
     IN_SEQ  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
    
    -- 生成時に1件出来てしまっているので、空にする
    OUT_TABLE.DELETE;
    
    FOR CSR_縦横変換 IN
        (    
            SELECT 
                  実験履歴.キー
                 ,実験履歴.値
            FROM
                 実験履歴
            WHERE
                実験履歴.SEQ = IN_SEQ
            AND 実験履歴.版数   = IN_版数
        )
    LOOP
        CASE CSR_縦横変換.キー
            WHEN '001' THEN OUT_REC.項目1 := CSR_縦横変換.値;
            WHEN '002' THEN OUT_REC.項目2 := CSR_縦横変換.値;
            WHEN '003' THEN OUT_REC.項目3 := CSR_縦横変換.値;
        END CASE;
    END LOOP;
    
    OUT_TABLE.EXTEND;   -- 1行増やす
    OUT_TABLE(1) := OUT_REC; 
    
    
    RETURN OUT_TABLE;

END;
/

OPEN CLOSE は、FOR IN の中で 自動的に行われている。また、FETCHも明示的に行う必要がない。

また、FETCH 文が無いため、INTO文もなく、退避する変数(WK_キー,WK_値)も 不要になる。

値を取り出す時には CSR_縦横変換.値 で可能となる。


リファクタリング その2

CREATE TYPE している 実験TYPE_REC 、実験TYPE_TABLE であるが、
FUNCTION 実験縦横変換 を宣言する時には 既に宣言されている必要があるので、外で CREATE TYPE していた。

PACKAGE を 用いる事により、一つのリソースにまとめる事ができる。

いろいろ、制約による変更が発生した。

  1. PACKAGE の中では、OBJECT型のTYPEが定義できなかった→ RECORD型に変更した。※1
  2. RECORD型 は コンストラクタが不要。宣言だけでよい。※2
  3. RECOTD型から TABLE型への 移入がうまくできず、実行すると、型が無効と言われた。…詳細は調べていない
    ネットのサンプルに従い PIPELINED 方式にする事で、TABLE へ 設定するようにした ※3
CREATE OR REPLACE PACKAGE MYPROJ.実験 IS

	-- TYPEの定義
	TYPE 実験TYPE_REC   --※1
	IS RECORD(
		 項目1 	VARCHAR2(10)
		,項目2 	VARCHAR2(10)
		,項目3 	VARCHAR2(10)
	);    

	TYPE 実験TYPE_TABLE
	IS TABLE OF 実験TYPE_REC ;


	-- FUNCTIONの定義
	FUNCTION 縦横変換
	(
	     IN_SEQ  IN VARCHAR2
	    ,IN_版数    IN NUMBER   
	)
	RETURN 実験TYPE_TABLE PIPELINED;  --※3

END 実験;
/
CREATE OR REPLACE PACKAGE BODY MYPROJ.実験 IS

	FUNCTION 縦横変換
	(
	     IN_SEQ  IN VARCHAR2
	    ,IN_版数    IN NUMBER   
	)
	RETURN
	     実験TYPE_TABLE PIPELINED --※3
	IS
	
	    OUT_REC     実験TYPE_REC ;  --※2
	
	BEGIN
	    
	    FOR CSR_縦横変換 IN
	        (    
	            SELECT 
	                  実験履歴.キー
	                 ,実験履歴.値
	            FROM
	                 実験履歴
	            WHERE
	                実験履歴.SEQ = IN_SEQ
	            AND 実験履歴.版数   = IN_版数
	        )
	    LOOP
	        CASE CSR_縦横変換.キー
	            WHEN '001' THEN OUT_REC.項目1 := CSR_縦横変換.値;
	            WHEN '002' THEN OUT_REC.項目2 := CSR_縦横変換.値;
	            WHEN '003' THEN OUT_REC.項目3 := CSR_縦横変換.値;
	        END CASE;
            
            
	    END LOOP;
		
             PIPE ROW(OUT_REC); 	--※3
	    
	    RETURN ; 		--※3

	END 縦横変換;

END 実験;
/
SELECT * 
FROM
TABLE(実験.縦横変換('0000000001',1))

PIPELINED  についてのメモ。

プロシジャーから大量の行を含む情報を返却する際、普通に実装すると、大量のデータ分のメモリ領域を確保しなければならない。
ここで、行あたりの処理が完了できるならば、処理が完了した行から、行単位に返却すれば メモリ領域の大量に食わなくても良いし、
また、返却レスポンスも行単位に帰ってくるので、向上する。

っていうことは、1行の処理が1秒づつかかっていたら、SELECT を投げた方は、1秒毎に 結果が増えることになるのかな?

        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行くらいやってみた所では、変化なく、一括して出力されたぁ??


 

戻る

inserted by FC2 system