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               MediumNolte                MediumLoren                MediumGueney               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 1970GROUP BY GROUPING SETS   (   (prod_category, prod_subcategory, country_id, cust_city),   (prod_category, prod_subcategory, country_id),   (prod_category, prod_subcategory),    country_id  );