script_64_没使用shangpin表.txt

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

---------- sysdate_insert.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：sysdate_insert.txt
 */

INSERT INTO plsql101_purchase VALUES 
     ('Small Widget', 'SH', sysdate,10 );
--以SYSDATE:系统当前时间作为PURCHASE_DATE

INSERT INTO plsql101_purchase VALUES 
     ('Medium Wodget', 'SH', sysdate-14, 15);
--14天以前购买的
INSERT INTO plsql101_purchase VALUES 
     ('Round Snaphoo', 'SH', sysdate-7, 25);
INSERT INTO plsql101_purchase VALUES 
     ('Chrome Phoobar', 'SH', sysdate+7, 10);
--7天以后将购买的：什么涵义呢？
     
 
     
---------- sysdate_insert_select.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：sysdate_insert_select.txt
 */

SELECT * FROM plsql101_purchase 
WHERE  purchase_date BETWEEN (SYSDATE-30) AND SYSDATE;
--什么涵义呢？
DELETE FROM plsql101_purchase
WHERE  SALESPERSON = 'SH';



---------- round.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：round.txt
 */

SELECT product_name, product_price
FROM   plsql101_product;

SELECT product_name, ROUND(product_price, 0)
FROM   plsql101_product;

SELECT ROUND(1234.5678, 4) FROM DUAL;
SELECT ROUND(1234.5678, 3) FROM DUAL;
SELECT ROUND(1234.5678, 2) FROM DUAL;
SELECT ROUND(1234.5678, 1) FROM DUAL;
SELECT ROUND(1234.5678, 0) FROM DUAL;
SELECT ROUND(1234.5678, -1) FROM DUAL;
SELECT ROUND(1234.5678, -2) FROM DUAL;
SELECT ROUND(1234.5678, -3) FROM DUAL;

--round实用
--商品信息表练习.txt中
--求出各库存商品的利润率 = 利润 / 投资
select itemname , 
(sale_price * (1-tax)-purchase_price ) / (purchase_price)
from sm_itemlist;
--有何不足？利润率不需要太精确。如何处理？

select itemname , 
round((sale_price * (1-tax)-purchase_price ) / (purchase_price),2)
from sm_itemlist;


---------- TRUNC.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：TRUNC.txt
 */

SELECT TRUNC(1234.5678, 4) FROM DUAL;
SELECT TRUNC(1234.5678, 3) FROM DUAL;
SELECT TRUNC(1234.5678, 2) FROM DUAL;
SELECT TRUNC(1234.5678, 1) FROM DUAL;
SELECT TRUNC(1234.5678, 0) FROM DUAL;
SELECT TRUNC(1234.5678, -1) FROM DUAL;
SELECT TRUNC(1234.5678, -2) FROM DUAL;
SELECT TRUNC(1234.5678, -3) FROM DUAL;



---------- UPPER_LOWER_INITCAP.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：UPPER_LOWER_INITCAP.txt
 */
 
SELECT UPPER(product_name) FROM plsql101_product;
SELECT LOWER(product_name) FROM plsql101_product;
SELECT INITCAP(product_name) FROM plsql101_product;

SELECT INITCAP('this TEXT hAd UNpredictABLE caSE') FROM DUAL;

--数据准备

drop table shangpin;
--建立商品表
create table shangpin
(name varchar(20),
 price number(8,2),
 shuliang number(3));
--插入数据
insert into shangpin values ('TCL电视',100,3);
insert into shangpin values ('豆腐',1,30);
insert into shangpin values ('tcl电脑',1000,3);
insert into shangpin values ('Tcl电话',50,3);
insert into shangpin values ('Abb开关',1,30);


--显示所有TCL的商品。
--一种笨方法
select * from shangpin where 
name like '%TCL%' 
OR
name like '%Tcl%' 
OR
name like '%tcl%'; --如果严格，还要加多种排列组合。

select * from shangpin where upper(name) like '%TCL%';

--显示所有TCL的商品,并以大写TCL...显示
select UPPER(name), price,shuliang
from shangpin where upper(name) like '%TCL%';
--显示所有TCL的商品,并以大写Tcl...显示
select initcap(name), price,shuliang
from shangpin where upper(name) like '%TCL%';

--更新所有TCL产品为标准名称TCL...
update shangpin set name=UPPER(name)
where UPPER(name) like '%TCL%';


 
 
---------- LENGTH.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：LENGTH.txt
 */

 
SELECT   product_name, LENGTH(product_name) NAME_LENGTH
FROM     plsql101_product
WHERE    LENGTH(product_name) >15
ORDER BY product_name;


SELECT   name, LENGTH(name)
FROM     shangpin
ORDER BY LENGTH(name);
--注意：如果为中文数据，一个中文一个英文全被认为是1


---------- SUB_STRING.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：SUB_STRING.txt
 */

drop table PLSQL101_OLD_ITEM;

CREATE TABLE plsql101_old_item (
     item_id   CHAR(20),
     item_desc CHAR(25)
     )
;

INSERT INTO plsql101_old_item VALUES
     ('LA-101', 'Can, Small');
INSERT INTO plsql101_old_item VALUES
     ('LA-102', 'Can, Large');
INSERT INTO plsql101_old_item VALUES
     ('LA-103', 'Bottle, Small');
