script_68.txt

orale培训教材包括了所有的sql说明和实例

     last_stock_date  DATE,
     PRIMARY KEY (product_name)
     )
;
--insert into plsql101_product3 values('beer',10,10,TO_DATE('2002/02/02','YYYY/MM/DD'));

drop table plsql101_product4;
CREATE TABLE plsql101_product4 (
     product_name     VARCHAR2(25),
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE,
     PRIMARY KEY (product_name,last_stock_date)
     )
;
trunc(sysdate)
--多字段组成primary key

insert into plsql101_product4 values
('beer',10,10,TO_DATE('2002/02/02','YYYY/MM/DD'));
insert into plsql101_product4 values('beer',10,10,TO_DATE('2002/02/02','YYYY/MM/DD'));
insert into plsql101_product4 values('beer',10,10,TO_DATE('2002/02/01','YYYY/MM/DD'));


--首先需运行insert_data.sql准备数据
--@c:\insert_data.sql

ALTER TABLE plsql101_product
ADD PRIMARY KEY (product_name);

ALTER TABLE plsql101_person
ADD PRIMARY KEY (person_code);

ALTER TABLE plsql101_purchase
ADD PRIMARY KEY (product_name,
                 salesperson,
                 purchase_date
                 )
;





---------- minzu.txt ----------
/*
 * 范例名称：FOREIGN KEY
 * 文件名称：minzu.txt
 */
--建立 民族和 人之间的关联关系 minzu表，man表
-- 测试父子表的dml
--父表insert
--son insert ,update,delete
--father delete ,update

drop table minzu;
drop table man;
create table minzu
(mincheng  varchar2(2) primary key);

create table man 
(name varchar2(10),
minzu varchar2(2),
constraint fk_minzu foreign key(minzu) references minzu(mincheng));

--父表插入纪录
insert into minzu values('汉');
insert into minzu values('满');
insert into minzu values('');

--插入子表---------------------------------------------------------- 
--练习: Attempting to enter a child record without a matching:向字表插入与父表不匹配的数据 

insert into man values('张飞','燕');
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (SCOTT.FK_MINZU) - 未找到父项关键字

insert into man values('张飞','汉');
--ok
--结论：子表insert的纪录必须满足fk约束，
在父表对应字段有相应值

--更新子表-------------------------------------------------------- 
update man set minzu='燕' where name='张飞';
--ERROR 位于第 1 行:
--ORA-02291: 违反完整约束条件 (SCOTT.PLSQL101_PURCHASE_FK_PRODUCT) - 未找到父项关键字

update man set minzu = '满' where name='张飞';
--ok
rollback;
--结论：子表update的纪录必须满足fk约束，
在父表对应字段有相应值

--删除子表纪录-----------------------------------------------------
delete from man;
rollback;
--结论：

--删除父表纪录-----------------------------------------------------
delete from minzu where mincheng = '满';
delete from minzu where mincheng = '汉';
--结论：没son的可del

--更新父表---------------------------------------------------------
update minzu set mincheng = '蒙';
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C001343)
update minzu set mincheng = '蒙' where mincheng='满';
update minzu set mincheng = '蒙' where mincheng='汉';
--结论：没son的可改。

--father delete

--总之：不可以出现孤儿！







-------------------------join以下为多表查询案例--------------------------------------
delete from man;
delete from minzu;
commit;



drop table man;

drop table minzu;

--在民族表增加人口总数字段:rekou  
create table minzu
(mincheng  varchar2(2) primary key,
renkou number(10,0));

insert into minzu values('汉',100000);
insert into minzu values('满',100);
--man表无这条纪录的对应数据
insert into minzu values('蒙',100);


--注意：此时不使用外键
create table man 
(name varchar2(10),
minzu varchar2(2));

insert into man values('张飞','汉');
insert into man values('康熙','满');
--民族表无这条纪录的对应数据
insert into man values('张飞2','燕');
commit;

--既要求得到人的姓名，民族，又要知道对应，相应民族的总人口数。一个连结
select man.name,man.minzu ,minzu.mincheng ,minzu.renkou
from man,minzu
where man.minzu = minzu.mincheng;

--简便的写法. 在字段前不加表名
select name ,minzu,  mincheng ,renkou
from man,minzu
where man.minzu = minzu.mincheng;

--结果分析
NAME       MI MI     RENKOU
---------- -- -- ----------
张飞       汉 汉     100000
康熙       满 满        100


--多表查询的笛卡尔积
select man.name,man.minzu ,minzu.mincheng ,minzu.renkou
from man,minzu order by man.name;

--outer_join:外连结：此时取出所有minzu纪录，man 表中没有的纪录补null
--(+)在哪边，哪边补null
select man.name ,minzu.mincheng,minzu.renkou
from man, minzu
where man.minzu(+)=minzu.mincheng


select man.name ,minzu.mincheng,minzu.renkou
from man, minzu
where man.minzu=minzu.mincheng(+);



---------- multi_table.txt ----------
/*
 * 范例名称：多表查询
 * 文件名称：multi_table.txt
 */

--重新生成数据,运行insert_data.txt
名称   plsql101_product 商品信息表                 
--------------------
PRODUCT_NAME         商品名称  
PRODUCT_PRICE        商品单价  
QUANTITY_ON_HAND     商品库存  
LAST_STOCK_DATE      商品最后采购日期  

名称      采购信息表plsql101_purchase                     
----------------------------
PRODUCT_NAME        商品名称           
SALESPERSON         经手人(编号)           
PURCHASE_DATE       采购日期           
QUANTITY            采购数量           

 名称   人员表plsql101_person                
 --------------------
 PERSON_CODE        人员编号    
 FIRST_NAME         姓   
 LAST_NAME          名    
 HIRE_DATE          雇佣日期    

--查询采购信息和对应经手人的各人信息：图形分析：补充3_product.ppt
--不仅要知道采购信息，还要知道相应采购人的信息。

SELECT plsql101_purchase.product_name,
       plsql101_person.last_name,
       plsql101_person.first_name,
       plsql101_purchase.quantity
FROM   plsql101_purchase,
       plsql101_person
WHERE  plsql101_person.person_code = plsql101_purchase.salesperson
; 
  
 

  
---------- multi_table_j.txt ----------
/*
 * 范例名称：多表查询的笛卡尔积
 * 文件名称：multi_table_j.txt
 */

SELECT plsql101_purchase.product_name,
       plsql101_person.last_name,
       plsql101_person.first_name,
       plsql101_purchase.quantity
FROM   plsql101_purchase,
       plsql101_person
;



---------- join.txt ----------
/*
 * 范例名称：JOIN
 * 文件名称：join.txt
 */

--采购的商品，此商品的单价，采购总量，对应经手人是谁：
--图形分析：补充3_product.ppt中的三表图

SELECT plsql101_purchase.product_name,
       plsql101_product.product_price,
       plsql101_purchase.quantity,
       plsql101_person.last_name
FROM   plsql101_product,
       plsql101_person,
       plsql101_purchase
WHERE  plsql101_product.product_name = plsql101_purchase.product_name
       and
       plsql101_person.person_code = plsql101_purchase.salesperson
;

-- 采购日期，数量，商品最后（上次）采购日期，对应经手人是谁：
SELECT plsql101_purchase.purchase_date,
       plsql101_purchase.quantity,
       plsql101_product.last_stock_date,
       plsql101_person.last_name
FROM   plsql101_product,
       plsql101_person,
       plsql101_purchase
WHERE  plsql101_product.product_name = plsql101_purchase.product_name
       and
       plsql101_person.person_code = plsql101_purchase.salesperson
;



---------- outer_join.txt ----------
/*
 * 范例名称：外部连接outer join
 * 文件名称：outer_join.txt
 */

SELECT product_name FROM plsql101_product ORDER BY product_name;

--注意：可以在from 语句中为表名 指定别名。
--在select,where中 用  别名.字段名 代替 表名.字段名
--如： plsql101_product  prod  ：为plsql101_product表指定别名为prod

--商品名称，此商品的单价，采购日期，数量：采购的详细信息
SELECT prod.product_name,
      product_price,
       purchase_date,
      quantity
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  prod.product_name = purc.product_name
ORDER BY prod.product_name;

--注意：(+)在字段后面:
--商业含义：商品名称，此商品的单价，采购日期，数量 
--以及在商品表中，但从未采购（到）过的商品
SELECT prod.product_name,
       product_price,
       purchase_date,
       quantity
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  prod.product_name = purc.product_name (+)
ORDER BY prod.product_name;


--Round Chrome Snaphoo和Square Zinculator只在product中有，
--在purchase中无，用为outer join

--查看已采购未入库的商品
SELECT prod.product_name 已入库货物,
       product_price,
       purchase_date,
       quantity,purc.product_name 采购货物
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  prod.product_name(+) = purc.product_name 
ORDER BY prod.product_name