Oracle存储过程

概念

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

使用存储过程的优缺点

优点
1.效率高
存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方（例如：记事本 上），都要先分析编译才会执行。所以想对而言存储过程效率更高。

2.降低网络流量
存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高
存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高
当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高
完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

缺点
1.如果新人需要对之前老员工做的存储过程又没有什么注释的情况下，针对内容很多的存储过程进行更改，可能会比较繁琐了。
2. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。
3. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。
4. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

创建语法

1. 无参存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称AS/IS
变量1 DATE;
变量2 NUMBER;
BEGIN
	--要处理的业务逻辑
	EXCEPTION    --存储过程异常
END

2. 有参存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(参数test.a%TYPE) --参数类型和test.a字段类型一样
AS/IS
k test.b%TYPE; --声明变量k，类型和test.b类型一样
m number :=20;-- 声明变量m，数字类型，初始长度为20
BEGIN
  --业务处理.....
END

3. 带参存储过程并且进行赋值

CREATE OR REPLACE PROCEDURE 存储过程名称(
       s_no in varchar,
       s_name out varchar,
       s_age number) AS
total NUMBER := 0;
BEGIN
--  查询语句，把参数s_age作为过滤条件，INTO关键字，把查到的结果赋给total变量。        
  SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
  	-- 输出查询结果  
  dbms_output.put_line('符合该年龄的学生有'||total||'人');
-- 异常处理
  EXCEPTION
    WHEN too_many_rows THEN 
    DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
END

---

参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

例子：通过存储过程做数据迁移，并做异常处理

CREATE OR REPLACE PROCEDURE P_CS
IS
start_date  DATE := SYSDATE;
end_date date;
gv_err  VARCHAR2(500);
begin
  EXECUTE IMMEDIATE 'TRUNCATE TABLE CCGC_CS';
 insert into CCGC_CS select 'P_CS' name,sysdate cstime  from dual@link_tjy;

  end_date:=sysdate;
  commit;
  insert into log_ccgc(tablename, start_time, end_time, log_msg, log_err, log_id)
  select 'P_CS' tablename,start_date start_time,end_date end_time,
         '测试成功' log_msg,null log_err,sys_guid() log_id from dual;
  commit;

-- 异常处理，将异常信息存到log表中
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    gv_err := SUBSTR(SQLERRM, 1, 130) || '####' ||
              SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(), 1, 60);
    insert into log_ccgc(tablename, start_time, end_time, log_msg, log_err, log_id)
    select 'P_CS' tablename,start_date start_time,end_date end_time,
         '测试失败（对数据无影响）' log_msg,gv_err log_err,sys_guid() log_id from dual;
    COMMIT;

END P_CS;