存储过程和自定义函数

在做项目的计费功能的时候，

首先需要在数据库里定义 一条费率规则，规则下包括若干个条件

在做到了查费率这一步的时候犯了难，要把商品的  若干个属性 都写到sql里查？这样写起来可就麻烦了

于是经理大笔一挥写了个存储过程，告诉我，你把属性拼成一个json串传进来就能查到费率了，看得我目瞪狗呆。。。

回来赶紧补课，看看存储过程是个什么玩意？、

书上写的：

存储过程是由过程化SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可

优点：

1运行效率高

2降低了客户机和服务器之间的通信量

3方便实施企业规则

存储过程的用户接口：

创建存储过程：

 //存储过程首部

as <CREATE OR REPLACE PROCEDURE 过程名 （[参数1，参数2，...])   过程化SQL串>              //存储过程体

执行存储过程：

CALL/PERFORM 存储过程名([参数1，参数2...])

修改存储过程：

改名字

ALTER PROCEDURE 过程名1   RENAME TO   过程名2;

可以使用ALTER PROCEDURE重新编译一个存储过程：

ALTER PROCEDURE 过程名 COMPILE

删除存储过程：

DROP PROCEDURE 过程名();

好了，现在去toad里看看存储过程长什么样，

CREATE OR REPLACE FUNCTION WHG_YMS."F_GETSEQNO" ( keyValue varchar2 ) RETURN NUMBER
等等，这是个什么东西，function ,功能?

没错，它叫自定义函数，那它跟存储过程有什么关系 呢？

自定义函数与存储过程相似，都是持久性存储模块。函数的定义和存储过程也类似，不同之处是函数必须指定返回的类型

自定义函数的定义：

CREATE OR REPLACE FUNCTION 函数名 （[参数1，参数2，...])   RETURNS <类型> 

AS <过程化SQL块> 

自定义函数的执行语句格式

CALL/SELECT 函数名（[参数1，参数2...]）

修改自定义函数

改名字

ALTER FUNCTION 过程名1   RENAME TO   过程名2;

可以使用ALTER FUNCTION重新编译一个存储过程：

ALTER PROCEDURE 函数名 COMPILE

现在给一个自定义函数的简单案例（从同事那里抄过来的，学习吗，都是从抄开始的）：

CREATE OR REPLACE FUNCTION  "F_GETSEQNO"  ( keyValue varchar2 )  RETURN NUMBER  //创建一个生成流水号的函数，返回值是数字
 IS
tmpVar NUMBER;
ls_keyValue  VARCHAR2(100);
ll_currentValue  number;
ll_rowCount   number;
ll_step   number;
returnValue   number;

//定义了若干变量

 PRAGMA AUTONOMOUS_TRANSACTION;

//这段代码的是自治事务？上网查了一下，自治事务的特点是不依赖于原有MAIN程序，比如MAIN程序中有未提交的数据，那么在自制事务中是查找不到的

在自治事务中，COMMIT或者rollback只会提交或回滚当前自治事务的DML，不会影响MAIN程序中的MDL。然而还是不太懂。。。

/******************************************************************************
   NAME:       F_GETSEQNO
   PURPOSE:    


   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2017/9/7   scown       1. Created this function.


   NOTES:
******************************************************************************/
BEGIN

//这东西好像是toad编辑器生成的注释，好厉害的样子


ls_keyValue := upper(keyValue);
//把传过来的keyValue 全部换成大写，赋值

----10.取得当前值
    SELECT COUNT (1)
      INTO ll_rowCount
      FROM SYS_SQLSEQUENCES
     WHERE keyValue = ls_keyValue;

//还有select into 这种用法，长姿势了     

//查出来的数字 赋值

    if ll_rowCount > 0  then 
        --检索当前值

        select CURRENTVAL,step
          into ll_currentValue,ll_step  
          from SYS_SQLSEQUENCES
         where KEYVALUE = ls_keyValue;
    else
        --插入新的序列
        INSERT INTO SYS_SQLSEQUENCES (ID, KEYVALUE, STARTWITH, CURRENTVAL,STEP, MINVALUE, MAXVALUE)
              VALUES (sys_guid(),ls_keyValue, 1, 1,1, 1, 99999999999999);
        ll_currentValue := 0;
        ll_step := 1 ;
    end if;

//如果>0执行什么，否则执行什么

----30.得到新值并跟新数据
    returnValue := ll_currentValue + ll_step;
    
    update SYS_SQLSEQUENCES 
       set CURRENTVAL = returnValue
     where KEYVALUE = ls_keyValue;
    commit;
    return returnValue;

//最后执行什么

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END F_GETSEQNO;

//异常的情况执行什么

修改存储过程：

改名字

ALTER PROCEDURE 过程名1   RENAME TO   过程名2;

可以使用ALTER PROCEDURE重新编译一个存储过程：

ALTER PROCEDURE 过程名 COMPILE