INSERT INTO plsql101_old_item VALUES
     ('LA-104', 'Bottle, Large');
INSERT INTO plsql101_old_item VALUES
     ('NY-101', 'Box, Small');
INSERT INTO plsql101_old_item VALUES
     ('NY-102', 'Box, Large');
INSERT INTO plsql101_old_item VALUES
     ('NY-103', 'Shipping Carton, Small');
INSERT INTO plsql101_old_item VALUES
     ('NY-104', 'Shipping Carton, Large');

--将一数据分开显示
SELECT SUBSTR(item_id, 1, 2) , --取第1，2个
       SUBSTR(item_id, 4, 3) , --取4，5，6个
       item_desc
FROM   plsql101_old_item
;
--
 
INSERT INTO plsql101_old_item VALUES
     ('NY-10444', 'Shipping Carton, Large');


-----开始
--从第4个起，全取出
SELECT SUBSTR(item_id, 1, 2) MFGR_LOCATION,
       SUBSTR(item_id, 4, LENGTH(item_id)) ITEM_NUMBER,
       item_desc
FROM   plsql101_old_item
;

---------- INSTR.txt ----------
---
---开始
/*
 * 范例名称：系统变量函数
 * 文件名称：INSTR.txt
 */
--解释：补充3.ppt
SELECT item_desc FROM   plsql101_old_item;
--为了将物品的单位，和对它的描述分开
--需要将old_item表的item_desc，从，进行分解，此时必须用INSTR定位逗号

--先定位，
SELECT item_desc, 
       INSTR(item_desc,',',1) 
FROM   plsql101_old_item;

--逗号以前的取出，取到逗号前一个
SELECT item_desc,
       SUBSTR(item_desc, 1, INSTR(item_desc,',',1)-1)
FROM   plsql101_old_item;

--逗号以后的取出，逗号和空格=+2，99为全取出
SELECT item_desc,
       SUBSTR(item_desc,INSTR(item_desc,',',1) +2,99)
FROM   plsql101_old_item;

--更好的方法
SELECT item_desc,
       SUBSTR(item_desc,INSTR(item_desc,',',1) +2,LENGTH(item_desc)-INSTR(item_desc,',',1)-2)
FROM   plsql101_old_item;

SELECT item_desc,
       SUBSTR(item_desc,INSTR(item_desc,',',1) +2,LENGTH(item_desc)-INSTR(item_desc,',',1)-2)
FROM   plsql101_old_item;
--err!此处当是-1.因为INSTR(item_desc,',',1)已经是,的位置。后面只有一个空格。
--这里没现出来是因为是char,length全为最长。如为varchar2会少一个。


--分别取出两段纪录
SELECT item_desc,
       SUBSTR(item_desc, 1,INSTR(item_desc,',',1) -1) ,
       SUBSTR(item_desc,INSTR(item_desc,',',1) +2,99) 
FROM   plsql101_old_item;

--练习要求
drop table shangpin;
--建立商品表
create table shangpin
(name varchar(20),
 price number(8,2),
 shuliang number(3));

--插入数据,商品名为 厂家,产品
insert into shangpin values ('TCL,电视',100,3);
insert into shangpin values ('三元,豆腐',1,30);
insert into shangpin values ('tcl,电脑',1000,3);
insert into shangpin values ('Tcl,电话',50,3);
insert into shangpin values ('Abb,开关',1,30);
insert into shangpin values ('Abb,电脑',100,30);

--将厂家与产品分别显示
--将所有含字母的厂家名大写



---------- TRIM.txt ----------
/*
 * 范例名称：系统变量函数
 * 文件名称：TRIM.txt
 */
select * from plsql101_old_item;
--想将item的名称和描述连接起来，问题：
--显示结果含大量空格，分析原因：是 CHAR,VARCHAR2
SELECT 'Item  ' || 
       item_id ||
       ' is described as a ' ||
       item_desc        
FROM   plsql101_old_item;


SELECT 'Item  ' || 
       RTRIM(item_id) ||
       ' is described as a ' ||
       RTRIM(item_desc)
FROM   plsql101_old_item;



---------- sysdate_err.txt ----------
/*
 * 范例名称：日期函数
 * 文件名称：sysdate_err.txt
 */

INSERT INTO plsql101_product VALUES 
     ('Square Zinculator', 45, 1, SYSDATE);

SELECT * FROM plsql101_product;


-- 当天日期输入为测试日期
SELECT * FROM plsql101_product
WHERE  last_stock_date = '当天日期';
如：
SELECT * FROM plsql101_product
WHERE  last_stock_date = '29-11月-01';


SELECT * FROM plsql101_product
WHERE  last_stock_date = '28-11月-00';


---------- sysdate_trunc.txt ----------
/*
 * 范例名称：日期函数
 * 文件名称：sysdate_trunc.txt
 */
 
-- 当前日期为标准格式
SELECT * FROM plsql101_product
WHERE  TRUNC(last_stock_date) = '当前日期';

--TRUNC(last_stock_date,0)对不对?

DELETE FROM plsql101_product 
WHERE  product_name = 'Square Zinculator';

--方法2：在插入时就避免
INSERT INTO plsql101_product VALUES 
     ('Square Zinculator', 45, 1, trunc(sysdate));
 
-- 当前日期为标准格式
SELECT * FROM plsql101_product
WHERE  last_stock_date = '当前日期';