以下のVIEWをPROCEDURE内で使用しています。

CREATE OR REPLACE VIEW VW_KAIIN
(CODE,NAME,TEL,STARTYMD)
AS
SELECT * FROM KAIIN
WHERE STARTYMD <= TO_CHAR(SYSDATE,'YYYYMMDD')

※STARTYMDには有効な開始日付が入ります。

条件部分でSYSDATEではまずいことになり、
特定の日付を指定しなければならなくなりました。


VIEW自体にパラメータ渡しはできないということで、

CREATE OR REPLACE PROCEDURE TABLE_B_UPDATE
(I_YYYY IN TABLE_A.YYYYY%TYPE,
I_MM IN TABLE_A.MM%TYPE,
I_DD IN TABLE_A.DD%TYPE,)

PROCEDURE内でI_YYYY、I_MM、I_DDを条件に変更した
VIEWを動的に作成して、処理をしたいのですが、
こういった事は可能でしょうか?

そのまま書いてみましたが、CREATE部分でエラーが出てしまい、コンパイルできませんでした。

もし不可能である場合は、代替案をご教授いただけるとありがたいです。

どうかよろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (2件)

プロシージャ内でも動的SQLを使えばDDLの実行はできます。

試してみてください。
set serveroutput on
declare
w_str varchar2(1000);
begin
--TABLE作成
execute immediate 'create table test_table(val varchar2(100))';
--データ投入
execute immediate 'insert into test_table values (:val)' using 'テスト';
--VIEW作成
execute immediate 'create view test_view as select * from test_table';
--VIEWからSELECT
execute immediate 'select val from test_view' into w_str;
--結果出力
dbms_output.put_line(w_str);
--TABLE,VIEW削除
execute immediate 'drop table test_table purge';
execute immediate 'drop view test_view';
exception
when others then raise;
end;
/
set serveroutput off

ただ、今回の質問はkouta77さんのおっしゃるとおりVIEW動的に作成する意味がよくわかりませんね。
やりたいこと、目的を明確にしてもらうと何かアドバイスできるかもしれません。

この回答への補足

元々、前任者が作っておったもので、
VIEW自体は、他のプログラムで呼び出したり、共通で使っているのは
間違いないと思います。

私自身、オラクルやPLSQLに触れるのが初めてなものですから、
VIEW自体でパラメータが使えたら一番よかったのですが。

プロシージャ内でVIEWの項目が基本軸として使用されており、
安易に、既存のVIEWのコピーを、プロシージャ内のパラメータを使用して、もうひとつ作ったら、いけるかなぁ、と思ってやってみたところ、ダメでしたので、質問させていただきました。

上記方法ためさせていただきます。
ありがとうございます。

補足日時:2011/04/12 19:24
    • good
    • 0
この回答へのお礼

上記方法でやりたいことが作成できました。
VIEW作成以外もご紹介いただき感謝です。
ありがとうございました。

お礼日時:2011/04/13 17:58

プロシージャ―内でCREATE文(DDL文)は記述できません。




そもそもVW_KAIINをどう使いたいのですか?
いちいちVIEWにする必要はなくて、プロシージャ―内でデータをSELECTする時に条件文を付けるのでは
駄目なんですか?
この質問文を見る限りではVIEWにする意図が分かりません。

この回答への補足

他のプロシージャや、プログラム内からも、共通で使いたいので、
VIEWにしております。

まだ、PLSQL自体、見始めて間もないもので、見当違いことを言ってたらすいません。

補足日時:2011/04/12 18:58
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qビュー定義をプログラムで動的に扱う方法

ビュー定義をプログラムで動的に扱う方法
SQLServer2008+VB6(ExcelVBA)構成のシステムで、日付フィールドを含むテーブルのビューを作成しておき、VBプログラムからSQL文でビューを呼び出す時、日付範囲を指定して絞り込まれた結果をビューで返したいのですが、ストアドを使わずにビューだけで実現することはできないでしょうか?

Aベストアンサー

#2です。

>Set Cmd = New ADODB.Command
>Cmd.CommandText = ”CREATE VIEW ビュー AS SELECT * FROM テーブル WHERE 日付='2010/07/09'”
>Cmd.Execute

もちろん、権限があればできます。

