转 oracle 开发第03章 sqlplus

####

1.term命令：

当和SPOOL命令联合使用时，可以取消SQLPLUS输出，查询结果仅仅存在于假脱机文件中
set term on：查询结果既显示于假脱机文件中，又在SQLPLUS中显示；
set term off：查询结果仅仅显示于假脱机文件中。

2.其他命令：

SQL>set colsep'|';　　　 //-域输出分隔符

SQL>set echo off;　　　 //显示start启动的脚本中的每个sql命令，缺省为on

SQL> set echo on //设置运行命令是是否显示语句

SQL> set feedback on; //设置显示“已选择XX行”

SQL>set feedback off;　 //回显本次sql命令处理的记录条数，缺省为on

SQL>set heading off;　　 //输出域标题，缺省为on

SQL>set pagesize 0;　　 //输出每页行数，缺省为24,为了避免分页，可设定为0。

SQL>set linesize 80;　　 //输出一行字符个数，缺省为80

SQL>set numwidth 12;　 //输出number类型域长度，缺省为10

SQL>set termout off;　　 //显示脚本中的命令的执行结果，缺省为on

SQL>set trimout on;　　 //去除标准输出每行的拖尾空格，缺省为off

SQL>set trimspool on;　 //去除重定向（spool）输出每行的拖尾空格，缺省为off

SQL>set serveroutput on; //设置允许显示输出类似dbms_output

SQL> set timing on; //设置显示“已用时间：XXXX”

SQL> set autotrace on; //设置允许对执行的sql进行分析

set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.

原文地址：http://blog.csdn.net/ziyifengfei/article/details/9964161

1.查看表结构 desc 2.编辑SQL语句 append、list、change、run 3.保存、检索并运行文件 save、get、start、edit、spool 4.格式化列 column 5.设置页面大小 pagesize 6.设置行大小 linesize 7.清除列格式 clear 8.使用变量 define 9.创建简单报表 10.帮助信息 help

1.查看表结构 desc

DESC customers;

2.编辑SQL语句 append、list、change、run

SQL> select customer_id,first_name,last_name
from customers
where customer_id = 1;

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
      1 John       Brown

SQL> 1
  1* select customer_id,first_name,last_name

SQL> append , dob                                     --在行尾添加", dob"
  1* select customer_id,first_name,last_name, dob

SQL> list                                            --查看sqlplus缓存区所有行
  1  select customer_id,first_name,last_name, dob
  2  from customers
  3* where customer_id = 1

SQL> change /customer_id = 1/customer_id = 2         --将最后一行"customer_id = 1"改为"customer_id = 2"
  3* where customer_id = 2

SQL> run                                             --执行sqlplus缓存区的查询，同/
  1  select customer_id,first_name,last_name, dob
  2  from customers
  3* where customer_id = 2

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB
----------- ---------- ---------- ---------
      2 Cynthia    Orange      05-FEB-68

SQL> /                                                --执行sqlplus缓存区的查询，同run

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB
----------- ---------- ---------- ---------
      2 Cynthia    Orange      05-FEB-68

3.保存、检索并运行文件 save、get、start、edit、spool

SQL> select customer_id,first_name,last_name
from customers
where customer_id = 1;  

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
      1 John       Brown

SQL> save /tmp/cust_query.sql                         --将sqlplus缓存区的内容保存到磁盘目录
Created file /tmp/cust_query.sql

SQL> get /tmp/cust_query.sql                         --将磁盘上的脚本读入sqlplus缓存区                
  1  select customer_id,first_name,last_name
  2  from customers
  3* where customer_id = 1

SQL> /

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
      1 John       Brown

SQL> start /tmp/cust_query.sql                         --执行磁盘目录上的sql脚本

CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
      1 John       Brown

SQL> define _editor = 'vim';                        --改变默认编辑器

SQL> edit                                             --编辑sqlplus缓存区的内容
Wrote file afiedt.buf

  1  select customer_id,first_name,last_name
  2  from customers
  3* where customer_id = 2


