基本的oracle语句以及sql语法（四）

46.厉害的with语句：  
        WITH   
         dept_costs  AS (--定义了一个临时的表  
                   SELECT  d.department_name, SUM(e.salary) AS dept_total--其间定义了一个临时的列dept_total  
                   FROM    employees e, departments d  
                   WHERE   e.department_id = d.department_id  
                   GROUP BY d.department_name),/*注意这里有逗号*/  
        avg_cost    AS (  
           SELECT SUM(dept_total)/COUNT(*) AS dept_avg  
           FROM   dept_costs)--这里的第二张临时表里面就引用了前面定义的临时表和之间的列！  
        SELECT *  FROM   dept_costs  WHERE  dept_total >  (SELECT dept_avg   FROM avg_cost) ORDER BY department_name;---最后的查询语句中使用了前面的临时表  
47.遍历树：  
        SELECT employee_id, last_name, job_id, manager_id  
        FROM   employees  
        START  WITH  employee_id = 101  
        CONNECT BY PRIOR manager_id = employee_id ;--自底向上的遍历树。  
 
48.更新语句  
UPDATE employees SET   
    job_id = 'SA_MAN', salary = salary + 1000, department_id = 120   
    WHERE first_name||' '||last_name = 'Douglas Grant';   
 
UPDATE TABLE (SELECT projs   
           FROM dept d WHERE d.dno = 123)  p   
  SET p.budgets = p.budgets + 1   
  WHERE p.pno IN (123, 456);