[MS-SQL] 변수, 조건문, 반목문, 저장프로시져

로컬변수의 특징
1. @라는 첨자를 가진다
2. 선언된 로컬 변수는 현제 커넥션에서 같은 배치내에서만 사용
3. 값을 초기화하기 위해 set, select문 사용
 
DECLARE @YMD DATETIME     //선언
SET @YMD=GETDATE()           // 초기화
SELECT @YMD
DECLARE @SAL INT
SET @SAL=5000
SELECT ENAME, SAL FROM EMP
WHERE SAL>@SAL
 
DECLARE @SAL INT
SELECT @SAL=AVG(SAL) FROM EMP
SELECT @SAL
 
**조건문
 
<IF문>
IF 조건
실행문
ELSE
실행문
DECLARE @I INT
SET @I=200
IF @I <100
PRINT ‘@I는 100보다 작습니다.’
ELSE
PRINT ‘@I는 100이상입니다.’
—————————
DECLARE @SAL INT
SET @SAL=2000
IF @SAL >=3000
     BEGIN    //실행문이 2개 이상일때는 BEGIN END로 묶어주어야함..
PRINT ‘3000이상인 사원몰록’
SELECT * FROM EMP
WHERE SAL>=3000
   END
ELSE
PRINT ‘3000미만인 사원목록’
SELECT * FROM EMP
WHERE SAL<3000
 
 
<CASE문(END로 끝남. JOIN한 것과 같은 결과)>
 
SELECT EMPNO, ENAME,DEPTNO,
(CASE
WHEN DEPTNO=10 THEN ‘전산실’
WHEN DEPTNO=10 THEN ‘총무부’
WHEN DEPTNO=10 THEN ‘영업부’
ELSE
‘기타’
END) AS 부서명
FROM EMP
 
**반복문
DECLARE @A INT
SET @A=1
WHILE @A <100    // BEGIN .. END를 쓰지 않으면 무한루프에 빠짐~!!
BEGIN
SELECT @A
SET @A=@A+1
END
**저장 프로시져(STRORED PROCEDURE)

–서버에 컴파일 시켜 저장시켜놓은 SQL코드의 집합
 
시스템 저장프로시져(SP_, MASTER DB)
확장 저장 프로시져(XP_, MASTER DB)
사용자 저장 프로시져
 
–장점: VIEW의 장점 +a
 
-처리속도가 빠르다
-네트워크 트래픽을 줄인다
-확장이 용이하다.
 
 
 
-일반 SQL문 처리과정
문법검사-표준화-보안점검-최적화-컴파일
생성————–>|
SYSOBJECTS
SYSCOMMENTS 저장
|————–>첫실행
|–>두번째(메모리에 있는것을 읽어온다.)
EXEC SP_HELP
DBCC FREEPROCCACHE // 캐쉬에 있는 내용을 싹 지워버리는것.
SELECT ENAME, SAL, DEPTNO
FROM EMP
CREATE PROC MYPROC01
AS
SELECT ENAME, SAL
FROM EMP
EXEC MYPROC01
//암호화, 수정 등등은 뷰와 동일하다.
 
CREATE PROC MYPROC02
AS
UPDATE EMP
SET SAL=SAL*2
EXEC MYPROC02
 
 
입력매개변수 : 프로시져를 실행할때 꼭 값을 입력해주어야 함
 
ALTER PROC MYPROC02
@SAL INT   // 입력매개변수
AS
UPDATE EMP
SET SAL = SAL* @SAL
 
 
BEGIN TRAN
EXEC MYPROC02 3// 프로시져 이름 옆에 @SAL에 들어갈 값을 써준다.
 
 
 
ALTER PROC MYPROC02
@SAL INT =2  //생성될때 입력매개 변수에 값을 주어 생성한다. 변수가 여러개일경우 ,를 사용
AS
UPDATE EMP
SET SAL=SAL*@SAL
 
 
 
-<실습해보기>—————————————————————
[ EXEC TITLEPRICE ‘%TALK%’,19.99 ]로 결과를 볼수있도록 프로시져를 생성해보자~!!
 
ALTER PROC TITLEPRICE
@NAME VARCHAR(100), @PRICE MONEY
AS
SELECT TITLE, PRICE
FROM TITLES
WHERE TITLE LIKE @NAME AND PRICE= @PRICE
 
★초기값을 주어 파라미터를 주지 않고도 실행시킬수 있도록 할수도 있다.
만약 IF문을 써넣지 않는다면, PRICE가 널값을 초기값으로 가지므로 AND연산자에 의해 결과가 나오지 않는다.
 
ALTER PROC TITLEPRICE
@NAME VARCHAR(100)=’%’, @PRICE MONEY=NULL 
AS
 
IF @PRICE IS NULL
SELECT TITLE, PRICE FROM TITLES
WHERE TITLE LIKE @TITLE
 
ELSE
SELECT TITLE, PRICE FROM TITLES
WHERE TITLE LIKE @NAME AND PRICE= @PRICE
 
 
★파라미터의 순서가 어긋날경우 실행할때 변수명을 써서 같이 실행을 해주어야 한다.
EXEC TITLEPRICE @PRICE=19.99
 
출력매개변수 : 변수를 그대로 넘겨줌
: 그냥 알아두기만 하기.
 
ALTER PROC MYPROC03
@NUM1 INT, @NUM2 INT, @SUM INT OUTPUT –출력매개변수
AS
SELECT @SUM=@NUM1+@NUM2
 
//확인
DECLARE @SUM INT
EXEC MYPROC03 3,10, @SUM OUTPUT
SELECT @SUM
 
**동적 쿼리
 
//테이블 이름은 변수 명칭으로 사용할수 없다.
CREATE PROC EXTEST
@TABLE VARCHAR(100)
AS
CREATE TABLE @TABLE (COL INT) –>에러발생
 
동적쿼리: 사용할수 없는 문법을 잠시 변형하여 사용할수 있게 한것.
쿼리를 문자열 처리하여서 전체를 EXECUTE를 사용한다.
 
ALTER PROC EXTEST
@TABLE VARCHAR(100)
AS
EXECUTE(‘CREATE TABLE ‘+ @TABLE +'(COL INT)’)// 공백까지 생각해야함.
 
EXTEST ABC // ABC라는 테이블이 생성됨
 
 
–지연된 이름 확인
 <뷰>
CREATE VIEW MYVIEW
AS
SELECT * FROM TEST
 
 <프로시져>
CREATE PROC MYPROC10
AS
SELECT * FROM TEST
 
// 프로시져는 참조하는 테이블이 없어도 생성이 되지만 뷰는 참조하는 테이블이 없이는 생성될수 없다.

댓글 남기기