Oracle存储过程和调用

欢迎大家来到IT世界,在知识的湖畔探索吧!

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;关键字。

欢迎大家来到IT世界,在知识的湖畔探索吧!

创建和运行图示

欢迎大家来到IT世界,在知识的湖畔探索吧!

图1创建存储过程

图2右键存储过程-test-执行

图3 sql窗口调用存储过程

图4 命令执行结果

2.1无参数方式和执行

欢迎大家来到IT世界,在知识的湖畔探索吧!--创建存储过程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都有方式

欢迎大家来到IT世界,在知识的湖畔探索吧!--创建存储过程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

欢迎大家来到IT世界,在知识的湖畔探索吧!--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='' #SP_XX(v_re,v_ca); SP_XX('',v_ca); end; / exit; / EOF 

3存储过程变量声明

3.1直接声明数据类型

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

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

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

3.2使用%TYPE声明

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

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

3.3使用%ROWTYPE声明

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

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

3.4给变量赋值的方式

变量赋值的方式有两种：

1. 直接赋值语句 := 比如: v_name := ‘zhangsan’
2. 语句赋值，使用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.存储过程中的异常处理举例

欢迎大家来到IT世界,在知识的湖畔探索吧!create or replace procedure xiaogou3 as begin dbms_output.put_line('age'); --异常 exception when others then dbms_output.put_line('error'); end;

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