PL/SQLの使用例
以前にPL/SQLとはなんぞ？ということについてブログで書いてみたけど、詳しい実装については触れていなかったので今回簡単な実装例を提示しておきます＼(^o^)／
↓↓↓参考までに前回のPL/SQLの概要についての記事です。
Oracle PL/SQL 無名ブロックとは？ストアドプロシージャとは？
ストアド・プロシージャ
ストアド・プロシージャの定義例
CREATE OR REPLACE PROCEDURE --<プロシージャ名> -- ここにプロシージャ名を定義します。 TEST_PROCEDURE IS --<宣言部> -- 変数の宣言等を行う。 var1 VARCHAR2(10) := 'HelloWorld'; var2 VARCHAR2(100) := '(/・ω・)/PROCEDURE'; BEGIN --<実行部> -- 実行したいSQLやプログラムを記述する。 DBMS_OUTPUT.PUT_LINE( var1 || var2 ); EXCEPTION WHEN OTHERS THEN NULL; END TEST_PROCEDURE; /
上記の定義をSQL*Plus等で発行するとストアド・プロシージャの定義を作成することができます。
ちなみに「CREATE OR REPLACE」のOR REPLACEは、今定義してあるものがあれば上書きするよって意味なので上書きしたくないときは発行しないようしてください！
SQL> CREATE OR REPLACE PROCEDURE 2 --<プロシージャ名> 3 -- ここにプロシージャ名を定義します。 4 TEST_PROCEDURE 5 IS 6 --<宣言部> 7 -- 変数の宣言等を行う。 8 var1 VARCHAR2(10) := 'HelloWorld'; 9 var2 VARCHAR2(100) := '(/・ω・)/PROCEDURE'; 10 BEGIN 11 --<実行部> 12 -- 実行したいSQLやプログラムを記述する。 13 DBMS_OUTPUT.PUT_LINE( var1 || var2 ); 14 EXCEPTION 15 WHEN OTHERS THEN 16 NULL; 17 END TEST_PROCEDURE; 18 / プロシージャが作成されました。
ストアド・プロシージャの実行例
ストアド・プロシージャは「EXECUTE」コマンドから実行することができます。
※「SET SERVEROUTPUT ON」は、結果を表示するためのものなので、実行に必須ではありません。
SET SERVEROUTPUT ON EXECUTE TEST_PROCEDURE;
SQL> SET SERVEROUTPUT ON SQL> EXECUTE TEST_PROCEDURE; HelloWorld(/・ω・)/PROCEDURE PL/SQLプロシージャが正常に完了しました。
ストアド・ファンクション
ストアド・ファンクションの定義例
CREATE OR REPLACE FUNCTION --<ファンクション名> -- ここにファンクション名を定義します。 TEST_FUNCTION --<データ型> -- 戻り値のデータ型を宣言する。 RETURN VARCHAR2 IS --<宣言部> -- 変数の宣言等を行う。 var1 VARCHAR2(20) := 'HelloWorld'; var2 VARCHAR2(20) := '(/・ω・)/FUNCTION'; BEGIN --<実行部> -- 実行したいSQLやプログラムを記述する。 DBMS_OUTPUT.PUT_LINE( var1 || var2 ); RETURN var1 || var2; EXCEPTION WHEN OTHERS THEN NULL; END TEST_FUNCTION; /
上記の定義をSQL*Plus等で発行するとストアド・ファンクションの定義を作成することができます。
ちなみにストアド・プロシージャと一緒で「CREATE OR REPLACE」のOR REPLACEは、今定義してあるものがあれば上書きするよって意味なので上書きしたくないときは発行しないようしてください！前の定義が消えちゃいます(´・ω・｀)
SQL> CREATE OR REPLACE FUNCTION 2 --<ファンクション名> 3 -- ここにファンクション名を定義します。 4 TEST_FUNCTION 5 --<データ型> 6 -- 戻り値のデータ型を宣言する。 7 RETURN 8 VARCHAR2 9 IS 10 --<宣言部> 11 -- 変数の宣言等を行う。 12 var1 VARCHAR2(20) := 'HelloWorld'; 13 var2 VARCHAR2(20) := '(/・ω・)/FUNCTION'; 14 BEGIN 15 --<実行部> 16 -- 実行したいSQLやプログラムを記述する。 17 DBMS_OUTPUT.PUT_LINE( var1 || var2 ); 18 RETURN var1 || var2; 19 EXCEPTION 20 WHEN OTHERS THEN 21 NULL; 22 END TEST_FUNCTION; 23 / ファンクションが作成されました。
ストアド・ファンクションの実行例 「EXECUTE」コマンドでの実行の場合
ストアド・ファンクションは「EXECUTE」コマンドから実行することができます。
ストアド・ファンクションは値を返すための変数を用意します＼(^o^)／
※「SET SERVEROUTPUT ON」は、結果を表示するためのものなので、実行に必須ではありません。
SET SERVEROUTPUT ON VARIABLE vRTN VARCHAR2(100) EXECUTE :vRTN := TEST_FUNCTION;
SQL> SET SERVEROUTPUT ON SQL> VARIABLE vRTN VARCHAR2(100) SQL> EXECUTE :vRTN := TEST_FUNCTION; HelloWorld(/・ω・)/FUNCTION PL/SQLプロシージャが正常に完了しました。
ストアド・ファンクションの実行例 SQLの中で実行する場合
SELECT TEST_FUNCTION() FROM DUAL;
こんな風にSQLの中で実行することもできます。今回は引数なしのファンクションを作成しましたが、ファンクション名()のカッコの中身は引数を与えます。＼(^o^)／
SQL> SELECT TEST_FUNCTION() FROM DUAL; TEST_FUNCTION() --------------------------------------------- HelloWorld(/・ω・)/FUNCTION
ストアド・パッケージの実行例
ストアド・パッケージ仕様部の定義例
CREATE OR REPLACE PACKAGE
--<パッケージ名>
-- パッケージ名を定義します。
TEST_PACKAGE
IS
--<宣言部>
-- 変数の宣言等を行う。
var1 VARCHAR2(20) := 'HelloWorld';
PROCEDURE test_package_pro;
END TEST_PACKAGE;
/
SQL> CREATE OR REPLACE PACKAGE
2 --<パッケージ名>
3 -- パッケージ名を定義します。
4 TEST_PACKAGE
5 IS
6 --<宣言部>
7 -- 変数の宣言等を行う。
8 var1 VARCHAR2(20) := 'HelloWorld';
9 PROCEDURE test_package_pro;
10 END TEST_PACKAGE;
11 /
パッケージが作成されました。
ストアド・パッケージ本体の定義例
ストアド・パッケージ本体の定義例です。
現場ではストアド・パッケージ本体とかは呼ばずに「パッケージボディ」ということが多いと思います。
間違いやすいのですが、パッケージ名は仕様部とパッケージ本体を同じにする必要があるので気をつけてください。
CREATE OR REPLACE PACKAGE BODY
--<パッケージ名>
-- パッケージ名を定義します。
TEST_PACKAGE
IS
--<宣言部>
-- 変数の宣言等を行う。
PROCEDURE test_package_pro
IS
var2 VARCHAR2(20) := '(/・ω・)/PACKAGE';
BEGIN
DBMS_OUTPUT.PUT_LINE( var1 || ' ' || var2);
END test_package_pro;
END TEST_PACKAGE;
/
SQL> CREATE OR REPLACE PACKAGE BODY 2 --<パッケージ名> 3 -- パッケージ名を定義します。 4 TEST_PACKAGE 5 IS 6 --<宣言部> 7 -- 変数の宣言等を行う。 8 PROCEDURE test_package_pro 9 IS 10 var2 VARCHAR2(20) := '(/・ω・)/PACKAGE'; 11 BEGIN 12 DBMS_OUTPUT.PUT_LINE( var1 || ' ' || var2); 13 END test_package_pro; 14 END TEST_PACKAGE; 15 / パッケージ本体が作成されました。
上記の定義をSQL*Plus等で発行するとストアド・パッケージの定義を作成することができます。
ストアド・パッケージ名の実行例 「EXECUTE」コマンドでの実行の場合
ストアド・パッケージは「EXECUTE」コマンドから実行することができます。
「<パッケージ名>.<オブジェクト名>」のように呼び出す事ができます。
SQL> EXECUTE TEST_PACKAGE.test_package_pro; HelloWorld (/・ω・)/PACKAGE PL/SQLプロシージャが正常に完了しました。
ちゃんと動作していますね＼(^o^)／
無名ブロック
無名ブロックの定義例
SET SERVEROUTPUT ON --無名ブロックの定義例 DECLARE --<宣言部> -- 変数の宣言等を行う。 var1 VARCHAR2(10) := 'Hello'; var2 VARCHAR2(10) := 'World'; BEGIN --<実行部> -- 実行したいSQLやプログラムを記述する。 DBMS_OUTPUT.PUT_LINE( var1 || var2 ); EXCEPTION --<例外部> -- 例外処理の動作を記述する。 WHEN OTHERS THEN NULL; END; /
無名ブロックの実行例
無名ブロックの実行のやり方は、無名ブロック全体をそのまま実行します。
※「SET SERVEROUTPUT ON」は、結果を表示するためのものなので、実行に必須ではありません。
SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 --<宣言部> 3 -- 変数の宣言等を行う。 4 var1 VARCHAR2(10) := 'Hello'; 5 var2 VARCHAR2(10) := 'World'; 6 BEGIN 7 --<実行部> 8 -- 実行したいSQLやプログラムを記述する。 9 DBMS_OUTPUT.PUT_LINE( var1 || var2 ); 10 EXCEPTION 11 --<例外部> 12 -- 例外処理の動作を記述する。 13 WHEN OTHERS THEN 14 NULL; 15 END; 16 / HelloWorld PL/SQLプロシージャが正常に完了しました。
コメント