Oracle & PL/SQL 存储过程

最新推荐文章于 2024-10-12 23:21:07 发布

Jancy丶

最新推荐文章于 2024-10-12 23:21:07 发布

阅读量994

点赞数

分类专栏：工作必修文章标签： oracle sql 数据库存储过程 plsql

本文链接：https://blog.csdn.net/qq_43175022/article/details/120186371

版权

工作必修专栏收录该内容

13 篇文章 0 订阅

订阅专栏

Oracle PL/SQL 存储过程

Oracle

简介：

是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小微机环境。它是一种高效率的、可靠性好的、适应高吞吐量的数据库方案。
oracle安装

Oracle一般安装于公司服务器中，更多应该关注于客户端连接数据方法。
oracle连接
1. 下载oracle客户端连接程序
  
  https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
  - 以下午的经验看，12.1的版本最常见。至于是64bit还是32bit要看服务器还有你自己的PL/SQL Developer程序的位数，如果按照方式流程全走完还是连接不成功则考虑更换位数版本。
2. 安装并配置instant-client
  1. 解压至你自己想解压的目录则完成安装
  2. 根据官网要求，在“环境变量”→“系统变量”→“path”中添加配置instant-client的路径（就是你刚刚解压的路径）
  3. 配置tnsname.ora
    1. 在instant-client根目录创建用于存放tnsname.ora的目录，官方推荐为~\network\admin目录下,我个人配置为~config；
    2. 在以上路径创建tnsname.ora文件，并填入以下信息，第一行为tnsname.ora全路径信息,第二段开始为自定义连接信息
```
# tnsnames.ora Network Configuration File: D:\Oracle\instantclient_12_1\config\tnsnames.ora
# Generated by Oracle configuration tools.


orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
    )
   (CONNECT_DATA =
      (SERVICE_NAME = orcl )
    )
 )


test-oracle =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XXXX)
    )
  )
```
      - 其中，第一段内容解释如下
        ORCL这个名字是由你自己取的,客户端是通过这个名字来获得服务器的登录信息的。
        PROTOCOL是指定的通信协议,oracle是通过TCP/IP通信的。所以这里写TCP就好。
        HOST的话如果客户端和数据库在同一台电脑可以用localhost也可以用IP。如果不同电脑就必须用IP了,是oracle所在电脑的IP。
        PORT一般都是1521
        SERVER是dedicated检测模式的监听。(这个我也不是很清楚,还有一中是请求还是怎么样)
        service_name是oracle的SID
  4. 配置环境变量使之生效
    
    在“环境变量”→“系统变量”中新建一个变量，name为TNS_ADMIN，路径为存放tnsname.ora的目录，如本例中为config
  5. 客户端软件连接
    
    推荐使用PL/SQL Developer连接使用。如何使用及配置见下文
PL/SQL Developer连接和使用
1. 连接配置
  
  参考此博客：https://blog.csdn.net/master_yao/article/details/51055850，instant-client配置请忽略上文已讲
2. 简单使用
  1. 打开软件连接成功后，首先查看左侧栏，选中Objects栏，最大化，只看它即可
  2. 该窗口中，要显示全部可查看内容，则下拉选项中，选择所有用户和All Objects；选择My Objects则显示自己定义的库内容；
  3. 该窗口中，主要要查看的信息有
    - Functions：存储函数
    - Procedures：存储过程
    - Tables：表
    - Indexs：索引
    - Sequences：序列（类似于MySQL的主键自增功能）
    - Users：用户
    - Tablespaces：表空间
      - 还有两个不知道有没有用的信息：Packages和Types
  4. 对具体存储函数和存储过程，右键可执行，编辑等，如有权限还可以测试
  5. 对表、索引等操作比较常规就不过多说
  6. 右侧大片空白区域，右键可新建SQL窗口用于书写SQL语句，选中要执行的语句点击绿色三角符号即可执行
Oracle操作：

包括表的CRUD，属性的CRUD、单\多行函数（count之类的）、条件表达式、分组查询、多表查询、连接、分页查询、视图、索引等，与常规SQl相似，就不过多学习，遇到问题现学。

学习链接：https://www.bilibili.com/video/BV1WZ4y1H7du?p=543
触发器：略。学习链接：https://www.bilibili.com/video/BV1WZ4y1H7du?p=566
java使用存储过程和存储函数
1. 常规的java配置数据库环境的配置
2. 在调用方法时，替换为存储过程/函数即可

PL/SQL

https://www.bilibili.com/video/BV14W411u7wT?t=18&p=34

简介：

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。
官方文档：

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/overview.html#GUID-2FBCFBBE-6B42-4DB8-83F3-55B63B75B1EB

PL/SQL 块的基本结构

<< label >> (optional)	--<<标签>>（可选）
DECLARE    -- Declarative part (optional)声明部分（可选）
  -- Declarations of local types, variables, & subprograms局部类型，变量和子程序的声明

