DESC customers;

SQL> select customer_id,first_name,last_namefrom customerswhere customer_id = 1;CUSTOMER_ID FIRST_NAME LAST_NAME----------- ---------- ----------      1 John       BrownSQL> 1  1* select customer_id,first_name,last_nameSQL> append , dob                                     --在行尾添加", dob"  1* select customer_id,first_name,last_name, dobSQL> list                                            --查看sqlplus缓存区所有行  1  select customer_id,first_name,last_name, dob  2  from customers  3* where customer_id = 1SQL> change /customer_id = 1/customer_id = 2         --将最后一行"customer_id = 1"改为"customer_id = 2"  3* where customer_id = 2SQL> run                                             --执行sqlplus缓存区的查询，同/  1  select customer_id,first_name,last_name, dob  2  from customers  3* where customer_id = 2CUSTOMER_ID FIRST_NAME LAST_NAME  DOB----------- ---------- ---------- ---------      2 Cynthia    Orange      05-FEB-68SQL> /                                                --执行sqlplus缓存区的查询，同runCUSTOMER_ID FIRST_NAME LAST_NAME  DOB----------- ---------- ---------- ---------      2 Cynthia    Orange      05-FEB-68

SQL> select customer_id,first_name,last_namefrom customerswhere customer_id = 1;  CUSTOMER_ID FIRST_NAME LAST_NAME----------- ---------- ----------      1 John       BrownSQL> save /tmp/cust_query.sql                         --将sqlplus缓存区的内容保存到磁盘目录Created file /tmp/cust_query.sqlSQL> get /tmp/cust_query.sql                         --将磁盘上的脚本读入sqlplus缓存区                  1  select customer_id,first_name,last_name  2  from customers  3* where customer_id = 1SQL> /CUSTOMER_ID FIRST_NAME LAST_NAME----------- ---------- ----------      1 John       BrownSQL> 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 = 2SQL> /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    OrangeSQL> spool off

column product_id format 99column name heading product_name format a13 word_wrappedcolumn description format a13 word_wrappedcolumn price format $99.99select product_id,name,description,pricefrom productswhere product_id < 6;

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

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

column product_id clearclear columns

select product_id,name,pricefrom productswhere product_id = &v_product_id;    --使用变量 &v_product_idEnter value for v_product_id: 2old   3: where product_id = &v_product_idnew   3: where product_id = 2PRODUCT_ID product_name    PRICE---------- ------------- -------     2 Chemistry      $30.00SQL> /Enter value for v_product_id: 3old   3: where product_id = &v_product_idnew   3: where product_id = 3PRODUCT_ID product_name    PRICE---------- ------------- -------     3 Supernova      $25.99

SQL> set verify off                    --禁止显示旧行和新行SQL> /Enter value for v_product_id: 4PRODUCT_ID product_name    PRICE---------- ------------- -------     4 Tank War      $13.95

SQL> set verify on                     --重新显示新旧行SQL> /Enter value for v_product_id: 1old   3: where product_id = &v_product_idnew   3: where product_id = 1PRODUCT_ID product_name    PRICE---------- ------------- -------     1 Modern      $19.95       Science

SQL> set define ‘#‘                    --修改变量定义符为‘#‘select product_id,name,pricefrom productswhere product_id = #v_product_id;Enter value for v_product_id: 4old   3: where product_id = #v_product_idnew   3: where product_id = 4PRODUCT_ID product_name    PRICE---------- ------------- -------     4 Tank War      $13.95SQL> set define ‘&‘                    --将变量定义符改回‘&‘

select name,&v_colfrom &v_tablewhere &v_col = &v_val;                --使用变量替换表名和列名Enter value for v_col: product_type_idold   1: select name,&v_colnew   1: select name,product_type_idEnter value for v_table: productsold   2: from &v_tablenew   2: from productsEnter value for v_col: product_type_idEnter value for v_val: 1old   3: where &v_col = &v_valnew   3: where product_type_id = 1

select name,&&v_colfrom &v_tablewhere &&v_col = &v_val;                --使用&&避免重复输入变量Enter value for v_col: product_type_idold   1: select name,&&v_colnew   1: select name,product_type_idEnter value for v_table: productsold   2: from &v_tablenew   2: from productsEnter value for v_val: 1old   3: where &&v_col = &v_valnew   3: where product_type_id = 1

SQL> define v_product_id = 4        --使用define命令定义变量SQL> define v_product_idDEFINE V_PRODUCT_ID    = "4" (CHAR)SQL> select product_id,name,pricefrom products  3  where product_id = &v_product_id;old   3: where product_id = &v_product_idnew   3: where product_id = 4PRODUCT_ID product_name    PRICE---------- ------------- -------     4 Tank War      $13.95SQL> defineDEFINE _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 ProductionWith 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: 4SQL> accept v_date date format ‘DD-MON-YYYY‘ prompt ‘Date: ‘Date: 06-MAY-2012SQL> accept v_password char prompt ‘Password: ‘ hidePassword:   SQL> defineDEFINE _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 ProductionWith 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_colSQL> undefine v_product_idSQL> undefine v_customer_idSQL> undefine v_dateSQL> undefine v_password                 --使用undefine命令删除变量SQL> defineDEFINE _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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)DEFINE _O_RELEASE      = "1102000400" (CHAR)

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

SQL> @ /tmp/report1.sqlEnter value for v_product_id: 2PRODUCT_ID product_name    PRICE---------- ------------- -------     2 Chemistry      $30.00

--suppress display of the statements and verification messageset echo offset verify offaccept v_product_id number format 99 prompt ‘Enter product id: ‘    --使用已定义变量v_product_idselect product_id,name,pricefrom productswhere product_id = &v_product_id;--clear upundefine v_product_id

SQL> @ /tmp/report2.sqlEnter product id: 4PRODUCT_ID product_name    PRICE---------- ------------- -------     4 Tank War      $13.95

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

SQL> @ /tmp/report3.sql 4PRODUCT_ID product_name    PRICE---------- ------------- -------     4 Tank War      $13.95

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

--添加页眉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.pnoset echo offset verify offset pagesize 15set linesize 70clear columnscolumn product_id heading id format 99column name heading ‘Product Name‘ format a20 word_wrappedcolumn description heading Description format a30 word_wrappedcolumn price heading Price format $99.99select product_id,name,description,pricefrom products;clear columnsttitle offbtitle off

--计算小计break on product_type_id                    --根据列值的范围分隔输出结果compute sum of price on product_type_id        --计算一列的值set echo offset verify offset pagesize 20set linesize 70clear columnscolumn price heading Price format $999.99select product_type_id,name,pricefrom productsorder by product_type_id;clear columns

helphelp index

select ‘drop table ‘ || table_name||‘;‘ from user_tablesorder 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;