알쓸다잡 (알아두면 쓸데 있는 다양한 잡식): 2023

2023년 12월 26일 화요일

MSSQL - CURSOR

-- select * from @TempTable order by ID

DECLARE @ID varchar(120)

-- DECLARE InfoCursor CURSOR FAST_FORWARD
DECLARE InfoCursor CURSOR FAST_FORWARD FORWARD_ONLY READ_ONLY
FOR
SELECT ID FROM @TempTable WITH(NOLOCK) ORDER BY MEM_ID
OPEN InfoCursor
FETCH NEXT FROM InfoCursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

-- 작업 구문 --

select * from @TempTable where ID = @ID

FETCH NEXT FROM InfoCursor INTO @ID
END

CLOSE InfoCursor
DEALLOCATE InfoCursor

MSSQL - 테이블 필드명 찾기 #3

SELECT 'DB명' as [DataBase명]
, c.[TABLE_NAME] AS [테이블명(물리명)]
, c.[COLUMN_NAME] AS [컬럼명(물리명)]
, (
CASE WHEN c.[CHARACTER_MAXIMUM_LENGTH] IS NOT NULL AND c.[DATA_TYPE] IN ('varchar', 'nvarchar', 'char')
THEN c.[DATA_TYPE] + '(' + CONVERT(VARCHAR, c.[CHARACTER_MAXIMUM_LENGTH]) + ')'
ELSE c.[DATA_TYPE] END
) AS [데이터타입]
, c.[IS_NULLABLE] AS [NULL여부]
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON c.[TABLE_NAME] = t.[TABLE_NAME] AND t.[TABLE_TYPE] = 'BASE TABLE'
--WHERE c.[TABLE_NAME] IN ('TB_ORDER_TAXA')
where c.[COLUMN_NAME] = '필드명'
ORDER BY c.[TABLE_NAME]

MSSQL - 프로시저, 함수 찾기 #2

SELECT *
FROM sysobjects AS a
LEFT JOIN syscomments AS b ON a.id = b.id
WHERE 1 = 1
and a.type in ('P','FN','TF')
AND b.text = 'DB명'

MSSQL - 함수 - Split_WithEmpty

CREATE FUNCTION [dbo].[fn_Split_WithEmpty]
(
@String VARCHAR (MAX),
@Delimiter VARCHAR (12)
)
RETURNS @ValueTable TABLE
(
idx int identity(1,1),
StrValue VARCHAR(MAX)
)
BEGIN
DECLARE
@NextString VARCHAR(MAX),
@Pos INT,
@NextPos INT,
@CommaCheck VARCHAR(1)

SET @NextString = ''
SET @CommaCheck = RIGHT(@String, 1)

SET @String = @String + @Delimiter

SET @Pos = CHARINDEX(@Delimiter, @String)
SET @NextPos = 1

WHILE (@pos > 0)
BEGIN
SET @NextString = SUBSTRING(@String, 1, @Pos - 1)

--IF (LEN(@NextString) > 0)
INSERT INTO @ValueTable (StrValue) VALUES (@NextString)

SET @String = SUBSTRING(@String, @pos + LEN(@Delimiter), LEN(@String))

SET @NextPos = @Pos
SET @pos = CHARINDEX(@Delimiter, @String)
END

RETURN
END

MSSQL - 함수 - Split

CREATE FUNCTION [fn_Split]
(
@String VARCHAR (MAX),
@Delimiter VARCHAR (12)
)
RETURNS @ValueTable TABLE
(
idx int identity(1,1),
StrValue VARCHAR(MAX)
)
BEGIN
DECLARE
@NextString VARCHAR(MAX),
@Pos INT,
@NextPos INT,
@CommaCheck VARCHAR(1)

SET @NextString = ''
SET @CommaCheck = RIGHT(@String, 1)

SET @String = @String + @Delimiter

SET @Pos = CHARINDEX(@Delimiter, @String)
SET @NextPos = 1

WHILE (@pos <> 0)
BEGIN
SET @NextString = SUBSTRING(@String, 1, @Pos - 1)

IF (LEN(@NextString) > 0)
INSERT INTO @ValueTable (StrValue) VALUES (@NextString)

SET @String = SUBSTRING(@String, @pos + LEN(@Delimiter), LEN(@String))

SET @NextPos = @Pos
SET @pos = CHARINDEX(@Delimiter, @String)
END

RETURN
END

MSSQL - 함수 - WorkDay(영업일) 계산

