'DB'에 해당되는 글 44건

SUBSTRB

- 특정 문자열의 BYTE 단위로 일부만 추출 

ex)

SELECT 'TEST테스트', 

        SUBSTRB('TEST테스트', 1, 4), 

        SUBSTRB('TEST테스트', 2, 4), 

        SUBSTRB('TEST테스트', 3) 

FROM DUAL; 

ALTER TABLE TEMP_TEST ADD (F VARCHAR(10));

-- 추가된 컬럼에는 기본값으로 널값이 들어갑

ALTER TABLE TEMP_TEST ADD (F VARCHAR(10) DEFAULT 'AA');

-- 추가된 컬럼에는 기본값으로 'AA'값이 들어감

ALTER TABLE TEMP_TEST ADD (G VARCHAR(10) NULL);

-- NULL이란 키워드를 사용하면 에러발생

 

ALTER TABLE TABLE_NAME ADD (COLUMN_NAME DATA_TYPE NOT NULL);

-- 해당 테이블에 로우가 없다면 정상실행

-- 해당 테이블에 로우가 있다면 에러발생

 

ALTER TABLE TEMP_TEST ADD (E VARCHAR(10) NOT NULL);

-- 현재 입력된 로우가 없다면 정상실행 로우가 있다면 에러발생

-- 위의 명령이 아래상황 일때는 실행됨

CREATE TABLE TEMP_TEST1

(A CHAR(2), B CHAR(2) NOT NULL);

COMMIT;

ALTER TABLE TEMP_TEST1 ADD C CHAR(2) NOT NULL;

SELECT * FROM TEMP_TEST1;

 

ALTER TABLE T_RA020 ADD RES_CD CHAR(2);

-- 기존에 로우가 있다면 추가한 컬럼값은 기본값이 널값으로 들어감

ALTER TABLE T_RA020 ADD POS_CD CHAR(2) DEFAILT '11';

-- 기존에 로우가 있다면 추가한 컬럼값은 기본값이 '11'값으로 들어감

 

 

 

컬럼 수정

 

ALTER TABLE T_KA031 MODIFY (FILE_NM_C CHAR(200) NOT NULL);

-- 기존에 있는 값중에 널값이 있다면 에러발생

-- 기존에 있는 값중에 널값이 없다면 정상실행

-- file_nm_c의 컬럼이 null인데 위의 명령어 쓰면 에러발생 아래의 명령어 써야 정상실행

ALTER TABLE t_bc016 MODIFY etc_remark CHAR(100);

 

ALTER TABLE T_KA031 MODIFY FILE_NM_C CHAR(200) NOT NULL;

-- 위의 명령과 같은 역할을 한다.

ALTER TABLE T_KA031 MODIFY FILE_NM_C CHAR(200) NULL;

-- 에러발생

ALTER TABLE t_ba060 MODIFY (dcsn_idea lvarchar(1000) NOT NULL);

-- 1000바이로 수정

 

char(300)이면 memo컬럼으로 변경됨

 

※ 인포믹스에서는 ALTER 명령후에 COMMIT 처리를 꼭 해야한다.

※ 인포믹스에서는 NOT NULL 제약조건을 안쓰면 기본적으로 NULL로 설정된다.

 

ALTER TABLE 명령시 "could not open database table" 에러출력 대처방법

 

테이블에 ALTER TABLE 명령시 "could not open database table" 에러가 발생하면 현재 

테이블을 티맥스 서비스 파일에서 물고 있거나 제우스 웹서버에서 물고 있는중이므로 

티맥스 서버와 제우스 웹서버를 내렸다가 실행후 다시 올리면 된다.

SELECT ORG_CD,
       UPP_ORG_CD,
       ORG_NM, 
       TO_NUMBER(ORG_CD,'XXXXXXXXXXXXXXX') AS ORG_CD_INT, 
       TO_NUMBER(UPP_ORG_CD,'XXXXXXXXXXXXXXX') AS UPP_ORG_CD_INT 
  FROM ecm006ct

alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;

Oracle 11g R2에는 디스크 공간을 확보하는 새로운 기능이 있다.

새로운 파라미터인 "DEFERRED_SEGMENT_CREATION"이 추가되었는데 초기값은 "TRUE"로 되어 있다.

이 파라미터는 exp를 통해 스키마를 export할 때 영향을 준다.

문제는 일반적인 exp 명령어를 이용해 export하면 빈 테이블들은 백업되지 않는다는 것이다.

Data Pump(expdp)를 이용한 export는 문제가 없다.

이 기능을 비활성화 시키기 위해 아래와 같이 파라미터의 값을 변경하면된다.

SQL> alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;

이 기능을 적용한 후에 생성된 빈 테이블을은 백업이 잘되지만 이미 생성되어 있던 테이블들은 적용되지 않으므로 

tablespace를 이동하거나 테이블을 재생성해야 한다.

이제, 파라미터 변경 후에 생성된 테이블들은 exp를 통해서도 잘 백업되어진다.

CREATE TABLE dbo.tbllb_DBoard (
    idx          INT NOT NULL, 
    lb_name      VARCHAR(10) NOT NULL, 
    lb_SCate     TINYINT NOT NULL CONSTRAINT DF_tbllb_DBoard_lb_SCate DEFAULT (0), 
    lb_news      BIT NOT NULL, 
    m_id         VARCHAR(20), 
    Umame        VARCHAR(20) NOT NULL, 
    eMail        VARCHAR(50), 
    Title        VARCHAR(100) NOT NULL, 
    TagFlag      BIT NOT NULL, 
    Content      TEXT NOT NULL, 
    pwd          VARCHAR(10), 
    readNum      INT NOT NULL CONSTRAINT DF_tbllb_DBoard_readNum DEFAULT (0), 
    rRef         SMALLINT, 
    rLevel       SMALLINT, 
    rStep        SMALLINT, 
    file1        VARCHAR(50), 
    file1_size   INT, 
    file1_width  INT, 
    file2        VARCHAR(50), 
    file3        VARCHAR(50), 
    writeday     DATETIME NOT NULL CONSTRAINT DF_tbllb_DBoard_writeday DEFAULT (getdate()), 
 CONSTRAINT idx_board 
   PRIMARY KEY NONCLUSTERED (idx ASC, lb_news DESC, rRef DESC, rStep ASC) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE dbo.tbllb_DBoard 
    WITH CHECK ADD CONSTRAINT FK_tbllb_DBoard_tbllb_config
      FOREIGN KEY ( lb_name )
        REFERENCES dbo.tbllb_config ( lb_name )
;






CREATE PROCEDURE [Proc_lb_DList] 
	@lb_name		VarChar(10),
	@rlb_SCate		VarChar(50),
	@SearchF		VarChar(30),
	@SearchS		VarChar(50),
	@npage		INT,
	@lb_CateName		VarChar(50) 	OUTPUT,
	@lb_Nickname		VarChar(50) 	OUTPUT,
	@lb_open		Char(1)	 	OUTPUT,
	@lb_design		Char(28) 	OUTPUT,
	@lb_record		INT		OUTPUT,
	@lb_width		VARCHAR(5)	OUTPUT,
	@szRecordCount	INT		OUTPUT,
	@szPageCount		INT		OUTPUT,
	@MsgFlag		SMALLINT	OUTPUT,
	@Msg			VARCHAR(50)	OUTPUT
AS
	DECLARE 	@sql 		varchar(8000)
	DECLARE 	@sql2 		varchar(1000)
	DECLARE 	@sql3 		nvarchar(4000)  ---NvarChar형식이여야 함.
	DECLARE 	@p 		nvarchar(100)
	DECLARE 	@sum_cnt 	INT -- output 파라메터를 위한 선언
	SET @sql=''
	SET @sql2=''
	SET @SearchS=rTrim(ltrim(@SearchS))
		IF @rlb_SCate<>99 BEGIN
			Set @sql2=@sql2 + ' and ((lb_news=0 and lb_SCate=' + Convert(Varchar(5),@rlb_SCate) + ') or lb_news=1)'
		END
	IF @SearchS<>'' BEGIN
		IF @SearchF='Title' BEGIN
			SET @sql2=@sql2 + ' and ((Title like ' +Char(39) + '%' + @SearchS + '%' + Char(39) + ') or (Umame like ' +Char(39) + '%' + @SearchS + '%' + Char(39) + '))'
		END
		ELSE	BEGIN
			SET @sql2=@sql2 + ' and Content like ' +Char(39) + '%' + @SearchS + '%' + Char(39) 	
		END
	END
	IF EXISTS(select lb_name from tbllb_config where lb_name=@lb_name)
	BEGIN
		Set @lb_CateName=(select lb_CateName from tbllb_Cate where lb_Cate=(select lb_Cate from tbllb_config where lb_name=@lb_name))
		Set @lb_Nickname=(select lb_Nickname from tbllb_config where lb_name=@lb_name)
		Set @lb_open=(select lb_open from tbllb_config where lb_name=@lb_name)
		Set @lb_design=(select lb_design from tbllb_config where lb_name=@lb_name)
		Set @lb_record=(select lb_record from tbllb_config where lb_name=@lb_name)
		Set @lb_width=(select lb_width from tbllb_config where lb_name=@lb_name)
		
		SET @sql = 'select top ' + convert(varchar(10),@lb_record) + ' idx, lb_news, m_id, Umame, eMail, lb_SCate, 
		(case lb_news when 1 then ' + Char(39) +Char(39) + ' else (case lb_SCate when 0 then (case when (select count(lb_SCate) from tbllb_SCate where lb_Name=tbllb_Dboard.lb_Name)>0 then ' + Char(39) + '[기타]' +Char(39) +' else  ' + Char(39) +Char(39) +' end) else (select '  + Char(39) +  '['  + Char(39) +  '+lb_SCateName+' + Char(39) + ']'  + Char(39) +  ' from tbllb_SCate where lb_Name=tbllb_Dboard.lb_Name and lb_SCate=tbllb_Dboard.lb_SCate) end) end ) as lb_SCateName,
		 Title,readNum, convert(varchar(10),writeday,121) as writeday, (case when convert(varchar(10),writeday,121)>=convert(varchar(10),getdate()-1,121) then 1 else 0 end) as date_flag, rLevel,(case when (file1_width>0 and file1<>' + char(39)+char(39)+ ') then 1 when file1<>' + char(39)+char(39)+ ' then 2 else 0 end) as file_flag, file1,(select count(num) from tbllb_reView where lb_name=' + char(39) + @lb_name + char(39) + ' and idx=tbllb_Dboard.idx) as reViweNO from tbllb_Dboard where lb_name=' + char(39) + @lb_name + char(39) + @sql2
		SET @sql = @sql + ' and idx Not in(select top ' +convert(varchar(10),@lb_record*(@npage-1))  + ' idx from tbllb_Dboard where lb_name=' + char(39) + @lb_name + char(39) + @sql2 + ' Order by lb_news Desc, rRef desc, rStep asc) Order by lb_news Desc, rRef desc, rStep asc'
		-- 주문 수량 카운트
		SET @sql3='select @sum_cnt_in=(count(idx)) from tbllb_DBoard where lb_name=' +char(39) + @lb_name + char(39) + @sql2
		set @p = '@sum_cnt_in INT OUTPUT'
		EXEC sp_executesql @sql3, @p, @sum_cnt_in=@sum_cnt OUTPUT
		SET @szRecordCount=@sum_cnt
		SET @szPageCount=(CONVERT(INT,((@szRecordCount - 1)/@lb_record) + 1))
	
		--select @sql
		exec(@sql)
		SET @MsgFlag=1 -- 1  성공
	END
	ELSE	BEGIN
			SET @MsgFlag=0 -- 1저장 실패
			SET @Msg='잘못 된 접근입니다.' 
	END

















CREATE PROCEDURE [Proc_lb_BregProc] 
	@lb_name		VarChar(10),
	@lb_SCate		tinyInt,
	@idx			INT,
	@rRef			SmallINT,
	@rLevel		SmallINT,
	@rStep			SmallINT,
	@flag			Char(1),
	
	@Umame		VarChar(10),
	@M_id			VarChar(20),
	@Title			VarChar(100),
	@eMail			VarChar(50),
	@Content		TEXT,
	@TegFlag		BIT,
	@lb_news		BIT,
	@Pwd			VarChar(10),
	@MsgFlag		SmallINT	OUTPUT,
	@Msg			VarChar(50)	OUTPUT,
	@NextIdx		INT		OUTPUT
AS
	set nocount on
	DECLARE  @regFlag  	CHAR(1)
	
	IF EXISTS(select lb_name from tbllb_config where lb_name=@lb_name)
	BEGIN
		SET @regFlag=(select lb_open from tbllb_config where lb_name=@lb_name)
		SET @NextIdx=(select isnull(MAX(idx),0)+1 from tbllb_Bboard)
	
		Set @M_id=rtrim(@M_id)
		Set @Pwd=rtrim(@Pwd)
	
		IF @flag='R' and @idx>0 BEGIN
			SET @lb_SCate=(select top 1 lb_SCate from dbo.tbllb_Bboard where rRef=@rRef Order by rLevel Asc)
			UPdate tbllb_Bboard Set rStep=rStep+1 Where lb_name=@lb_name and rRef=@rRef and rStep>@rStep
			SET @rStep=@rStep+1
			SET @rLevel=@rLevel+1
		END 
		ELSE BEGIN
			SET @rRef=@NextIdx
			SET @rStep=0
			SET @rLevel=0
		END
	
		IF @regFlag='W' or LEN(@M_id)>0 BEGIN
			INSERT INTO tbllb_Bboard
			(idx,lb_name,lb_SCate,lb_news, m_id,Umame,eMail,Title, TagFlag, Content, pwd, rRef, rLevel, rStep) 
			values 
			(@NextIdx,@lb_name,@lb_SCate,@lb_news, @M_id,@Umame,@eMail,@Title, @TegFlag, @Content, @Pwd, @rRef, @rLevel, @rStep)
	
			SET @MsgFlag=1 -- 1저장 성공
		END
		ELSE	BEGIN
			SET @MsgFlag=0 -- 1저장 성공
			SET @Msg='게시판 쓰기 권한이 존재하지 않습니다.' -- 1저장 성공
		END
	END
	ELSE	BEGIN
			SET @MsgFlag=0 -- 1저장 성공
			SET @Msg='잘못 된 접근입니다.' -- 1저장 성공
	END
	set nocount off

ALTER PROCEDURE [dbo].[proc_Account] 
	@yymm  CHAR(6),
	@flag  VARCHAR(10)
AS
	DECLARE @sql varchar(5000)
	DECLARE @opt_price INT
	--SET @opt_price=(select isnull(sum(add_inDanga+((su-1)*add_optDanga*add_inDanga)),0) from Order_detail OD, order_options OO where OD.seq=OO.seq and OD.num=OO.num and OD.buy_code=@yymm  + '-' + char(39) + ')
	SET @sql='select Account_code, Account_no, '
IF @flag='buy' BEGIN
	SET @sql=@sql +'(select buyCo_nameC from tbl_buyCo where buyCo_id=tbl_Account.Account_co) as make_co_name, 0 as co_tax, '
END
ELSE	BEGIN
	SET @sql=@sql +'case when Account_co=0 then ' + char(39) + '이미지사이트' + char(39) +  ' when Account_co<>0 then (select custom_name from dbo.other_order where custom_code=tbl_Account.Account_co) end as make_co_name, '
	SET @sql=@sql +'isnull((select co_tax from other_order where custom_code=tbl_Account.Account_co),0) as co_tax, '
END
	SET @sql=@sql + 'Account_st, Account_en, '
IF @flag='buy' BEGIN
	SET @sql=@sql + ' (isnull((select sum(case order_detail.ea_box when 0 then order_detail.in_danga*order_detail.su when 1 then order_detail.in_danga*order_detail.su*order_detail.box_su end) from order_detail,[order] where buy_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no) and order_detail.seq=[order].seq and ( (left([order_detail].seq,1)<>' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag in(70,80)) or  (left([order_detail].seq,1)=' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag>30) ) ),0))+(select isnull(sum(add_inDanga+((su-1)*add_optDanga*add_inDanga)),0) from Order_detail OD, order_options OO where OD.seq=OO.seq and OD.num=OO.num and OD.buy_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no) ) as Account_price '
END
ELSE	BEGIN
	SET @sql=@sql + '((select sum(case when [order].custom_code=0 then total_pay when [order].custom_code<>0 then custom_danga*su end) from order_detail,[order] where order_detail.seq=[order].seq and sell_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no) and  ((left([order_detail].seq,1)<>' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag in(70,80)) or  (left([order_detail].seq,1)=' + Char(39) + 'C' + char(39) + ' and  [order_detail].detail_flag>30) ) )+(select isnull(sum(add_sellDanga+((su-1)*add_optDanga*add_sellDanga)),0) from Order_detail OD, order_options OO where OD.seq=OO.seq and OD.num=OO.num and OD.sell_code=' + char(39) +@yymm  + '-' + char(39) + '+Convert(varchar(10), Account_no)) ) as Account_price '
