基于Oracle数据库存储过程的创建及调用

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了基于Oracle数据库存储过程的创建及调用，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9255字，纯文字阅读大概需要14分钟。

内容图文

目录

• 基于Oracle数据库存储过程的创建及调用
  • 1.PLSQL编程
    • 1.1 概念和目的
    • 1.2 程序结构
    • 1.3 Hello World
    • 1.4 变量
    • 1.5 流程控制
  • 2 游标
    • 2.1 什么是游标
    • 2.2 语法
    • 2.3 游标的属性
    • 2.4 创建和使用
    • 2.5 带参数的游标
  • 3 存储过程
    • 3.1 概念作用
    • 3.2 语法
    • 3.3 无参存储
    • 3.4 带输入参数的存储过程
    • 3.5 带输出参数的存储过程
    • 3.6 JAVA程序调用存储过程

基于Oracle数据库存储过程的创建及调用

1.PLSQL编程

1.1 概念和目的

什么是PL/SQL？

• PL/SQL（Procedure Language/SQL）
• PLSQL是oracle对sql语言的过程化扩展（类似于Basic）
• 指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语句具有过程处理能力。

1.2 程序结构

通过Plsql developer 工具盒Tset Window创建 程序模板或者用过语句在SQL windows编写。

提示：PLSQL语言的大小写是不区分的

PL/SQL 可以分为三个部分：声明部分、可执行部分、异常处理部分

-- Created on 2019/10/31 by ADMINISTRATOR 
declare 
  -- Local variables here（声明变量，游标）
  i integer;
begin
  -- Test statements here（处理异常）
  
end;

其中declare 部分用来声明变量和游标（结果集类型变量），如果程序中无变量声明可去掉。

1.3 Hello World

begin
  -- Test statements here
  --java system.out.println("hello world");
  dbms_output.put_line('hello world');
  
end;

dbms_output为oracle内置程序包，相当于Java中的System.out,而PUT_LINE()是调用方法，相当于println()方法

在sqlplus中也可以编写运行PLSQL程序

SQL>begin
  -- Test statements here
  --java system.out.println("hello world");
  dbms_output.put_line('hello world');
  
end;
/

执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的，我们需要开启一下 set serveroutput on

SQL>set serveroutput on
SQL>begin
  -- Test statements here
  --java system.out.println("hello world");
  dbms_output.put_line('hello world');
end;
/

SQLPLUS中执行PLSQL程序，需要在程序最后添加一个/标识程序的结束。

1.4 变量

PLSQL编程中常见的变量分为两大类：

1.普通数据类型（char，varchar2，date，number，boolean，long）

2.特殊变量类型（引用型变量、记录型变量）

声明变量的方式为

1 变量名 变量类型（变量长度）  例如： v_name varchar2（20）；

1.4.1 普通变量

变量赋值的方式有两种

1.直接赋值语句 := 比如 ： v_name :='zhangsan'

2.语句赋值，使用select... into... 赋值: (语法select 值 into 变量)；

【示例】 打印人员个人信息，包括：姓名、薪水、地址

--打印人员个人信息，包括：姓名、薪水、地址
-- Created on 2019/10/31 by ADMINISTRATOR 
declare 
  v_name varchar(20) :='张三';
  v_val number;
  v_addr varchar(300);
begin
  v_val :='10000';
  select '合肥' into v_addr from dual;
  dbms_output.put_line('姓名：'||v_name||',薪水:'||v_val||',地址:'||v_addr);
end;

1.4.2 引用型变量

变量的类型和长度取决于表中字段的类型和长度

通过表名.列名%TYPE指定变量的类型和长度，例如 v_name emp.ename%TYPE;

【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
declare
  --姓名
  v_name emp.ename%TYPE;
  --薪水
  v_sal emp.sal%TYPE;
begin
--查询表中的姓名和薪水并赋值给变量
--注意查询的字段和赋值的变量的顺序、个数、类型要一致
  select ename, sal
    into v_name, v_sal
    from emp
   where empno = 7839;
   -- 打印输出
   dbms_output.put_line('姓名：' || v_name || ',薪水:' || v_sal);

