SQLServer - 저장 프로시저의 기본 구문-PHP开发-php.cn

집

php教程

PHP开发

SQLServer - 저장 프로시저의 기본 구문

高洛峰

Dec 14, 2016 pm 02:55 PM

Oracle의 테이블 생성 sql을 sqlserver의 테이블 생성 sql로 변환 시 주의 사항:
1. 주석문은 모두 삭제해야 합니다.
2. clob 유형을 텍스트 유형으로 변환합니다.
3. Blob 유형을 이미지 유형으로 변환합니다.
4.숫자형은 int로, 숫자(16,2) 등은 십진수(16,2)로, 숫자(18)는 bigint로 변환합니다.
5.기본 sysdate가 기본 getDate()로 변경되었습니다.
6.to_date('2009-12-18','yyyy-mm-dd')가 Cast('2009-12-18' as datetime)로 변경되었습니다.

SQLSERVER:
변수 선언:
변수 선언 시 변수 앞에 @ 기호를 추가해야 합니다.
DECLARE @I INT

변수 할당:
변수 할당 시 setSET @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 ABC Select
-- 테이블 ABC가 있어야 합니다.
-- 테이블 Uname의 Username 필드를 테이블 ABC에 복사
Insert INTO ABC Select Username FROM Uname

-- 임시 테이블 생성
Create TABLE #temp(
UID int Identity(1, 1) PRIMARY KEY,
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)

CommentID=@commentid인 댓글에서 * 선택

-입력 및 출력 매개변수가 있는 저장 프로시저--

Proc GetCommentCount 생성

@newsid int,

@count int 출력

NewsID=@newsid

인 댓글에서 @count=count(*)를 선택하세요. 🎜>

--단일 값을 반환하는 함수--

MyFunction 함수 생성

(@newsid int)

int

begin

declare @count int

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

@count 반환

end

--호출 메서드--

@count int 선언

exec @count =MyFunction 2

print @count

--반환 값이 테이블인 함수--

GetFunctionTable 함수 만들기

( @newsid int)

테이블 반환

return

(NewsID=@newsid인 댓글에서 * 선택)

--반환 값이 테이블인 함수 호출--

select * from GetFunctionTable(2)

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

SQLServer는 저장 프로시저의 SQL 문자열을 연결하지 않습니다. 다중 조건 쿼리 구현

