oracle sqlplus命令详解（官方示例）

以为内容选自Oracle官方文档，只讲command-line：

规范：<变量名> , {举例} , a | b 枚举可选值，（XX）描述

----------------------------------------------------------------------------------------------------

1、glogin.sql常用脚本（配置sqlplus command line）：

-- To change the SQL*Plus prompt to display the current user,
-- connection identifier and current time.
-- First set the database date format to show the time.
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH:MI:SS';
-- SET the SQLPROMPT to include the _USER, _CONNECT_IDENTIFIER
-- and _DATE variables.
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
-- To set the number of lines to display in a report page to 24.
SET PAGESIZE 24
-- To set the number of characters to display on each report line to 78.
SET LINESIZE 78
-- To set the number format used in a report to $99,999.
--SET NUMFORMAT $99,999

--SET SERVEROUTPUT ON

2、常用命令：

--sqlplus xxx@xxx 登陆sqlplus

--exit 退出

--CTRL+C 强制中断会话

--help index 调出sqlplus帮助索引界面

{

}

--? | help <commandname> 查看指定命令说明

--@ <scriptname> 运行指定脚本

--@@ <scriptname> 同@，运行nest script时使用

--/ 执行最近脚本

--accept 接受用户输入行存储到替代变量中

{

ACCEPT pswd CHAR PROMPT 'Password: ' HIDE

}

--append 向当前SQL行追加文本

{

HR@orcl> list
  1  select employee_id, last_name from employees
  2*
HR@orcl> append  where salary > 12000
  2*  where salary > 12000
HR@orcl> list
  1  select employee_id, last_name from employees
  2*  where salary > 12000

}

--achive log 查看和管理归档信息

--attribute 设置object类型column属性

{

ATTRIBUTE EMPLOYEE_TYPE.LAST_NAME FORMAT A20

}

--break 分开重复列

{

HR@orcl> BREAK ON DEPARTMENT_ID;
HR@orcl> SELECT DEPARTMENT_ID, LAST_NAME, SALARY
  2  FROM EMP_DETAILS_VIEW
  3  WHERE SALARY > 12000
  4  ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
           80 Russell                        14000
              Partners                       13500
           90 King                           24000
              Kochhar                        17000
              De Haan                        17000
          100 Greenberg                      12008
          110 Higgins                        12008

HR@orcl> CLEAR BREAKS

breaks 已清除

}

--COLUMN 命令集改变列格式

{

COLUMN SALARY FORMAT $99,999 --设置数字显示格式
COLUMN LAST_NAME FORMAT A10 --设置LAST_NAME列每行显示10个字符
COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS --LIKE通知SQLPLUS COPY之前定义好的列SALARY属性
COLUMN column_name CLEAR --清楚已设置列属性
CLEAR COLUMNS --重设所有列属性

}            --compute                                       运算查询结果集
{

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY SKIP 1;
SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY;
SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

CLEAR COMPUTES

}

--change err/correct 修改错误代码

{

SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY
                               *
第 1 行出现错误:
ORA-00904: "JO_ID": 标识符无效


HR@orcl> list 1
  1* SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY
HR@orcl> change /jo_id/job_id
  1* SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY
HR@orcl> l
  1  SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY
  2  FROM EMPLOYEES
  3* WHERE SALARY>12000

}

--connect | conn 会话中切换连接命令，如conn hr/hr@orcl

--disconnect 断开连接

--clear buffer 清空sql buffer

--clear screen 清空screen

--copy

--define <name> 定义变量（ variables）

{

HR@orcl> define l_name = "SMITH"
HR@orcl> define                 --列出所有替代变量
DEFINE _DATE           = "2014-02-13 03:47:00" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "HR" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.
1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE _RC             = "0" (CHAR)
DEFINE L_NAME          = "SMITH" (CHAR)
HR@orcl> undefine l_name

}

--undefine 取消替代变量

--describle/desc <table| view | function | synonym| procedure> 描述表、视图的列以及函数、程序的说明。

--del <line n> 删除SQL BUFFER指定行（不知道n 则倒序删除行）

{

HR@orcl> del 3
HR@orcl> list
  1  SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY
  2* FROM EMPLOYEES
HR@orcl> del
HR@orcl> list
  1* SELECT EMPLOYEE_ID, LAST_NAME, job_id, SALARY

HR@orcl> del
HR@orcl> list
SP2-0223: SQL 缓冲区中不存在行。

}

--edit | ed 创建或编辑sql 脚本

--execute <statement> 执行语句

--get <sqlfilename> 将sql脚本从文件load into sql buffer

{

HR@orcl> get c:\myfile
  1* select sysdate from dual;
HR@orcl> list
  1* select sysdate from dual;
HR@orcl>

}

--save <filename> 将sql buffer内容保存至文件

--host <cls | del...> sql会话模式利用host命令调用cmd命令

--input 追加新行enter a new line

{

HR@orcl> LIST
  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMPLOYEES
  3* WHERE JOB_ID='SA_MAN'
HR@orcl> input
  4  ORDER BY SALARY
  5
HR@orcl> l
  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMPLOYEES
  3  WHERE JOB_ID='SA_MAN'
  4* ORDER BY SALARY

}

