DB를 만들기 위한 간단한 식
USE [CHUN]
GO
/****** Object: Table [dbo].[주문내역] Script Date: 04/15/2016 17:36:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[주문내역](
[주문번호] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[주문자] [varchar](50) NULL,
[구매물건] [varchar](20) NULL,
[구매수량] [int] NULL,
)
GO
트랜젝션을 이해하기
use CHUN
go
declare @test int = 1
begin tran
insert 주문내역 values('강감찬', '핸드폰', 4)
if @test != 1
begin
print '아싸 잘된다'
rollback tran
end
else
begin
print '뭐지??'
commit tran
end
go
select * from 주문내역
설명
USE CHUN
- 어떤 DB를 사용할껀지 정해주는 부분
DECLARE
- 지역변수를 선언하는 부분 ( 여기서만 사용할 변수하나 만들기 )
- 이름은 "@[이름]" 식으로 작성하고 먼저 기본값을 주고 싶으면 저렇게 넣어도됨
BEGIN TRANSACTION
- 줄여서 "BEGIN TRAN"으로 적어도 됨
- 커밋을 따로 입력하기 때문에 INSERT, UPDATE, DELETE 등에 자주 쓰이고
몇개를 입력하든 DB입장에서는 한번에 처리가 됨
- "COMMIT TRAN" or "ROLLBACK TRAN" 두 개를 꼭 써줘야함
- 문제가 생기는걸 함수나 조건을 통해서 미리 파악하고 롤백으로 수행을 안하게 하는 것
프리시저 생성과 사용
프리시저의 사용이유
- 일반적인 쿼리를 날리는 것보다 속도가 빨라짐
- 그래서 자주사용하는 간단한 쿼리들을 미리 만들어 놓으면 편함
- 보안 관리를 강화
- 네트워크에서 오고 가는 긴 SQL문의 네트워크 트래픽을 줄인다
- 모듈식 프로그래밍
프리시저 생성
USE CHUN
GO
CREATE PROCEDURE hr_address
@OD1 INT
AS
SELECT TOP(10) *
FROM 주문내역
WHERE 주문번호 = @OD1
GO
프로그래밍 기능 -> 저장 프로시저 안에 파일이 생김
프리시저 사용
설명
CREATE PROCEDURE hr_address
- hr_address 라는 이름으로 프리시저를 만들어라
@OD1 INT
- 실행되면서 int 변수하나를 받을꺼야~
SELECT TOP(10) * FROM 주문내역 WHERE 주문번호 = @OD1
- 실행되면 보여질 값의 쿼리
EXEC hr_address 1
- EXECUTE을 줄여서 EXEC만 적어도 됨
- hr_address 라는 프리시저를 실행해라
- "1" 이건 던져줄 int 매개변수
트리거란?
- 어떤 이벤트에 따라 자동으로 실행되는 프로시저
- DML : INSERT, UPDATE, DELETE / DDL : CREATE,DROP,ALTER
- 하나의 테이블(뷰)에 작성할 수 있다
- 트랜잭션의 일부로 처리된다.
- 데이터의 무결성을 유지하기 위해 사용된다
- 많이 쓰면 시스템의 성능을 저하시키니 꼭 필요한곳에만 사용!
트리거의 생성
USE CHUN
GO
CREATE TRIGGER testTRG
ON 구매내역
AFTER INSERT
AS
PRINT('트리거가 작동 했네요');
GO
트리거의 사용
- 인서트가 되면 자동으로 동작됨
USE CHUN
GO
INSERT INTO 구매내역 VALUES(2,'사랑의 단상', '롤랑 바르트', '2007-07-15')
SELECT * FROM 구매내역
GO
설명
CREATE TRIGGER testTRG
- "testTRG"라는 이름으로 트리거를 만들꺼야
ON 구매내역
- "구매내역"이라는 테이블 또는 뷰에다가 작성할꺼야
AFTER INSERT
- INSERT가 동작 한 다음에 동작될꺼야
AS
PRINT('트리거가 작동 했네요');
- 이런 동작을 할꺼야
트리거의 경로
- DML의 경우에는 선택한 테이블안에 트리거 폴더에 있음
- DDL의 경우 루트 -> 서버 개체 -> 트리거 안에 들어감
DDL 트리거의 생성 예시
USE CHUN
GO
CREATE TRIGGER DDLTrg_Test
ON DATABASE
AFTER DROP_TABLE
AS
PRINT(' 테이블 삭제하면 미워할 거야.')
ROLLBACK TRANSACTION;
GO
중첩 및 재귀 트리거
- 이건 그냥 인터넷 찾아보거나 다시 책보자
- 중첩은 하나 실행되면서 다른 트리거를 실행하는것
- 재귀는 자기가 실행하면서 다시 자기를 부르는것! 그래서 중간에 안빠져 나가면 롤백됨
함수를 써보자!!
기본 제공 함수
- 궁금한건 구글링을 할것!
- 집계함수와 순위 함수는 자주 사용할 것으로 예상되어 일단 써 놓음
1. 행 집합 함수
- 음.... 당장 몰라도 될듯
2. 집계 함수
- AVG(), MIN(), MAX(), SUM(), COUNT(), COUNT_BIG()
STDEV(), STDEVP(), GROUPING(), VAR(), VARP(), CHECKSUM_AGG()
3. 순위 함수
- RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER()
4. 스칼라 함수
- 유형이 많아서 따로 정리를...
사용자 정의 함수
- 모듈별 프로그래밍 가능
- 함수의 빠른 실행이 가능
- 네트워크 트래픽을 줄일 수 있음
- 리턴받는 함수의 유형별로 나누어짐 (단일 값/스칼라 값, 여러 행/테이블, 데이터 액세스)
- 함수 생성 모양
CREATE FUNCTION [스키마 이름 .] 생성할 함수 이름(
[ { @입력 받는 매개변수이름} {데이터 형식} ] )
RETURNS 반환할 데이터 형식
AS
BEGIN
DECLARE { @지역변수 이름 } { 데이터 형식 }
생성할 T-SQL 문
RETURN { @지역변수 이름 }
END
생성예제
CREATE FUNCTION dbo.adchangete (
@adyear INT)
RETURNS INT
AS
BEGIN
DECLARE @ychange INT
SET @ychange = @adyear + 2333
RETURN @ychange
END
사용예제
SELECT dbo.adchangete(2007) AS 단기
GO
설명
CREATE FUNCTION dbo.adchangete (
- dbo.adchangete 라는 함수 생성 저 앞에 "dbo"이와같이 스키마 이름 꼭 필요함!
@adyear INT)
- 입력받을 변수이름과 형식을 정해줌
RETURNS INT
- 반환되는 값이 인트임!
BEGIN ~ END
- 함수가 계산 및 연산을 하는부분
SELECT dbo.adchangete(2007) AS 단기
- 실행시 앞에 스키마 이름 꼭!! 써주고~ "(2007)" 이렇게 값을 던져준다
결과값을 테이블로도 돌려 받을 수 있다!
간단한 예제를 첨부~
생성
USE AdventureWorks
GO
CREATE FUNCTION dbo.productfind (
@prdt INT)
RETURNS TABLE
AS
RETURN( SELECT ProductID, ProductNumber, Color,
SafetyStockLevel, ReorderPoint
FROM Production.Product
WHERE ProductID < @prdt)
GO
실행
USE AdventureWorks
GO
SELECT * from dbo.productfind(320)
GO
정의된 다른 함수들
- 필요하면 나중에 구글링을 하자..
- 수치연산 함수
- 문자 함수
- 날짜 함수
- 변환 함수
- 기타 함수