しかし、そのリクエストをした人にもう一度確認することをお勧めします。
私もVB内で10行以上のクエリを組み立てて実行しているのを見てげっそりすることはありますので、
「長いクエリはビューで管理したい」というリクエストは理解できます。

ただ、本当に「パラメータまで含めて一切をビューにし、すべてWHERE文の一切ないSELECT * FROM ビューだけにしたい」
というリクエストをしたとすれば、その人はシステムのことを理解していないので、説明すべきだと思います。

・同時に複数のユーザ・複数の処理でビューを参照できなくなるので、ユーザ数×処理数だけ同じようなビューが必要
・ビュー定義の一部だけを再作成することはできないので、再作成には常に全部を実行する必要がある。処理はわかりやすくならない

#2です。

>Set Cmd = New ADODB.Command
>Cmd.CommandText = ”CREATE VIEW ビュー AS SELECT * FROM テーブル WHERE 日付='2010/07/09'”
>Cmd.Execute

もちろん、権限があればできます。

しかし、そのリクエストをした人にもう一度確認することをお勧めします。
私もVB内で10行以上のクエリを組み立てて実行しているのを見てげっそりすることはありますので、
「長いクエリはビューで管理したい」というリクエストは理解できます。

ただ、本当に「パラメータまで含めて一切をビューにし、すべてWHERE文の一切...続きを読む

Qビューで引数を使いたい

4つのテーブルのいずれかをアクセスしますが、
select文はひとつでwhereでテーブルを指定したいので、
 例:select * from ビュー where table_no=1
とかで、table_noの値でアクセスするビューは
作れますか?
※この例の場合、select * from table1が実行したい。

4つのテーブルは全て同じ構造で、
データが違うのみです。

Aベストアンサー

ビューでは不可能だと思います。

同じ様なアクセス方法を行いたいのであれば、
テーブル値を返すユーザー定義関数を作成する方法があるかと思います。構造は同じと言う事なので、1つの関数で済むと思いますし。

例:Select * From ユーザー定義関数(1~4のパラメータ)

上記例の様な使い方が可能です。

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

QOracle(オラクル)で、日付時刻型の検索方法について

質問させていただきます。
データベースはオラクルを使っていて、
SQL文で、抽出するときにエラーが出て困っています。

日付時刻型が「2005/05/26 19:13:00」という感じで入ってます。
2005/05/26 を抽出したいのですが、
BETWEEN '2005/05/26 00:00:00' AND '2005/05/26 23:59:59'

だと、エラーでできません。
どなた様か、ご教授よろしくお願いしますm(_ _)m

Aベストアンサー

日付検索を行う場合は、以下のように書式を含める必要があります。

col BETWEEN TO_DATE('2005/05/26 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2005/05/26 23:59:59','YYYY/MM/DD HH24:MI:SS')

ただ、厳密には

col >= TO_DATE('2005/05/26', 'YYYY/MM/DD')
AND
col < TO_DATE('2005/05/27', 'YYYY/MM/DD')

と書くべきでしょうね。

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

Qsqlplusで表示が変なので、出力を整形したい。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------

上記のように意味不明な形式で出てきます。

例えばこんな風に

select カラム1,カラム2,カラム3 from hoge;

カラム1 1の値
---------------------------
カラム2 2の値
---------------------------
カラム3 3の値

等のように分かりやすく表示できないでしょうか?

ちなみにOracle9iR2を使用しています。
sqlに関するツールは使用できないルールでして、あくまでsqlplusのコマンド上でみやすくしなければなりません。

分かりづらくですいませんが、皆さま、ご教授お願いします。

いつもお世話になっています。

サーバにアクセスしてsqlplusで、
データを調べたいのですが、
出力形式が見づらくて困っています。

よくわからいのですが、
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
---------------------------
1の値 2の値
3の値
---------------------------
カラム名1
---------------------------
カラム名2
---------------------------
カラム名3
-----------------------...続きを読む

Aベストアンサー

SQLPLUSを起動して、

SQL>set linesize 列数

でどうだ。

SQL>show linesize

で確認ができる。

QViewにインデックスは張れますか?

件名の通りなのですが、作成したViewが遅くて困っています。
改善方法としてはViewを作成しないで従来のSQLにインデックスを張って取得する方法にしようかなと考えています。
なにかいい方法はありますか?

