SQLServer - 저장 프로시저의 기본 구문
- 高洛峰원래의
- 2016-12-14 14:55:321254검색
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 문:
if .. begin ... end else if .. begin ... end else begin ... end예:SQL 코드
DECLARE @d INT set @d = 1 IF @d = 1 BEGIN PRINT '正确' END ELSE BEGIN PRINT '错误' END
여러 조건 선택 문:
예:
declare @today int
declare @week nvarchar(3)
set @today=3
set @week= case
when @today=1 then '星期一'
when @today=2 then '星期二'
when @today=3 then '星期三'
when @today=4 then '星期四'
when @today=5 then '星期五'
when @today=6 then '星期六'
when @today=7 then '星期日'
else '值错误'
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 @cur1SQL 코드
AS
declare @CATEGORY_CI_TABLENAME VARCHAR(50) =''
declare @result VARCHAR(2000) = ''
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= 'Y' and CATEGORY_CODE =@CATEGORY_CODE
IF (@ATTRIBUTE2='A')
begin
DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= 'Y' and CATEGORY_CODE =@CATEGORY_CODE
OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
set @result = @result+@CONFIG_CODE+','
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+','
end
else if(@num=@countnum)
begin
set @result = @result +@CONFIG_CODE
end
END
CLOSE MyCursor
DEALLOCATE MyCursor
set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result +') select '+ @result +' from '+@CATEGORY_CI_TABLENAME +' where CI_ORDER_LINE_ID='+@KEY_ID
end
else if((@ATTRIBUTE2='U'))임시 테이블: -- 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)를 사용하여 임시 테이블을 명시적으로 삭제할 수 있습니다.
임시 테이블은 너무 과도하지 않으면 실행 효율성에 거의 영향을 미치지 않습니다. 반대로, 특히 데이터베이스 임시 테이블 공간이 충분하다면 효율성을 높일 수 있습니다. 🎜>커서가 너무 많으면 실행이 심각해지니 가능하면 피하세요!
기타:
인 댓글에서 @count=count(*)를 선택하세요. 🎜>
--단일 값을 반환하는 함수--
MyFunction 함수 생성
(@newsid int)
int
as
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)
테이블 반환
as
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 출력
AS
BEGIN
set @ sum =@a+@b
END
--반환 반환 값 저장 프로시저 생성
CREATE PROCEDURE PR_Sum2
@a int ,
@b int
AS
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를 반환
as
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
)
as
시작
@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)의 내용은 다음과 같습니다
요구 사항 1: 테이블bankMoney의 내용을 쿼리하는 저장 프로시저
sp_query_bankMoney 프로시저 생성
asselect * 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'
-- 또는
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'
생성 절차 au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'AS
SELECT au_lname, au_fname, title , pub_nameFROM 작성자 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 @firstname = 'A%'-- 또는
EXECUTE au_info2 '[CK]ars[OE]n'
-- 또는
EXECUTE au_info2 'Hunter', 'Sheryl'
-- 또는
EXECUTE au_info2 ' H %', 'S%'
= 'proc2'
예 6: if...else
--다음은 if의 저장 프로시저입니다. ...else:
존재하는 경우(이름 = 'Student'이고 유형 ='u'인 sysobjects에서 1 선택)Student 테이블 삭제
go
존재하는 경우(다음에서 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.