본문 바로가기

오라클34

[오라클] PK(Primary Key)만 가져오는 Oracle 쿼리 1234567SELECT C.POSITION ,C.COLUMN_NAMEFROM USER_CONS_COLUMNS C INNER JOIN USER_CONSTRAINTS S ON C.CONSTRAINT_NAME = S.CONSTRAINT_NAME AND S.CONSTRAINT_TYPE = 'P'WHERE C.TABLE_NAME = 'SKUWC'ORDER BY 1Colored by Color Scriptercs 해당 테이블의 primary key(프라이머리 키)만 가져오는 쿼리랍니다.이 쿼리를 응용해서 DB 상의 모든 테이블의 Primary Key만 뽑을수도 있겠죠. 1234567891011121314151617181920212223242526272829303132333435363738394041WITH LIS.. 2017. 5. 12.
[오라클] 테이블정의서 자동으로 만들어주는 Oracle 쿼리 테이블정의서 자동으로 만들어주는 오라클 쿼리입니다. 12345678910111213SELECT A.TABLE_NAME AS "테이블명", A.COLUMN_NAME AS "컬럼명", A.DATA_TYPE AS "데이터타입", A.DATA_LENGTH AS "길이", A.NULLABLE AS "Null 여부", B.COMMENTS AS "Comments"FROM dba_tab_columns A, all_col_comments BWHERE A.OWNER = B.OWNERAND A.TABLE_NAME = B.TABLE_NAMEAND A.COLUMN_NAME = B.COLUMN_NAMEAND A.OWNER = 'LOTOSDEV' -- DB명ORDER BY A.TABLE_NAMEcs 이거 실행하고 엑셀로 저장하면 .. 2017. 5. 11.
[오라클] trunc(SYSDATE)로 시분초가 0인 날짜만 가져오기 0시인 현재날짜의 시간을 가져오기 위해 어떻게 사용하시나요?저는 이제껏 아래와 같은 방법으로 사용을 했었는데요. TO_CHAR(trunc(SYSDATE), ‘yyyyMMddHH24miSS‘) TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, ‘yyyyMMdd’), ‘yyyyMMdd’), ‘yyyyMMddHH24miSS‘) TO_CHAR(SYSDATE, ‘yyyyMMdd‘) ||’000000‘ trunc() 함수를 사용하면 간편하게 날짜만 가져올수가 있습니다. TO_CHAR(trunc(SYSDATE), ‘yyyyMMddHH24miSS’) SELECT TO_CHAR(SYSDATE, 'yyyyMMddHH24miSS'), TO_CHAR(trunc(SYSDATE), 'yyyyMMddHH24miSS'), .. 2016. 12. 6.
[오라클] Oracle에서 key값이 없을 때 중복데이터 rowid로 제거하는 샘플 쿼리 특별히 primary key가 없거나 unique index가 없어서 중복되어서는 안되는데 같은 내용의 데이터가 중복되어서 쌓여있을 경우, 1개의 row만 남기고 나머지 데이터들은 모두 삭제하고 싶다면? 이 쿼리를 수정해서 사용하면 됩니다. ROW_NUMBER() OVER(PARTITION BY RLTM_SRNO, EQT_SRNO ORDER BY ROWID) 중복되어서는 안되는 컬럼, 즉 key가 되는 컬럼을 PARTITION BY … 부분에 넣으시면 됩니다. 데이터가 같아도 오라클 내부적으로 ROWID는 모두 다르기 때문에 ROWID를 이용하여 데이터 하나만 남기고 나머지 중복데이터는 모두 제거가 가능합니다. DECLARE CURSOR cur IS SELECT RW FROM ( SELECT RLTM_S.. 2016. 11. 2.
[오라클] 검색어 자동완성을 위한 초성, 중성, 종성 분리해서 검색하는 oracle 함수 프로젝트를 하면서 검색어 자동완성을 작업할 일이 있어서 개발을 해봤습니다.네이버나 구글에서 검색어를 입력하면 나오는 그 검색어 자동완성 말이죠. 바로 이건데요.여기서 핵심은 초성, 중성, 종성 검색이 가능해야 한다는 것이었습니다. 즉, 'ㄱ'을 입력하면 'ㄱ'으로 시작하는 모든 게 다 나와야 합니다.'날ㅆ'를 입력하면 '날씨, 날쏘' 등등이 나와야 하구요.'백'으로 검색을 하면 '배경화면'같은 단어들도 나와야 합니다. 어떻게 하면 이런 게 가능할까? 고민하다가검색할 데이터를 초성, 중성, 종성으로 나눠서 따로 저장해놓고검색어도 초성, 중성, 종성으로 나눠서 검색을 하면 되겠다는 생각이 들더군요. 이렇게 DB에 저장할 때 실제 제목과, 검색을 위한 초/중/종성으로 분리한 제목 2가지로 저장을 했습니다.그리.. 2016. 9. 12.
오라클(Oracle) 설치시.. Command line option Syntax error Type Command/? for Help 해결방법 오라클 설치시 다음과 같은 오류가 발생했다면...환경변수의 TEMP, TMP의 경로를 수정해 주시면 됩니다. [제어판 > 시스템 및 보안 > 시스템]으로 이동하세요. 환경변수 버튼을 클릭하시구요. 이렇게 돼 있을 텐데요. 이렇게 바꿔주시면 됩니다^^ 2016. 7. 4.
[쿼리문/오라클] 토드(Toad)에서 바로 실행가능한 변수선언 쿼리문 변수를 선언해서 쿼리를 실행해야 할 경우가 있는데요. 반복적인 작업을 완료하고 select를 한다던지... 여러가지 경우가 있을 텐데요. DECLARE 변수선언 BEGIN 실행구문 END; 이런식으로 DECLARE BEGIN ~ END 구문으로 프로그래밍을 하여 결과를 확인할 수 있습니다. 아래 샘플을 참고하시면 됩니다. DECLARE V_NUM INTEGER := 0; V_TARGET_DATE DATE; BEGIN LOOP V_TARGET_DATE := SYSDATE - V_NUM; DELETE FROM USE_STATUS_DAY WHERE CREATE_DATE >= TO_DATE(TO_CHAR(SYSDATE - V_NUM, 'YYYYMMDD')) AND CREATE_DATE < TO_DATE(TO_C.. 2016. 2. 5.
[오라클] oracle에서 저장프로시저 IN, OUT 파라미터 있을 경우 실행하는 방법 저장 프로시저를 만들었는데, 그걸 Toad에서 실행할 때는 간단하게 아래처럼 실행하면 됩니다.예를들어, 저장프로시저명이 prc_tree 라는 이름이라면...CALL prc_tree(); CALL prc_tree('x'); ==> IN 파라미터가 있을 경우또는EXEC prc_tree(); EXEC prc_tree('x'); ==> IN 파라미터가 있을 경우또는EXECUTE prc_tree(); EXECUTE prc_tree('x'); ==> IN 파라미터가 있을 경우 그런데, OUT 파라미터도 같이 있다면 어떻게 실행해야 할까요? CREATE OR REPLACE PROCEDURE prc_tree ( v_dept_code IN VARCHAR2, p_code OUT VARCHAR2, t_code OUT VAR.. 2016. 1. 29.
[오라클] Oracle에서 수행(실행)시간이 알고 싶다면 ‘SET TIMING ON’ 명령을 실행하세요. 오라클에서 쿼리나 저장프로시저, 커서 등의 실행시간을 알고 싶다면 어떻게 해야 할까요? 제가 약간 복잡한 통계용 프로시저를 만들었는데 100만건을 돌리면 얼마나 걸리는지 알고 싶어서 저장프로시저를 돌려놓고 퇴근을 했습니다.다음날 출근을 해서 확인을 했는데 웬걸!저장프로시저는 다 실행완료가 됬는데 실행시간이 찍히지 않는 겁니다 ㅠ 그래서, 찾아보니 SET TIMING ON이라는 명령어가 있더라구요.그냥 명령을 실행하면 아래처럼 실행시간이 나오지 않지만... SET TIMING ON 이라는 명령어를 넣으면 맨 아래에 실행시간이 찍히는 걸 보실 수 있습니다. 아~ 그리고, 참고로 저는 Toad에서 F5 단축키로 실행해서 테스트했습니다. 2015. 11. 10.
[쿼리문/오라클] 간단한 쿼리로 일련번호 쉽게 넣기 - ROWNUM 사용 보통 PK로 일련번호를 많이 넣는데요.PK가 없는 테이블에 ROWNUM을 사용하면 간단하게 일련번호를 넣을 수 있습니다. JOBS 라는 테이블에 PK가 없는데요.다음 쿼리로 PK로 사용할 SEQ_NO라는 컬럼을 만들어 보겠습니다.ALTER TABLE JOBS ADD SEQ_NO NUMBER(12); 다음 쿼리로 SEQ_NO 컬럼에 일련번호를 넣습니다.UPDATE JOBS SET SEQ_NO = ROWNUM; 결과를 볼까요? 일련번호 넣는데 커서 만들고 복잡하게 할 필요 없습니다^^순서가 아주 중요한 게 아니라면요. 2015. 9. 16.
[오라클] Oracle에서 ORA-28000: 계정이 잠금되었습니다. - 해결 방법 Oracle에서 ORA-28000: 계정이 잠금되었습니다. 해결방법입니다. 1. SYS나 SYSTEM 계정으로 로그인합니다. 2. ALTER USER 계정 ACCOUNT UNLOCK; 명령어를 실행한다. SCOTT을 로그인하려니 계정이 잠겼다네요 ㅠ SYSTEM으로 로그인했습니다. (SYS로 하셔도 됩니다.) 아래 명령어를 실행하고 다시 로그인하니 해결 완료~!!! [출처] ORA-28000: 계정이 잠금되었습니다. (the account is locked.) 오라클 오류|작성자 호옹 2015. 8. 7.
[오라클/쿼리문] Oracle에서 Foreign Key가 걸려있는 컬럼값 업데이트(update) 하기 오라클에서는 Foreign Key가 걸려 있을 경우,ON UPDATE CASCADE 옵션이 실행되지 않기 때문에 컬럼값을 바꾸기가 쉽지 않습니다.이때, 트리거를 만들어서 부모테이블이 업데이트 될 때, 자식테이블도 업데이트 되도록 하면 됩니다. 자식테이블 COMTCCMMNDETAILCODE 테이블의 CODE_ID가 현재 Foreign Key구요.Foreign Key 제약 때문에 UPDATE가 진행되지 않습니다. 부모 : COMTCCMMNCODE.CODE_ID 자식 : COMTCCMMNDETAILCODE.CODE_ID아래와 같이 트리거를 하나 만들고 UPDATE문을 실행합니다.CREATE OR REPLACE TRIGGER RENAME_TRIGGER AFTER UPDATE ON COMTCCMMNCODE FOR.. 2015. 6. 18.
[오라클/쿼리문] Oracle에서 사용자가 생성한 모든 Table, View를 삭제하는 쿼리 만들기 이것저것 하다보면 가끔은 DB를 삭제하고 다시 만들고 싶은 생각이 들 때가 듭니다. 그렇지만, DB를 다시 만드는 것보다 내가 만든 테이블만 모두 삭제하면 더 편리하겠다는 생각이 들었죠.SELECT 'DROP TABLE ' || object_name || ' CASCADE CONSTRAINTS;' FROM user_objects WHERE object_type = 'TABLE'; 생성된 쿼리를 긁어다가 [F5] 버튼 꾹~ 눌러주면 테이블이 다 삭제됩니다. View도 이렇게 쿼리를 만들어 같은 방법으로 삭제하시면 됩니다.SELECT 'DROP VIEW ' || object_name ||';' FROM user_objects WHERE object_type = 'VIEW'; 2015. 6. 4.
[전자정부 표준프레임워크] 로그인 컴포넌트 설치시 뷰 생성 에러 해결방법 (ORA-01031: 권한이 불충분합니다) ORA-01031: 권한이 불충분합니다.전자정부프레임워크 로그인 컴포넌트를 설치할 때 나는 에러입니다. 이유는 오라클 접속계정에 뷰(View) 생성 권한이 없기 때문입니다. GRANT CREATE VIEW TO 사용자명;SYSTEM으로 접속해서 위 쿼리를 실행해주면 해결됩니다. 자, 그럼 다시 로그인 컴포넌트를 설치해 볼까요? 이렇게 뷰 생성에 실패를 했었는데요. 뷰 생성 권한을 주고 다시 해보면?? 네~ 성공입니다^^ 2015. 6. 3.
[오라클/쿼리문] Oracle에서 세자리 콤마 찍기 1. 숫자인 경우SELECT TO_CHAR('1000000', 'FM9,999,999') FROM DUAL => 1,000,000 2. 화폐인 경우SELECT TO_CHAR('1000000', 'L9,999,999') FROM DUAL => \1,000,000 2015. 3. 25.

티스토리툴바