Oracle Conditions

구퍼 2008.09.17 16:03 조회 수 : 8647

General Information

Condition Precedence

SQL Operators are evaluated before conditions

Order	Operator
1	=, !=, <, >, <=, >=, !=, <>, ^=, ~=
2	IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF TYPE
3	NOT
4	AND
5	OR

EXISTS

EXISTS Semijoin

EXISTS (<subquery>)

SELECT table_name
FROM user_tables t
WHERE EXISTS (
SELECT table_name
FROM user_indexes i
WHERE i.table_name = t.table_name);

SELECT COUNT(*)
FROM serv_inst
WHERE srvr_id = 503;

SELECT COUNT(*)
FROM dual
WHERE EXISTS (
SELECT NULL
FROM serv_inst
WHERE srvr_id = 503
AND srvr_id IS NOT NULL);

NOT EXISTS

NOT EXISTS (<subquery>)

SELECT table_name
FROM user_tables t
WHERE NOT EXISTS (
SELECT table_name
FROM user_indexes i
WHERE i.table_name = t.table_name);

FLOATING POINT

INFINITE

Determines whether an expression is infinite r is the undefined result of an operation (that is, is not a number)

-- to create all_objs: click here

SELECT COUNT(*)
FROM all_objs;
WHERE data_object_id IS NOT INFINITE;

NAN

Determines whether an expression is infinite r is the undefined result of an operation (that is, is not a number)

-- to create all_objs: click here

SELECT COUNT(*)
FROM all_objs
WHERE data_object_id IS NOT NAN;

GROUP COMPARISON

ALL Test

Evaluates to TRUE if the query returns no rows

ALL(expression_list | sub_query)

conn scott/tiger

SELECT ename, sal, deptno
FROM emp;

SELECT sal
FROM emp WHERE deptno = 30;

SELECT ename, sal, deptno FROM emp WHERE sal >= ALL (
SELECT sal FROM emp WHERE deptno = 30);

ANY Test

Evaluates to FALSE if the query returns no rows

ANY(expression_list | sub_query)

conn scott/tiger

SELECT ename, sal, deptno FROM emp WHERE sal >= ANY
(SELECT sal FROM emp WHERE deptno = 30);

Note: <col> = ANY(list) is equivalent to <col> IN (list)

SOME Test

Evaluates to FALSE if the query returns no rows.

SOME(expression_list | sub_query)

conn scott/tiger

SELECT ename, sal, deptno FROM emp WHERE sal >= SOME (
SELECT sal FROM emp WHERE deptno = 30);

INTERROW

IS ANY

See the Model Clause link under Related Topics

IS PRESENT

See the Model Clause link under Related Topics

IS OF TYPE

Basic syntax

Tests object instances based on specific type information

conn uwclass/uwclass

CREATE TYPE person_t AS OBJECT
(name VARCHAR2(30), ssn NUMBER) NOT FINAL;
/

CREATE TYPE employee_t UNDER person_t
(dept_id NUMBER, salary NUMBER) NOT FINAL;
/

CREATE TYPE part_time_emp_t UNDER employee_t
(num_hrs NUMBER);
/

CREATE TABLE persons OF person_t;

INSERT INTO persons VALUES (person_t('Morgan',1234));
INSERT INTO persons VALUES (employee_t('Cline',32456,12,10000));
INSERT INTO persons VALUES (part_time_emp_t('Kyte',5678,13,1000,20));

SELECT *
FROM persons;

SELECT *
FROM persons p
WHERE VALUE(p) IS OF TYPE (employee_t);

With NOT Operator

Tests object instances based on specific type information

SELECT *
FROM persons p
WHERE VALUE(p) IS NOT OF (ONLY part_time_emp_t);

With ONLY Clause

Tests object instances based on specific type information

SELECT *
FROM persons p
WHERE VALUE(p) IS OF (ONLY part_time_emp_t);

LIKE (Be sure to check the link on Wildcards at the bottom of the page)

Syntax

