MS SQL Stored Procedure

출처: http://cafe.daum.net/iw-introducer/1Eic/74?docid=1D1MR|1Eic|74|20080304154453&q=ms%20sql%20%BA%E4%20%C6%C4%B6%F3%B9%CC%C5%CD

--사용자 정의 함수 (UDF)

--스칼라함수(단일값 반환)-TIMESTAMP,IMAGE,TEXT,NTEXT
--테이블 반환함수
--다중 테이블 반환함수

 

 

CREATE FUNCTION TOTALSUM
(@NUM1 INT,@NUM2 INT)
RETURNS INT
AS
BEGIN  -- BEGIN.. END구문을 빼면 무조건 에러가 남.. 문법으로 정해짐.
RETURN(@NUM1+@NUM2)
END

 

 

!!- 변수선언시 기본값이 설정이 되어 있어도 실행할때는 DEFAULT값이로라도 파라미터의 갯수를 맞춰줘야한다.

만들어진 함수 사용

SELECT DBO.TOTALSUM(2,3) --함수를 사용할때는 소유자의 이름을 앞에 같이 사용해야한다.

확정적 함수 
비확정적 함수 : 함수를 사용할때마다 값이 매번 바뀌는 함수.예)GETDATE()함수, NEWID()함수 등..

 

 

ALTER FUNCTION EMPSUM
(@EMPNO INT, @MGR INT, @SAL INT, @COMM INT,@DEPTNO INT)
RETURNS INT
AS
BEGIN
RETURN(@EMPNO+ISNULL(@MGR,0)+@SAL+ISNULL(@COMM,0)+@DEPTNO)
END

 

SELECT ENAME, DBO.EMPSUM(EMPNO, MGR, SAL, COMM,DEPTNO)
FROM EMP

--------------------------------------------------------------------------------------
--날짜를 입력하면 월 만 리턴해주는 함수 생성

SELECT DBO.MONTHH('2000-10-10')-->10

 

CREATE FUNCTION MONTHH
(@DATE DATETIME)
RETURNS INT
AS
BEGIN
RETURN(DATEPART(MM,@DATE))
END

 

 

SELECT DBO.MONTHH(GETDATE())

--------------------------------------------------------------------------------------
--날짜의 구분자 부분을 원하는 기호로 변화하여 출력시켜주는 함수 생성

SELECT DBO.SEDATE('2001-10-10','/')
-->2002/10/10

 

 

CREATE FUNCTION SEDATE
(@DATE DATETIME,@SEP CHAR(1))
RETURNS VARCHAR(10)
AS
BEGIN
RETURN(DATENAME(YY,@DATE)+@SEP+DATENAME(MM,@DATE)+@SEP+DATENAME(DD,@DATE))
END

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

CREATE TABLE MYTABLE01
(NUM1 INT, NUM2 INT, NSUM AS NUM1+NUM2)

 

INSERT MYTABLE01 VALUES(10,20)
SELECT * FROM MYTABLE01

 

 

CREATE TABLE MYTABLE02
(ID INT,NDATE DATETIME, MON AS DBO.MONTHH(NDATE))

-- 여기서의 AS는 SELECT문에서의 별칭을 줄때 사용하는 AS와 다른것이다.

 

INSERT MYTABLE02 VALUES(1,GETDATE())
SELECT *FROM MYTABLE02

 

 

 

 

 

--테이블 반환함수

 

CREATE FUNCTION HIGHEMP
(@SAL INT)
RETURNS TABLE
AS  --BEGIN..END구문이 필요없다.
RETURN(SELECT ENAME, SAL, DEPTNO FROM EMP
               WHERE SAL>@SAL)  --뷰에서는 변수를 선언을 못하는 반면 테이블 반환함수에서는 변수사용이 가능하다.

 

 

(만들어진 함수 사용)

 

SELECT * FROM HIGHEMP(1000) --반환되는 결과가 테이블 형태를 갖추었기때문에~
                                                       스칼라 함수 반환과의 차이점 : 함수명 앞에 소유자의 이름을 쓰지 않는다.

 

 

 

SELECT ENAME, SAL, D.DEPTNO, DNAME
FROM DEPT D JOIN HIGHEMP(5000) H
ON D.DEPTNO = H.DEPTNO

 

 

 

 

--다중문 함수 반환

CREATE FUNCTION HIGHEMPS
(@SAL INT)
RETURNS @TEMM TABLE
(DEPTNO INT, DNAME VARCHAR(15),ENAME VARCHAR(15))
AS
BEGIN
INSERT INTO @TEMM
SELECT E.DEPTNO,DNAME,ENAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO AND SAL>@SAL
RETURN
END

 

 

 

 

--NORTHWIND DB

<실습>
--EMPLOYEES 테이블에서 이름을 출력하되, 'SHORTNAME'으로 실행하면 LASTNAME 컬럼만 출력,
-- 'LONGNAME'으로 실행하면 직원의 전체 이름을 출력하는 함수 생성

CREATE FUNCTION EMPNAME
(@NAME VARCHAR(10))
RETURNS @TEMM TABLE
(EMPNAME VARCHAR(50))
AS
BEGIN
IF @NAME='SHORTNAME'
INSERT INTO @TEMM
SELECT LASTNAME FROM EMPLOYEES
ELSE IF @NAME='LONGNAME'
INSERT INTO @TEMM
SELECT LASTNAME+' ' +FIRSTNAME FROM EMPLOYEES
RETURN
END

 

(확인)

SELECT *FROM EMPNAME('SHORTNAME')
SELECT *FROM EMPNAME('LONGNAME')