ORACLE语句常用技巧收集

Simple CASE Example

For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.

SELECT cust_last_name,
   CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium' END
   FROM customers;

CUST_LAST_NAME       CASECR
-------------------- ------
...
Bogart               Medium
Nolte                Medium
Loren                Medium
Gueney               Medium





Simple FunctionExample






CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE) 
   RETURN NUMBER IS
   emp_hiredate DATE;
 before number :=0;
 after number:=0;
begin
 loop
  fetch cur into emp_hiredate;
  exit when cur%NOTFOUND;
  if emp_hiredate > mgr_hiredate then
    after:=after+1;
  else
    before:=before+1;
  end if;
 end loop;
 close cur;
 if before > after then
  return 1;
 else
  return 0;
 end if;
end;
Simple  Date Example
The following example converts the datetime value of one time zone to another time zone:
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00', 
      'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') 
   AT TIME ZONE 'America/Los_Angeles' "West Coast Time" 
   FROM DUAL;

West Coast Time
------------------------------------------------
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES
Simple GROUP BY clause, you can use either the upper or lower form of expression list:
SELECT department_id, MIN(salary), MAX(salary)
   FROM employees
   GROUP BY department_id, salary;

SELECT department_id, MIN(salary), MAX(salary)
   FROM employees
   GROUP BY (department_id, salary);

In ROLLUP, CUBE, and GROUPING SETS clauses of GROUP BY clauses, you can combine individual expressions with sets of expressions in the same expression list. The following example shows several valid grouping sets expression lists in one SQL statement:
SELECT 
prod_category, prod_subcategory, country_id, cust_city, count(*)
   FROM  products, sales, customers
   WHERE sales.prod_id = products.prod_id 
   AND sales.cust_id=customers.cust_id 
   AND sales.time_id = '01-oct-00'
   AND customers.cust_year_of_birth BETWEEN 1960 and 1970
GROUP BY GROUPING SETS 
  (
   (prod_category, prod_subcategory, country_id, cust_city),
   (prod_category, prod_subcategory, country_id),
   (prod_category, prod_subcategory),
    country_id
  );