래채'sTory

**변수사용

로컬변수의 특징

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

// 프로시져는 참조하는 테이블이 없어도 생성이 되지만 뷰는 참조하는 테이블이 없이는 생성될수 없다.