script_65.txt

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

--在以后的练习中，我们经常要用到以下两个表，所以先讲解一下这两个表的结构及含意


 名称  产品库存表  :plsql101_product用于存放产品信息                               
 ---------------------------------------------------------------
 PRODUCT_NAME                           产品名称
 PRODUCT_PRICE                          产品单价
 QUANTITY_ON_HAND                       库存数
 LAST_STOCK_DATE                        最后进货日期

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


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

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


---------- tochar_date.txt ----------
/*
 * 范例名称：日期，时间转换为文本
 * 文件名称：tochar_date.txt
 */

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')
FROM DUAL;
--MM两位数字月

SELECT TO_CHAR(SYSDATE, 'Mon-DD-YYYY') from dual;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL;
--

--将最后一次进货时间用完整时间方式显示
select PRODUCT_NAME, 
       PRODUCT_PRICE, 
       QUANTITY_ON_HAND,
       to_char(LAST_STOCK_DATE, 'MON DD, YYYY HH24:MI')
from   PLSQL101_PRODUCT;

--MON为月份用本地语言表示,dd日期.




---------- to_char_number.txt ----------
/*
 * 范例名称：数字转换为文本
 * 文件名称：to_char_number.txt
 */

SELECT * FROM plsql101_product;

SELECT product_name, 
       TO_CHAR(product_price, '$9,999.00') "Price",
       quantity_on_hand,
       last_stock_date
FROM   plsql101_product;

-- 结果图 5-17
select PRODUCT_NAME, 
       to_char(PRODUCT_PRICE, '$9,999.00') "Price",
       to_char(QUANTITY_ON_HAND, '999,999') "On Hand",
       '  '|| to_char(LAST_STOCK_DATE, 'MON DD, YYYY') "Last Stocked"
from   PLSQL101_PRODUCT;




---------- sm_to_char_number.txt ----------
/*
 * 范例名称：数字转换为文本
 * 文件名称：sm_to_char_number.txt
 */

select TO_CHAR(199.99,'$99.99') FROM DUAL;
select TO_CHAR(1999.99,'$9,999.99') FROM DUAL;

select TO_CHAR(SALARY,'$999.99') FROM SM_EMP;
--ERR!纪录长度超出FORMAT CODE.
select TO_CHAR(SALARY,'$99999.99') FROM SM_EMP;



---------- sm_todate.txt ----------
/*
 * 范例名称：文本转换为日期
 * 文件名称：sm_todate.txt
 */

SELECT TO_DATE('2000/01/01','YYYY_MM_DD')  FROM DUAL;

SELECT TO_DATE('2000/01/01','YY_MM_DD')  FROM DUAL;

SELECT TO_DATE('2000/01/01','MM_DD_YY')  FROM DUAL;
--ERROR 位于第 1 行:
--ORA-01843: 无效的月份  年，月，日的位置要对应



---------- todate.txt ----------
/*
 * 范例名称：文本转换为日期
 * 文件名称：todate.txt
 */
 
SELECT product_name, 
       product_price,
       quantity_on_hand,
       TO_CHAR(last_stock_date, 'MM-DD-YYYY HH24:MI')
FROM   plsql101_product;

UPDATE plsql101_product
SET    last_stock_date 
= TO_DATE('2002_12_12 18:18:18',
          'yyyy_mm_dd HH24:mi:ss');
WHERE  product_name LIKE '%Zinc%';


UPDATE plsql101_product
SET    last_stock_date = 
TO_DATE('12月 31, 2002, 11:30 下午',
'Month dd, YYYY, HH:MI P.M.')
WHERE  product_name LIKE '%Zinc%';



SELECT product_name, 
       product_price,
       quantity_on_hand,
       TO_CHAR(last_stock_date, 'MM-DD-YYYY HH24:MI') 
FROM   plsql101_product; 
 --显示格式仍然为MM-DD-YYYY HH24:MI,但数据改变了
  
  
---------- decode.txt ----------
/*
 * 范例名称：DECODE函数
 * 文件名称：decode.txt
 */
--雇员部门信息表，
名称                   
-------------------
ID           雇员编号          
DEPTNO       部门编号          
DEPT         部门名称          

drop table sm_emp_dept;
create table sm_emp_dept (id char(10), deptno char(2),dept varchar2(20));
insert into sm_emp_dept(id,deptno)  values('001','10');
insert into sm_emp_dept(id,deptno)  values('002','20');
insert into sm_emp_dept(id,deptno)  values('003','10');
insert into sm_emp_dept(id,deptno)  values('007',null);
insert into sm_emp_dept(id,deptno)  values('008','80');


update sm_emp_dept set dept= decode 
(deptno , 10 , '财务',
          20 , '公关',
               '未知');      
	       
update sm_emp_dept set dept= decode 
(deptno , 10 , '财务',
          20 , '公关',
	  80  , '情报',
               '未知');   

--未知 相当于else.


---------- sm_nvl.txt ----------
/*
 * 范例名称：NVL函数
 * 文件名称：sm_nvl.txt
 */

INSERT INTO SM_EMP 
VALUES('0000000007','007',NULL,'62634567');

SELECT SALARY+100  NEWSALAY,NAME FROM SM_EMP;


SELECT NVL(SALARY,0)+100  NEWSALAY,NAME FROM SM_EMP;
--ok.0和SALARY都为数字。

SELECT NVL(SALARY,' null!')  NEWSALAY,NAME FROM SM_EMP;

SELECT NVL(SALARY,'NULL!')  NEWSALAY,NAME FROM SM_EMP;
--ERROR 位于第 1 行:
--ORA-01722: 无效数字
--NVL函数输入值与输出值必须一致

--一定要输出字符，用to_char
SELECT NVL(to_char(SALARY,'9999'),'NULL!')  NEWSALAY,NAME FROM SM_EMP;



---------- nvl.txt ----------
/*
 * 范例名称：NVL函数
 * 文件名称：nvl.txt
 */

SELECT product_name,
       last_stock_date
FROM   plsql101_product;

SELECT product_name,
       NVL(last_stock_date, '01-1月-2001') "Last Stocked"
FROM   plsql101_product;

SELECT product_name,
       NVL(last_stock_date, TRUNC(SYSDATE)) "Last Stocked"
FROM   plsql101_product;



---------- nvl_type.txt ----------
/*
 * 范例名称：NVL函数
 * 文件名称：nvl_type.txt
 */

-- This will fail, because the datatypes of 
-- the two arguments are different
SELECT product_name,
       NVL(last_stock_date, 'N/A') "Last Stocked"
FROM   plsql101_product;

-- This version accommodates the datatype difference by
-- placing a TO_CHAR function around the LAST_STOCK_DATE
SELECT product_name,
       NVL(TO_CHAR(last_stock_date), 'N/A') "Last Stocked"
FROM   plsql101_product;



select trunc(1001/1000,0) from dual;
select trunc(1999/1000,0) from dual;
select trunc(2001/1000,0) from dual;