今回は仕事中によく使う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;
/
–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;
/
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 $?
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;
/
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’);
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
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
