프로시저란?

데이터베이스에서의 프로시저란 프로그래머가 생성해놓은 쿼리문을 마치 하나의 메서드 형식으로 관리하는 것입니다.

실무에서는 굉장히 복잡한 쿼리문을 많이사용해요.

많은 컬럼을 조회하고 여러 테이블을 조인하고 거기다가 WHERE조건까지...

심한것은 하나의 쿼리를 만드는데 1000라인이 넘어가는경우도 종종있어요.

이렇게 장문의 쿼리를 사용할때마다 써줘야한다면 굉장히 불편할거에요.

그러므로 이 장문의 쿼리를 프로시저에 저장해주고,

쿼리문이 저장된 프로시저를 호출하여 프로그래밍을 하는것이 훨씬 효율적입니다.

프로시저 사용법

프로시저 생성 문법

프로시저 생성 예제

프로시저 1개 조회

전체프로시저 조회

프로시저 삭제

저장 프로시져 내용 검색

SELECT o.name

FROM sysobjects o JOIN syscomments c

on o.id = c.id

WHERE o.type = 'P' AND c.text LIKE '%텍스트%'

프로시져 내용 검색 말고도 트리거, 함수, 뷰테이블 검색이 가능함.

o.type 구분 확인.

날짜 함수
select getdate() <= 현재 시스템의 날짜를 가저온다. (시스템에 설정된 나라의 형식을 따른다.)

select Year(getdate()),month(getdate()),day(getdate()) <= 년,월,일 을 뽑는다

select getdate(),datename(Weekday,getdate()) <= 요일을 뽑는다

select datediff(yy,'1945-08-15',getdate()) <= 적어준 날의 년과 현재날의 년과의 차이를 출력

select datediff(dd,'1945-08-15',getdate()) <= 적어준 날의 일과 현재날의 일과의 차이를 출력

-- convert(바꿀형식, 원본데이타) ; 바꿀형식으로 데이타를 바꾼다 아래의 예는 문자형으로 바꾸어 문자와 '+' 기호를 사용하여 결합하는 예이다
select convert(char(4),year(getdate())) + '년'

--select * from insa => ex) example problem

select name, datediff(yy,ibsa_date,getdate()) as 근무년,datediff(dd,ibsa_date,getdate()) as 근무일
from insa
where datediff(dd,ibsa_date,getdate()) > 0

select name as 이름, convert(char(4), year(ibsa_date)) + '년' + convert(char(2),month(ibsa_date)) + '월' +
convert(char(2), day(ibsa_date)) + '일' + datename(weekday,ibsa_date) as 입사일 from insa

수식관련 내장함수
floor => 소수점아래 제거,
round => 반올림함수, n 자리까지 남김. 0 인경우 일의자리까지 남김
select floor(13.7), floor(-13.7), round(13.75, 1)
result -> 13 -14 13.80

select floor(13.7), floor(-13.7), round(13.75, 0)
result -> 13 -14 14.00

select floor(13.7), floor(-13.7), round(13.75, -1)
result -> 13 -14 10.00

--select replicate('*',10) -- 해당문자를 n 번 반복 예에서는 * 을 10번 출력

select name, (gi_pay + su_pay) as [급여], replicate('*', floor((gi_pay + su_pay)/100000)) as [그래프]
from insa

stuff 내장함수
select stuff('영업팀', 3, 1, '부') as stuff

select stuff('서울시', 3, 1, '특별시') as stuff

select name as [이름], stuff(buseo, 3, 1, '팀') as [팀명] from insa

select name, gi_pay, su_pay from insa order by gi_pay desc, su_pay
select * from insa order by ibsa_date desc, substring(ssn,1,6)

select * from insa order by convert(varchar(10),ibsa_date, 102) desc, substring(ssn,1,6)

select top 5 name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa

where city='서울' order by (gi_pay + su_pay ) desc

select top 5 with ties name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa where city='서울' order by (gi_pay + su_pay ) desc

select top 25 percent name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa where city='서울' order by (gi_pay + su_pay ) desc

select top 25 name, city, (gi_pay + su_pay) as [보수], ssn as [주민등록번호] from insa
where city='서울' order by (gi_pay + su_pay ) desc

