develog

INSERT ALLINTO AATEST (SEQ, TITLE, REG_TS) VALUES (11, 11, SYSTIMESTAMP)INTO AATEST (SEQ, TITLE, REG_TS) VALUES (22, 22, SYSTIMESTAMP)INTO AATEST (SEQ, TITLE, REG_TS) VALUES (33, 33, SYSTIMESTAMP)SELECT * FROM DUAL;

exists, 서브 쿼리에 결과 행이 있을때만 조건 만족 CASE1SELECT *FROM( SELECT 'AA' NM FROM DUAL UNION ALL SELECT 'BB' NM FROM DUAL UNION ALL SELECT 'CC' NM FROM DUAL)WHERE EXISTS( SELECT NULL FROM DUAL WHERE ROWNUM = 1); CASE2SELECT *FROM( SELECT 'AA' NM FROM DUAL UNION ALL SELECT 'BB' NM FROM DUAL UNION ALL SELECT 'CC' NM FROM DUAL)WHERE EXISTS( SELECT NULL FROM DUAL WHERE ROWNUM = 0);

( CASE WHEN COL1 IS NOT NULL AND TRANSLATE(COL1, 'A1234567890', 'A') IS NULL THEN '숫자' ELSE '숫자 아님' END) 참고: http://oracleerp.co.kr/bbs/board.php?bo_table=database&wr_id=12

SELECT * FROM DBA_TAB_PRIVS; SELECT OWNER, TABLE_NAME, GRANTOR, GRANTEE, PRIVILEGE, GRANTABLE, HIERARCHY FROM USER_TAB_PRIVS WHERE OWNER = 'SCOTT' AND INSTR(TABLE_NAME, '$') = 0 ORDER BY OWNER, TABLE_NAME, GRANTOR, GRANTEE, PRIVILEGE ; SELECT * FROM ALL_OBJECTS WHERE 1=1 AND INSTR(OBJECT_NAME, 'USER') > 0 AND INSTR(OBJECT_NAME, 'PRIVS') > 0 ORDER BY OBJECT_NAME ; USER_AQ_AGENT_PRIVS USER_COL_PRI..

DECLARE PROCEDURE P1 IS BEGIN RAISE_APPLICATION_ERROR(-20000, '에러임!'); END; PROCEDURE P2 IS BEGIN P1; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; BEGIN P2; END;

SELECT TABLE_NAME, COLUMN_NAMEFROM USER_IND_COLUMNSWHERE INDEX_NAME = 'IDX_MY_TABLE_01'