Oracle存储过程

简介

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

优点

  1. 效率高
    存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的sql语句我们要保存到其他地方,都要先分析编译才会执行。所以相对而言存储过程效率更高。
  2. 降低网络流量
    存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句。
  3. 复用性高
    存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。
  4. 可维护性高
    当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。
  5. 安全性高
    完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

结构

Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理

查看

SELECT * FROM USER_SOURCE WHERE TYPE = 'PROCEDURE';

删除

DROP PROCEDURE p_demo;

无参

CREATE OR REPLACE PROCEDURE p_demo
AS
BEGIN
	DBMS_OUTPUT.PUT_LINE('hello word!');--打印输出
END;

关键字 OR REPLACE 用来创建或覆盖一个原有的存储过程。

有参

CREATE OR REPLACE PROCEDURE p_demo(param1 NUMBER,param2 IN VARCHAR,param3 OUT VARCHAR,param4 IN OUT VARCHAR)
AS
username VARCHAR(50);-- 声明变量
password VARCHAR(50) := '123456';-- 变量初始化赋值
BEGIN
	param3 := 'hello word!';
	SELECT SYSDATE INTO param4 FROM DUAL;-- DUAL系统虚拟表
	-- 打印输出
	DBMS_OUTPUT.PUT_LINE('参数1:'||param1||'参数2:'||param2||'参数3:'||param3||'参数4:'||param4);
END;

关键字 IN 修饰的参数,代表入参,可以省略不写,是参数的默认模式,表示只读,但不能给参数赋值。
关键字 OUT 修饰的参数,代表出参,类型可以使用任意Oracle中的合法类型,允许修改。
关键字 IN OUT 修饰的参数,表示该参数可以向该过程中传递值,也可以将某个值传出去。
声明变量要带取值范围,参数则不用,变量赋值可以用 :=SELECT INTO
打印变量使用 DBMS_OUTPUT.PUT_LINE() ,连接字符串使用 ||

事务

CREATE OR REPLACE PROCEDURE p_demo
AS
BEGIN
	-- 处理逻辑
	DBMS_OUTPUT.PUT_LINE('新增修改删除操作');
	COMMIT;-- 提交事务
	EXCEPTION
		WHEN OTHERS THEN
		 DBMS_OUTPUT.PUT_LINE(SQLERRM);-- 打印输出错误
		 ROLLBACK;-- 回滚事务
END;

在使用 SELECT INTO 赋值时常见的有三种异常类型,分别是 NO_DATA_FOUND 没有数据,TOO_MANY_ROWS 多行数据,OTHERS 其他异常。

游标

CREATE OR REPLACE PROCEDURE p_demo
AS
CURSOR curs_user is SELECT 列 FROM 表;
curs_row curs_user %ROWTYPE; --定义游标变量
BEGIN
	-- for循环游标
  FOR curs_row IN curs_user LOOP
    DBMS_OUTPUT.PUT_LINE(curs_row.列);
  END LOOP;
	
	-- FETCH循环游标
  OPEN curs_user;-- 必须要明确的打开和关闭游标
  LOOP
    FETCH curs_user INTO curs_row;
    EXIT WHEN curs_user%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(curs_row.列);
  END LOOP;
  CLOSE curs_user;
	
	-- WHILE循环游标
	OPEN curs_user;-- 必须要明确的打开和关闭游标
		FETCH curs_user INTO curs_row;
		WHILE curs_user%FOUND LOOP
			DBMS_OUTPUT.PUT_LINE(curs_row.列);
			FETCH curs_user INTO curs_row;
		END LOOP;
	CLOSE curs_user;
END;

判断

CREATE OR REPLACE PROCEDURE p_demo(param NUMBER)
AS
BEGIN
	-- if判断
	IF param = 0 THEN
		DBMS_OUTPUT.PUT_LINE('true');
	ELSE
		DBMS_OUTPUT.PUT_LINE('false');
  END IF;
	-- 多重if判断
	IF param = 1 THEN
		DBMS_OUTPUT.PUT_LINE('true');
	ELSIF param = 2 THEN
		DBMS_OUTPUT.PUT_LINE('false');
  END IF;
END;

循环

CREATE OR REPLACE PROCEDURE p_demo
AS
i NUMBER := 0;
BEGIN
	-- while循环
	WHILE i < 10 LOOP
			DBMS_OUTPUT.PUT_LINE('while循环次数=>'||i);
			i := i + 1;
  END LOOP;
	
	-- for循环
	FOR i IN 1 .. 10 LOOP
		DBMS_OUTPUT.PUT_LINE('for循环次数=>'||i);
	END LOOP;
END;

© 版权声明
THE END
喜欢就支持一下吧
点赞15 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片