?? script_63.txt
字號:
--首先解釋 使用的表的含義：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;?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -