Corredor

ウェブ、プログラミングの勉強メモ。

Oracle

Oracle DB で SQL ファイルをスクリプトとして実行した時に Sleep・Wait 処理を挟む

Oracle DB の操作中、Sleep・Wait 的な処理を挟めることが分かったのでやってみた。 先に、対象の環境で DBMS_LOCK 権限を付与しておく。 SYS/SYSDBA@my_oracle AS SYSDBA GRANT EXECUTE ON DBMS_LOCK TO my_db; 次に、SQL スクリプト中で DBMS_LOCK.SLEEP()…

Oracle DB の NVL() と NVL2() の違い

Oracle には NVL() と NVL2() という2つの関数があったので、違いを見てみた。 SELECT NVL(NULL, 'NULL!'), NVL2(NULL, 'NOT NULL!', 'NULL!') FROM DUAL; NVL() は第1引数が NULL の時に第2引数を返すモノ。第1引数が NULL でなければ、第1引数がそのまま返…

Oracle DB で数値のみのフィールドかどうかを判定する

Oracle DB で、対象のフィールドの内容が数値のみかどうかを判定する。 例えば、users.user_code には 08114 といった数値のみのユーザコード z916294 といった数値以外の文字列を含むユーザコード が混在していて、これらを区別したいとする。 以下のように…

Oracle DB で MINUS ALL・INTERSECT ALL を再現する

Oracle DB には MINUS ALL や INERSECT ALL がないので、ちょっと手を加えて再現する。 MINUS ALL の再現 INTERSECT ALL の再現 MINUS ALL の再現 通常の MINUS は1つ目の SQL から2つ目の SQL にあるものを除外し、重複行は1行にまとめて返す。UNION と違い…

Oracle DB で色々な一覧取得

Oracle DB でテーブル一覧やデータディクショナリビューの一覧を取得する。 テーブル一覧を取得する データディクショナリビューの一覧を取得する ついでに : 行番号を一緒に出力するには テーブル一覧を取得する SELECT * FROM USER_TABLES ORDER BY TABLE_…

Oracle DB のシーケンスを作成・参照・操作する

Oracle DB のシーケンスを参照したり、「1 ずつ増える」挙動を「100 ずつ増える」挙動に変更してみたりする。 -- シーケンス一覧を取得する SELECT * FROM USER_SEQUENCES; -- 次のシーケンス値を取得 (実行するたびにインクリメントされる) SELECT my_seque…

Oracle DB で改行を含む文字列を Insert・Update する