SELECT * FROM INSA WHERE GI_PAY >= 1000000 AND GI_PAY <= 2000000

SELECT * FROM INSA WHERE GI_PAY BETWEEN 1000000 AND 2000000

--SELECT NAME AS 이름, IBSA_DATE AS 입사일 FROM INSA WHERE IBSA_DATE LIKE '%2004%'

SELECT NAME AS 이름, IBSA_DATE AS 입사일 FROM INSA
WHERE IBSA_DATE BETWEEN '2004-01-01' AND '2004-12-31'

문자함수
LEFT, RIGHT
LEFT는 지정해준 자릿수만큼 왼쪽에서부터 문자열을 반환한다.
당연히 RIGHT는 반대이다.
형식 : LEFT(문자, 자릿수)

LTRIM, RTRIM
LTRIM은 문자열의 왼쪽 공백을 제거한다. 역시 RTRIM은 반대일 경우 사용된다.
형식: LTRIM(문자)

LEN
LEN함수는 문자열에서 문자의 갯수를 추출한다.
형식: LEN(문자)
Len함수는 문자 뒤쪽의 공백은 문자로 계산하지 않는다.

UPPER, LOWER
UPPER는 소문자를 대문자로, LOWER는 대문자를 소문자로 바꾼다.
형식: UPPER(문자)

REVERSE
REVERSE는 문자열을 반대로 표시한다.
형식: REVERSE(문자열)

REPLACE
REPLACE함수는 지정한 문자열을 다른 문자열로 바꾸어준다.
형식: R-PLACE(문자, 타겟문자, 바꿀문자)

REPLICATE
REPLICATE함수는 문자열을 지정된 횟수만큼 반복한다.
형식: REPLICATE(문자, 횟수)

STUFF
STUFF함수는 문자열에서 특정 시작위치에서 지정된 길이만큼 문자를 바꾸어준다.
형식: STUFF(문자, 시작위치, 길이, 바꿀문자)

SUBSTRING
SUBSTRING은 STUFF와 비슷하지만 문자를 바꾸는 것이 아니라 그 문자를 반환한다.
형식: SUBSTRING(문자, 시작위치, 길이)

PATINDEX, CHARINDEX
PATINDEX와 CHARINDEX는 문자열에서 지정한 패턴이 시작되는 위치를 뽑아준다.
형식: PATINDEX(문자패턴, 문자) - 문자패턴은 Like 사용과 같다.
형식: CHARINDEX(문자패턴, 문자) - 문자패턴은 일반형식을 사용한다.

SPACE
SPACE함수는 지정한 수 만큼 공백을 추가한다.
형식: SPACE(횟수)

시간 및 날짜 함수
GETDATE()
GETDATE()는 현재 시간을 표시해준다.
DATEADD
DATEADD함수는 날자에 지정한 만큼을 더한다.
형식: DATEADD(날자형식, 더할 값, 날자)

DATEDIFF
DATEDIFF함수는 두 날자사이의 날자형식에 지정된 부분을 돌려준다.
형식: DATEDIFF(날자형식, 시작 날자, 끝 날자)

DATENAME
DATENAME함수는 지정한 날자의 날자형식의 이름을 돌려준다.
형식: DATENAME(날자형식, 날자)

DATEPART
DATEPART함수는 날자에서 지정한 날자형식부분만 추출해줍니다.
형식: DATEPART(날자형식, 날자)
주일은 일요일부터 1로 시작해서 토요일날 7로 끝나게 된다.

NULL 함수

ISNULL
ISNULL은 NULL값을 대체값으로 바꾼다.
형식: ISNULL(NULL값, 대체값)

NULLIF
NULLIF함수는 두개의 표현식을 비교하여 같으면 NULL을 반환한다.
형식: NULLIF(표현식1, 표현식2)

COALESCE
COALESCE함수는 NULL이 아닌 첫번째 표현식이 반환된다.
형식: COALESCE(표현식)

GETANSINULL
GETANSINULL은 데이터베이스의 기본 NULL 상태를 표시해준다.
형식: GETANSINULL(데이터베이스 이름)

테이블의 칼럼정보 보기
exec sp_columns sales

/* Substring 처리 */
select payterms from sales
select substring(payterms,1,3) from sales

/* 문자열연결 연산자 '+' */
select substring(payterms,1,4)+ ' 테스트' from sales

/*현재날짜 */
select getdate()

/* 1년후날짜 */
select getdate() + 365

/* 날짜데이타 변경 CONVERT(변경될문자열의 데이타타입및길이, 날짜데이타, 출력형식) */
select convert(varchar(30), getdate(), 2)

select convert(varchar(30), getdate(), 102)

/* YYYYMMDD 형식으로 가져오기( Varchar(8)로 설정 ) */
select convert(varchar(8), getdate(), 112)

/* 해당 [월] 만 가져오기 */
select datepart(mm,getdate())
select month(getdate())

/* 현재일자에 20개월추가 (월 추가) */
select dateadd(mm,20,getdate())

/* 현재일자에 100일 후의 날짜 */
select dateadd(dd,100,getdate())

/* Oracle 에서의 NVL함수에 대응하는 MS-SQL함수 */
NVL(price,0) ===> ISNULL(price,0)

/* 판매수량이 30권 이상인책에 대하여 각 서적별 판매수량총계 조회 */
select title_id as '서적코드' , sum(qty) as '판매수량' from sales
group by title_id
having sum(qty) >= 30

/* COMPUTE & COMPUTE BY 사용법 */
select type, title_id, price from titles
order by type
compute avg(price) by type
해당 select칼럼이 별로 Average값을 구해준다. ( Order by 반드시 필요 )

select type, title_id, price from titles
order by type
compute avg(price) by type
마지막행에 Select된 전체칼럼중 Average(price)칼럼에 대해 평균값을 구해준다.

/* Rollup & Cube */
select type, avg(price) from titles group by type --각 타입별 평균
select avg(price) from titles -- 전체평균

데이터베이스 데이터 정렬 설정 또는 변경 (MSDN, SQL Server 2014 기준)

http://msdn.microsoft.com/ko-kr/library/ms175835.aspx

1. DB 생성시에는 언제나 Collation을 설정하도록 되어 있습니다.

기본값 때문에 잊고 지낼 때가 많지만 모든 DB는 자신의 Collation을 보유하고 있습니다.

CREATE DATABASE

MyOptionsTest

COLLATE

korean_wansung_ci_as

2. 생성된 DB의 Collation 변경

Collation은 생각보다 쉽게 변경할 수 있습니다.

ALTER DATABASE

MyOptionsTest

COLLATE

korean_wansung_ci_as

3. 테이블 컬럼의 Collation 변경

MSSQL에서는 DB 외에 char, nvarchar, text 등의 필드에도 Collation이 설정 됩니다.

ALTER TABLE

MyTestTable

ALTER COLUMN

MyColumnName varchar(50)

COLLATE

korean_wansung_ci_as

주의) 컬럼의 Collation을 변경 할 때는 COLLATE만 단독으로 변경하는 구문이 지원되지 않기 때문에

필드 정의 등 복잡한 부분 들까지 함께 입력 해 주어야 합니다.

4. SINGLE USER MODE

작업을 진행하다 보면 겪게 되는 Collation 변경의 진짜 장벽은 배타적 락 문제 입니다.

다른 유저가 변경되어야 하는 DB를 사용중인 경우 SQL Server는 Collation 변경을 허용하지 않습니다.

이 경우 해당 DB를 SINGLE USER MODE로 변경하는 작업이 추가로 필요 합니다.

데이터베이스 데이터 정렬 설정 또는 변경(MSDN, SQL Server 2014 기준)

http://msdn.microsoft.com/ko-kr/library/ms175835.aspx

-- SINGLE USER MODE 변경

ALTER DATABASE

MyTestDB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

-- 작업 진행

-- MULTI USER MODE 복귀 (기본값)

ALTER DATABASE

MyTestDB

SET MULTI_USER

Image -> Text

CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX),field))

varbinary -> Text

CONVERT(VARCHAR(MAX),convert(varbinary(4000),field))

XML -> Text

cast(field as xml) as Field_Name

SELECT @@VERSION

