剑破冰山oracle 开发艺术10.4源代码
由于书记没有提供源代码，本人录入后提供给大家，仅供学习：
剑破冰山oracle 开发艺术10.4源代码
1，employeeinfo表
create table employeeinfo(emp_id int,
emp_name varchar2(50),
dept_id int,
hire_date date,
salary int);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(100, 'Wang John', 10, to_date('1990-01-01', 'yyyy-mm-dd'), 20000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(101, 'Kochhar Neena', 90, to_date('1989-09-21', 'yyyy-mm-dd'), 17000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(102, 'De Haan Lex', 90, to_date('1993-01-13', 'yyyy-mm-dd'), 17000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(103, 'Hunold Alexander', 60, to_date('1990-01-03', 'yyyy-mm-dd'), 9000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(104, 'Ernst Bruce', 60, to_date('1991-05-21', 'yyyy-mm-dd'), 6000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(105, 'Austin David', 60, to_date('1997-06-25', 'yyyy-mm-dd'), 4800);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(106, 'Pataballa Valli', 60, to_date('1998-02-05', 'yyyy-mm-dd'), 4800);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(107, 'Lorentz Diana', 60, to_date('1999-02-07', 'yyyy-mm-dd'), 4200);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(108, 'Greenberg Nancy', 100, to_date('1994-08-17', 'yyyy-mm-dd'), 12000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(109, 'Faviet Daniel', 100, to_date('1994-08-16', 'yyyy-mm-dd'), 9000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(110, 'Chen John', 100, to_date('1997-09-26', 'yyyy-mm-dd'), 8200);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(111, 'Sciarra Ismael', 100, to_date('1997-09-30', 'yyyy-mm-dd'), 7700);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(112,
'Urman Jose Manuel',
100,
to_date('1998-03-07', 'yyyy-mm-dd'),
7800);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(113, 'Popp Luis', 100, to_date('1999-12-07', 'yyyy-mm-dd'), 6900);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(114, 'Raphaely Den', 30, to_date('1994-12-07', 'yyyy-mm-dd'), 11000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(120, 'Weiss Matthew', 50, to_date('1996-07-18', 'yyyy-mm-dd'), 8000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(121, 'Fripp Adam', 50, to_date('1997-04-10', 'yyyy-mm-dd'), 8200);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(122, 'Kaufling Payam', 50, to_date('1995-05-01', 'yyyy-mm-dd'), 7900);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(123, 'Vollman Shanta', 50, to_date('1997-10-10', 'yyyy-mm-dd'), 6500);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(124, 'Mourgos Kevin', 50, to_date('1999-11-16', 'yyyy-mm-dd'), 4800);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(145, 'Russell John', 80, to_date('1996-10-01', 'yyyy-mm-dd'), 14000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(146, 'Partners Karen', 80, to_date('1997-01-05', 'yyyy-mm-dd'), 13500);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(147,
'Errazuriz Alberto',
80,
to_date('1997-03-10', 'yyyy-mm-dd'),
12000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(148, 'Cambrault Gerald', 80, to_date('1999-10-15', 'yyyy-mm-dd'), 11000);
insert into employeeinfo
(emp_id, emp_name, dept_id, hire_date, salary)
values
(149, 'Zlotkey Eleni', 80, to_date('2000-01-29', 'yyyy-mm-dd'), 10500);
2，sales表
create table sales(country varchar2(10),sale_month varchar2(10),
sales_number int,sales_value number(10,2));
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-1-1', 1200, 500000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-2-1', 1150, 450000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-3-1', 1300, 520000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-4-1', 1280, 510000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-5-1', 1350, 530000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-6-1', 1400, 535000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-7-1', 1300, 510000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-8-1', 1250, 460000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-9-1', 1400, 530000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-10-1', 1380, 520000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-11-1', 1450, 540000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2008-12-1', 1500, 545000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-1-1', 1600, 550000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-2-1', 1390, 532000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-3-1', 1730, 570000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-4-1', 1900, 600000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-5-1', 1850, 585000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-6-1', 3800, 780000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-7-1', 1700, 560000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-8-1', 1490, 542000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-9-1', 1830, 580000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-10-1', 2000, 610000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-11-1', 1950, 595000.00);
insert into sales
(country, sale_month, sales_number, sales_value)
values
('USA', '2009-12-1', 1900, 590000.00);
3,常见分析函数
分析该两个语句区别 ：
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
avg(salary),
sum(salary),
max(salary),
count(salary)
from employeeinfo
where dept_id in (10, 30, 50, 60)
group by emp_id, emp_name, dept_id, hire_date, salary
order by hire_date;
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
avg(salary) over(partition by dept_id order by hire_date) avg_salary,
sum(salary) over(partition by dept_id order by hire_date) sum_salary,
max(salary) over(partition by dept_id order by hire_date) max_salary,
count(salary) over(partition by dept_id order by hire_date) count_salary
from employeeinfo
where dept_id in (10, 30, 50, 60);
4,rows用法
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
sum(salary) over(partition by dept_id order by hire_date) sum_salary_part_order,
sum(salary) over(partition by dept_id) sum_salary_part,
sum(salary) over(order by hire_date) sum_salary_order,
sum(salary) over(partition by dept_id order by hire_date rows between unbounded preceding and unbounded following) sum_1,
sum(salary) over(partition by dept_id order by hire_date rows between unbounded preceding and current row) sum_2,
sum(salary) over(partition by dept_id order by hire_date rows between unbounded preceding and 1 /*value_expr*/ preceding) sum_3,
sum(salary) over(partition by dept_id order by hire_date rows between unbounded preceding and 1 /*value_expr*/ following) sum_4,
sum(salary) over(partition by dept_id order by hire_date rows between current row and unbounded following) sum_5,
sum(salary) over(partition by dept_id order by hire_date rows between current row and 1 /*value_expr*/ following) sum_6,
sum(salary) over(partition by dept_id order by hire_date rows between current row and 1 /*value_expr*/ following) sum_7,
sum(salary) over(partition by dept_id order by hire_date rows between 1 /*value_expr*/ preceding and unbounded following) sum_8,
sum(salary) over(partition by dept_id order by hire_date rows between 1 /*value_expr*/ preceding and current row) sum_9,
sum(salary) over(partition by dept_id order by hire_date rows between 2 /*value_expr1*/ preceding and 1 /*value_expr2*/ preceding) sum_10,
sum(salary) over(partition by dept_id order by hire_date rows between 1 /*value_expr1*/ preceding and 2 /*value_expr2*/ following) sum_11,
sum(salary) over(partition by dept_id order by hire_date rows between 1 /*value_expr*/ following and unbounded following) sum_12,
sum(salary) over(partition by dept_id order by hire_date rows between 1 /*value_expr1*/ following and 2 /*value_expr2*/ following) sum_13,
sum(salary) over(partition by dept_id order by hire_date rows unbounded preceding) sum_14,
sum(salary) over(partition by dept_id order by hire_date rows current row) sum_15,
sum(salary) over(partition by dept_id order by hire_date rows 1 /*value_expr*/ preceding) sum_16
from employeeinfo;
5,range具体用法
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
sum(salary) over(partition by dept_id order by hire_date) sum_salary_part_order,
sum(salary) over(partition by dept_id) sum_salary_part,
sum(salary) over(order by hire_date) sum_salary_order,
sum(salary) over(partition by dept_id order by hire_date range between unbounded preceding and unbounded following) sum_1,
sum(salary) over(partition by dept_id order by hire_date range between unbounded preceding and current row) sum_2,
sum(salary) over(partition by dept_id order by hire_date range between unbounded preceding and 365 /*value*/ preceding) sum_3,
sum(salary) over(partition by dept_id order by hire_date range between unbounded preceding and 365 /*value_expr*/ following) sum_4,
sum(salary) over(partition by dept_id order by hire_date range between current row and unbounded following) sum_5,
sum(salary) over(partition by dept_id order by hire_date range between current row and current row) sum_6,
sum(salary) over(partition by dept_id order by hire_date range between current row and 365 /*value_expr*/ following) sum_7,
sum(salary) over(partition by dept_id order by hire_date range between 365 /*value_expr*/ preceding and unbounded following) sum_8,
sum(salary) over(partition by dept_id order by hire_date range between 365 /*value_expr*/ preceding and current row) sum_9,
sum(salary) over(partition by dept_id order by hire_date range between 365 /*value_expr1*/ preceding and 30 /*value_expr2*/ preceding) sum_10,
sum(salary) over(partition by dept_id order by hire_date range between 365 /*value_expr1*/ preceding and 30 /*value_expr2*/ following) sum_11,
sum(salary) over(partition by dept_id order by hire_date range between 365 /*value_expr*/ following and unbounded following) sum_12,
sum(salary) over(partition by dept_id order by hire_date range between 30 /*value_expr1*/ following and 365 /*value_expr2*/ following) sum_13,
sum(salary) over(partition by dept_id order by hire_date range unbounded preceding) sum_14,
sum(salary) over(partition by dept_id order by hire_date range current row) sum_15,
sum(salary) over(partition by dept_id order by hire_date range 365 /*value_expr*/ preceding) sum_16
from employeeinfo;
6,keep用法
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
dense_rank() over(partition by dept_id order by salary) dense_rank,
min(hire_date) keep(dense_rank first order by salary) over(partition by dept_id) min_first,
min(hire_date) keep(dense_rank last order by salary) over(partition by dept_id) min_last,
max(hire_date) keep(dense_rank first order by salary) over(partition by dept_id) max_dirst,
max(hire_date) keep(dense_rank last order by salary) over(partition by dept_id) max_last
from employeeinfo
where dept_id in (10, 30, 50, 90);
7,排序函数用法：
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
rank() over(partition by dept_id order by salary) as rank,
dense_rank() over(partition by dept_id order by hire_date) as dense_rank,
min(salary) keep(dense_rank first order by hire_date) over(partition by dept_id) worst,
max(salary) keep(dense_rank last order by hire_date) over(partition by dept_id) best,
lag(salary, 1, 0) over(order by hire_date) as prev_sal,
lead(salary, 1, 0) over(order by hire_date) as next_sal,
first_value(emp_name) over (partition by dept_id order by salary) as first_value_asc,
first_value(emp_name) over (partition by dept_id order by salary) as first_value_desc,
last_value(emp_name) over (partition by dept_id order by salary) as last_value_asc,
last_value(emp_name) over (partition by dept_id order by salary) as last_value_desc,
row_number() over (partition by dept_id order by emp_id) as row_number
from employeeinfo;
8,数据分布函数
select emp_id,
emp_name,
dept_id,
hire_date,
salary,
cume_dist() over(partition by dept_id order by salary) as cume_dist,
ntile(4) over(partition by dept_id order by salary) as quartile,
percent_rank() over(partition by dept_id order by salary) as pr,
percentile_disc(0.7) within group(order by salary) over(partition by dept_id) "Percentile_Disc",
percentile_cont(0.7) within group(order by salary) over(partition by dept_id) "Percentile_cont"
from employeeinfo;
