frog.raindrop.jp.knowledge

必要があったので、MySQL の DELETE 文で、複合テーブル構文ってのを調べた。例えばこんなん。

DELETE dept, emp
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
WHERE dept.deptno = 20;

例は Oracle のサンプルデータベース SCOTT のものだが、何らかの理由で SCOTT さんの会社から RESERCH 部がなくなって、その部署の従業員も一斉解雇になったらしい。SCOTT さんもクビで、愛猫 TIGER 君とともに路頭に迷うことに。それをこの DELETE 文一発でデータベースに反映することができる。

あるいは

DELETE sub
FROM emp boss LEFT JOIN emp sub
ON boss.empno = sub.mgr
WHERE boss.empno IN (7698, 7902)

何があったのかはわからないが、BLAKE 氏と FORD 氏の部下を全員削除する。この例は DELETE 句では FROM 句で指定した別名が使用でき、FROM 句で指定した表のうち一部の表のみを削除できることを示している。条件が複雑な場合に便利。他の RDBMS にもこういう構文あったっけな ?

Oracle の SCOTT データベースが分かんない人のために、EMP 表と DEPT 表のみ MySQL 用の DDL をのっけておきます。

MySQL で、別々の型同士の比較の際には REAL に変換される。
MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.1.2 式評価でのタイプ変換

NCHARやNVARCHAR2の列と文字列リテラルを返す列をUNIONするようなばあい、そのままではエラーになります。

SQL> DESC Table1;
名前             NULL?    型
---------------- -------- ---------------------------
COLUMN1                   NCHAR(10)
COLUMN2                   NVARCHAR2(30)

SQL> SELECT COLUMN1, COLUMN2
  2  FROM Table1
  3  UNION
  4  SELECT 'あああああ' AS COLUMN1,
  5  'いいいいい' AS COLUMN2
  6  FROM DUAL;
SELECT COLUMN1, COLUMN2
       *
行1でエラーが発生しました。:
ORA-12704: character set mismatch

文字列リテラルは、前に'N'をつけてやると、UNICODEとして記述できますので、同じケースなら以下のようにすると、うまくいきます。

SQL> SELECT COLUMN1, COLUMN2
  2  FROM Table1
  3  UNION
  4  SELECT N'あああああ' AS COLUMN1,
  5  N'いいいいい' AS COLUMN2
  6  FROM DUAL;

COLUMN1    COLUMN2
---------- ----------
えええええ くくくくく
あああああ いいいいい

EXTRACT という関数がある。Oracle や PostgreSQL に実装されているが、Oracleでの挙動が少々わかりにくい。

Oracle のSQLリファレンスによると

注意: 抽出を実行するフィールドは、datetime_value_expr または interval_value_expr フィールドである必要があります。たとえば、DATE 値からは、YEAR、MONTH およびDAY のみを抽出できます。同様に、TIMESTAMP WITH TIME ZONE データ型からは、TIMEZONE_HOUR および TIMEZONE_MINUTE のみを抽出できます。

という注意書きが付いているが意味が良くわかんないのである。確かに、SELECT EXTRACT(SECOND FROM SYSDATE) FROM DUAL を実行しようとすると

ORA-30076: 抽出ソースの抽出フィールドが無効です

と叱られる。じゃあいったい SECOND ってキーワードは何なの? である。

このあいだ、使おうと思ったときに思い出せなかったので。

Oracle で、SQL を書く時に、各行について、「ある列が5以上かどうか」を真偽値でとりたい場合がある。こういうのは、DECODE 関数と LEAST 関数、GREATEST 関数なんかを駆使すると可能。たとえば、EMP表のSALが1000未満の人数、1000以上3000未満の人数、3000以上5000未満の人数、5000の人数を求めたいような場合を考えてみる。(もちろん、WHERE句で条件を指定して、COUNTした結果をUNIONすればいいのだけれど。)

