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

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

新規登録して質問してみよう
ただいま回答率
85.50%
Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) はOracle CorporationによるSQL(非手続き型言語)を手続き型言語に拡張させるために開発されたプログラミング言語です。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

2411閲覧

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

退会済みユーザー

退会済みユーザー

総合スコア0

Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) はOracle CorporationによるSQL(非手続き型言語)を手続き型言語に拡張させるために開発されたプログラミング言語です。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2018/11/03 01:39

編集2018/11/11 17:45

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

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

SQL

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

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

SQL

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

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

SQL

1set serveroutput on 2CREATE OR REPLACE PROCEDURE empty_blocks(p_owner VARCHAR2, p_table VARCHAR2) IS 3DECLARE 4 v_str VARCHAR2(2000); 5 v_blocks TERATEIL.SAMPLE.blocks%TYPE; 6 BEGIN 7 v_str := 'SELECT blocks FROM dba_segments WHERE owner= :x 8 AND segment_name= :y AND segment_type='TABLE''; 9 EXECUTE IMMEDIATE v_str INTO v_blocks USING p_owner, p_table; 10 dbms_output.put_line(v_blocks); 11EXCEPTION 12 WHEN OTHERS THEN dbms_output.put_line(sqlcode||' -- '||sqlerrm); 13END; 14/ 15execute empty_blocks('TERATAIL', 'SAMPLE'); 16

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

PL/SQL

1set serveroutput on 2execute empty_blocks('TERATEIL', 'SAMPLE'); 3CREATE OR REPLACE PROCEDURE empty_blocks(p_owner VARCHAR2, p_table VARCHAR2) IS 4DECLARE 5 v_str VARCHAR2(2000); 6 v_num NUMBER; 7 v_block NUMBER; 8BEGIN 9 10 SELECT blocks into v_block FROM dba_segments WHERE owner= p_owner 11 AND segment_name = p_owner AND segment_type='TABLE'; 12 13 14 v_str := 'SELECT count(*) FROM' 15 ||' (SELECT DISTINCT dbms_rowid.rowid_relative_fno(ROWID) file_id,' 16 ||' dbms_rowid.rowid_object(ROWID) data_object, dbms_rowid.rowid_block_number(ROWID) block_nr FROM ' 17 || p_owner 18 || '.' 19 || p_table 20 || ')'; 21 22 EXECUTE IMMEDIATE v_str INTO v_num; 23 24 25 dbms_output.put_line(v_block - v_num); 26END; 27/

エラーメッセージ
PLS-00905: object TEST.EMPTY_BLOCKS is invalid
ORA-06550: 行1、列126:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2018/11/05 15:23

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

退会済みユーザー

2018/11/11 17:40

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

回答2

0

ベストアンサー

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

投稿2018/11/12 02:42

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2018/11/12 13:59

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

0

SQL

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

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

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

投稿2018/11/03 04:14

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2018/11/03 22:34

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問