END
	SET @sql=@sql + ', Convert(varchar(10),Account_date,112) as Account_date,  Account_price as Account_price2, receive_pay, aFlag, (select Isnull(sum(Account_in-Account_out),0) from tbl_inOutPay where Account_idx=' + char(39) +@yymm + '-' + char(39) + '+Convert(varchar(10), Account_no)) as sum_pay, Account_memo, Account_tax, apply '
	SET @sql=@sql + ', (select top 1 Summary from tbl_inOutPay where Account_idx= tbl_Account.Account_code + ' + Char(39) + '-' + Char(39) + ' + Convert(varchar(3),Account_no) and Account in(1,2)) as summary'
	SET @sql=@sql + ' from tbl_Account '
	SET @sql=@sql + ' where Account_flag=' + char(39) + @flag + char(39) + ' and Account_code=' + @yymm
	SET @sql=@sql + ' order by Account_No asc'
	--select @sql
	exec(@sql)

ALTER PROCEDURE [dbo].[get_goods_search] 
	@sang 		varchar(20),
	@userIP 	varchar(20)
AS
	set nocount on
	declare  @sql varchar(5000)
	SET @sang=replace(@sang,' ','')
	
	insert SearchHit(searchS,userIP) values (left(@sang,25), @userIP)
	if len(@sang)>=2 BEGIN
		set @sql = 'select sang_name,brand,code,ea_danga,box_danga,box_su,simage, icon, (case when Convert(varchar(10),indate,112)>=Convert(varchar(10),getdate()-30,112) then 1 else 0 end) as New_icon from goods where hidden_flag=''0'' and  ((brand>0 and brand<1000) or brand=20) and '
		set @sql = @sql + ' (replace(sang_name,'+char(39) + ' ' + char(39) + ','+char(39)+char(39)+') like ' + char(39) + '%' + @sang + '%'  + char(39)  + ' or replace(keyword,'+char(39) + ' ' + char(39) + ','+char(39)+char(39)+') like ' + char(39) + '%' + @sang + '%'  + char(39)  + ' or goods_code like ' + char(39) + '%' + @sang + '%'  + char(39) +') or Convert(varchar(7),code)=' + char(39) + @sang + char(39) + ' Order by seq, Code Desc'
	END
	set nocount off
