PL/SQLプロシージャとファンクションの書き方および実行方法のまとめ

plsql実行方法 データベース

今回は仕事中によく使うPL/SQLの書き方および実行方法を紹介します。
今後、PLSQLを作成するとき、参照いただければ、幸いです。

PL/SQLプロシージャの書き方

プロシージャでTYPEの配列とカーソルの使い方を参照できます。

CREATE OR REPLACE PROCEDURE testPlSql(testPlsql VARCHAR2) IS
  –TYPEの配列
  type T_TEST is record(
      test1 VARCHAR2(4)
   ,test2 VARCHAR2(8)
  );
  type L_TEST is table of T_TEST index by binary_integer;
  v_test L_TEST;
 
  –カーソルの定義
  CURSOR cEmp IS
     SELECT * FROM EMP;
BEGIN
    FOR i IN 1.. 100 LOOP
   v_test(i).test1 := ‘a’;
   v_test(i).test2 := ‘b’;
 END LOOP;
  FOR vEmp IN cEmp LOOP
    UPDATE emp SET empno=vEmp.empno || ‘1’ where empno=vEmp.empno;
  END LOOP;
  COMMIT;
END;
/

PL/SQLファンクションの書き方

CREATE OR REPLACE FUNCTION testPlSql2(testPlsql VARCHAR2) RETURN number
IS
  v_test number;
BEGIN
  select DBMS_RANDOM.VALUE(1,100) into v_test from dual;
  RETURN v_test;
END;
/

PL/SQLの実行方法

ブロックで実行

シェルで変数を定義し、PL/SQLブロック文をSQL*PLUSで実行する。
※PL/SQLブロックをファイルに書いて、ファイルで実行してもいいです。

シェルで変数を定義し、実行する

使用例:vi TestPlSql.sh

シェルの内容は以下のように書けば、実行できます。

#!/bin/bash
plsql=”SET ECHO OFF\n”
plsql=”${plsql}SET SERVEROUTPUT ON\n”
plsql=”${plsql}SET VERIFY OFF\n”
plsql=”${plsql}WHENEVER OSERROR EXIT 1 ROLLBACK\n”
plsql=”${plsql}WHENEVER SQLERROR EXIT 2 ROLLBACK\n”
plsql=”${plsql}DECLARE\n”
plsql=”${plsql}  msg VARCHAR2(20);\n”
plsql=”${plsql}BEGIN\n”
plsql=”${plsql}  UPDATE テーブル名 SET カラム名=”;\n”
plsql=”${plsql}  COMMIT;\n”
plsql=”${plsql}EXCEPTION\n”
plsql=”${plsql}  WHEN OTHERS THEN\n”
plsql=”${plsql}    DBMS_OUTPUT.PUT_LINE(‘error:’ || SQLERRM);\n”
plsql=”${plsql}END;\n”
plsql=”${plsql}/\n”
echo -e “${plsql}” | sqlplus -s ユーザ名/パスワード@接続文字列
echo $?

ファイルに書いて、ファイルを実行する

vi plsql.sql

SET ECHO OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
WHENEVER OSERROR EXIT 1 ROLLBACK
WHENEVER SQLERROR EXIT 2 ROLLBACK
DECLARE
  msg VARCHAR2(20);
BEGIN
  UPDATE テーブル名 SET カラム名=”;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(‘error:’ || SQLERRM);
END;
/

sqlplus -s ユーザー/パスワード@接続文字列 @plsql.sql

プロシージャを実行する

定義済みのプロシージャを実行するにはCALLまたはEXECUTE文を実行する。

SQL>call testPlSql(‘9’);
SQL>execute testPlSql(‘9’);

ファンクションを実行する

ストアドプロシージャやストアドファンクションを実行するにはCALLまたはEXECUTE文を実行する。
CALLでは戻り値を受け取るため、INTOを使います。

CALLで実行する
SQL> variable vNum number;
SQL> call testPlSql2(‘1’) into :vNum;
SQL> print vNum
EXECUTE文で実行する
SQL> variable vNum number;
SQL> execute :vNum := testPlSql2(‘1’);
SQL> print vNum

コメント

タイトルとURLをコピーしました