실수로 BOARD 테이블에 내용을 지웠다면 데이터를 복구 할수 있다. NO의 196데이터를 실수로 삭제 DELETE FROM BOARD S WHERE S.NO='196'; 실수로 삭제한 데이터를 조회 할수 있음. SELECT * FROM BOARD AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '10' MINUTE); 해당 데이터 복구 INSERT INTO BOARD (SELECT * FROM BOARD AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '10' MINUTE));
DB/Oracle
스케줄러 잡 조회 SELECT * FROM USER_JOBS; 스케줄러 잡 등록 DECLARE X NUMBER; BEGIN DBMS_JOB.SUBMIT ( X -- 잡등록 ID ,'IMSI_PROC;' -- 실행할 프로시저명 ,to_date('29-09-2011 09:00:00','dd/mm/yyyy hh24:mi:ss') --실행시킬 시간 지정 ,'TRUNC(SYSDATE+1)+9/24' --반복기간 지정 ,FALSE ); END; JOB 실행 방법 DBMS_JOB.RUN(90);JOB 중지 방법 EXEC DBMS_JOB.BROKEN(JOBNO, TRUE);JOB 삭제 방법 BEGIN DBMS_JOB.REMOVE(91); END;
SELECT /*+ RULE */ S.USERNAME ORA_USER, OSUSER OS_USER, S.SID SID , S.SERIAL# SERIAL#, P.SPID, L.TYPE TYPE, DECODE(LMODE,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') HELD, O.NAME OBJECT_NAME, ID1 OBJECT_ID, S.STATUS STATUS, DECODE(REQUEST,1,'NONE',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') REQ, S.TERMINAL FROM..
UPDATE TEMP SET COMMENT ='특수문자''입력' WHERE NO='100'; 특수문자'입력 - 특수문자를 입력할때는 '+특수문자 를 입력하여 DB에 데이터를 입력
DELETE FROM 테이블명 A WHERE ROWID > ( SELECT MIN(ROWID) FROM 테이블명 B WHERE A.컬럼명1= B.컬럼명1 AND A.컬럼명2 = B.컬럼명2 );
CREATE TABLE 스키마명.테이블명 AS SELECT 컬럼리스트 FROM 원본 테이블명;
SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME='EMP';
오라클 SQL의 조건식의 결과로는 TRUE, FALSE 외에 UNKNOWN 타입이라는것이 있는데 이중에 UNKNOWN타입은 NULL이 포함된 조건연산을 수행할 경우 발생하는 타입 즉 NULL이 포함된 연산에서 조건이 맞지 않은 경우 UNKNOWN 타입을 리턴한다.
Exception Name ORAError SQLCODE Raised When ... ACCESS_INTO_NULL 06530 -6530 초기화 되지 않은 오브젝트에 값을 할당할경우 CASE_NOT_FOUND 06592 -6592 CASE문장에서 ELSE구문도 없고 WHEN절에 명시된 조건을 만족하는 것이 하나도 없을 경우 COLLECTION_IS_NULL 06531 -6531 초기화 되지 앟은 중첨 테이블이나 varray 같은 콜렉션을 EXISTS외의 다른 메소드로 접근을 시도할 경우 발생 CURSOR_ALREADY_OPEN 06511 -6511 이미 오픈된 커서를 다시 오픈하려고 시도하는 경우 DUP_VAL_ON_INDEX 00001 -1 유일 인덱스가 걸린 컬럼에 중복 데이터를 입격한 경우 INVA..
1. (+)는 WHERE 절에서만 사용이 가능 2. 테이블간 외부조인 조건이 한개 이상일 경우, 모든 외부조인 조건에 (+)을 붙여야 외부조인이 성립 또한 정확한 외부조건 결과를 얻기 위해서는 조인조건 외의 일본조건에도 (+)을 붙여야함 3. 테이블 자신에 (+)을 붙일수 없음 4. (+)는 오직 컬럼에만 붙일 수 있으며 OR 연산자와는 같이 사용 할수 없음 5. 외부조인시에 대상 테이블은 오직 한개의 테이블과만 외부조인이 가능 6. 조인조건식에서 (+)가 붙은 컬럼과는 IN 연산자를 같이 사용할수 없다. 7. 조인조건식에서 (+)가 붙은 컬럼과는 서브쿼리를 가티 사용할수 없다 8. 조인조건의 오직 한쪽에만 (+)을 붙일수 있다.
함수(Function)란?보통 값을 계산하고 결과 값을 반환하기 위해서 함수를 많이 사용 한다.대부분 구성이 프로시저와 유사 하지만 IN 파라미터만 사용 할 수 있다.반드시 반환 될 값의 데이터 타입을 RETURN문에 선언해야 한다.또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 한다.함수 문법 -- PL/SQL 블록에는 적어도 한 개의 RETURN 문이 있어야 한다. -- PL/SQL Block은 함수가 수행 할 내용을 정의한 몸체부분이다. CREATE OR REPLACE FUNCTION function name [(argument...)] RETURN datatype -- datatype은 반환되는 값의 datatype입니다. IS [변수 선언 부분] BEGIN [PL/SQL..
프로시저(Procedure)란?특정 작업을 수행 하는, 이름이 있는 PL/SQL BLOCK 이다.매개 변수를 받을 수 있고, 반복적으로 사용 할 수 있는 BLOCK 이다.보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL BLOCK을 데이터베이스에 저장하기 위해 생성 한다.프로시저 문법 CREATE OR REPLACE procedure name IN argument OUT argument IN OUT argument IS [변수의 선언] BEGIN --> 필수 [PL/SQL Block] -- SQL문장, PL/SQL제어 문장 [EXCEPTION] --> 선택 -- error가 발생할 때 수행하는 문장 END; --> 필수 - CREATE OR REPLACE 구문을 사용하여 생성 한다.- ..
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
사용법 : tnsping C:\Users\NB>tnsping 127.0.0.1 5 연결성공 연결실퍠
SELECT KEYWORD FROM V$RESERVED_WORDS ORDER BY KEYWORD;
오라클은 MySql 에서 지원하는 order by LIMIT 를 지원하지 않아 페이징 처리 속도가 느리다. 알려진 오라클 페이징 기법을 테스트 하여 속도를 비교해 보자 SAMPLE 테이블에 100만건의 데이트를 입력 후 10개씩 보여지는 마지막 페이지를 조회 정렬 조건은 PRIMARY KEY DESC 정렬 1. 페이지 수를 계산하여 셀렉트 SELECT B.* FROM ( SELECT CEIL(ROWNUM/10) PAGE --, COUNT(*) OVER() AS TOTAL_COUNT , A.* FROM ( SELECT * FROM OP_SAMPLE ORDER BY ID DESC ) A ) B WHERE PAGE = 100000; 100만 건인 경우 수행시간 : 1.327초, TOTAL_COUNT 까지 조회..
SELECT E.ENAME ,E.SAL ,RANK () OVER (ORDER BY E.SAL DESC) AS RANK ,DENSE_RANK () OVER (ORDER BY E.SAL DESC) AS DENSE_RANK ,ROW_NUMBER () OVER (ORDER BY E.SAL DESC) AS ROW_NUMBER FROM EMP E; 결과
SELECT LOC FROM DEPT WHERE LOC LIKE '%_%'; 결과 LOC --------------------------------------- NEW_YORK DALLAS CHICAGO BOSTON SELECT LOC FROM DEPT WHERE LOC LIKE '%#_%' ESCAPE '#'; 결과 LOC--------------------------------------- NEW_YORK ※ LOC컬럼에서 _이 들어간 문자열을 검색하기 위해서는 _의 앞에 문자(#: 다른 문자가 와도 됨) 추가해주고 ESCAPE '#'; 문을 붙여 주면 특수문자를 검색할수 있음
--1. 패키지 내에서 테이블이 어느 프로시저에서 사용하고 있는지 정보를 출력 --2. 프로시저 위치 정보와 테이블 위치정보를 가지는 테이블 생성해서 최종적으로 데어터 추출 -- TABLE : PACKAGE_PROCEDURE_LIST, PACKAGE_TABLE_LOC_LIST --3. 해당 쿼리를 프로젝트에 맞게 쿼리를 약간 수정해서 사용 --패키지 내 프로시저 위치 정보 테이블을 생성 CREATE TABLE PACKAGE_PROCEDURE_LIST AS SELECT NAME , TYPE , LINE , TEXT , OBJECT_NAME , PROCEDURE_NAME FROM ( SELECT A.NAME , A.TYPE , A.LINE , TRIM(REPLACE(REPLACE(REPLACE(REPLACE..
WITH T AS ( SELECT 'AA' AS "A", 'AA' AS "B", 10 AS VALUE FROM DUAL UNION ALL SELECT 'AA' AS "A", 'CC' AS "B", 20 AS VALUE FROM DUAL UNION ALL SELECT 'AA' AS "A", 'AA' AS "B", 30 AS VALUE FROM DUAL UNION ALL SELECT 'AA' AS "A", 'CC' AS "B", 30 AS VALUE FROM DUAL ) SELECT A.A ,A.B ,A.VALUE ,RATIO_TO_REPORT(A.VALUE) OVER (PARTITION BY A.A, A.B) AS RATIO FROM T A ORDER BY A.A, A.B 설명 RATIO_TO_REPORT..
NTITLE() 함수를 사용시 레코드 수에 대해서 NTITLE()에 파라미터에 세팅된 값으로 나눈 비율로 순위를 구함 레코드가 100개이고 NTITLE(5)이면 100/5 = 20개씩 그룹으로 묶어 순위를 출력 이때 나머지가 없을경우와 / 있을경우가 배분 차이 있음으로 이 내용에 대해서 알아보기로 한다. 나머지가 없을경우 레코드 : 20, NTITLE(5) SELECT V, NTILE(5) OVER(ORDER BY V) X FROM (SELECT LEVEL V FROM DUAL CONNECT BY LEVEL
set timing on select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 경 과: 00:00:00.01 실행시간 시작 : set timing on 실행시간 종료 : set timing off
/* PIVOT/UNPIVOT 사용 (오라클 11g부터 지원) 열을 행으로 SUM(CNT) : 집계 MM 데이터를 가지고 피벗함 '01', '02','03'으로 집계하면서 '01','02','03'이 컬럼 타이틀이 됨 컬럼명을 바꾸고 싶을때는 ALIAS를 주어 변경함 */ WITH T AS ( SELECT '2013' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL UNION ALL SELECT '2013' AS BAS_YM, '01' AS MM, 1 AS CNT FROM DUAL UNION ALL SELECT '2013' AS BAS_YM, '02' AS MM, 1 AS CNT FROM DUAL UNION ALL SELECT '2013' AS BAS_YM, '03' AS MM..
--ROLLUP 과 GROUPING을 이용하여 그룹지어진 소계와 합계를 출력해보자 SELECT DEPTNO ,EMPNO ,DECODE(GROUPING(DEPTNO),1,'합계',DECODE(GROUPING(EMPNO),1,'소계')) TOT ,SUM(SAL) S_SAL ,GROUPING(DEPTNO) GRP_DEPT ,GROUPING(EMPNO) GRP_EMP ,GROUPING_ID(DEPTNO, EMPNO) GID FROM EMP GROUP BY ROLLUP(DEPTNO, EMPNO); 결과 1. DECODE(GROUPING(DEPTNO),1,'합계',DECODE(GROUPING(EMPNO),1,'소계')) TOT DECODE를 이용하여 GROUPING(DEPTNO)이 1이면 '합계', DECODE(G..
WITH T AS ( SELECT '경역기획' AS DEPT_NAME, '200' AS MANAGER_ID FROM DUAL UNION SELECT '경영' AS DEPT_NAME, '201' AS MANAGER_ID FROM DUAL UNION SELECT 'IT' AS DEPT_NAME, NULL AS MANAGER_ID FROM DUAL UNION SELECT '물류' AS DEPT_NAME, NULL AS MANAGER_ID FROM DUAL UNION SELECT '회계' AS DEPT_NAME, '204' AS MANAGER_ID FROM DUAL ) SELECT * FROM T ORDER BY MANAGER_ID NULLS FIRST; WITH T AS ( SELECT '경역기획' AS DE..
CREATE OR REPLACE FUNCTION FN_GET_MACHINE_NO( l_cd IN VARCHAR2 ,m_cd IN VARCHAR2 ,s_cd IN VARCHAR2 ) RETURN VARCHAR IS retval VARCHAR2(14); seqNextVal VARCHAR(7); lCdVal VARCHAR(1); mCdVal VARCHAR(2); sCdVal VARCHAR(3); INPUT_ERR EXCEPTION; OUTPUT_ERR EXCEPTION; BEGIN seqNextVal := 0; lCdVal := TRIM(l_cd); mCdVal := TRIM(m_cd); sCdVal := TRIM(s_cd); BEGIN -- 20100823 LCS 수정 : 소분류가 NULL이면 '99'기본값..
WITH T AS ( SELECT '0001' AS CD, 'S0001' AS ID, 1 AQS ROWNO FROM DUAL UNION ALL SELECT '0002' AS CD, 'S0002' AS ID, 1 AQS ROWNO FROM DUAL UNION ALL SELECT '0003' AS CD, 'S0003' AS ID, 1 AQS ROWNO FROM DUAL ) SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(CD,','))), 2) AS CD FROM T START WITH ROWNO = 1 CONNECT BY PRIOR ROWNO = ROWNO - 1 결과 0001,0002,0003
DELETE FROM EMP A WHERE EXISTS ( SELECT 1 FROM DEPT B WHERE A.DEPTNO = B.DEPTNO AND B.DEPTNO = '20' ); DELETE FROM EMP A WHERE A.DEPTNO = ( SELECT B.DEPTNO FROM DEPT B WHERE A.DEPTNO = B.DEPTNO AND B.DEPTNO = '20' ); DELETE FROM EMP A WHERE (A.DEPTNO, A.ENAME) = ( SELECT B.DEPTNO, B.NAME FROM DEPT B WHERE B.DEPTNO = '20' );
공휴일, 토요일, 일요일을 제외한 평일 구하기 SELECT COUNT(1) CNT FROM (SELECT TO_CHAR(SDT + LEVEL - 1, 'YYYYMMDD') DT , TO_CHAR(SDT + LEVEL - 1, 'D') D FROM (SELECT TO_DATE('20150501', 'YYYYMMDD') SDT , TO_DATE('20150531', 'YYYYMMDD') EDT FROM DUAL) CONNECT BY LEVEL
SELECT A.OWNER , A.COLUMN_ID , A.COLUMN_NAME , DECODE(NULLABLE,'N','Not Null') NULLABLE , DATA_TYPE ||'(' ||CASE WHEN DATA_TYPE IN ('NUMBRER') THEN DATA_PRECISION||','||DATA_SCALE WHEN DATA_TYPE IN ('DATE') THEN '' ELSE TO_CHAR(DATA_LENGTH) END ||')' DATA_SIZE , DATA_DEFAULT DATA_DEFAULT , B.COMMENTS COMMENTS FROM ALL_TAB_COLUMNS A , ALL_COL_COMMENTS B WHERE A.TABLE_NAME = '테이블명' AND A.OWNER = '..
728x90
반응형