end;

引用型变量的好处：

使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

1.4.3 记录型变量

接收表中的一整行记录，相当于java中的一个对象

语法：变量名称 表名%ROWTYPE, 例如 v_emp emp%ROWTYPE

【示例】

查询并打印7839号员工的姓名和薪水

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
declare
--记录型变量接收一行
   v_emp EMP%TYPE
begin
  --记录型变量默认接收表中的一行数据，不能指定字段
  select *
    into v_emp
    from emp
   where empno = 7839
   dbms_output.put_line('姓名：' || v_emp.ename || ',薪水:' || v_emp.sal);

end;

优点：如果有一个表，有100个字段，那么你程序如果要使用这100字段话，如果你使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题

错误的使用

1.记录型变量只能存储一个完整的行数据。

2.返回的行太多，也记录不了

1.5 流程控制

1.5.1 条件分支

语法：

BEGIN 
IF 条件1 then 执行1
 elsif 条件 2 then 执行2
 else 执行3
 end if；
 end；

注意关键字：ELSIF

【示例】判断emp表中是否超过20条，10-20之间，或者10条以下

-- 判断emp表中是否超过20条，10-20之间，或者10条以下
declare
  --声明变量接收emp表中的数量
  v_count number;
begin
--查询emp表中的记录数赋值给变量
  select count(1) into v_count from emp;
--判断打印
  if v_count > 20 then
    dbms_output.put_line('emp表中的记录数据超过20条为：' || v_count);
  elsif v_count >= 10 then
    dbms_output.put_line('emp表中的记录数为10-20之间，为：' || v_count);
  else
    dbms_output.put_line('emp表中的记录数为10以下，为：' || v_count);
  end if;

end;

1.5.2 循环

在oracle中有三种循环方式，只介绍一种：loop循环

语法：

begin 
    loop
        exit when 退出循环条件
    end loop;
end;

【示例】 打印数字1-10

declare
  --声明循环变量并赋初值
  v_num number :=1;
begin
   loop
     exit when v_num >10;
     dbms_output.put_line(v_num);
    -- 循环变量自增
     v_num :=v_num+1;
   end loop;

end;

2 游标

2.1 什么是游标

用于临时存储一个查询返回的多行数据（结果集，类似于java的jdbc链接返回额ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：声明-->打开-->读取-->关闭

2.2 语法

游标声明：

CURSOR 游标名[(参数列表)] IS 查询语句；

游标的打开：

open 游标名；

游标的取值；

FETCH 游标名 INTO 变量列表；

游标的关闭：

CLOSE 游标名；

2.3 游标的属性

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真，否则为假

其中%NOTFOUND 是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环。

2.4 创建和使用

【示例】使用游标查询emp表中所有员工的姓名和工资，并且将其依次打印出来

--使用游标查询emp表中所有员工的姓名和工作，并将其依次打印出来
declare
  --声明游标
  CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP;
  
  --声明变量用来接收游标中的元素
  V_NAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
begin
  --打开游标
  OPEN C_EMP;
  
  --遍历游标中的值
  LOOP
    --通过FETCH语句获取游标中的值并赋值给变量
    FETCH E_EMP
          INTO V_NAME,V_SAL;
    --通过%NOTFOUND判断是否有值，有值打印，没有则则退出循环
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.put_line('姓名：'||V_NAME||'，薪水：'V_SAL);
  END LOOP;
  --关闭游标
  CLOSE C_EMP;
END;

执行结果：

2.5 带参数的游标

【示例】查询某个部门的所有员工的姓名和工作，并将其依次打印出来

--使用游标查询emp表中所有员工的姓名和工作，并将其依次打印出来
declare
  --声明游标 添加判断语句WHERE deptno=v_deptno 并且赋值给游标C_EMP
  CURSOR C_EMP(v_deptno emp.deptno%TYPE) IS SELECT ENAME,SAL FROM EMP WHERE deptno=v_deptno;
  
  --声明变量用来接收游标中的元素
  V_NAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
begin
  --打开游标
  OPEN C_EMP(10);
  
  --遍历游标中的值
  LOOP
    --通过FETCH语句获取游标中的值并赋值给变量
    FETCH E_EMP
          INTO V_NAME,V_SAL;
    --通过%NOTFOUND判断是否有值，有值打印，没有则则退出循环
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.put_line('姓名：'||V_NAME||'，薪水：'V_SAL);
  END LOOP;
  --关闭游标
  CLOSE C_EMP;
END;

注意：%NOTFOUND 属性默认值为Flase，所以在循环中要注意判断条件的位置，如果先判断在FETCH会导致最后一条记录的值被打印两次（多循环一次默认）；

3 存储过程

3.1 概念作用

上面我们编写的PLSQL程序可以进行表的操作，判断，循环逻辑处理的工作，但无法重复调用。

可以理解之前的代码全都编写在了main方法中，是匿名程序，JAVA可以通过封装对象和方法来解决复用问题

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用，这些被存储起来的的PLSQL程序称之为存储过程。

存储过程作用

1.在开发程序中，为了一个特定的业务功能，会向数据库进行 多次连接关闭（连接和关闭是很耗费资源），需要对数据库进行多次的I/O读写，性能比较低。如果把这些业务放到PL/SQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务，可以大大提高效率。

2.ORACLE官方给的建议：能够让数据库操作的不要放在程序中，在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误（如果在数据库中操作数据，可以有一定的日志恢复等功能）、

3.2 语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END[过程名称];

根据参数的类型，可以分为三种

不带参数的

带输入参数的

带输入输出参数（返回值）的

3.3 无参存储

3.3.1 创建存储

通过Plsql Developer 或者语句创建存储过程：

【示例】通过调用存储过程打印hello world

创建存储过程：

--通过调用存储过程打印hello world
create or replace procedure P_HELLO AS 
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello world');
END 

通过工具查看创建好的存储过程；

3.3.2 调用存储过程

1.通过PLSQL程序可调用：

begin
    --直接输入调用存储过程的名称
    P_HELLO
end

2.在sqlplus中通过exec命令调用：

提示：sqlplus中显示结果的前提是需要set serveroutput on

注意：

第一个问题：is和as是可以互用的，用哪个都没关系的

第二个问题：过程中没有declare关键字，declare用在语句块中。

3.4 带输入参数的存储过程

【实例】查询并打印某个员工的姓名和薪水--存储过程：要求，调用额时候传入员工编号，自动控制台打印

--查询并打印某个员工的姓名和薪水--存储过程：要求，调用额时候传入员工编号，自动控制台打印
create or replace procedure p_querynameandsal(i_empno in emp.empno%TYPE) AS 
--声明变量
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin 
--查询emp表中某个员工的姓名和薪水并赋值给变量
select ename,sal into  v_name,v_sal from emp where empno=i_empno;
dbms_output.put_line('姓名：'||v_name||'，薪水：'||v_sal)；
END p_querynameandsal;

调用

begin
    p_querynameandsal(7839);
end

3.5 带输出参数的存储过程

【示例】输入员工号查询某个员工信息，要求将薪水作为返回值输出，给调用的程序使用。

--输入员工号查询某个员工信息，要求将薪水作为返回值输出，给调用的程序使用。
create or replace procedure p_querysal_out(i_empno in emp.empno%TYPE,o_sal out emp.sal%TYPE) AS 

begin 
--查询emp表中某个员工的姓名和薪水并赋值给变量
select sal into  o_sal from emp where empno=i_empno;
END p_querysal_out;

PL/SQL调用

declare
v_sal emp.sal%TYPE;
begin
    p_querysal_out(7839,v_sal);
    dbms_output.put_line(v_sall);
end

3.6 JAVA程序调用存储过程

内容总结

以上是互联网集市为您收集整理的基于Oracle数据库存储过程的创建及调用全部内容，希望文章能够帮你解决基于Oracle数据库存储过程的创建及调用所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---