Sybase SQL & Functions

 

SQL

 

@:識別子の最初の文字として使用すると、ローカル変数となる。

@@:識別子の最初の文字として使用すると、グローバル変数となる。

#:テンポラリ・テーブルの接頭辞

 

select valid_name(xxx)で識別子が有効かどうか確認できる。

 

算術演算子

%:算術演算子として用いられた場合、剰余を返す。

 

ビット演算子

&:ビットAND演算子   A&B(両方trueのとき、true

|:ビットOR演算子    A|B(片方trueのとき、true

^:ビットXOR演算子   A^B(両者が異なるとき、true

~:ビットNOT(補数)演算子  ~A01を反転させる)

 

文字列連結演算子

+:文字列を結合

 

比較演算子

!>:より大でない

!<:より小でない

 

any:

all:

 

select:

カラム名の指定(すべて同じ結果をかえす)

select boj_sec_code as "BOJ Security Code", * from apps_gm..tb_cur_price;
select boj_sec_code "BOJ Security Code", * from apps_gm..tb_cur_price;
select "BOJ Security Code" = boj_sec_code, * from apps_gm..tb_cur_price;

 

出力する行数を制限する。

set rowcount 10      10行に制限)

set rowcount 0       (制限なしにリセット)

ASE15.0以降ではselect top 10が使える。

 

from:

ひとつのクエリによって参照できるテーブルとビューの最大数は16

(ビュー内部で参照されるテーブル等も含まれる)

 

where:

nullの使い方

where a = null       // 有効

where b != null      // 有効
where c is null      //
有効

where d is not null  // 有効

 

like:

%:0文字以上の文字列と一致

_:任意の一文字と一致

[specifier]:[a-f](文字範囲指定),[ace] (文字セット指定)などとして使う。
[^specifier]:
指定した文字列が含まれないものを抽出。

[]:like句の中で、ワイルドカード文字を囲むとそれをワイルドカードとしてではなく、その文字として扱う。

like 5[%](5%が抽出される)

 

Tips!

大文字小文字の区別なしに’doi’という文字を検索したい場合、ちょっとした工夫を施せば実現できる。

(言い換えれば、Nativeでこれをサポートする機能はない)

select * FROM hts_group_control
where upper(group_user_name) like '%DOI%'
order by group_user_name
として、検索される側をあらかじめ全て大文字に変換しておけばよいだけのことである。

 

 

escape:
like句にエスケープ文字を指定する。

like 5@% escape @(5%が抽出される)

 

引用符:

文字および日付データは一重または二重引用符で囲む。

文字の中に引用府が含まれる場合は、その引用符を連続して2つ使用するか、

もう一方の種類の引用符で引用符を囲む。

 

NULL:

null同士はほかのカラムとジョインしない。

 

isnull(expr, value)

nullの置き換。exprnullの場合、valueの内容で置き換えられる。

 

print:

print '@beginning_date = %1! : %2!', @beginning_date, @ending_date

で、変数の内容が表示できる。

 

 

集合関数

syntax)

aggregate_funciton ([all|distinct] expression)

 

functions)

sum

avg

max

min

count

NULLcountによって、カウントされない。ただし、count(*)はすべての行をカウントする。

 

keyword)

distinct:(ただ単に)重複する値を欠落させる

select sum(distinct a) from testAggregate

 

 

group by:クエリ結果のグループ編成(小分け)

一つのgroup by句に使用できるカラムや式の最大数は16

1> select a, sum(b), avg(c) from testAggregate group by a

2> go

 a

 ----------- ----------- -----------

           1          10          15

           2          20        NULL

           3          70          72

これらの結果を「ベクトル集合」と呼ぶ。

 

それに対して、

1> select sum(b), avg(c) from testAggregate

2> go

 

 ----------- -----------

         100          53

のような単一行の結果を「スカラ集合」と言う。

 

fipsflagger ?

 

having:group by句によって定義されたローの取捨選択

1> select avg(b), sum(c) from testAggregate group by a having avg(b) > 30

2> go

 

 ----------- -----------

35                                145

 

order by:

descを指定すると降順に並べ替えられる(ascで昇順だが、これは省略可能)

 

ジョイン:

セルフジョインと相関名:

 

ANSI外部ジョイン:(左方結合、右方結合)

select select_list

from table1 {left | right} [outer] join table2 on predicate [ and (join restriction) ]

 

述語はon句に入れるべきか、where句に入れるべきか

内部テーブルと外部テーブルの両方を含む制約を入れる場所は、必要な結果セットによって決まる。

- 制約がtrueであるローだけに興味がある場合は、制約をwhere句に入れる。(内部、外部両方のテーブルがwhere句の制約を受ける)

- 制約を満たすかどうかに関係なく、外部テーブルのすべてのローを含める場合、制約をon句に入れる。(onで結合されたテーブルのみ制約を受ける)

 

ネストしたANSI外部ジョイン:

ネストした外部ジョインは一つの外部ジョインの結果セットを別の外部ジョインのテーブル参照として利用する。

 

select * from TA_User U
left join TA_UserGroup G
on U.UserGroup = G.UserGroup
and G.UserGroup = 'DEBT'
left join TA_UserGroupTask T
--on U.UserGroup = T.UserGroup    @
on G.UserGroup = T.UserGroup      A
@、AのどちらをActiveにするかで結果が変わってくる。

というのは、AでGDEBTしか抽出しないので、TDEBTとのみジョインするが、

@のUはすべてのUserGroupを持っているので、TはすべてのUserGroupとジョインする

 

ネストした外部ジョインの括弧

ネストした外部ジョインはカッコのあるなしに関係なく同じ結果を生成する。

 

ネストした外部ジョインの順番

select * from TA_User U
left join
(TA_UserGroup G
left join TA_UserGroupTask T
on G.UserGroup = T.UserGroup
and G.UserGroup = 'DEBT'

)
on U.UserGroup = G.UserGroup
と書くと、先にボールド部分を評価して、その結果とTA_Userのジョインを実行する。

sp_helpjoins:

ジョイン可能なフィールドの候補を表示

 

exec sp_helpjoins TA_User, TA_UserGroup

first_pair   

LastUpdated   LastUpdated

ModifiedBy    ModifiedBy

UserGroup     UserGroup

lgcl_del_flg  lgcl_del_flg

(return status = 0) 

 

サブクエリ:

サブクエリの制限事項

サブクエリのSelectリストはexistsサブクエリを除き、1つのカラム名だけで構成される必要がある。

existsサブクエリの場合は、通常(*)が使われる。

order by, group by, compute byリストの中ではサブクエリは使えない。

