Oracle PL/SQL编程入门篇（oracle数据库编程经典300例）
mhr18 2024-09-15 21:36 22 浏览 0 评论
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相关推荐
- 重大故障!业务核心表被truncate删除，准备跑路……
-
完蛋！最近项目组同事说，核心业务表被truncate删除，也惊呆了，现在的业务都这么粗暴了，安全机制、作业规范形同虚设？接下来就给大家一起聊下，表被truncate,解决问题的关键就是找到关键的问题！...
- 数据孤岛(Data Silos)（数据孤岛解决方案）
-
数据孤岛（DataSilos）是指组织内部数据因技术、管理或文化壁垒被隔离在不同部门、系统或平台中，无法有效共享、整合和交互的状态。这些数据如同分散的“岛屿”，虽各自有价值，但因孤立性难以发挥整体效...
- 弱口令漏洞总结及研究（弱口令是高危漏洞吗）
-
1.1弱口令漏洞定义弱口令漏洞是指因使用简单、易猜测、易破解的密码导致的系统安全风险。常见的弱口令包括：默认密码：如admin/admin、root/123456。规律字符：如连续数字（123456）...
- SQL 逻辑处理核心知识点全解析，从入门到精通看这篇就够了!
-
刚入行程序员/数据分析？写SQL总卡壳？别愁！今天一次性把SQL逻辑处理核心技能讲透，涵盖数值、字符、日期函数+条件表达式，附超全数据库差异对比，工作中直接套用！一、SQL函数大揭秘！...
- 智能文档检索:AI语义搜索实践（智能语音检索）
-
以下是基于AI语义搜索的智能文档检索实践要点解析，结合行业前沿案例与技术框架：一、核心技术架构与实现路径语义理解与嵌入模型采用BERT、GPT等预训练模型进行文本向量化，捕捉上下文语义15通过多模态分...
- 国产IAutodraw办公工具更新到3.7.5!这些隐藏功能你知道吗?
-
大家好，IAuto流程软件（简称IAutodraw）终于迎来2025年的第一个版本更新，本次更新同时包含Window、Mac等操作系统，更新的内容包括：1、流程画布支持Mermaid语法的粘贴（mer...
- 企业复杂数据处理必备:五款主流好用的报表工具
-
1.山海鲸报表山海鲸报表（免费的零代码BI报表工具，为您提供BI报表+GIS+数字孪生+可视化大屏一体化服务）作为国产报表工具中的后起之秀，专注于为企业提供高效、便捷的数据可视化与分析方案。它最大的...
- Oracle数据库批量更新翻车!老司机3招省80%时间
-
开篇热梗：凌晨3点，程序员老张盯着屏幕上转不停的进度条，第N次猛灌咖啡——10万条数据更新跑了半小时还没完！突然“啪”的一声，屏幕黑了...（你的系统是不是也这样崩溃过？评论区扣1）终极总结：3句话讲...
- 硬盘smart健康深度检测工具——wgcloud
-
S.M.A.R.T的全称为“Self-MonitoringAnalysisandReportingTechnology”，上个世纪九十年代，由于经常发生硬盘莫名其妙的故障问题，导致很多用户的数...
- Oracle 数据库培训:让你从菜鸟到大师的华丽转身
-
深入学习理论知识基础教程：介绍数据库管理系统的基本原理，让学员理解数据库的工作机制。讲解Oracle数据库的安装过程，包括各种环境配置和参数设置。教授如何使用SQL语言进行基本操作，如数据的查...
- 从 0 到 1:30 天变身 Oracle 数据库大师的独家学习秘籍
-
基础理论学习数据库基础概念：学习数据库的基本概念，如数据库管理系统表、行、列、索引、事务等。可以阅读《数据库系统概念》这样的经典教材，系统地掌握数据库的基础知识。SQL语言：SQL是与数据库交互的...
- 数据库故障排查指南（数据库故障处理流程）
-
数据库故障排查指南大纲常见故障类型与症状连接问题：无法连接、连接超时、连接数过多性能问题：查询缓慢、CPU/内存占用过高、锁等待数据一致性问题：数据丢失、数据损坏、主从不一致日志与错误消息：关键错误日...
- Java性能监控工具大揭秘:全面掌握你的应用表现
-
Java性能监控工具大揭秘：全面掌握你的应用表现在Java开发的世界里，性能优化如同一场永无止境的马拉松。一个程序再优雅的设计，如果执行效率低下，也会让人头疼不已。而性能监控工具就像是我们身边的“跑表...
- jmap（jmap -heap详解）
-
jmap是JDK自带的一个命令行工具，用于生成Java进程的堆内存快照（heapdump），也可以查看Java堆内存的详细统计信息。它是排查内存泄漏、分析对象占用内存情况的重要工具之...
- Java性能监控工具:让程序跑得更快的秘密武器
-
Java性能监控工具：让程序跑得更快的秘密武器大家好呀！今天咱们来聊聊Java程序背后的“幕后英雄”——性能监控工具。这些工具就像是医生手中的听诊器，能够帮我们及时发现程序运行中的各种“健康问题”。无...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle位图索引 (63)
- oracle批量插入数据 (62)
- oracle事务隔离级别 (53)
- oracle 空为0 (50)
- oracle主从同步 (55)
- oracle 乐观锁 (51)
- redis 命令 (78)
- php redis (88)
- redis 存储 (66)
- redis 锁 (69)
- 启动 redis (66)
- redis 时间 (56)
- redis 删除 (67)
- redis内存 (57)
- redis并发 (52)
- redis 主从 (69)
- redis 订阅 (51)
- redis 登录 (54)
- redis 面试 (58)
- 阿里 redis (59)
- redis 搭建 (53)
- redis的缓存 (55)
- lua redis (58)
- redis 连接池 (61)
- redis 限流 (51)