Oracle PL/SQL Tutorial/View/Filter View — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 16:45, 26 мая 2010
Содержание
- 1 Create a view for employee and its course during
- 2 Create a view to link two table together
- 3 Create a view with condition
- 4 Creating a simple filtering view
- 5 Creating a view that joins tables
- 6 Query a filtering view
- 7 Top "N" analysis
- 8 Use view to simplify the query
- 9 Use view to wrap complex select statement
Create a view for employee and its course during
<source lang="sql">
SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1)) ;
Table created. SQL> SQL> SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 ); 1 row created. SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 ); 1 row created. SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 ); 1 row created. SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 ); 1 row created. SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 ); 1 row created. SQL> insert into registrations values (9, "XML",date "2000-02-03",4 ); 1 row created. SQL> insert into registrations values (10,"XML",date "2000-02-03",5 ); 1 row created. SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL); 1 row created. SQL> SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
Table created. SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> SQL> create or replace view course_days as
2 select e.empno 3 , e.ename 4 , sum(c.duration) as days 5 from registrations r 6 join courses c on (c.code = r.course) 7 join employees e on (e.empno = r.attendee) 8 group by e.empno 9 , e.ename;
View created. SQL> SQL> select *
2 from course_days 3 where days > 10;
no rows selected SQL> SQL> SQL> drop table employees; Table dropped. SQL> SQL> drop table courses; Table dropped. SQL> SQL> drop table registrations; Table dropped. SQL> SQL></source>
Create a view to link two table together
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE employee (
2 id number, 3 name varchar(100), 4 birth_date date, 5 gender varchar2(30) );
Table created. SQL> SQL> CREATE TABLE employee_evaluation (
2 id number, 3 title varchar2(100), 4 written_date date );
Table created. SQL> SQL> SQL> CREATE OR REPLACE VIEW employee_employee_evaluation as
2 SELECT employee.id, 3 employee.name, 4 employee_evaluation.title, 5 employee_evaluation.written_date 6 FROM employee, 7 employee_evaluation 8 WHERE employee.id = employee_evaluation.id;
View created. SQL> SQL> drop table employee; Table dropped. SQL> SQL> drop table employee_evaluation; Table dropped.</source>
Create a view with condition
<source lang="sql">
SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> SQL> create or replace view dept20_v as
2 select * from employees where deptno=20;
View created. SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>
Creating a simple filtering view
<source lang="sql">
SQL> SQL> CREATE TABLE product_order (
2 product_name VARCHAR2(25), 3 salesperson VARCHAR2(3), 4 order_date DATE, 5 quantity NUMBER(4,2) 6 );
Table created. SQL> SQL> SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1); 1 row created. SQL> SQL> SQL> SQL> SELECT * FROM product_order; PRODUCT_NAME SAL ORDER_DAT QUANTITY
--- --------- ----------
Product 1 CA 14-JUL-03 1 Product 2 BB 14-JUL-03 75 Product 3 GA 14-JUL-03 2 Product 4 GA 15-JUL-03 8 Product 5 LB 15-JUL-03 20 Product 6 CA 16-JUL-03 5 Product 7 CA 17-JUL-03 1 7 rows selected. SQL> SQL> CREATE OR REPLACE VIEW sales_by_atlas_v AS
2 SELECT * FROM product_order WHERE salesperson = "CA";
View created. SQL> SQL> SELECT * FROM sales_by_atlas_v; PRODUCT_NAME SAL ORDER_DAT QUANTITY
--- --------- ----------
Product 1 CA 14-JUL-03 1 Product 6 CA 16-JUL-03 5 Product 7 CA 17-JUL-03 1 SQL> SQL> drop table product_order; Table dropped. SQL> SQL></source>
Creating a view that joins tables
<source lang="sql">
SQL> SQL> CREATE TABLE product_order (
2 product_name VARCHAR2(25), 3 salesperson VARCHAR2(3), 4 order_date DATE, 5 quantity NUMBER(4,2) 6 );
Table created. SQL> SQL> SQL> CREATE TABLE person (
2 person_code VARCHAR2(3) PRIMARY KEY, 3 first_name VARCHAR2(15), 4 last_name VARCHAR2(20), 5 hire_date DATE 6 );
Table created. SQL> SQL> SQL> INSERT INTO person VALUES ("CA", "Chase", "At", "01-FEB-02"); 1 row created. SQL> INSERT INTO person VALUES ("GA", "Gary", "Talor", "15-FEB-02"); 1 row created. SQL> INSERT INTO person VALUES ("BB", "Bob", "Bark", "28-FEB-02"); 1 row created. SQL> INSERT INTO person VALUES ("LB", "Laren", "Baby", "01-MAR-02"); 1 row created. SQL> INSERT INTO person VALUES ("LN", "Linda", "Norman", "01-JUN-03"); 1 row created. SQL> SQL> SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1); 1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE VIEW sales_per_person_v AS
2 SELECT pers.first_name || " " || pers.last_name SALESPERSON, 3 purc.product_name, 4 purc.order_date, 5 purc.quantity 6 FROM person pers, 7 product_order purc 8 WHERE pers.person_code = purc.salesperson (+);
View created. SQL> SQL> SELECT * FROM sales_per_person_v
2 ORDER BY salesperson, product_name, order_date;
SALESPERSON PRODUCT_NAME ORDER_DAT QUANTITY
------------------------- --------- ----------
Bob Bark Product 2 14-JUL-03 75 Chase At Product 1 14-JUL-03 1 Chase At Product 6 16-JUL-03 5 Chase At Product 7 17-JUL-03 1 Gary Talor Product 3 14-JUL-03 2 Gary Talor Product 4 15-JUL-03 8 Laren Baby Product 5 15-JUL-03 20 Linda Norman 8 rows selected. SQL> SQL> drop table product_order; Table dropped. SQL> drop table person; Table dropped.</source>
Query a filtering view
<source lang="sql">
SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> SQL> create or replace view dept20_v as
2 select * from employees where deptno=20;
View created. SQL> SQL> SQL> select * from dept20_v;
EMPNO last_name INIT JOB MGR BDATE MSAL DEPTNO
-------------------- ----- -------- ------ ---------- ------ ------
3 Jord T SALESREP 4 21-10-1967 1700 20
4 Mary J MANAGER 5 22-09-1968 1800 20
9 Yellow C DIRECTOR 10 27-04-1973 2300 20
3 rows selected. SQL> SQL> drop table employees; Table dropped. SQL></source>
Top "N" analysis
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25) PRIMARY KEY, 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE 6 );
Table created. SQL> SQL> INSERT INTO product VALUES ("Product 1", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 3", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 4", 25, 10000, null); 1 row created. SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04"); 1 row created. SQL> INSERT INTO product VALUES ("Product 6", 45, 1, TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M.")); 1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE VIEW overstocked_items AS
2 SELECT product_name, quantity_on_hand 3 FROM product 4 WHERE rownum <= 3 5 ORDER BY quantity_on_hand;
View created. SQL> SQL> SQL> SELECT * FROM overstocked_items; PRODUCT_NAME QUANTITY_ON_HAND
----------------
Product 1 1 Product 3 100 Product 2 1000 SQL> SQL> SQL> drop table product; Table dropped. SQL> SQL> drop view overstocked_items; View dropped. SQL></source>
Use view to simplify the query
<source lang="sql">
SQL> SQL> SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1)) ;
Table created. SQL> SQL> SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 ); 1 row created. SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 ); 1 row created. SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 ); 1 row created. SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 ); 1 row created. SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 ); 1 row created. SQL> insert into registrations values (9, "XML",date "2000-02-03",4 ); 1 row created. SQL> insert into registrations values (10,"XML",date "2000-02-03",5 ); 1 row created. SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL); 1 row created. SQL> SQL> SQL> SQL> create or replace view avg_evaluations as
2 select course 3 , avg(evaluation) as avg_eval 4 from registrations 5 group by course;
View created. SQL> SQL> SQL> select *
2 from avg_evaluations 3 where avg_eval >= 4;
COURSE AVG_EVAL
--------
OAU 5 XML 5 SQL 4 3 rows selected. SQL> SQL> SQL> SQL> select r.course
2 , avg(r.evaluation) as avg_eval 3 from registrations r 4 group by r.course 5 having avg(r.evaluation) >= 4;
COURSE AVG_EVAL
--------
OAU 5 XML 5 SQL 4 3 rows selected. SQL> SQL> drop table registrations; Table dropped. SQL></source>
Use view to wrap complex select statement
<source lang="sql">
SQL> SQL> create table employee
2 ( 3 empl_no integer primary key 4 ,lastname varchar2(20) not null 5 ,firstname varchar2(15) not null 6 ,midinit varchar2(1) 7 ,street varchar2(30) 8 ,city varchar2(20) 9 ,state varchar2(2) 10 ,zip varchar2(5) 11 ,zip_4 varchar2(4) 12 ,area_code varchar2(3) 13 ,phone varchar2(8) 14 ,company_name varchar2(50));
Table created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created. SQL> SQL> SQL> SQL> create view phone_list as
2 select firstname || " " || midinit || ". " || lastname as "Name",
3 "(" || area_code || ")" || phone as "Telephone#"
4 from employee;
SQL> SQL> drop view phone_list ; View dropped. SQL> SQL> drop table employee; Table dropped. SQL></source>