SELECT
	SUM( DECODE( LEAST( SAL, 1000 ), 1000, 0, 1 ) )	AS "1000未満", 
	SUM( DECODE( LEAST( SAL, 1000 ), 1000, 
	     DECODE( LEAST( SAL, 3000 ), 3000, 0, 1 ) )
	                                              )	AS "1000以上3000未満", 
	SUM( DECODE( LEAST( SAL, 3000 ), 3000, 
	     DECODE( LEAST( SAL, 5000 ), 5000, 0, 1 ) )
	                                              )	AS "3000以上5000未満", 
	SUM( DECODE( LEAST( SAL, 5000 ), 5000, 1, 0)  )	AS "5000以上" 
FROM EMP
/

ODBC を使ったサンプルは、巷にごろごろしてるのですが、SQLAllocEnv といった、推奨されない古い関数を使用したものが多いようです。新しい関数を使おうとすると、サンプルが少ないうえに、ドキュメントが英語で苦労しました。共通関数化したものをアップしておきます。使用例はそのうち追記します。

追記(2004/08/17)。トランザクションを明示的にコミットまたはロールバックするには、データソース接続後に自動コミットをオフにする必要があります。SDKドキュメントには、接続前でもいいと書かれていますが、後でないと期待した動きにならないようです。サンプルは未修正です。

追記(2004/08/18)。データソースに接続した後、自動コミットをオフにする処理を追加しました。

oo4o Class Library をインクルードした C++ のアプリをテストする環境を構築しようとしたのですが、テスト環境はHDが4.3GB(いまどき！) で、空き容量が600MB、Oracle9i Client (9.2.0.1.0) の「管理者」は798MBもあるため、すべて入れるのは無理な感じでした。以前「ランタイム(193MB)」でインストールして、oo4o が使用できないのは確認済みですが、管理者インストールは見るからに不要なものがいっぱい含まれていますし、多分、「ランタイム」にちょろっと追加すれば動きそうな気はします。そこで、カスタムでインストールできないかと思い、oo4o を使用するには最低限何が必要かということをググってみました。

ビンゴ！は、個人サイトですが、こちら。自分用メモということなので、以下に引用しておきます。

Orecle9i Clientランタイムだけだと接続できない？

CD Packに入っていた「Oracle9i Client」のCDを利用し、インストールタイプはランタイムを選択しました。 インストールも無事終わり、アプリケーションから接続を試みると「ORACLM32.dllが見つからない」のエラーになってしまった。
今度は、インストール時にカスタムを選択し、「Oracle Windows Interfaces 9.2.0.1.0」を追加インストールしたところアプリケーションからの接続ができました。 ODBC経由の接続も大丈夫です。
一見、ランタイムというとoo4oとかODBC関係もインストールされるものと思いましたが、上記のように、Windows専用のインターフェイスが必要ということでした。

とりあえず、インストールはできました・・・が、この空き容量でテスト、、、ですな、、、、、。

Oracle の表と列にコメントをつけることができます

SQL> comment on table MY_TABLE is '私のテーブル';

コメントが作成されました。

SQL> comment on column MY_TABLE.MY_COLUMN is '私のカラム';

コメントが作成されました。

こうして付けたコメントは USER_TAB_COMMENTS と USER_COL_COMMENTS というデータディクショナリで参照することができます。こんな感じのテーブルになってます。

SQL> desc USER_TAB_COMMENTS
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)
 COMMENTS                                           VARCHAR2(4000)

SQL> desc USER_COL_COMMENTS
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)

MSDEのデータを手軽にExport/ImportするWSHスクリプトです。'.js'拡張子をつけて保存し、ダブルクリックするかコンソールからCSCRIPT Export.jsなどとして実行します。わざわざ実行速度の遅いJScriptで作っている理由は、単に try{...} catch( e ) {...} でトランザクション処理がしたかったというだけのことです 笑。

MSDE用になっているのは接続文字列だけなので、その部分のみ変更すればOracleやJETなど、ADOで接続可能なDBであれば使用可能だと思います。(でもWSHが利用可能な環境でしか実行できません。)

Oracleで、テーブルスペースを指定して、インデックス・主キーを作成する方法です。