サブクエリにはfor browse句またはunionを含むことはできない。

式サブクエリでは、単一の値を返すためにdistinctを含めることが多い。

限定述語サブクエリ0個以上のリストを返すもの

 

 

any, allサブクエリ:

> all (リストにあるどの値よりも大きいóサブクエリのリストの最大値より大きいもの)

> any (サブクエリのリストの値より大きいものóサブクエリのリストの最小値より大きいものすべて)

= any inと同じ意味

!= anynot inではない。

!= any -> aではない,またはbではない,またはcではない」

not in -> aではない,かつbではない,かつcではない」

!= allnot inと同じ意味

外部クエリのwhereキーワードに続く式は、カラム名と同様に定数にもできる(その場合、相関サブクエリになる)

 

NULLとともにnot inを使用したサブクエリ

NULLnot inの結果には含まれない

 

existsを使用するサブクエリ:

サブクエリから何らかの結果が返ってくるかのテスト。

常に相関サブクエリとなる。

 

 

相関サブクエリ(繰り返しサブクエリ):

サブクエリの値は外部クエリに依存し、サブクエリは外部クエリによって選択されるそれぞれのローに対して1回ずつ繰り返し実行される。

 

 

EXISTS句を使った相関サブクエリの例:

例)あるテーブルのレコードのうち、別のテーブルとの関連において一定の条件を満たすレコードのみ抽出したい。

以下テーブルを仮定。

 

Person

Name

Age

Sex

Akiko

46

female

Hinako

7

female

Kotaro

39

male

Yuiko

5

female

 

Belong

Title

Name

Chirol

Yuiko

Hiyomina

Hinako

Jazza

Akiko

Mizuho

Kotaro

Surpass

Akiko

Surpass

Hinako

Surpass

Kotaro

Surpass

Yuiko

 

BelongテーブルでTitleMizuhoであるNameだけを、Personテーブルから抽出する(これをBelongPersonをジョインさせずに実行する)

 

select * from Person where exists (

select 1 from Belong where Name = Person.Name

and Title = 'Mizuho'

)

select 1existsを使ったときの常套句。

 

同じことをIN句を使っても書ける。

select * from Person where Name in (

select Name from Belong where Title = 'Mizuho'

)

 

 

 

データ型の使用と作成:

sp_addtypeにてユーザ定義型が追加できる。(後述)

sp_helpにて既存のテーブルの型情報などが参照できる。

例)exec sp_help radBankHolidays 

 

numericdecimalの違い

numericで位取り0を指定したカラムのみIDENTITYカラムに使用できる。

 

真数値型:小数

datatype [(precision [, scale])]

precision:    カラムに格納される10進の桁の最大数

小数点の左または右のすべての桁が含まれる

138までの範囲が指定可能

              デフォルトは、18

scale:        小数点の右側に格納できる桁数の最大数。

              precision > scale

138までの範囲が指定可能

              デフォルトは、0

              0の場合、真数値型は小数点なしで表示される。

 

カラムの精度または位取りのいずれかを超える値は入力できない。

 

クエリ結果をテーブルとして使う:

また、こんなクエリも実行できる。

SELECT * FROM
(
select * from hts_mark
 
where mrk_dt_stamp >= '20080130') A

 

FROM句内で、クエリを実行しその結果を外側のクエリのテーブルとして利用する。

 

注意!)

あるデータベースにおいてはこれが出来ない場合がある。

select * from (select * FROM PS_SecurityCode S) X

しかも、その際のエラーメッセージがふざけたものである。

Incorrect Syntax near the keyword select.

訂正)12.5.1以降で有効だとDBAによって確認された。(仕様)

 

問題)あるテーブルNumberのカラムaが最大のレコードから、カラムbが最大のものを抽出する。

以下のテーブルデータを仮定。

Number

a

b

3

3

3

2

3

1

2

4

2

3

2

2

1

4

1

3

1

2

 

この場合、以下のSQLは不可。

誤)

select * from Number having a = max(a) and b = max(b)

aが最大、かつbが最大とすると having a=3 and b=4となり、結果は空となる。

 

以下とすることで実行可能。

解)

select * from (

select * from Number having a = max(a)

) N1 having b = max(b)

一時テーブルでaの最大値をもつレコードのみ抽出し、外部のクエリでbの最大値を持つもののみ抽出。

 

 

 

 

慨数値データ型

float, double precision, realは算術演算の丸めを許可する。

isqlは小数点以下の有効桁数を6桁しか表示せず、残りを丸める。

モジュロ(%)演算をサポートしない。

 

文字データ型

nchar(n), nvarchar(n):     国別文字(日本語等のマルチバイト文字セット。その文字コードで格納したい最大文字数でnを宣言することが可能)

 

日付データ型

datetime:            175311日〜99991231日までの日付と時刻

smalldatetime:       199011日〜207966日までの日付を分の単位まで

 (これ以外の範囲は、charもしくはvarcharで格納する)

 

bitデータ型

true|false, yes|no   01を保持する。(1以外が入力された場合は、1として解釈される)

NULL不可、インデックスを持つこともできない。

 

timestampデータ型

timestampカラムを含むローが挿入または更新されるたびに、timestampカラムは自動的に更新される。

timestampという名前のカラムは、自動的にシステムデータ型timestampを持つ。

 

データ型間の変換

暗黙的な変換(自動処理)と明示的な変換(convert関数等を使用)がある。

 

ユーザ定義データ型の作成

sp_addtype

例)

EXEC sp_addtype 'aaa','int','NOT NULL'
go

create procedure use_aaa as
begin
   
declare @hoge aaa
end
go
    
create procedure use_aaa_and_temptable as       これは成功しない!dbForumに質問中
begin
   
declare @hoge aaa
   
create table #foo (
        bbb aaa
NOT NULL
    )
end
go

drop procedure use_aaa
go

EXEC sp_droptype 'aaa'
go

 

7.データベースおよびテーブルの作成

 

データの整合性:

「必要条件」  テーブル・カラムは有効な値をどのローにも1つ含む必要がある。nullは許可されない。

「検査」      テーブル・カラムに挿入されるデータ値を限定する。

「一意性」    1つ以上のテーブル・カラムに対してnull以外の同じ値を持つテーブル・ローが2つとない。

「参照」      テーブル・カラムに挿入されたデータが対応するデータを別のテーブルのカラムまたは同じテーブルの別のカラムに持っている必要がある。

 

TODO: 73データベース内のパーミッションから

 

 

 

create:テーブルを作成

create table AAA (

    a
int,
    b
float   
)

 

注)あるViewに参照されているViewに変更を加えた場合、参照している側のViewもリコンパイルする必要がある。

