📄 script_64.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
('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_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_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: 表或视图不存在
edit
--修改，保存，退出。在sqlplus中
/
--执行
---------- 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;
--在oracle8i中要求将login.sql写在当前目录下
SET LINESIZE 80
SET PAGESIZE 14
store set login.sql append;
--close this sql*plus
--open another sql*plus
select * from user_tables;
---------- sm_sqlplus_column.txt ----------
/*
* 范例名称：在SQL*Plus中格式化输出结果
* 文件名称：sm_sqlplus_column.txt
*/
--必须首先执行sm_dataInsert.txt
update sm_emp set salary=10000.111
where empid='0000000003';
--按小数点对齐
SELECT * FROM sm_emp;
COLUMN salary FORMAT 9999.99
SELECT * FROM sm_emp;
--当9999.99时，若纪录〉9999则显示如何？
COLUMN salary FORMAT 99999.99
--可以通过COLUMN salary FORMAT 99.99测试
COLUMN salary FORMAT 99.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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -