일상을 추억으로

순번을 키로 가지는 테이블에 여러 행을 한번에 SELECT INSERT 하는 방법

아래에 테이블 SERVICE_CHECK_LIST는 매일 담당자가 체크를 해야 할 대상 서버를 유지한다.

TABLE service_check_list

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPID NOT NULL VARCHAR2(3)

SEQ NOT NULL NUMBER

SERVER_NAME NOT NULL VARCHAR2(20)

CHECKED_AT DATE

CHECK_DATE NOT NULL DATE

DISCONTINUED NOT NULL VARCHAR2(1)

매일 아침 해당 테이블에서 DISCONTINUED가 1이 아닌 행들 중에서 어제 생성되었던 행들을 복제해서 오늘 날짜로 밀어 넣으려고 한다.

EMPID SEQ SERVER_NAME CHECKED_AT CHECK_DATE DISCONTINUED

----- ---------- -------------------- ---------- ---------- ------------

JJM 1 MICRO1 18/04/10 0

JJM 0 MICRO2 18/04/10 0

SAM 1 BIG1 18/04/10 0

SAM 0 BIG2 18/04/10 1

EMPID, SEQ가 키 이므로 매번 실행시 마다 SEQ의 최대값을 구해서 새로 넣어주어야 한다. 이 때 어려움은 JJM의 경우에는 MAX값만을 구해서 +1을 해준 값을 넣어줄 수 없다는 것이다. 루프를 쓰지 않고 SQL로 해결하자.

group by 를 써서 insert into 하려고 하면 다음처럼 된다.

SELECT EMPID, SERVER_NAME, MAX(SEQ)+1, SYSDATE, 0

FROM SERVICE_CHECK_LIST

WHERE DISCONTINUED <> 1

GROUP BY EMPID, SERVER_NAME

EMPID SERVER_NAME MAX(SEQ)+1 SYSDATE 0

----- -------------------- ---------- -------- ----------

JJM MICRO1 2 18/04/10 0

JJM MICRO2 1 18/04/10 0

SAM BIG1 2 18/04/10 0

원하는 결과가 아니다. JJM, 1 이 unique key 제약을 해친다. 이 문제를 group by를 통해서 해결하려고 하면 문제가 계속 꼬인다. empid, server_name등이 모두 group by에 나타나야 하기 때문이다.

이 테이블에서 SEQ에 대한 기준은 EMPID뿐인데, 이렇게 하려면 EMPID에 대한 ROW_NUMBER를 구하는 서브쿼리를 얻은 후 다시 대상 행들을 선택해서 ROW_NUMBER를 더하고 이걸... 이런식으로 쿼리를 복잡하게 만들지 말자.

Analytic function 을 사용하자. 다음 쿼리는 위 문제를 해결한다. 이 문장으로 INSERT INTO하면 문제는 해결된다.

SELECT EMPID,

SERVER_NAME,

MAX (SEQ) OVER (PARTITION BY EMPID ORDER BY EMPID)

+ ROW_NUMBER () OVER (PARTITION BY EMPID ORDER BY EMPID)

SEQ,

SYSDATE,

FROM SERVICE_CHECK_LIST

WHERE DISCONTINUED <> 1;

EMPID SERVER_NAME SEQ SYSDATE 0

----- -------------------- ---------- -------- ----------

JJM MICRO2 2 18/04/10 0

JJM MICRO1 3 18/04/10 0

SAM BIG1 2 18/04/10 0

3 rows selected.

UI 를 만들 때 꼭 지켜야 할 사항.

이 글을 쓰면서 나는 화가 나 있다. 이 글은 때때로 업데이트 될 것이고, 아마 그때마다 나는 누군가에게 화가 나 있을 것이다. UI를 기획한 사람이든 이런 문제를 알고도 화면을 만들어준 개발자이든 누구든 혼이 나야 한다.

다음 사항을 꼭 참고해서 스스로를 부끄럽게 만들지 말자.

