script_63.txt

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

--首先解释 使用的表的含义：product_purchase表的说明.txt

---------- data_insert.txt ----------
/*
 * 范例名称：为以后测试INSERT DATA
 * 文件名称：data_insert.txt
 */
  
DROP TABLE plsql101_product;

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

INSERT INTO plsql101_product VALUES 
     ('Small Widget', 99, 1, '15-1月-03');

INSERT INTO plsql101_product VALUES 
     ('Medium Wodget', 75, 1000, '15-1月-02');
INSERT INTO plsql101_product VALUES 
     ('Medium Wo1dget', 75, 1000, '15-1月-02');
INSERT INTO plsql101_product VALUES 
     ('Medium Wo12dget', 75, 1000, '15-1月-02');


INSERT INTO plsql101_product VALUES 
     ('Chrome Phoobar', 50, 100, '15-1月-03');

--日期插入NULL,注意测试结果
INSERT INTO plsql101_product VALUES 
     ('NULL INSERTED!', 25, 10000, null);
     
--共插入3条1000的，1条1，1条100，1条10000     

---------- FIELD_Number1.txt ----------
/*
 * 范例名称：基于数值字段的WHERE子句
 * 文件名称：FIELD_Number1.txt
 */
 

--测试!<,!>
SELECT * FROM plsql101_product 
WHERE  quantity_on_hand !< 500;
--ERROR 位于第 2 行:
--ORA-00927: 缺少等号
SELECT * FROM plsql101_product 
WHERE  quantity_on_hand !> 500;
--


SELECT * FROM plsql101_product
WHERE  quantity_on_hand = 1;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand < 500;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand < 1000;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand <= 1000;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand > 1000;




SELECT * FROM plsql101_product 
WHERE  quantity_on_hand >= 1000;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand > 1000;


--SELECT * FROM plsql101_product 
--WHERE  quantity_on_hand >= 1000;

--not equal
SELECT * FROM plsql101_product 
WHERE  quantity_on_hand != 1000;



---------- FIELD_Number_and.txt ----------
/*
 * 范例名称：基于多数值字段的WHERE子句
 * 文件名称：FIELD_Number_and.txt
 */
 
SELECT * FROM plsql101_product 
WHERE  quantity_on_hand >= 100 AND product_price>20 ;

SELECT * FROM plsql101_product 
WHERE  quantity_on_hand >= 100 AND product_price>30 ;


SELECT * FROM plsql101_product 
WHERE  quantity_on_hand = 100 OR product_price=30 ;



---------- FIELD_Number_rang.txt ----------
/*
 * 范例名称：基于多数值字段的WHERE子句--排除记录
 * 文件名称：FIELD_Number_rang.txt
 */
 
SELECT * FROM plsql101_product  
WHERE product_price>=50  AND  produc_price<=100;

--等价与上一句
SELECT * FROM plsql101_product 
WHERE product_price BETWEEN 50 AND 100;
--大家可以将50，100换过来试试。结果有些意外。

SELECT * FROM plsql101_product 
WHERE product_price BETWEEN 100 AND 50;


SELECT * FROM plsql101_product 
WHERE product_price<50   OR product_price>100;

--等价与上一句
SELECT * FROM plsql101_product 
WHERE product_price NOT  BETWEEN 50 AND 100;


SELECT * FROM plsql101_product 
WHERE product_price<>99;

 
SELECT * FROM plsql101_product 
WHERE product_price!=99;



---------- FIELD_Number_IN.txt ----------
/*
 * 范例名称：基于多数值字段的WHERE子句--基于一组可接受的值
 * 文件名称：FIELD_Number_IN.txt
 */

SELECT * FROM plsql101_product
WHERE  product_price IN (50, 99);



---------- FIELD_TEXT1.txt ----------
/*
 * 范例名称：基于文本字段的WHERE子句
 * 文件名称：FIELD_TEXT1.txt
 */
 
 SELECT * FROM plsql101_product
WHERE  product_name = 'Small Widget';

SELECT * FROM plsql101_product
WHERE  product_name IN ('Small Widget', 'Round Chrome Snaphoo');



---------- FIELD_TEXT_LIKE.txt ----------
/*
 * 范例名称：检验LIKE句的通配符
 * 文件名称：FIELD_TEXT_LIKE.txt
 */
 
SELECT * FROM plsql101_product
WHERE  product_name LIKE 'Chrome%';

--任何个字符，也可以是0个字符。
SELECT * FROM plsql101_product
WHERE  product_name LIKE '%Chrome%';

SELECT * FROM plsql101_product
WHERE  product_name LIKE '%W_d%';

