(mysql)存储过程

(1)

存储过程是数据库服务器端的一段程序.

mysql的存储过程,只有版本是mysql5.0或以上的才有此特性.

(2)

什么时候需要用存储过程
存储过程通常是一些经常要执行的任务，这些任务往往是针对大量的记录而进行的。在服务器上执行存储过程，可以

改善应用程序的性能。这是因为：
2.1.
服务器往往具有强大的计算能力和速度。
2.2
避免把大量的数据下载到客户端，减少网络上的传输量。
2.3
存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所

以使用存储过程可提高数据库执行速度。
2.4
存储过程可以重复使用,可减少数据库开发人员的工作量
2.5
安全性高,可设定只有某此用户才具有对指定存储过程的使用权

(3)
3.1
使用存储过程之前,须换一种分隔符.因为在存储过程中,存在多个SQL语句,所有须用另一个分隔符来表示结束符号.
分隔符:         --->只对当前连接有效.下次连接时,仍以";"为分隔符.
DELIMITER //
用回原来的";",可以用: delimiter ;
3.2
第二部分是过程名，合法标识符的问题:
存储过程名对大小写不敏感，因此‘P1’和‘p1’是同一个名字，在同一个数据库中你将能给两个存储过程取相同的

名字，因为这样将会导致重载。某些DBMS允许重载（Oracle支持），但是MySQL不支持。
你可以采取“数据库名.存储过程名”这样的折中方法，如“db5.p1”。

(4)

4.1

创建存储过程时，先用选择相应的数据库。因为创建的存储过程是属于某一数据库的。

4.2

调用存储过程:
基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
如下:
call p1 () //

4.3

删除存储过程
1.基本语法：
drop procedure sp_name//

注意事项:

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

4.4

存储过程的查看:
1)
显示当前数据库存储过程:
show procedure status//
会显示数据库中所有的存储过程的所属数据库,存储过程名,类型(type),定义者(如:root@localhost),创建时间,修改

时间等.
2)
show create procedure sp_name
显示某一个存储过程的详细信息
3)
也可用:
select body from mysql.proc where name="过程名";

(5)

作用域:
CREATE PROCEDURE p11 ()
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'outer';  -- 采用了默认子句,就不用进行set了
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'inner';
SELECT x1;
END;
SELECT x1;
END; //
现在我们来讨论一下作用域的问题。例子中有嵌套的BEGIN/END块，当然这是合法的。同时包含两个变量，名字都是x1

，这样也是合法的。内部的变量在其作用域内享有更高的优先权。当执行到END语句时，内部变量消失，此时已经在其

作用域外，变量不再可见了，因此在存储过程外再也不能找到这个声明了的变量，但是你可以通过OUT参数或者将其值

指派给会话变量来保存其值.
调用作用域例子的过程：
mysql> CALL p11()//
+-------+
| x1 |
+-------+
| inner |
+-------+
+-------+
| x1 |
+-------+
| outer |
+-------+

(6)

case的用法:
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (17);
WHEN 1 THEN INSERT INTO t VALUES (18);
ELSE INSERT INTO t VALUES (19);
END CASE;
END; //

(7)

WHILE ..  DO .... END WHILE
CREATE PROCEDURE p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //
这是WHILE循环的方式。使用"SET v = 0;"语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而

NULL和任何操作的结果都是NULL.
声明类型时，若是数据，则可以先设置其默认值为0。不然，会出现上面这个常见的错误。

(8)

repeat ...end repeat:
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 5         --->这里可以加上";",也可不用
END REPEAT;
END; //
这是一个REPEAT循环的例子，功能和前面WHILE循环一样。区别在于repeat在执行后进行检查,而WHILE则是执行前检查

。

(9)

存储过程的注释:
多行注释,用"/**/"
单行注释,用"--",须注意."--"后,须至少一个空格.

(10)

备份存储过程:
mysqldump -uroot -R procedureDemo > /root/backup.sql
说明:
-R,表示在导出时,导出存储过程以及自定义函数。

(11)

在java中调用存储过程:
存储过程:
mysql> create procedure sumPrice(nameType int,out totalAmount double)
    -> begin
    -> declare name varchar(32);
    -> if nameType=0 then          -- 不能用"==0"
    -> set name= 'all';
    -> select sum(price*qty) into totalAmount from price;
    -> insert into Amount values(name,totalAmount);
    -> else
    -> select customer into name from price where id=nameType;
    -> select sum(price*qty) into totalAmount from price where id=nameType;
    -> insert into Amount values(name,totalAmount);
    -> end if;
    -> end//
Query OK, 0 rows affected (0.08 sec)

代码:
 public  double jProcedureTest(int customerid)
 {
  double results=0;
  
  CallableStatement cstmt=null;
  String sql="{call sumPrice(?,?)}";
  try {
   cstmt=con.prepareCall(sql);
   cstmt.setInt(1, customerid);
   cstmt.registerOutParameter(2,Types.DOUBLE);
   cstmt.execute();
   results=cstmt.getDouble(2);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
  return results;
 }

具体的调用是:
double amount=dbOperator.jProcedureTest(2);
System.out.println("------调用的存储过程返回的值是: "+amount);

--------------------------->分析:
1)
设置输入参数(如果有的话)
类似PreparedStatement的设置参数方法.
setXX(a,b),a是用来表示存储过程中的第几个参数.参数从第一个开始.b,表示此参数的值.
如:
//设置第一个参数,且值为"abcdefg".
cStmt.setString(1, "abcdefg");    -->根据不同的类型,采用不用的set**().
//设置第二个参数,且值为1.
cStmt.setInt(2, 1);

