오라클 함수, SQL Functions(10g) - NUL-Related Functions, Environment and Identifier Functions

*SQL Functions(10g) - NUL-Related Functions

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

 

1. COALESCE(Expr) - Expr List에서 첫번째 NULL이 아닌 값을 반환한다. Expr List의 값이 모두 NULL이면 NULL을 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm#i1001341

 

* 아래의 3개는 모두 유사하다.

1) CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

2) COALESCE(expr1, expr2, ..., exprn), for n >= 3

3) CASE WHEN expr1 IS NOT NULL THEN exp1

 

SQL> SELECT coalesce('','', 'Expr1', 'Expr2') FROM DUAL;

 

SQL> SELECT ename, comm, sal, coalesce(comm, sal, 0) AS COAL_TEST
FROM emp;

 

 

SQL> SELECT last_name, manager_id, commission_pct,
COALESCE(manager_id,commission_pct, -1) comm
FROM employees
ORDER BY commission_pct;

2. LNNVL(Expr) - It provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or NUKNOWN and FALSE if the condition is TRUE. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions.

 

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm#i1479295

 

아래의 1)번과 2)번 결과는 동일하다.

 

 

 

1)

SQL> SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE LNNVL((SAL+COMM) >= 1800)

;

 

2)

SQL> SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE (SAL+COMM) < 1800 OR (SAL+COMM) IS NULL
;

 

 

SQL> SELECT COUNT(*) FROM employees WHERE commission_pct < .2;

SQL> SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

 

3. NULLIF(Expr1, Expr2) - Expr1과 Expr2가 같으면 NULL 아니면 Expr1을 반환한다. Expr1에는 NULL이 할당될 수 없다.

 

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions102.htm#i1001340

 

SQL> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

 

4. NVL(Expr1, Expr2) - Expr1이 NULL이면 Expr2를 반환하고, Expr1이 NOT NULL이면 Expr1을 반환한다. 

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm#i91798

 

SQL> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
   "COMMISSION" FROM employees;

 

 

5. NVL2(Expr1, Expr2, Expr3) - Expr1이 NOT NULL이면 Expr2를 반환한다. 만약 Expr1이 NULL이면 Expr3를 반환한다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm#i91806

 

SQL> SELECT last_name, salary, commission_pct, NVL2(commission_pct,
   salary + (salary * commission_pct), salary) income
   FROM employees;

 

 

*SQL Functions(10g) -  Environment and Identifier Functions

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

 

1. SYS_CONTEXT  - 환경에 관련된 정보들을 보여준다.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#i1038176

 

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

 

 

ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USERID, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL

2. SYS_GUID - SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions168.htm#i79194

3. SYS_TYPEID

 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions169.htm#i1044156

4. UID - UID는 세센 사용자를 중복 없이 구분하기 위해 정수를 반환한다.

 

SQL> SELECT UID FROM DUAL;

 

 

5. USER - 세션 사용자(the user who logged on)의 이름을 반환한다.(데이터 타입은 VARCHAR2이다.)

 

SQL> SELECT USER, UID FROM DUAL; 

 

 

6. USERENV - 현재 세션의 정보를 반환한다. 이러한 정보들을 통해 application-specific audit trail table를 작성하거나 현재 세션의 determining the language-specific characters를 알 수 있다.

 

SQL> SELECT USERENV('LANGUAGE') AS "Lang",
--USERENV('ENTRYID') "ENTRYID",
--USERENV('ISDBA') "ISDBA",
USERENV('LANG') AS  "LANG",
USERENV('SESSIONID') "SESSIONID"
--USERENV('TERMINAL') "TERMINAL"
FROM DUAL;