カテゴリー別アーカイブ: オラクル

[C#]オラクルとの接続とSQL実行

using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

using (OracleCommand com = new OracleCommand()){
OracleConnection con = new OracleConnection();
con.ConnectionString = "User id=USERID;Password=PASSWORD;Data Source=DBNAME;";

con.Open();
OracleTransaction tra = con.BeginTransaction(IsolationLevel.ReadCommitted);

try
{
com.CommandText = "insert into TEST values(val1,va2)";
com.Connection = con;
com.Transaction = tra;
com.ExecuteNonQuery();
tra.Commit();
}
catch(System.Exception)
{
tra.Rollback();
throw;
}
finally
{
con.Close();
}
}

オラクル 11g新機能(SQL関数)

役に立ったものを紹介する

1.withによる再帰処理(with)
2.列集計追加(rollup)
3.n番目集計(nth_value)



1.再帰を用いたwith
階層問い合わせが容易に行える。使い方によっては最適化問題も解くことができる。
TEST_TABLE

	ID	OYAID
---------- ----------
	 1
	 2	    1
	 3	    1
	 4	    2
	 5	    3
	10
	11	   10
	12	   10

SQL

with rec(ID,oyaID,LV,Path) as
(
	select ID,oyaID,1,to_char(ID) from TEST_TABLE where oyaID is NULL
	union all
	select b.ID,b.oyaID,LV+1,a.Path || '-' || to_char(b.ID)
	from rec a, TEST_TABLE b
	where  a.ID=b.oyaID
)
select * from rec

実行結果

	ID	OYAID	      LV PATH
	 1		       1 1
	10		       1 10
	 2	    1	       2 1-2
	 3	    1	       2 1-3
	11	   10	       2 10-11
	12	   10	       2 10-12
	 4	    2	       3 1-2-4
	 5	    3	       3 1-3-5



2.pivotとrollupを用いた例

SQL

select d,sum(a)/count(a) as a,sum(b)/count(b) as b,sum(c)/count(c) as c
from
(
WITH REC(d,g,val) AS
(
	select 1,1,1 from dual union all
	select 1,1,2 from dual union all
	select 1,2,6 from dual union all
	select 2,2,2 from dual union all
	select 2,3,4 from dual union all
	select 3,3,4 from dual
)
SELECT * FROM REC
pivot (sum(val) for g IN (1 as a,2 as b,3 as c))
)
group by rollup(d)

実行結果

	 D	    A	       B	  C
---------- ---------- ---------- ----------
	 1	    3	       6
	 2		       2	  4
	 3				  4
		    3	       4	  4



3.n番目集計(nth_value)

select d,val
,nth_value(max(val),2)over(partition by d order by val
rows between unbounded preceding and unbounded following) max_2nd
from
(
WITH REC(d,val) AS
(
	select 1,1 from dual union all
	select 1,2 from dual union all
	select 1,6 from dual union all
	select 2,2 from dual union all
	select 2,8 from dual union all
	select 2,4 from dual union all
	select 3,1 from dual union all
	select 3,9 from dual union all
	select 3,4 from dual union all
	select 3,0 from dual union all
	select 4,8 from dual union all
	select 4,9 from dual union all
	select 4,3 from dual
)
SELECT * FROM REC
)
group by d,val
order by d,val

実行結果

	 D	  VAL	 MAX_2ND
---------- ---------- ----------
	 1	    1	       2
	 1	    2	       2
	 1	    6	       2
	 2	    2	       4
	 2	    4	       4
	 2	    8	       4
	 3	    0	       1
	 3	    1	       1
	 3	    4	       1
	 3	    9	       1
	 4	    3	       8
	 4	    8	       8
	 4	    9	       8

参考文献
図でイメージするOracle DatabaseのSQL全集

集合演算子

オラクルのdual表を用いて一時的な表を二つ作成し、集合演算子の確認をする。

SQL

select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual

実行結果

	 2	    3
---------- ----------
	 2	    3
	 3	    4
	 4	    5
	 5	    6

SQL

select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5

以上2つのクエリを用いて、以下にunion, union all, intersect, exceptの実行結果を示す。PostgreSQLではminusについてexceptを用いる。

1.union
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
union
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5
	 5	    6

2.union all
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
union all
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5
	 2	    3
	 3	    4
	 4	    5
	 5	    6

3.intersect
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
intersect
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 2	    3
	 3	    4
	 4	    5

4.minus
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
minus
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2

オラクル データディクショナリ

オラクルにはテーブルやソースなどの情報をデータディクショナリとして記録してある。

データディクショナリの種類

DBA_*	データベース内全てのオブジェクトに関する情報
ALL_*	自分がアクセスできるオブジェクトに関する情報
USER_*	自分の所有するオブジェクト(自スキーマのオブジェクト)に関する情報
その他	セッションやロール、言語環境に関する情報

主要なデータディクショナリ

DAU	表名	         説明
***	CATALOG	         表・ビュー・索引・シノニムの情報を調べる
***	COL_COMMENTS	 表の列に付けられたコメント情報を調べる(便利!)
**	COL_PRIVS_MADE	 表の列にアクセスできるユーザを調べる
**	COL_PRIVS_RECD	 アクセスできるスキーマの表の列を調べる
***	CONSTRAINTS	 制約の情報を調べる
***	CONS_COLUMNS	 制約が定義されている列の情報を調べる
*	DATA_FILES	 データファイルの情報を調べる
***	ERRORS	         解決されていないエラー情報を調べる
**	EXTENTS	         セグメントに割り当てられたエクステントの情報
**	FREE_SPACE	 表領域内の使用可能エクステント情報
***	INDEXES	         索引の情報
***	IND_COLUMNS	 索引がひも付いている表・列を調べる
***	OBJECTS	         オブジェクトの情報
***	PROCEDURES	 プロシージャに関する情報
*	PROFILES	 プロファイルに関する情報
*	ROLES	         ロールに関する情報
**	ROLE_PRIVS	 ユーザやロールに付与されたロールを調べる
***	SOURCES	         ストアドやファンクションのソースを確認する(便利!)
**	SEGMENTS	 セグメントに割り当てられたエクステントとブロック数を調べる
***	SEQUENCES	 シーケンスに関する情報
***	SYNONYMS	 USER_:プライベートシノニムを確認する
**	SYS_PRIVS	 ユーザとロールに付与されたシステム権限の情報を示す
***	TABLES	         表に関する情報
**	TABLESPACES	 表領域に関する情報
***	TAB_COLUMNS	 表の列に関する情報
***	TAB_COMMENTS	 表に付けられたコメントに関する情報(便利!)
**	TAB_PRIVS_MADE	 スキーマにアクセスできるユーザを調べる
**	TAB_PRIVS_RECD	 アクセスできるスキーマを調べる
*	TEMP_FILES	 一時表領域に属するファイル情報を調べる
***	TRIGGERS	 トリガーに関する情報
**	TS_QUOTAS	 ユーザの表領域に対する領域割り当て制限情報
***	TYPES	         タイプに関する情報
***	USERS	         ユーザに関する情報
***	VIEWS	         ビューに関する情報

例えばパッケージPK_TESTのソースを取得したいときは、
select TEXT from all_sources where NAME=’PK_TEST’