oracle存储过程-尝鲜

一、什么是存储过程

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

一句话概括----专门干一件事的一段sql语句。

二、写存储过程有什么好处

• 效率高
• 可复用

三、使用介绍（部分用法）

1. 带入参，无返回值。
2. 带入参，有返回值。
3. 带入参，有返回，有循环

---

• 例1：带入参，有返回值

/**
 * 带入参，返回值
 */
CREATE OR REPLACE
PROCEDURE ADD_TEST(a NUMBER , b NUMBER , c OUT NUMBER)
AS
BEGIN
	SELECT (a + b) INTO c FROM dual ;
	DBMS_OUTPUT.PUT_LINE('结果：' || c );
END;


--上面例子，是一个加法计算的简易过程。调用时，call ADD_TEST(-8 , 4 , ?); 

--调用

call ADD_TEST(-8 , 4 , ?); 

• 例2：带入参，有返回值，有循环

/**
 * 带入参，返回值，循环
 * a 起始值
 * b 循环次数，每次自增
 * d 步长
 * c 结果
 */
CREATE OR REPLACE
PROCEDURE INCREASE_TEST(a NUMBER , b NUMBER , d NUMBER , c OUT NUMBER)
AS
	x NUMBER;
	y NUMBER;
BEGIN
	x := b;
	c := 0;
	y := 0;
	IF x < 0 THEN
		--如果循环次数小于等于零，给报错提示  第一个参数是报错码(是一个负整数，范围从 -20999 到 -20000)，可以自定义；第二个是报错信息
		DBMS_OUTPUT.PUT_LINE('x：' || x );
		RAISE_APPLICATION_ERROR(-20999 , '循环次数只能为正整数');
    END IF;
		

	WHILE x > 0 LOOP
		y := a + d;
		c := c + y;
		x := x - 1;
	END LOOP;

	DBMS_OUTPUT.PUT_LINE('结果：' || c );

    EXCEPTION
	  	WHEN OTHERS THEN
	  		DBMS_OUTPUT.PUT_LINE('发生异常:  '|| SQLERRM);	 --输出异常
END;




--调用call INCREASE_TEST(0 , -10 , 10 , ?);

--调用call INCREASE_TEST(0 , -10 , 10 , ?);会抛出异常

--调用call INCREASE_TEST(0 , 10 , 10 , ?);

这里特别说明一下自定义异常的错误码范围（未查证，仅测试了边界），是一个负整数，范围从 -20999 到 -20000。

• 例3：带入参，有返回值，有循环

先建两个表  TESTA、TESTB

TESTA

TESTB

/**
 * 带入参，返回值，循环  
 * 先查出一个集合，再作为其他查询的入参
 * 
 * 查询a编号的人名字
 */
CREATE OR REPLACE
PROCEDURE LOOP_TEST(a NUMBER , b OUT varchar2)
AS
	x NUMBER;--用于入参判断
	ss varchar2(100);--查询结果
	i NUMBER;--记录运行次数
BEGIN
	x := a;
	i := 0;
	IF x < 0 THEN
		RAISE_APPLICATION_ERROR(-20999 , '人员编号不能为负');
    END IF;
   
     	for rec in (
     		select * from TESTA t2
		) LOOP
		
	if rec.num = a then
      SELECT t.NAME INTO ss FROM TESTB t WHERE t.NUM = a;
      b := ss;
      DBMS_OUTPUT.PUT_LINE('运行第' || i || '次时，查询出结果：'|| b);	 
    end if;
	
 	DBMS_OUTPUT.PUT_LINE('运行次数：' || (i + 1));
 	i := i + 1;
	END LOOP;

    EXCEPTION
	  	WHEN OTHERS THEN
	  		DBMS_OUTPUT.PUT_LINE('发生异常:  '|| SQLERRM);	 --输出异常

END;

--调用 call LOOP_TEST(5,?); 

日志记录如下：

查询记录：

 --调用 call LOOP_TEST(3,?); 

日志记录如下：

查询记录：

以上是入门示例，可以用在哪里，自己根据业务，实际权衡。