--select @sql
exec(@sql)

--테이블 목록 가져오기:
SELECT * FROM sysobjects WHERE xtype='U' 

--뷰:
SELECT * FROM sysobjects WHERE xtype='V' 

--프로시져:
SELECT * FROM sysobjects WHERE xtype='P' 


--테이블 리스트
SELECT * FROM  INFORMATION_SCHEMA.TABLES
--뷰 리스트
SELECT * FROM  INFORMATION_SCHEMA.VIEWS
--컬럼 리스트
SELECT * FROM  INFORMATION_SCHEMA.COLUMNS
--컬럼 키값
SELECT * FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE

--프로시져
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
--프로시져 파라메타값
SELECT * FROM  INFORMATION_SCHEMA.PARAMETERS

 

--컬럼 정보 가져오기
SELECT
 A.TABLE_CATALOG
 ,A.TABLE_NAME
 ,A.ORDINAL_POSITION
 ,A.COLUMN_NAME
 ,A.DATA_TYPE
 ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH,'')
 ,ISNULL(A.NUMERIC_PRECISION,'')
 ,A.IS_NULLABLE
 ,ISNULL(A.COLUMN_DEFAULT,'')
 ,ISNULL(B.CONSTRAINT_NAME,'')
 ,ISNULL(A.CHARACTER_SET_NAME,'')
 ,ISNULL(A.COLLATION_NAME,'')
 ,CASE WHEN ISNULL(C.NAME,'') = '' THEN '' ELSE 'Identity' END auto