これをしないで、はまることがままある。



grant:権限を付与

GRANT SELECT ON V_BOJMaxProcessDate TO read_position;

GRANT DELETE ON V_BOJMaxProcessDate TO write_position;

GRANT INSERT ON V_BOJMaxProcessDate TO write_position;

GRANT SELECT ON V_BOJMaxProcessDate TO write_position;

GRANT UPDATE ON V_BOJMaxProcessDate TO write_position;

 

GRANT EXECUTE ON SP_BOJMaxProcessDate TO write_position;      (プロシージャ)

 

 

revoke:権限を剥奪

REVOKE UPDATE ON STP_TicketHistWork FROM test123
go

詳しくは、「セキュリティ機能ユーザーズ・ガイド」を参照。

 

 

ifelse:

SQL文の実行に条件を設定

 

if boolean

       statement

[else

       [if Boolean]

       statement]

 

もしくは、

 

if Boolean

begin

       statement1

       statement2

end

[else

       [if Boolean]

       begin

       statement1

statement2

end]

 

例)

IF EXISTS (select name from sysobjects where name = 'V_BOJMaxProcessDate_Bak')
BEGIN
   
DROP VIEW V_BOJMaxProcessDate_Bak
END
;

with recompile:

データベースに、カラムを削除したテーブル上で、select *を実行するオブジェクトがある場合、エラーメッセージが表示されるようにする。

 

プライマリキーの作成

ALTER TABLE dbo.radUser ADD PRIMARY KEY NONCLUSTERED (Account)

go

 

プライマリキーの交換

ALTER TABLE TRADE_FQ
   
DROP CONSTRAINT PK_TRADE_FQ
go
ALTER TABLE dbo.TRADE_FQ
   
ADD CONSTRAINT PK_TRADE_FQ
   
PRIMARY KEY CLUSTERED (fq_tradeno)
go

CREATE TABLE test123 (
    fq_tradeno
VARCHAR(30) NOT NULL
   
)

insert into test123 values ('222')

select * from test123



ALTER TABLE dbo.test123
   
ADD CONSTRAINT PK_test123
   
PRIMARY KEY NONCLUSTERED (fq_tradeno)


 

11. テーブルのインデックスの作成

インデックスはユーザからは透過的。AdaptiveServerが使うかを判断する。

複合インデックス2つ以上のカラムを利用

ユニークインデックスNULL値も含め、同一の値を持つ複数のローをもつことはできない。

クラスタードインデックス:強制的にソートおよび再ソートを連続して実行し、テーブルのローの物理的な順序を常に論理的な順序に一致させるようにする。

ノンクラスタードインデックス:上記のソートの必要がない。

 

(作成方法)

  1. create index
  2. create table unique, primary key

 

(ガイドライン)

-         IDENTITYカラムに手動でデータを挿入する場合

-         order byで指定されたソート順で頻繁にアクセスされる場合

-         規則的にジョインで使用されるカラム

-         テーブルのプライマリキーを保管するカラムには、常にクラスタードインデックスを作成する。

-         値の範囲が頻繁に検索されるカラムには、クラスタードインデックスを選択するとよい。

 

(構文)

create [unique] [clustered|nonclustered] index index_name on table_name(column_name) [with {{fillfactor|max_rows_per_page} = x,

例)create index au_id_ind on authors(au_id)

 

注)

bit, text, image型はインデックスを作成できない。

create, dropはテーブルの所有者しかできない。

 

(インデックスオプション)

ユニーククラスタード    ignore_dup_key

クラスタード             ignore_dup_row|allow_dup_row

ユニークノンクラスタードignore_dup_key|ignore_dup_row

ノンクラスタード        なし

 

ignore_dup_key:ユニークインデックスがあるカラムに、重複する値を挿入しようとすると、コマンドはキャンセルされる。ignore_dup_keyを指定すると、大きなトランザクションがキャンセルされないようにすることができ、重複する値を挿入しようとしたアクションのみがキャンセルされる。データを入力するときに、重複キーを挿入しようとするたびにエラーメッセージが表示され、入力がキャンセルされる。この挿入が取り消されると、その時点でアクティブだったトランザクション全体は、キャンセルされたupdateinsertコマンドの実行がなかったかのように継続できる。

 

allow_dup_row|ignore_dup_row: ユニークでないクラスタードインデックスを作成する際に指定できる。ユニークでないクラスタードインデックスを作成する際には適用できない。AdaptiveServerノンクラスタードインデックスは、ユニークなロー識別番号を内部的に各ローに付加するため、同じデータベース値を持つローでも重複ローを気にする必要は無い。

これら2つのオプションを同時に指定することは出来ない。ユニークでないクラスタードインデックスを作成した場合、重複キーは作成できても、重複キーは作成できないので、両者は並存しえない。 

 

allow_dup_row: ユニークでないクラスタードインデックスにこのオプションを設定すると、重複ローを含むデーブルに対してユニークでないクラスタードインデックスを作成できる。こうすうることで、重複ローを、ユニークでないクラスタードインデックスを持つテーブルにinsertまたはupdateできる。ただし、このテーブルにいずれかのインデックスがユニークである場合、ユニーク要件はallow_dup_rowオプションよりも優先する。allow_dup_rowオプションは、ユニークでないクラスタードインデックスを持つテーブルに対してだけ適用される。

 

ignore_dup_row:データのバッチ入力から重複ローが取り除かれる。振る舞いは、ignore_dup_keyと同様。

 

重複ローを含むテーブルに、ユニークでないクラスタードインデックスを作成しようとした場合:

(オプション)

なし                 create indexは失敗

allow_dup_row        正常に完了          

ignore_dup_row       インデックスは作成されるが、重複ローは削除されエラーメッセージが表示される

 

ユニークでないクラスタードインデックスを持つテーブルに、重複ローを挿入しようとした場合:

(オプション)

なし                 コマンドは失敗

allow_dup_row        正常に完了

ignore_dup_row       重複ローの挿入または更新はされずに、そのアクションのエラーメッセージが表示される。トランザクション全体は正常に完了する。

 

(インデックスの確認)

sp_helpindex table_name

 

(インデックスに関する統計値の更新)

update statics table_name [index_name]

 

(インデックスに関する統計値の更新を次回行うように予約するシステムプロシージャ)

sp_recompileシステムプロシージャの項参照

 

(複合インデックスについて)

複数のカラムにまとめてインデックスを設定した場合それは「複合インデックス」と呼ばれる。

ただし、そのうちの一部のカラムに対してのみ検索をかけたとしても、そのインデックスは検索時に活用される。

バッチおよびフロー制御言語の使用

