10-Oracle存储过程（创建，修改，使用及管理）

CSDN专家-赖老师（软件之家）

已于 2023-03-12 16:58:33 修改

阅读量1.6k

点赞数 1

分类专栏： Oracle学习笔记文章标签： oracle 存储过程定义存储过程调用 java调用存储过程

于 2023-03-11 08:45:08 首次发布

本文链接：https://blog.csdn.net/software7503/article/details/129439963

版权

Oracle学习笔记专栏收录该内容

13 篇文章 2 订阅

订阅专栏

本章内容

1、我们为什么要用存储过程？

2、存储过程是如何定义和维护的？

3、我们如何调用存储过程？

4、存储过程中常用的复合数据处理方式及CTE

5、存储过程如何进行异常处理？

6、存储过程如何进行事务处理？

7、我们应如何优化存储过程？

1、我们为什么要用存储过程？

1.存储过程是指用于特定操作的PL/SQL块，是由流控制和SQL语句书写的过程

2.存储过程经编译和SQL优化后存储在数据库服务器中，使用时只要调用即可。在Oracle数据库中，若干个有联系的存储过程，可以组合在一起构成包

存储过程具有如下特点：

1 ）存储过程是预编译过的，并且经优化后存储于 SQL 内存中，使用时无需再次编译，提高了工作效率；

2 ）存储过程的代码直接存放于数据库中，一般由客户端直接通过存储过程的名字进行调用，减少了网络流量，加快了系统执行速度，例如在进行百万以上的大批量数据查询时，使用存储过程分页要比其他方式分页快得多；

3 ）使用存储过程可以减少 SQL 注入式攻击，提高了系统的安全性，执行存储过程的用户要具有一定的权限才能使用存储过程，没有数据操作权限的用户只能在其控制下间接地存取数据；

4）在同时进行主、从表及多表间的数据维护及有效性验证时，使用存储过程比较方便，而且可以有效利用SQL中的事务处理的机制；

5）使用存储过程，可以实现存储过程设计和编码工作分开进行，只要将存储过程名、参数、及返回信息告诉编码人员即可；

6）但使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改存储过程的参数或者其返回的数据及类型的话，需要修改应用程序的相关代码，比较繁琐。

存储过程的创建

• 基本语法

CREATE [OR REPLACE] PROCEDURE procedure_name

(parameter1_name [mode] datatype

    [DEFAULT|:=value]

[, parameter2_name [mode] datatype

    [DEFAULT|:=value], … ])

AS|IS

   /*Declarative section is here */

BEGIN

   /*Executable section is here*/

EXCEPTION

   /*Exception section is here*/

•参数说明

         参数的模式

        IN（默认参数模式）表示当过程被调用时，实参值被传递给形参；在过程

        内，形参起常量作用，只能读该参数，而不能修改该参数；当子程序调用

        结、束返回调用环境时，实参没有被改变。IN模式参数可以是常量或表达

        式。

        OUT表示当过程被调用时，实参值被忽略；在过程内，形参起未初始化的

        PL/SQL 变量的作用，初始值为 NULL ，可以进行读 / 写操作；当子程序调用结

        束后返回调用环境时，形参值被赋给实参。OUT 模式参数只能是变量，不能

        是常量或表达式。

        IN OUT表示当过程被调用时，实参值被传递给形参；在过程内，形参起已初

        始化的PL/SQL 变量的作用，可读可写；当子程序调用结束返回调用环境时，

        形参值被赋给实参。IN OUT 模式参数只能是变量，不能是常量或表达式。

参数的限制

        • 在声明形参时，不能定义形参的长度或精度、刻度，它们是作为参数传递机

        制的一部分被传递的，是由实参决定的。

参数传递方式

        当子程序被调用时，实参与形参之间值的传递方式取决于参数的模式。IN参

        数为引用传递，即实参的指针被传递给形参；OUT ， IN OUT 参数为值传递，

        即实参的值被复制给形参。

参数默认值

        可以为参数设置默认值，这样存储过程被调用时如果没有给该参数传递值，

        则采用默认值。需要注意，有默认值的参数应该放在参数列表的最后。

2、存储过程是如何进行定义和维护的？

CREATE OR REPLACE PROCEDURE USP_OutTime
IS
BEGIN
	DBMS_OUTPUT.PUT_LINE(SYSDATE);
END USP_OutTime;

CREATE OR REPLACE PROCEDURE USP_Learing
(
	p_para1			varchar2 := '参数一',
	p_para2			nvarchar2 default '参数二',
	p_para3	    out		varchar2,
	p_para4	    in out 	varchar2
)
IS
BEGIN
DECLARE
	v_para5			varchar2(20);
BEGIN
	v_para5 := '输入输出：'||p_para4;
	p_para3 := '输出：'||p_para1||p_para2;
	p_para4 := v_para5;
END;
END USP_Learing;

存储过程的维护：

1）删除存储过程

DROP PROCEDURE procedure_name;

2）编译存储过程

ALTER PROCEDURE procedure_name COMPILE;

3）与存储过程相关的几个查询

--查看无效的存储过程

SELECT    object_name

FROM       USER_OBJECTS

WHERE    STATUS='INVALID'

AND    OBJECT_TYPE='PROCEDURE'

--查看存储过程的代码

SELECT   TEXT

FROM       USER_SOURCE

WHERE    NAME= procedure_name

其中： procedure_name是存储过程的名字

创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。

CREATE OR REPLACE PROCEDURE show_emp(
p_deptno emp.deptno%TYPE)
AS
  v_sal emp.sal%TYPE;
BEGIN
SELECT avg(sal) INTO v_sal FROM emp 
    WHERE deptno=p_deptno;
DBMS_OUTPUT.PUT_LINE(p_deptno||' '||'average salary is:'|| 
                                      v_sal);
FOR v_emp IN (SELECT * FROM emp WHERE 
                              deptno=p_deptno AND sal>v_sal) LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||
                                           v_emp.ename);
END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('The department doesn’’t exists!');
END show_emp;

通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用 OUT 或 IN OUT 模式参数来实现。

创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。

CREATE OR REPLACE PROCEDURE return_deptinfo(
p_deptno emp.deptno%TYPE,
p_avgsal OUT emp.sal%TYPE,
p_count  OUT emp.sal%TYPE)
AS
BEGIN
     SELECT avg(sal),count(*) INTO p_avgsal,p_count 
     FROM emp 
     WHERE deptno=p_deptno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('The department don’’t exists!');
END return_deptinfo;

3、如何调用存储过程

1、当在SQL*PLUS中调用存储过程时，需要使用CALL或EXECUTE命令，而在PL/SQL块中可以直接引用。当调用存储过程时，如果无参数，那么直接引用存储过程名；如果存储过程带有输入参数，那么需要为输入参数提供数据值；如果存储过程带有输出参数，那么需要使用变量接收输出结果；如果存储过程带有输入输出参数，那么在调用时需要使用具有输入值的变量。

2、当为参数传递变量或者数据时，可以采用位置传递、名称传递和组合传递三种方法。

存储过程的调用

在SQL*PLUS中调用

EXEC procedure_name(parameter_list)

EXECUTE show_emp(10)

在PL/SQL块中调用

BEGIN

procedure_name(parameter_list);

END ；

注意

在PL/SQL程序中，存储过程可以作为一个独立的表达式被调用。

DECLARE
  v_avgsal emp.sal%TYPE;
  v_count  NUMBER;
BEGIN
   show_emp(20);
   return_deptinfo(10,v_avgsal,v_count);
   DBMS_OUTPUT.PUT_LINE(v_avgsal||' '||  v_count);
END;

调用无参存储过程
	EXEC USP_OutTime;
调用带有输入输出参数的存储过程
declare
	v_para1			varchar2(10);
	v_para2			nvarchar2(10);
	v_para3	        varchar2(30);
	v_para4	        varchar2(30);
begin
  -- Call the procedure
  v_para1 := '123';
  v_para2 := '456';
  v_para4 := '789';
  -- 位置传递
  USP_Learing(v_para1,v_para2,v_para3,v_para4);  
  -- 值传递
  USP_Learing(p_para1=>v_para1,p_para2=>v_para2,p_para3=>v_para3,p_para4=>v_para4);
  -- 组合传递
  USP_Learing(v_para1,v_para2,p_para3=>v_para3,p_para4=>v_para4);
  dbms_output.put_line(v_para3);
  dbms_output.put_line(v_para4);