FROM 
 INFORMATION_SCHEMA.COLUMNS A
 LEFT OUTER JOIN
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
 ON A.TABLE_NAME = B.TABLE_NAME 
 AND A.COLUMN_NAME = B.COLUMN_NAME
 LEFT OUTER JOIN
 syscolumns C 
 ON C.ID = object_id(A.TABLE_NAME) AND A.COLUMN_NAME = C.NAME AND C.COLSTAT & 1 = 1 
WHERE
 A.TABLE_NAME = '테이블명'
ORDER BY A.ORDINAL_POSITION

ALTER PROCEDURE [dbo].[get_board] 
@code  int,
@num  int
AS
declare @p_code int
declare @p_num int
declare @n_code int
declare @n_num int
set nocount on
select top 1 @p_code=code,@p_num=num  from board where (code=@code and num>@num) or (code>@code)  order by code asc ,num desc
select top 1 @n_code=code,@n_num=num from board where (code=@code and num<@num ) or (code<@code) order by code desc, num desc
update board set  Cnt = cnt+1 WHERE code=@code and  num=@num
Select  top 1
seq,
name,
title,
cnt,
convert(char(16),indate,120),passwd,user_ip,
isnull(@p_code,-1),isnull(@p_num,-1),isnull(@n_code,-1),isnull(@n_num,-1),
content 
from board where code=@code and num=@num
set nocount off

CREATE  FUNCTION dbo.getDateFormat(@p_brand varchar(64), @p_fieldName varchar(64), @p_value datetime)
 RETURNS VARCHAR(4000)
AS
BEGIN
	declare @val varchar(4000)
	declare @format varchar(4000)
	declare @sql varchar(4000)

	set @format=
		(SELECT top 1 TRAN_VALUE 
		  FROM TB_MEMBER_CODETRAN 
		 WHERE BRAND=@p_brand 
			   AND FIELD_NAME=@p_fieldName 
		)
		
	if @format='date' begin
		set @val = (select @p_value)
	end 
	else begin
		set @val = (select dbo.FNDateFormat(convert(varchar(23),@p_value,121),@format))
	end
	return @val
END