이전 작성 방법
set @sql=' select * from table where 1=1 '
if (@addDate가 null이 아님)
set @sql = @sql+' and addDate = '+ @addDate + ' '
if (@name '' and is not null)
set @sql = @sql+ ' and name = ' + @name + ' '
exec(@sql )
다음은 SQL 문자열을 연결하지 않고 다중 조건 쿼리를 달성하는 솔루션입니다
첫 번째 작성 방법은 코드가 약간 중복되는 느낌을 받는 것입니다
if (@addDate가 null이 아님) 및 (@name addDate = @addDate 및 name = @name인 테이블에서 *를 선택합니다.
else if(@addDate가 null이 아니고 @name ='')
select * from addDate = @addDate
else if(@addDate is null) and (@name '')
select * from table where and name = @name
else if(@addDate is null) 및 (@name = '')
select * from table
작성하는 두 번째 방법은
select * from table where (addDate = @addDate 또는 @addDate가 null) 및 (name = @name 또는 @name = '')
세 번째 작성 방법은
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 문을 사용하여 값 할당

@user1 nvarchar(50 ) 선언

select @user1= '张三'

@user1 인쇄

@user2 nvarchar(50) 선언

select @user2 = ID=1인 ST_User의 이름

print @user2

-업데이트 문을 사용하여 값 할당

declare @user3 nvarchar(50)

update ST_User set @user3 = ID가 1인 이름

print @user3

2. 임시 테이블, 테이블 변수

--임시 테이블 1 생성

테이블 #DU_User1 생성

(

[ID] [ int ] NOT NULL , [ Oid] NOT NULL ,

[로그인] [nvarchar](50) NOT NULL ,

[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에서 데이터를 쿼리하고 새로 생성된 임시 파일에 입력합니다. table

select * into #DU_User2 from ST_User where ID

--두 개의 임시 테이블 삭제

drop table #DU_User1

테이블 삭제 #DU_User2

--임시 테이블 만들기

CREATE TABLE #t NOT NULL ,

[로그인] [nvarchar]( 50) NULL이 아님,

[Rtx] [nvarchar](4) NOT NULL ,

[상태] [nvarchar](8) NOT NULL ,

)

--쿼리 결과 집합(여러 데이터 조각)을 임시 테이블에 삽입

insert into #t select * from ST_User

--이와 같이 삽입할 수 없습니다

--dbo.ST_User에서 *를 #t로 선택

--int 유형의 자체 증가 하위 섹션으로 열 추가

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

--열을 추가하면 전역 고유 식별자가 기본적으로 채워집니다.

alter table #t add [myid1] Uniqueidentifier NOT NULL 기본값 (newid()) 🎜>drop table #t

--쿼리 결과 집합에 자동 증가 열 추가

--기본 키가 없는 경우 :

ST_User에서 #t로 IDENTITY( int ,1,1)를 ID, 이름, [로그인], [ 비밀번호 ]로 선택

#t에서 * 선택

--기본 키가 있는 경우 ：

myID로 ST_User에서 SUM (1) 선택(ID

--테이블 변수 정의

@t 테이블 선언

(

id int not null ,

msg nvarchar(50) null

)

@t 값에 삽입 (1, '1' )

@t 값에 삽입 (2, '2' )

select * from @t

3. 루프

--while 루프는 1에서 100까지의 합을 계산합니다.

declare @a int

declare @ sum int

set @a=1

set @ sum =0

while @a

begin

set @ sum +=@a

set @a+=1

end

print @ sum

조건문

- -if,else 조건부 분기

if(1+1=2 )

begin

'right' 인쇄

end

else

begin

print 'Wrong'

end

--조건 분기

선언 @today int

@week nvarchar(3) 선언

set @today=3

set @week= case

when @today=1 then '월요일'

@today=2일 때 '화요일'

@today=3일 때 '수요일'

@today=4일 때 '목요일'

@today=5일 때 '금요일'

@today=6일 때 '토요일' @week

커서

declare @ID int

declare @Oid int

declare @Login varchar (50)

--커서 정의

ST_User

에서 ID,Oid,[Login] 선택에 대한 user_cur 커서 선언--커서 열기

user_cur 열기

while @@fetch_status=0

begin

--커서 읽기 🎜> print @ID

--print @Login

end

user_cur 닫기

--커서 삭제

user_cur 할당 해제

6. 트리거

트리거의 임시 테이블:

삽입됨

삽입 및 업데이트 작업 후 데이터 저장

삭제됨

진행 중인 데이터 저장 삭제 및 업데이트 작업 전 데이터

--트리거 생성 User_OnUpdate

트리거 생성 User_OnUpdate

On ST_User

for Update 🎜>As

선언 @msg nvarchar(50)

--@msg 레코드 수정 상태

select @msg = N 'Name from "' + 삭제됨. 이름 + N '" 수정: "' + 삽입됨. 이름 + '"' fromInserted,Deleted

--로그 테이블에 삽입

[LOG](MSG) 값에 삽입 (@msg)

--트리거 삭제

트리거 삭제 User_OnUpdate

7 . 저장 프로시저

--출력 매개변수를 사용하여 저장 프로시저 만들기

CREATE PROCEDURE PR_Sum

@a int ,

@b int ,

@ sum int 출력

BEGIN

set @ sum =@a+@b

END

--반환 반환 값 저장 프로시저 생성

CREATE PROCEDURE PR_Sum2

@a int ,

@b int

BEGIN

Return @a+@b

END

--출력 유형 반환 값을 얻기 위해 저장 프로시저를 실행합니다.

@mysum int 선언

execute PR_Sum 1,2,@mysum 출력

print @mysum

--저장 프로시저 실행 반환 유형 반환 값을 얻으려면

declare @mysum2 int

execute @mysum2= PR_Sum2 1 ,2

print @mysum2

8. 사용자 정의 함수

함수 분류:

1) 스칼라 값 함수

2) 테이블 값 함수

a: 인라인 테이블 반환 함수

b: 다중 문 테이블 반환 함수

3 ) 시스템 함수

