script_64_bak.txt

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

---------- 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 
     ('Chrome Phoobar', 50, 100, '15-1月-03');
INSERT INTO plsql101_product VALUES 
     ('Round Chrome Snaphoo', 25, 10000, null);

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

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

---------- sm_sqlplus_edit.txt ----------
/*
 * 范例名称：使用文本编辑器
 * 文件名称：sm_sqlplus_edit.txt
 */
 
select * from su_emp

--ERROR 位于第 1 行:
--ORA-00942: 表或视图不存在

edit

/



---------- sqlplus_edit.txt ----------
/*
 * 范例名称：使用文本编辑器,edit语句
 * 文件名称：sqlplus_edit.txt
 */

-- Note: 必须在datainsert.txt中的sql执行之后
SELECT   product_nmae
FROM     plsql101_produtc
WHERE    quantity_on_hand >= 100
         AND
         last_stock_date IS NOT NULL
ORDER BY product_name;

--ERROR 位于第 2 行:
--ORA-00942: 表或视图不存在



---------- sm_sqlplus_change1.txt ----------
/*
 * 范例名称：CHANGE语句
 * 文件名称：sm_sqlplus_change1.txt
 */
 
select * from su_emp;

ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> change/su
  1* select * from _emp
SQL> /
select * from _emp
              *
ERROR 位于第 1 行:
ORA-00911: 无效字符

SQL> c/_/sm_/
  1* select * from sm_emp
SQL> /

EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
000000001  张飞              100 62613546



---------- sqlplus_change1.txt ----------
/*
 * 范例名称：CHANGE语句
 * 文件名称：sqlplus_change1.txt
 */

SELECT product_nmae FROM plsql101_product;

change/nmae/name
/



---------- sm_sqlplus_change2.txt ----------
/*
 * 范例名称：CHANGE语句
 * 文件名称：sm_sqlplus_change2.txt
 */
 
--在sm_dataInsert.txt中的sql执行之后执行
select * from su_emp
where salare>100;

1

c/su/sm

2
c/salare/salary

/

结果:
EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
000000003  刘备              150 62613547



---------- sm_sqlplus_changeErr.txt ----------
/*
 * 范例名称：CHANGE语句
 * 文件名称：sqlplus_changeErr.txt
 */

SQL> select * from su_emp
  2  where salare>100;
select * from su_emp
              *
ERROR 位于第 1 行:
ORA-00942: 表或视图不存在

SQL> 1
  1* select * from su_emp
SQL> c/su/sm
  1* select * from sm_emp
SQL> 2
  2* where salare>100
SQL> c/re/ry
  2* whery salare>100
SQL> /
whery salare>100
      *
ERROR 位于第 2 行:
ORA-00933: SQL 命令未正确结束



---------- sm_sqlplus_setEnv.txt ----------
/*
 * 范例名称：定制SQL*Plus环境
 * 文件名称：sm_sqlplus_setEnv.txt
 */

select * from user_tables;

SET LINESIZE 1000
SET PAGESIZE 9999

select * from user_tables;



---------- sqlplus_env_store.txt ----------
/*
 * 范例名称：保存定制SQL*Plus环境
 * 文件名称：sqlplus_env_store.txt
 */

select * from user_tables;

SET LINESIZE 1000
SET PAGESIZE 9999

select * from user_tables;

store set d:\oracle\ora81\sqlplus\admin\glogin.sql  append
--close this sql*plus

--open another sql*plus
select * from user_tables;



---------- sm_sqlplus_column.txt ----------
/*
 * 范例名称：在SQL*Plus中格式化输出结果
 * 文件名称：sm_sqlplus_column.txt
 */
 
update sm_emp set salary=10000
where  empid='0000000003';

--按小数点对齐

SELECT * FROM sm_emp;

COLUMN salary FORMAT 9999.99

SELECT * FROM sm_emp;

--当9999.99时，若纪录〉9999则显示不出。
COLUMN salary FORMAT 99999.99
SELECT * FROM sm_emp;

-- 增加组分隔符
COLUMN salary FORMAT 99,999

SELECT * FROM sm_emp;

-- 货币符号
COLUMN  salary  FORMAT $99.99

SELECT * FROM sm_emp;

--当9999.99时，若纪录〉99则显示不出。
COLUMN  salary  FORMAT $99999.99

SELECT * FROM sm_emp;

COLUMN  salary  FORMAT L99999.99

