【Oracle】PL/SQL

ファンクション

SQL文の中で実行できる。
パラメータで渡された値の判定、変換処理、値の抽出を行う。

作り方

CREATE OR REPLACE FUNCTION FNC_SAMPLE(
 base IN NUMBER,
 height IN NUMBER
) RETURN NUMBER
-- 宣言部
IS
  area NUMBER;
-- 処理部
BEGIN
  SELECT (base * height / 2)
  INTO area
  FROM DUAL;

  RETURN area;
END;

CREATE OR REPLACE FUNCTION FNC_SAMPLE(
  I_社員コード IN VARCHAR2,
  I_氏名 IN VARCHAR2
) RETURN NUMBER --戻り値の型
-- 宣言部
IS
  O_年齢 NUMBER(10);
  TMP_VAL VARCHAR2(120);
  CURSOR CUR_社員マスタ IS
    SELECT 年齢 FROM 社員マスタ WHERE 社員コード=I_社員コード AND 氏名=I_氏名;
-- 処理部
BEGIN
  OPEN CUR_社員マスタ;
  FETCH CUR_社員マスタ INTO TMP_VAL;
  IF CUR_社員マスタ%FOUND THEN
    O_年齢:=TMP_VAL;
  END IF;
  CLOSE CUR_社員マスタ;

  RETURN O_年齢;

EXCEPTION
  WHEN OTHERS THEN
    IF CUR_社員マスタ%ISOPEN THEN
      CLOSE CUR_社員マスタ;
    END IF;
    O_年齢:=NULL;
    RETURN O_年齢;
END;

カーソルの属性	意味
カーソル名%ISOPEN	カーソルがオープン状態ならばTRUEを返す
カーソル名%FOUND	直前のFETCH文でレコードを取得した場合にTRUEを返す
カーソル名%NOTFOUND	直前のFETCH文でレコードを取得できなかった場合にTRUEを返す
カーソル名%ROWCOUNT	この属性を参照時点での、取得したレコードの件数を返す

使い方

SELECT FNC_SAMPLE('1') FROM DUAL;

削除

DROP FUNCTION FNC_SAMPLE;

プロシージャ

バッチ処理などの一連の処理手続きを実装する

作り方

DECLARE
 rec$  users%ROWTYPE;    --テーブル
 name$ users.name%TYPE;    --項目
 CURSOR users$ IS SELECT name, age FROM users;
BEGIN
 FOR rec$ IN users$ LOOP
  DBMS_OUTPUT.PUT_LINE(rec$.name || 'は' || rec$.age || '歳');
 END LOOP;
END;
/

使い方