--함수 만들기 FUNC_Sum1

FUNC_Sum1 함수 만들기

( @a int,

@b int

)

int를 반환

begin

return @a+@b

end

--새로운 인라인 테이블 반환 함수

FUNC_UserTab_1 함수

)

테이블을

return(ID

--다중 문 테이블 반환 함수 만들기 FUNC_UserTab_2

( @myId int

)는 @t 테이블을 반환합니다

(

[ID] [ int ] NOT NULL ,

[Oid] [ int ] NOT NULL ,

[로그인] [nvarchar](50) NOT NULL ,

[Rtx] [nvarchar](4) NOT NULL ,

[이름] [nvarchar](5) NOT NULL ,

[ 비밀번호 ] [nvarchar]( max ) NULL ,

[상태] [nvarchar](8) NOT NULL

)

시작

@t에 삽입 * ST_User에서 ID

반환

끝

--테이블 값 함수 호출

select * from dbo.FUNC_UserTab_1(15)

--스칼라 값 함수 호출

@s int 선언

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

print @s

--스칼라 값 함수 삭제

에 대해 이야기해 보세요. 사용자 정의 함수와 저장 프로시저의 차이점:

1. 사용자 정의 함수:

1. 테이블 변수를 반환할 수 있음

2. 제한 사항

출력 매개변수를 사용할 수 없습니다.

임시 테이블을 사용할 수 없습니다.

함수 내부의 작업은 외부 환경에 영향을 미칠 수 없습니다.

결과를 반환할 수 없습니다. set;

데이터베이스 테이블을 업데이트, 삭제할 수 없습니다.

3. 스칼라 값 또는 테이블 변수를 반환해야 합니다.

일반적으로 사용자 정의 함수는 재사용을 위해 사용됩니다. 단순하고 단일한 기능, 강력한 경쟁력.

2. 저장 프로시저

1. 테이블 변수를 반환할 수 없습니다.

2. 제한이 적고, 데이터베이스 테이블에 대한 작업을 수행할 수 있으며, 데이터 세트를 반환할 수 있습니다.

3. 스칼라 값을 반환할 수도 있고, 반환을 생략할 수도 있습니다

저장 프로시저는 일반적으로 복잡한 기능과 데이터 조작을 구현하는 데 사용됩니다.

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

SqlServer 저장 프로시저--예

예 1: 단일 레코드 세트만 반환하는 저장 프로시저.

은행 예금 테이블(bankMoney)의 내용은 다음과 같습니다

SQLServer - 저장 프로시저의 기본 구문 요구 사항 1: 테이블bankMoney의 내용을 쿼리하는 저장 프로시저

sp_query_bankMoney 프로시저 생성

select * frombankMoney
go
exec sp_query_bankMoney

참고* 사용 중에는 T-Sql의 SQL 문을 다음으로 바꾸기만 하면 됩니다. 저장 프로시저 이름입니다. 아주 편리합니다!

예제 2(저장 프로시저에 매개변수 전달):

bankMoney 테이블에 레코드를 추가하고 이 테이블에서 userID = Zhangsan을 사용하여 모든 예금의 총액을 쿼리합니다.

암호화를 사용하여 insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int 출력

프로시저 만들기 ------- --암호화

as
bankMoney(id,userID,sex,Money)에 삽입
값(@param1,@param2,@param3, @param4)
@param5=sum(Money) 선택 frombankMoney where userID='Zhangsan'
go
SQL Server 쿼리 분석기에서 이 저장 프로시저를 실행하는 방법은 다음과 같습니다.
declare @total_price int
exec insert_bank '004','Zhangsan', ' Male',100,@total_price 출력
print 'The total Balance is'+convert(varchar,@total_price)
go

여기서 우리는 3가지 반환 값에 대해 논의할 것입니다. 저장 프로시저(이 예제를 보고 있는 친구들은 구문 내용을 확인할 필요가 없어 편리합니다):

1. Return으로 정수를 반환합니다

2. 매개변수를 출력 형식으로 반환합니다

3.Recordset

반환 값의 차이점:

배치 프로그램의 변수를 사용하여 출력과 반환을 모두 받을 수 있지만, 레코드 세트는 일괄 처리를 실행하는 클라이언트로 다시 전달됩니다.

예 3: 복잡한 SELECT 문과 함께 간단한 프로시저 사용

다음 저장 프로시저는 4개의 테이블 조인에서 모든 저자(제공된 이름), 출판 도서 및 출판사를 반환합니다. 이 저장 프로시저는 매개변수를 사용하지 않습니다.

pubs 사용

IF EXISTS(SELECT name FROM sysobjects

WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
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 titles t
ON t.title_id = ta. title_id INNER JOIN 출판사 p
ON t.pub_id = p.pub_id
GO

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 titles t
ON t .title_id = ta .title_id INNER JOIN 게시자 p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO

au_info 저장 프로시저 실행 가능 다음 방법으로: 또는

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

-- 또는
EXEC au_info 'Dull', 'Ann'
-- 또는
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- 또는
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

프로세스가 다음과 같은 경우 배치 A 문의 첫 번째 문에서는 다음을 사용할 수 있습니다.

au_info 'Dull', 'Ann'
-- 또는

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

-- Or

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

예 5: 와일드카드 매개변수가 포함된 간단한 프로시저 사용

생성 절차 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 titles t
ON t.title_id = ta.title_id INNER JOIN 게시자 p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO

au_info2 저장 프로시저는 다양한 조합으로 실행될 수 있습니다. 아래에는 일부 조합만 나열되어 있습니다.

　EXECUTE au_info2
-- 또는

EXECUTE au_info2 'Wh%'

-- 또는

EXECUTE au_info2 @firstname = 'A%'

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

= 'proc2'

예 6: if...else

저장됨 업데이트를 실행하기 위한 선택 기준으로 @case를 사용하고, 실행 중에 전달된 매개변수에 따라 다른 수정을 구현하기 위해 if...else를 사용하는 프로시저

--다음은 if의 저장 프로시저입니다. ...else:

존재하는 경우(이름 = 'Student'이고 유형 ='u'인 sysobjects에서 1 선택)

Student 테이블 삭제

존재하는 경우(다음에서 1 선택) sysobjects 여기서 이름 = 'spUpdateStudent' 및 유형 = 'p' )
drop proc spUpdateStudent
go

Student 테이블 만들기
(
fName nvarchar (10),
fAge

smallint ,
fDiqu varchar (50),
fTel int
)

Student 값에 삽입 ('X.X.Y' , 28, ' Tesing' , 888888)
이동

프로세스 spUpdateStudent 생성
(
@fCase int ,
@fName nvarchar (10),
@fAge smallint ,
@ fDiqu varchar (50),
@fTel int
)

update Student
set fAge = @fAge, -- fAge를 업데이트하려면 1,2,3을 전달합니다. case
fDiqu = (@fCase = 2 또는 @fCase = 3인 경우 @fDiqu else fDiqu end ),
fTel = (@fCase = 3인 경우 @fTel else fTel end )
where fName = @fName
학생에서 * 선택
이동

-- 연령만 변경
exec spUpdateStudent
@fCase = 1,
@fName = N'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel = 1010101

-- 연령 및 Diqu 변경
exec spUpdateStudent
@fCase = 2,
@fName = N'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel = 1010101

--전체 변경
exec spUpdateStudent
@fCase = 3,
@fName = N'X.