SELECT * FROM sm_emp;



---------- sqlplus_column.txt ----------
/*
 * 范例名称：在SQL*Plus中格式化输出结果
 * 文件名称：sqlplus_column.txt
 */
 
INSERT INTO plsql101_product VALUES (
     'Extra Huge Mega Phoobar +', 
     9.95, 
     1234, 
     '15-1月-04')
;

--按小数点对齐

SELECT * FROM plsql101_product;

COLUMN product_price FORMAT 9999.99

SELECT * FROM plsql101_product;


-- 增加组分隔符
COLUMN quantity_on_hand FORMAT 99,999

SELECT * FROM plsql101_product;


-- 货币符号
COLUMN product_price FORMAT $99.99

SELECT * FROM plsql101_product;



---------- sqlplus_column_text.txt ----------
/*
 * 范例名称：在SQL*Plus中格式化输出结果--文本
 * 文件名称：sqlplus_column_text.txt
 */
 
SELECT * FROM plsql101_product;

COLUMN product_name FORMAT A10 WORD_WRAP

SELECT * FROM plsql101_product;


COLUMN product_name FORMAT A5 WORD_WRAP;
SELECT * FROM plsql101_product;


COLUMN product_name FORMAT A100 WORD_WRAP;
SELECT * FROM plsql101_product;


--CHANGE  对SQL*PLUS专有指令无作用
SQL>  COLUMN product_name FORMAT A100 WORD_WARP;
SP2-0158: 未知的COLUMN选项"WORD_WARP"
SQL> C/WARP/WRAP
SP2-0023: 未找到字符串



---------- sm_sqlplus_column_all.txt ----------
/*
 * 范例名称：在SQL*Plus中格式化输出结果
 * 文件名称：sm_sqlplus_column_all.txt
 */
 
 --heading 
select * from sm_emp;

COLUMN empid HEADING '雇员|编号' JUSTIFY CENTER

select * from sm_emp;

-- 综合运用
select * from sm_emp;

COLUMN empid HEADING '雇员|编号' JUSTIFY CENTER
COLUMN name  FORMAT A2 WORD_WRAP HEADING '姓|名' JUSTIFY CENTER

COLUMN salary FORMAT L99999.99 HEADING '薪水' JUSTIFY RIGHT
COLUMN TelNo HEADING '电话' JUSTIFY RIGHT

select * from sm_emp;



---------- sm_sqlplus_column_close.txt ----------
/*
 * 范例名称：在SQL*Plus中格式化输出结果
 * 文件名称：sm_sqlplus_column_close.txt
 */

--CLOSE COLUMN

SELECT * FROM sm_emp;
COLUMN  salary OFF
SELECT * FROM sm_emp;

COLUMN salary FORMAT L99999.99 HEADING '薪水' JUSTIFY RIGHT

--COLUMN  salary OFF之后，再用COLUMN语句不起作用
SELECT * FROM sm_emp;

--MUST OPEN AGAIN!
COLUMN  salary ON

COLUMN salary FORMAT L99999.99 HEADING '薪水' JUSTIFY RIGHT
SELECT * FROM sm_emp;



---------- sqlplus_column_all.txt ----------
/*
 * 范例名称：在SQL*Plus中格式化输出结果
 * 文件名称：sqlplus_column_all.txt
 */
 
 --heading 
select * from sm_emp;

COLUMN empid HEADING '雇员|编号' JUSTIFY CENTER

select * from sm_emp;


-- 综合运用
select * from sm_emp;

COLUMN empid HEADING '雇员|编号' JUSTIFY CENTER
COLUMN name  FORMAT A2 WORD_WRAP HEADING '姓|名' JUSTIFY CENTER

COLUMN salary FORMAT L99999.99 HEADING '薪水' JUSTIFY RIGHT
COLUMN TelNo HEADING '电话' JUSTIFY RIGHT

select * from sm_emp;



---------- sqlplus_spool.txt ----------
/*
 * 范例名称：将输出结果假脱机到磁盘
 * 文件名称：sqlplus_spool.txt
 */

SPOOL c:\plsql101_test.prn
SELECT * FROM plsql101_product;
SELECT * FROM plsql101_purchase;
SPOOL OFF

--.txt file will be ok.
SPOOL c:\plsql101_test.txt
SELECT * FROM plsql101_product;
SELECT * FROM plsql101_purchase;
SPOOL OFF