--list | L <n> 列出SQL BUFFER中sql语句

{

HR@orcl> list
  1  SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  2  FROM EMPLOYEES
  3* WHERE SALARY>12000
HR@orcl> list 2
  2* FROM EMPLOYEES

}

--password 修改current user 密码

--print 打印变量

{

HR@orcl> print n

         N
----------
         2

}

--prompt 向screen发送消息

--quit 用法同exit

--recover 恢复数据库等操作

--repheader 放置report头（见ttitle）

--repfooter 放置report角

{

REPFOOTER PAGE CENTER 'END EMPLOYEE LISTING REPORT'
TTITLE RIGHT 'Page: ' FORMAT 999 SQL.PNO
SELECT LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000;

}

--remark | rem 单行注释

{

REMARK Commission Report;
REMARK to be run monthly.;
COLUMN LAST_NAME HEADING 'LAST_NAME';
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999;
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90;
REMARK Includes only salesmen;
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';

}

--run 列出sql buffer内容同时执行该内容

--show <variable> 显示变量或参数值

--shutdown 关闭数据库（DBA用户执行）

--startup 启动数据库（DBA用户执行）

--SET 命令集：

--SET PAGESIZE 60 设置一页显示行数

--set autocommit on 设置事务自动提交

--set verify off 关闭替代变量校验

{

SQL> SET VERIFY ON
SQL> SELECT &name FROM DUAL;
输入 name 的值:  1
原值    1: SELECT &name FROM DUAL
新值    1: SELECT 1 FROM DUAL

         1
----------
         1

SQL> SET VERIFY OFF
SQL> SELECT &name FROM DUAL;
输入 name 的值:  1

         1
----------
         1

}

--SET SERVEROUTPUT ON 打开屏幕输出dbms_output

--set underline <character> 设置标题与内容分隔符号

{

HR@orcl> select * from dual;

D
-
X

HR@orcl> set underline =
HR@orcl> /

D
=
X

}

--set termout off | on 关闭打开sqlplus输出显示

--SET AUTOTRACE TRACEONLY 查看执行计划

--set feedback off 关闭”PL/SQL 过程已成功完成。“

--store and start <variable> 存储set 变量值和恢复set 变量值

{

SCOTT@orcl> store set c:\plusenv.sql  --存储当前session set 变量值
已创建 file c:\plusenv.sql
SCOTT@orcl> show pagesize
pagesize 24
SCOTT@orcl> set pagesize 60
SCOTT@orcl> show pagesize
pagesize 60
SCOTT@orcl> start c:\plusenv.sql     --恢复set 变量值
SCOTT@orcl> show pagesize
pagesize 24
SCOTT@orcl> host del /f c:\plusenv.sql

}

--ttitle 设置report抬头

--btitle 设置report尾

{

HR@orcl> TTITLE CENTER -
> "ACME SALES DEPARTMENT PERSONNEL REPORT"
HR@orcl> BTITLE CENTER "COMPANY CONFIDENTIAL"
HR@orcl> SELECT DEPARTMENT_ID, LAST_NAME, SALARY
  2  FROM EMP_DETAILS_VIEW
  3  WHERE DEPARTMENT_ID = 30
  4  ORDER BY DEPARTMENT_ID, SALARY;

                    ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
           30 Himuro                          2600
           30 Tobias                          2800
           30 Baida                           2900
           30 Khoo                            3100
           30 Raphaely                       11000













                             COMPANY CONFIDENTIAL

已选择6行。

}

--spool <filename> 将屏幕显示保存到文件

--timing 计时器

{

HR@orcl> timing start sql_timer
HR@orcl> timing show
计时: sql_timer
已用时间:  00: 00: 05.25
HR@orcl> timing stop
计时: sql_timer
已用时间:  00: 00: 17.29

}

--variable <name> 声明绑定变量供PLSQL块引用

{

HR@orcl> variable ret_val number
HR@orcl> begin
  2       :ret_val := 4;
  3      end;
  4      /

PL/SQL 过程已成功完成。

HR@orcl> print ret_val

 RET_VAL
--------
      $4

HR@orcl> VARIABLE employee_info REFCURSOR
HR@orcl> BEGIN
  2  OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY
  3  FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ;
  4  END;
  5  /

PL/SQL 过程已成功完成。

HR@orcl> PRINT employee_info

}

--whenever oserror exit 执行命令中遇到系统异常自动退出

--whenever sqlerror exit sql.sqlcode 执行命令中遇到SQL异常自动退出

--XQUERY 运行xquery语句

{

set long 160
set linesize 160
xquery for $i in ora:view("EMP_DETAILS_VIEW") return $i
/

}

To be continue...

---------------------------

Dylan Presents.

posted @ 2014-02-12 22:13 一锤子技术员阅读(180) 评论(0) 编辑收藏举报来源

刷新页面返回顶部

wikiman

每一个不曾起舞的日子，都是对生命的辜负！

oracle sqlplus命令详解（官方示例）

公告