Aベストアンサー

Viewの元テーブルに適切なIndexを貼る、ではいけないのですか?

Qバインド変数について

PL/SQLのバインド変数について調べていますが理解できず困っています。

[理解内容]
1.利点
 :SQL文の検索にバインド変数を利用すると、同一のSQLとして実行できる
  ⇒ 繰り返し処理(ループ)では有効

2.使用方法
 :静的SQL・動的SQLで使用可能 

 [疑問点]
  静的SQLではバインド変数をどう宣言すればよいですか?
  [SQL*Plus]・[動的SQL]は例文がありました

  ●SQL*Plusでの宣言方法

   (例)
    SQL> set null '<null>'
    SQL> set head off
    SQL> variable var_at_sqlplus number

  ●動的SQLの記述方法

   (例)
    declare
     v_sql varchar2(256);
     v_id varchar2(3);

    begin
     v_sql = 'select id into :v_id from test';
     execute immediate v_sql into v_id;
    end;

  ●静的SQL ・・・【 エラーになる 】

    declare
     variable v_id varchar2(3);

    begin
     select id into :v_id from test'
    end;


どんな事でもかまいませんので回答よろしくお願いします

PL/SQLのバインド変数について調べていますが理解できず困っています。

[理解内容]
1.利点
 :SQL文の検索にバインド変数を利用すると、同一のSQLとして実行できる
  ⇒ 繰り返し処理(ループ)では有効

2.使用方法
 :静的SQL・動的SQLで使用可能 

 [疑問点]
  静的SQLではバインド変数をどう宣言すればよいですか?
  [SQL*Plus]・[動的SQL]は例文がありました

  ●SQL*Plusでの宣言方法

   (例)
    SQL> set null '<null>'
    SQL> set head off
    SQL> variable var_at...続きを読む

Aベストアンサー

#1 です。

>という事はPL/SQL内の静的SQLではバインド変数は使えないと言うことでしょうか?

ストアドプログラム(パッケージ/プロシージャ/ファンクション)内では動的以外には使えないと思います。
(SQL*PlusからストアドをEXECUTEする時の引数として使用する事は、もちろん可能ですが)

但し、私が例に書いた通り、無名PL/SQLブロック(DECLARE~BEGIN~END)内では静的にも使用可能です。

質問者様の試したサンプルで言えば。

CREATE OR REPLACE PROCEDURE TEST.TEST(v_Id IN VARCHAR2) IS

v_name varchar2(20);

BEGIN
SELECT name
INTO v_name
FROM test
WHERE id = v_Id;

EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
/

これでコンパイルは通りますよね。(すいません、環境が無いので未検証です)

「これじゃ、v_Idはバインド変数にならないんじゃ?」と思うかもしれませんが、これバインド変数なんです。
(うーん、混乱しちゃいますかね?)

SQLトレースを取るとわかるんですが、引数v_Idが '1' だろうが '2' だろうがトレースで解析されるSQLでは

WHERE id = :b1

と言うバインド変数になるんですよ。

>このあたりが理解できていない部分です。

パフォーマンスチューニングで扱われるバインド変数と置換変数(リテラル)の差異を把握する事が、実はこの辺の理屈を理解する上での近道かもしれません。
OTNでパフォーマンス・チューニング・ガイドを落とすか、もしくはパフォチュー系の本を一冊読んでみるとよいかと思います。

もしJavaの経験がおありでしたら、"?"(プレースホルダ)を使ったSQLと、リテラル直指定の同じSQLをそれぞれ実行するJDBCプログラムを作って、SQLトレース(とtkprof)を取って較べてみると見えてくる物があると思います。
私自身、SQLトレースの中身を見るようになって、この辺の事が理解できるようになりましたから。
(SQLトレースに関しては前述のチューニング・ガイド等参照)

#1 です。

>という事はPL/SQL内の静的SQLではバインド変数は使えないと言うことでしょうか?

ストアドプログラム(パッケージ/プロシージャ/ファンクション)内では動的以外には使えないと思います。
(SQL*PlusからストアドをEXECUTEする時の引数として使用する事は、もちろん可能ですが)

但し、私が例に書いた通り、無名PL/SQLブロック(DECLARE~BEGIN~END)内では静的にも使用可能です。

質問者様の試したサンプルで言えば。

CREATE OR REPLACE PROCEDURE TEST.TEST(v_Id IN VARCHAR2) IS

v_name var...続きを読む

QOracle 2つのDate型の値の差を「分」で取得したい

DBはOracleを使用しています。
Date型の項目AとBがあり、それぞれ日時が設定されています。そのAとBの日時の差を「分」で取得したいと思います。
AとBは、1時間以内の場合もあれば、複数日にまたがる場合もあります。
SQLでA-Bで差分は取得できるのですが、その後、どうすれば、「分」に変換できるのかがわかりません。

よろしくお願いします。

例1)
A: 2003/06/18 9:00
B: 2003/06/18 6:00
→3時間→180「分」を返却したい

例2)
A: 2003/06/18 9:00
B: 2003/06/16 9:00
→2日→2880「分」を返却したい

Aベストアンサー

date型は、整数部で日、小数部で時間を管理しています。
なんで、1分 = 1 / 24 * 60 = 1/1440となります。

ということで、
(B - A) * 1440

とすればいいことになります。

QOracleで流したSQLのログを取得できますか?

ASP+Oracle9iで作られたシステムがあります。
Oracleの設定などは前任者がしており、まともな引継ぎを受けないまま私が維持運用員となりました。
現在DBからあるレコードが消えてしまい(最初から作られてない可能性もあり)、顧客からクレームがついています。
状況から考えて怪しいプログラムを調査したのですが、原因不明でした。
そこで、Oracleの方で今まで流れたSQLをログとして保存していれば手がかりがつかめるのではないかと思い、情報を探しているのですが見つからないので教えて頂きたいのです。
私はOracleの知識(管理面について)が殆どなく、Oracleを使うことはできるが、Oracleの設定がどういう状況になっているか調べることもできず、資料もありません。

1.そもそもOracleにSQLをログとして保存する機能があるのか?
2.あるとすれば、保存機能が有効になっているか、保存したファイルがどこにあるかをどこで確認できるか?

以上の点についてご存知の方、どうぞ回答よろしくお願いします。

Aベストアンサー

OracleのSQL実行痕跡は以下に示すものの中にあります。
ただし、それぞれの設定レベル、保存方法によって、参照できる範囲は変わります。

1.オンラインREDOログとアーカイブログ
  オンラインREDOログはすべてのOracleデータベースに存在します。
  ですが、そのREDOログのサイズとシステムのアクセス量によって保存期間は変化します。アーカイブログ設定しておけば、古いREDOログはアーカイブとして吐き出されます。
  これらは#1の方がおっしゃったLOGMINERで実行済みSQLを調査することができます。
  アーカイブログモードになっているかどうかは、下記で確認できます。
   SQL> select log_mode from v$database;

  また、どのアーカイブログファイルを調査するかは下記を参照すれば良いと思います。
   SQL> select name,to_char(completion_time, 'yyyy/mm/dd hh24:mi') adate from v$archived_log;

2.フラッシュバック問い合わせ
  これは初期化パラメータUNDO_MANAGEMENTがAUTOに設定されており、かつUNDO_RETENTIONが保存期間として適切に設定されている場合のみ使用できます。
  やっていることは、UNDO表領域(旧RBS用表領域)の中に残っている実行履歴を参照しています。したがって、データベース設計時にフラッシュバック問い合わせを前提としていない限り、使える可能性は低いです。

3.その他
  監査を実行していれば、実行SQLを取得できる可能性があります。
  ただし、監査レベルなどを理解しておかないと、すべてのSQLが取得されていない可能性がありますので、難しいかもしれません。

OracleのSQL実行痕跡は以下に示すものの中にあります。
ただし、それぞれの設定レベル、保存方法によって、参照できる範囲は変わります。

1.オンラインREDOログとアーカイブログ
  オンラインREDOログはすべてのOracleデータベースに存在します。
  ですが、そのREDOログのサイズとシステムのアクセス量によって保存期間は変化します。アーカイブログ設定しておけば、古いREDOログはアーカイブとして吐き出されます。
  これらは#1の方がおっしゃったLOGMINERで実行済みSQLを調査することができま...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報