집계 데이터와 표를 함께 보여줄 때에는 집계 데이터는 반드시 표의 데이터들로 부터 생성되어 한다. 즉 집계 데이터를 표로 부터 만들 수 없으면 사용자는 매우 혼란스러울 것이고, 이 사용자들의 질문에 당신 혹은 당신의 후임도 매우 혼란스러울 것이다. 누군가 이런 화면을 기획해 온다면 욕을 해 주자.
한 화면에 나타나는 모든 숫자들은 동일한 정렬, 동일한 소수점 표시(자리수)를 가져야 한다. USD와 KRW를 함께 보여주면 USD 화폐들은 횡하게 비어 보일 것이다. 그렇지만 횡하게 보이는 것이 자리수들이 맞지 않는것 보다는 낫다.

to be continued...

Go의 클로져를 사용할 때의 흔한 실수와 이유, 해결책

클로져는 함수 리터럴이다. 함수 리터럴은 자신을 감싸는 함수의 변수를 참조할 수 있다. 내가 느끼기에 고의 클로져는 자바스크립트의 클로져와 동일하다고 생각된다. 클로져의 쓰임새는 매우 다양하지만 초보자들에겐 약간의 혼동을 주기도 한다.

(나를 포함해서) 개발자들이 주로 하는 실수는 다음과 같이 루프 안에서 클로져를 생성할 때 생긴다.

이 클로져는 예상대로 동작하지 않는다. 왜일까.

클로져가 만들어질 때 클로져는 for 의 변수 i를 참조해서 messages 슬라이스에 접근했다. 클로져들이 만들어 질 때 참조한 변수 i는 이 클로져들이 생성된 후, 즉 루프가 끝났을 때 4가 되었다.
다음 루프에서 클로져를 호출 했을 때 i는 4이고 messages[i]는 you? 이다.

해결책은 두번째 코드에서 보여주고 있는데 여기에서 처럼 클로져가 만들어 질 때마다 새로운 변수를 만들도록 프로그램을 고치면 된다.
세번째 코드 블록은 같은 이름의 변수를 재선언 하는데 이렇게 하면 j와 같은 새로운 변수를 만들 필요없이 루프 내에서만 참조가능한 i를 만들게 되고 실제로 이 i는 외부의 i와 같은 의미와 값을 가지므로 헷갈리지도 않는다. 고 언어에서 흔히 사용되는 방법이다.

오라클 EXECUTE IMMEDIATE 바인딩 변수

오라클의 NDS(Native Dynamic SQL)은 엄청나게 강력한 기능중 하나다.

NDS가 성능이 낮아서 사용을 꺼리는 경우가 있는데, 9i에 들어와서 성능이 많이 개선되었다고 한다.

성능이 개선되지 않았을 때에도 프로그램 유지 보수성, 가독성등 여러가지 측면을 따져보았을 때 약간의 성능을 버리더라도 NDS를 사용하는것이 유리한 경우가 많았다.

NDS에 대한 자세한 정보는 다음 링크를 보거나 ORACLE에서 제공하는 메뉴얼을 읽기 바란다. 정말 잘 되어 있다.

http://www.oracle.com/technetwork/issue-archive/o64sql-095035.html

NDS는 다음으로 구성된다

EXECUTE IMMEDIATE sql_string
   [INTO variable_list ]
   [USING bind_variable_list ]

이 간결한 문법을 보라! 오라클이 강력한 이유중 하나는 문법이 간결해서인것 같다.

대부분의 리더를 위해서 예제부터 들어간다.

-------------------

동적 쿼리에서 파라미터 사용과 반환값 받기

EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM STUDENTS WHERE FIRST_NAME = :1' INTO C USING 'Brian'

포인트는 반환값을 설정하기 위해서 select 문에 into 구문이 들어가지 않는다는 점이다.
여러 컬럼을 반환하고자 할 때에는 into 절에 여러 변수를 병렬로 지정한다.

그리고 where절을 보면 :1 바인드 변수를 사용하는데 1이라는 숫자는 가독성을 위한 것이지 의미가 있지는 않다. USING에 나열된 순서대로 바인드 변수에 차례대로 적용시킨다.

ORACLE 바인딩 변수, 치환 기능

이 문서의 내용은 아래 링크에 새로 정리하였습니다. 토드와 SQL*PLUS에서의 바인드 변수 사용 방법을 함께 살펴 보려면 아래 링크를 참고하세요.

http://www.jongminjang.com/plsql/2018/08/14/bindvariable.html

오라클은 바인드 변수라는 개념의 변수 사용을 지원한다.

바인딩 변수는 SQL 문장을 실행할 때 SQL에 사용자 값을 전달할 수 있는 통로 역할을 한다. 또한 오라클 성능에 큰 영향을 미치는 SQL 공유와도 큰 연관이 있다.

바인드 변수는 다음 방법들로 선언될 수 있다.

var[riable] 을 사용해서 선언
Toad, SQL Developer등의 툴이 선언
declare 내부에서 선언
프로그램 파라미터에서 선언

일단 var를 사용하는 예를 보자.

var a number;
exec :a := 2;
select :a from dual;

var로 선언된 바인드 변수가 다른 방법들로 선언된 바인드 변수와 가장 다른점이 여기서 나왔다. a를 선언할 때에는 콜론을 사용하지 않지만 참조할 때는 콜론이 사용되었다. var변수를 자주 사용하지 않기 때문에 항상 헷갈리는 점이다.

var로 선언된 변수의 특징은 다음과 같다.

선언시에는 이름만 사용, 참조시에는 콜론(:) 함께 사용
세션에서 전역적으로 선언됨. 즉 블럭내부에서 var를 사용해서 선언할 수 없음
값 할당시 exec 를 사용. 즉 함수 호출처럼 다루어짐

1의 특징에 대해서는 처음의 예제에서 설명이 되었다.

2는 var변수가 한번 선언되고 나면 해당 세션에서 계속 선언되어 있음을 뜻한다.

var 명령어를 수행하면 var로 선언된 모든 변수의 목록이 출력된다.

VARIABLE a  DATATYPE VARCHAR2
...

3은 값의 할당에 관련된 내용이다. 함수 호출 문법을 사용해서 할당을 수행해야 한다.

Oracle PL/SQL Ed3은 SQL*Plus에서 variable 할당이 익명블럭에서만 가능하다고 설명하고 있지만 실제로 그런 제약은 존재하지 않았다(ORACLE 9i에서 테스트됨)

var 는 스크립트 수행등 사용자가 개입할 여지가 없는 환경에서 사용된다.

Var사용시 Toad는 SQL*Plus와 약간 다르게 동작하였다. SQL*Plus와 SQL Developer는 var를 사용하지 않고 변수를 참조하려고 할때 정상적인 에러를 생성하였는데 Toad은 이 경우에 에러를 생성하지 않고 빈(NULL아님) 변수가 존재하는것 처럼 동작하였다.

다음은 툴이 바인딩 변수를 선언해주는 경우이다. 이 방식은 콜론이 붙은 바인드 변수 명을 프로그램이 인식해서 SQL 실행시에 사용자에게 값을 요구하는 방식이다.

다음 프로그램을 보자

select :i from dual;

위 문장을 SQL*Plus에서 호출하면 :i 가 정의되어 있지 않아서 오류를 발생할 것이다. 같은 문장을 Toad, Sql Developer에서 수행하면 :i 바인딩 변수에 대한 값을 사용자에게 요구한다. 여기서 값을 입력하면 :i 는 사용자가 var로 선언한 것처럼 바인딩 변수로 다루어 진다. 그러나 var선언과는 다르게 이 변수는 문장의 실행이 끝나면 세션에서 찾을 수 없다.

