什么是存储过程，如何创建一个存储过程
* Stored Procedure  
* 存储过程=SQL语句+流控制语句

定义存储过程

定义

create procedure 存储过程名称（【参数列表】）

begin

需要执行的语句

end.
创建

CREATE PROCEDURE `get_hero_scores`( OUT max_max_hp FLOAT, OUT min_max_mp FLOAT, OUT avg_max_attack FLOAT, s VARCHAR(255) )BEGIN SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;END


调用

call add_num(50);

流程控制语句

BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
SET：赋值语句，用于对变量进行赋值。
SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
CASE：CASE 语句用于多条件的分支判断，使用的语法是下面这样的。
LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。
REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

存储过程使用的问题
优点
1. 一次编译，多次使用。提升了SQL的执行效率![在这里插入图片描述](https://static001.geekbang.org/resource/image/54/9f/54a0d3b0c0e5336c1da6fc84c909a49f.jpg)
2. 将代码封装成模块，减少了开发的工作量。
3. 减少网络传输量。

缺点
1. 可移植性差
2. 调试困难
3. 版本管理困难
4. 不适合高并发场景。

                    
利用存储过程来实现下面的应用: 
从一个账户转指定数额的款项到另一个账户中。
*/ 建表：create table account(accountnum int,uname char(10),total float)             
insert into account values(1001,'u1',50000)            
 insert into account values(1002,'u2',1000)             
 select * from account 


建存储过程：  
CREATE PROCEDURE TRANSFER  
@outAccount int, 
@inAccount int, 
@amount 
FLOAT AS 


     DECLARE  @totalDeposit FLOAT    
BEGIN TRANSACTION                  /* 检查转出账户的余额 */                             SELECT @totalDeposit=total FROM ACCOUNT WHERE ACCOUNTNUM=@outAccount     
    IF @totalDeposit IS NULL    /* 转出账户不存在或账户中没有存款 */       BEGIN                 ROLLBACK TRANSACTION          
    PRINT '转出账户不存在或账户中没有存款,不能操作！' 
 RETURN       
END       


IF @totalDeposit<@amount   /* 转出账户存款不足 */       
BEGIN          
ROLLBACK TRANSACTION          
PRINT '转出账户存款不足,不能操作！'                                /*  建议使用函数RAISERROR('转出账户存款不足,不能操作！',10,1)   */          
RETURN  
END


IF exists(SELECT * FROM ACCOUNT WHERE ACCOUNTNUM=@inAccount )    /* 检查转入账户是否存在 */        BEGIN                             
UPDATE account SET total=total-@amount WHERE ACCOUNTNUM=@outAccount                              /* 修改转出账户，减去转出额 */          
UPDATE account SET total=total+@amount WHERE ACCOUNTNUM=@inAccount                                                   /* 修改转入账户，增加转出额 */          
COMMIT TRANSACTION                 /* 提交转账事务 */          
PRINT '转账成功,谢谢使用本存储过程！'          
RETURN         
END     


ELSE        
BEGIN           
ROLLBACK TRANSACTION            
PRINT '转入账户不存在,不能操作！'          
RETURN        
END 
本文转自程序猿博客51CTO博客，原文链接http://blog.51cto.com/haihuiwei/1704845如需转载请自行联系原作者


365850153


        
        
                
    

创建存储过程
CREATE PROCEDURE <存储过程名> ([参数1，参数2，...])            /*存储过程首部*/
AS
<PL/SQL块>;             /*存储过程体，描述该存储过程操作*/
 
CREATE PROCEDURE Transfer(inAccount INT, outAccount INT, amount FLOAT)
　　AS
 
　　 /*存储过程体，PL/SQL块 - star*/
　　DECLARE              
　　　　totalDeposit FLOAT;
　　BEGIN
　　　　SELECT total INTO totalDeposit FROM ACCOUNT WHERE Accountnum = outAccount;
 
　　　　IF totalDeposit IS NULL THEN
　　　　　　ROLLBACK;         /*回滚，当有一个SQL语句执行时，条件不符合要求，撤销事务开始后的操作*/
　　　　　　RETURN;
　　　　END IF;
 
　　　　IF totalDeposit < amount THEN
　　　　　　ROLLBACK;
　　　　　　RETURN;
　　　　END IF;
 
　　　　UPDATE account SET total = total - amount WHERE Accountnum = outAccount;
　　　　UPDATE account SET total = total + amount WHERE Accountnum = inAccount;
 
　　　　COMMIT;        /*提交事务*/
 
　　　END;
　　     /*存储过程体，PL/SQL块 - end*/
 
重命名存储过程
ALTER PROCEDURE <存储过程名1> RENAME TO <存储过程名2>;
 
执行存储过程
CALL PROCEDURE Transfer(1000032324,123244444,1000);
 
删除存储过程
DROP PROCEDURE Transfer;

 1.创建如下表

 

2.创建存储过程

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE(MYNMAE IN VARCHAR2,
                                           AGE    IN NUMBER,
                                           OUT_R  OUT NUMBER,
                                           DATA   OUT SYS_REFCURSOR) IS
  --int float都可以定义为 NUMBER 类型, 入参 VARCHAR2不要定义长度(本来就是可变长度的)  IS AS都可以, 默认是IN,可以省略IN
  --入参mynmae不能作为赋值目标,即不可以 myname:='zhangsan'; 只能对声明的变量进行赋值(mysql最后啊也遵循这一点规范)
  --存储过程有IN OUT   函数只有IN
  --声明变量不需要 declare关键字
  --使用前必须先声明变量, 变量不区分大小写
  --字符串类型必须指定size(否则报错), 变量的声明必须在 begin之前
  --断点调试(在行号处打断点),选中 入参myname 右键选择 add variable to watches
  V_NAME       VARCHAR2(30) := '小丽' || '小花'; --变量的声明 和 赋值一块进行
  V_SQL        VARCHAR2(100); -- 也可以写为V_NAME my_user.name%type;  表示V_NAME变量 数据类型和my_user表的name字段一致
  I_BEGIN_DATE DATE;
BEGIN
  OPEN DATA FOR
    SELECT * FROM MY_USER; --存储过程返回结果集
  OUT_R := 88; --给OUT赋值
  DBMS_OUTPUT.PUT_LINE(V_NAME); --输出结果到DBMS标签页面
  --oracle的动态sql
  V_SQL := 'SELECT NAME FROM MY_USER WHERE ID=:1'; --注意这里用的是=: 不是前面赋值用的:=
  EXECUTE IMMEDIATE V_SQL
    INTO V_NAME
    USING 1;
  COMMIT; --当执行增删改操作设计事务,必须COMMIT
  DBMS_OUTPUT.PUT_LINE(V_NAME);
  --存储过程中的异常问题
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('error');
END TEST_PROCEDURE;


3.点击调试存储过程 F9 执行可以断点调试



除了可以在下方表格填写入参， 当然也可以  ：AGE替换为入参的数字，点击F8执行也是没问题的





 

存储函数

CREATE OR REPLACE FUNCTION TEST_FUNCTION(AGE IN NUMBER)
RETURN NUMBER --返回的函数值类型
 AS
  V_NAME VARCHAR2(30); --定义变量但不用
BEGIN
  RETURN AGE * 2;
END;