-- 타입
NUMBER
BINARY_INTEGER
PLS_INTEGER
VARCHAR2
CHAR
LONG
NCHAR, NVARCHAR2
RAW, LONG RAW
DATE
TIMESTAMP
INTERVAL
ROWID
UROWID
BOOLEAN
-- 변환 함수
TO_CHAR
TO_DATE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TO_NUMBER
RAWTOHEX
HEXTORAW
CHARTOROWID
ROWIDTOCHAR
-- 문자열 함수
CONCAT(STR1, STR2)
INITCAP(STR)
LOWER(STR)
LPAD(STR1, X[,STR2])
LTRIM(STR1, STR2)
REPLACE(STR, SEARCH_STR, [,REPLACE_STR])
RPAD(STR1, X[STR2])
RTRIM(STR1 [,STR2])
SOUNDEX(STR)
SUBSTR(STR, A[,B])
SUBSTRB(STR, A[,B])
SUBSTRC(STR, A[,B])
SUBSTR2(STR, A[,B])
SUBSTR4(STR, A[,B])
TRANSLATE(STR, FROM_STR, TO_STR)
TRIM(STR)
UPPER(STR)
-- 숫자값 문자 함수
ASCII(STR)
INSTR(STR1, STR2[,A][,B])
INSTRB(STR1, STR2[,A][,B])
INSTRC(STR1, STR2[,A][,B])
INSTR2(STR1, STR2[,A][,B])
INSTR4(STR1, STR2[,A][,B])
LENGTH(STR)
LENGTHB(STR)
LENGTHC(STR)
LENGTH2(STR)
LENGTH4(STR)
-- NLS 함수
CONVERT(STR, DESC_CHARSET[,SOURCE_CHARSET])
NCHAR(X)
NLS_CHARSET_DECL_LEN(BYTE_WIDTH, CHARSET)
NLS_CHARSET_DECL_ID(CHARSET_NAME)
NLS_CHARSET_NAME(CHARSET_ID)
NLS_INITCAP(STR[,NLSPARAMS])
NLS_LOWER(STR[,NLSPARAMS])
NLS_UPPER(STR[,NLSPARAMS])
NLSSORT(STR[,NLSPARAMS])
TRANSLATE(STR USING {CHAR_CS|NCHAR_CS})
UNISTR(S)
-- 숫자 함수
ABS(X)
ACOS(X)
ASIN(X)
ATAN(X)
ATAN2(X, Y)
BITAND(X, Y)
CEIL(X)
COS(X)
COSH(X)
EXP(X)
FLOOR(X)
LN(X)
LOG(X, Y)
MOD(X, Y)
POWER(X, Y)
ROUND(X[,Y])
SIGN(X)
SIN(X)
SINH(X)
SORT(X)
TAN(X)
TANH(X)
TRUNC(X[,Y])
WIDTH_BUCKET(X, MIN, MAX, NUM_BUCKETS)
-- 데이트 함수
ADD_MONTHS(D, X)
CURRENT_DATE
CURRENT_TIMESTAMP[(PRECISION)]
DBTIMEZONE
EXTRACT({YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR} FROM DATETIME OR INTERVAL)
LAST_DAY(D)
LOCALTIMESTAMP[(PRECISION)]
MONTH_BETWEEN(DATE1, DATE2)
NEW_TIME(D, ZONE1, ZONE2)
NEXT_DAY(D, STR)
ROUND(D, [,FORMAT])
SESSIONTIMEZONE
SYS_EXTRACT_UTC(DATETIME)
SYSDATE
SYSTIMESTAMP
TRUNC(D[,FORMAT])
TZ_OFFSET(TIMEZONE)
-- 변환 함수
ASCIISTR(STR)
BIN TO NUM(NUM[,NUM]...)
CHARTOROWID(STR)
COMPOSE(STR)
DECOMPOSE(STR)
FROM_TZ(TIMESTAMP, TIMEZONE)
HEXTORAW(STR)
NUMTODSINTERVAL(X, UNIT)
NUMTOYMINTERVAL(X, UNIT)
REFTOHEX(REFVALUE)
RAWTOHEX(RAWVALUE)
RAWTONHEX(RAWVALUE)
ROWIDTOCHAR(ROWID)
ROWIDTONCHAR(ROWID)
TO_CHAR
TO_CLOB(STR)
TO_DATE
TO_DSINTERVAL(STR[,NSLPARAMS])
TO_LOB(LONG COLUMN)
TO_NCHAR
TO_MULTI_BYTES(STR)
TO_NCLOB(STR)
TO_NUMBER
TO_SINGLE_BYTE(STR)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL(STR)
-- 집계 함수와 해석 함수
AVG([DISTINCT|ALL] COL)
CORR(X1, X2)
COUNT(*|[DISTINCT|ALL] COL)
COVAR_POP(X1, X2)
COVAR_SAMP(X1, X2)
CUME_DIST
DENSE_RANK
GROUP_ID()
GROUPING
GROUPING_ID
MAX([DISTINCT|ALL] COL)
MIN([DISTINCT|ALL] COL)
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR 함수
STDDEV([DISTINCT|ALL] COL)
STDDEV_POP(COL)
STDDEV_SAMP(COL)
SUM([DISTINCT|ALL] COL)
VAR_POP(X)
VAR_SAMP(X)
VARIANCE([DISTINCT|ALL] COL)
-- 기타 함수
BFILENAME(DIRECTORY, FILE_NAME)
COALESCE(EXPR1, ...,[EXPRN])
DECODE(BASE_EXPR, COMP1, VAL1, COMP2, VAL2, ... DEFAULT)
DUMP
EMPTY_BLOB/EMPTY_CLOB
EXISTNODE(XMLTYPE_INSTANCE, XPATH_STRING)
EXTRACT(XMLTYPE_INSTANCE, XPATH_STRING)
GREATEST(EXPR1[,EXPR2] ...)
LEAST(EXPR1[,EXPR2] ...)
NULLIF(A, B)
NVL(EXPR1, EXPR2)
NVL2(EXPR1, EXPR2, EXPR3)
SYS_CONNECT_BY_PATH
SYS_CONTEXT(NAMESPACE, PARAM[,LENGTH])
SYS_DBURIGEN
SYS_GUID
SYS_TYPEID(OBJECT_TYPE)
SYS_XMLAGG
SYS_XMLGEN
TREAT(EXPR AS [REF][SCHEMA.]TYPE)
UID
USER
USERNV
VSIZE(X)
-- 커서 속성
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT
NO_DATA_FOUND
-- 오라클 예외
DUP_VAL_ON_INDEX
TIMEOUT_ON_RESOURCE
TRANSCATION_BACKED_OUT
INVALID_CURSOR
NOT_LOGGED_ON
LOGIN_DENIED
NO_DATA_FOUND
SYS_INVALID_ROWID
TOO_MANY_ROWS
ZERO_DIVIDE
INVALID_NUMBER
STORAGE_ERROR
PROGRAM_ERROR
VALUE_ERROR
ROWTYPE_MISMATCH
CURSOR_ALREADY_OPEN
ACCESS_INTO_NULL
COLLECTION_IS_NULL
SUBSCRIP_OUTSIDE_LIMIT
SUBSCRIPT_BEYOND_COUNT
CASE_NOT_FOUND
SELF_IS_NULL
-- 컬렉션 메소드
EXISTS
COUNT
LIMIT
FIRST&LAST
NEXT&PRIOR
EXTEND
TRIM
DELETE
-- EXECUTE IMMEDIATE로 쿼리하기
V_SQL VARCHAR2(200);
V_CLASS CLASSES%ROWTYPE;
V_SQL := ' SELECT * FROM TBL_STUDENT' || ' WHERE DESC = :desc';
EXECUTE IMMEDIATE V_SQL INTO V_CLASS;
-- 프로시저 파라미터
CREATE OR REPLACE PROCEDURE MODETEST
(
P_INPARAM IN NUMBER,
P_OUTPARAM OUT NUMBER,
P_IOPARAM IN OUT NUMBER
) IS
V_LOCALVAR NUMBER := 0;
BEGIN
IF (P_INPARAM IS NULL) THEN
DBMS_OUTPUT.PUT('NULL');
ELSE
DBMS_OUTPUT.PUT('P_INPARAM' || P_INPARAM);
END IF;
END;
-- 모의칼럼
CURRVAL, NEXTVAL
LEVEL
ROWID
ROWNUM
-- 일반구조
DECLARE
-- 선언절
BEGIN
-- 실행절
EXCEPTION
-- 예외 처리절
WHEN OTHERS THEN
END;
-- RETURNNING
V_NEWROWID ROWID;
INSERT INTO TBL_STUDENT (FIRST_NAME, LAST_NAME) VALUES ('Son','JungSeung');
RETURNING ROWID INTO V_NEWROWID;
DBMS_OUTPUT.PUT_LINE(V_NEWROWID);
-- 조건문
DECLARE
V_TOTALSTUDENTS NUMBER;
V_CNT BINARY_INTEGER := 1;
BEGIN
SELECT COUNT(*) INTO V_TOTALSTUDENTS FROM TBL_STUDENT;
IF V_TOTALSTUDENTS = 0 THEN
-- 쿼리
ELSEIF V_TOTALSTUDENTS < 5 THEN
-- 쿼리
ELSE
-- 쿼리
END IF;
LOOP
-- 쿼리
V_CNT := V_CNT + 1;
EXIT THEN V_CNT > 50;
END LOOP;
FOR V_CNT IN 1..50 LOOP
-- 쿼리
END LOOP;
CASE V_TOTALSTUDENTS
WHEN 0 THEN
-- 쿼리
WHEN 5 THEN
-- 쿼리
ELSE
-- 쿼리
END CASE
END
-- 커서
DECLARE
V_FIRSTNAME VARCHAR2(20);
V_LASTNAME VARCHAR2(20);
CURSOR C_STUDENTS IS
SELECT FIRST_NAME, LAST_NAME FROM TBL_STUDENT;
BEGIN
OPEN C_STUDENTS;
LOOP
FETCH C_STUDENTS INTO V_FIRSTNAME, V_LASTNAME;
EXIT WHEN C_STUDENTS%NOTFOUND;
END LOOP;
CLOSE C_STUDENTS;
END;
-- 프로시저
CREATE OR REPLACE PROCEDURE SERVICEPROCEDURE AS
BEGIN
NULL;
END SERVICEPROCEDURE;
-- 라벨, 프로시저 쿼리
<<L_TEMP_TABLE>>, CREATE OR REPLACE PROCEDURE P_TEMP_TABLE AS
DECLARE
V_NUM1 NUMBER := 1;
V_NUM2 NUMBER := 2;
V_STRING1 VARCHAR2(50) = 'Hello World!';
V_STRING2 VARCHAR2(50) = 'Hi, Oracle!';
V_OUTPUTSTR VARCHAR2(50);
BEGIN
INSERT INTO TEMP_TABLE(NUM_COL, CHAR_COL) VALUES (V_NUM1, V_STRING1);
INSERT INTO TEMP_TABLE(NUM_COL, CHAR_COL) VALUES (V_NUM2, V_STRING2);
SELECT CHAR_COL INTO V_OUTPUTSTR FROM TEMP_TABLE WHERE NUM_COL = V_NUM1;
DBMS_OUTPUT.PUT_LINE(V_OUTPUTSTR);
SELECT CHAR_COL INTO V_OUTPUTSTR FROM TEMP_TABLE WHERE NUM_COL = V_NUM2;
DBMS_OUTPUT.PUT_LINE(V_OUTPUTSTR);
ROLLBACK;
END L_TEMP_TABLE;, END P_TEMP_TABLE;
-- 트리거
CREATE OR REPLACE TRIGGER ONLYPOSITIVE
BEFORE INSERT OR UPDATE OF NUM_COL ON TEMP_TABLE
FOR EACH ROW
BEGIN
IF :NEW.NUM_COL < 0 THEN
RAISE_APPLICATION_ERROR(-20100, 'Please insert a positive value');
END IF;
END ONLYPOSITIVE;
-- 트랜잭션
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
COMMIT;
END;
-- 디비 링크
CREATE DATABASE LINK EXAMPLE_BACKUP
CONNECT TO EXAMPLE IDENTIFIED BY EXAMPLE
USING 'BACKUP_DATABASE';
UPDATE TBL_STUDENT@EXAMPLE_BACKUP SET FIRST_NAME = 'SON' WHERE ID=10005;
-- 페이징 쿼리
select * from
(
select rownum as rnum, name, address from members
) where rnum between (:PageNo * (:PageNo-1)) and ((:PageNo * (:PageNo-1)) + :PageSize )
SELECT * FROM (
SELECT ROWNUM AS RNUM, A.* FROM (
{검색쿼리 - 정렬이 필요할 경우 정렬조건 포함}
) A WHERE ROWNUM <= {범위까지}
) WHERE RNUM > {범위부터};
SELECT * FROM (
SELECT /*+ INDEX_ASC or INDEX_DESC(A {정렬조건 인덱스명}) */
ROWNUM AS RNUM, A.* FROM (
{검색쿼리 - 정렬이 필요한 경우 정렬조건을 포함하지 않고 ORACLE 힌트사용}
) A WHERE ROWNUM <= {범위까지}
) WHERE RNUM > {범위부터};
select * from members where age>20 and CEIL(rownum/:PageSize) = :PageNo
