script_68_new_bak.txt

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

                 )
;

--when create table
CREATE TABLE plsql101_product2 (
     product_name     VARCHAR2(25) PRIMARY KEY,
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE
     )
;

CREATE TABLE plsql101_product3 (
     product_name     VARCHAR2(25),
     product_price    NUMBER(4,2),
     quantity_on_hand NUMBER(5,0),
     last_stock_date  DATE,
     PRIMARY KEY (product_name)
     )
;



---------- foreign_key.txt ----------
/*
 * 范例名称：FOREIGN KEY
 * 文件名称：foreign_key.txt
 */

--必须首先执行primary_key.txt

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
          FOREIGN KEY (product_name) 
          REFERENCES plsql101_product;


--当REFERENCES 的表没有PK
--ERROR 位于第 4 行:
--ORA-02268: 引用的表不具有主关键字


--再次建立
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
          FOREIGN KEY (product_name) 
          REFERENCES plsql101_product;


--子表中有，父表中没有的纪录
select plsql101_product.product_name,plsql101_purchase.product_name
from plsql101_product,plsql101_purchase
where  plsql101_product.product_name(+)=plsql101_purchase.product_name

--删除子表孤儿纪录，再次建立
delete from plsql101_purchase where product_name='Round Snaphoo';

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
          FOREIGN KEY (product_name) 
          REFERENCES plsql101_product;
--ok


/***************************************************************
-- 练习: 也可以用update改变子表（plsql101_purchase）的值 creating the FK constraint

UPDATE plsql101_purchase
SET    product_name = 'Round Chrome Snaphoo'
WHERE  product_name = 'Round Snaphoo';

ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
          FOREIGN KEY (product_name) 
          REFERENCES plsql101_product;
**************************/


-- 练习: Attempting to enter a child record without a matching 
-- 插入子表(plsql101_purchase)
INSERT INTO plsql101_purchase VALUES (
     'Small Widgee', 'CA', '17-7月-03', 1)
;
--ERROR 位于第 1 行:
--ORA-02291: 违反完整约束条件 (SCOTT.PLSQL101_PURCHASE_FK_PRODUCT) - 未找到父项关键字
--为什么呢？Small Widgee与Small Widget不同

-- Corrected version

--子表insert的纪录必须满足fk约束，在父表对应字段有相应值
INSERT INTO plsql101_purchase VALUES (
     'Small Widget', 'CA', '17-7月-03', 1)
;

--
UPDATE plsql101_purchase
SET    product_name = 'Small Widget_11'
WHERE  product_name = 'Small Widget';


ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (SCOTT.PLSQL101_PURCHASE_FK_PRODUCT) - 未找到父项关键字


UPDATE plsql101_purchase
SET    product_name = 'Round Chrome Snaphoo'
WHERE  product_name = 'Small Widget';
--OK.select * from plsql101_PRODUCT,product_name字段有Round Chrome Snaphoo


-- 为plsql101_purchase建立FOREIGN KEY ,通过salesperson字段
--与plsql101_person建立关联
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_person
          FOREIGN KEY (salesperson)
          REFERENCES plsql101_person;



---------- 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
 */

--采购的商品，此商品的单价，采购总量，对应经手人是谁：
--
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,
       prod.product_price,
       purc.purchase_date,
       purc.quantity
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  prod.product_name = purc.product_name
ORDER BY prod.product_name;

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


----------------------------------------
--以下表达式是错误的！
SELECT prod.product_name,
       prod.product_price,
       purc.purchase_date,
       purc.quantity
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  (+) prod.product_name = purc.product_name 
ORDER BY prod.product_name;
--ERROR 位于第 7 行:
--ORA-00936: 缺少表达式

SELECT prod.product_name,
       prod.product_price,
       purc.purchase_date,
       purc.quantity
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE   prod.product_name (+)= purc.product_name (+)
ORDER BY prod.product_name;
--ERROR 位于第 7 行:
--ORA-01468: 一个谓词只能引用一个外部连接表


--练习：通过  人员信息    和    采购信息表    商品信息 三个表求出

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

--商品名称，此商品的单价，采购日期，数量,采购总花费 按采购总花费排序
SELECT prod.product_name,
       prod.product_price,
       purc.purchase_date,
       purc.quantity,
       purc.quantity * prod.product_price  采购总花费
FROM   plsql101_product  prod,
       plsql101_purchase purc
WHERE  prod.product_name = purc.product_name
ORDER BY (purc.quantity * prod.product_price) desc;