踏入PL/SQL大门的第一步之SQLPLUS 学习（第二章）

最新推荐文章于 2021-04-05 03:22:00 发布

Rhys-Oracle

最新推荐文章于 2021-04-05 03:22:00 发布

阅读量1k

点赞数

分类专栏： ORACLE 之 PL /SQL

本文链接：https://blog.csdn.net/rhys_oracle/article/details/8747365

版权

ORACLE 之 PL /SQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

踏入PL/SQL大门的第一步

SQL> declare
  2  v_res number(6,2);
  3  begin
  4  v_res := 200/7;
  5  dbms_output.put_line('The result is: ' || v_res);
  6  end;
  7  /
The result is: 28.57

PL/SQL procedure successfully completed.

#####################################################
declare
 v_dname varchar2(12);
begin
 select dname into v_dname from scott.dept
 where LOC='DALLAS';
 dbms_output.put_line('The result is:' || v_dname);
exception
  when no_data_found then
     dbms_output.put_line('nothings');
  when too_many_rows then
     dbms_output.put_line('the data is very many,please to check it');
end;
/

注意： select ....into  from  是pl/sql特有的赋值语句。select的内容要和into后面的变量一一对应，另外这个语句的结果要返回一个条信息，如果多与一条或者没有，则会引发异常。

SQLPLUS 经常使用的参数介绍

1）追加文本命令：

append text；

SQL> select * from t;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20


SQL> append  where ENAME='WARD';
  1* select * from t where ENAME='WARD'
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500

2）添加文本命令

input text；

SQL> l
  1* select * from t
SQL> input
  2  where
  3  ename='MANAGER'
  4  ;

未选定行

SQL> L
  1  select * from t
  2  where
  3  ename='MANAGER'
  4*
SQL>

3）替换文本命令

change /old/new

SQL> l
  1  select * from t
  2  where
  3  ename='MANAGER'
  4*
SQL> 3
  3* ename='MANAGER'
SQL> change /ename/JOB
  3* JOB='MANAGER'
SQL> L
  1  select * from t
  2  where
  3  JOB='MANAGER'
  4*
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20


SQL>

