【PL/SQL】HelloWorldとか

次の現場で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

ストアドプロシージャ

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

ストアドファンクション

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

ストアドパッケージ

パッケージはパッケージ仕様とパッケージ本体に分けるのが定石のようです。

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

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 

まとめ

いろいろ種類があってムズいな！！