@H_502_3@-
@H_502_3@-使用
with子句
@H_502_3@- @H_502_3@-使用 WITH子句，可以定义一个查询块，该查询块可以在一个查询中
@H_502_3@- @H_502_3@-反复调用
@H_502_3@- @H_502_3@- with子句可以提高复杂查询的性能：
@H_502_3@- @H_502_3@-使用 WITH子句，Oracle服务器检索查询块的结果并存储它
@H_502_3@- @H_502_3@-在用户的临时表空间中。这可以提高性能。
@H_502_3@- @H_502_3@-使用 with子句写一个查询：
@H_502_3@- @H_502_3@-显示每个部门的名称以及总薪水，条件是这些部门的总薪水要大于
@H_502_3@- @H_502_3@-跨部门的平均薪水。
@H_502_3@- @H_502_3@-本例中的问题需要执行以下的中间计算：
@H_502_3@- @H_502_3@- 1、对每个部门计算总的月薪，并使用一个 WITH 子句存储结果
@H_502_3@- @H_502_3@- 2、计算跨部门的平均月薪，并使用一个 WITH 子句存储结果
@H_502_3@- @H_502_3@- 3、比较在第一步中计算的总的月薪和在第二步中计算的平均月薪。
@H_502_3@- @H_502_3@-如果一个特定部门的总的月薪大于跨部门的平均月薪，
@H_502_3@- @H_502_3@-对那个部门显示部门名称和总的月薪。
with dept_costs as (
select d.department_name,sum(e.salary) dept_total
from departments d join employees e
on (d.department_id @H_502_3@ = e.department_id)
group by d.department_name
),
avg_cost as (
select sum(dept_total) @H_502_3@/ count( @H_502_3@*) dept_avg
from dept_costs
)
select @H_502_3@ *
from dept_costs
where dept_total @H_502_3@ > ( select dept_avg
from avg_cost)
order by department_name;
@H_502_3@- @H_502_3@-展开形式：
select @H_502_3@ *
from (
select d.department_name,sum(e.salary) dept_total
from departments d join employees e
on (d.department_id @H_502_3@ = e.department_id)
group by d.department_name
)
where dept_total @H_502_3@ > ( select dept_avg
from (
select sum(dept_total) @H_502_3@/ count( @H_502_3@*) dept_avg
from (
select d.department_name,sum(e.salary) dept_total
from departments d join employees e
on (d.department_id @H_502_3@ = e.department_id)
group by d.department_name
)
))
order by department_name;
@H_502_3@- @H_502_3@-递归 with查询
@H_502_3@- @H_502_3@-在 11g中，递归 with查询有公式可以套用
@H_502_3@- @H_502_3@-就是所谓的使用公用表表达式（ CTE ）的递归查询
@H_502_3@- @H_502_3@-根据给定的航班信息，构建出所有可能的新的航班。
@H_502_3@- @H_502_3@-构建规则：到达航班的目的地等于出发航班的出发地
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 @H_502_3@+ outgoing.flight_time
from reachable_from incoming,flights outgoing
where incoming.destin @H_502_3@ = outgoing. source
)
select @H_502_3@ *
from reachable_from;
@H_502_3@- @H_502_3@-使用正则表达式
@H_502_3@- @H_502_3@-正则表达式是一个特殊的字符串，用来作为匹配或者搜索模式
@H_502_3@- @H_502_3@-正则表达式中有特殊含义的字符叫做元字符。
@H_502_3@- @H_502_3@-在Oracle中，有 5个函数提供了对正则表达式的支持
@H_502_3@- @H_502_3@- 1、regexp_like:
@H_502_3@- @H_502_3@-用来替换 like操作符使用。该函数比较其两个参数，返回 true或 false。
@H_502_3@- @H_502_3@-本例显示了名字包含 Steven 或者 Stephen 的所有雇员的姓名
select first_name,last_name
from employees
where regexp_like(first_name,'^Ste(v|ph)en$');
@H_502_3@- @H_502_3@- 2、regexp_replace:在源字符串中查找满足正则表达式的
@H_502_3@- @H_502_3@-子串，找到以后用替换字符串换掉它
@H_502_3@- @H_502_3@-将电话号码列中的点替换成短横线
select phone_number,
regexp_replace(phone_number,'\.','-')
from employees;
@H_502_3@- @H_502_3@-只替换第二个点
select phone_number,'-',1,2)
from employees;
@H_502_3@- @H_502_3@- 3、regexp_instr:查找满足正则表达式的子串出现的位置
@H_502_3@- @H_502_3@-注意，语法[: @H_502_3@< class @H_502_3@>:]指明一个字符类，匹配这个类中的任意字符。
@H_502_3@- @H_502_3@-[:alpha:]匹配任意字母字符
@H_502_3@- @H_502_3@-在街道地址中，查找第一个字母字符出现的位置
select street_address,
regexp_instr(street_address,'[[:alpha:]]')
from locations;
@H_502_3@- @H_502_3@-等价写法
select street_address,'[a-zA-Z]')
from locations;
@H_502_3@- @H_502_3@- 4 @H_502_3@/regexp_substr:
@H_502_3@- @H_502_3@-从街道地址中查找一个子串：前后各有一个空格，之间可以有
@H_502_3@- @H_502_3@- 1到多个任意字符
select street_address,
regexp_substr(street_address,' [^ ]+ ')
from locations;
@H_502_3@- @H_502_3@-正则表达式中的子表达式
@H_502_3@- @H_502_3@-小括号确定子表达式。从左往右读，并且从外括号到内括号
@H_502_3@- @H_502_3@-在源字符串中查找满足子表达式的子串第一次出现的位置
select regexp_instr(
'0123456789',
'(123)(4(56)(78))',
1,
0,
'i',
4
)
from dual;
@H_502_3@- @H_502_3@- 5 @H_502_3@/regexp_count:
@H_502_3@- @H_502_3@- 11g新加的
@H_502_3@- @H_502_3@-它返回一个整数表示在源字符串中模式出现的次数。
@H_502_3@- @H_502_3@-如果没有找到匹配的，则返回 0.
select regexp_count( 'hello world','l')
from dual;
@H_502_3@- @H_502_3@-在 check约束中使用正则表达式
create table emp8
as select @H_502_3@* from employees;
@H_502_3@- @H_502_3@-添加约束
@H_502_3@- @H_502_3@- sql 错误: ORA @H_502_3@- 02293: 无法验证 (HR.EMP8_EMAIL_CK) @H_502_3@- 违反检查约束条件
alter table emp8
add constraint emp8_email_ck
check (regexp_like(email,'@'));
@H_502_3@- @H_502_3@-novalidate选项告诉oracle，对表中已有的行不要检查约束
alter table emp8
add constraint emp8_email_ck
check (regexp_like(email,'@')) novalidate;
@H_502_3@- @H_502_3@- 错误: ORA @H_502_3@- 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');
@H_502_3@- @H_502_3@-进一步学习：
@H_502_3@- @H_502_3@- select中model子句的使用：做类似电子表格计算
@H_502_3@- @H_502_3@-分析函数的使用：例如 sum()...over()...
@H_502_3@- @H_502_3@-层次查询: connect by
@H_502_3@- @H_502_3@-分组操作中的 cube和 rollup子句：产生一些小计等汇总操作
@H_502_3@- @H_502_3@-pivot和unpivot关键字： 11g专门做“行转列”和“列转行”查询
@H_502_3@- @H_502_3@-第一个plsql程序
declare
v_first_name varchar2( 35);
v_last_name varchar2( 35);
begin
@H_502_3@- @H_502_3@-查找学生 123的姓名并保存到变量中
select first_name,last_name
into v_first_name,v_last_name
from student
where student_id @H_502_3@= 123;
@H_502_3@- @H_502_3@-打印变量
dbms_output.put_line( '学生123的姓名是:'||
v_first_name|| ' '||v_last_name);
exception
when no_data_found then
dbms_output.put_line( '学生123不存在');
end;
@H_502_3@- @H_502_3@-改进上例，使用替代变量接收用户输入的学生编号
declare
v_student_id number : @H_502_3@= &sv_id;
v_first_name varchar2( 35);
v_last_name varchar2( 35);
begin
@H_502_3@- @H_502_3@-查找学生 123的姓名并保存到变量中
select first_name,v_last_name
from student
where student_id @H_502_3@=v_student_id;
@H_502_3@- @H_502_3@-打印变量
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;
@H_502_3@- @H_502_3@-注意：在sqlplus中要看到 dbms_output.put_line
@H_502_3@- @H_502_3@-过程的输出，必须执行命令：
@H_502_3@- @H_502_3@- set serveroutput on
@H_502_3@- @H_502_3@-DBMS_OUTPUT.PUT_LINE会把信息输出到缓存进行存储。
@H_502_3@- @H_502_3@-当程序执行完毕后，缓存中信息会显示在屏幕上。
@H_502_3@- @H_502_3@-缓存的尺寸在 2,000字节和 1,000,000字节之间
@H_502_3@- @H_502_3@- @H_502_3@- @H_502_3@-调整put_line过程使用的缓冲大小
set serveroutput on size 50000
@H_502_3@- @H_502_3@-使用 WITH子句，可以定义一个查询块，该查询块可以在一个查询中
@H_502_3@- @H_502_3@-反复调用
@H_502_3@- @H_502_3@- with子句可以提高复杂查询的性能：
@H_502_3@- @H_502_3@-使用 WITH子句，Oracle服务器检索查询块的结果并存储它
@H_502_3@- @H_502_3@-在用户的临时表空间中。这可以提高性能。
@H_502_3@- @H_502_3@-使用 with子句写一个查询：
@H_502_3@- @H_502_3@-显示每个部门的名称以及总薪水，条件是这些部门的总薪水要大于
@H_502_3@- @H_502_3@-跨部门的平均薪水。
@H_502_3@- @H_502_3@-本例中的问题需要执行以下的中间计算：
@H_502_3@- @H_502_3@- 1、对每个部门计算总的月薪，并使用一个 WITH 子句存储结果
@H_502_3@- @H_502_3@- 2、计算跨部门的平均月薪，并使用一个 WITH 子句存储结果
@H_502_3@- @H_502_3@- 3、比较在第一步中计算的总的月薪和在第二步中计算的平均月薪。
@H_502_3@- @H_502_3@-如果一个特定部门的总的月薪大于跨部门的平均月薪，
@H_502_3@- @H_502_3@-对那个部门显示部门名称和总的月薪。
with dept_costs as (
select d.department_name,sum(e.salary) dept_total
from departments d join employees e
on (d.department_id @H_502_3@ = e.department_id)
group by d.department_name
),
avg_cost as (
select sum(dept_total) @H_502_3@/ count( @H_502_3@*) dept_avg
from dept_costs
)
select @H_502_3@ *
from dept_costs
where dept_total @H_502_3@ > ( select dept_avg
from avg_cost)
order by department_name;
@H_502_3@- @H_502_3@-展开形式：
select @H_502_3@ *
from (
select d.department_name,sum(e.salary) dept_total
from departments d join employees e
on (d.department_id @H_502_3@ = e.department_id)
group by d.department_name
)
where dept_total @H_502_3@ > ( select dept_avg
from (
select sum(dept_total) @H_502_3@/ count( @H_502_3@*) dept_avg
from (
select d.department_name,sum(e.salary) dept_total
from departments d join employees e
on (d.department_id @H_502_3@ = e.department_id)
group by d.department_name
)
))
order by department_name;
@H_502_3@- @H_502_3@-递归 with查询
@H_502_3@- @H_502_3@-在 11g中，递归 with查询有公式可以套用
@H_502_3@- @H_502_3@-就是所谓的使用公用表表达式（ CTE ）的递归查询
@H_502_3@- @H_502_3@-根据给定的航班信息，构建出所有可能的新的航班。
@H_502_3@- @H_502_3@-构建规则：到达航班的目的地等于出发航班的出发地
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 @H_502_3@+ outgoing.flight_time
from reachable_from incoming,flights outgoing
where incoming.destin @H_502_3@ = outgoing. source
)
select @H_502_3@ *
from reachable_from;
@H_502_3@- @H_502_3@-使用正则表达式
@H_502_3@- @H_502_3@-正则表达式是一个特殊的字符串，用来作为匹配或者搜索模式
@H_502_3@- @H_502_3@-正则表达式中有特殊含义的字符叫做元字符。
@H_502_3@- @H_502_3@-在Oracle中，有 5个函数提供了对正则表达式的支持
@H_502_3@- @H_502_3@- 1、regexp_like:
@H_502_3@- @H_502_3@-用来替换 like操作符使用。该函数比较其两个参数，返回 true或 false。
@H_502_3@- @H_502_3@-本例显示了名字包含 Steven 或者 Stephen 的所有雇员的姓名
select first_name,last_name
from employees
where regexp_like(first_name,'^Ste(v|ph)en$');
@H_502_3@- @H_502_3@- 2、regexp_replace:在源字符串中查找满足正则表达式的
@H_502_3@- @H_502_3@-子串，找到以后用替换字符串换掉它
@H_502_3@- @H_502_3@-将电话号码列中的点替换成短横线
select phone_number,
regexp_replace(phone_number,'\.','-')
from employees;
@H_502_3@- @H_502_3@-只替换第二个点
select phone_number,'-',1,2)
from employees;
@H_502_3@- @H_502_3@- 3、regexp_instr:查找满足正则表达式的子串出现的位置
@H_502_3@- @H_502_3@-注意，语法[: @H_502_3@< class @H_502_3@>:]指明一个字符类，匹配这个类中的任意字符。
@H_502_3@- @H_502_3@-[:alpha:]匹配任意字母字符
@H_502_3@- @H_502_3@-在街道地址中，查找第一个字母字符出现的位置
select street_address,
regexp_instr(street_address,'[[:alpha:]]')
from locations;
@H_502_3@- @H_502_3@-等价写法
select street_address,'[a-zA-Z]')
from locations;
@H_502_3@- @H_502_3@- 4 @H_502_3@/regexp_substr:
@H_502_3@- @H_502_3@-从街道地址中查找一个子串：前后各有一个空格，之间可以有
@H_502_3@- @H_502_3@- 1到多个任意字符
select street_address,
regexp_substr(street_address,' [^ ]+ ')
from locations;
@H_502_3@- @H_502_3@-正则表达式中的子表达式
@H_502_3@- @H_502_3@-小括号确定子表达式。从左往右读，并且从外括号到内括号
@H_502_3@- @H_502_3@-在源字符串中查找满足子表达式的子串第一次出现的位置
select regexp_instr(
'0123456789',
'(123)(4(56)(78))',
1,
0,
'i',
4
)
from dual;
@H_502_3@- @H_502_3@- 5 @H_502_3@/regexp_count:
@H_502_3@- @H_502_3@- 11g新加的
@H_502_3@- @H_502_3@-它返回一个整数表示在源字符串中模式出现的次数。
@H_502_3@- @H_502_3@-如果没有找到匹配的，则返回 0.
select regexp_count( 'hello world','l')
from dual;
@H_502_3@- @H_502_3@-在 check约束中使用正则表达式
create table emp8
as select @H_502_3@* from employees;
@H_502_3@- @H_502_3@-添加约束
@H_502_3@- @H_502_3@- sql 错误: ORA @H_502_3@- 02293: 无法验证 (HR.EMP8_EMAIL_CK) @H_502_3@- 违反检查约束条件
alter table emp8
add constraint emp8_email_ck
check (regexp_like(email,'@'));
@H_502_3@- @H_502_3@-novalidate选项告诉oracle，对表中已有的行不要检查约束
alter table emp8
add constraint emp8_email_ck
check (regexp_like(email,'@')) novalidate;
@H_502_3@- @H_502_3@- 错误: ORA @H_502_3@- 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');
@H_502_3@- @H_502_3@-进一步学习：
@H_502_3@- @H_502_3@- select中model子句的使用：做类似电子表格计算
@H_502_3@- @H_502_3@-分析函数的使用：例如 sum()...over()...
@H_502_3@- @H_502_3@-层次查询: connect by
@H_502_3@- @H_502_3@-分组操作中的 cube和 rollup子句：产生一些小计等汇总操作
@H_502_3@- @H_502_3@-pivot和unpivot关键字： 11g专门做“行转列”和“列转行”查询
@H_502_3@- @H_502_3@-第一个plsql程序
declare
v_first_name varchar2( 35);
v_last_name varchar2( 35);
begin
@H_502_3@- @H_502_3@-查找学生 123的姓名并保存到变量中
select first_name,last_name
into v_first_name,v_last_name
from student
where student_id @H_502_3@= 123;
@H_502_3@- @H_502_3@-打印变量
dbms_output.put_line( '学生123的姓名是:'||
v_first_name|| ' '||v_last_name);
exception
when no_data_found then
dbms_output.put_line( '学生123不存在');
end;
@H_502_3@- @H_502_3@-改进上例，使用替代变量接收用户输入的学生编号
declare
v_student_id number : @H_502_3@= &sv_id;
v_first_name varchar2( 35);
v_last_name varchar2( 35);
begin
@H_502_3@- @H_502_3@-查找学生 123的姓名并保存到变量中
select first_name,v_last_name
from student
where student_id @H_502_3@=v_student_id;
@H_502_3@- @H_502_3@-打印变量
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;
@H_502_3@- @H_502_3@-注意：在sqlplus中要看到 dbms_output.put_line
@H_502_3@- @H_502_3@-过程的输出，必须执行命令：
@H_502_3@- @H_502_3@- set serveroutput on
@H_502_3@- @H_502_3@-DBMS_OUTPUT.PUT_LINE会把信息输出到缓存进行存储。
@H_502_3@- @H_502_3@-当程序执行完毕后，缓存中信息会显示在屏幕上。
@H_502_3@- @H_502_3@-缓存的尺寸在 2,000字节和 1,000,000字节之间
@H_502_3@- @H_502_3@- @H_502_3@- @H_502_3@-调整put_line过程使用的缓冲大小
set serveroutput on size 50000
