1、定义

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

2.创建存储过程和执行

参数的几种类型:

in 是参数的默认模式，在程序运行的时候已经具有值，在程序体中值不会改变。
out 模式定义的参数只能在过程体内部赋值，可以接收值并返回。
in out 表示高参数可以向该过程中传递值，也可以将某个值传出去

create or replace procedure 存储过程名（xx in int, xxxx in out varchar, x out varchar2……）
as
--在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量
begin
--这里可以给变量赋值，执行sql语句等
end;

注：AS, IS 创建在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。
as：关键字。
begin：关键字。
end;关键字。

创建和运行图示

2.1无参数方式和执行

--创建存储过程wenxiao
create or replace procedure wenxiao
as
begin
  dbms_output.put_line('hello word');
--insert into a(……) select ……from b;
end;

--dbms_output.put_line(‘hello word’); 输出内容。
--方式一：PL/SQL环境下，左侧Procedure下找到存储过程-右键test-执行。图2
--方式二：命令执行：
--1.sql窗口执行 --输出结果'hello word'
begin 
wenxiao()；--()没有参数可以不带。图3
end；
--2.sql窗口执行 输出结果'hello word'
call wenxiao()；

2.2.1有参数方式和执行，都是in 的方式

--创建存储过程wenxiao1--这里参数并未使用，但不影响结果
create or replace procedure wenxiao1(name in varchar2,age  in int)
as
begin
  dbms_output.put_line(name);
end;
方式一：PL/SQL环境下，左侧Procedure下找到存储过程-右键test并输入in的参数
（参数不输入默认为空）-执行。一般参数设置都是有用的，需要输入
方式二：命令执行：
--1.sql窗口 --输出结果'小狗'
begin 
wenxiao('小狗',9)；
end；
--2.sql窗口 --输出结果'小狗'
call wenxiao('小狗',9)；

2.3.1有参数方式和执行，in，out都有方式

--创建存储过程wenxiao2
create or replace procedure wenxiao2(name in varchar2,age out int)
as
begin
  dbms_output.put_line('hello word');
end;
方式一：PL/SQL环境下，左侧Procedure下找到存储过程-右键test并输入in的参数
（参数不输入默认为空）-执行。一般参数设置都是有用的，需要输入
方式二：命令执行：
----sql窗口---输出结果'hello word' --age是输出参数被声明接收参数即可
declare 
xx int;
begin
wenxiao2('小狗',xx);
end;

2.4linux调用执行

linux 环境下：定时任务配置

--分 小时 天 月 * 路径下的shell脚本和日志脚本
39 18 13 12 * cd /home/oracle/etl && sh bbxr.sh > bbxr.log 2>&1

--linux bbxr.sh脚本，log脚本为空--这是固定日期方式，还有通过linux灵活传参方式
#!/bin/bash
source ~/.bash_profile
username='用户名'
password='密码'
sqlplus -S ${username}/${password}@EAST-PRD <<EOF
set sqlblankline on;
declare
v_ca number;
v_re varchar2(200);
begin
#v_re='20231231'
#SP_XX(v_re,v_ca);
SP_XX('20231231',v_ca);
end;
/
exit;
/
EOF

3存储过程变量声明

3.1直接声明数据类型

格式：变量名数据类型(大小) ；v_name varchar2(20); v_age number;

字段类型：oracle 数据库认识的类型都可以直接用.

注：存储过程中声明变量必须在begin之前并且声明字符串时必须指定大小，不然会报错

3.2使用%TYPE声明

格式：变量名表名.字段名%TYPE 。STUDENT.NAME%TYPE;

含义：该变量的数据类型与指定表的指定字段的数据类型一致。

优点：当定义多个变量或常量时，只要前面使用过的数据类型，后边的变量就可以利用%TYPE 引用，最常见的就是把表中的字段类型作为变量或常量的数据类型。利用%TYPE 方式定义的变量类型和被引用的常量数据类型保持一致，如果以前是VARCHAR 类型，现在要改成NUMBER 类型，那就只需要直接改数据表中的类型即可，对已写好的存储过程没有影响。
存储过程基本都会有表操作，这就避免了在数据传入的时候，引发数据溢出或不符的情况。

3.3使用%ROWTYPE声明

格式：变量名表名%ROWTYPE。STUDENT%ROWTYPE;

含义：该变量的数据类型与指定表的指定行记录（所有字段）的数据类型一致

3.4给变量赋值的方式

变量赋值的方式有两种：

直接赋值语句 := 比如: v_name := ‘zhangsan’
语句赋值，使用select …into … 赋值：（语法 select 值 into 变量),只能单行。

--创建存储过程wenxiao3--输出结果：'小狗23424'
create or replace procedure wenxiao3(name in varchar2,age out int)
as
v_name varchar2(20);
v_name1 student.name%type;
v_age student%type;
---可直接赋值但不建议使用
v_id varchar2(20) :='23424'
begin
--给变量赋值
v_name :='小狗';
  dbms_output.put_line(v_name||v_id);
end;

4.存储过程中的异常处理举例

create or replace procedure xiaogou3
as
begin
  dbms_output.put_line('age');
  --异常
  exception when others then
    dbms_output.put_line('error');
end;

总结：存储过程创建就完成了

玖叶教程网

前端编程开发入门

Oracle存储过程和调用（oracle存储过程调用函数）