-- select [MFUNC_GetWorkDay](getdate(), -3)
-- select [MFUNC_GetWorkDay]('20231111', 3)
CREATE FUNCTION [MFUNC_GetWorkDay]
(
@startDate date,
@interval int
)
RETURNS INT
AS
BEGIN
--Declare @startDate date, @interval int
--SET @startDate = '20230607' --getdate()
--SET @interval = -3
DECLARE @holidayCalendar TABLE (CalendarDate date NULL)
DECLARE @AddDate date
if @interval is null set @interval = 0
-- 휴일만 등록
insert into @holidayCalendar (CalendarDate)
values ('20230101'), ('20230121'), ('20230122'), ('20230123'), ('20230124')
, ('20230301')
, ('20230505'), ('20230527'), ('20230529')
, ('20230606')
, ('20230815')
, ('20230928'), ('20230929'), ('20230930')
, ('20231003'), ('20231009')
, ('20231225')
, ('20240101')
, ('20240209'), ('20240210'), ('20240211'), ('20240212')
, ('20240301')
, ('20240410')
, ('20240505'), ('20240506'), ('20240515')
, ('20240606')
, ('20240815')
, ('20240916'), ('20240917'), ('20240918')
, ('20241003'), ('20241009')
, ('20241225')
--select * from @holidayCalendar
Declare @rCnt int, @cCnt int, @flag int
SET @rCnt = 0
SET @cCnt = 0
If @interval >= 0
set @flag = 1
else
set @flag = -1
set @AddDate = @startDate
while abs(@interval) > @rCnt
begin
set @cCnt = @cCnt + 1
set @AddDate = DateAdd(day, @cCnt * @flag, @startDate)
if (datepart(dw, @AddDate) = 1)
OR (datepart(dw, @AddDate) = 7)
OR exists(SELECT CalendarDate FROM @holidayCalendar WHERE CalendarDate = @AddDate)
begin
set @interval = @interval + (1 * @flag)
end
set @rCnt = @rCnt + 1
end
RETURN CONVERT(VARCHAR(10), @AddDate, 112)
END

MSSQL - 함수 - MASKING

/*
select [MASKING]('1','010-1234-1234')
select [MASKING]('1','01012341234--')
select [MASKING]('1','1234--')
select [MASKING]('2','홍길동')
select [MASKING]('3','test007@test.com')
select [MASKING]('4','12345')
select [MASKING]('5','서울시 영등포구 여의대여의동 122 abcd')
select [MASKING]('6','12345678')
select [MASKING]('8','12345')
select [MASKING]('9','1234-5678-1234-0123')
select [MASKING]('10','123-23-456789')
select [MASKING]('11','123-23-1234')
select [MASKING]('12','123456789')
select [MASKING]('16','1234567890abcdefghi1234567890abcdefghijk')
*/
CREATE FUNCTION [MASKING] (
@GUBUN VARCHAR(2),
@PARM VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @RET VARCHAR(255)
-- 전화번호
IF @GUBUN = '1'
/*
SET @RET = CASE WHEN CHARINDEX('-',@PARM) > 0 THEN
CASE WHEN CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) > 0 THEN
LEFT(@PARM, CHARINDEX('-', @PARM)) + REPLICATE( '*', CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) - 1 - CHARINDEX('-', @PARM)) +
+ '-' + RIGHT(@PARM, LEN(@PARM) - CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1))
ELSE LEFT(@PARM, CHARINDEX('-', @PARM) - 1) + '-' + '****'
END
ELSE '****'
END
*/
SET @RET = CASE WHEN CHARINDEX('--',@PARM) > 0 THEN
CASE WHEN LEN(@PARM) > 8 THEN SUBSTRING(@PARM,1,4) + '****' + SUBSTRING(@PARM, 9, LEN(@PARM)-8)
WHEN LEN(@PARM) = 8 THEN SUBSTRING(@PARM,1,4) + '****'
WHEN LEN(@PARM) <= 4 THEN '****'
ELSE SUBSTRING(@PARM,1,LEN(@PARM)-4) + '****'
END
WHEN CHARINDEX('-',@PARM) > 0 THEN
CASE WHEN CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) > 0 THEN
LEFT(@PARM, CHARINDEX('-', @PARM)) + REPLICATE( '*', CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) - 1 - CHARINDEX('-', @PARM)) +
+ '-' + RIGHT(@PARM, LEN(@PARM) - CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1))
ELSE LEFT(@PARM, CHARINDEX('-', @PARM) - 1) + '-' + '****'
END
ELSE '****'
END
-- 이름(한글)
ELSE IF @GUBUN = '2'
SET @RET = CASE WHEN LEN(@PARM) < 2 THEN '*'
WHEN LEN(@PARM) = 2 THEN LEFT(@PARM, 1) + '*'
WHEN LEN(@PARM) > 2 THEN LEFT(@PARM, 1) + '*' + RIGHT(@PARM, 1)
END
-- EMAIL
ELSE IF @GUBUN = '3'
begin
-- SET @RET = REPLICATE('*', CHARINDEX('@', @PARM) - 1) + SUBSTRING(@PARM, CHARINDEX('@', @PARM), LEN(@PARM))
---- SET @RET = REPLICATE('*', 8) + '@' + REPLICATE('*', 8)
set @RET = [dbo].[fn_Split_IndexString](@PARM,'@',1)
if (LEN(@RET) > 4)
set @RET = '****' + RIGHT(@RET, LEN(@RET)-4) + '@' + [dbo].[fn_Split_IndexString](@PARM,'@',2)
else
set @RET = '**' + RIGHT(@RET, 2) + '@' + [dbo].[fn_Split_IndexString](@PARM,'@',2)
end
-- 우편번호
ELSE IF @GUBUN = '4'
--SET @RET = SUBSTRING(@PARM,1,4) + REPLICATE('*', 3)
SET @RET = case when len(@PARM) > 2 then left(@PARM, 3) + '**' else @PARM + '**' end
-- 주소
ELSE IF @GUBUN = '5'
--SET @RET = CASE SUBSTRING(@PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ',@PARM) + 1) + 1) - 1, 1) WHEN '구'
-- THEN SUBSTRING(@PARM, 1, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM) + 1) + 1)) + REPLICATE('*', 10)
-- ELSE SUBSTRING(@PARM, 1, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM) + 1)) + REPLICATE('*', 10)
-- END
SET @RET = LEFT(@PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM, CHARINDEX(' ', @PARM) + 1) + 1))) + '*****'
-- 요양기관번호
ELSE IF @GUBUN = '6'
--SET @RET = '********'
set @RET = case when len(@PARM) > 3 then '****' + right(@PARM, LEN(@PARM)-4) else '****' + @PARM end
-- 주민번호
ELSE IF @GUBUN = '7'
SET @RET = LEFT(@PARM, 6) + '-*******'
-- 면허번호
ELSE IF @GUBUN = '8'
--SET @RET = REPLICATE('*', 5)
SET @RET = case when len(@PARM) > 2 then left(@PARM, 3) + '**' else @PARM + '**' end
-- 카드번호
ELSE IF @GUBUN = '9'
SET @RET = SUBSTRING(@PARM, 1, CHARINDEX('-', @PARM) - 1) + '-****-****-' +
RIGHT(@PARM, LEN(@PARM) - CHARINDEX('-', @PARM, CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) + 1))
-- 계좌번호
ELSE IF @GUBUN = '10'
--SET @RET = SUBSTRING(@PARM, 1, LEN(@PARM) / 2) + REPLICATE('*', 10)
SET @RET = case when len(@PARM) > 5 then left(@PARM, len(@PARM)-5) + '****' else @PARM + '****' end
-- 사업자등록번호
ELSE IF @GUBUN = '11'
--SET @RET =
-- CASE
-- WHEN LEN(@PARM) > 0 THEN
-- LEFT(@PARM, CHARINDEX('-', @PARM))
-- + REPLICATE( '*', CHARINDEX('-', @PARM, CHARINDEX('-', @PARM) + 1) - 1 - CHARINDEX('-', @PARM)) +
-- + '-' + '*'+RIGHT (RIGHT(@PARM, CHARINDEX('-', REVERSE(@PARM))-1), LEN(RIGHT(@PARM, CHARINDEX('-', REVERSE(@PARM))-1))-1)
-- ELSE ''
-- END
set @RET = case when len(@PARM) > 6 then left(@PARM, 7) + '****' when len(@PARM) = 0 then '' else @PARM + '****' end
-- ID
ELSE IF @GUBUN = '12'
--SET @RET = LEFT(@PARM, LEN(@PARM)-2) + '**'
set @RET = case when len(@PARM) > 4 then '****' + right(@PARM, LEN(@PARM)-4)
when len(@PARM) > 1 then '**' + right(@PARM, LEN(@PARM)-2)
else '*' end
-- 생년월일 (yyyy-MM-dd)
ELSE IF @GUBUN = '13'
set @RET = [dbo].[fn_Split_IndexString](@PARM,'-',1) + '-**-**'
-- IP (ver4)
ELSE IF @GUBUN = '14'
set @RET = [dbo].[fn_Split_IndexString](@PARM,'.',1) + '.' + [dbo].[fn_Split_IndexString](@PARM,'.',2) + '.***.' + [dbo].[fn_Split_IndexString](@PARM,'.',4)
-- IP (ver6)
ELSE IF @GUBUN = '15'
set @RET = case when len(@PARM) > 34 then left(@PARM, 35) + '****' else @PARM + '****' end
-- DI
ELSE IF @GUBUN = '16'
set @RET = case when len(@PARM) > 6 then left(@PARM, 7) + '*****************' else @PARM + '*****************' end
-- 법인등록번호
ELSE IF @GUBUN = '17'
set @RET = case when len(@PARM) > 6 then left(@PARM, 7) + '*******' else @PARM + '*******' end
-- 이름(영문)
ELSE IF @GUBUN = '18'
set @RET = case when len(@PARM) > 2 then left(@PARM, 3) + '*****' else @PARM + '*****' end
RETURN @RET
END