以前、PostgreSQL の文脈で改行を含む Insert・Update を紹介したが、Oracle DB の場合はちょっと違いがあったので紹介。 neos21.hatenablog.com Oracle の場合、CHR(13) で CR (キャリッジリターン) を入れてやらないといけなかった。CHR(10) での LF (ライ…

Oracle DB でのプロシージャの作り方

Oracle DB でプロシージャを作り、それを呼び出す方法。 -- プロシージャを作る Create Or Replace Procedure myProcedure ( param In Varchar2 ) Is var Number; Begin Execute Immediate 'SELECT hoge FROM my_table WHERE name = ''' || param || '''' In…

Oracle DB でカラムの順序を変更するには

Oracle DB でカラムの順序を入れ替えたりするには、ALTER TABLE では操作できないので、以下のように操作する。 -- 元となる table_1 からカラム順を変更して SELECT し、table_2 を作る CREATE TABLE table_2 AS SELECT column_c, column_b, column_a FROM …

Oracle DB でシステム日付を参照したり、和暦変換したり、日付を加減算したり

Oracle DB の日付に関するアレコレ。11g ぐらいの頃の知識なので少し古くなっているかも?最近のことはよく調べていない。 システム日付を取得する SELECT TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24:MI:SS' ), TO_CHAR(SYSTIMESTAMP , 'YYYY-MM-DD HH24:MI:SS.FF3…

PowerShell から Oracle DB に接続してみる

前回は PowerShell から ODBC データソースを利用して DB 接続してみたが、今度は Oracle DB の接続文字列を用意して直接 Oracle DB にアクセスしてみる。 neos21.hatenablog.com # SQL 文作成 Function function createSelectSQL() { $SQL = New-Object Sys…

Oracle DB で一時テーブルを作る

Oracle DB では、一時テーブルを作れる。 CREATE GLOBAL TEMPORARY TABLE my_temporary_table ( my_column_1 CHAR(10) NOT NULL, my_column_2 NUMBER(5, 2) my_column_3 DATE ) ON COMMIT PRESERVE ROWS; こんな感じ。 例のように ON COMMIT PRESERVE ROWS …

PL/SQL の基本的な書き方をまとめてみる

PL/SQL でよくある「SELECT した結果を1行ずつ操作する」という処理を雛形的に書いてみた。その他、よくあるイディオムをまとめてみた。 基本形 ログを出力する UPDATE 時の更新行数を知る 暗黙的カーソル処理 基本形 Declare Cursor csr Is SELECT hoge FRO…

Oracle DB で表領域の使用率などを見る

プロとしてのOracleアーキテクチャ入門第2版 図解と実例解説で学ぶ、データベースの仕組み [ 渡部亮太 ]ジャンル: 本・雑誌・コミック > PC・システム開発 > その他ショップ: 楽天ブックス価格: 2,808円 Oracle DB で表領域の使用率などを見るには、以下のよ…

Oracle DB でテーブルとリサイクルビンの復元・削除

絵で見てわかるOracleの仕組み [ 小田圭二 ]ジャンル: 本・雑誌・コミック > PC・システム開発 > その他ショップ: 楽天ブックス価格: 2,376円 Oracle DB でテーブルを消すと、リサイクルビンというゴミ箱的な領域にゴミが残る。このリサイクルビンを含めたテ…

Oracle DB でよく使う一覧表示系のユーザディクショナリ

Oracleの現場を効率化する100の技 [ 鈴木健吾 ]ジャンル: 本・雑誌・コミック > PC・システム開発 > その他ショップ: 楽天ブックス価格: 3,218円 Oracle DB を保守管理するときによく使うと思われる、一覧表示して確認できる系のユーザディクショナリを紹介…

Oracle DB のパスワードが分からなくなった時の裏技

Oracle Database で、あるユーザのパスワードが分からなくなった時に、以下の方法でパスワードを変更することができる。 -- ALTER USER 権限を持つユーザで接続する -- SYS ユーザのパスワードを「test」に変更する ALTER USER SYS IDENTIFIED BY test; -- …

Oracle DB の全テーブルのレコード数と最終更新日時を求めてみる PL/SQL 版

前回の記事と全く同じことを PL/SQL でやってみる。 neos21.hatenablog.com これも TableList.sql とでも名付けて Start で実行してやればデータが CSV 形式で保存できる。 Set feedback off Set serverout on Set trimspool on Spool C:\TableList.csv DECL…

Oracle DB の全テーブルのレコード数と最終更新日時を求めてみる

以前こんな記事を書いたのだが、それの発展版。 neos21.hatenablog.com 大抵の業務システムの DB だと、レコードの最終更新日時を記録するカラムを作ってあると思う。ココではそういうカラムを DATE 型の updated_at カラムとして全テーブルに作ってあるもの…

SQL*Loader を使ってみる

SQL*Loader とは、Oracle DB 付属のツールで、CSV ファイルなどに書かれたデータを一気に INSERT できるツールだ。コントロールファイルと呼ばれる設定ファイルの書き方に特徴があったり、設定値未指定の場合の初期値に難があったりするので、参考となる情報…

VBSQLipt:Oracle DB に接続して SQL を実行する VBScript を作った

VBScript実用プログラミング・テクニック―正規表現、OOP、SQLを応用した実用ツールの作成技法作者: 佐藤信正出版社/メーカー: メディアテック出版発売日: 2006/12メディア: 単行本購入: 1人 クリック: 6回この商品を含むブログ (1件) を見る SQL*Plus がない…

Oracle で複数レコードを一気に INSERT する記法を毎回忘れる

INSERT ALL の書き方を毎回忘れるのでメモ。 こう書く。 INSERT ALL INTO tbl_hoge (id, name, age) VALUES (1, 'Michael', 55) INTO tbl_hoge (id, name, age) VALUES (2, 'Christopher', 77) INTO tbl_hoge (id, name, age) VALUES (3, 'Lea', 55) INTO tb…

Oracle でひらがな・全角カタカナ・半角カタカナ変換

英数字の半角・全角変換は TO_SINGLE_BYTE (半角化) や TO_MULTI_BYTE() (全角化) で可能。これに UPPER() (大文字化) や LOWER() (小文字化) を組み合わせれば、LIKE 演算子であいまい検索が可能になる。 しかし、これらはひらがなやカタカナの変換は行って…

Oracle のデータディクショナリの一覧を取得する

データディクショナリとは Oracle データベース内のオブジェクトや各種情報をテーブル形式で取得できる特殊なビュー。 SELECT TABLE_NAME FROM DICTIONARY; でそのデータディクショナリの一覧が取得できる。あとは DESC でカラム名を確認したり、SELECT * で…

Windows7 で Oracle 12c を使う (環境構築)

【オラクル認定資格試験対策書】ORACLE MASTER Bronze[Bronze DBA 12c](試験番号:1Z0-065)完全詳解+精選問題集(オラクルマスタースタディガイド)作者: エディフィストラーニング,飯室美紀,西昭彦,岡野友紀出版社/メーカー: SBクリエイティブ発売日: 2015/03/…

Oracle 12c をインストールする

前回の記事で、64bit Windows7 環境に Oracle 12c をインストールするためのファイルをダウンロードした。 neos21.hatenablog.com 今回はその続き。 2ファイルをダウンロードしたら まずは2つの zip ファイルを解凍する。いずれも database フォルダがルート…

Oracle 12c をダウンロードする間に OTN ライセンスについて勉強する

オラクルマスター教科書 Silver Oracle Database 12c作者: 株式会社システム・テクノロジー・アイ林優子,代田佳子出版社/メーカー: 翔泳社発売日: 2016/03/11メディア: 単行本(ソフトカバー)この商品を含むブログ (1件) を見る Oracle DB の 12c を個人の …

Oracle DB で現在オープンなカーソルを調べるには

手っ取り早く調べるには SELECT * FROM V$OPEN_CURSOR; という SELECT 文で確認できる。 ただしこれは完全に現在の状態が分かるワケではないようで、既に閉じてあるカーソルもしばらく残って見えてしまう。少し間を空けるとたしかに消えるので、簡単な動作確…

PreparedStatement を close しないとカーソルが close されない?:PreparedStatement と ResultSet の関係

前回の続き。 neos21.hatenablog.com Java プログラムで PreparedStatement を使って SQL を発行した時も、Oracle DB 側では内部的にカーソルオブジェクトが生成されていることは分かった。 ではどうして、「ORA-01000 最大オープン・カーソル数を超えました…

PreparedStatement を close しないとカーソルが close されない?:まずは PreparedStatement とカーソルをおさらい

Oracle DB に DBUtils を使う Java プログラムから接続していて、とある処理をさせていたら「ORA-01000 最大オープン・カーソル数を超えました」というエラーが出た。 Oracleを使用していると「ORA-01000 最大オープン・カーソル数を超えました」と表示され…