change /old(删除原先的数据内容）


SQL> L
  1  select * from t
  2  where
  3  JOB='MANAGER'
  4*
SQL> 3
  3* JOB='MANAGER'
SQL> change /JOB='MANAGER';
  3*
SQL> L
  1  select * from t
  2  where
  3
  4*
SQL> 2
  2* where
SQL> CHANGE /WHERE
  2*
SQL> L
  1  select * from t
  2
  3
  4*
SQL>

4）SQLPLUS删除命令

del（删除当前行）

del n（删除第N行）

del n * （从第N行一直删除到当前行）

SQL> L
  1  select * from t
  2  where empno='7369'
  3* and job='CLERK'
SQL> del
SQL> l
  1  select * from t
  2* where empno='7369'
SQL> del 2
SQL> l
  1* select * from t
SQL> select * from t
  2  where empno='7369'
  3  and job='clear';

未选定行

SQL> l
  1  select * from t
  2  where empno='7369'
  3* and job='clear'
SQL> del 2 *
SQL> l
  1* select * from t
SQL>

5）清除缓冲区命令

clear buffer；

SQL> l
  1* select * from t
SQL> clear buffer;
buffer 已清除
SQL> l
SP2-0223: SQL 缓冲区中不存在行。
SQL>

6）显示缓冲区命令

list；（显示缓冲区中所有的命令）

list n；（显示缓冲区中第N行的命令）

list n lat；（显示缓冲区中从第n行到最后一行的数据）

SQL>
SQL>
SQL> L
  1  select * from t
  2  where empno='7566'
  3* and job='MANAGER'
SQL> L 1
  1* select * from t
SQL> L2
  2* where empno='7566'
SQL> L 2 LAST;
  2  where empno='7566'
  3* and job='MANAGER'
SQL>

7）sqlplus的保存命令

edit ；利用记事本保存

save filename；保存到一个txt文件中

SQL> EDIT
已写入 file afiedt.buf

  1  select * from t
  2  where empno='7566'
  3* and job='MANAGER'
SQL> SAVE C:\XIAOHAI.TXT
已创建 file C:\XIAOHAI.TXT
SQL> L
  1  select * from t
  2  where empno='7566'
  3* and job='MANAGER'
SQL>

8）查询结果的格式化

set pagesize n；（设置每一页显示的行数）

set newpage n；（设置页与页之间间隔的行数）

set linesize n；（设置每一行可以容纳的字符数目）

SCOTT@conn#SHOW LINESIZE  
linesize 80
SCOTT@conn#show pagesize;
pagesize 14
SCOTT@conn#show newpage
newpage 1
SCOTT@conn#select * from t;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20

      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
        30

      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20


SCOTT@conn#set linesize 145
SCOTT@conn#select * from t;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

SCOTT@conn#set pagesize 3
SCOTT@conn#select * from t;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80            800                    20

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

SCOTT@conn#set newpage 2;
SCOTT@conn#select * from t;


      7369 SMITH      CLERK           7902 17-12月-80            800                    20


      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30


      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30


      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

SCOTT@conn#

9）列名的格式化

column column_name format dataformat;

格式含义

9 代表一个数字字符

0 在指定的位置显示前导0或后置0

$ 美元货币符号

MI 显示负号

，显示千分位分割符

. 显示小数点

L 显示本地区域的货币符号

10）保存查询结果

spool file

spool off；

11）调用外部脚本

start filename;

@filename;

12）注释的使用

remark comment；

Rem
Rem $Header: catalog.sql 07-mar-2008.13:28:05 huagli Exp $ catalog.sql
Rem
Rem Copyright (c) 1988, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem   CATALOG.SQL
Rem FUNCTION
Rem   Creates data dictionary views.
Rem NOTES
Rem   Must be run when connected AS SYSDBA
Rem
Rem MODIFIED
Rem     huagli     01/07/08  - add cddst.sql
Rem     cdilling   01/18/07  - remove reference to cdfmap.sql
Rem     schakkap   09/20/06  - cdoptim.sql depends on *partv views created in
Rem                            cdpart.sql, run it as single process.
Rem     cdilling   08/03/06  - add catexp.sql, catldr.sql, catsum.sql
Rem     rburns     05/18/06  - add multiprocessing notations
Rem     cdilling   05/04/06  - move definitions into separate files
Rem     mgirkar    04/26/06  - Add redo_dest_resp_histogram view
Rem     jforsyth   03/07/06  - shifting encryption, compression, and sharing
Rem                            flags
Rem     jforsyth   03/06/06  - add ENCRYPT, COMPRESS, SHARE columns to LOB
Rem                            views
Rem     rdongmin   04/12/06  - add fixed view for hint definition
Rem     spsundar   05/01/06  - change DOMIDX_MANAGEMENT bit
Rem     dmukhin    04/25/06  - ADP: embedded xforms
Rem     dmukhin    03/28/06  - ADP: add xform table type
Rem     rdecker    03/28/06  - add assembly
Rem     jgalanes   03/14/06  - Syns for LogMiner views (IDR)
Rem     mbaloglu   03/06/06  - Add SYNC column for LOB views, enable CACHE
Rem                            NOLOGGING
Rem     spsundar   02/22/06  - add DOMIDX_MANAGEMENT to *_indexes
Rem     shsong     04/20/06  - Bug 5014810: remove useless V$temp_histogram
Rem     tbingol    11/22/05  - Result_Cache: Add synonyms for V$ fixed views
Rem     rdongmin   03/15/06  - add fixed views for feature control
Rem     msusaira   03/03/06  - add vSiostat_network
Rem     ssubrama   02/21/06  - lrg 2069988 fix dba_indexes
Rem     svshah     02/22/06  - Fix gv,v$sqlstats declarations
Rem     ssubrama   02/07/06  - bug 4969399 change dba_indexes
Rem     ramekuma   12/10/05  - Add invisible flag to index flags
Rem     mtakahar   10/28/05  - #(4704779) fix wrong histogram type
Rem     aramarao   12/13/05  - all_tables, all_tab_columns and
Rem                            all_tab_comments are notconsistent handling the