もう、8になってこの設定はいらなくなったんだけど、この前たまたま7のインストールされたマシンを使うことがあって困って調べたので、一応書いておくことにします。

Object Browserはあると何かと便利なので、よく使ってるし、この設定自体はものすごく何度もやってるはずなんだけどね。覚えるほどのことでもないし。

「BDE設定」をスタートメニューから起動し、ツリーの「環境設定」ってタブを選択。「 Drivers > Native > Oracle 」のDLL32とVENDOR INITを以下に設定します。

データベース	DLL32	VENDOR INIT
Ver 7.3	SQLORA32.DLL	ORA73.DLL
Ver 8.03	SQLORA32.DLL	ORA803.DLL
Ver 8.04以降	SQLORA8.DLL	OCI.DLL

UDLファイルを作成して、Microsoft OLE DB Provider for Oracleを使用してOracleに接続するテストを行い、成功することを確認したのですが･･･ASPより接続すると以下のエラー

-2147467259(Microsoft OLE DB Provider for Oracle)
Oracle クライアントとネットワーク コンポーネントが見つかりません。これらのコンポーネントは Oracle Corporation から提供され、Oracle Version 7.3.3 (またはそれ以降) のクライアント ソフトウェアの一部としてインストールされます。
プロバイダは、これらのコンポーネントがインストールされるまで機能しません。

原因は、c:\oracle\ora92 ディレクトリに、Administrators, Authenticated Users, SYSTEMのみアクセス許可になっていたためでした。IUSR_(コンピュータ名)を追加すると、接続可能になりました。