SQL> /
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
      2 Cynthia    Orange

SQL> spool /tmp/cust_results.txt                    --将sqlplus的输出结果保存到磁盘文件中
SQL> /
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ----------
      2 Cynthia    Orange
SQL> spool off

4.格式化列 column

column product_id format 99
column name heading product_name format a13 word_wrapped
column description format a13 word_wrapped
column price format $99.99

select product_id,name,description,price
from products
where product_id < 6;

5.设置页面大小 pagesize

set pagesize 100         --设置一页显示的行数
                        --页面大小最大为50000，默认14

6.设置行大小 linesize

set linesize 50         --设置一行显示的字符数，默认80

7.清除列格式 clear

column product_id clear
clear columns

8.使用变量 define

select product_id,name,price
from products
where product_id = &v_product_id;    --使用变量 &v_product_id
Enter value for v_product_id: 2
old   3: where product_id = &v_product_id
new   3: where product_id = 2

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     2 Chemistry      $30.00

SQL> /
Enter value for v_product_id: 3
old   3: where product_id = &v_product_id
new   3: where product_id = 3

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     3 Supernova      $25.99

SQL> set verify off                    --禁止显示旧行和新行
SQL> /
Enter value for v_product_id: 4

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     4 Tank War      $13.95

SQL> set verify on                     --重新显示新旧行
SQL> /
Enter value for v_product_id: 1
old   3: where product_id = &v_product_id
new   3: where product_id = 1

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     1 Modern      $19.95
       Science

SQL> set define '#'                    --修改变量定义符为'#'
select product_id,name,price
from products
where product_id = #v_product_id;
Enter value for v_product_id: 4
old   3: where product_id = #v_product_id
new   3: where product_id = 4

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     4 Tank War      $13.95

SQL> set define '&'                    --将变量定义符改回'&'

select name,&v_col
from &v_table
where &v_col = &v_val;                --使用变量替换表名和列名
Enter value for v_col: product_type_id
old   1: select name,&v_col
new   1: select name,product_type_id
Enter value for v_table: products
old   2: from &v_table
new   2: from products
Enter value for v_col: product_type_id
Enter value for v_val: 1
old   3: where &v_col = &v_val
new   3: where product_type_id = 1

select name,&&v_col
from &v_table
where &&v_col = &v_val;                --使用&&避免重复输入变量
Enter value for v_col: product_type_id
old   1: select name,&&v_col
new   1: select name,product_type_id
Enter value for v_table: products
old   2: from &v_table
new   2: from products
Enter value for v_val: 1
old   3: where &&v_col = &v_val
new   3: where product_type_id = 1

SQL> define v_product_id = 4        --使用define命令定义变量
SQL> define v_product_id
DEFINE V_PRODUCT_ID    = "4" (CHAR)
SQL> 
select product_id,name,price
from products
  3  where product_id = &v_product_id;
old   3: where product_id = &v_product_id
new   3: where product_id = 4

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     4 Tank War      $13.95

SQL> define
DEFINE _DATE           = "06-JAN-16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "unicode" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR           = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000400" (CHAR)
DEFINE V_COL           = "product_type_id" (CHAR)
DEFINE V_PRODUCT_ID    = "4" (CHAR)

SQL> accept v_customer_id number format 99 prompt 'Customer id: '    --使用accept命令定义并设置变量
Customer id: 4
SQL> accept v_date date format 'DD-MON-YYYY' prompt 'Date: '
Date: 06-MAY-2012
SQL> accept v_password char prompt 'Password: ' hide
Password:  
 SQL> define
DEFINE _DATE           = "06-JAN-16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "unicode" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR           = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000400" (CHAR)
DEFINE V_COL           = "product_type_id" (CHAR)
DEFINE V_PRODUCT_ID    = "4" (CHAR)
DEFINE V_CUSTOMER_ID   =      4 (NUMBER)
DEFINE V_DATE           = "06-MAY-2012" (CHAR)
DEFINE V_PASSWORD      = "1234567" (CHAR)