MSSQL - 함수 - Split_IndexString

CREATE Function [fn_Split_IndexString]
(
@str VARCHAR(MAX)
, @p CHAR(1)
, @n INT
)
Returns varchar(max)
AS
Begin
IF (LEN(@str) > 0 AND RIGHT(@str,1) != @p)
SET @str = @str + @p

DECLARE @new_str VARCHAR(50), @i TINYINT

SET @new_str = ''
IF (CHARINDEX(@p, @str) <> 0)
BEGIN
SET @i = 1
WHILE (CHARINDEX(@p, @str) <> 0)
BEGIN
SET @new_str = SUBSTRING(@str,1,CHARINDEX(@p,@str) - 1)
IF (@n = @i)
RETURN @new_str
--SET @str = SUBSTRING(@str,CHARINDEX(@p, @str) + LEN(@p), LEN(@str))
if (@p = ' ')
SET @str = SUBSTRING(@str,CHARINDEX(@p, @str) + 1, LEN(@str))
else
SET @str = SUBSTRING(@str,CHARINDEX(@p, @str) + LEN(@p), LEN(@str))
SET @i = @i + 1
END
END
RETURN NULL
End

2023년 12월 20일 수요일

css - CSS / 표(table) 꾸미기 / 틀 고정(행 또는 열 고정) 만들기

엑셀에 틀 고정이라는 기능이 있다. 틀 고정을 사용하면 상하 또는 좌우로 스크롤해도 항상 보이는 행 또는 열을 만들 수 있다. CSS로도 같은 효과를 낼 수 있다. 이를 구현하기 위해 사용하는 속성은 display: sticky이다.

예를 들어 다음과 같은 표가 있다고 하자.

<!doctype html>
<html lang="ko">
  <head>
    <meta charset="utf-8">
    <title>CSS</title>
    <style>
      table#jb-table th, table#jb-table td { padding: 20px 80px; border: 1px solid #444444; }
      table#jb-table th { background-color: #eeeeee; }
      table#jb-table tbody tr td:first-child { background-color: #eeeeee; }
    </style>
  </head>
  <body>
    <h1>Sticky</h1>
    <table id="jb-table">
      <thead>
        <tr>
          <th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
        <tr>
          <td>1</td><td>2</td><td>3</td><td>4</td><td>5</td><td>6</td><td>7</td>
        </tr>
      </tbody>
      <tfoot>
        <tr>
          <th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th>
        </tr>
      </tfoot>
    </table>
  </body>
</html>

웹브라우저 창 크기가 충분히 크면 모든 내용을 볼 수 있으나...

그렇지 않은 경우 상하좌우 스크롤이 생긴다.

내용을 보기 위해 스크롤 하다보면 무엇에 대한 값인지 알 수가 없다.

제1행을 고정하고 싶다면 다음 코드를 CSS에 추가한다.

table#jb-table thead { position: sticky; top: 0; }

상하 스크롤을 해도 제1행은 항상 보인다.

제1열을 고정하고 싶다면 다음 코드를 CSS에 추가한다.

table#jb-table th:first-child,
table#jb-table td:first-child { position: sticky; left: 0; }

좌우 스크롤을 해도 제1행은 항상 보인다.

제1행과 제1열을 고정하기 위해, 위에 사용했던 코드를 다 넣으면...

table#jb-table thead { position: sticky; top: 0; }
table#jb-table th:first-child,
table#jb-table td:first-child { position: sticky; left: 0; }

제1행 제1열의 셀이 가려지게 된다.

이를 해결하는 방법은 z-index를 추가하는 것이다.

table#jb-table thead { position: sticky; top: 0; z-index: 1; }
table#jb-table th:first-child,
table#jb-table td:first-child { position: sticky; left: 0; }

이제 상하좌우 스크롤을 해도 제1행과 제1열은 항상 보인다.

마지막 행도 고정하고 싶다면 다음과 같이 한다.

table#jb-table thead { position: sticky; top: 0; z-index: 1; }
table#jb-table th:first-child,
table#jb-table td:first-child { position: sticky; left: 0; }
table#jb-table tfoot { position: sticky; bottom: 0; }

제1열과 제2열을 고정하고 싶다면 다음과 같이 한다. 제2열의 left 값은 제1열의 크기에 맞게 적절히 정한다.

table#jb-table th:first-child,
table#jb-table td:first-child { position: sticky; left: 0; }
table#jb-table th:nth-child(2),
table#jb-table td:nth-child(2) { position: sticky; left: 183px; }

출처 : https://www.codingfactory.net/13033

알쓸다잡 (알아두면 쓸데 있는 다양한 잡식)