2004年03月19日

慢慢学会体验生活,其实生活还是很美好的,只不过每个人看的角度不同而已,学会用积极的态度来看待!

朝露昙花,咫尺天涯,人道是黄河十曲,毕竟东流去。
八千年玉老,一夜枯荣,问苍天此生何必?

昨夜风吹处,落英听谁细数。
九万里苍穹,御风弄影,谁人与共?
千秋北斗,瑶宫寒苦,不若神仙眷侣,百年江湖

2004年03月18日

第7章 集合运算符
当我们需要将两个或者更多的SELECT语句结合起来。SQL中可以使用集合运算符来做到。ORACLE SQL支持下面4种类型的集合运算符:


 UNION ALL:结合两个SELECT语句结果为一个结果集。
 UNION:结合两个SELECT语句结果为一个结果,然后消除任何相同的行。
 MINUS:取一个语句的结果,然后消除同时出现在第二个语句种的行。
 INTERSECT:只返回那些同时出现在两个语句种的元素。



SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5;
CUST_NBR NAME
———- ——————————
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries



SELECT C.CUST_NBR, C.NAME
FROM CUSTOMER C
WHERE C.CUST_NBR IN (SELECT O.CUST_NBR
FROM CUST_ORDER O, EMPLOYEE E
WHERE O.SALES_EMP_ID = E.EMP_ID
AND E.LNAME = ‘MARTIN’);
CUST_NBR NAME
———- ——————————
4 Flowtech Inc.
8 Zantech Inc.



UNION ALL


SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
UNION ALL
SELECT C.CUST_NBR, C.NAME
FROM CUSTOMER C
WHERE C.CUST_NBR IN (SELECT O.CUST_NBR
FROM CUST_ORDER O, EMPLOYEE E
WHERE O.SALES_EMP_ID = E.EMP_ID
AND E.LNAME = ‘MARTIN’);
CUST_NBR NAME
———- ——————————
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
4 Flowtech Inc.
8 Zantech Inc.
7 rows selected.



UNION


SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
UNION
SELECT C.CUST_NBR, C.NAME
FROM CUSTOMER C
WHERE C.CUST_NBR IN (SELECT O.CUST_NBR
FROM CUST_ORDER O, EMPLOYEE E
WHERE O.SALES_EMP_ID = E.EMP_ID
AND E.LNAME = ‘MARTIN’);
CUST_NBR NAME
———- ——————————
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
4 Flowtech Inc.
5 Gentech Industries
8 Zantech Inc.
6 rows selected.


INTERSECT


SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
INTERSECT
SELECT C.CUST_NBR, C.NAME
FROM CUSTOMER C
WHERE C.CUST_NBR IN (SELECT O.CUST_NBR
FROM CUST_ORDER O, EMPLOYEE E
WHERE O.SALES_EMP_ID = E.EMP_ID
AND E.LNAME = ‘MARTIN’);
CUST_NBR NAME
———- ——————————
4 Flowtech Inc.


MINUS


SELECT CUST_NBR, NAME
FROM CUSTOMER
WHERE REGION_ID = 5
MINUS
SELECT C.CUST_NBR, C.NAME
FROM CUSTOMER C
WHERE C.CUST_NBR IN (SELECT O.CUST_NBR
FROM CUST_ORDER O, EMPLOYEE E
WHERE O.SALES_EMP_ID = E.EMP_ID
AND E.LNAME = ‘MARTIN’);
CUST_NBR NAME
———- ——————————
1 Cooper Industries
2 Emblazon Corp.
3 Ditech Corp.
5 Gentech Industries


第9章 DECODE 与 CASE


Function syntax                            Logic equivalent
DECODE(E1, E2, E3, E4)   IF E1 = E2 THEN E3 ELSE E4
NVL(E1, E2)                        IF E1 IS NULL THEN E2 ELSE E1
NVL2(E1, E2, E3)                IF E1 IS NULL THEN E3 ELSE E2


看例子:
SELECT lname,DECODE(manager_emp_id, NULL, ‘MANAGER’, ‘NON-MANAGER’) emp_type
FROM employee;
LNAME EMP_TYPE
——————– ———–
Brown MANAGER
Smith MANAGER
Blake MANAGER
Freeman NON-MANAGER
……


更复杂的例子:
SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier,
  DECODE(p.status, ‘INSTOCK’, ‘In Stock’,
    ‘DISC’, ‘Discontinued’,
    ‘BACKORD’, ‘Backordered’,
    ‘ENROUTE’, ‘Arriving Shortly’,
    ‘UNAVAIL’, ‘No Shipment Scheduled’,
    ‘Unknown’) part_status
  FROM part p, supplier s WHERE p.supplier_id = s.supplier_id;
这个例子将part status和5个值依次进行比较,如果能找到一个匹配的,将返回一个相应的串,如果没有找到匹配的,将返回Unknown.


有条件更新


UPDATE part SET status = ‘INSTOCK’ WHERE inventory_qty > 0;
UPDATE part SET status = ‘ENROUTE’ WHERE inventory_qty = 0 AND resupply_date < SYSDATE + 5;
UPDATE part SET status = ‘BACKORD’ WHERE inventory_qty = 0 AND resupply_date > SYSDATE + 5;
UPDATE part SET status = ‘UNAVAIL’ WHERE inventory_qty = 0 and resupply_date IS NULL;



UPDATE part SET status =
DECODE(inventory_qty, 0,
  DECODE(resupply_date, NULL, ‘UNAVAIL’,
    DECODE(LEAST(resupply_date, SYSDATE + 5), resupply_date,’ENROUTE’, ‘BACKORD’)),’INSTOCK’);


UPDATE part SET status =
  CASE WHEN inventory_qty > 0 THEN ‘INSTOCK’
       WHEN resupply_date IS NULL THEN ‘UNAVAIL’
       WHEN resupply_date < SYSDATE + 5 THEN ‘ENROUTE’
       WHEN resupply_date > SYSDATE + 5 THEN ‘BACKORD’
  ELSE ‘UNKNOWN’ END;

第五章 子查询
不相关子查询


不相关子查询允许从SQL语句返回的行能与一个值的集合进行比较,分为下列三种情况:


单行单列子查询
多行单列子查询
多列子查询


单行,单列子查询 
如下所示:
SELECT lname FROM employee WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);
DELETE FROM load_log WHERE load_dt < (SELECT MAX(TRUNC(load_dt)) FROM load_log);
SELECT sales_emp_id, COUNT(*) FROM cust_order GROUP BY sales_emp_id HAVING COUNT(*) =
  (SELECT MAX(COUNT(*)) FROM cust_order GROUP BY sales_emp_id);
SALES_EMP_ID COUNT(*)
———— ———-
30   121


多行子查询


当一个子查询返回多个行时,我们不能向上面那样使用比较运算符,这时使用特定的ALL与ANY来进行比较运算,以此决定一个值是相等(或者小于,大于等等)集合中的any或者all成员 。比如:


SQL> SELECT fname, lname FROM employee WHERE dept_id = 3 AND salary >= ALL
  2  (SELECT salary FROM employee WHERE dept_id = 3);
FNAME LNAME
——————– ——————–
Mark Russell
或者像下面这样写:
SQL> SELECT fname, lname FROM employee WHERE dept_id = 3 AND NOT salary < ANY
  2  (SELECT salary FROM employee WHERE dept_id = 3);


这种写法没有前面那样易读。


同any和all一样,我们还可以在返回多行子查询中使用in。


UPDATE cust_order SET expected_ship_dt = TRUNC(SYSDATE) + 1
   WHERE ship_dt IS NULL AND order_nbr IN (
       SELECT l.order_nbr FROM line_item l, part p WHERE l.part_nbr = p.part_nbr
       AND p.inventory_qty = 0);


DELETE FROM customer WHERE cust_nbr NOT IN (SELECT cust_nbr FROM cust_order WHERE order_dt >= TRUNC(SYSDATE)-(365 * 5));


多列子查询


UPDATE monthly_orders SET
tot_orders =
(SELECT COUNT(*) FROM cust_order WHERE order_dt >= TO_DATE(‘01-NOV-2001′,’DD-MON-YYYY’)
AND order_dt < TO_DATE(‘01-DEC-2001′,’DD-MON-YYYY’) AND cancelled_dt IS NULL), max_order_amt = (SELECT MAX(sale_price)
FROM cust_order
WHERE order_dt >= TO_DATE(‘01-NOV-2001′,’DD-MON-YYYY’)
AND order_dt < TO_DATE(‘01-DEC-2001′,’DD-MON-YYYY’) AND cancelled_dt IS NULL), min_order_amt = (SELECT MIN(sale_price)
FROM cust_order
WHERE order_dt >= TO_DATE(‘01-NOV-2001′,’DD-MON-YYYY’)
AND order_dt < TO_DATE(‘01-DEC-2001′,’DD-MON-YYYY’) AND cancelled_dt IS NULL), tot_amt = (SELECT SUM(sale_price)
FROM cust_order
WHERE order_dt >= TO_DATE(‘01-NOV-2001′,’DD-MON-YYYY’)
AND order_dt < TO_DATE(‘01-DEC-2001′,’DD-MON-YYYY’) AND cancelled_dt IS NULL) WHERE month = 11 and year = 2001;


这个UPDATE语句修改了monthly_orders表中的四列,每一列的值来源于聚合cust_order表而得到,下面的这个查询显示
了在一个语句中求4列的值:
UPDATE monthly_orders
    SET (tot_orders, max_order_amt, min_order_amt, tot_amt) =
    (SELECT COUNT(*), MAX(sale_price), MIN(sale_price), SUM(sale_price)
    FROM cust_order
    WHERE order_dt >= TO_DATE(‘01-NOV-2001′,’DD-MON-YYYY’)
        AND order_dt < TO_DATE(‘01-DEC-2001′,’DD-MON-YYYY’)
        AND cancelled_dt IS NULL
   )
    WHERE month = 11 and year = 2001;
尽管上面的语句显示的是在UPDATE中的使用,也可以在SELECT,DELETE中,看下面的语句:


DELETE FROM line_item
    WHERE (order_nbr, part_nbr) IN
    (SELECT c.order_nbr, p.part_nbr
        FROM cust_order c, line_item li, part p WHERE c.ship_dt IS NULL AND c.cancelled_dt IS NULL
            AND c.order_nbr = li.order_nbr
            AND li.part_nbr = p.part_nbr
            AND p.status = ‘DISCONTINUED’);


相关子查询


SELECT p.part_nbr, p.name
FROM supplier s, part p
WHERE s.name = ‘Acme Industries’
   AND s.supplier_id = p.supplier_id
   AND 10 <=
 (SELECT COUNT(*)
         FROM cust_order co, line_item li
  WHERE li.part_nbr = p.part_nbr
     AND li.order_nbr = co.order_nbr
     AND co.order_dt >= TO_DATE(‘01-DEC-2001′,’DD-MON-YYYY’));


对p.part_nbr的引用使得子查询是相关的,在子查询执行之前,这个值必须提供,如果有在parts表中有10000个parts,但是只有
100个被Acme Industries所提供,子查询将对通过连接part表和提供的其他相关表所产生的中间查询的结果的每100行执行一次。


相关子查询通常用来检查不考虑集合个数情况下的联系是否存在,比如下面这个例子:


SELECT p.part_nbr, p.name, p.unit_cost
FROM part p
WHERE EXISTS
(SELECT 1 FROM line_item li, cust_order co
WHERE li.part_nbr = p.part_nbr
      AND li.order_nbr = co.order_nbr
      AND co.ship_dt >= TO_DATE(‘01-JAN-2002′,’DD-MON-YYYY’));


因为SET 从句赋值到表中列的值,唯一的操作符是=,下面的子查询只返回一行,所以子查询结果可以安全向目标列赋值。
UPDATE customer c SET (c.tot_orders, c.last_order_dt) =
(SELECT COUNT(*), MAX(co.order_dt) FROM cust_order co WHERE co.cust_nbr = c.cust_nbr AND co.cancelled_dt IS NULL);


内嵌视图
请使用下面的定义来看待SQL中SELECT语句的FROM从句:FROM从句包含一个数据集的列表。在这个意义上,很容易看到,FROM从句可以包含表格(永久性数据集),视图(虚拟数据集),select语句(临时数据集),FROM从句中包含SELECT语句的称为内嵌视图,下面是一个例子:
SELECT d.dept_id, d.name, emp_cnt.tot FROM department d,
(SELECT dept_id, COUNT(*) tot FROM employee GROUP BY dept_id) emp_cnt
WHERE d.dept_id = emp_cnt.dept_id;
DEPT_ID NAME TOT
———- ——————– ———-
1 Human Resources 1
2 Accounting 1
3 Sales 24


嵌入视图基础
由于内嵌视图的结果集可能被其他查询所引用,我们必须给我们的内嵌视图一个名称并对名称不明确的列提供明确的名称
同其他类型子查询类似,内嵌视图能连接多表,包含GROUP BY,HAVING,CONNECT BY从句。不像其他类型的子查询,一个内嵌视图也可以包含GROUP BY 从句。


查询的执行


SELECT d.dept_id dept_id, d.name dept_name,dept_orders.tot_orders tot_orders FROM
department d,
(SELECT e.dept_id dept_id, SUM(emp_orders.tot_orders) tot_orders FROM employee e,
   (SELECT sales_emp_id, COUNT(*) tot_orders FROM cust_order
      WHERE order_dt >= TRUNC(SYSDATE) – 365 AND cancelled_dt IS NULL GROUP BY sales_emp_id
   ) emp_orders
   WHERE e.emp_id = emp_orders.sales_emp_id GROUP BY e.dept_id
) dept_orders
WHERE d.dept_id = dept_orders.dept_id;


DEPT_ID DEPT_NAME TOT_ORDERS
———- ——————– ———-
3 Sales 2760