--%:任何个字符，也可以是0个字符。_:必须且只能有一位
INSERT INTO plsql101_product VALUES 
     ('Wd', 25, 1, null);

INSERT INTO plsql101_product VALUES 
     ('W1d', 25, 1, null);
INSERT INTO plsql101_product VALUES 
     ('W12d', 25, 1, null);
--再执行一遍
SELECT * FROM plsql101_product
WHERE  product_name LIKE '%W_d%';

--一个_代表一个汉字或一个英文字母
INSERT INTO plsql101_product VALUES 
     ('W我d', 25, 1, null);

---------- FIELD_DATE.txt ----------
/*
 * 范例名称：日期在where子句中
 * 文件名称：FIELD_DATE.txt
 */
SELECT * FROM plsql101_product
WHERE  last_stock_date = '15-1月-03';

SELECT * FROM plsql101_product
WHERE  last_stock_date = '15-1月-2003';
--也OK

SELECT * FROM plsql101_product
WHERE  last_stock_date = '15-1月-2003年';
--ERROR 位于第 2 行:
--ORA-01830: 日期格式图片在转换整个输入字符串之前结束

----日期曾插入NULL,注意测试结果
--INSERT INTO plsql101_product VALUES ('NULL INSERTED!', 25, 10000, null);

SELECT * FROM plsql101_product
WHERE  last_stock_date > '31-12月-02';

SELECT * FROM plsql101_product
WHERE  last_stock_date BETWEEN '01-1月-03' and '31-12月-03';

SELECT * FROM plsql101_product
WHERE  last_stock_date NOT BETWEEN '01-1月-03' and '31-12月-03';



---------- FIELD_DATE_NULL.txt ----------
/*
 * 范例名称：NULL的比较
 * 文件名称：FIELD_DATE_NULL.txt
 */
SELECT * FROM plsql101_product
WHERE  last_stock_date > '31-12月-02';

SELECT * FROM plsql101_product
WHERE  last_stock_date < '31-12月-02';
--NULL都不会显示出，为什么？

--如何取出NULL?

SELECT * FROM plsql101_product
WHERE  last_stock_date = NULL;

SELECT * FROM plsql101_product
WHERE  last_stock_date != NULL;


SELECT * FROM plsql101_product
WHERE  last_stock_date IS NULL;

SELECT * FROM plsql101_product
WHERE  last_stock_date IS NOT NULL;



---------- FIELD_ORDERBY.txt ----------
/*
 * 范例名称：排序
 * 文件名称：FIELD_ORDERBY.txt
 */
 
SELECT * FROM plsql101_product ORDER BY product_price asc;
SELECT * FROM plsql101_product ORDER BY quantity_on_hand;

SELECT * FROM plsql101_product 
ORDER BY last_stock_date asc, product_name asc;

SELECT   last_stock_date, 
         product_name, 
         product_price, 
         quantity_on_hand
FROM     plsql101_product
ORDER BY last_stock_date, 
         product_name
;

SELECT * FROM plsql101_product ORDER BY product_price DESC;



SELECT product_name FROM plsql101_product ORDER BY quantity_on_hand;


--升序

SELECT * FROM plsql101_product ORDER BY product_price ASC;

--字段为文本按ASC码排大小
SELECT * FROM plsql101_product ORDER BY product_name ASC;



---------- FIELD_DISTINCT.txt ----------
/*
 * 范例名称：消除重复纪录
 * 文件名称：FIELD_DISTINCT.txt
 */
 
DROP TABLE plsql101_purchase;

CREATE TABLE plsql101_purchase ( 
     product_name  VARCHAR2(25), 
     quantity      NUMBER(4,2),
     purchase_date DATE, 
     salesperson   VARCHAR2(3)
     )
;

INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 1, '14-7月-03', 'CA');
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 75, '14-7月-03', 'BB');
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 2, '14-7月-03', 'GA');
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 8, '15-7月-03', 'GA');
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 20, '15-7月-03', 'LB');
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 2, '16-7月-03', 'CA');
INSERT INTO plsql101_purchase VALUES 
     ('Round Snaphoo', 25, '16-7月-03', 'LB');
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 2, '17-7月-03', 'BB');




--重复的产品被去掉
SELECT DISTINCT product_name 
FROM            plsql101_purchase
ORDER BY        product_name;



--重复的经手人被去掉
SELECT DISTINCT salesperson
FROM            plsql101_purchase
WHERE           purchase_date BETWEEN '01-7月-03' AND '15-7月-03'
ORDER BY        salesperson; 




---------- UPDATE_1.txt ----------
/*
 * 范例名称：更改表中的数据
 * 文件名称：UPDATE_1.txt
 */

SELECT * FROM plsql101_purchase;

UPDATE plsql101_purchase
SET    product_name = 'Large Widget'
WHERE  product_name = 'Small Widget';

UPDATE plsql101_purchase
SET    product_name = 'Large Widget' ,salesperson='11'
WHERE  product_name = 'Small Widget';

SELECT * FROM plsql101_purchase; 



---------- DELETE.txt ----------
/*
 * 范例名称：删除表中的记录	
 * 文件名称：DELETE_1.txt
 */

SELECT * FROM plsql101_purchase;

DELETE FROM plsql101_purchase
WHERE purchase_date > '15-7月-03';

SELECT * FROM plsql101_purchase;


DELETE FROM plsql101_purchase
WHERE product_name = 'Small Widget';

SELECT * FROM plsql101_purchase;

---------- DELETE_ROLLBACK.txt ----------
/*
 * 范例名称：DELETE,ROLLBACK的使用	
 * 文件名称：DELETE_ROLLBACK.txt
 */
commit;
SELECT * FROM plsql101_purchase;

DELETE FROM plsql101_purchase;

ROLLBACK;

SELECT * FROM plsql101_purchase;



---------- TRUNCATE_ROLLBACK.txt ----------
/*
 * 范例名称：TRUNCATE删除表中的记录	
 * 文件名称：TRUNCATE_ROLLBACK.txt
 */

SELECT * FROM plsql101_purchase;

TRUNCATE TABLE plsql101_purchase;

ROLLBACK;

SELECT * FROM plsql101_purchase;



---------- ROLLBACK_SAVEPOINT.txt ----------
/*
 * 范例名称：事务处理	
 * 文件名称：ROLLBACK_SAVEPOINT.txt
 */

--具体参见savepoint.ppt
 
SELECT * FROM plsql101_purchase;


INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 1, '14-7月-03', 'CA');
SAVEPOINT a;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 75, '14-7月-03', 'BB');
SAVEPOINT sp_2;
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 2, '14-7月-03', 'GA');
SAVEPOINT third;
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 8, '15-7月-03', 'GA');
SAVEPOINT final_sp;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 20, '15-7月-03', 'LB');
SELECT * FROM plsql101_purchase;
ROLLBACK TO final_sp;
SELECT * FROM plsql101_purchase;
ROLLBACK TO third;
SELECT * FROM plsql101_purchase;
ROLLBACK TO sp_2;
SELECT * FROM plsql101_purchase;
ROLLBACK TO a;
SELECT * FROM plsql101_purchase;
ROLLBACK;
SELECT * FROM plsql101_purchase;



---------- COMMIT_SAVEPOINT.txt ----------
/*
 * 范例名称：事务处理	
 * 文件名称：COMMIT_SAVEPOINT.txt
 */
 
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 1, '14-7月-03', 'CA');
SAVEPOINT A;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 75, '14-7月-03', 'BB');
SAVEPOINT B;
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 2, '14-7月-03', 'GA');
SAVEPOINT C;
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 8, '15-7月-03', 'GA');
SAVEPOINT D;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 20, '15-7月-03', 'LB');

COMMIT;

--结果如何呢？
ROLLBACK TO D;

SELECT * FROM plsql101_purchase;



---------- COMMIT_ROLLBACK_TO.txt ----------
/*
 * 范例名称：事务处理	
 * 文件名称：COMMIT_ROLLBACK_TO.txt
 */

DELETE FROM plsql101_purchase;
COMMIT;

INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 1, '14-7月-03', 'CA');
SAVEPOINT A;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 75, '14-7月-03', 'BB');
SAVEPOINT B;
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 2, '14-7月-03', 'GA');
SAVEPOINT C;
INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 8, '15-7月-03', 'GA');
SAVEPOINT D;
INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 20, '15-7月-03', 'LB');

ROLLBACK TO D;

COMMIT;

SELECT * FROM plsql101_purchase;



---------- date_share.txt ----------
/*
 * 范例名称：事务处理	
 * 文件名称：data_share.txt
 */

--窗口a
DELETE FROM plsql101_purchase;
select * from plsql101_purchase;



--another sql*pluse 窗口b  connect
select * from plsql101_purchase;

--窗口a
commit;

--another sql*pluse 窗口b  connect
select * from plsql101_purchase;


---------- 事务处理的终止.txt ----------
/*
 * 范例名称：事务处理的终止	
 * 文件名称：事务处理的终止.txt
 */

DESC SM_EMP;

insert into SM_EMP values('200000001','曹操',100,'62613546');
commit;
insert into SM_EMP values('300000001','孙权',100,'62613546');

create table sm_test( name varchar2(20));

rollback;
--此时结果如何？
select * from sm_emp;