覚え書き/PL／SQL勉強 - tt備忘録

PL/SQL勉強メモ

• Oracle Database 11g Express Editionインストール

途中で聞かれるパスワードはSYS,SYSTEMユーザに適用される。

デフォルトのセットアップ情報

宛先フォルダ:C:\oraclexe\
Oracleホーム: C:\oraclexe\app\oracle\product\11.2.0\server\
Oracleベース:C:\oraclexe\
'Oracle Databaseリスナー'のポート: 1521
'Oracle Services for Microsoft Transaction Server'のポート: 2030
'Oracle HTTPリスナー'のポート: 8080

• 参考

http://www.atmarkit.co.jp/fdb/ref/ref_oracle.html
http://www.shift-the-oracle.com/sqlplus/

• SQL*Plusのコンソールからデータベースの起動

DOSコマンドプロンプトからSQL*Plus起動

>sqlplus /NOLOG
SQL>CONNECT system/zxcvbnm AS SYSDBA
SQL>STARTUP

systemはユーザ名(最初から登録されている)
zxcvbnmはパスワード(インストールする時に入力しているはず)

• ユーザを追加する

とりあえず新しくユーザを追加して作業をする。

ユーザ作成構文

    CREATE USER my_name
      IDENTIFIED BY "my_password"
      [DEFAULT TABLESPACE my_tablespace]
      [TEMPORARY TABLESPACE my_temp_tablespace]
      [PROFILE my_profile]

OS認証で SYSDBA に接続。NETのサンプル通り、深くは追求しない…

CONNECT / AS SYSDBA

TEST_USER追加

CREATE USER TEST_USER IDENTIFIED BY "TEST_USER"

テスト用なので権限拡大と使用容量を無限にしておく。

GRANT DBA TO TEST_USER;
GRANT UNLIMITED TABLESPACE TO TEST_USER;

• ユーザを確認する

SELECT * FROM all_users;

• 表を作成

CREATE TABLE working
(
  id   NUMBER NOT NULL PRIMARY KEY,
  dt   DATE,
  name VARCHAR2(24)
);

CREATE TABLE log
(
--  id        NUMBER NOT NULL IDENTITY,
  logtime   TIMESTAMP NOT NULL,
  msg       VARCHAR2(48)
);

• 表を削除する

DROP TABLE working;

• 表定義の確認

DESCRIBE working

• レコードを追加

INSERT INTO working
(id, dt, name)VALUES(1, '2000/01/01', 'AAA');

INSERT INTO log
(logtime, msg)VALUES(CURRENT_TIMESTAMP, 'AAA');

• ループしてみる

DECLARE
  cnt NUMBER;
BEGIN
  cnt := 0;
  LOOP
    EXIT WHEN cnt >= 10;
    cnt := cnt + 1;
  END LOOP;
END;

• 1レコードを抽出して変数に読み込む

※DBMS_OUTPUTパッケージを使用して変数の内容を表示している。
　デフォルトでは表示がOFFになっているため以下でONにする必要がある。

SET SERVEROUTPUT ON

DECLARE
  row  working%ROWTYPE;
  row0 VARCHAR2(24);
  row1 VARCHAR2(24);
  row2 VARCHAR2(24);
BEGIN
  SELECT * INTO row FROM working WHERE id=1;
  SELECT * INTO row0,row1,row2 FROM working WHERE id=1;

  DBMS_OUTPUT.PUT_LINE(row0 || ', ' || row1 || ', ' || row2);
  DBMS_OUTPUT.PUT_LINE(row.id || ', ' || row.dt || ', ' || row.name);
END;

• カーソルを使って複数レコードを抽出する

DECLARE
  CURSOR cur IS SELECT * FROM working;
  row cur%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO row;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(row.id || ', ' || row.dt || ', ' || row.name);
  END LOOP;
  CLOSE cur;
END;

• ファイルに出力してみる

オラクルが提供するPL/SQLパッケージ「UTL_FILE」を使用してファイル出力を行う。

--TEST_USERに実行権限を与える
CONNECT / AS SYSDBA
GRANT EXECUTE ON UTL_FILE TO TEST_USER;

--ディレクトリオブジェクト一覧を確認
SELECT * FROM ALL_DIRECTORIES;

--ディレクトリオブジェクト作成
CREATE DIRECTORY temp AS 'C:\temp\';

--ディレクトリオブジェクトのアクセス権限を付与
GRANT READ,WRITE ON DIRECTORY temp TO TEST_USER;

--ファイル出力
DECLARE
  fh   UTL_FILE.FILE_TYPE;
BEGIN
  --ファイルオープン ※ディレクトリオブジェクト名は大文字じゃないとエラーになる
  fh := UTL_FILE.FOPEN('TEMP', 'test2.txt','w');

  UTL_FILE.PUT_LINE(fh,'testtest');
  UTL_FILE.FCLOSE(fh);
END;

---

2015-02-27 17:58:08 (Fri);