LIKE(
str VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

LIKE(
str VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET str%CHARSET,
esc VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

Syntax

NOT_LIKE(
str VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

NOT_LIKE(
str VARCHAR2 CHARACTER SET ANY_CS,
pat VARCHAR2 CHARACTER SET str%CHARSET,
esc VARCHAR2 CHARACTER SET str%CHARSET)
RETURN BOOLEAN;

Condition Variations

Variant	Purpose
LIKE	defined by the input character set
LIKEC	with Unicode
LIKE2	with UCS2 code points
LIKE4	with UCS4 code points

Ending Wildcard

Find any string that begins with the letter 'S'

conn scott/tiger

SELECT ename FROM emp WHERE ename LIKE 'S%';

Leading Wildcard

Find any string that ends with the letter 'S'

SELECT ename FROM emp WHERE ename LIKE '%S';

Multiple Wildcards

Find any string that contains, anywhere, the letter 'S'

SELECT ename FROM emp WHERE ename LIKE '%S%';

Single Character Wildcard

Find any string that contains the letter 'A' followed by any single character which followed by the letter 'E'

SELECT ename FROM emp WHERE ename LIKE '%A_E%';

LOGICAL

AND

<value_or_expression> >= <value_or_expression>
AND <value_or_expression> >= <value_or_expression>

SELECT table_name
FROM all_tables
WHERE initial_extent IS NOT NULL
AND next_extent IS NULL;

NOT

WHERE <value_or_expression> NOT <condition> <comparison_condition> <value_or_expression>

See demos for EMPTY, EXISTS, IN, INFINITE, and NULL.

<value_or_expression> >= <value_or_expression>
OR <value_or_expression> >= <value_or_expression>

SELECT owner, table_name
FROM all_tables
WHERE (table_name LIKE 'D%' OR owner = 'SYSTEM');

MEMBERSHIP

IN (list)

<value_or_expression> IN (<expression_list>)

SELECT owner, table_name
FROM all_tables
WHERE owner IN ('SYS', 'SYSTEM');

IN (subquery)

<value_or_expression> IN (<expression_list>)

SELECT owner, table_name
FROM all_tables
WHERE initial_extent IN (
SELECT MIN(initial_extent)
FROM all_tables);

NOT IN Antijoin

<value_or_expression> NOT IN (<expression_list>)

SELECT owner, table_name
FROM all_tables
WHERE initial_extent NOT IN (
SELECT MIN(initial_extent)
FROM all_tables);

Complex IN Demo

Using CAST

-- based on emp table in scott/tiger

set serveroutput on

DECLARE
i     PLS_INTEGER;
InStr VARCHAR2(20) := '10';
BEGIN
SELECT COUNT(*)
INTO i
FROM emp
WHERE deptno IN (InStr);

dbms_output.put_line(i);
END;
/

--==============================

DECLARE
i     PLS_INTEGER;
InStr VARCHAR2(20) := '10,30';
BEGIN
SELECT COUNT(*)
INTO i
FROM emp
WHERE deptno IN (InStr);

dbms_output.put_line(i);
END;
/

--==============================

CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(20);
/

DECLARE
i PLS_INTEGER;
x InStrTab := InStrTab('10','30');
BEGIN
SELECT COUNT(*)
INTO i
FROM emp
WHERE deptno IN (
    SELECT column_value
    FROM TABLE(CAST(x AS InStrTab)));

dbms_output.put_line(i);
END;
/

-- or

DECLARE
i PLS_INTEGER;
x InStrTab := InStrTab('10','30');
BEGIN
SELECT COUNT(*)
INTO i
FROM emp
WHERE deptno IN (
    SELECT column_value
    FROM TABLE(x));

dbms_output.put_line(i);
END;
/

NESTED TABLE

Is A Set

Tests whether a specified nested table is composed of unique element

conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab cust_address_tab_typ)
NESTED TABLE cust_address_ntab STORE AS cust_add_ntab_store;

desc customer_demo

UPDATE customer_demo cd
SET cust_address_ntab = CAST(MULTISET(SELECT cust_address
FROM customers c
WHERE c.customer_id = cd.customer_id)
AS cust_address_tab_typ);

set linesize 121

SELECT customer_id, cust_address_ntab
FROM customer_demo
WHERE cust_address_ntab IS A SET
AND customer_id < 106;

Is Empty

Tests whether a specified nested table is empty

conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab cust_address_tab_typ)
NESTED TABLE cust_address_ntab STORE AS cust_add_ntab_store;

desc customer_demo

SELECT COUNT(*)
FROM customer_demo
WHERE cust_address_ntab IS NOT EMPTY;

UPDATE customer_demo cd
SET cust_address_ntab = CAST(MULTISET(SELECT cust_address
FROM customers c
WHERE c.customer_id = cd.customer_id)
AS cust_address_tab_typ)
WHERE ROWNUM <11;

SELECT COUNT(*)
FROM customer_demo
WHERE cust_address_ntab IS NOT EMPTY;

Member

Tests whether an element is a member of a nested table

conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab cust_address_tab_typ)
NESTED TABLE cust_address_ntab STORE AS cust_add_ntab_store;

desc customer_demo

UPDATE customer_demo cd
SET cust_address_ntab = CAST(MULTISET(SELECT cust_address
FROM customers c
WHERE c.customer_id = cd.customer_id)
AS cust_address_tab_typ);

SELECT customer_id, cust_address_ntab
FROM customer_demo
WHERE cust_address_typ('8768 N State Rd 37', 47404, 'Bloomington', 'IN', 'US')
MEMBER OF cust_address_ntab;

Submultiset

Tests whether a specified nested table is a submultiset of another specified nested table

conn oe/oe

CREATE TABLE customer_demo AS
SELECT *
FROM oe.customers;

CREATE OR REPLACE TYPE cust_address_tab_typ AS
TABLE OF cust_address_typ;
/

ALTER TABLE customer_demo
ADD (cust_address_ntab1 cust_address_tab_typ,
cust_address_ntab2 cust_address_tab_typ)
NESTED TABLE cust_address_ntab1 STORE AS cust_add_ntab1_store
NESTED TABLE cust_address_ntab2 STORE AS cust_add_ntab2_store;

desc customer_demo

UPDATE customer_demo cd
SET cust_address_ntab1 = CAST(MULTISET(SELECT cust_address
FROM customers c
WHERE c.customer_id = cd.customer_id)
AS cust_address_tab_typ),
cust_address_ntab2 = CAST(MULTISET(SELECT cust_address
FROM customers c
WHERE c.customer_id = cd.customer_id)
AS cust_address_tab_typ);

SELECT customer_id, cust_address_ntab1
FROM customer_demo
WHERE cust_address_ntab1 SUBMULTISET OF cust_address_ntab2;

NULL

IS NULL

IS NULL(s VARCHAR2 CHARACTER SET ANY_CS) RETURN BOOLEAN;

conn scott/tiger

SELECT ename FROM emp WHERE comm IS NULL;

IS NOT NULL

IS NOT NULL(s VARCHAR2 CHARACTER SET ANY_CS) RETURN BOOLEAN;

conn scott/tiger

SELECT ename FROM emp WHERE comm IS NOT NULL;

PATH

DEPTH

DEPTH is an ancillary function used only with the UNDER_PATH and EQUALS_PATH conditions. It returns the number of levels in the path specified by the UNDER_PATH condition with the same correlation variable

conn / as sysdba

desc resource_view

SELECT path(1), depth(2)
FROM resource_view
WHERE UNDER_PATH(res, '/sys/schemas', 1)=1
AND UNDER_PATH(res, '/sys/schemas', 2)=1;

EQUALS_PATH

The EQUALS_PATH condition determines whether a resource in the Oracle XML database can be found in the database at a specified path

SELECT any_path
FROM resource_view
WHERE EQUALS_PATH(res, '/sys/schemas/PUBLIC/www.w3.org')=1;

PATH

Returns the relative path that leads to the resource specified in the parent condition. Only used with EQUALS_PATH and UNDER_PATH

SELECT PATH(1), DEPTH(2)
FROM resource_view
WHERE UNDER_PATH(res, '/sys/schemas/PUBLIC', 1)=1
AND UNDER_PATH(res, '/sys/schemas/PUBLIC', 2)=1;

UNDER_PATH

The UNDER_PATH condition determines whether resources specified in a column can be found under a particular path specified by path_string in the Oracle XML database repository. The path information is computed by the RESOURCE_VIEW view, which you query to use this condition.

SELECT any_path
FROM resource_view
WHERE UNDER_PATH(res, '/sys/schemas/PUBLIC/www.w3.org')=1;

RANGE

BETWEEN

<value_or_expression> BETWEEN <value> AND <value>

SELECT table_name, num_rows
FROM all_tables
WHERE num_rows BETWEEN 100 AND 500;

NOT BETWEEN

<value_or_expression> NOT BETWEEN <value> AND <value>

SELECT table_name, num_rows
FROM all_tables
WHERE num_rows NOT BETWEEN 100 AND 500;

REGULAR EXPRESSIONS

REGEXP_LIKE

See Regular Expressions Link under Related Topics

SINGLE COMPARISON

Equal

<value_or_expression> = <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent = next_extent;

Not Equal

<value_or_expression> <> <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent <> next_extent;

<value_or_expression> != <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent != next_extent;

<value_or_expression> ^= <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent ^= next_extent;

Not Equal (PL/SQL only)

<value_or_expression> ~= <value_or_expression>

set serveroutput on

BEGIN
IF 1 ~= 0 THEN
dbms_output.put_line('1 is not equal to zero.');
END IF;
END;
/

Less Than

<value_or_expression> < <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent < next_extent;

Less Than Or Equal To

<value_or_expression> <= <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent <= next_extent;

Greater Than

<value_or_expression> > <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent > next_extent;

Greater Than Or Equal To

<value_or_expression> >= <value_or_expression>

SELECT table_name, initial_extent, next_extent
FROM all_tables
WHERE initial_extent >= next_extent;

Related Topics

Operators (Built-in)

이 게시물을

이 글의 추천인 목록 수정 삭제 목록

번호	제목	글쓴이	날짜	조회 수
26	다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법 (1)	박상현	2006.06.13	6891
25	다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법 (2)	박상현	2006.06.13	6828
24	달력 생성 쿼리	박상현	2006.06.13	6330
23	소수(prime number) 구하기	박상현	2006.06.13	6331
22	문자열에서 특정 단어 갯수 구하기	박상현	2006.06.13	6565
21	최대공약수, 최소공배수 구하기	박상현	2006.06.13	7057
20	시간선분(선분이력)의 합집합 구하기	박상현	2006.06.13	7902
19	PL/SQL에서 쿼리시 Array Processing 처리방법	박상현	2006.10.27	6725
18	최단거리찾기 혹은 멀리 떨어진 두지점간의 거리의 합구하기	박상현	2006.11.07	6929
17	순환구조에서 임시테이블에 하위 조직을 엮어서 넣는 프로시져	하늘과컴	2007.10.01	7880
16	분산트랜젝션 가능여부 확인 방법	하늘과컴	2008.05.29	7661
15	현재의 년월일시분초 구하기	구퍼	2008.09.10	7995
14	문자열 연결, 날짜 변환, 오늘 구하기	구퍼	2008.09.10	7020
13	Basic Update Statements	구퍼	2008.09.17	7865
12	Basic Delete Statements	구퍼	2008.09.17	7797
»	Conditions	구퍼	2008.09.17	8647
10	oracle 10g tnsnames.ora, listener.ora위치	구퍼	2008.09.29	6261
9	Update ~ Select 구문 예제	구퍼	2008.10.31	7575
8	mssql update예제(select ~ update)	구퍼	2008.10.31	8750
7	데이타에 한글로 공백이 있는 경우 값을 가져오지 못하고 오류가 발생하는 경우 처리	구퍼	2009.03.23	8213

쓰기 태그

첫 페이지 1 2 3 4 5 6 7 끝 페이지

tnt_db

Oracle Conditions

댓글 0

tnt_db

Oracle Conditions

댓글 0

로그인

LOGIN