虚拟数据集
比如下面这个:
SELECT ‘SMALL’ name, 0 lower_bound, 999 upper_bound from dual
UNION ALL
SELECT ‘MEDIUM’ name, 1000 lower_bound, 24999 upper_bound from dual
UNION ALL
SELECT ‘LARGE’ name, 25000 lower_bound, 9999999 upper_bound from dual;


NAME LOWER_BOUND UPPER_BOUND
—— ———– ———–
SMALL 0 999
MEDIUM 1000 24999
LARGE 25000 9999999


下面就可以在内嵌视图中使用这个查询并进行聚合:


SELECT sizes.name order_size, SUM(co.sale_price) tot_dollars
FROM cust_order co,
(SELECT ‘SMALL’ name, 0 lower_bound, 1000 upper_bound from dual
UNION ALL
SELECT ‘MEDIUM’ name, 1000 lower_bound, 25000 upper_bound from dual
UNION ALL
SELECT ‘LARGE’ name, 25000 lower_bound, 9999999 upper_bound from dual
) sizes
WHERE co.cancelled_dt IS NULL
AND co.order_dt >= TO_DATE(‘01-JAN-2001′,’DD-MON-YYYY’)
AND co.order_dt < TO_DATE(‘01-JAN-2002′,’DD-MON-YYYY’)
AND co.sale_price BETWEEN sizes.lower_bound AND sizes.upper_bound
GROUP BY sizes.name;
ORDER_ TOT_DOLLARS
—— ———–
LARGE 7136214
MEDIUM 32395018
SMALL 63676


克服SQL限制
聚合查询
进行聚合的查询有下面的限制:在SELECT从句中所有非聚合列必须出现在GROUP BY从句中。考虑下面的查询,它通过
customer和salesperson对数据进行聚合,然后从customer,region,employee,department添加支持数据。


SELECT c.name customer, r.name region, e.fname || ‘ ‘ || e.lname salesperson, d.name department,
SUM(co.sale_price) total_sales
FROM cust_order co, customer c, region r, employee e, department d
WHERE co.cust_nbr = c.cust_nbr
       AND c.region_id = r.region_id
       AND co.sales_emp_id = e.emp_id
       AND e.dept_id = d.dept_id
       AND co.cancelled_dt IS NULL
       AND co.order_dt >= TO_DATE(‘01-JAN-2001′,’DD-MON-YYYY’)
 GROUP BY c.name, r.name, e.fname || ‘ ‘ || e.lname, d.name;


由于每个customer仅仅在一个region,并且每个employee仅仅在一个 department,我们仅仅需要在customer和employee上进行排序,上面的例子在对region和department排序上浪费了时间。


SELECT c.name customer, r.name region, e.fname || ‘ ‘ || e.lname salesperson, d.name department,
cust_emp_orders.total total_sales
FROM customer c, region r, employee e, department d,
(SELECT cust_nbr, sales_emp_id, SUM(sale_price) total
  FROM cust_order
  WHERE cancelled_dt IS NULL
  AND order_dt >= TO_DATE(‘01-JAN-2001′,’DD-MON-YYYY’)
  GROUP BY cust_nbr, sales_emp_id
) cust_emp_orders
  WHERE cust_emp_orders.cust_nbr = c.cust_nbr
  AND c.region_id = r.region_id
  AND cust_emp_orders.sales_emp_id = e.emp_id
AND e.dept_id = d.dept_id;


由于cust_order表包含customer number与salesperson ID,我们能在不需要包含另外4个表的基础上进行这两个列上的聚合操作。
我们不仅仅是在较少的列上进行聚合(customer number与employee ID),而且避免了在潜在较长的字符串类型列(customer name,
region name,department name)上的操作。这个包含的查询使用内嵌视图的cust_nbr和sales_emp_id列连接customer表和department表,


DML语句中的内嵌视图


内嵌视图同样能使用于INSERT,UPDATE,DELETE语句。在大多数情况下,在DML中使用内嵌视图提高了可读性。


UPDATE cust_order co SET co.expected_ship_dt = co.expected_ship_dt + 7
WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
AND EXISTS (
      SELECT 1 FROM line_item li, part p
      WHERE li.order_nbr = co.order_nbr
      AND li.part_nbr = p.part_nbr
      AND p.inventory_qty = 0
    );


UPDATE (SELECT co.expected_ship_dt exp_ship_dt
 FROM cust_order co
 WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
 AND EXISTS (
       SELECT 1 FROM line_item li, part p
       WHERE li.order_nbr = co.order_nbr
       AND li.part_nbr = p.part_nbr
       AND p.inventory_qty = 0
     )
 ) suspended_orders
SET suspended_orders.exp_ship_dt = suspended_orders.exp_ship_dt + 7;


在第一个语句中,UPDATE语句的where从句决定了要更新的行集,在第二个语句中,SELECT语句返回的结果集决定了目标行。
为了让内嵌视图添加额外数据到语句中,它必须能作一些简单uodate语句不能作的事情:连接多个表,下面的语句试图使用三表连接替代子查询。
UPDATE (SELECT co.expected_ship_dt exp_ship_dt
FROM cust_order co, line_item li, part p
WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
AND co.order_nbr = li.order_nbr AND li.part_nbr = p.part_nbr
AND p.inventory_qty = 0) suspended_orders
SET suspended_orders.exp_ship_dt = suspended_orders.exp_ship_dt + 7;
但是这样有错误:
ORA-01779: cannot modify a column which maps to a non key-preserved table
要在DML语句中利用连接操作的好处,我们必须遵守下面规则:


只有连接视图中的一个连接表能被DML语句所修改。
一个表要想是可修改的,那么在内嵌视图的结果集中目标表的关键字必须被保留。


前面的更新语句试图修改一个表(cust_order),其关键字order_nbr在结果集中没有被保留,因为一个order有多个line items。换句话说,
通过三表连接查询产生的结果的行不能仅仅使用order_nbr字段标识,所以他是不可更新的。事实上,使用同样的连接更新或者删除line_item是可能的,因为line_item表的关键字匹配内嵌视图所返回的关键字(order_nbr和part_nbr).


DELETE FROM (SELECT li.order_nbr order_nbr, li.part_nbr part_nbr
  FROM cust_order co, line_item li, part p
  WHERE co.cancelled_dt IS NULL AND co.ship_dt IS NULL
    AND co.order_nbr = li.order_nbr AND li.part_nbr = p.part_nbr
    AND p.inventory_qty = 0) suspended_orders;


上面的内嵌视图中select从句所引用的列实际上是不相关的。由于line_item表是from从句所列出表中唯一关键字保留的,这也是delete语句能进行的表。


使用WITH CHECK OPTION选项的严格访问


另外一个内嵌视图能向DML语句添加数据的地方是通过限定可以修改的行和列。比如,大多数公司仅允许HR部门的成员查看或者修改salary信息。通过限定对DML可见的列,我们能有效隐藏salary列:


UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id FROM employee) emp
SET emp.manager_emp_id = 11 WHERE emp.dept_id = 4;


前面的语句执行顺利,下面的语句将产生错误:


UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id FROM employee) emp
SET emp.manager_emp_id = 11, emp.salary = 1000000000 WHERE emp.dept_id = 4;
ORA-00904: invalid column name


当然,发布update语句的人要有对这个表的访问权,这里的意图是保护数据不被未授权的修改。


上面的语句的机理对限定特定的列十分有用,它不限制对目标表中的特定行的访问。为了限制能使用DML语句修改的行,我们能添加where从句到内嵌视图中并指定with check option。
比如,下面的例子对用户修改HR department表中任何employee的数据进行了限定。


UPDATE (SELECT emp_id, fname, lname, dept_id, manager_emp_id
        FROM employee
        WHERE dept_id != ( SELECT dept_id FROM department WHERE name = ‘Human Resources’)
           WITH CHECK OPTION
       ) emp
         SET emp.manager_emp_id = 11
         WHERE emp.dept_id = 4;


with check option选项约束了DML语句遵守内嵌视图中where从句约束。
企图更新或者删除HR department中employee的信息不会成功,但是也不会产生错误(更新/删除0行)。但是企图添加一个新的employee到HR将产生下面的错误:
ORA-01402: view WITH CHECK OPTION where-clause violation
因此,下面的语句不会成功,产生上面的错误:


INSERT INTO ( SELECT emp_id, fname, lname, dept_id, manager_emp_id FROM employee
              WHERE dept_id != ( SELECT dept_id FROM department  WHERE name = ‘Human Resources’ )
              WITH CHECK OPTION
            ) emp
 SELECT 99, ‘Charles’, ‘Brown’, d.dept_id, NULL
 FROM department d
 WHERE d.name = ‘Human Resources’;
后面是一个例子。。。

第四章 分组函数
aggregate_function([DISTINCT | ALL] expression)
aggregate_function函数就是SUM,COUNT,AVG,MAX,MIN等
expression指定了一个列或者其他的用来进行聚合的列。
空值和聚合函数
SQL> SELECT COUNT(*), COUNT(SALE_PRICE) FROM CUST_ORDER;
COUNT(*) COUNT(SALE_PRICE)
——– —————–
20           14


COUNT(*)不忽略空值,它统计行数,不是行值。除了COUNT(*)之外,只有另外一个聚合函数不忽略空值,
就是GROUPING,其他所有的聚合函数都会忽略空值


DISTINCT和ALL的使用
DICTINCT允许丢弃相同的表达值,而All不会丢弃。但是ALL考虑空值。
SQL> SELECT COUNT(CUST_NBR), COUNT(DISTINCT CUST_NBR), COUNT(ALL CUST_NBR) FROM CUST_ORDER;
COUNT(CUST_NBR) COUNT(DISTINCTCUST_NBR) COUNT(ALLCUST_NBR)
————— ———————– ——————
20                              6                          20


SQL> SELECT COUNT(ALL SALE_PRICE) FROM CUST_ORDER;
COUNT(ALLSALE_PRICE)
——————–
14
由于ALL是缺省的,我们能对每一个聚合函数显式使用ALL。实际上,带有多余一个参数的的聚合函数不允许使用DISTINCT
这些函数包括CORR,COVAR_POP等线性回归函数。
除此以外,一些只带一个参数的的函数不允许使用DISTINCT,包括STTDEV_SAMP,VAR_POP,VAR_SAMP,GROUPING


Group BY 从句


同聚合函数一起的GROUP BY从句,将一个结果集分成多个组,然后对每个组产生一个单行的统计信息。比如:


SQL> SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR;
CUST_NBR COUNT(ORDER_NBR)
———- —————-
201   2
231   6
244   2
255   6
264   2
288   2
6 rows selected.


SELECT 语句中的非聚合表达式也出现在GROUP BY从句中,如果我们在SELECT语句中夹杂聚合与非聚合表达式,
SQL期望我们进行GROUP BY操作,我们必须在GROUP BY语句中列出所有的非聚合表达式。


我们不能在GROUP BY从句中使用分组函数(聚合函数)。
如果我们在SELECT 列表中有常数,我们不需要将它包含在GROUP BY从句中。实际上,将常数包含在GROUP BY从句中也不
影响结果。


某些情况下,我们需要在SELECT列表中出现一个表达式,但是不需要用它来分组。比如,我们需要在每个customer的总结信息
前显示行号。试图使用下面的信息将会得到错误:


SQL> SELECT ROWNUM, CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR;
SELECT ROWNUM, CUST_NBR, COUNT(ORDER_NBR)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression


如果我们在GROUP BY从句中包括ROWNUM,我们将得到不期望的结果:


SQL> SELECT ROWNUM, CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY ROWNUM, CUST_NBR;


ROWNUM CUST_NBR COUNT(ORDER_NBR)
———- ———- —————-
1   231  1
2   201  1
3   255  1
4   264  1
5   244  1
6   288  1
7   231 1
8   255  1
9   255 1
10   231 1
…….


应该这样写:
SELECT ROWNUM, V.* FROM (SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR) V;


在SELECT列表中包含GROUP BY从句中所有的表达式不是必须的。
SQL> SELECT COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR;
COUNT(ORDER_NBR)
—————-
2
6
2
6
2
2
6 rows selected.


扩展前面的查询,


SQL> SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR, ORDER_DT;
CUST_NBR COUNT(ORDER_NBR)
———- —————-
201   2
231   2
231   4
244   2
255   2
255   2
255   2
264   2
288   2
9 rows selected.


下面的查询看起来更加有意义:


SQL> SELECT CUST_NBR, ORDER_DT, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR, ORDER_DT;
CUST_NBR    ORDER_DT  COUNT(ORDER_NBR)
———- ——— —————-
201  19-JUL-01  2
231  18-JUL-01  2
231  22-JUL-01  4
244  18-JUL-01  2
255  12-JUL-01  2
255  20-JUL-01  2
255  21-JUL-01  2
264  16-JUL-01  2
288  22-JUL-01  2
9 rows selected.


GROUP BY 从句和NULL值
当我们对某些包括空值的行使用GROUP BY时,所有带空值的项被放到一个单一的组,在输出中作为一个总结出现:


SQL> SELECT SALE_PRICE, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY SALE_PRICE;
SALE_PRICE COUNT(ORDER_NBR)
———- —————-
25  4
34  2
56  4
99  4
    6
注意输出的最后一行。


HAVING从句
HAVING从句和GROUP BY 从句紧密相关,它用于过滤用GROUP BY产生的组。如果一个查询在GROUP BY 后
有一个HAVING从句,结果集将只包含满足HAVING条件的组。看下面的例子:


SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR HAVING CUST_NBR < 260;
CUST_NBR COUNT(ORDER_NBR)
———- —————-
201   2
231   6
244   2
255   6


上面的这个例子是HAVING从句的不恰当用法,因为那个从句仅仅引用未总结的数据,更有效的办法是使用WHERE CUST_NAME<260。
下面的这个例子显示了HAVING从句的更好用法:


SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR HAVING COUNT(ORDER_NBR) > 2;


HAVING的语法与WHERE相似。实际上,对HAVING有一个限制,在HAVING使用的条件中,只能引用SELECT列表或者GROUP BY中的表达式,比如,下面的语法不对:


SQL> SELECT CUST_NBR, COUNT(ORDER_NBR) FROM CUST_ORDER GROUP BY CUST_NBR HAVING ORDER_DT < SYSDATE;
HAVING ORDER_DT < SYSDATE
*
ERROR at line 4:
ORA-00979: not a GROUP BY expression

第三章 连接
外部链接
有时在进行表之间的链接的时候,你需要返回一个表的所有行,
即使这个表在另外一个表中没有对应的行。考虑下面的表:
SQL> SELECT * FROM SUPPLIER;


SUPPLIER_ID NAME
———– ——————————
        101 Pacific Disks, Inc.
        102 Silicon Valley MicroChips
        103 Blue River Electronics


SQL> SELECT * FROM PART;


PART_NBR      NAME       SUPPLIER_ID STATUS           INVENTORY_QTY  UNIT_COST RESUPPLY_DATE
——— ——————— ———– ——————– ————- ———-
HD211     20 GB Hard Disk             101 ACTIVE                 5         2000         12-DEC-00
P3000     3000 MHz Processor      102 ACTIVE                12          600           03-NOV-00


如果你要列出所有的供应者和他们所供应的所有产品,很自然会使用下面的查询:
SQL> SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
  2  FROM SUPPLIER S, PART P
  3  WHERE S.SUPPLIER_ID = P.SUPPLIER_ID;


SUPPLIER_ID SUPPLIER_NAME                  PART_NBR             PART_NAME
———– —————————— ——————– ——————-
        101 Pacific Disks, Inc.            HD211                20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000                3000 MHz Processor


这就是内部查询,它没有显示第三个第三个供应者,实际上,我们要看出所有的供应者,即使他们没有提供任何部分。
Oracle提供了一种特殊的链接类型来包括一个表中的所有行,即使它并不和以外一个表中的行匹配,这种链接就是外部链接,
可以如下所示:
SQL> SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
  2  FROM SUPPLIER S, PART P
  3  WHERE S.SUPPLIER_ID = P.SUPPLIER_ID (+);


SUPPLIER_ID SUPPLIER_NAME                  PART_NBR             PART_NAME
———– —————————— ——————– ——————–
        101 Pacific Disks, Inc.            HD211                20 GB Hard Disk
        102 Silicon Valley MicroChips      P3000                3000 MHz Processor
        103 Blue River Electronics


也可以这样写:
SQL> SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
  2  FROM SUPPLIER S, PART P
  3  WHERE P.SUPPLIER_ID (+) = S.SUPPLIER_ID;


外部链接的限制


1)外部链接只能出现在join条件的一边,如果你试图在两边使用,你将得到ORA-1468错误。
比如:
SQL> SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME
  2  FROM SUPPLIER S, PART P
  3  WHERE S.SUPPLIER_ID (+) = P.SUPPLIER_ID (+);
WHERE S.SUPPLIER_ID (+) = P.SUPPLIER_ID (+)
                        *
ERROR 位于第 3 行:
ORA-01468: 一个谓词只能引用一个外部连接表


2)如果链接操作涉及多余两个表,那么一个表不能同多余一个表进行外部链接,看下面的例子:
SQL> DESC EMPLOYEE
Name     Null?  Type
——————————- ——– —-
EMP_ID     NOT NULL NUMBER(4)
LNAME                     VARCHAR2(15)
FNAME       VARCHAR2(15)
DEPT_ID                                  NUMBER(2)
MANAGER_EMP_ID      NUMBER(4)
SALARY       NUMBER(7,2)
HIRE_DATE      DATE
JOB_ID       NUMBER(3)


SQL> DESC JOB
Name     Null?   Type
——————————- ——– —-
JOB_ID     NOT NULL NUMBER(3)
FUNCTION      VARCHAR2(30)


SQL> DESC DEPARTMENT
Name     Null?   Type
——————————- ——– —-
DEPT_ID    NOT NULL NUMBER(2)
NAME       VARCHAR2(14)
LOCATION_ID      NUMBER(3)


如果你要列出所有员工的job和department名称,并且要包括所有的department和job(即使他们没有对应的员工),
你可能会试图使用下面的查询


SQL> SELECT E.LNAME, J.FUNCTION, D.NAME FROM EMPLOYEE E, JOB J, DEPARTMENT D
  2  WHERE E.JOB_ID (+) = J.JOB_ID AND E.DEPT_ID (+) = D.DEPT_ID;
WHERE E.JOB_ID (+) = J.JOB_ID AND E.DEPT_ID (+) = D.DEPT_ID
                   *
ERROR 位于第 2 行:
ORA-01417: 表可以外部连接到至多一个其它的表


你能创建一个两个表之间外部链接的视图来完成这个工作,然后将这个视图和第三个表之间进行外部链接来达到这个目的。


CREATE VIEW V_EMP_JOB AS SELECT E.DEPT_ID, E.LNAME, J.FUNCTION FROM EMPLOYEE E, JOB J
WHERE E.JOB_ID (+) = J.JOB_ID;
SELECT V.LNAME, V.FUNCTION, D.NAME FROM V_EMP_JOB V, DEPARTMENT D WHERE V.DEPT_ID (+) = D.DEPT_ID;


你也可以使用内部视图来达到同样的结果。


SELECT V.LNAME, V.FUNCTION, D.NAME FROM
 (
 SELECT E.DEPT_ID, E.LNAME, J.FUNCTION FROM EMPLOYEE E, JOB J WHERE E.JOB_ID (+) = J.JOB_ID
 ) V, DEPARTMENT D WHERE V.DEPT_ID (+) = D.DEPT_ID;
3)包含(+)操作符的外部链接不能使用in操作符。
4)包含or运算符的外部链接不能同另外一个使用or的条件结合。
5)包含(+)操作符的条件不能包含子查询。


完全外部链接
一个外部链接通过包含一个在另外一个表(B)中没有对应行的表(A)扩展了内部链接的结果,
但是要注意的是这个链接没有包含表B中那些在表A中没有对应行的行,也就是,外部链接是单向的
,可能你需要双向的链接。
SQL> DESC LOCATION
Name     Null?  Type
——————————- ——– —-
LOCATION_ID    NOT NULL NUMBER(3)
REGIONAL_GROUP      VARCHAR2(20)


SQL> DESC DEPARTMENT
Name     Null?  Type
——————————- ——– —-
DEPT_ID    NOT NULL NUMBER(2)
NAME       VARCHAR2(14)
LOCATION_ID      NUMBER(3)


假定在LOCATION表中有在DEPARTMENT中没有对应行的行,同时在DEPARTMENT表中包含在LOCATION中没有对应行的行,
这时你进行内部链接只能得到在两个表中都有对应行的行,使用前面的外部链接只能得到LOCATION中没有对应行的DEPARTMENT或者
DEPARTMENT行中没有对应行的LOCATION。
SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP FROM DEPARTMENT D, LOCATION L WHERE D.LOCATION_ID (+) = L.LOCATION_ID;
SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP FROM DEPARTMENT D, LOCATION L WHERE D.LOCATION_ID = L.LOCATION_ID (+) ;
如果你要包含没有任何LOCATION对应的DEPARTMENT和没有任何DEPARTMENT对应的LOCATION,你可能会想到两边的外部链接,如下所示:
SQL> SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP
  2  FROM DEPARTMENT D, LOCATION L
  3  WHERE D.LOCATION_ID (+) = L.LOCATION_ID (+);
WHERE D.LOCATION_ID (+) = L.LOCATION_ID (+)
                        *
ERROR 位于第 3 行:
ORA-01468: 一个谓词只能引用一个外部连接表


但是这样是不允许的。
这时你可以使用UNION来达到这个目的。
SQL> SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP FROM DEPARTMENT D, LOCATION L WHERE D.LOCATION_ID (+) = L.LOCATION_ID
  2  UNION
  3  SELECT D.DEPT_ID, D.NAME, L.REGIONAL_GROUP FROM DEPARTMENT D, LOCATION L WHERE D.LOCATION_ID = L.LOCATION_ID (+) ;


   DEPT_ID NAME                 REGIONAL_GROUP
———- ——————– ——————–
        10 ACCOUNTING           NEW YORK
        12 RESEARCH             NEW YORK
        13 SALES                NEW YORK
        14 OPERATIONS           NEW YORK
        20 RESEARCH             DALLAS
        23 SALES                DALLAS
        24 OPERATIONS           DALLAS
        30 SALES                CHICAGO
        34 OPERATIONS           CHICAGO
        40 OPERATIONS           BOSTON
        43 SALES                BOSTON


   DEPT_ID NAME                 REGIONAL_GROUP
———- ——————– ——————–
        50 MARKETING
        60 CONSULTING
                                SAN FRANCISCO


已选择14行。


链接与子查询
考虑下面的查询:
SELECT supplier_id, name FROM supplier s WHERE EXISTS
(SELECT * FROM part p WHERE p.inventory_qty < 10 AND p.supplier_id = s.supplier_id);
这个SELECT语句中的子查询是一个相关查询,它意味着要为supplier中的每一行执行一次,假定你在PART表的
INVENTORY_QTY和SUPPLIER_ID列上没有索引,那么查询将会导致多次全表扫描,可以如下重写查询:
SELECT s.supplier_id, s.name FROM supplier s, part p WHERE p.supplier_id = s.supplier_id AND p.inventory_qty < 10;


链接视图上的DML语句
链接视图是基于链接的视图,当你在链接视图上使用INSERT,UPDATE,DELETE命令的时候要特别注意。当你向链接视图插入一行时,
将会发生什么?哪一个表将会被插入,当你从视图中删除一行时哪个表中的数据将被删除。
要成为可以修改的视图,链接视图不能包含下列中的任何东西:
1)分等级的查询从句,比如START WITH或者CONNECT BY.
2)GROUP BY或者HAVING 从句。
3)集合操作符,比如UNION,UNION ALL,INTERSECT,MINUS.
4)聚合函数,比如AVG,COUNT,MAX,min,SUM等。
5)DISTINCT操作符。
6)ROWNUM伪列。


一个链接视图上的DML语句只能修改视图中的一个表,因此,一个链接视图必须有一个用于修改的关键字保留的表。

关键字保留的表
为了一个连接视图可以修改,一个关键词保留的表是很重要的。在一个链接中,如果一个表的关键字通过连接操作而得以
保留(也就是说表的关键字也是结果链接的关键字)。基表中的主键或者唯一键必须是链接结果结果中唯一的。看下面的表:


SQL> DESC EMPLOYEE
Name     Null?   Type
——————————- ——– —-
EMP_ID     NOT NULL NUMBER(4)
LNAME       VARCHAR2(15)
FNAME       VARCHAR2(15)
DEPT_ID      NUMBER(2)
MANAGER_EMP_ID      NUMBER(4)
SALARY       NUMBER(7,2)
HIRE_DATE      DATE
JOB_ID       NUMBER(3)


SQL> DESC RETAILER
Name     Null? Type
——————————- ——– —-
RTLR_NBR    NOT NULL NUMBER(6)
NAME       VARCHAR2(45)
ADDRESS      VARCHAR2(40)
CITY           VARCHAR2(30)
STATE       VARCHAR2(2)
ZIP_CODE      VARCHAR2(9)
AREA_CODE      NUMBER(3)
PHONE_NUMBER      NUMBER(7)
SALESPERSON_ID      NUMBER(4)
CREDIT_LIMIT      NUMBER(9,2)
COMMENTS      LONG


SQL> CREATE VIEW V_RTLR_EMP AS SELECT C.RTLR_NBR, C.NAME, C.CITY, E.EMP_ID, E.LNAME SALES_REP FROM RETAILER C, EMPLOYEE E
 WHERE C.SALESPERSON_ID = E.EMP_ID;
View created.


SQL> SELECT * FROM V_RTLR_EMP;
  RTLR_NBR NAME                                          CITY                               EMP_ID SALES_REP
———- ——————————————— —————————— ———- ——————–
       100 JOCKSPORTS                                    BELMONT                              7844 TURNER
       101 TKB SPORT SHOP                                REDWOOD CITY                         7521 WARD
       102 VOLLYRITE                                     BURLINGAME                           7654 MARTIN
       103 JUST TENNIS                                   BURLINGAME                           7521 WARD
……
32 rows selected.


V_RTLR_EMP是RETAILER和EMPLOYEE表在RETAILER.SALESPERSON_ID和EMPLOYEE.EMP_ID上的连接,如果
你观察两个表和连接的查询,你将注意到RTLR_NBR是RETAILER的主键,也是连接的主键,
这是因为对于连接视图中的每一行,在RETAILER表中只有一行与之对应,并且视图中只有每一行只有一个RTLR_NBR。
因此RETAILER表是关键字保留的表,而EMPLOYEE不是关键字保留的表,因为EMP_ID在视图中不是唯一的,EMP_ID不能是连接的主键。

对于关键字保留的表,你必须记住:
1)一个表在一个视图中关键字保留,不一定在另外一个视图中也是关键字保留的。
2)对于关键字保留的表,其中的关键字列在连接视图中不一定是SELECTED。比如,在V_RTLR_EMP视图中,
RETAILER表是关键字保留的表,即使我们没有在连接视图的SELECT列表中包含它。
3)另一方面,如果我们在视图定义中选择了一个表的关键字列,这也不会将这个表变为关键字保留的,正如上面的EMPLOYEE表。


连接视图上的插入操作:


SQL> INSERT INTO V_RTLR_EMP (RTLR_NBR, NAME, SALESPERSON_ID) VALUES (345, ‘X-MART STORES’, 7820);
1 row created.


下面这个INSERT从EMPLOYEE表中为视图一个列提供了一个值。
SQL> INSERT INTO V_RTLR_EMP (RTLR_NBR, NAME, SALES_REP)  VALUES (456, ‘LEE PARK RECREATION CENTER’, ‘JAMES’);
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
这个插入语句试图将值插入两个表,这是不允许的。如果这个视图在创建时使用了WITH CHECK OPTION从句,那么视图上的INSERT
语句是不允许的,即便你是仅仅向关键字保留的表进行插入。
SQL> CREATE VIEW V_RTLR_EMP_WCO AS SELECT C.RTLR_NBR, C.NAME, C.CITY, C.SALESPERSON_ID, E.LNAME SALES_REP FROM RETAILER C,  EMPLOYEE E WHERE C.SALESPERSON_ID = E.EMP_ID WITH CHECK OPTION;
View created.
INSERT INTO V_RTLR_EMP_WCO (RTLR_NBR, NAME, SALESPERSON_ID) VALUES (345, ‘X-MART STORES’, 7820);
INSERT INTO V_RTLR_EMP_WCO (RTLR_NBR, NAME, SALESPERSON_ID)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


连接视图上的删除操作
只能在有且仅有一个关键字保留的表上进行删除操作。上面的视图V_RTLR_EMP仅有一个关键字保留表,因此,可以进行删除操作。
SQL> DELETE FROM V_RTLR_EMP WHERE RTLR_NBR = 214;
1 row deleted.


连接视图上的更新操作
更新操作涉及的列只能在关键字保留的表上。比如:
SQL> UPDATE V_RTLR_EMP SET NAME = ‘PRO SPORTS’ WHERE RTLR_NBR = 214;
1 row updated.
如果这个视图使用了with check option选项,你不能修改连接列上的任何值,不管是不是在关键字保留的表中。


Oracle提供了USER_UPDATABLE_COLUMNS视图,它显示了用户方案中的表和视图中所有可更新的列

2004年03月17日

本系列Oracle SQL均翻译自Alan Beaulieu, Sanjay Mishra所著的Mastering Oracle SQL(O’Reilly)


首先运行下面的脚本生成必要的表和视图:
– Script to create tables / views
– You should have a tablespace USERS,
– and the user running this script should have sufficient quota on USERS tablespace.
– For simplicity data as well as indexes are kept in one tablespace.


CREATE TABLE CUST_ORDER(
    ORDER_NBR           NUMBER(7)           NOT NULL,
    CUST_NBR            NUMBER(5)           NOT NULL,
    SALES_EMP_ID        NUMBER(5)           NOT NULL,
    SALE_PRICE          NUMBER(9,2),
    ORDER_DT            DATE                NOT NULL,
    EXPECTED_SHIP_DT    DATE                NOT NULL,
    CANCELLED_DT        DATE,
    SHIP_DT             DATE,
    STATUS              VARCHAR2(20),
    CONSTRAINT cust_order_pk PRIMARY KEY (ORDER_NBR))  ;


CREATE TABLE CUSTOMER(
    CUST_NBR    NUMBER(5)       NOT NULL,
    NAME        VARCHAR2(30)    NOT NULL,
    REGION_ID   NUMBER(5),
    INACTIVE_DT DATE,
    INACTIVE_IND CHAR(1),
    TOT_ORDERS  NUMBER(5),
    LAST_ORDER_DT DATE,
    CONSTRAINT customer_pk PRIMARY KEY (CUST_NBR)    );


CREATE TABLE DEPARTMENT(
    DEPT_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    LOCATION_ID NUMBER(3),
    CONSTRAINT department_pk PRIMARY KEY (DEPT_ID) );


CREATE TABLE EMPLOYEE(
    EMP_ID            NUMBER(5)    NOT NULL,
    FNAME             VARCHAR2(20),
    LNAME             VARCHAR2(20),
    DEPT_ID           NUMBER(5)    NOT NULL,
    MANAGER_EMP_ID    NUMBER(5),
    SALARY            NUMBER(5),
    HIRE_DATE         DATE,
    JOB_ID            NUMBER(3),
    CONSTRAINT employee_pk PRIMARY KEY (EMP_ID) );


CREATE TABLE INVENTORY_CLASS(
    INV_CLASS    VARCHAR2(3) NOT NULL,
    LOW_COST     NUMBER(8,2),
    HIGH_COST    NUMBER(8,2),
    CONSTRAINT inv_class_pk PRIMARY KEY (INV_CLASS));


CREATE TABLE JOB(
    JOB_ID    NUMBER(3) NOT NULL,
    FUNCTION  VARCHAR2(30),
    CONSTRAINT job_pk PRIMARY KEY (JOB_ID));
   
CREATE TABLE LINE_ITEM(
    ORDER_NBR     NUMBER(9)      NOT NULL,
    PART_NBR      VARCHAR2(20)   NOT NULL,
    QTY           NUMBER(5)      NOT NULL,
    FILLED_QTY    NUMBER(5),
    CONSTRAINT line_item_pk PRIMARY KEY (ORDER_NBR,PART_NBR) );


CREATE TABLE LOCATION(
    LOCATION_ID    NUMBER(3) NOT NULL,
    REGIONAL_GROUP VARCHAR2(20),
    CONSTRAINT location_pk PRIMARY KEY (LOCATION_ID) );


CREATE TABLE MONTHS(
    YEAR       NUMBER(4) NOT NULL,
    MONTH      NUMBER(2) NOT NULL,
    CONSTRAINT months_pk PRIMARY KEY (YEAR, MONTH) );


CREATE TABLE MTD_ORDERS(
    TOT_ORDERS                  NUMBER(7) NOT NULL,
    TOT_SALE_PRICE              NUMBER(11,2) NOT NULL,
    MAX_SALE_PRICE              NUMBER(9,2) NOT NULL,
    EUROPE_TOT_ORDERS           NUMBER(7) NOT NULL,
    EUROPE_TOT_SALE_PRICE       NUMBER(11,2) NOT NULL,
    EUROPE_MAX_SALE_PRICE       NUMBER(9,2) NOT NULL,
    NORTHAMERICA_TOT_ORDERS     NUMBER(7) NOT NULL,
    NORTHAMERICA_TOT_SALE_PRICE NUMBER(11,2) NOT NULL,
    NORTHAMERICA_MAX_SALE_PRICE NUMBER(9,2) NOT NULL
 ) ;


CREATE TABLE ORDERS(
    CUST_NBR       NUMBER(5) NOT NULL,
    REGION_ID      NUMBER(5) NOT NULL,
    SALESPERSON_ID NUMBER(5) NOT NULL,
    YEAR           NUMBER(4) NOT NULL,
    MONTH          NUMBER(2) NOT NULL,
    TOT_ORDERS     NUMBER(7) NOT NULL,
    TOT_SALEs      NUMBER(11,2) NOT NULL,
    CONSTRAINT orders_pk PRIMARY KEY (YEAR, MONTH, CUST_NBR, REGION_ID,   SALESPERSON_ID)    );
   
CREATE TABLE PART(
    PART_NBR         VARCHAR2(20)        NOT NULL,
    NAME             VARCHAR2(30)        NOT NULL,
    SUPPLIER_ID      NUMBER(5)           NOT NULL,
    STATUS           VARCHAR2(20)        NOT NULL,
    INVENTORY_QTY    NUMBER(6),
    UNIT_COST        NUMBER(8,2),
    RESUPPLY_DATE    DATE,
    CONSTRAINT part_pk PRIMARY KEY (PART_NBR) );


CREATE TABLE SALESPERSON(
    SALESPERSON_ID    NUMBER(5) NOT NULL,
    NAME              VARCHAR2(50) NOT NULL,
    PRIMARY_REGION_ID NUMBER(5) NOT NULL,
    CONSTRAINT salesperson_pk PRIMARY KEY (SALESPERSON_ID)  );


CREATE TABLE SUPPLIER(
    SUPPLIER_ID    NUMBER(5)     NOT NULL,
    NAME           VARCHAR2(30)  NOT NULL,
    CONSTRAINT supplier_pk PRIMARY KEY (SUPPLIER_ID) );


CREATE TABLE REGION(
    REGION_ID    NUMBER(5)       NOT NULL,
    NAME       VARCHAR2(20),
    SUPER_REGION_ID NUMBER(5),
    CONSTRAINT region_pk PRIMARY KEY (REGION_ID) );
        
CREATE TABLE ASSEMBLY (
 ASSEMBLY_TYPE              VARCHAR2(4)  NOT NULL,
 ASSEMBLY_ID                NUMBER(6)   NOT NULL,
 DESCRIPTION                VARCHAR2(20)  NOT NULL,
 PARENT_ASSEMBLY_TYPE       VARCHAR2(4),
 PARENT_ASSEMBLY_ID         NUMBER(6),
     CONSTRAINT assembly_pk PRIMARY KEY (ASSEMBLY_TYPE, ASSEMBLY_ID) );


– Create table retailer


CREATE TABLE RETAILER(
 RTLR_NBR             NUMBER(6)    NOT NULL ,
 NAME                 VARCHAR2(45),                   
 ADDRESS              VARCHAR2(40),
 CITY                 VARCHAR2(30),
 STATE                VARCHAR2(2),
 ZIP_CODE             VARCHAR2(9),
 AREA_CODE            NUMBER(3),
 PHONE_NUMBER         NUMBER(7),
 SALESPERSON_ID       NUMBER(4),
 CREDIT_LIMIT         NUMBER(9,2),
 COMMENTS             LONG,
    CONSTRAINT RETAILER_PK PRIMARY KEY (RTLR_NBR)    );


– Create view DEPT


CREATE OR REPLACE VIEW DEPT AS
SELECT *
FROM DEPARTMENT
WHERE DEPT_ID IN (10,20,30,40);     
  

面朝大海, 春暖花开 – 海子

从明天起, 做一个幸福的人 
喂马, 劈柴, 周游世界 
从明天起, 关心粮食和蔬菜 
我有一所房子, 面朝大海, 春暖花开 

从明天起, 和每一个亲人通信 
告诉他们我的幸福 
那幸福的闪电告诉我的 
我将告诉每一个人 

给每一条河每一座山取一个温暖的名字 
陌生人, 我也为你祝福 
愿你有一个灿烂的前程 
愿你有情人终成眷属 
愿你在尘世获的幸福 
我只愿面朝大海, 春暖花开

夸奖我们,赞叹我们的,这都不是名师。会讲我们,指示我们的,这才是善知识,有了他们我们才会进步。
白白的过一天,无所事事,就像犯了窃盗罪一样。
与其说是别人让你痛苦,不如说自己的修养不够。
内心没有分别心,就是真正的苦行。
当你劝告别人时,若不顾及别人的自尊心,那么再好的言语都没有用的。
要克服对死亡的恐惧,你必须要接受世上所有的人,都会死去的观念。
你每天若看见众生的过失和是非,你就要赶快去忏悔,这就是修行。
愚痴的人,一直想要别人了解他。有智慧的人,却努力的了解自己。
凡是能站在别人的角度为他人着想,这个就是慈悲。
其实爱美的人,只是与自己谈恋爱罢了。
无事莫把闲话聊,是非往往闲话生。
恋爱不是慈善事业,不能随便施舍的。感情是没有公式,没有原则,没有道理可循的。可是人们至死都还在执著与追求。
默默的关怀与祝福别人,那是一种无形的布施。

原文发表在
http://www.itpub.net/showthread.php?s=&threadid=201766


SQL> create table blocktest as select * from system.help;
表已创建。
SQL> insert into blocktest select * from blocktest;
已创建5085行。
SQL> insert into blocktest select * from blocktest;
已创建10170行。
此时数据库中应该有10170*2行。
SQL> connect /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
–在此使用UltraEdit修改数据文件,注意是修改,不是删除其中的字节,否则数据库无法正常打开.
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> select count(*) from blocktest.blocktest;
select count(*) from blocktest.blocktest
                               *
ERROR 位于第 1 行:
ORA-01578: ORACLE 数据块损坏(文件号10,块号160)
ORA-01110: 数据文件 10: ‘E:\ORACLE\ORADATA\ORADB\BLOCKTEST.ORA’


C:\>dbv file=blocktest.ora blocksize=8192
DBVERIFY: Release 9.2.0.1.0 – Production on 星期三 3月 10 17:52:32 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
DBVERIFY – 验证正在开始 : FILE = blocktest.ora
标记为损坏的页160
***
Corrupt block relative dba: 0×028000a0 (file 10, block 160)
Bad check value found during dbv:
Data in bad block -
 type: 6 format: 2 rdba: 0×028000a0
 last change scn: 0×0000.0007cbb1 seq: 0×1 flg: 0×06
 consistency value in tail: 0xcbb10601
 check value in block header: 0×7237, computed block checksum: 0xc37a
 spare1: 0×0, spare2: 0×0, spare3: 0×0
***
DBVERIFY – 验证完成
检查的页总数         :6400
处理的页总数(数据):179
失败的页总数(数据):0
处理的页总数(索引):0
失败的页总数(索引):0
处理的页总数(其它):20
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数            :6200
标记为损坏的总页数:1
汇入的页总数           :0


C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 – Production on 星期三 3月 10 17:55:05 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> connect /@oradb as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> SELECT tablespace_name, segment_type, owner, segment_name  FROM dba_extents   WHERE file_id = 10   and 160 between block_id AND block_id + blocks – 1;


TABLESPACE_NAME        SEGMENT_TYPE       OWNER  SEGMENT_NAME
—————— —————— —————- —————
BLOCKTEST              TABLE              BLOCKTEST BLOCKTEST

SQL> ALTER SYSTEM SET EVENTS=’10231 trace name context forever,level 10′ ;
系统已更改。
SQL> host;
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:\>exp blocktest/blocktest file=t.dmp tables=blocktest
Export: Release 9.2.0.1.0 – Production on 星期三 3月 10 18:06:15 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径 …
. . 正在导出表                       BLOCKTEST      20212 行被导出
在没有警告的情况下成功终止导出。
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 – Production on 星期三 3月 10 18:11:14 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> connect  / @oradb as sysdba
已连接。
SQL> alter system set events=’10231 trace name context off’;
系统已更改。
SQL>