오라클 연산자

1.  About SQL Operators

Operators manipulate individual data item called operands or agruments. Operators are represented by special characters or by keywords.

 

 

2. Unary and Binary Operators

단항연산자, 이항연산자

The two general classes of operators are:

 

• unary : A unary operator operates on only one operand. A unary operator typically appers with its operand in this format:
  
  operator operand
  
  
• binary : A binary operator operates on two operands. A binary operator appears with its operands in this format :
  
  operand1 operator operand2 

Other operators with special formats accept more than two operands. If an operator is given a null operand, then the result is always null. The only operator that does not follow this rule is concatenation(||).

 

 

3. Arithmetic  Operators

산술연산자

Operator	Purpose	Example
+ -	When these denote a positive or negative expression, they are unary operators.	SELECT * FROM order_items WHERE quantity = -1;
+ -	When they add or subtract, they are binary operators.	SELECT 1+1 FROM DUAL ;
* /	Multiply, divide. These are binary operators	UPDATE employees SET salary = salary *1.1 ;

 

4. Concatenation Operator

접합연산자 쯤 되겠지 ~~

 

|| 

   - Concatenates character string and CLOB data

 

SQL> SELECT 'aaa' || 'bbb'  FROM DUAL ;

   --> aaabbb

 

The Result of concatenating two character strings is another character string. If both character string are of data type CHAR, then the result has data type CHAR and is limited to 2000 characters, If either string is of data type VARCHAR2, the result has data type VARCHAR2 and is limited to 4000 characters. If either argument is a CLOB, the result is a temporary CLOB. Trailing blanks in character string are preserved by concatenation, regardless of the data types of the string or CLOB

 

 

오라클에서 문자열을 합칠 때 '||'를 사용하는데

CHAR 과 CHAR 을 합치면 최대 2000,

CHAR 와 VARCHAR2, VARCHAR2와 VARCHAR2 이면 최대 4000 자까지 제한이 되고

CLOB이 들어가 있으면 CLOB 형태가 된다는 것 같다

빈 문자열의 끝에 있으면 문자열을 합쳐도 유지가 되는 것 같다. 

 

 

SQL> SELECT STR1 || 'CCCC' FROM (
             SELECT 'aaa' || 'bbb   ' AS STR1 FROM DUAL
          ) ;

   --> aaabbb   CCCC

 

만약 || 이 동작하지 않는 경우라고 한다면 CONCAT 함수로 이 기능을 대체할 수 있다. 

 

SQL> SELECT CONCAT('aaa', 'bbb') FROM DUAL;    

--> aaabbb 

 

 

5. Hierarchical Query Operators

Two operators, PRIOR and CONNECT_BY_ROOT, are valid only in hierachical queries

 

6. Set operators

집합 연산자

MINUS 의 경우 두 개의 테이블을 비교할 때 효과적으로 사용한 적이 있다. 

 

UNION        :  All distinct rows selected by either query 

UNION ALL  :  All rows selected by either query, including all duplicates

INTERSECT   :  All distinct rows selected by both queries

MINUS        :  All distinct rows selected by the first query but not the second

 

7. Multiset Operators

Multiset operators combine the result of two nested tables into a single nested table.

 

   - 안써봐서 그런지 잘 모르겠다.