存储过程

存储过程：操作表数据 类似于java方法 入参 出参

表增删改查 判断 循环 异常捕获 嵌套查询过程

CREATE OR REPLACE PROCEDURE P_存储过程名(变量名 IN|OUT 数据类型)   -- 存储过程名称通常以P_开头  in|out  输入参数|输出参数

IS                                                                                        -- IS作为申明变量的关键词

       V_NUM       NUMBER;                                                        -- 声明NUMBER类型的变量，后以分号结束

       V_USERNAME  VARCHAR2(40);                             -- 声明VARCHAR2类型的变量

       V_SORT         INTEGER;                                          -- 声明Integer类型的变量

       V_IS_BIND   NUMBER(12);                                           -- 声明长度为12的NUMBER类型变量

       V_NAME              T_USER.NAME%TYPE;                               -- 声明变量直接赋值(表中NAME类型和长度就是V_NAME的类型和长度)

       V_USER         T_USER%ROWTYPE;                                         -- 声明记录型变量，相当于Java的对象，可以使用点加变量名获取值

       CURSOR T_USER IS SELECT NAME,AGE FROM T_USER;  -- 声明游标

       -- 此处只作演示所用，其他变量声明可参考博文下面的示例及注释；

BEGIN                                                                                  -- 执行代码开始

       -- 执行的代码逻辑，类似Java的方法体

       V_USER := '张三';                                     -- 给变量赋值。语法是冒号后面跟等号

       DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'YYYYMMDD'));    -- 打印语句，相当于Java中的println方法

EXCEPTION                                                                         -- 异常

  WHEN OTHERS THEN ...                                                   -- OTHERS相当于Java中的Exception，会捕获所有异常

END;                                                                                    -- 执行代码结束，以分号结束，也有一些是END后跟储存过程名加分号

执行存储过程的方式

-- 方式一，在SQL>后面执行

EXEC 存储过程名(参数..);

或

EXECUTE 存储过程名(参数..);

-- 方式二，在PL/SQL Developer的SQL窗口中执行（下有图）

BEGIN

       存储过程名(参数); -- 执行的存储过程需要加分号

END;

-- 方式三，在PL/SQL Developer的Test窗口中执行，可执行debug，或者编译（下有图）

BEGIN

       存储过程名(参数); --该种调用方式可调试

END;

-- 方式四，Java代码调用

CallableStatement callableStatement = connection.divpareCall("{call 存储过程名(?)}");

-- 方式五，在Mapper中调用，有入参和出参，使用<![CDATA[]]>包裹

<![CDATA[

       {CALL 存储过程名(#{name,mode=IN,jdbcType=VARCHAR},#{age,mode=OUT,jdbcType=INTEGER})}

]]>

外键缺点 会产生临时表，占用内存 数据多尽量不用外键 用一张表

create procedure 存储过程名字（[参数列表]）

begin

       --sql语句

end

调用：call 存储过程名

show create procedure存储过程名

drop

创建存储过程：要加delimiter指定sql语句的结束符

delimiter

create procedure p1()

begin

       select count(*) from stu表；

end

delimiter;

call p1();

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'book_sys';

show create procedure p1;

# 删除存储过程drop procedure if exists p1;

  全局变量和会话变量 global session  默认session Mysqld重启失效，写到/etc/my.cnf中

show session variables;

select @@global.autocommit  

set session autocommit=0 关闭自动提交 需要手动提交

commit 手动提交

select @var_name:= expr

select 字段名into @var_name from 表明

select @var_name使用

create procedure p2()

begin

       declare stu_count int default 0;

select count(*) into stu_count from stu表

select stu_count;

end

call p2();

create procedure p3()

degin

declare score int default default 60;

declare result varchar(10)

if score >=85 then

    set result :=优秀

elseif score >=60 then

    set result :=’及格’；

else

    set result :=’不及格’；

end if;

select result;

end;

call p3();

# 根据传入参数score，判定当前分数对应的分数等级，并返回

# score >= 85分，等级为优秀。

# score>=60分且score<85分，等级为及格

# score<60分，等级为不及格。

create procedure p4(in score int,out result varchar(10))

begin

#     定义了变量，定死了，不能改变，如果要改变参数，则在p3()里面传参

#     declare score int default 60;

#     declare result varchar(10);

    if score >=85 then

        set result :='优秀';

    elseif score>=60 then

        set result :='及格';

    else

        set result :='不及格';

    end if;

#     select result;

end;

call p4(99,@result);

select @result;

# 将传入的 200分制的分数,进行换算,换算成百分制，然后返回分数--inout既是输入也是输出

create procedure p5(inout score double)

begin

    set score = score*0.5;

end;

# 进行复值

set @result=99.9;

# 赋值完后调用分数，进行换算，换算完返回给result

call p5(@result);

select @result;

# case

# 根据传入的月份，判定月份所属的季节(要求采用case结构)

# --1-3月份，为第一季度

# --4-6月份，为第二季度

# --7-9月份，为第三季度

# 10-12月份，为第四季度

create procedure p6(in month int)

begin

    declare reusult varchar(10);

    case

        when month>=1 and month <=3 then

            set reusult :='第一季度';

        when month >=4 and month <=6 then

            set reusult :='第二季度';

        when month >=7 and month <=9 then

            set reusult :='第三季度';

        when month >=10 and month <=12 then

            set reusult :='第四季度';

        else

            set reusult :='非法参数';

    end case;

    select concat('你输入的月份为：',month,'所属为：',reusult);

end;

call p6(13);

# while计算从1累加到n的值:n为传入的参数值-

# --A.定义局部变量，记录累加之后的值;

# B.每循环一次，就会劝进行减1，如果n减到0，则退出循环

create procedure p7(in n int)

begin

    declare total int default 0;

    while n>0 do

        set total:=total+n;

        set n :=n-1;

        end while;

    select total;

end;

call p7(100);

loop

LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

create procedure p8(in n int)

begin

       declare totle int default 0;

       sum:loop

              if n<=0 then

                     leave sum;

              end if;

              set totle :=totle+n;

       end loop sum;

       select totle;

end;

call p8(10);

# A.定义局部变量，记录累加之后的值;

# B.每循环一次，就会y进行-1，如果n减到，则退出循环 ----> leave xx

# c.如果当次累加的数据是奇数，则直接进入下一次循环.--------> iterqte XX

create procedure p9(in n int)

begin

    declare totle int default 0;

    sum:loop

        if n <= 0 then

            leave sum ;

#             在loop中通过leave退出循环

        end if;

        if n %2 = 1 then

            set n:=n-1;

            iterate sum; 使用iterate跳过这次循环，直接进入下一个循环

        end if;

        set totle :=totle+n;

        set n:=n-1;

    end loop sum;

    select totle;

end;

# 调用1-10之间偶数的和

call p9(100);