Oracle DatabaseのPL/SQLを使って、FizzBuzzをやってみました
設定
ダウンロード・インストール
まず、Oracle Databaseを以下のサイトからダウンロードして、画面の案内にそってインストールします。
接続・起動
SYSDBA権限で接続します。
sqlplus / as sysdba
DBに接続した後、「アイドル・インスタンスに接続しました。」と表示された場合は、DBに接続した状態（SQL>が表示された状態）で次のコマンドを実行し、DBを起動させます。
STARTUP
ちなみに切断はSHUTDOWNです。
ユーザ作成
以下のコマンドを実行し、ユーザを作成します。
CREATE USER <user> IDENTIFIED BY <password> DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
但し、いきなり実行すると多分
行1でエラーが発生しました。:
ORA-65096: 共通ユーザーまたはロール名は、接頭辞C##で始める必要があります
ヘルプ: https://docs.oracle.com/error-help/db/ora-65096/
が表示されると思いますので、その場合はCDB（コンテナデータベース）からPDB（プラガブルデータベース）に切り替える必要があります。
以下のSQLコマンドを実行し、
SELECT name FROM v$pdbs WHERE open_mode = 'READ WRITE';
その結果を、以下の<pdb>の部分に代入して実行します。
ALTER SESSION SET container = <pdb>;
こうして改めてCREATE USERした後、このユーザに権限を付与します。
GRANT CREATE SESSION TO <user>;
GRANT CREATE TABLE TO <user>;
GRANT CREATE SEQUENCE TO <user>;
新規ユーザでログイン
先程作成したユーザでログインします。
sqlplus <user>/<password>@<pdb>
が、多分
ERROR:
ORA-12154: データベースに接続できません。別名<pdb>
(/app/･･･/product/26ai/dbhomeFree/network/admin/tnsnames.ora内)が見つかりません。
ヘルプ: https://docs.oracle.com/error-help/db/ora-12154/
等、表示されると思いますので、上記ディレクトリ内にあるtnsnames.oraファイルの、以下のFREE（LISTENER_FREEを除く）2ヶ所を先程のPDB名に置き換えます。
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhose)(PORT = 1521))
PL/SQL本体
漸く、本題である「FizzBuzzをPL/SQLでやってみる」に進むことができます。
単に表示して終わり、でもいいのですが、今回は
- テーブルを作成し、データを挿入する
- カーソル操作する
ようにしたいと思います。
テーブル作成
OracleDBで主キーを自動採番（AUTO INCREMENT）したい場合、例えば以下の様に書きます。
CREATE TABLE fizzbuzz(
id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
name VARCHAR2(8)
);
データ挿入
先程作成したfizzbuzzテーブルに、整数1,2,...,100に対して、
- 整数が3でも5でも割り切れる場合は「FizzBuzz」
- 整数が3で割り切れる場合は「Fizz」
- 整数が5で割り切れる場合は「Buzz」
- 整数が3でも5でも割り切れない場合はその数字
をテーブルに格納していきます。
また、データを挿入、更新、削除する前に、テーブルにロックをかけておき、成功したらコミット、失敗したらエラーメッセージを出力してロールバックすることにします（コミットまたはロールバックした時にテーブルのロックは解放されます）。標準出力にエラーメッセージを出力するためにSET SERVEROUTPUT ON;を実行します。
SET SERVEROUTPUT ON;
LOCK TABLE fizzbuzz IN EXCLUSIVE MODE;
BEGIN
FOR i IN 1..100 LOOP
IF MOD(i, 3) = 0 AND MOD(i, 5) = 0 THEN
INSERT INTO fizzbuzz(name) VALUES ('FizzBuzz');
ELSIF MOD(i, 3) = 0 THEN
INSERT INTO fizzbuzz(name) VALUES ('Fizz');
ELSIF MOD(i, 5) = 0 THEN
INSERT INTO fizzbuzz(name) VALUES ('Buzz');
ELSE
INSERT INTO fizzbuzz(name) VALUES (TO_CHAR(i));
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
/
正しく挿入できたか、SELECT文で確認します。
SELECT * FROM fizzbuzz ORDER BY id;
もし、画面にされるものが多いと感じた時は
CLEAR SCREEN;
で画面の出力を消すことができます。
暗黙カーソル
暗黙カーソルを使って、fizzbuzzテーブルの中身を1レコードずつ表示していきます。
SELECT文の結果が0レコードの場合はNO_DATA_FOUND例外が、2レコード以上の場合はTOO_MANY_ROWS例外が発生しますので、例外処理部EXCEPTIONにて、その旨を表示させます。
DECLARE
s fizzbuzz.name%TYPE;
BEGIN
FOR i IN 1..100 LOOP
BEGIN
SELECT name INTO s FROM fizzbuzz WHERE id=i;
DBMS_OUTPUT.PUT_LINE('| ' || LPAD(i, 3) || ' | ' || RPAD(s, 8) || ' |');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(i || ': No data found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(i || ': Too many rows.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || SQLCODE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END;
/
明示カーソル
明示カーソルを使って、fizzbuzzテーブルの中身を1レコードずつ表示していきます。
宣言部DECLAREにて
- カーソルを
CURSOR <カーソル名> IS <SELECT文>; - フェッチしたレコードを格納する変数を
<変数名> <カーソル名>%ROWTYPE;
で宣言します。そして、実行部BEGIN～ENDで
- （カーソルがオープンされていなければ）カーソルを開く
-
SELECT文の結果を一行ずつフェッチする- フェッチ結果が無ければ、カーソルを閉じて終了
- フェッチした結果を出力する
ことにします。
DECLARE
CURSOR c IS SELECT * FROM fizzbuzz ORDER BY id;
r c%ROWTYPE;
BEGIN
IF NOT c%ISOPEN THEN
OPEN c;
END IF;
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('| ' || LPAD(r.id, 3) || ' | ' || RPAD(r.name, 8) || ' |');
END LOOP;
CLOSE c;
END;
/
上記の例では
- カーソルを開く
- 一行ずつフェッチする
- フェッチ結果が無ければループ脱出
- カーソルを閉じる
ように書きましたが、FOR <変数名> IN <カーソル名> LOOPを使えば、以下の様に簡潔に書くことができます（カーソルのみの宣言で、OPENもFETCHもCLOSEも書きません）。
DECLARE
CURSOR c IS SELECT * FROM fizzbuzz ORDER BY id;
BEGIN
FOR r IN c LOOP
DBMS_OUTPUT.PUT_LINE('| ' || LPAD(r.id, 3) || ' | ' || RPAD(r.name, 8) || ' |');
END LOOP;
END;
/
後片付け
最後、
- 標準出力をOFF
-
fizzbuzzテーブルを削除
しておきます。
SET SERVEROUTPUT OFF;
DROP TABLE fizzbuzz;