메리

varbinary은(는) text과(와) 호환되지 않습니다.

내가 insert하려는 컬럼중에 text 타입인 컬럼이 있는데..

NULL이 가능한 컬럼인데.. NULL을 넣으려하니까 에러가난다...

NULL이 binary 형태의 값으로 변환되어서 들어가려는 모양이다..

해결) 그래서 null값이 아니라 ''로 넣음

ROW_NUMBER() OVER PARTITION BY ORDER BY

PARTITION BY 기준으로 그룹핑한 뒤 ORDER BY로 순번 매기는 함수.

예를 들어 아래와 같은 테이블이 있다

@TEMP 테이블임.

[ REG_DATE 를 기준으로 그룹핑하여 ID 순으로 순번 매기기]

SELECT
	ID,
	REG_DATE, 
	ROW_NUMBER() OVER ( PARTITION BY REG_DATE ORDER BY ID ) 
FROM @TEMP

11/1에는 김치와 콩차반을 샀군요

[ ID 를 기준으로 그룹핑하여 REG_DATE 순으로 순번 매기기]

SELECT
	ID,
	REG_DATE,
	ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY REG_DATE ) 
FROM @TEMP

김부각은 11월 2일과 11월 10일 두 번 샀군요

이렇게 쓰는 것이었다

group 별로 컬럼 내용 합치고 싶을때...

오라클에서는 LISTAGG mysql에서는 GROUP_CONCAT 을 썼었는데...

MSSQL에서는 또 다르다..

[TABLE]

이런 테이블이 있다고 칩시다.

아래 쿼리를 수행하면 다음과 같이 결과가 나온다. 

SELECT DISTINCT ID
STUFF( (select ','+name from table p where p.id = b.id for xml path('') ),1,1,'') AS NAME2
FROM table B

** STUFF : 문자열의 시작위치와 크기를 지정하여 원하는 문자로 치환
STUFF('[문자열]','[시작위치]','[크기]','[치환문자]')

** FOR XML PATH : 쿼리의 결과 데이터를 XML 형태로 표현

[MEMBER] 테이블

>> 예를 들어 외부에서 이름을 받아 해당하는 이름과 ID를 출력(반환)하는 프로시저를 생성하기 

(한 행만 반환하는 경우)

CREATE PROCEDURE PS_GETID(

@NAME VARCHAR(10) --파라미터로 받을 값

@NAME_OUTPUT VARHCAR(10) OUTPUT, --반환할 값은 OUTPUT을 붙여준다.
@ID VARCHAR(10) OUTPUT 	--반환할 값은 OUTPUT을 붙여준다.

)

AS
BEGIN
SET NOCOUNT ON 

--실행할 쿼리
SELECT @NAME_OUTPUT = NAME, @ID = ID FROM MEMBER WHERE NAME = @NAME FROM MEMBER
--@NAME을 매개변수로 @NAME_OUT과 @ID를 반환하기 

SET NOCOUNT OFF
RETURN
END
--실행을 시켜 생성해준다.

* SET NOCOUNT ON : SELECT, INSERT, UPDATE, DELETE의 영향을 받은 행의 개수를 출력하지 않도록 설정.

>> 생성한 프로시저 확인

SP_HELPTEXT PS_GETID

>> 생성한 프로시저 수정

ALTER PROCEDURE PS_GETID(

-- 아래 내용은 같되 CREATE만 ALTER로 바꿔주면 됨

>> 생성한 프로시저를 사용하여 결과값을 변수에 담기

DECLARE @ID VARCHAR(10) --결과값을 받아올 변수 생성
DECLARE @NAME_OUTPUT VARCHAR(10) --결과값을 받아올 변수 생성

--프로시저 생성. 파라미터와 결과값변수 넘겨주기
--프로시저 결과가 변수에 담긴다.
EXEC PS_GETID 'LUFFY',@ID OUTPUT, @NAME_OUTPUT OUTPUT;


SELECT @ID, @NAME --EXEC 결과 조회

>> 프로시저에서 반환한 여러 행을 받을 경우

테이블 변수에 프로시저 결과를 담아 사용한다.

-- 여러 행을 반환하는 프로시저를 만든다.
CREATE PROCEDURE PS_GETTABLE
AS 
BEGIN
	SELECT * FROM MEMBER
END

DECLARE @TEMP TABLE(
	NAME VARCHAR(10),
	ID VARCHAR(10)
) --테이블 변수를 만든다.

-- 프로시저 결과를 테이블변수에 담는다.
INSERT INTO @TEMP EXEC PS_GETTABLE

-- 조회
SELECT * FROM @TEMP

여러개의 변수를 프로시저 파라미터로 넘기고 싶은 경우

위와 비슷하게 테이블 변수를 매개변수로 넘겨보자...

table에서 특정 행만 출력하기.

예를 들어 아래 테이블에서 2번째 행만 가져오기.

[MEMBER] 테이블

요약) 순번 열을 만들어 준 후 SELECT 하면 됨.

1. ROW_NUMBER()로 순번 열을 만들어 주기

쿼리 결과

2. 순번 열을 만들어준 테이블을 임시테이블로 생성하거나 테이블 변수에 넣어서 거기서 select 하면 댐.

A) 임시테이블로 생성한 후에 SELECT하기 

WITH TEMP AS 
(
  SELECT ROW_NUMBER() OVER(order by ID asc) as 'NO', ID, NAME 
  FROM MEMBER
) --임시테이블 생성

SELECT * FROM TEMP WHERE NO=2; --2번째 열 SELECT

B) 테이블 변수에 넣어서 SELECT하기

DECLARE @TEMP TABLE( --테이블 변수 생성
	NO INT,
	ID VARCHAR(10),
	NAME VARCHAR(10)
)

--테이블 변수에 SELECT결과 넣기
INSERT INTO @TEMP 
SELECT ROW_NUMBER() OVER(order by ID asc) as 'NO', ID, NAME FROM MEMBER


SELECT * FROM @TEMP WHERE NO=2; --2번째 열 SELECT

머가 편한지는 선택 

INSERT INTO myTable (ID, Name) VALUES ('2', 'myName')

테이블에 insert를 하려는데 이런 에러가 뜬다... 

[ IDENTITY_INSERT가 OFF로 설정되면 .... 명시적 값을 삽입할 수 없습니다. ]

* identity 속성 : 숫자형식의 컬럼에 대해 지정된 초기값과 증가치를 주어 insert시 마다 자동으로 증가되며 값이 발생된다. 

예를 들어 table 생성시에

CREATE TABLE myTable(
	[ID] [decimal](9, 0) IDENTITY(1,1) NOT NULL --1부터 1씩증가
)

IDENTITY 속성을 주면  INSERT시에 ID값을 주지 않아도 

즉 자동으로  들어간다고 생각하면 됨..

이걸 임의로 값을 넣으려고 하니 에러가 나는 것. 

-> INSERT시에 해당 컬럼에 임의로 값을 넣고 싶다면

SET IDENTITY_INSERT [테이블이름] ON

->ON설정을 해주고 INSERT 후에 다시 OFF를 해주자.

SET IDENTITY_INSERT [테이블이름] OFF

invalid host/bind variable name

쿼리에 콤마 빠짐

존재하는 테이블인데 쿼리에서 인식 못하는 경우 

TEST 라는 이름의 테이블. TEST1이라는 컬럼이 있음 (모두 대문자)

이름이 대문자인경우 ""를 붙여줘야한다.

""를 붙여주면 대소문자를 구분

안 붙이면 대소문자 구분 안 함.

select * from TEST;  --> ERROR
select * from "TEST"; --> SUCCESS
select * from "test"; --> ERROR

select TEST1 from "TEST"; --> ERROR
select "TEST1" from "TEST"; --> SUCCESS

select sysdate-1/24/60 from dual; //1분 전
select sysdate-1/24/30 from dual; //2분 전
select sysdate-1/24/12 from dual; //5분 전
select sysdate-1/24/6 from dual; //10분 전
select sysdate-10/24/60 from dual; //10분 전
select sysdate-1/144 from dual; //10분 전
select sysdate-30/1440 from dual; //30분 전
select sysdate-1/24 from dual; //1시간 전

계산방법

하루는 1일 혹은 24시간 혹은 1440분이니까 각 단위에 맞춰서 계산

ex) 10/24/60  

1440분 ÷ 60 ÷ 24 × 10 하면 됨!

혹은

24시간 ÷ 24 × 10 = 10시간(600분)

600분 ÷ 60 하면 됨!

1. 시작하기

[root@dbdb]# su -l oracle

[oracle@dbdb ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 6 09:26:13 2021

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size                  2027640 bytes
Variable Size             956305288 bytes
Database Buffers         4043309056 bytes
Redo Buffers               14745600 bytes
Database mounted.
Database opened.

SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
[oracle@dbdb ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-APR-2021 09:26:37

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /opt/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0/db_1/network/log/listener.ora
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kdb2)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                06-APR-2021 09:26:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbdb)(PORT=1521)))
Services Summary...
Service "dbdb" has 1 instance(s).
  Instance "dbdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

2. 종료

[root@dbdb]# su -l oracle

[oracle@dbdb ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 06-APR-2021 09:24:45

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbdb)(PORT=1521)))
The command completed successfully

[oracle@dbdb ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 6 09:25:05 2021

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - 64bit Production