end;

修改存储过程 
CREATE OR REPLACE PROCEDURE procedure_name
查看存储过程及其源代码 
查询数据字典视图USER_SOURCE
SELECT name,text FROM user_source 
   WHERE type='PROCEDURE'; 
重新编译存储过程 
ALTER PROCEDURE…COMPILE 
ALTER PROCEDURE show_emp COMPILE; 
删除存储过程 
DROP PROCEDURE
DROP PROCEDURE show_emp;

4、存储过程中常用的复合数据类型、CTE

PL/SQL记录(RECORD)，单行多列

PL/SQL 表(TABLE)，多行多列

PL/SQL嵌套表(TABLE)，多行多列

变长数组(VARRY)，多行单列

Common Table Expression（CTE）

PL/SQL记录（RECORD）

PL/SQL记录(record)主要用于处理单行多列数据。当使用RECORD时，既可以自定义记录的类型和变量，也可以使用%ROWTYPE属性定义记录变量。

自定义记录变量

TYPE type_name IS RECORD
(
field_declaration,...
);
identifier type_name;

使用%ROWTYPE属性定义记录变量

identifier [table_name | view_name]%ROWTYPE;

type_name用于指定记录类型的名称； field_declaration用于定义记录成员； identifier用于指定记录变量的名称； table_name用于指定表名； view_name用于指定视图名。

    declare
        type type_dz_record is record
        (
             v_xh rx_dz_nc.xh%type,--序号
             v_dz rx_dz_nc.dz%type,--地址串
             v_xsbj char(1)        --虚实标记
        );
        dz_record type_dz_record;
    begin
      select xh,dz,xsbj
      into dz_record from rx_dz_nc  where xh = &xh;
      dbms_output.put_line(dz_record.v_xh);  
      dbms_output.put_line(dz_record.v_dz);
    end;

    declare
      dz_record rx_dz_nc%rowtype;
    begin
      select xh,dz,xsbj into dz_record from rx_dz_nc where xh = &xh;
      dbms_output.put_line(dz_record.XH); 
      dbms_output.put_line(dz_record.DZ);
    end;

存储过程中异常处理

1、为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。

2、异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；

3、预定义异常是指由PL/SQL提供的系统异常；非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；自定义异常用于处理与Oracle错误的其他异常情况。

4、RAISE_APPLICATION_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间。

CREATE OR REPLACE PROCEDURE USP_Exception
(
 p_pcid       integer,      --批次ID
 p_fm         number,  --分母
 p_fz         number,  --分子
 p_result out number   --结果
)
IS
  v_raise EXCEPTION;    --异常处理
  type type_table_pcmx is table of t_bl_pcmx%rowtype;
  table_pcmx type_table_pcmx;
BEGIN
  if p_fz = 0 then
     RAISE v_raise;
  end if;
  p_result := p_fm/p_fz;
  select ID,PCID,XMID,ZJXMID,BZ,CZSJ,CJSJ
  bulk collect into table_pcmx
  from t_bl_pcmx
  where pcid = p_pcid;
  EXCEPTION
  WHEN v_raise THEN
       RAISE_APPLICATION_ERROR(-20010,'ERROR：分子为零！');
  WHEN NO_DATA_FOUND THEN
       RAISE_APPLICATION_ERROR(-20011,'ERROR：批次明细不存在！');
  WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20012,'ERROR：数据错误！');
END;

6、存储过程中事务处理

1、事务用于确保数据的一致性，由一组相关的DML语句组成，该组DML语句所执行的操作要么全部确认，要么全部取消。

2、当执行事务操作(DML)时，Oracle会在被作用的表上加锁，以防止其他用户改变表结构，同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。

3、当执行事务提交或事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。

4、提交事务（COMMIT）确认事务变化，结束当前事务、删除保存点，释放锁，使得当前事务中所有未决的数据永久改变。

5、保存点（SAVEPOINT）在当前事务中，标记事务的保存点。

6、回滚事务（ROLLBACK）回滚整个事务，删除该事务所定义的所有保存点，释放锁，丢弃所有未决的数据改变。

7、回滚事务到指定的保存点（ROLLBACK TO SAVEPOINT）回滚当前事务到指定的保存点，丢弃该保存点创建后的任何改变，释放锁。

当执行DDL、DCL语句，或退出SQL*PLUS时，会自动提交事务；

事务期间应避免与使用者互动；

查询数据期间，尽量不要启动事务；

尽可能让事务持续地越短越好；

在事务中尽可能存取最少的数据量。

create or replace procedure usp_shiwu
is
begin
    INSERT INTO table_test
    VALUES(1,'2009042201','2009042201');
    COMMIT;
    SAVEPOINT savepoint1;
    INSERT INTO table_test
    VALUES(2,'2009042201','2009042202');
    DBMS_TRANSACTION.savepoint('savepoint2');
    UPDATE table_test SET vCode = '2009042202' WHERE iID=2;
    COMMIT;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
         ROLLBACK TO SAVEPOINT savepoint1;
         --DBMS_TRANSACTION.rollback_savepoint(savepoint1); 
         RAISE_APPLICATION_ERROR(-20010,'ERROR：违反唯一索引约束！');
    WHEN OTHERS THEN
         ROLLBACK;
         --DBMS_TRANSACTION.rollback;
end usp_shiwu;

7、存储过程的优化

1、SQL语句的优化

2、索引的优化

3、游标的优化

SQL语句的优化

SELECT语句的执行顺序

(8)SELECT

(9) [DISTINCT]
(11) 传回结果列表[INTO 新数据表名称]
(1) FROM 数据表
(3) [INNER | LFT | RIGHT] JOIN 数据表
(2) ON <数据表JOIN的条件>
(4) [WHERE <过滤条件>]
(5) [GROUP BY <群组语法>]
(6) [WITH {CUBE | ROLLUP}
(7) [HAVING <过滤条件>]
(10) ORDER BY <排序列表> [ASC | DESC]]

SQL使用时应注意的地方

1、当使用SELECT子句查询数据时，应尽量避免使用万用字符(*)，传回所有数据行。尽量利用WHERE子句进一步限制查询结果，以确保所得的数据是有用的数据，降低传送过多数据所造成的负荷；

2、尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接；

3、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作；

4、注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小；

5、不要在where子句的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引；

6、注意存储过程中参数和数据类型的关系，并注意表之间连接的数据类型，避免不同数据类型之间的连接；

7、尽可能的使用索引字段作为查询条件，尤其是聚簇索引。

JAVA调用存储过程

    /**
	 * 执行不带参数的存储过程
	 */
	public void execute1() {
		Connection conn = null;
		try {
			conn = getConn();
			//执行存储过程
			String sql = "{call PRO_HELLO}";
			CallableStatement cs = conn.prepareCall(sql);
			cs.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
    /**
	 * 执行带IN, OUT参数的存储过程
	 */
	public void execute2() {
		Connection conn = null;
		try {
			conn = getConn();
			//执行存储过程
			String sql = "{call PRO_EMP(?, ?, ?)}";
			CallableStatement cs = conn.prepareCall(sql);
			cs.setLong(1, 7499);
			cs.registerOutParameter(2, Types.VARCHAR);
			cs.registerOutParameter(3, Types.VARCHAR);
			cs.executeUpdate();
			
			String name = cs.getString(2);
			String job = cs.getString(3);
			System.out.println("姓名："+name+"  职位:"+job);
		} catch (Exception e) {
			e.printStackTrace();
		}

本人从事软件项目开发20多年，2005年开始从事Java工程师系列课程的教学工作，录制50多门精品视频课程，包含java基础，jspweb开发，SSH,SSM，SpringBoot，SpringCloud,人工智能，在线支付等众多商业项目，每门课程都包含有项目实战，上课PPT，及完整的源代码下载，有兴趣的朋友可以看看我的在线课堂

讲师课堂链接：https://edu.csdn.net/lecturer/893

CSDN专家-赖老师（软件之家）

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
10-Oracle存储过程（创建，修改，使用及管理）

1、我们为什么要用存储过程？2、存储过程是如何定义和维护的？3、我们如何调用存储过程？4、存储过程中常用的复合数据处理方式及CTE5、存储过程如何进行异常处理？6、存储过程如何进行事务处理？7、我们应如何优化存储过程？
复制链接

扫一扫