declare절에서 선언되는 경우나 매개변수 파라미터로 선언되는 경우에는 변수는 해당 블럭 내부에서 자유롭게 사용될 수 있다.

다음 프로그램은 두 개의 변수를 사용한 간단한 프로그램이다.

declare
 a number;
 b number;
begin
 a := 1;
 select a into b from dual;
end;

var를 사용하거나 툴이 선언해주는 방식과는 다르게 이름앞에 콜론이 들어가지 않는다.

매개변수와는 다르지만 사용자로 부터 값을 입력받는 다른 방식인 치환을 살펴보자. 치환은 SQL문장 자체를 변환시켜서 오라클에 전송한다.

다음 예제를 보자.

select * from &al;

이 방식은 SQL*Plus, Toad, SQL developer등의 모든 툴에서 사용가능하다. 이 문장을 실행하면 a에 대한 변경 값을 요구한다.

&a에 dual을 입력하면 위 문장은

select * from dual;

로 변경되어 오라클에 전송되고 실행된다. 사실 바인드 변수로는 위와 같은 문법을 사용하지 못한다. 예를 들어

select * from :a;

는 명백한 오류이다. 왜냐하면 오라클은 위 문장을 파싱할 수 없기 때문이다.

이 문서의 내용은 아래 링크에 새로 정리하였습니다. 토드와 SQL*PLUS에서의 바인드 변수 사용 방법을 함께 살펴 보려면 아래 링크를 참고하세요.

http://www.jongminjang.com/plsql/2018/08/14/bindvariable.html

오라클 TO_CHAR 결과와 문자열 비교시 예상대로 작동하지 않는 경우 원인

PL/SQL에서 1 월에서 12 월 까지의 값을 처리하기 위해 루프를 만들었는데 예상대로 동작하지 않는 상황이 생겼다.

FOR MTH IN 1 .. 12 LOOP

F(TO_CHAR(MTH, '00'));

END LOOP;

01~12를 순회하려고 하는데 F는 문자열로 된 두자리의 월 값을 인자로 기대한다.

PROCEDURE F ( mthIN VARCHAR2 ) IS

BEGIN

IF mth = '01' THEN

...

END IF;

만약 다음처럼 직접 F를 호출한다면 아무런 문제가 없을 것이다.

F ( '01' );

그렇지만 위의 루프를 통해서는 IF mth= '01' 조건을 만족하는 케이스를 찾을 수 없을것이다.

그 이유는 TO_CHAR(1, '00')의 결과가 '01'이 아니라 ' 01' 이기 때문이다.

오라클 TO_CHAR는 마이너스 사인(-)을 넣기 위한 자리를 포함해서 반환하기 때문에 위 루프를 제대로 동작하게 만들기 위해서는 TO_CHAR 함수의 인자를 다음처럼 변경해 주어야 한다.

TO_CHAR(MTH, 'FM00')

오라클 Label 을 사용한 루프문 제어

Labeling a PL/SQL Loop

Like PL/SQL blocks, loops can be labeled. The optional label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement. The label name can also appear at the end of the LOOP statement. When you nest labeled loops, use ending label names to improve readability.
With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement, as shown in Example 4-9. Every enclosing loop up to and including the labeled loop is exited.

Example 4-9 Using EXIT With Labeled Loops

DECLARE
  s      PLS_INTEGER := 0;
  i      PLS_INTEGER := 0;
  j      PLS_INTEGER;
BEGIN
  <>
  LOOP
    i := i + 1;
    j := 0;
    <>
    LOOP
      j := j + 1;
      s := s + i * j; -- sum a bunch of products
      EXIT inner_loop WHEN (j > 5);
      EXIT outer_loop WHEN ((i * j) > 15);
    END LOOP inner_loop;
  END LOOP outer_loop;
  DBMS_OUTPUT.PUT_LINE('The sum of products equals: ' || TO_CHAR(s));
END;
/

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/controlstructures.htm#i8296

프로필