자바 이야기: query

2020년 3월 12일 목요일

MySQL schema query with comment

Table

SELECT * FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE TABLE_NAME LIKE 'm_%'

Column

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbname'
AND TABLE_NAME LIKE 'T_%'
ORDER BY TABLE_NAME ASC, ORDINAL_POSITION ASC

2011년 8월 4일 목요일

[oracle] 컬럼정보 조회

SELECT

A.TABLE_NAME, NVL(A.COMMENTS, '') AS T_CON,

B.COLUMN_NAME, B.DATA_PRECISION, B.DATA_SCALE,

DECODE(B.NULLABLE, 'Y', 'NULL', 'N', 'NOT NULL') AS NOT_NULL,

NVL(C.COMMENTS, '') AS COL_CON,

B.DATA_TYPE, B.DATA_LENGTH, B.COLUMN_ID

, (

SELECT DECODE(UC.CONSTRAINT_TYPE, 'P',POSITION , '0') PRIMARY_KEY

FROM USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC

WHERE UCC.TABLE_NAME = UC.TABLE_NAME

AND UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME

AND UC.CONSTRAINT_TYPE = 'P'

AND UCC.TABLE_NAME = A.TABLE_NAME

AND UCC.COLUMN_NAME = B.COLUMN_NAME

) PRIMARY_KEY

FROM

USER_TAB_COMMENTS A, USER_TAB_COLUMNS B, USER_COL_COMMENTS C

WHERE

A.TABLE_NAME = B.TABLE_NAME

AND A.TABLE_NAME = C.TABLE_NAME

AND B.COLUMN_NAME = C.COLUMN_NAME

AND A.TABLE_NAME = 'EMP'

ORDER BY A.TABLE_NAME, B.COLUMN_ID

2011년 8월 3일 수요일

[MSSQL] 테이블 컬럼조회 쿼리

-- 테이블 리스트

SELECT * FROM INFORMATION_SCHEMA.TABLES

;

-- 컬럼 상세 정보

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'SQUTY02TT'

ORDER BY 5

;

[MSSQL] 동적쿼리를 이용한 프로시저

프로시저 소스

ALTER PROCEDURE SP_SQUTY12PL_WITH_OLD_000
@year nvarchar(8)
, @season nvarchar(2)
, @area nvarchar(2)
, @agent nvarchar(10)
, @bokjong nvarchar(8)
AS
BEGIN
DECLARE
@sql nvarchar(4000)
, @param nvarchar(400)
, @yyyyMinus1 nvarchar(8)
, @yyMinus1 nvarchar(4)
, @yyMinus2 nvarchar(4)
, @yyMinus3 nvarchar(4)
, @yySeason nvarchar(6)
set @param = '@p_year nvarchar(8)
, @p_season nvarchar(2)
, @p_area nvarchar(2)
, @p_agent nvarchar(2)
, @p_yyyyMinus1 nvarchar(8)
, @p_yyMinus1 nvarchar(4)
, @p_yyMinus2 nvarchar(4)
, @p_yyMinus3 nvarchar(4)
, @p_yySeason nvarchar(6)
, @p_bokjong nvarchar(8)
'
set @yySeason = substring(@year, 3, 2) + @season
set @yyyyMinus1 = convert(nvarchar(4), @year - 1)
set @yyMinus1 = convert(nvarchar(4), substring(@year, 3, 2) - 1)
set @yyMinus2 = convert(nvarchar(4), substring(@year, 3, 2) - 2)
set @yyMinus3 = convert(nvarchar(4), substring(@year, 3, 2) - 3)
set @sql = ' SELECT
T1.AREA
, ISNULL(SUJU_QTY, 0) SUJU_QTY, ISNULL(SUJU_AMT, 0) SUJU_AMT
, ISNULL(CHGO_QTY, 0) CHGO_QTY, ISNULL(CHGO_AMT, 0) CHGO_AMT
, ISNULL(INVT_QTY, 0) INVT_QTY, ISNULL(INVT_AMT, 0) INVT_AMT
, ISNULL(CHGO_QTY, 0) - ISNULL(INVT_QTY, 0) SALE_QTY
, ISNULL(CHGO_AMT, 0) - ISNULL(INVT_AMT, 0) SALE_AMT
FROM (
SELECT * FROM (
SELECT CODE AS AREA, SERIAL FROM MCODE02TT WHERE GRCODE = ''101''
) T1
) T1 LEFT OUTER JOIN (
SELECT
AREA, SUM(T1.SUJU_QTY) SUJU_QTY, SUM(T1.SUJU_AMT) SUJU_AMT
FROM (
SELECT
T1.AREA, T1.ORDERNO, T1.SUJU_QTY
, T1.SUJU_QTY * ISNULL((SELECT CHULGOGA CHULGOGA FROM SCHGO01TT X WHERE X.SEASON=T1.SEASON AND X.PUMBUN = T1.PUMBUN),0) SUJU_AMT
FROM SMAST01TT T1 INNER JOIN SQUTY01TT T2
ON T1.ORDERNO = T2.ORDERNO
WHERE T2.QTY_GUBUN = ''1''
AND SEASON = @p_yySeason '
if @bokjong is not null or @bokjong <> ''
begin
set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
end
set @sql = @sql + ') T1
GROUP BY AREA
) T2 ON T1.AREA = T2.AREA
LEFT OUTER JOIN (
SELECT
AREA, SUM(CHGO_QTY) AS CHGO_QTY, SUM(CHGO_QTY * CHGO_AMT) CHGO_AMT
FROM (
SELECT AREA, SIZE1+SIZE2+SIZE3+SIZE4+SIZE5+SIZE6+SIZE7+SIZE8+SIZE9+SIZE10+SIZE11+SIZE12+SIZE13+B_QTY1+B_QTY2+B_QTY3 AS CHGO_QTY
, ISNULL((SELECT TOP 1 CHULGOGA FROM SMAST01TT X INNER JOIN SCHGO01TT Y ON X.PUMBUN = Y.PUMBUN AND X.SCHOOL = T1.SCHOOL AND X.BOKJONG = T1.BOKJONG WHERE X.SEASON = @p_yySeason),0) AS CHGO_AMT
FROM SINVT01TT T1
WHERE YEAR = @p_yyyyMinus1 AND SEASON = @p_season AND S_YEAR IN (@p_yyMinus1, @p_yyMinus2, @p_yyMinus3) '
/* AND T1.BOKJONG = ''C'' */
if @bokjong is not null or @bokjong <> ''
begin
set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
end
set @sql = @sql + ') T1
GROUP BY AREA
) T3
ON T1.AREA = T3.AREA
LEFT OUTER JOIN (
SELECT
AREA, SUM(INVT_QTY) AS INVT_QTY, SUM(INVT_QTY * INVT_AMT) INVT_AMT
FROM (
SELECT AREA, SIZE1+SIZE2+SIZE3+SIZE4+SIZE5+SIZE6+SIZE7+SIZE8+SIZE9+SIZE10+SIZE11+SIZE12+SIZE13+B_QTY1+B_QTY2+B_QTY3 AS INVT_QTY
, ISNULL((SELECT TOP 1 CHULGOGA FROM SMAST01TT X INNER JOIN SCHGO01TT Y ON X.PUMBUN = Y.PUMBUN AND X.SCHOOL = T1.SCHOOL AND X.BOKJONG = T1.BOKJONG WHERE X.SEASON = @p_yySeason),0) AS INVT_AMT
FROM SINVT01TT T1
WHERE YEAR = @p_year AND SEASON = @p_season AND S_YEAR IN (substring(@p_year, 3, 2), @p_yyMinus1, @p_yyMinus2) '
/* AND T1.BOKJONG = ''C'' */
if @bokjong is not null or @bokjong <> ''
begin
set @sql = @sql + ' AND T1.BOKJONG = @p_bokjong '
end
set @sql = @sql + ') T1
GROUP BY AREA
) T4
ON T1.AREA = T4.AREA
ORDER BY SERIAL
'
exec SP_EXECUTESQL @sql, @param, @p_year=@year, @p_season=@season, @p_area=@area, @p_agent=@agent, @p_yyyyMinus1=@yyyyMinus1, @p_yyMinus1=@yyMinus1, @p_yyMinus2=@yyMinus2, @p_yyMinus3=@yyMinus3, @p_yySeason=@yySeason, @p_bokjong=@bokjong
END

테스트

USE [skoolooks_erp]
GO

DECLARE@return_value int

EXEC@return_value = [dbo].[SP_SQUTY12PL_WITH_OLD_000]
@year = N'2012',
@season = N'F',
@area = null,
@agent = null,
@bokjong = null

SELECT'Return Value' = @return_value

GO

2011년 8월 2일 화요일

MySQL 스키마 조회 쿼리

* 테이블 정보 조회
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = SCHEMA()

SCHEMA()은 현재 테이블스페이스(?)를 의미 하는 듯...
문자열로 검색도 가능, 대소문자는 구분 안하는듯..

* 컬럼 정보 조회
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = '<TABLE_NAME>'
ORDER BY ORDINAL_POSITION

테이블명 대소문자는 구분 안하는 듯..

* 컬럼 코멘트 추가하는 쿼리
ALTER TABLE <TABLE_NAME> CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> VARCHAR(10) COMMENT '<NEW COMMENT>';

* 테이블 생성시 코멘트 추가
CREATE TABLE TB_USER (
USER_ID VARCHAR(32) NOT NULL COMMENT '사용자 아이디',
PRIMARY KEY (USER_ID)
)
COMMENT = '사용자 정보'
;