開発環境がFAT32、実機がNTFSだったのが敗因でした:-(

実機セットアップのため、Windows 2000 Serverに、新たにMSDEとOracle Clientをインストールして、MSDEにデータベースを作成し、Oracleのリンクサーバを作成しました。

そして、MSDEから使用するためOracleの表をビューとして作成しようとするとこんなエラーが。

1>SELECT * FROM DB_SVR..USER_FOO.TABLE_BAR
2>GO
メッセージ 7302、レベル 16、状態 1、サーバー TESTSRV、行 1
OLE DB プロバイダ 'MSDAORA.1' のインターフェイスを作成できませんでした。
1>

インターフェースと言う言葉から想像するに、COM回りのエラーであろうと想像は付くのですが(OLE DBってCOMですから)MDACの再インストールを行っても、MSDEの再インストールを行っても解決しませんでした。

一応、
280106 - [HOWTO] SQL Server で Oracle へのリンク サーバーのセットアップおよびトラブルシューティングを行う方法[support.microsoft.com]
に情報がありますが、REGSVR32で確認する前にMDACの再インストールをしたので、あまり有用な情報とは思えませんでした。

だいぶん長いこと試したのですが、結局は、

1. コントロールパネル→管理ツール→コンポーネント サービス を開く
2. ツリーの コンポーネント サービス＞コンピュータ＞マイ コンピュータ を右クリックして、プロパティを表示する
3. 規定のセキュリティ タブの、規定のアクセス許可 で、規定値の編集 を押下しレジストリ値のアクセス許可 ウインドウを表示する
   ちなみにWindows Xpでは、ここではディレクトリセキュリティを編集するみたいな画面が出てきた記憶があるが、2000系はちょっと違うらしい。
4. 追加 を押下し、ユーザーとグループの追加 ダイアログでEveryoneを選択して追加を押下し、OKする

で、ビューが作成できるようになりました。Everyoneでなく、MSDEサービスがどのユーザの権限で実行されるのか調べて設定し直さなくてはいけないのですが、それはまたあとで。とりあえず原因は権限不足、だったみたいです。

2003.12.08 追記
SYSTEMアカウントに対し、権限を追加すればOKです。

しょーもないことですが、SQL関数のCOALESCEって、いつもちょっと読み方に困ってました。
gooの英和辞典で発音を調べてみると「コアレス」てな感じみたいです。

HTMLに出力することを考えて、NULL項目は' 'と出力させようとしたSQLをテストしようとSQL*Plusで実行したのですが

SQL> SELECT NVL(emp_cd, ' ') AS emp_cd
  2  FROM emp_tbl
  3  /
nbspに値を入力してください:

&は変数として扱われるの?そんなん知りませんでした。これはSQL*Plusの置換変数と言って、実行時に値を聞いてきます。ちなみに、&&var、と言うように&を2つ重ねると、そのSQL文内の後続の&varすべてを置換します。

で、置換変数を無効にするにはこうするそうです。

SQL> SET DEFINE OFF

こういうのもありましたね･･･

SQL> SELECT NVL(TRIM('   '), 'null!') FROM DUAL
  2  /

NVL(T
-----
null!

オラクルで、CHAR(8)で年月日の区切りなしで日付を格納してる列を、TO_DATE, TO_CHARでSELECTすると、こんなエラーが出ました。

ORA-01841: (周)年は-4713と+9999の間で指定してください。0であってはなりません。

エラーメッセージを見てもさっぱりわけがわからないのですがたまたまデータが空白の行でした。そりゃあそうですね。

こんな感じで実行すると、同じエラーになりました。

CREATE TABLE SAMPLE_TABLE(
	CHAR_DATE	CHAR(8)
	)
/
INSERT INTO SAMPLE_TABLE
	VALUES('        ')
/
SELECT 
	TO_CHAR(TO_DATE(CHAR_DATE, 'YYYYMMDD'), 'YYYY/MM/DD')	AS FORMATED_DATE
FROM SAMPLE_TABLE
/

このエラーのわけがわからなくて、Web上の情報をあさったところ、面白い事例がありました。

プロバイダがOraOLEDB.Oracle.1なら可能みたい。
MSDAORA.1ではできなかった。

ASPで書くのが面倒な処理はDB側でプロシージャにしておくと便利な場合がある。

MSDEでプロシージャを作ってASPから呼び出してみた。

'ASP
Dim adoCmd
Set adoCmd    = Server.CreateObject("ADODB.Command")
With adoCmd
    .CommandText                    = "pCheckValid"
    .CommandType                    = adCmdStoredProc
    Set .ActiveConnection           = adoConGRDB
    .Parameters("@emp_cd")          = strEmpCd
    .Parameters("@require_admin")   = "1"
    Call .Execute()
End With
If Not IsNull(adoCmd.Parameters("@ret_msg")) Then
    Call Response.Write(adoCmd.Parameters("@ret_msg"))
    Set adoCmd    = Nothing
    Exit Sub
End If

ポイントは、CommandText, CommandTypeを設定後にActiveConnectionを設定すること。そうしないと、パラメータがうまくバインドしない。(なんで?)

ちなみに、パラメータにEmptyを渡すとそのパラメータは「省略された」物として扱われる。ASPのCOOKIEやRequest.Formなどの初期値もEmptyなので、その値をパラメータとして渡す場合は、プロシージャ側で初期値を設定しておくのがお勧めである。

※ プロシージャ側でデフォルト値が指定されていないパラメータを省略すると実行時エラーになってしまう。

プロシージャの中身は、こんな感じ(実際に使ったものをいろいろ省略したため変なプロシージャだが)

SQLServerやMSDEの場合は、SHOWPLAN_ALLまたはSHOWPLAN_TEXTオプションを設定すると、以降発行するSQLはすべて実際には実行されず、実行計画を表示する、という感じになります。

C:\>osql -U username -P password -S servername
1> SET SHOWPLAN_TEXT ON
2> GO 
1> 

通常の実行モードに戻すのは、ONをOFFに変えて、同じ操作を行います。
SHOWPLAN_TEXTより、SHOWPLAN_ALLの方が情報量が多いですが、OSQLでSHOWPLAN_ALLを使用すると、列数が多いため表示が折り返してわけがわからなくなります。
SHOWPLAN_TEXTを使用するか、Common SQL Environment等、実行結果をグリッドに表示できるようなSQL実行ツールを使用するのがお勧めです。