goisqlのバッチ終了信号

 

1> select count(*) from STP_Ticket where trade_date >= ' 20060701'

2> select count(*) from STP_Ticket where trade_date >= ' 20060601'

3> go

 

-----------

         393

(1 row affected)

 

 -----------

         451

(1 row affected)

 

 

ストアド・プロシージャの使用

ストアド・プロシージャのセキュリティ機能:

ユーザはストアド・プロシージャが参照するテーブルやビューのパーミッションを持っていなくてもストアド・プロシージャを実行する権限を持つことができる。

 

パラメータの指定順:

パラメータを@parameter=valueの形式で指定する場合は、指定順は順不同。

パラメータをカンマ区切りでパラメータ名抜きで指定する場合は、create procedure文で指定された順序で指定する。

 

パラメータのデフォルト値:

Nullの指定も可能。

 

with recompile

create procedureもしくはexecではwith recompileの指定が可能で、クエリの実行プランを毎回実行毎もしくはその実行指定時に、再最適化する。

 

リターン・ステータス:
ストアド・プロシージャは、実行が正常に終了したかどうか、または失敗した場合はその理由を示すリターン・ステータスを返す。AdapitveServerは失敗した場合のステータスを-1-99まで予約している。(0もまた成功用に予約されている)。またユーザ生成の戻り値をreturnで返すことも出来る。

 

リターン・パラメタ:

以下のストアドプロシージャでは、引数の宣言の際に出力値も含めて定義している。

CREATE PROCEDURE dbo.test @mult1 int, @mult2 int, @result int output
AS
   
BEGIN
       
select @result = @mult1 * @mult2
   
END

これを実行するには、

1> declare @result int

2> exec test 1, 2, @result output

3> go

 

その結果は、

 (return status = 0)

 

Return parameters:

 

 -----------

           2

となり、戻り値がreturn statusとは別にストアドプロシージャから返されることが確認できる。

注)しかし、これはストアドプロシージャを関数のように扱いたい場合の方法であって、プロシージャ自体のステータスを返すものではないことに注意。その場合は、returnを使う。

 

(戻り値のルール)

- ストアドプロシージャに戻り値のパラメータを渡す場合は、execute文の最後で渡すか、または後続のすべてのパラメータを@parameter=valueの形式で渡す。

- ストアドプロシージャは複数の値を返すことができる。

 

CREATE PROCEDURE dbo.test @op1 int, @op2 int, @plus int output, @minus int output, @mult int output, @div int output
AS
   
BEGIN
        
select @plus = @op1 + @op2
       
select @minus = @op1 - @op2
       
select @mult = @op1 * @op2
       
select @div = @op1 / @op2
   
END

 

(制約)

-         単一のバッチ内でストアドプロシージャを他の文と結合することはできない。

-         プロシージャ内である名前のオブジェクトを作成し、削除してから、同じ名前のオブジェクトを新しく作成することはできない。

-         プロシ−ジャ内で#を使って作成された一時テーブルはプロシージャの実行中だけ存在する。

 

例1)   与えられた引数がNULLの場合は、その条件には関係なくすべてを表示したい場合

CREATE PROCEDURE dbo.SP_UserGroupQuery @UserGroup varchar(10) = NULL
WITH RECOMPILE
AS
   
BEGIN
       
BEGIN
       
SELECT  UserGroup,
                Products,
                BookTypes,
                TicketReport,
                TicketSubReport1,
                ModifiedBy,
                LastUpdated
       
FROM    TA_UserGroup
       
WHERE   lgcl_del_flg = 0
      
AND    UserGroup LIKE ISNULL(@UserGroup, '%')
      END
   
END

例2)もっといい方法があった。。

CREATE PROCEDURE dbo.SP_UserGroupQuery @UserGroup varchar(10) = NULL
WITH RECOMPILE
AS
   
BEGIN
       
BEGIN
       
SELECT  UserGroup,
                    Products,
                    BookTypes,
                    TicketReport,
                    TicketSubReport1,
                    ModifiedBy,
                    LastUpdated
           
FROM        TA_UserGroup
           
WHERE   lgcl_del_flg = 0
       
AND     UserGroup = ISNULL(@UserGroup, UserGroup)       -- IF NOT NULL, =@UserGroup, ELSE everything
      END
   
END


ネストしたプロシージャ内で共通のテンポラリテーブル(一時テーブル)を使うようなプロシージャの作成

あるプロシージャから別のプロシ−ジャを呼び出すことができるが、呼び出し側で作成されたテーブルに呼び出された側のプロシージャもアクセスすることが出来るのだが、このような2対のプロシージャを作成する際にはちょっとした工夫が必要となる。つまり、呼び出し側のプロシージャは毎回それを実行するたびにテンポラリテーブルを作成するのだが、呼び出される側のプロシージャを作成する時点ではそのテンポラリテーブルは既に存在していなくてはいけない。これを実現するために、このようなネスとしたプロシージャを作成する際は以下のテクニックを使う。

 

1.2つのプロシージャで使用されるテンポラリテーブルをあらかじめ作成しておく。

2.呼び出される側のプロシージャを作成する。

3.あらかじめ作成しておいたテンポラリテーブルを削除する。

4.呼び出す側のプロシージャを作成する。

 

例)

1.テンポラリテーブル作成

    CREATE TABLE #InternalIds (
        internal_id
NUMERIC(14,0) NOT NULL
    )

2.呼び出される側のプロシージャ作成(#InternalIdsが存在していることを前提としている)

CREATE PROCEDURE dbo.SP_ParseInternalIds
@InternalIds   
VARCHAR(1024) = NULL
WITH RECOMPILE
AS

DECLARE     @cplace         INT,
            @id            
INT,
            @length        
INT

BEGIN
       
   
-- Extract internal_id by parsing @TradeSids (START)
    IF (@InternalIds != NULL) BEGIN


       
-- STUFF indexId
        WHILE (CHAR_LENGTH(@InternalIds)) > 0 BEGIN
           
SET @cplace = patindex("%,%", @InternalIds)
           
IF (@cplace) <> 0 BEGIN
               
SET @id = CONVERT(NUMERIC(10,0), SUBSTRING(@InternalIds, 1, @cplace-1))
               
SET @InternalIds = STUFF(@InternalIds, 1, @cplace-1, NULL)
               
IF (patindex("%,%", @InternalIds)) = 1
                   
SET @InternalIds = STUFF(@InternalIds, 1, 1, NULL)
           
END
           
ELSE BEGIN
               
SET @length = CHAR_LENGTH(@InternalIds)
               
SET @id = CONVERT(NUMERIC(10,0), SUBSTRING(@InternalIds, 1, @length))
               
SET @InternalIds = STUFF(@InternalIds, 1, @length, NULL)
           
END

           
INSERT INTO #InternalIds
            VALUES (@id)
        
END

       
SET @InternalIds = "Processed"

   
END
   
-- Extract internal_id by parsing @InternalIds (END)

END
go

3.テンポラリテーブル削除

DROP TABLE  #InternalIds

 

4.呼び出す側のプロシージャ作成(テンポラリテーブルの作成および削除も以後この中で行う)

CREATE PROCEDURE dbo.SP_TicketQueryTest
@InternalIds   
VARCHAR(1024) = NULL
WITH RECOMPILE
AS

DECLARE     @cplace         INT,
            @id            
INT,
            @length        
INT

BEGIN
   
-- internal_id temp table
    CREATE TABLE #InternalIds (
        internal_id
NUMERIC(14,0) NOT NULL
    )
   
   
exec SP_ParseInternalIds @InternalIds


   
-- FROM STP_Ticket
        SELECT      Ticket.internal_id as internal_id
   
--INTO        #Ticket
        FROM            STP_Ticket Ticket
        ,                       TA_TradeType TradeType
        ,                       istar_account_code istar_account_code
   
--,           #InternalIds Ids
   
       
WHERE       Ticket.lgcl_del_flg = 0
       
AND                 Ticket.trade_type *= TradeType.Type
       
AND                 Ticket.book *= istar_account_code.book
   
AND         Ticket.internal_id = 444188
   
UNION
   
-- FROM STP_TicketHist
    --INSERT INTO #Ticket
        SELECT      Ticket.internal_id as internal_id
       
FROM            STP_TicketHist Ticket
        ,                       TA_TradeType TradeType
        ,                       istar_account_code istar_account_code
   
--,           #InternalIds Ids
   
       
WHERE       Ticket.lgcl_del_flg = 0
       
AND                 Ticket.trade_type *= TradeType.Type
       
AND                 Ticket.book *= istar_account_code.book
   
AND         Ticket.internal_id = 444188
   
   
--SELECT      Ticket.internal_id
    --FROM        #Ticket Ticket
      
      
   
DROP TABLE  #InternalIds

END
go

 

(怪現象 2007/11/12

ごくまれにストアドプロシージャ内のSELECT文とそれとまったく同じSELECT文をISQLセッションなどから実施した場合に異なる結果を返すことがある。

これは、ストアドプロシージャ内に潜在的なバグが含まれていることで起こる場合がある。

 

EXECUTE dkba_get_borrow_cost '.N300', 'EQT'
go

/*
create proc dkba_get_borrow_cost
    @name varchar(32),
    @trader varchar(4) = "EQT"
as
begin
    select bl.borrowlst_days, bl.borrowlst_rate
    from hts_borrow_detail bd, hts_borrow_list bl
    where bd.borrow_mnemonic = @name
    and bd.borrow_trader = @trader
    and bd.borrow_asof_date = 0
    and bl.borrowlst_detail_id = bd.borrow_id
    order by bl.borrowlst_days
end
*/



select bl.borrowlst_days, bl.borrowlst_rate
from hts_borrow_detail bd, hts_borrow_list bl
where bd.borrow_mnemonic = '.N300'
and bd.borrow_trader = 'EQT'
and bd.borrow_asof_date = 0
and bl.borrowlst_detail_id = bd.borrow_id
order by bl.borrowlst_days

両者はまったく同じSELECT文を発行しているように見えるが(少なくとも文そのものはまったく同じだが)、内部的には異なっていることがクエリ・プランナーを実行することで判明した。

 

SELECT文)

        FROM TABLE     

            hts_borrow_list    

            bl 

        Nested iteration.      

        Table Scan.    

        Forward scan.  

 

EXECUTE文)
        FROM TABLE     

            hts_borrow_list    

            bl 

        Nested iteration.      

        Index : borrow_detail_id_ind   

        Forward scan.  

        Positioning by key.    


今回はこのIndexにバグが含まれていた。(archiveテーブルにIndexが張ってある!!)

CREATE NONCLUSTERED INDEX borrow_detail_id_ind
    ON dbo.hts_borrow_list_arc(borrowlst_detail_id)
go

 

お見事、Kevin

 

(後日談)

前述の怪現象は以下の状況にて発生することが確認された。

つまり、ストアドプロシージャのクエリツリーはsysproceduresシステムテーブルに保有され、このクエリツリーはオブジェクトの名前ではなくIDを保存することになっている。

よって、テーブルの名前が変更される場合、sysproceduresシステムテーブルは元のオブジェクトIDを保有し続けるのでストアドプロシージャは元の(名前が変更された)テーブルを使い続けるのである。

また、クエリツリーはストアドプロシージャをリコンパイルすることによっては更新されない。

よって、この問題を解消するには、

1)ストアドプロシージャをいったん削除して、作成しなおす

2)元のテーブルを削除する。すると、クエリツリーは次回ストアドプロシージャが実行されるときに見直され、正しくクエリツリーを再構築する。

 

 

テーブル名(table name)やカラム名(column name)を動的(dynamic)に変数(variable)として扱いたいような場合:

declare @table_name varchar(20)

declare @statement varchar(50)

select @table_name = 'hts_account'

select @statement = 'select * from ' + @table_name

exec(@statement)

go

 

ここでのポイントはexec文の使い方である。Exec関数の括弧内にSQLステートメントをそのまま放り込み、それを実行することができる。

なお、これはASE12以降で可能な処理であり、11以前では結構苦労するかもしれない。。

http://www.sypron.nl/dynsql.html

 

 

カーソル:ロー単位のデータ・ローへのアクセス

カーソルとは:select文に関連するシンボリック名

-         カーソル結果セット:カーソルと関連のあるクエリの実行によって生じるローのセット(テーブル)

-         カーソル位置:カーソル結果セット内の1つのローを示すポインタ

 

カーソルを指定する句が含まれるupdateまたはdelete文を使用して、そのローを明示的に更新もしくは削除できる。

フェッチにより、現在のカーソルの位置を変更する。fetchコマンドで、1行または複数行下のローにカーソルの現在位置が移動する(後ろのローへの移動しかサポートしない)

 

例)

declare <カーソル名> cursor

for select column_a, column _b, column _c from table_a

where

for { read only | update [of column_d] }

 

112233

Abc

---    (フェッチが実行されたとき、カーソルはこの行をさす)

445566

Def

///   (次のカーソルの位置はこの行と前の行の間)

778899

Ghi

[[[

 

カーソル使用手順:

1)カーソルの宣言

2)カーソルのオープン

3)ローのフェッチ

4)ローの処理(検索/更新/削除)

5)次のロー

6)カーソルのクローズ

7)カーソルの割付解除

 

カーソルからのフェッチ:

fetchコマンドはコンパイルされたカーソルを実行し、カーソルに定義されている条件に一致する1つ以上のローを返す。

最初のfetchは探索条件に一致する最初のローを返し、カーソルの現在位置を保管する。

2番目のfetchは、探索条件に一致する次のローを返し、その現在位置を保管する。(以下同様)

set cursor rowsコマンドで、fetchによって返されるローの数を指定できる。

 

注)カーソルを使用してローを挿入することはできない!

 

カーソルのクローズ:

一時テーブルの削除、サーバリソースの解放を行う(ただしカーソルのクエリプランは保持)。

何回でもクローズできるので、結果セット全体を毎回一番上から一番下まで移動する必要はない。

 

カーソル名:

30文字以下、文字、#_のいずれかで始める必要がある。

 

Select句:

compute, for browse, into句の指定は不可。

Transact-SQLのパラメタ名、またはローカル変数への参照を含めることができる。

 

ワークテーブル:

一部のクエリはカーソル結果セットを生成するためにワークテーフルを使用する。この場合、fetchで取り出されたローは元のテーブルのローの値を反映しないことがある。

例えば、order by句を指定して宣言されたカーソルは、カーソル結果セットのローを並べるために、ワークテーブルを作成する必要がある。

ワークテーブルにローに対応する元のテーブルのローはロックされないので、ほかのクライアントがこのローを更新することができてしまう。

 

カーソルを更新可能にするには:

更新するテーブルには、ユニーク・インデックスが必要。

ないと、declare cursor文が拒否される。ただし、ストアドプロシージャのコンパイルはできてしまうので注意が必要

select文に下記が含まれる場合for update句の指定ができない。

-         distinct

-         group by

-         集合関数

-         サブクエリ

-         union

-         at isolation read uncommitted (???)

 

fetch構文:

fetch cursor_name [into fetch_target_list]

ローをすべてフェッチすると、カーソルは結果セットの最後のローをさす。

もう一度フェッチを実行すると、データがないことを示す@@sqlstatusを使って警告を返す。

into句は指定された変数にカラム・データを返すように指定する。

例)fetch cursor_a into @cola, @colb, @colc

 

カーソル・ステータスのチェック

@@sqlstatus:      (fetchコマンドのみがこの値を更新する)

       0:     fetchが正常に終了
       1:     fetch
によってエラーが発生

       2:     結果セットにこれ以上データがない。

 

カーソルを使用したローの更新と削除:

update文のwhere current of句を使用して、現在のカーソル位置のローを更新できる。

      update table_name

      set   col_a =

      where current of cursor_name

更新後もカーソル位置は変わらない。

 

delete文のwhere current of句を使用して、現在のカーソル位置のローを削除できる。

      delete [from] table_name

      where current of cursor_name

削除後、カーソルは削除されたローの次のローの前に移動する。fetchを使用して、次のローにアクセスする必要がある。

 

 

例)

drop procedure dbo.sp_radUserTest
go

CREATE PROCEDURE dbo.sp_radUserTest
AS
   
BEGIN
   
     
declare @account varchar(50)
      
declare @readonly bit
           
declare @msg varchar(200)
     
     
declare user_cursor cursor for
     
select Account, read_only
     
from radUser
     
where lgcl_del_flg = 0
     
for update
     
     
open user_cursor
     
     
fetch user_cursor into @account, @readonly
     
     
if (@@sqlstatus = 2)
     
begin
       
print "We have no data."
       
close user_cursor
       
return
     
end
     
     
while (@@sqlstatus = 0)
     
begin
       
if (@account = 'eguchik')
        
begin
          
select @msg = @account + " is Koji"
          
print @msg
          
update radUser
          
set read_only = 1
          
where current of user_cursor
       
end
       
       
fetch user_cursor into @account, @readonly
     
end
        
   
END
go

 

 
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


ロックコマンド・オプション

 

トランザクション

トランザクションモード:

トランザクションを開始するために暗黙的なbegin transaction文を使用するかしないかを設定する。

 

- Chained(連鎖)モード

SQL標準と互換性のある連鎖モード。このモードでは、暗黙的にトランザクションが開始される。修正を完了するためには、明示的にcommit transactionを発行する必要がある。

ちなみにOracleはこちらをデフォルトで採用。

 

- Unchained(非連鎖)モード

Sybaseではデフォルトの非連鎖モード。

 

Chained/Unchainedの別は、

 

set chaied on/off

 

で指定できる。

 

また、現在の状態を、

 

select @@tranchained

 

の戻り値:

0:非連鎖モード

1:連鎖モード

 

によって確認できる。

 

独立性レベル:

トランザクション実行中にほかのユーザがデータにアクセスできるレベルを設定する。

 

 

 

オプション

nocount:

n rows effected”メッセージの出力を制御する。

Ø       set nocount on/off

 

 

グローバル変数

@@textsize:

返されるデータの長さの制限

 

@@servername:

サーバ名を返す

 

@@error:

現在のユーザ・セッションで最後に実行されたバッチのエラー・ステータスを認識するために使用する。

注)print文やifテストを含むあらゆるTransact-SQL文によって@@errorはリセットされるので、ステータス・チェックは成功を確認したいバッチの直後に行う必要がある。

 

@@rowcount:

直前に実行したSQLが影響を及ぼしたローの数を返す。

システムプロシージャ

sp_rename:オブジェクトを別の名称に変更

 

EXEC sp_rename 'STP_Ticket','STP_Ticket_Bak'

 

sp_help:データベースオブジェクトの属性情報を表示。

> sp_help <object>

 

sp_helptext:ストアドプロシージャのソース情報を表示。

> sp_helptext <procedure>

 

sp_helpdb:データベース情報を表示

EXEC sp_helpdb <database>

注)データベースオプションも表示される。

 

sp_depends:従属オブジェクトの識別

> sp_depends <procedure>

 

sp_helprotect:パーミッションの識別

> sp_helprotect <procedure>

 

sp_spaceused:

使用済みスペースのリスト。

 

sp_dboption:

設定可能なデータベースオプションの一覧を表示。

 

sp_who:

現在のすべてのAdaptiveServerユーザとプロセス、または特定のユーザとプロセスの情報についてレポートする。

 

sp_lock:

現在ロックを保持しているスレッドについての情報をレポートする。

 

sp_helptext:

オブジェクトの定義内容を表示する。

Exec sp_helptext SP_ArchiveTicket

 

sp_recompile:

テーブルにアクセスするプロシージャとトリガのクエリプランを次回実行するときに再最適化するように予約する。

Exec sp_recompile <table>

 

sp_procxmode:

プロシージャのトランザクションモードを変更する。

> sp_procxmode <procedure_name>, "<mode>"

 

[mode]

-         Chained:

-         Unchained

-         Any Mode

 

sp_dba_dbused:

ディスクの利用サイズを表示する。

 

システムオブジェクト

sysobjects:

select * from sysobjects  (システムオブジェクト格納テーブル)

 

(応用)sysobjectsを用いて、テーブルやビューを更新する際のスクリプトが自動化できる。

 

IF EXISTS (select name from sysobjects where name = 'V_BOJMaxProcessDate_Bak')
BEGIN
   
DROP VIEW V_BOJMaxProcessDate_Bak
END
;

EXEC sp_rename V_BOJMaxProcessDate, V_BOJMaxProcessDate_Bak;

CREATE VIEW dbo.V_BOJMaxProcessDate
AS
   
SELECT boj_sec_code, Max(process_date) AS process_date
FROM PS_BOJHaircut
GROUP BY boj_sec_code;

GRANT SELECT ON V_BOJMaxProcessDate TO read_position;
GRANT DELETE ON V_BOJMaxProcessDate TO write_position;
GRANT INSERT ON V_BOJMaxProcessDate TO write_position;
GRANT SELECT ON V_BOJMaxProcessDate TO write_position;
GRANT UPDATE ON V_BOJMaxProcessDate TO write_position;

master..sysmessages:     (システムメッセージ格納テーブル、masterデータベース内に格納されている)

select * from master..sysmessages where error = <error code>

(エラーコードの詳細が参照可能)

 

master.. sysprocesses: (現在実行中のプロセスを確認できる)

select suser_name(suid), suid, ipaddr, program_name
--select *
from sysprocesses
where ipaddr = '10.160.142.44'
and program_name not in ('Market Feed Serv', 'GM_TICKETING')

などとすれば、目標のプロセスを補足できる。

 

システム関数

日付関連

getdate(): 現在のシステム時刻を取得

select getdate();

 

datepart(<date label>, <date>): 時刻の部分を取得

select datepart(year, getdate());

 

> dateadd(mm, -6, getdate())で現時刻の半年前の時刻を取得

 

convert(varchar, <date>, <style>): 時刻を文字列に変換する

注)指定できるオプショナル引数が他にもある

select convert(varchar, getdate(), 112);

 

 

日付(日付時刻)を日付のみに変換

select convert(datetime, convert(varchar, getdate(), 112));

 

各日付部分は以下の形式で指定できる。

日付部分      省略形       

year          yy            1753-9999

quarter       qq            1-4

month         mm            1-12

week          wk            1-54

day           dd            1-31

dayofyear     dy            1-366

weekday       dw            1-7 (Sun-Sat)

hour          hh            0-23

minute        mi            0-59

second        ss            0-59

millisecond   ms            0-999

 

datediff:

日付データの差分を指定したフォーマットに基づき出力する。

> DATEDIFF(ms, @start, @end)

 

注)なお、日付データ型はintfloatなどの数値型に変換することが出来ない。

 

suser_name():

suser_name(<suid>)suidのユーザ名が取得できる。

引数を省略した場合は、現在のユーザを返す。

 

db_name():

現在のデータベース名を取得

select db_name()

 

文字列関連

substring:

部分文字列を返す

substring(expression, start, length)

 

charindex:

検索した文字列の開始位置を返す。見つからない場合は、0を返す。

charindex(char_expr)

 

col_length:

定義されたカラム長を返す。

 

datalength:

各ローに保管されたデータの実際の長さをバイト単位で返す。

 

char_length:

文字式の文字数を返す。

char_length(char_expr)

 

stuff:

stuff(char_expr1, start, length, char_expr2)

startから数えてlength個の文字をchar_expr1から削除した後、startで始まるchar_expr1char_expr2を挿入する。文字を削除する場合、char_expr2にはnullを指定する。

 

例)文字列を置換する。

declare @string varchar(10)
declare @newstring varchar(20)
declare @old varchar(10)
declare @new varchar(10)
declare @index int
declare @len int
select @string = 'abcdefghi'
select @old = 'def'
select @new = 'xyz' -- null is ok here
select '@string: ' + @string
select '@old: ' + @old
select '@new: ' + @new
select @index = charindex(@old, @string)
select '@index: ' + convert(varchar, @index)
if @index = 0 begin
   
select 'not found'
   
select '@newstring: ' + @string
end
else begin
   
select @len = char_length(@old)
   
select '@len: ' + convert(varchar, @len)
   
select @newstring = stuff(@string, @index, @len, @new)
   
select '@newstring: ' + @newstring
end

 

ダンプロード、レプリケーション、CIS

dbAにレプリテーブル(from外部マスタDBサーバ)があり、dbBにダンプロードする場合:

dbBにレプリテーブルの物理実体(データを含む)が残る。

 

dbACISテーブル(from外部マスタDBサーバ)があり、dbBにダンプロードする場合:

dbBCISの情報が残り、同一ASEであれば動く可能性があるが、別ASEの場合有効に機能しない。

 

技術的には、レプリ先のテーブルXにレプリ元のテーブルXにはない、ノンクラスタードインデックスを張ることが可能。

 

ダンプ・ロードコマンド

dump database a_db to "D:\Dump\a_db.dmp"

go

 

use master

go

 

sp_dboption "a_db", "single user", true

go

 

checkpoint

go

 

load database a_db from "D:\Dump\a_db.dmp"

go

 

online databasse a_db

go

 

コンソールコマンド

bcp:

$ bcp apps_gm.dbo.<table> <in|out> <file> -n -Udbo_appsgm -P<password> -S<server>

out: Download data from Database to File

in : Upload data from File to Database

(オプション)

-b (数値)   数値の行が挿入される毎にバッチを区切る。つまり、ログのダンプ等がその都度行われるようになる。

-c            テキストベースのファイルを入出力に使用することを指示する

-t (文字)   デリミタ(区切り文字)のうち、フィールド区切り文字(ターミネータ)を指定

-r (文字)   デリミタ(区切り文字)のうち、行区切り文字(リターン)を指定

-J (文字コード)    sjisなどを指定

 

コマンド例)

Ø       bcp abc_db.dbo.UserTableOut out UserTable.dat -Uuser -Ppass -SASESERVER -n -Jsjis

Ø       bcp abc_db.dbo.UserTableIn in UserTable.dat -Uuser -Ppass -SASESERVER -n -Jsjis -Y -b1

 

バイナリ(-n)sjis文字コード(-Jsjis)で出力(out)し、それをその文字コードのまま(-Y)入力(in)する。入力の際は、1行ずつ(-b1)コミットしている。

 

bcpの最中に、こんなエラーメッセージが出たら、、、

doik@tk1d1140app bcp]$ bcp stp_apps.dbo.PS_BOJHaircut in PS_BOJHaircut.dat -n -Udbo_appsgm -Pfl3ps1de -STK_GDCL_D01_DS
Server Message: TK_GDCL_D01_DS - Msg 4806, Level 16, State 1:
You cannot run the non-logged version of bulk copy in this database. Please check with the DBO.

DBAに連絡して、sp_dboptionで、select into/bulkcopy/pllsortを有効にしてもらうよう頼みましょう。

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs64.htm

注)ただし、場合によってはDBAはこのリクエストに対して「No」ということがある。それはselect intoや高速bcpがトランザクションログをとらないタイプの更新を実行し、障害が起こった場合にその情報を失う恐れがあるからである。実際、本番環境でこの設定をすることはまったくもっておススメではない。

 

bcpフォーマットファイル:

bcpの出力先もしくは入力元のファイルの形式を指定するファイル。これによりユーザはフィールドの選択やフォーマットの特定等が可能となる。

bcpコマンドで-nまたは-cオプションを指定しないと、bcpコマンドはフォーマットの指定を対話形式で聞いてくる。そこで指定したフォーマットをbcpbcp.fmtという名前のファイルに保存する。ユーザはこのファイルを変更することで好みのフォーマットを指定することができる。(もちろんこのフォーマットファイルを一から自分で書いても良いが、この方が手間が省けてよい)

 

 

isql:

$ isql -Udbo_appsgm -P<password> -S<server> -D<database> -i <input file> -o <output file>

 

1>     use <database>

2>     go

でデータベースが変更できる。

 

コマンドラインオプション

-e     発行された各コマンドを出力に含める

-w <columnwidth>

       行の長さを変更      

 

 

例)

isql -Ufox_xxxx -Pfoxxxxx -STK_FOX1_xxx -e << EOF >&1 2>&1

use fox

go

EOF

 

注1)   << EOFでヒアドキュメントが参照できる。

注2)   ヒアドキュメント内のコマンドは行頭から始まる必要がある。

注3)   >&1で出力のリダイレクションが可能

 

(詳細は、「ユーティリティ・プログラム」を参照)

 

 

DBArtisan

 

Query Option:

 

クエリーの実行プランが確認できる

 

 

 

 

プラン中にTable Scan(全表検索)が出たらIndexを設置してIndexが使われるようにチューンしましょう。

 

 

JDBC

SybaseでのJDBC接続には基本的には、

resultSetType= TYPE_FORWARD_ONLY

resultSetConcurrency=ResultSet.CONCUR_READ_ONLY

を用いるが、INSERTUPDATEDELETEを使うときには、resultSetConcurrency=ResultSet. CONCUR_UPDATABLEを使う。

 

またSybase内のストアドプロシージャをJavaから使いたい場合、CallableStatementインタフェースにてプロシージャにアクセスする。

その際はConnectionインタフェースのprepareCallメソッドにてCallableStatementを取得する。

sql引数その他は以下のようになる。

 

       cs = cn.prepareCall("exec SP_TicketQueryTest ?",

                     ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

 

JDBCを経由したSybaseへのアクセスには思ったよりも制約が多い。

もっとも大きな制約はJDBCCallableStament経由ではプロシージャ内でSELECTが一度しか使えないことであろう。(これはある意味致命的)

それがために分離不能なプロシージャも技術的に分離せざるを得ないことがある。そのような場合、呼び出し側のJavaプログラムでトランザクション処理を行う必要がある。

具体的には、以下のことが可能および不可能である。

 

CallableStatementから呼び出すストアドプロシージャが、

 

-         単一のSELECT文のみからなる場合、ResultSet取得可

-         CREATE文を含む(テンポラリテーブルの作成等)場合、ResultSet取得可

-         SET @variable, SELECT @variable等ローカル変数の設定を行う場合、ResultSet取得不可

-         SELECT文がUNIONを含む場合、ResultSet取得可

-         SELECT文がテンポラリテーブルの結果を返す場合、ResultSet取得不可

 

特にローカル変数の設定が行えない点とテンポラリテーブルの結果を返すことが出来ない点はプログラミングの観点からはものすごく痛い。

これが出来るような方法をどなたかご存知であればぜび教えていただきたいのだが、20077月時点で自分の知る限りでは、どうやら不可能のようだ。

ちなみに、上記の取得不可の条件を含むプロシージャにJDBC経由でアクセスすると、結果は以下のようになる。(注:resultSetConcurrencyの設定によって結果が異なる)

 

resultSetConcurrency=ResultSet.CONCUR_READ_ONLYのとき、

 

       rs = cs.getResultSet();

 

としても、rsには何も代入されない。

 

resultSetConcurrency=ResultSet. CONCUR_UPDATABLEのとき、

 

       cs.execute();

 

を実行すると、以下の例外が返される。

 

com.sybase.jdbc2.jdbc.SybSQLException: Execute cursor 'jconnect_implicit_1' is declared on a procedure which contains a non-SELECT or a SELECT with a COMPUTE clause. For the declaration of this cursor to be legal it should have a single SELECT statement without a COMPUTE clause.

     

 

しかし!!!!!

 

PreparedStatementを使えば上記の問題とはまったくの無縁である。PreparedStatementの使い方はCallableStatementとほぼ同じだが、PreparedStatementを取得する際にprepareStatementメソッドを呼ぶことと、クエリを実行する際にexecute()メソッドではなくexecuteQuery()メソッドを使うところ、またexecuteQuery()メソッドがResultSetを返すあたりであろうか。また、executeUpdate()メソッドで、UPDATE文の実行が出来て、またaddBatch()メソッドで複数回のUPDATEが一度に実行できたりもするので、非常に便利である。(ありがとう、Kevin

 

 

 

 

(参考:AddDavosUser

create proc AddDavosUser

       @UserName            varchar(32),

       @UserCompany  varchar(32)

as

 

if exists (select 1 from DavosUser

                     where UserName = @UserName)

begin

       raiserror 20001 "User already exists : Name=%1!", @UserName

       goto fail

end

 

insert DavosUser

(

       UserName,

       UserCompany

)

values

(

       @UserName,

       @UserCompany

)

 

if @@error != 0 goto fail

 

return 0

fail:

if @@trancount > 0 rollback tran

return 1