第六章: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,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,'-',1,2)
from employees;

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

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

- -等价写法
select 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,
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,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