BEGIN      -- Executable part (required)可执行部分（必填）
  -- Statements (which can use items declared in declarative part)语句（可以使用声明部分中声明的项目）

[EXCEPTION -- Exception-handling part (optional)处理部分（可选）
  -- Exception handlers for exceptions (errors) raised in executable part在可执行文件中提出的例外（错误）的异常处理程序
]
END;

三类控制语句
1. 条件选择：https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-control-statements.html#GUID-B7DD4E62-3ED2-41E9-AAE5-90A78788BB31
2. 循环语句：https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-control-statements.html#GUID-4CD12F11-2DB8-4B0D-9E04-DF983DCF9358
3. Sequential control statements顺序控制语句：https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-control-statements.html#GUID-7CA9E984-CCB3-4DB8-8D0C-30BF0C74BA83
游标

游标是指向私有 SQL 区域的指针，该区域存储有关处理特定 SQL 语句或 PL/SQL SELECT INTO 语句的信息。

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/static-sql.html#GUID-F1FE15F9-5C96-4C4E-B240-B7363D25A8F1

异常和触发器

异常

异常类型
- Internally Defined Exceptions
  
  内部定义的异常
- Predefined Exceptions
  
  预定义的异常
- User-Defined Exceptions
  
  用户定义异常

异常区分

Category 类别	Definer 定义者	Has Error Code 有错误代码	Has Name 有名字	Raised Implicitly 隐式声明	Raised Explicitly 显示声明
Internally defined内部定义	Runtime system	Always	Only if you assign one除非你指派一个	Yes	Optionally支持
Predefined预定义的	Runtime system	Always	Always	Yes	Optionally支持
User-defined用户自定义	User	Only if you assign one除非你指派一个	Always	No	Always

异常处理部分结构

EXCEPTION
  WHEN ex_name_1 THEN statements_1                 -- Exception handler
  WHEN ex_name_2 OR ex_name_3 THEN statements_2  -- Exception handler
  WHEN OTHERS THEN statements_3                      -- Exception handler
END;
--WHEN OTHERS THEN一定在最后

内部定异常语句

DECLARE
  deadlock_detected EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);	-- -60为内部异常，可在文档找到内部异常含义
BEGIN
  ...
EXCEPTION
  WHEN deadlock_detected THEN
    ...
END;

用户自定义异常语句

CREATE PROCEDURE account_status (
  due_date DATE,
  today    DATE
) AUTHID DEFINER
IS
  past_due  EXCEPTION;  -- declare exception
BEGIN
  IF due_date < today THEN
    RAISE past_due;  -- explicitly raise exception
  END IF;
EXCEPTION
  WHEN past_due THEN  -- handle exception
    DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;

关键：RAISE关键字抛出异常

触发器：略，需要再学

存储过程

见下方存储过程

简单实例：https://www.bilibili.com/video/BV14W411u7wT?p=41

存储过程

简介：存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含逻辑控制语句和数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;

优缺点：https://blog.csdn.net/tojohnonly/article/details/70738629

Oracle存储过程

https://www.bilibili.com/video/BV1WZ4y1H7du?p=562，简单演示视频
比较全面的博客总结：https://blog.csdn.net/weixin_41968788/article/details/83659164/
Open for语句与游标的使用：https://blog.csdn.net/keven2840/article/details/77870465/
异常：https://blog.csdn.net/qq_34745941/article/details/82846820
- 抛出异常后，PL/SQL 块或子程序的正常执行将停止，并将控制转移到其异常处理部分（即捕获异常代码块）：
```
EXCEPTION
   WHEN exception_name1 THEN  -- handler
      sequence_of_statements1
   WHEN exception_name2 THEN  -- another handler
      sequence_of_statements2
   ...
   WHEN OTHERS THEN           -- optional handler
      sequence_of_statements3
END;
```
  原文：https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/07_errs.htm
  
  When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part, which is formatted as follows:

MySQL存储过程

官方文档：https://dev.mysql.com/doc/refman/5.7/en/stored-objects.html
非常全面的总结：https://www.runoob.com/w3cnote/mysql-stored-procedure.html
游标：

https://blog.csdn.net/liguo9860/article/details/50848216

https://blog.csdn.net/weixin_42981419/article/details/86162179

Oracle存储过程向MySQL转换

全面的博客总结：
- https://blog.csdn.net/shy_snow/article/details/78262644
- https://www.cnblogs.com/HondaHsu/p/3641258.html
- https://www.cnblogs.com/loveismile/p/5030911.html
oracle的SYS_REFCURSOR游标，在MySQL的转换方案
1. https://forums.mysql.com/read.php?10,434966,434966#msg-434966
2. https://blog.csdn.net/weixin_35755188/article/details/114341979