主頁 > 知識庫 > SQL PLUS基本命令的使用方法示例

SQL PLUS基本命令的使用方法示例

1、Oracle 中 dba，all，user，之間的區(qū)別

1. 結論：'權限大小不同': dba_* > all_* > user_*
   (1) dba_* : 可以訪問 '數(shù)據(jù)庫' 中所有的對象（前提：該用戶是 dba 用戶）
   (2) all_* ：某一用戶 '擁有' 的或 '可以訪問' 的所有的對象
   (3) user_*：某一用戶 '擁有' 的所有對象

2. 查詢是否是 dba 用戶
   select * from dba_role_privs t where t.granted_role = 'DBA';

2、desc

desc:萬能查看命令

查看dept表
SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

3、設置SQL*PLUS的運行環(huán)境，包括每行的顯示字符數(shù)、每頁顯示行數(shù)、每頁打印標題前空行數(shù)。 set命令基本語法

在Oracle 11g數(shù)據(jù)庫中，用戶可以使用set命令來設置SQL*Plus的運行環(huán)境；

set system_variable value;

system_variable：變量名；
value：變量值；

通過set命令設置的環(huán)境變量是臨時的，不是永久的；

當用戶退出SQL*Plus環(huán)境后，用戶設置的環(huán)境參數(shù)會全部消失；

使用set命令設置運行環(huán)境

pagesize變量：該變量用來設置從頂部標題至頁結束之間的行數(shù)；
set pagesize value;
value的默認值為14；

使用show pagesize命令顯示當前SQL*Plus環(huán)境中的一頁有多少行；
show pagesize;

同理：
newpage變量：該變量用來設置一頁中空行的數(shù)量；
set newpage value;
value的默認值為1；

show newpage;

linesize變量：
該變量用來設置在SQL＊Plus環(huán)境中一行所顯示的最多字符總數(shù)；
set linesize value;
value的默認值為80；

show linesize;

pause變量：
該變量用來設置SQL*Plus輸出結果是否滾動顯示；
set pause value;

value變量值有以下三種情況：
- off：默認值，表示返回結果一次性輸出完畢，中間的每一頁不會暫停；
- on：表示輸出結果的每一頁都暫停，用戶按后回車鍵后會繼續(xù)顯示；
- text：在設置pause的值為on之后，再設置text的值，則每次暫停都將顯示該字符串； 
        當pause的值設置為off時，設置text的值沒有任何意義；

oracle用命令執(zhí)行sql腳本文件

當sql命令過多（sql文件過大）時，用plsql執(zhí)行時比較慢而且容易超時，此時可以用sqlplus命令直接執(zhí)行sql腳本文件。

@ H:/sql/test.sql  (絕對路徑)

oracle中spool將查詢的數(shù)據(jù)寫入文件

通常情況下，我們使用SPOOL方法，將數(shù)據(jù)庫中的表導出為文本文件的時候會采用兩種方法，如下述：

方法一：采用以下格式腳本
set colsep '' ------設置列分隔符
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路徑+文件名
select * from tablename;
spool off

方法二：采用以下腳本
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路徑+文件名
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off

差別：

比較以上方法，即方法一采用設定分隔符然后由sqlplus自己使用設定的分隔符對字段進行分割，方法二將分隔符拼接在SELECT語句中，即手工控制輸出格式。

在實踐中，我發(fā)現(xiàn)通過方法一導出來的數(shù)據(jù)具有很大的不確定性，這種方法導出來的數(shù)據(jù)再由sql導入的時候出錯的可能性在95%以上，尤其對大批量的數(shù)據(jù)表，如100萬條記錄的表更是如此，而且導出的數(shù)據(jù)文件狂大。

而方法二導出的數(shù)據(jù)文件格式很規(guī)整，數(shù)據(jù)文件的大小可能是方法一的1/4左右。經(jīng)這種方法導出來的數(shù)據(jù)文件再由sqll導入時，出錯的可能性很小，基本都可以導入成功。

因此，實踐中我建議大家使用方法二手工去控制spool文件的格式，這樣可以減小出錯的可能性，避免走很多彎路。

ttitle,btitle命令

它主要是對頭標題與尾標題的設置，如下：
eg:為查詢結果設置居中的頭部標題、居右的當天日期作為尾部標題，并為查詢結果列定義列標題。

SQL> ttitle center '結果';
SQL> btitle right '2021/03/30';
SQL> select * from dept; ;

                                      結果
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON





                                                                      2021/03/30
如果想撤消ttitle與btitle:
SQL> ttitle off;
SQL> btitle off;
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

column 命令

format格式化輸出

format 選項
用于格式化指定的列
SQL> column sal format $99,99,99;
SQL> select empno,ename,sal from scott.emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH           $8,00
      7499 ALLEN          $16,00
      7521 WARD           $12,50
      7566 JONES          $29,75
      7654 MARTIN         $12,50
      7698 BLAKE          $28,50
      7782 CLARK          $24,50
      7788 SCOTT          $30,00
      7839 KING           $50,00
      7844 TURNER         $15,00
      7876 ADAMS          $11,00

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES           $9,50
      7902 FORD           $30,00
      7934 MILLER         $13,00

14 rows selected.

heading 選項
用于定義列標題
SQL> col empno heading 雇員編號;
SQL> col ename heading 雇員姓名;
SQL> col sal heading 雇員薪水;
SQL> select empno,ename,sal from scott.emp;

  雇員編號 雇員姓名     雇員薪水
---------- ---------- ----------
      7369 SMITH           $8,00
      7499 ALLEN          $16,00
      7521 WARD           $12,50
      7566 JONES          $29,75
      7654 MARTIN         $12,50
      7698 BLAKE          $28,50
      7782 CLARK          $24,50
      7788 SCOTT          $30,00
      7839 KING           $50,00
      7844 TURNER         $15,00
      7876 ADAMS          $11,00

  雇員編號 雇員姓名     雇員薪水
---------- ---------- ----------
      7900 JAMES           $9,50
      7902 FORD           $30,00
      7934 MILLER         $13,00

14 rows selected.

對緩存區(qū)的操作

（1）查看當前緩存區(qū)中的SQL命令。
>list
（2）執(zhí)行存儲在緩存區(qū)的SQL命令。
>run
（3）將緩存區(qū)中的內(nèi)容保存到一個SQL腳本文件（文件名自擬）。
>save C:/sql.sql
（4）把一個腳本文件的內(nèi)容放進緩存區(qū)，并執(zhí)行緩存區(qū)中的語句。
>get C:/sql.sql
>start C:/sql.sql
（5）清除緩存區(qū)中的內(nèi)容。
>clear buffer

SQL> save H:/sql/test3.txt;
Created file H:/sql/test3.txt
SQL> get H:/sql/test.sql;
  1  select table_name from user_tables;
  2* desc dept
SQL> start H:/sql/test.sql;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> clear buffer;
buffer cleared
SQL>