SQLServer - ストアドプロシージャの基本構文-PHP开发-php.cn

ホームページ

php教程

PHP开发

SQLServer - ストアドプロシージャの基本構文

高洛峰

Dec 14, 2016 pm 02:55 PM

oracleのテーブル作成SQLをsqlserverのテーブル作成SQLに変換する際の注意点
1. コメント文は全て削除する必要があります。
2. clob 型を text 型に変換します。
3. BLOB タイプを画像タイプに変換します。
4.数値型はintに変換され、number(16,2)などは10進数(16,2)に変換され、number(18)はbigintに変換されます。
5.デフォルトのsysdateがデフォルトのgetDate()に変更されます。
6.to_date('2009-12-18','yyyy-mm-dd') は Cast('2009-12-18' as datetime) に変更されます

SQLSERVER:
変数の宣言:
次の場合に使用する必要があります変数の宣言変数の前に @ 記号を追加します
DECLARE @I INT

変数の代入:
変数を代入するときは、変数の前に set を追加する必要があります
SET @I = 30

複数の変数を宣言:
DECLARE @ s varchar(10),@a INT

if ステートメント:

Java コード

if ..  
begin  
  ...  
end  
else if ..  
begin  
  ...  
end  
else  
begin  
  ...  
end

例:

SQL コード

DECLARE @d INT  
set @d = 1  
IF @d = 1 BEGIN  
   PRINT &#39;正确&#39;  
END  
ELSE BEGIN  
   PRINT &#39;错误&#39;  
END

複数の条件選択ステートメント:
例:

SQL コード

declare @today int  
declare @week nvarchar(3)  
set @today=3  
set @week= case  
     when @today=1 then &#39;星期一&#39;  
     when @today=2 then &#39;星期二&#39;  
     when @today=3 then &#39;星期三&#39;  
     when @today=4 then &#39;星期四&#39;  
     when @today=5 then &#39;星期五&#39;  
     when @today=6 then &#39;星期六&#39;  
     when @today=7 then &#39;星期日&#39;  
     else &#39;值错误&#39;  
end  
print @week

ループステートメント:

Java コード

WHILE 条件 BEGIN    
执行语句  
END

例:

Java コード

DECLARE @i INT  
SET @i = 1  
WHILE @i<1000000 BEGIN  
set @i=@i+1  
END

カーソルの定義:

SQL コード

DECLARE @cur1 CURSOR FOR SELECT .........  
  
OPEN @cur1  
FETCH NEXT FROM @cur1 INTO 变量  
WHILE(@@FETCH_STATUS=0)  
BEGIN  
处理.....  
FETCH NEXT FROM @cur1 INTO 变量  
END  
CLOSE @cur1  
DEALLOCATE @cur1

SQL コード

AS  
  
declare @CATEGORY_CI_TABLENAME VARCHAR(50) =&#39;&#39;  
declare @result VARCHAR(2000) = &#39;&#39;  
declare @CI_ID DECIMAL = 0  
declare @num int = 1  
declare @countnum int = 1  
  
BEGIN  
select  @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= &#39;Y&#39; and CATEGORY_CODE =@CATEGORY_CODE  
   