2)
设置输出参数(如果有的话)
须用registerOutParameter()方法进行注册.如下:
cstmt.registerOutParameter(2,Types.DOUBLE);
表示注册第二个参数为输出参数,且类型是Doulbe.

3)
执行CallableStatement,获取结果集合或输出参数
尽管CallableStatement支持Statement接口的所有执行方法(executeUpdate(),executeQuery()或execute()),最具伸

缩性的方法是execute(),因为你不需要知道是否这个存储过程返回结果集合.

(12)

使用游标进行循环输出:
12.1
可以用在存储过程的SQL语句主要有以下类型:
1、 无返回结果语句，如：INSERT,UPDATE,DROP, DELETE等
2、 select语句返回单行变量并可传给本地变量(select ..into)
3、 返回多行结果集的select语句,并可使用游标循环处理

12.2
定义
DECLARE cursor_name CURSOR FOR SELECT_statement;
游标操作
OPEN 打开游标  
OPEN cursor_name;

FETCH 获取游标当前指针的记录，并传给指定变量列表,注意变量数必须与游标返回的字段数一致,要获得多行数据，

使用循环语句去执行FETCH
FETCH cursor_name INTO variable list;
 
CLOSE关闭游标
CLOSE cursor_name ;

12.3
具体实例:
create procedure showAll()
begin
declare a,b,c int;
declare cur cursor for select price,qty from price;
declare exit handler for not found set c=1;
create temporary table temp1(price double,qty double,jdate varchar(255));
open cur;
repeat
fetch cur into a,b;
insert into temp1 values(a,b,now());
until c=1      -- 不能有";"
end repeat;
close cur;
end//

使用说明: -->important
将存储过程利用游标处理后的结果,插入相应数据表即可.
-->可以不用返回到代码.返回代码最后也要插入数据表.
-->中间处理可以用到创建临时表,但在最后,要进行删除,比较合理.

(13)

异常处理：
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement

exit与continue的区别：

exit:会退出这个复合语句，即所在的begin..end;
continue：执行后，会不退出，会继续执行下支。

(14)

多个游标互不影响的处理方法:
delimiter //
drop procedure if exists jt //
create procedure jt()
begin
-- 将每一个游标放于一个复合语句中，当进行exit handler时，就不会影响到另一个游标了。
-- 再将所有的子复合语句放于一个父复合语句中即可。
begin
declare 1_id,a int;
declare 1_amount double;
declare 1_cur cursor for select * from customer where ctype=1;
declare exit handler for not found set a=1;
open 1_cur;
repeat
fetch 1_cur into 1_id,1_amount;
insert into customer2 values(1_id,1_amount);
until a=1
end repeat;
close 1_cur;
end;             -- 要加“;”
-- 局部变量的好处：出了本身的begin..end后，变量消失。
begin
declare 1_id,a int;
declare 1_amount double;
declare 1_cur cursor for select * from customer where ctype=2;
declare exit handler for not found set a=1;
open 1_cur;
repeat
fetch 1_cur into 1_id,1_amount;
insert into customer2 values(1_id,1_amount);
until a=1
end repeat;
close 1_cur;
end;
end //

(15)

存储过程的调试:
1)
调试存储过程时,要去掉异常处理机制,否则不会显示出错信息......---->important
2)
查看存储过程中变量的值,可以将变量的值赋给用户变量.如:
set @x=v_end;
执行后存储过程后,可以用select @x;进行查看.
3)
查看存储过程执行到何处出错:
可以从上到下不同位置,设置如下:
set @x=1;
........
set @x=2;
........
set @x=3;
........

最后执行完存储过程后,用select @x查看到底到哪一步.

(16)

存储过程中调用其它的存储过程--->好用:一个存储过程可以在另一存储过程的被调用,使原来的 存储过程的代码比较简洁,且封装,如java一样.

(17)

实例：

delimiter //
drop procedure if exists trend_scm.jICActionPostAC //
create procedure jICActionPostAC(actionid double,out isSucess int)
begin
-- define the variables
declare v_csign int default 2;
declare v_dsign int default 1;

declare
v_a,v_b,v_c,v_d,
insert_ac_transaction_id,
v_actioncode,
略
v_sales_lastid Double;

declare
v_isPI,
略
v_sales_hometel,
v_sales_sales varchar(255);

-- define exception is very important.
declare exit handler for SQLException rollback;

-- create the temporary table to show values and caculate
   drop table if exists trend_scm.tempa;
   create temporary table tempa(action_id double, account_id double, account_name varchar(255), foreign_currency double,account_sign int,item_from varchar(255),foreign_amount double);

set isSucess=0;
start transaction;

-- (1)get the values of action into variables
   select code,Action_Code,Date,Business_Partner_Code into v_actionnumber,v_actioncode,v_actiondate,v_bpcode from ic_action where id=actionid;

-- (2)get the values in action type panel.
   略

-- (3)get the values of other charge,and insert into the tempb
  begin
    declare v_end int;
    declare v_currencyid double;
    declare oc_cur cursor for select acc.id,acc.name_en_US, ia2.Total_Amount,ia2.Doc_Currency from ic_action_2 ia2,other_charge oc,ac_chart_of_account acc where ia2.action_number=actionid and ia2.item_from='Other Charge' and ia2.Related_PI_Code=oc.code and oc.Chart_of_Account=acc.id;
    declare exit handler for not found set v_end=1;
    set v_item_from='Other Charge';
    open oc_cur;
    repeat
    fetch oc_cur into v_oc_accountid,v_oc_accountname,v_oc_totalamount,v_currencyid;
    if length(v_isPI)>0 then  -- 不为null
      insert into tempa values(actionid,v_oc_accountid,v_oc_accountname,v_currencyid,v_dsign,v_item_from,v_oc_totalamount);
    else
      insert into tempa values(actionid,v_oc_accountid,v_oc_accountname,v_currencyid,v_csign,v_item_from,v_oc_totalamount);
    end if;
    until v_end=1
    end repeat;
    close oc_cur; 
  end;

-- (5)get the values of customer and caculate and insert into tempb.
    select acc.id,acc.name_en_US,bp.currency_id into v_cu_accountid,v_cu_accountname,v_cu_currency from bp_customer bp,ac_chart_of_account acc where bp.id=v_bpcode and bp.chart_ac=acc.id;
    set v_item_from='customer';

    -- first caculate all the other charge values
    select sum(foreign_amount) into v_oc_all_totalamount from tempa where action_id=actionid and item_from='Other Charge';
    if v_oc_all_totalamount is null then  -- 判断是否为null，若开始就定义默认值为0，则可不必定义
    set v_oc_all_totalamount=0;
    end if;

    -- caculate the customer value.
    set v_cu_totalamount = v_it_all_totalamount + v_oc_all_totalamount ;
    if length(v_isPI)>0 then
       insert into tempa values(actionid,v_cu_accountid,v_cu_accountname,v_cu_currency,v_csign,v_item_from,v_cu_totalamount);
    else
       insert into tempa values(actionid,v_cu_accountid,v_cu_accountname,v_cu_currency,v_dsign,v_item_from,v_cu_totalamount);
    end if;
-- (6)insert data into the account's table: ac_transaction
   -- 获取表的最大ID，即刚插入的记录ID。
   select max(id) into v_a from issaccount_demo4.ac_transaction;
   select max(id) into v_b from issaccount_demo4.ac_transaction_tray;
   select max(id) into v_c from issaccount_demo4.ac_transaction_post;
     -- 若为null，则null与其它加减乘除=null
     if v_a is null then set v_a=0; end if;
     if v_b is null then set v_b=0; end if;
     if v_c is null then set v_c=0; end if;

     if v_a > v_b then
       set v_d=v_a;
     else
       set v_d=v_b;
     end if;

     if v_d > v_c then
       set insert_ac_transaction_id=v_d;
     else
       set insert_ac_transaction_id=v_c;
     end if;

     set insert_ac_transaction_id=insert_ac_transaction_id + 1;

   insert into issaccount_demo4.ac_transaction set id=insert_ac_transaction_id,voucher_type=v_vouchertype, transaction_date=v_actiondate, remark='post from scm sales return', create_time=now();

-- (7)insert data into the account's table: ac_transaction_line
   略

-- (8)post the business partner to account

  -- (8.1)post bp to ac and handle the parent company field
  select parent_co into v_hasparent from bp_customer where id=v_bpcode;
if v_hasparent = 0 then
 call trend_scm.jBPIdChange(v_bpcode);
else

  select id into v_ac_bpid from issaccount_demo4.pt_mf_product where code=(select code from bp_customer where id=v_hasparent);

  if v_ac_bpid is not null then
    -- another procedure
    call trend_scm.jBPIdChange(v_bpcode);  -- 调用另一存储过程
    select max(id) into v_bp_lastid_2 from issaccount_demo4.pt_mf_product;
    update issaccount_demo4.pt_mf_product set `Parent Company`=v_ac_bpid where id=v_bp_lastid_2;
  else
    select parent_co into v_scm_bpparentid from bp_customer where id=v_bpcode;
    call trend_scm.jBPIdChange(v_scm_bpparentid);
    call trend_scm.jBPIdChange(v_bpcode);
    select max(id) into v_bp_lastid from issaccount_demo4.pt_mf_product;
    select id into v_ac_bpid_2 from issaccount_demo4.pt_mf_product where code=(select code from bp_customer where id=(select parent_co from bp_customer where id=v_bpcode));
    update issaccount_demo4.pt_mf_product set `Parent Company`=v_ac_bpid_2 where id=v_bp_lastid;
  end if;

 end if;

commit;
 set isSucess=1;  -- 设置返回值
end //
delimiter ;