Oracle PL/SQL编程入门篇

本文主要是介绍Oracle PL/SQL编程入门篇，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

关注【郑大钱呀】【_公】【_众】【_号】，我们一起交流，一起学习。

文章目录

Windows启动/关闭Oracle数据库
解锁scott用户
查询Oracle的版本号
使用SQLPlus创建匿名块
常用的SQLPlus命令
- - 查询表结构(DESC)
  - 设置控制台显示字符长度
  - L命令，显示刚刚输入的SQL缓冲区命令
  - n text 修改SQL缓冲区命令
  - N和A命令追加
  - /执行命令
  - DEL删除命令
  - C修改命令
  - SAVE命令(生成脚本文件)
  - @运行脚本文件
  - help命令
  - spool命令
将Oracle数据导出给其他系统
利用AUTOTRACE追踪SQL语句

Windows启动/关闭Oracle数据库

启动就用start,关闭就用stop

# 打开命令行(管理员权限)启动监听
lsnrctl start/stop
# 监听启动成功后，启动服务
net start/stop OracleServiceORCL

解锁scott用户

打开sqlplus,使用SYSDBA的身份登录
执行如下命令alter user scott account unlock解锁账户
然后执行commit，提交
使用scott用户登录，默认密码为tiger，登录成功后需要重新修改密码即可。
具体实例如下：

请输入用户名:  sys as sysdba
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user scott account unlock;

用户已更改。

SQL> commit;

提交完成。

SQL> conn scott/tiger
ERROR:
ORA-28001: the password has expired

更改 scott 的口令
新口令:
重新键入新口令:
口令已更改
已连接。

查询Oracle的版本号

-- 查询Oracle版本号
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

使用SQLPlus创建匿名块

DECLARE
  v_name varchar2(20) := '郑大钱';
begin
  DBMS_OUTPUT.PUT_LINE(v_name||'给您拜年了');
end;

知识点：

PL/SQL编程，变量需要先声明才能使用
:=表示为变量赋值
所有的字符串都要放在单引号内
||表示字符串的拼接符
DBMS_OUTPUT为Oracle提供的I/O包，PUT_LINE的作用就是将信息打印出来

我们创建好了之后如何在SQLPlus中执行呢？具体如下：

SQL> DECLARE
  2    v_name varchar2(20) := '郑大钱';
  3  begin
  4    DBMS_OUTPUT.PUT_LINE(v_name||'给您拜年了');
  5  end;
  6  /

PL/SQL 过程已成功完成。

SQL>

值得注意的是，要在输入语句后，需要在下一行加一个/,才会执行，执行完成后，但是我们并没有看到打印的信息，这是因为要开启DBMS_OUTPUT的功能才行，命令如下：

-- 查看DBMS_OUTPUT的状态，OFF表示关闭
SQL> show serveroutput
serveroutput OFF
-- 开启DBMS_OUTPUT
SQL> set serveroutput on
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL>

我们再次执行，结果如下：

SQL> DECLARE
  2    v_name varchar2(20) := '郑大钱';
  3  begin
  4    DBMS_OUTPUT.PUT_LINE(v_name||'给您拜年了');
  5  end;
  6  /
郑大钱给您拜年了

PL/SQL 过程已成功完成。

SQL>

常用的SQLPlus命令

查询表结构(DESC)

SQL> desc emp;
 名称           是否为空?       类型
 ----------------- -------- ----------------------------
 EMPNO           NOT NULL    NUMBER(4)
 ENAME                       VARCHAR2(10)
 JOB                         VARCHAR2(9)
 MGR                         NUMBER(4)
 HIREDATE                    DATE
 SAL                         NUMBER(7,2)
 COMM                        NUMBER(7,2)
 DEPTNO                      NUMBER(2)
SQL>

NOT NULL 表示该字段不能为空，无则表示可以为空
SQLPLUS的命令可以不加;号

设置控制台显示字符长度

set line 100;

L命令，显示刚刚输入的SQL缓冲区命令

SQL> select *
  2  from emp
  3  where empno>7900
  4  order by empno desc;

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20

SQL> L
  1  select *
  2  from emp
  3  where empno>7900
  4* order by empno desc
SQL>

值得注意的是，SQL缓冲区只能存储一个SQL语句。

n text 修改SQL缓冲区命令

SQL> L
  1  select *
  2  from emp
  3  where empno>7900
  4* order by empno desc
SQL> 3 where empno>7875
SQL> L
  1  select *
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL>
SQL>

比如我们想修改L命令中的第2行，此时我们就可以指定行号和修改的内容，然后就可以把第二行的内容替换了。

N和A命令追加

SQL> l
  1  select empno,ename,job
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL> /

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      7934 MILLER               CLERK
      7902 FORD                 ANALYST
      7900 JAMES                CLERK
      7876 ADAMS                CLERK
SQL>

这里我们看第4行，4后面有个*号，此时表示当前行在第四行，如果我们想要在在查询的字段后面再加个mgr字段,需要我们先将当前行切换到第一行，具体操作如下：

SQL> L
  1  select empno,ename,job
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL> 1
  1* select empno,ename,job
SQL>

输入行号即可，切换完当前行后，就可以使用A命令修改了,具体如下：

SQL> L
  1  select empno,ename,job
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL> 1
  1* select empno,ename,job
SQL> A ,MGR
  1* select empno,ename,job,MGR
SQL> L
  1  select empno,ename,job,MGR
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL> /

     EMPNO ENAME                JOB                       MGR
---------- -------------------- ------------------ ----------
      7934 MILLER               CLERK                    7782
      7902 FORD                 ANALYST                  7566
      7900 JAMES                CLERK                    7698
      7876 ADAMS                CLERK                    7788

SQL>

/执行命令

我们上面修改了L的命令，此时执行就需要/命令，具体如下：

SQL> L
  1  select *
  2  from emp
  3  where empno>7900
  4* order by empno desc
SQL> 3 where empno>7875
SQL> L
  1  select *
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL> /

     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20
      7900 JAMES                CLERK                    7698 03-12月-81            950                    30
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                    20

SQL>

DEL删除命令

此时我们想删除L命令的第4行，实例如下：

SQL> L
  1  select empno,ename,job,MGR
  2  from emp
  3  where empno>7875
  4* order by empno desc
SQL> del 4
SQL> L
  1  select empno,ename,job,MGR
  2  from emp
  3* where empno>7875
SQL>

我们也可以删除多行，实例如下：

SQL> l
  1  select empno,ename,job,MGR
  2  from emp
  3  where empno>7875
  4* order byempno desc
SQL> DEL 3 4
SQL> l
  1  select empno,ename,job,MGR
  2* from emp
SQL>

C修改命令

将某一行的某个字串修改成另一个字串，实例如下：

SQL> 1
  1* select enamename,job,MGR
SQL> c /enamename/ename
  1* select ename,job,MGR
SQL> l
  1  select ename,job,MGR
  2* from emp

SAVE命令(生成脚本文件)

实例如下：

SQL> save E:\SQL\temp.sql
已创建 file E:\SQL\temp.sql

@运行脚本文件

通过@/start命令直接运行脚本文件，示例如下：

SQL> @ E:\SQL\temp.sql

ENAME                JOB                       MGR
-------------------- ------------------ ----------
SMITH                CLERK                    7902
ALLEN                SALESMAN                 7698
WARD                 SALESMAN                 7698
JONES                MANAGER                  7839
MARTIN               SALESMAN                 7698
BLAKE                MANAGER                  7839
CLARK                MANAGER                  7839
SCOTT                ANALYST                  7566
KING                 PRESIDENT
TURNER               SALESMAN                 7698
ADAMS                CLERK                    7788

ENAME                JOB                       MGR
-------------------- ------------------ ----------
JAMES                CLERK                    7698
FORD                 ANALYST                  7566
MILLER               CLERK                    7782

已选择14行。

SQL> start E:\SQL\temp.sql

ENAME                JOB                       MGR
-------------------- ------------------ ----------
SMITH                CLERK                    7902
ALLEN                SALESMAN                 7698
WARD                 SALESMAN                 7698
JONES                MANAGER                  7839
MARTIN               SALESMAN                 7698
BLAKE                MANAGER                  7839
CLARK                MANAGER                  7839
SCOTT                ANALYST                  7566
KING                 PRESIDENT
TURNER               SALESMAN                 7698
ADAMS                CLERK                    7788

ENAME                JOB                       MGR
-------------------- ------------------ ----------
JAMES                CLERK                    7698
FORD                 ANALYST                  7566
MILLER               CLERK                    7782

已选择14行。

值得注意的是@命令与脚本文件路径空格可有可无，但是start命令与路径直接必须要有空格。

help命令

帮助命令，使用方法help 命令名，示例如下：

SQL> help set;

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n
   ...

spool命令

当我们想要把控制台的内容输出道文件时候，我们就可以使用此命令，具体如下：

SQL> spool E:\SQL\output
SQL> l
  1  select ename,job,MGR
  2* from emp
SQL> /

ENAME                JOB                       MGR
-------------------- ------------------ ----------
SMITH                CLERK                    7902
ALLEN                SALESMAN                 7698
WARD                 SALESMAN                 7698
JONES                MANAGER                  7839
MARTIN               SALESMAN                 7698
BLAKE                MANAGER                  7839
CLARK                MANAGER                  7839
SCOTT                ANALYST                  7566
KING                 PRESIDENT
TURNER               SALESMAN                 7698
ADAMS                CLERK                    7788

ENAME                JOB                       MGR
-------------------- ------------------ ----------
JAMES                CLERK                    7698
FORD                 ANALYST                  7566
MILLER               CLERK                    7782

已选择14行。

SQL> spool E:\SQL\output
SQL> spool off
SQL> l
  1  select ename,job,MGR
  2* from emp
SQL> /

ENAME                JOB                       MGR
-------------------- ------------------ ----------
SMITH                CLERK                    7902
ALLEN                SALESMAN                 7698
WARD                 SALESMAN                 7698
JONES                MANAGER                  7839
MARTIN               SALESMAN                 7698
BLAKE                MANAGER                  7839
CLARK                MANAGER                  7839
SCOTT                ANALYST                  7566
KING                 PRESIDENT
TURNER               SALESMAN                 7698
ADAMS                CLERK                    7788

ENAME                JOB                       MGR
-------------------- ------------------ ----------
JAMES                CLERK                    7698
FORD                 ANALYST                  7566
MILLER               CLERK                    7782

已选择14行。
-- 先指定输出结果文件的位置
SQL> spool E:\SQL\output
-- 然后在执行SQL语句
SQL> l
  1  select ename,job,MGR
  2* from emp
SQL> /

ENAME                JOB                       MGR
-------------------- ------------------ ----------
SMITH                CLERK                    7902
ALLEN                SALESMAN                 7698
WARD                 SALESMAN                 7698
JONES                MANAGER                  7839
MARTIN               SALESMAN                 7698
BLAKE                MANAGER                  7839
CLARK                MANAGER                  7839
SCOTT                ANALYST                  7566
KING                 PRESIDENT
TURNER               SALESMAN                 7698
ADAMS                CLERK                    7788

ENAME                JOB                       MGR
-------------------- ------------------ ----------
JAMES                CLERK                    7698
FORD                 ANALYST                  7566
MILLER               CLERK                    7782

已选择14行。
-- 最后在关闭文件
SQL> spool off;

将Oracle数据导出给其他系统

创建一个SQL文件，内容如下：

set line 20
set pagesize 10000
-- 查询语句的输出结果不显示列名
set heading OFF
-- 内容输出到data.text文件
spool e:\sql\data.txt
-- 选择输出的数据
select deptno||','||dname||','||loc from dept;
spool off

使用@命令执行刚才的SQL文件，然后数据就写入到data.txt文件中

10,ACCOUNTING,NEW YORK              
20,RESEARCH,DALLAS          
30,SALES,CHICAGO             
40,OPERATIONS,BOSTON

利用AUTOTRACE追踪SQL语句

开启auttrace需要使用管理员账户才可以

-- 查看autotrace状态
SQL> show autotrace
autotrace OFF
-- 开启autotrace
-- 开启追踪，set autotrace off,可关闭追踪
SQL> set autotrace on
SQL> show autotrace
autotrace ON EXPLAIN STATISTICS
-- 执行查询语句
select * from hr.employees;
-- 除了执行结果，还有执行计划内容
执行计划
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       9843  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed

我们也可以关闭查询结果和执行计划，只显示统计信息

-- 关闭统计信息和查询结果，只显示执行计划
set autotrace traceonly explain
-- 关闭统计信息和查询结果，只显示统计信息
set autotrace traceonly statistics

这篇关于Oracle PL/SQL编程入门篇的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

C/C++教程

Oracle PL/SQL编程入门篇

文章目录

Windows启动/关闭Oracle数据库

解锁scott用户

查询Oracle的版本号

使用SQLPlus创建匿名块

常用的SQLPlus命令

查询表结构(DESC)

设置控制台显示字符长度

L命令，显示刚刚输入的SQL缓冲区命令

n text 修改SQL缓冲区命令

N和A命令追加

/执行命令

DEL删除命令

C修改命令

SAVE命令(生成脚本文件)

@运行脚本文件

help命令

spool命令

将Oracle数据导出给其他系统

利用AUTOTRACE追踪SQL语句

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