IF (@ATTRIBUTE2=&#39;A&#39;)  
  begin    
        DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where  CMDB_UPDATE_FLAG= &#39;Y&#39; and CATEGORY_CODE =@CATEGORY_CODE  
         OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE  
                set @result = @result+@CONFIG_CODE+&#39;,&#39;  
             WHILE @@FETCH_STATUS = 0  
                    BEGIN  
                    FETCH NEXT FROM MyCursor INTO @CONFIG_CODE  
                    set @num = @num+ 1  
                        if(@num<@countnum)   
                            begin  
                                set @result = @result+@CONFIG_CODE+&#39;,&#39;  
                            end   
                        else if(@num=@countnum)   
                             begin  
                                set @result = @result +@CONFIG_CODE  
                             end   
                    END  
            CLOSE MyCursor   
            DEALLOCATE MyCursor   
        set @result = &#39;insert into &#39; + @ATTRIBUTE1 + &#39;(&#39; + @result +&#39;) select &#39;+ @result +&#39; from &#39;+@CATEGORY_CI_TABLENAME +&#39; where CI_ORDER_LINE_ID=&#39;+@KEY_ID  
  end             
 else if((@ATTRIBUTE2=&#39;U&#39;))

一時テーブル:

-- INTO を選択すると、計算結果から新しいテーブルが作成されます。クエリ。通常のSelectとは異なり、データはクライアントに返されません。新しいテーブルのフィールドは、Select の出力フィールドに関連付けられているのと同じ名前とデータ型を持ちます。 * SELECT * Into Newtable
From Uname

--- Insert Into SELECT
-Table ABC は UNAME のフィールドのテーブルに存在する必要があります

--- 一時テーブルを作成します
テーブル #Temp (
) UID int Identity (1, 1) 主キー、
Username Varchar (16)、
PWD VARCHAR (50)、Age Smallint、
Sex Varchar (6)

------------- ----------------------------------- 一時テーブルを開きます
#temp

から * を選択します

1ローカル一時テーブル (# で始まる) は現在の接続に対してのみ有効であり、現在の接続が切断されると自動的に削除されます。

2. グローバル一時テーブル (## で始まる) は他の接続にも有効で、現在の接続およびそれにアクセスしていた他の接続が切断されると自動的に削除されます。

3. ローカル一時テーブルかグローバル一時テーブルかに関係なく、接続にアクセス権がある限り、drop table #Tmp (またはdrop table ##Tmp) を使用して一時テーブルを明示的に削除できます。

一時テーブルは、過剰でない限り、実行効率にほとんど影響を与えません。逆に、データベースの一時テーブル領域が十分である限り、効率を向上させることができます。実行効率に影響しますので、可能であれば避けてください。

その他:

-- 入力パラメータを含むストアドプロシージャ --

create proc GetComment

(@commentid int)

select * from Comment where CommentID=@commentid

-- 入力ストア付き出力パラメータを持つプロシージャ--

create proc GetCommentCount

@newsid int,

@count int Output

select @count=count(*) from Comment where NewsID=@newsid

- -単一の値を返す関数--

create function MyFunction

(@newsid int)

returns int

begin

declare @count int

select @count=count(*) from Comment where NewsID=@newsid

return @count

end

--メソッドの呼び出し--

declare @count int

exec @count=MyFunction 2

print @count

--戻り値Aテーブルの関数 --

関数 GetFunctionTable

(@newsid int) を作成します

returns table

return

(select * from Comment where NewsID=@newsid)

-- 戻り値is Table function call--

select * from GetFunctionTable(2)

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

SQLServer は、複数条件クエリを実装するためにストアドプロシージャ内の SQL 文字列を結合しません

前のスプライシング方法
set @sql=' select * from table where 1=1 '
if (@addDate is not null)
set @sql = @sql+' and addDate = '+ @addDate + ' '
if ( @name '' and is not null)
set @sql = @sql+ ' and name = ' + @name + ' '
exec(@sql)
以下は SQL 文字列を結合しない複数条件クエリです解決策
最初の書き方はコードが少し冗長に感じることです
if (@addDate is not null) and (@name '')
select * from table where addDate = @addDate and name = @ name
else if (@addDate is not null) and (@name ='')
select * from table where addDate = @addDate
else if (@addDate is null) and (@name '')
select * from table where and name = @name
else if(@addDate is null) and (@name = '')
select * from table
2番目の書き方は
select * from table where (addDate = @ addDate or @addDate is null) and (name = @name or @name = '')
3番目の書き方は
SELECT * FROM table where
addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END,
name = CASE @name WHEN '' THEN name ELSE @name END

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

SQLSERVER ストアドプロシージャ基本構文

1. 変数の定義

-- 単純な代入

declare @a int

set @a=5

print @a

-- 使用する値を割り当てる select ステートメント

declare @user1 nvarchar (50)

select @user1='Zhang San'

print @user1

declare @user2 nvarchar(50)

select @user2 = ID= の ST_User からの名前1

print @user2

- - update ステートメントを使用して値を割り当てます

declare @user3 nvarchar(50)

update ST_User set @user3 = Name where ID=1

print @user3

2テーブル、一時テーブル、テーブル変数

-- 一時テーブル 1 を作成します

テーブル #DU_User1 を作成します

( [ID] [ int ] NOT NULL ,

[Oid] [ int ] NOT NULL ,

[ログイン] [nvarchar](50) [Rtx] [nvarchar](4) NOT NULL 、

[名前] [nvarchar](5) NOT NULL 、

[パスワード] [nvarchar]( max ) NULL 、

[状態] [nvarchar](8) NOT NULL

);

-- 一時テーブル 1 にレコードを挿入します

#DU_User1 (ID,Oid,[Login],Rtx, Name,[Password],State) 値に挿入します(100,2, 'LS ' ,'0000' , 'Temporary' , '321' , 'Special' );

-- ST_User からデータをクエリし、新しく生成された一時テーブルに入力します

select * into #DU_User2 from ST_User where ID

-- 2 つの一時テーブルをクエリして結合します

select * from #DU_User2 where ID

-- 2 つの一時テーブルを削除します

テーブル # DU_User1 を削除

テーブル # DU_User2 を削除

--一時テーブルを作成

CREATE TABLE #t

(

[ID] [ int ] NOT NULL ,

[Oid ] [ int ] NOT NULL 、

[ログイン] [nvarchar]( 50) NOT NULL 、

[nvarchar]（4）

[state] [nvarchar]（8）not null、

）

-クエリ結果セット（複数のデータ）を一時テーブルに挿入してください。 * from ST_User

--このように挿入できません

--select * into # t from dbo.ST_User

--int 型の自動増加サブセクションの列を追加します

alter table #t add [myid ] int NOT NULL IDENTITY(1,1)

--列を追加し、デフォルトで世界を埋める一意の識別子

alter table #t add [myid1] uniqueidentifier NOT NULL default (newid())

select * from #t

drop table #t

-- クエリ結果セットに自動増加列を追加します

-- 主キーがない場合:

IDENTITY(int,1,1) を ID として選択します、名前、[ログイン]、[パスワード] を ST_User から #t に入力します

select * from #t

--はい主キー:

select (ST_User から SUM (1) を選択します (ID -- テーブル変数を定義します

declare @t table

(

id int not null ,

msg nvarchar(50) null

)

@t に挿入値 (1, '1' )

@t に挿入値 (2, '2' )

select * from @t

3. ループ

-- while ループは 1 から 1 までの合計を計算します100

declare @a int

declare @ sum int

set @a=1

set @ sum =0

while @abegin

set @ sum +=@a

set @a+=1

end

print @ sum

4. 条件文

--if,else 条件分岐

if(1+1 =2)

begin

print 'right'

end

else

begin

print 'wrong'

end

--when then 条件分岐

declare @today int

declare @ Week nvarchar(3)

set @today=3

@を設定week = case @ @ @today = 1 then 'manday' when @ @today = 2 then 'today' when @today = 3 then 'can' can 'can' can 'else' else 'valueエラーprint @week

5. Cursor

declare @ID int

declare @Oid int

declare @Login varchar (50 )

-- カーソルを定義します

select ID の user_cur カーソルを宣言します,オイド,ST_User から [ログイン]

-- カーソルを開く

user_cur を開く

@@ fetch_status = 0カーソル

deallocate user_cur

6. トリガー

トリガー内の一時テーブル:

挿入

挿入と更新操作後にデータを保存する

削除

削除と更新操作の前にデータを保存するデータ

--トリガーの作成作成クレア@msg nvarchar(50)

--@msg レコードの変更

select @msg = N ' 名前が "' + Deleted. Name + N '" から "' + Inserted. Name + '"' fromInserted,削除済み

-- トリガー

drop トリガー User_OnUpdate

7. ストアドプロシージャ

-- 出力パラメーターを使用してストアドプロシージャを作成します

CREATE PROCEDURE PR_Sum

@a int ,

@b int 、

@ sum int 出力

BEGIN

set @ sum =@a+@b

END

--戻り値ストアドプロシージャの作成

CREATE PRO CEDURE PR_Sum2

@a int ,

@b int

BEGIN

Return @a+@b

END

--ストアドプロシージャを実行して出力タイプの戻り値を取得します

declare @mysum int

execute PR_Sum 1,2,@mysum Output

print @mysum

8. カスタム関数

関数の分類:

1) スカラー値関数

2) テーブル値関数

a: インラインテーブル値関数

b: 複数のステートメントテーブル値関数

3) システム関数

-- 新しいスカラー値関数

create function FUNC_Sum1

(

@a int ,

@b int

)

returns int

として

begin

return @a+@b

end

-- 新しいインラインテーブル値関数

create function FUNC_UserTab_1

(

@myId int

)

テーブル

を

return として返します(select * from ST_User where ID

- - 新しい複数ステートメントのテーブル値関数

create function FUNC_UserTab_2

(

@myId int

)

returns @t table

(

] nullではない、

[[ログイン] [nvarchar] (50) null ではありません、

[rtx] [nvarchar] (4) null ではありません、

[名前] [nvarchar] (5) null ではありません、

[パスワード] [nvarchar] ( nvarchar] (nvarchar] (nvarchar] (nvarchar] (nvarchar] max ) NULL ,

[State] [nvarchar](8) NOT NULL

)

begin

insert into @t select * from ST_ User where ID return

end

-- テーブル値関数を呼び出す

select * from dbo.FUNC_UserTab_1(15)

-- スカラー値関数を呼び出す

declare @s int

set @s=dbo.FUNC_Sum1(100,50)

print @s

-- スカラー値関数を削除

drop function FUNC_Sum1

カスタム関数とストアドプロシージャの違いについて話しましょう:

1. カスタム関数:

1. テーブル変数を返すことができます

出力パラメータは使用できません;

関数内の操作は外部環境に影響を与えることができません。

select では結果セットを返せない;

データベーステーブルの更新、削除はできない ;

3. スカラー値かテーブル変数を返さなければならない

カスタム関数は再利用性が高く、関数が単純で強力な場所でよく使用されます。競争。

2. ストアドプロシージャ

1. テーブル変数を返すことはできません

2. 制限はほとんどありませんが、データベーステーブルに対して操作を実行でき、データセットを返すことができます

3. スカラー値を返すことができ、リターンは省略可能です

ストアドプロシージャは通常、複雑な関数とデータ操作を実装するために使用されます。

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

SqlServer ストアドプロシージャ -- 例

例 1:単一のレコードセットのストアドプロシージャのみを返します。

テーブル銀行預金テーブル(bankMoney)の内容は以下の通りです

要件1: テーブルbankMoneyの内容をクエリするストアドプロシージャ

createプロシージャsp_query_bankMoney SQLServer - ストアドプロシージャの基本構文 as

select * from BankMoney

exec sp_query_bankMoney

注* 使用中は、T-Sql 内の SQL ステートメントをストアドプロシージャ名に置き換えるだけで済み、とても便利です。

例 2 (ストアドプロシージャにパラメータを渡す):

テーブル BankMoney にレコードを追加し、このテーブル内の userID = Zhangsan を持つすべての預金の合計金額をクエリします。

proc insert_bank を作成します @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int 出力

を暗号化付き --------暗号化

BankMoney (id,userID,sex,Money) に挿入します

Values(@param1,@param2,@param3, @param4)

SQL Server の userID='Zhangsan' の BankMoney から @param5=sum(Money) を選択します
go
クエリアナライザーでこのストアドプロシージャを実行するメソッドは次のとおりです:
declare @total_price int
exec insert_bank '004','Zhangsan','M',100,@total_price Output
print '合計残高は'+convert(varchar ,@ total_price)
go

ここでは、ストアドプロシージャの 3 つの戻り値について説明します (この例を見ている友人が構文の内容を確認する必要がないように):

1. Return 付きの整数
2. 出力形式の戻りパラメータ

戻り値の違い:

output と return はバッチプログラム内の変数を使用して受け取ることができますが、レコードセットは実行するクライアントに返されます。バッチ。

例 3: 複雑な SELECT ステートメントでの単純なプロシージャの使用

次のストアドプロシージャは、4 つのテーブルの結合からすべての著者 (名前が指定されている)、出版された書籍、および出版社を返します。このストアドプロシージャはパラメータを使用しません。

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type = 'P')

DROP PROCEDURE au_info_all

CREATE PROCEDURE au_info_all

AS
SELECT au_lname, au_fname, title, pub _name
著者から内部結合タイトル著者 ta
内部結合タイトルオン

au_info_all ストアドプロシージャは次の方法で実行できます:

EXECUTE au_info_all
-- または
EXEC au_info_all

プロシージャがバッチ内の最初のステートメントである場合は、次のコマンドを使用できます:

au_info_all

例 4: 単純なプロシージャの使用パラメーター付き

CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN タイトル t
use using using ta. _info ストアドプロシージャには、次のメソッドが実行されます:

EXECUTE au_info 'Dull', 「アン」
-- または
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'

-- または

EXECUTE au_info @firstname = 'Ann', @ lastname = 'Dull'

-- または

EXEC au_info 'Dull', 'Ann'
-- または
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = ' Ann', @lastname = 'Dull'

プロセスがバッチ内の最初のステートメントである場合、次を使用できます:

au_info '鈍い'、'アン'
-- または
au_info @lastname = '鈍い'、@firstname = 'アン'

-- または

au_info @firstname = 'アン'、@lastname = '鈍い'

例 5: ワイルドカードパラメーターを使用した単純なプロシージャの使用

CREATE PROCEDURE au_info2

@ lastname varchar(30) = 'D%',

@firstname varchar(18) = '%'

SELECT au_lname, au_fname, title, pub_name

FROM 著者 a INNER JOIN titleauthor ta

ON a.au_id = ta.au_id INNER JOIN タイトル t
ON t.title_id = ta.title_id INNER JOIN Publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO

au_info2 ストアドプロシージャは、実装のさまざまな組み合わせで使用できます。以下にいくつかの組み合わせのみを示します:

EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'

-- Or

EXECUTE au_info2 @firstname = 'A%'

-- Or

EXECUTE au_info2 '[CK]ars[ OE ]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'

= 'proc2'

例 6: if...else

ストアドプロシージャ。更新を実行するための選択基準として @case が使用され、実行中に渡されるパラメーターに従ってさまざまな変更を実装するために if...else が使用されます。

-- 以下は、 if...else のストアドプロシージャ：

if 存在する (select 1 from sysobjects where name = 'Student' and type ='u' )

drop table Student

if存在する (select 1 from sysobjects where name = 'spUpdateStudent ' そして ='p' と入力します)
drop proc spUpdateStudent
go

create table Student
(
fName nvarchar (10),
fAge

smallint ,
fDiqu varchar (50),
fTel int
)
go

Student の値 ('X.X.Y ' , 28, 'Tesing' , 888888) に挿入します
go

create proc spUpdateStudent
(
@fCase int ,
@fName nvarchar (10),
@fAge smallint ,
@fDiqu varchar (50),
@fTel int
)
as
update Student
set fAge = @fAge, -- fAge を更新するために 1,2,3 を渡します。ケース
fDiqu = (@fCase = 2 の場合、または@fCase = 3 then @fDiqu else fDiqu end ),
fTel = (case when @fCase = 3 then @fTel else fTel end )
where fName = @fName
select * from Student
go

-- 年齢のみ変更
exec spUpdateStudent
@fCase = 1,
@ fName = N'X.
@fAge = 80、
@fDiqu = N'Update' 、
@fTel = 1010101