메뉴 건너뛰기

검색창 닫기

tnt_db

Oracle Conditions

구퍼 2008.09.17 16:03 조회 수 : 5844

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.
OR    <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
Delete
GROUP BY & Having
Insert
Model Clause
Nested Tables
NULL
Operators (Built-in)
Regular Expressions
Select
Update
WHERE Clause
WildCards
이 게시물을
이 글의 추천인 목록 수정 삭제 목록
쓰기 태그
위로