SQL> undefine v_col
SQL> undefine v_product_id
SQL> undefine v_customer_id
SQL> undefine v_date
SQL> undefine v_password                 --使用undefine命令删除变量

SQL> define
DEFINE _DATE           = "06-JAN-16" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "unicode" (CHAR)
DEFINE _USER           = "STORE" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR           = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000400" (CHAR)

9.创建简单报表

vim /tmp/report1.sql

--suppress display of the statements and verification message
set echo off                --禁止显示脚本中的SQL语句
set verify off                --禁止显示验证消息
select product_id,name,price
from products
where product_id = &v_product_id;    --使用临时变量v_product_id

SQL> @ /tmp/report1.sql
Enter value for v_product_id: 2

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     2 Chemistry      $30.00

vim /tmp/report2.sql

--suppress display of the statements and verification message
set echo off
set verify off
accept v_product_id number format 99 prompt 'Enter product id: '    --使用已定义变量v_product_id
select product_id,name,price
from products
where product_id = &v_product_id;
--clear up
undefine v_product_id

SQL> @ /tmp/report2.sql
Enter product id: 4

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     4 Tank War      $13.95

vim /tmp/report3.sql

--suppress display of the statements and verification message
set echo off                --禁止显示脚本中的SQL语句
set verify off                --禁止显示验证消息
select product_id,name,price
from products
where product_id = &1;        --向脚本中的变量传递值

SQL> @ /tmp/report3.sql 4

PRODUCT_ID product_name    PRICE
---------- ------------- -------
     4 Tank War      $13.95

vim /tmp/report4.sql

--suppress display of the statements and verification message
set echo off                --禁止显示脚本中的SQL语句
set verify off                --禁止显示验证消息
select product_id,product_type_id,name,price
from products
where product_id = &1
and price > &2;                --向脚本中的多个变量传递值

vim /tmp/report5.sql

--添加页眉
ttitle left 'Run date: ' _date center 'Run by the' sql.user ' user' right 'Page: ' format 999 sql.pno skip 2
--添加页脚
btitle center 'Thanks for running the report' right 'Page: ' format 999 sql.pno

set echo off
set verify off
set pagesize 15
set linesize 70
clear columns
column product_id heading id format 99
column name heading 'Product Name' format a20 word_wrapped
column description heading Description format a30 word_wrapped
column price heading Price format $99.99

select product_id,name,description,price
from products;

clear columns
ttitle off
btitle off

vim /tmp/report6.sql

--计算小计
break on product_type_id                    --根据列值的范围分隔输出结果
compute sum of price on product_type_id        --计算一列的值
set echo off
set verify off
set pagesize 20
set linesize 70

clear columns
column price heading Price format $999.99

select product_type_id,name,price
from products
order by product_type_id;

clear columns

10.帮助信息 help

help
help index

11.自动生成SQL语句

select 'drop table ' || table_name||';' 
from user_tables
order by table_name;

'DROPTABLE'||TABLE_NAME||';'
------------------------------------------
drop table CUSTOMERS;
drop table EMPLOYEES;
drop table PRODUCTS;
drop table PRODUCT_TYPES;
drop table PURCHASES;
drop table SALARY_GRADES;

define 不区分大小写

&data 和 &DATA 是一样的。

相关阅读:
【已解决】github中git push origin master出错：error: failed to push some refs to
好记心不如烂笔头，ssh登录　The authenticity of host 192.168.0.xxx can't be established.　的问题
 THINKPHP 5.0目录结构
 thinkphp5.0入口文件
 thinkphp5.0 生命周期
 thinkphp5.0 架构
 Django template
Django queryset
Django model
Python unittest
原文地址：https://www.cnblogs.com/feiyun8616/p/5900719.html

转 oracle 开发 第03章 sqlplus

转 oracle 开发第03章 sqlplus