質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.12%

二つのセレクト文から得られた2つの結果を引き算する動的SQL

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 404

mitsu12345

score 13

今、あるオブジェクトが実際に使っているブロック数を調べるsqlを書いています。

まず、実際に割り当てられているブロック数を調べる。(結果は8とする)

SELECT blocks FROM dba_segments WHERE owner='TERATAIL'
AND segment_name='SAMPLE' AND segment_type='TABLE'


次に実際に使われているブロック数を調べる。(結果は4とする)

SELECT count(*) FROM
(SELECT DISTINCT dbms_rowid.rowid_relative_fno(ROWID) file_id,
 dbms_rowid.rowid_object(ROWID) data_object, dbms_rowid.rowid_block_number(ROWID) block_nr
 FROM TERATEIL.SAMPLE);

この場合、実際に使われているブロック数は4ですが、これを動的sqlを使って4という数字を得たいと考えています。

set serveroutput on
CREATE OR REPLACE PROCEDURE empty_blocks(p_owner VARCHAR2, p_table VARCHAR2) IS
DECLARE
 v_str     VARCHAR2(2000);
 v_blocks   TERATEIL.SAMPLE.blocks%TYPE;
 BEGIN
  v_str :=  'SELECT blocks FROM dba_segments WHERE owner= :x
                AND segment_name= :y AND segment_type='TABLE'';
  EXECUTE IMMEDIATE v_str INTO v_blocks USING p_owner, p_table;
  dbms_output.put_line(v_blocks);
EXCEPTION
  WHEN OTHERS THEN dbms_output.put_line(sqlcode||' -- '||sqlerrm);
END;
/
execute empty_blocks('TERATAIL', 'SAMPLE');


ひとまず、ここまではコードを書いたのですが、ここから先が全く思いつきません。何かアドバイスを宜しくお願いします。

set serveroutput on
execute empty_blocks('TERATEIL', 'SAMPLE');
CREATE OR REPLACE PROCEDURE empty_blocks(p_owner VARCHAR2, p_table VARCHAR2) IS
DECLARE
  v_str    VARCHAR2(2000);
  v_num    NUMBER;
  v_block  NUMBER;
BEGIN

          SELECT blocks into v_block FROM dba_segments WHERE owner= p_owner
          AND segment_name = p_owner AND segment_type='TABLE';


           v_str :=   'SELECT count(*) FROM'
                ||' (SELECT DISTINCT dbms_rowid.rowid_relative_fno(ROWID) file_id,'
                ||' dbms_rowid.rowid_object(ROWID) data_object, dbms_rowid.rowid_block_number(ROWID) block_nr FROM '
                || p_owner
                || '.'
                || p_table
                || ')';

           EXECUTE IMMEDIATE v_str INTO v_num;


           dbms_output.put_line(v_block - v_num);
END;
/

エラーメッセージ
PLS-00905: object TEST.EMPTY_BLOCKS is invalid
ORA-06550: 行1、列126:
PL/SQL: Statement ignored

  1. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2018/11/06 00:23

    求めたいのは「割り当てられているブロック数」-「実際に使われているブロック数」ですか?

    キャンセル

  • mitsu12345

    2018/11/12 02:40

    すみません。返信遅れました。はい、求めたいのは「割り当てられているブロック数」-「実際に使われているブロック数」です。ひとまず、ある程度まではコードを書いたのですが、object is invalidと出てエラーになってしまう状態です。編集でコードを追加しておきます。

    キャンセル

回答 2

checkベストアンサー

+1

エラーはコンパイルエラーですね。
DECLAREの宣言が不要だと思われます。
CREATE PROCEDURE文

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/12 22:59

    回答ありがとうございます。DECLAREを消去したらコンパイルが上手くいき、結果4と出力されました。

    キャンセル

0

SELECT count(*) FROM
(SELECT DISTINCT dbms_rowid.rowid_relative_fno(ROWID) file_id,
 dbms_rowid.rowid_object(ROWID) data_object, dbms_rowid.rowid_block_number(ROWID) block_nr
 FROM TERATEIL.SAMPLE);


をPROCEDUREの中で実行したいといいう質問なら、明示カーソルか暗黙カーソルを使えば良いです。わざわざ EXECUTE IMMEDIATE を使う必要もないです。

[PL/SQL]タグは追加してください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/04 07:34

    回答ありがとうございます。EXECUTE IMMEDIATEを使うのではなく、カーソルを使うとした場合、どのように二つのselect文の結果を格納すればいいのでしょうか?静的pl/sqlならば、どちらかのセレクト文の結果をカーソルの宣言時に得て、BEGINの中で他方のセレクト文を実行して、最後に計算するということは可能だと思うのですが、動的sqlの場合でも同様のことは可能でしょうか?

    キャンセル

  • 2018/11/04 08:08

    いちおう、キーワードでググれば見つかる情報は提示しているんですけど。
    まずはこれだけPL/SQL【第1回 変数・制御構文・カーソル・例外処理まで】
    https://qiita.com/ikep/items/fd710bb5645d90c5b77d

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる