存储过程

官方解释

存储过程，百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

理解

存储过程 即是将具有某种特定功能的SQL语句集，存储在数据库中，一次编译后续直接使用不在编译。用户通过存储过程的名字并给出参数来调用存储过程。
优点：
相比于SQL语句调用一次编译一次将大大提高编译效率。
远程调用时，减少大量的SQL语句传输
可重复利用
维护成本低
可以添加限制，安全

存储过程基础

基本结构

Oracle存储过程包含三部分：

1.过程声明2.执行过程部分3.存储过程异常

无参存储过程

1.CREATE OR REPLACE PROCEDURE demo AS/IS变量2 DATE;变量3 NUMBER;2.BEGIN--要处理的业务逻辑3.EXCEPTION--存储过程异常END

有参存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)AS/ISname student.name%TYPE;age number :=20;BEGIN--业务处理.....END

语法

运算符

SELECT INTO STATEMENT语句

CREATE OR REPLACE PROCEDURE DEMO_CDD1 ISs_name VARCHAR2; --学生名称s_age NUMBER;--学生年龄s_address VARCHAR2; --学生籍贯BEGIN--给单个变量赋值SELECT student_address INTO s_addressFROM student where student_grade=100; --给多个变量赋值SELECT student_name,student_age INTO s_name,s_ageFROM student where student_grade=100;--输出成绩为100分的那个学生信息dbms_output.put_line('姓名：'||s_name||',年龄：'||s_age||',籍贯：'||s_address);END

选择语句

IF s_sex=1 THENdbms_output.put_line('这个学生是男生');END IFIF s_sex=1 THENdbms_output.put_line('这个学生是男生');ELSEdbms_output.put_line('这个学生是女生');END IF

循环语句

基本循环
LOOPIF 表达式 THENEXIT;END IFEND LOOP;
while循环
WHILE 表达式 LOOPdbms_output.put_line('haha');END LOOP;
for循环
FOR a in 10 .. 20 LOOPdbms_output.put_line('value of a: ' || a);END LOOP;

游标

概念
  • 游标提供一种从表检索数据并且进行操作的灵活手段
  • 处理客户端到服务端的sql语句，或者是批处理、存储过程、触发器中的数据处理请求
理解
  • 通过游标，PL/SQL程序可以一次处理查询结果集中的某行，对数据进行特定操作
原理
  • PL/SQL块在执行SELECT、INSERT、UPDATE、DELETE语句时，Oracle会在内存中为其分配上下文区（缓冲区），游标是指向该区的一个指针。
  • 游标为应用程序提供了一种具有对多行数据查询结果集中的每一行数据分班进行单独处理的方法
  • 游标分显示游标、隐式游标、REF游标

使用游标

  • 声明游标定义游标的名称和相关的SELECT语句：
CURSOR 游标名（参数1，参数2） IS SELECT 语句 FROM student;
  • 打开游标游标分配内存，使得它准备取的SQL语句转换成它返回的行：
OPEN 游标名（参数1，参数2）;
  • 抓取游标中的数据，可用LIMIT关键字来限制条数，如果没有默认每次抓取一条：
FETCH cur_cdd INTO id, name ;
  • 关闭游标来释放分配的内存:
CLOSE cur_cdd;

PL/sql处理存储过程实操

无参存储过程大致流程：

找到Procedure，并创建新Procedure

图 4.1

创建名字

图 4.2

编写Procedure

图 4.3


编写无参Procedure完成，准备执行

图 4.4

Procedure创建完毕，在左侧导航栏中可以找到

图 4.5

新建commend命令对话框

图 4.6

输入命令，Procedure执行成功

图 4.7

进阶提升

Ps：熟悉存储过程后看，作为技术的再提升部分

场景如下：

有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。

这条sql，写出来如下：

update student set s_grade=s_grade+1

编写存储过程：

(1).存储过程1

名称为：process_student1，student表的s_no字段类型为varchar2(16)。

说明：

把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT，每次从这个游标里抓取10万条数据赋值给数组students，每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。FETCH .. BULK COLLECT INTO .. LIMIT rows语句中：这个rows我测试目前最大可以为10万条。

如果我们这个student表没有主键，也没有索引呢，该怎么来做呢？

分析下：

 ROWNUM是伪列，每次获取结果后，然后在结果集里会产生一列，从1开始排，每次都是从1开始排。ROWID在每个表中，每条记录的ROWID都是唯一的。在这种情况下，我们可以用ROWID。但要注意的是，ROWID是一个类型，注意它和VARCHAR2之间的转换。有两个方法：ROWIDTOCHAR（）是把ROWID类型转换为CHAR类型；CHARTOROWID()是把CAHR类型转换为ROWID类型。

接下来我们编写存储过程process_student2，脚本如下：

说明：

 我们首先查到记录的ROWID并把它转换为CHAR类型，存放到游标CUR_STUDENT里，

再每次抓取10万条数据赋值给数组进行更新，更新语句的WHERE条件时，又把数组元素是CAHR类型的rowid串转换为ROWID类型。

学习过程中借鉴的文章

Oracle 执行存储过程五种方法（带参数& 不带参数）_oracle 调用存储过程_Offset_Ht的博客-CSDN博客

PLSQL 使用教程_xinyuezitang的博客-CSDN博客

Oracle–带参数的存储过程（IN、OUT和IN OUT）_oracle 带参数的存储过程_吴声子夜歌的博客-CSDN博客

ORACLE 存储过程编译时报错PLS-00103_luojinyao2021的博客-CSDN博客

ORACLE存储过程_@coward的博客-CSDN博客

Oracle进阶（二）存储过程（Procedure）

oracle数据库体系架构详解_路途…的博客-CSDN博客

Oracle数据库详解(超详细)_Senfos.W的博客-CSDN博客

超详细centos7安装Oracle11g（图文结合）_centos7 oracle11g安装教程_一只大头piang的博客-CSDN博客