次の現場でPL/SQLが必要そうなので勉強中。
とりあえずいろいろ種類があるっぽい。
・無名ブロック
・ストアドプロシージャ – PROCEDURE
・ストアドファンクション – FUNCTION
・ストアドパッケージ – PACKAGE
テーブルとか
過去に作成した適当なテーブルとデータです。。。。
PL/SQL標準出力
とりあえず標準出力を許可。
-- DBMS_OUTPUT 出力ON
SET SERVEROUTPUT ON;
参考
SET SERVEROUTPUTオラクル・Oracle SQL*Plus リファレンス
無名ブロック その1
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/実行結果
PL/SQLプロシージャが正常に完了しました。 Hello World
無名ブロック その2
DECLARE
-- 変数設定
hoge VARCHAR2(10) := 'test';
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world:' || hoge);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error!!');
END;
/実行結果
PL/SQLプロシージャが正常に完了しました。 Hello World:test
ストアドプロシージャ
get_name.sql
CREATE OR REPLACE PROCEDURE GET_NAME (
v_id IN NUMBER,
v_name OUT VARCHAR2
) AS
BEGIN
SELECT NAME INTO v_name FROM TRANSACTION1 WHERE ID = v_id;
END GET_NAME;
/無名ブロック から呼び出し
DECLARE
v_name varchar2(40);
BEGIN
GET_NAME(v_id => 2, v_name => v_name);
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/PL/SQLプロシージャが正常に完了しました。 BBB
ストアドファンクション
get_name2.sql
CREATE OR REPLACE FUNCTION GET_NAME2(v_id IN NUMBER)
RETURN VARCHAR2 AS
v_name VARCHAR2(40);
BEGIN
SELECT NAME INTO v_name FROM TRANSACTION1 WHERE ID = v_id;
RETURN v_name;
END GET_NAME2;
/SQL から呼び出し
SELECT GET_NAME2(3) FROM DUAL;GET_NAME2(3) -------------------------------------------------------------------------------- CCC
無名ブロック から呼び出し
DECLARE
v_name VARCHAR2(40);
BEGIN
v_name := GET_NAME2(v_id => 3);
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/PL/SQLプロシージャが正常に完了しました。 CCC
IF文
DECLARE
v_id NUMBER;
BEGIN
v_id := 10;
IF v_id BETWEEN 1 AND 5 THEN
DBMS_OUTPUT.PUT_LINE('1～5');
ELSIF v_id BETWEEN 6 AND 9 THEN
DBMS_OUTPUT.PUT_LINE('6～9');
ELSE
DBMS_OUTPUT.PUT_LINE('ELSE：' || TO_CHAR(v_id));
END IF;
END;
/実行結果
PL/SQLプロシージャが正常に完了しました。 ELSE：10
%TYPE と %ROWTYPE
DECLARE
v_name TRANSACTION1.NAME%TYPE;
v_transaction1 TRANSACTION1%ROWTYPE;
BEGIN
SELECT NAME INTO v_name FROM TRANSACTION1 WHERE ID = 1;
SELECT * INTO v_transaction1 FROM TRANSACTION1 WHERE ID = 2;
DBMS_OUTPUT.PUT_LINE(v_name);
DBMS_OUTPUT.PUT_LINE(v_transaction1.KBN);
END;
/実行結果
PL/SQLプロシージャが正常に完了しました。 AAA KBN001
LOOP
BEGIN
FOR v_count IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(v_count);
END LOOP;
END;
/実行結果
PL/SQLプロシージャが正常に完了しました。 1 2 3 4 5 6 7 8 9 10
ストアドパッケージ
パッケージはパッケージ仕様とパッケージ本体に分けるのが定石のようです。
PKG_TEST.sql
CREATE OR REPLACE PACKAGE PKG_TEST
AS
FUNCTION TEST(p_number NUMBER)
RETURN NUMBER;
FUNCTION TEST(p_char NVARCHAR2)
RETURN NVARCHAR2;
FUNCTION TEST(p_date DATE)
RETURN DATE;
END;
/ PKG_TEST_BODY.sql
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
FUNCTION TEST(p_number in NUMBER)
RETURN NUMBER
AS
BEGIN
RETURN p_number + 1; -- 加算
END;
FUNCTION TEST(p_char in NVARCHAR2)
RETURN NVARCHAR2
AS
BEGIN
RETURN p_char || ' - TEST'; -- 結合
END;
FUNCTION TEST(p_date in DATE)
RETURN DATE
AS
BEGIN
RETURN p_date + 1; -- 加算
END;
END;
/実行結果
-- SQLから実行
SELECT PKG_TEST.TEST(100) FROM DUAL;
SELECT PKG_TEST.TEST('oqiita') FROM DUAL;
SELECT PKG_TEST.TEST(SYSDATE) FROM DUAL;
PKG_TEST.TEST(100)
---------------------------------------
101
PKG_TEST.TEST('OQIITA')
--------------------------------------------------------------------------------
oqiita - TEST
PKG_TEST.TEST(SYSDATE)
----------------------
2018-09-06 14:26:32
まとめ
いろいろ種類があってムズいな！！
