【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

ストアドプロシージャ

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   

まとめ

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

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください