第六章:with、正则、变量



- -使用 with子句
- -使用 WITH子句，可以定义一个查询块，该查询块可以在一个查询中
- -反复调用

- - with子句可以提高复杂查询的性能：
- -使用 WITH子句，Oracle服务器检索查询块的结果并存储它
- -在用户的临时表空间中。这可以提高性能。

- -使用 with子句写一个查询：
- -显示每个部门的名称以及总薪水，条件是这些部门的总薪水要大于
- -跨部门的平均薪水。

- -本例中的问题需要执行以下的中间计算：
- - 1、对每个部门计算总的月薪，并使用一个 WITH 子句存储结果
- - 2、计算跨部门的平均月薪，并使用一个 WITH 子句存储结果
- - 3、比较在第一步中计算的总的月薪和在第二步中计算的平均月薪。
- -如果一个特定部门的总的月薪大于跨部门的平均月薪，
- -对那个部门显示部门名称和总的月薪。

with dept_costs as (
   select d.department_name, sum(e.salary) dept_total
     from departments d join employees e
     on (d.department_id = e.department_id)
     group by d.department_name
),
avg_cost as (
   select sum(dept_total) / count( *) dept_avg
     from dept_costs
)    
select *
   from dept_costs
   where dept_total > ( select dept_avg
                        from avg_cost)
   order by department_name;

- -展开形式：
select *
   from (
    select d.department_name, sum(e.salary) dept_total
     from departments d join employees e
     on (d.department_id = e.department_id)
     group by d.department_name
)
   where dept_total > ( select dept_avg
                        from (
       select sum(dept_total) / count( *) dept_avg
          from (
    select d.department_name, sum(e.salary) dept_total
     from departments d join employees e
     on (d.department_id = e.department_id)
     group by d.department_name
)
   )  )
   order by department_name;


- -递归 with查询
- -在 11g中，递归 with查询有公式可以套用
- -就是所谓的使用公用表表达式（ CTE ）的递归查询

- -根据给定的航班信息，构建出所有可能的新的航班。
- -构建规则：到达航班的目的地等于出发航班的出发地

create table flights(
   source varchar2( 10),
  destin varchar2( 10),
  flight_time number
);

insert into flights
   values( '上海', '北京', 1. 3);

insert into flights
   values( '广州', '深圳', 1. 1); 

insert into flights
   values( '北京', '广州', 5. 8);  

with reachable_from( source,destin,totalFlightTime)
   as (
     select source,destin,flight_time
       from flights
     union all  
     select incoming. source,outgoing.destin,
           incoming.totalFlightTime + outgoing.flight_time
      from reachable_from incoming,flights outgoing
      where incoming.destin = outgoing. source
  )
select *
   from reachable_from;


- -使用正则表达式
- -正则表达式是一个特殊的字符串，用来作为匹配或者搜索模式

- -正则表达式中有特殊含义的字符叫做元字符。

- -在Oracle中，有 5个函数提供了对正则表达式的支持

- - 1、regexp_like:
- -用来替换 like操作符使用。该函数比较其两个参数，返回 true或 false。

- -本例显示了名字包含 Steven 或者 Stephen 的所有雇员的姓名
select first_name,last_name
   from employees
   where regexp_like(first_name, '^Ste(v|ph)en$');

- - 2、regexp_replace:在源字符串中查找满足正则表达式的
- -子串，找到以后用替换字符串换掉它

- -将电话号码列中的点替换成短横线
select phone_number,
       regexp_replace(phone_number, '\.', '-')
from employees;

- -只替换第二个点
select phone_number,
       regexp_replace(phone_number, '\.', '-', 1, 2)
from employees; 

- - 3、regexp_instr:查找满足正则表达式的子串出现的位置
- -注意，语法[: < class >:]指明一个字符类，匹配这个类中的任意字符。
- -[:alpha:]匹配任意字母字符

- -在街道地址中，查找第一个字母字符出现的位置
select street_address,
       regexp_instr(street_address, '[[:alpha:]]')
   from locations;

- -等价写法
select street_address,
       regexp_instr(street_address, '[a-zA-Z]')
   from locations;  

- - 4 /regexp_substr:

- -从街道地址中查找一个子串：前后各有一个空格，之间可以有
- - 1到多个任意字符
select street_address,
       regexp_substr(street_address, ' [^ ]+ ')
   from locations;  

- -正则表达式中的子表达式
- -小括号确定子表达式。从左往右读，并且从外括号到内括号

- -在源字符串中查找满足子表达式的子串第一次出现的位置
select regexp_instr(
   '0123456789',
    '(123)(4(56)(78))',
   1,
   1,
   0,
   'i',
   4
)
from dual;


- - 5 /regexp_count:
- - 11g新加的
- -它返回一个整数表示在源字符串中模式出现的次数。
- -如果没有找到匹配的，则返回 0.

select regexp_count( 'hello world', 'l')
   from dual;

- -在 check约束中使用正则表达式
create table emp8
   as select * from employees;

- -添加约束 
- - SQL 错误: ORA - 02293: 无法验证 (HR.EMP8_EMAIL_CK) - 违反检查约束条件
alter table emp8
   add constraint emp8_email_ck 
     check (regexp_like(email, '@'));

- -novalidate选项告诉oracle，对表中已有的行不要检查约束    
alter table emp8
   add constraint emp8_email_ck 
     check (regexp_like(email, '@')) novalidate;    

- - 错误: ORA - 02290: 违反检查约束条件 (HR.EMP8_EMAIL_CK)
insert into emp8(employee_id,last_name,job_id,
  hire_date,email)
   values( 300, 'zs', 'SA_REP',sysdate, 'zs163.com');

- -进一步学习：
- - select中model子句的使用：做类似电子表格计算
- -分析函数的使用：例如 sum()...over()...
- -层次查询: connect by
- -分组操作中的 cube和 rollup子句：产生一些小计等汇总操作
- -pivot和unpivot关键字： 11g专门做“行转列”和“列转行”查询

- -第一个plsql程序
declare
  v_first_name varchar2( 35);
  v_last_name varchar2( 35);
begin
   - -查找学生 123的姓名并保存到变量中
   select first_name,last_name
     into v_first_name,v_last_name
     from student
     where student_id = 123;

   - -打印变量
  dbms_output.put_line( '学生123的姓名是:'||
  v_first_name|| ' '||v_last_name);
exception
   when no_data_found   then
    dbms_output.put_line( '学生123不存在');  
end;

- -改进上例，使用替代变量接收用户输入的学生编号
declare
  v_student_id number : = &sv_id;
  v_first_name varchar2( 35);
  v_last_name varchar2( 35);
begin
   - -查找学生 123的姓名并保存到变量中
   select first_name,last_name
     into v_first_name,v_last_name
     from student
     where student_id =v_student_id;

   - -打印变量
  dbms_output.put_line( '学生'||v_student_id|| '的姓名是:'||
  v_first_name|| ' '||v_last_name);
exception
   when no_data_found   then
    dbms_output.put_line( '学生'||v_student_id|| '不存在');  
end;

- -注意：在sqlplus中要看到 dbms_output.put_line
- -过程的输出，必须执行命令：
- - set serveroutput on

- -DBMS_OUTPUT.PUT_LINE会把信息输出到缓存进行存储。
- -当程序执行完毕后，缓存中信息会显示在屏幕上。
- -缓存的尺寸在 2,000字节和 1,000,000字节之间

- - - -调整put_line过程使用的缓冲大小
set serveroutput on size 50000