SELECT
      SERVERPROPERTY('ProductVersion') AS ProductVersion,
      SERVERPROPERTY('ProductLevel') AS ProductLevel,
      SERVERPROPERTY('Edition') AS Edition,
      SERVERPROPERTY('ISClusterd') AS Clusterd;
  GO

  SP_READERRORLOG

  HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names

데이터베이스 위치 변경하는 방법

1. 주 데이터베이스 파일이 어떤 것인지 Enterprise Manager 혹은 sp를 이용하여 확인
해당 SQL 서버의 데이터베이스를 선택하고 등록정보를 봅니다.

예) pubs 데이터베이스 정보 보기

sp_helpdb 'pubs '

C:\MSSQL\Data\pubs.mdf
C:\MSSQL\Data\pubs.ldf

2. Query Manager 에서 다음 명령을 수행 후 pubs DB 분리

sp_detach_db pubs

3. 주 데이터베이스 C 드라이브의 mdf, ldf 파일을 D 드라이브에 원하는 곳으로
copy 합니다. 예제에서는 각각

D:\MSSQL\Data\pubs.mdf
D:\MSSQL\Data\pubs.ldf

에 옮기는 것으로 하겠습니다.

4. 파일 copy 가 끝나면, Query Manager 에서 다음 명령을 수행합니다.

sp_attach_db 'pubs','D:\MSSQL\Data\pubs.mdf','D:\MSSQL\Data\pubs.ldf'

5. Enterprise Manager를 기동하여 아까 1번에서 메모했던 속성과 비교합니다.

alter database 사용시는 아래 사이트 참고

http://hyunki1019.tistory.com/136

-- Select Into 사용 방법

1. Select * Into New_Table_Name From Org_Table_Name

2. Select * Into New_Table_Name From Org_Table_Name

       Where Site='한국'

-- Insert Into 사용 방법

1. 두 테이블의 컬럼이 같을때 SELECT INSERT 하기

두개의 테이블간에 컬럼이 table_a 와 table _b 처럼 완전히 일치할 때는 아래와 같이 간단하게 처리할 수 있습니다.

예1) INSERT INTO table_b SELECT * FROM table_a

위의 예1) 에서는 table_a 의 레코드 전체를 table_b 로 삽입(INSERT) 시킵니다. 만약에 조건을 주어 데이터를 삽입(INSERT) 시키려 한다면 아래와 같이 뒤에 WHERE 절만 추가해 주시면 됩니다.

예2) INSERT INTO table_b SELECT * FROM table_a WHERE seq = 2

위의 SQL 문은 table_a 에서 seq 의 값이 2 인 레코드를 table_b 에 INSERT 시킵니다.

2 . 두 테이블간의 컬럼이 일치하지 않을때 SELECT INSERT 하기
두개의 테이블간에 컬럼이 table_a 와 table _c 처럼 일치하지 않을때는 아래와 같이 컬럼명을 지정해 주시면됩니다.

예3) INSERT INTO table_c ( seq, Name, Email, Idate ) SELECT seq, Name, Email, Idate FROM table_a

위의 예3) 에서는 table_a 의 컬럼중 seq, Name, Email, Idate 컬럼을 table_c 에 Insert 시키고 있습니다. 한가지 주의
할 것은 table_c 의 구조가 table_b 와 다른 만큼 INSERT 될 컬럼이 SELECT 되는 컬럼과 일치하지 않으면 아래와 같은
에러가 발생하게 됩니다.

cA_Table
SET A_Column_01 = b.B_Column_01
FROM A_Table a
    INNER JOIN B_Table b
    ON a.A_Key = b.B_Key
WHERE a.A_Key is NOT NULL

예를 들어, A_Table의 A_Column_01의 값을 B_Table의 B_Column_01 값으로 업데이트 하려한다. 단, A_Table의 A_Key와 B_Table의 B_Key가 같은 항목에 한해서만 변경이 이루어져야 한다.
* 위와같이 Join이 필요한 업데이트 구문은 다음과 같다.

Select object_name(id) From syscomments Where text like '%테이블 명 또는 필드명%'
Group by object_name(id)
Order by object_name(id)