当前位置: 澳门新濠3559 > 数据库 > 正文

比如希望快速使用多条select,分支结构

时间:2019-11-09 00:17来源:数据库
存储过程的使用:   调用存储过程:call 存储过程名(); 带参数的调用存储过程:call 存储过程名(参数); 对于in类型的,参数可以是数值,可以是变量 对于outinout类型的,参数必须是变量

存储过程的使用:

 

  • 调用存储过程:call 存储过程名();
  • 带参数的调用存储过程:call 存储过程名(参数);
    • 对于in类型的,参数可以是数值,可以是变量
    • 对于outinout类型的,参数必须是变量
    • 所有MySQL变量都必须以 @ 开始
    • 示例:call myselect("lilei",@变量名);示例:call myselect(@变量名,@变量名)

 

下面调用的存储过程就是上面创建存储过程中定义的存储过程:

call myselect();

call getInfo("lilie");

set @mname="lilei";
call getInfo(@mname);

call getInfo3("lilei",@mname);

 

/********************* 带输入参数的存储过程  *******************/

3、PLSQL异常处理

        1、声明异常

         异常名 EXCEPTION;

        2、抛出异常

         RAISE 异常名

        3、处理异常

         抛出异常后的逻辑代码不会被继续执行

        异常的定义使用

 ―――――――――――――――――――――――――――――――――――――

    begin

          dbms_output.put_line(1/0);  --1/0出现错误,抛出异常,输出error

    exception

          when others then

             dbms_output.put_line('error');

    end;

 -----------------------------------------------------------------------------

    declare

            e_myException exception; --声明异常

    begin

            dbms_output.put_line('hello');

                        --raise抛出异常,用此关键字,抛出后转到自定义的e_myException,执行其

                        --里面的putline函数后,再跳到end处,结束PL/SQL块,raise接下面的2句不会继续执行。

            raise e_myException; 

            dbms_output.put_line('world');

            dbms_output.put_line(1/0);

    exception

            when e_myException then   --执行异常

                dbms_output.put_line(sqlcode); --当前会话执行状态,错误编码

                dbms_output.put_line(sqlerrm); --当前错误信息

                dbms_output.put_line('my error');

            when others then

                dbms_output.put_line('error');

    end;     

 

三、存储过程(procedure)

存储过程是一种没有返回值的函数,是一种用来处理数据的方式。

(1)创建过程

create procedure 过程名([参数列表])
begin
-- 过程体
end

澳门新濠3559 1

创建过程

(2)查看过程

函数的查看方式完全适用于过程:关键字换成procedure

查看所有过程
show procedure status;
或者
show procedure status [like 'pattern'];

澳门新濠3559 2

查看所以过程

查看过程创建语句
show create procedure 过程名;

澳门新濠3559 3

查看过程创建语句

(3)调用过程

过程没有返回值:select是不能访问的

调用过程关键字:call

澳门新濠3559 4

调用过程

(4)修改过程&&删除过程

过程不能修改,只能先删除后新增

删除过程
drop procedure 过程名;

澳门新濠3559 5

删除过程

(5)过程参数

函数的参数需要数据类型指定,过程需要类型限定:三种类型

  • in:数据只是从外部传入给内部使用(值传递)。可以是数值也可以是变量
  • out:只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才回进入到内部),只能是变量
  • inout:外部可以在内部使用,内部修改也可以给外部使用,典型的引用传递:只能传变量

基本使用
create procedure 过程名(in 形参名 数据类型,out 形参名 数据类型,inout 形参名 数据类型)

澳门新濠3559 6

过程参数

调用:out和inout类型的参数必须传入变量,不能是变量。

澳门新濠3559 7

调用

存储过程对于变量的操作是滞后的:是在存储过程调用结束的时候,才会重新将内部的值赋给外部传入全局变量

澳门新濠3559 8

比如希望快速使用多条select,分支结构。过程参数

澳门新濠3559 9

调试

最后,在存储过程调用结束之后,系统会将局部变量重复返回给全局变量(out和inout)

澳门新濠3559 10

验证

删除存储过程:

  • 语法:drop procedure 存储过程名;
  • 示例:
  • drop procedure getInfo;

 


select max(id) into p from test;

--if elsif else判断--

        declare

                v_name varchar2(20):='cheng';

        begin

                 if v_name='0701' then

                        dbms_output.put_line('0701');

                 elsif v_name='cheng' then

                        dbms_output.put_line('cheng');

                 else

                        dbms_output.put_line('false');

                 end if;

        end;

二、函数

函数:将一段代码块封装到一个结构中,在需要执行代码块的时候,调用结果执行即可。(代码复用)

  • 系统函数
    系统定义好的函数,直接调用即可。
    任何函数都有返回值,因此函数的调用是通过select调用。

MySQL字符串的基本操作单位

substring:字符串截取(字符为单位)

澳门新濠3559 11

字符串截取

char_length:字符长度
length:字节长度

澳门新濠3559 12

字符,字节长度

instr:判断字符串是否在某个具体的字符串中存在,存在返回位置

澳门新濠3559 13

字符串寻找

lpad:左填充,将字符串按照某个指定的填充方式,填充到指定长度(字符单位)

澳门新濠3559 14

字符串填充

insert:替换,找到目标位置,指定长度的字符串,替换成目标字符串

澳门新濠3559 15

字符串替换

strcmp:compere,字符串比较

澳门新濠3559 16

字符串比较

  • 自定义函数

函数要素:函数名,参数列表(形参和实参),返回值,函数体(作用域)

(1)创建函数

创建语法
create function 函数名([形参列表])returns 数据类型 -- 规定要返回的数据类型
begin
-- 函数体

-- 返回值:return 类型(指定数据类型);
end

澳门新濠3559 17

创建函数

自定义函数和系统函数调用方式是一样的:select 函数名([实参列表])

澳门新濠3559 18

调用函数

(2)查看函数

查看所有函数:
show function status;
查看函数的创建语句:
show create function 函数名;

澳门新濠3559 19

查看所有函数

澳门新濠3559 20

查看函数的创建语句

(3)修改函数&&删除函数

函数只能先删除后新增,不能修改

drop function 函数名;

澳门新濠3559 21

删除函数

函数参数
参数分为两种

  • 形参:定义时的参数
  • 实参:调用时的参数(实参可以是数值也可以是变量)

必须指定数据类型
function 函数名(形参名 字段类型) returns 数据类型

举个栗子

澳门新濠3559 22

函数运用

澳门新濠3559 23

函数检验

作用域

MySQL中全局变量可以在任何地方使用;局部变量只能在函数内部使用

全局变量:使用set关键字定义,使用@符号标志
局部变量:使用declare关键字声明,没有@符号。所有的局部变量的声明,必须在函数体开始之前

澳门新濠3559 24

局部变量的应用

本文内容:

  • 什么是存储过程
  • 存储过程的创建
  • 存储过程的使用
  • 查看存储过程
  • 修改存储过程
  • 删除存储过程

 

 

首发日期:2018-04-17


/* 调用该存储过程,注意:输入参数是一个值,而输出参数则必须是一个带@符号的变量 */

 1、insert-------------------------------------------------------------------------------------------------

        create or replace procedure say_hello(  

               i_name in varchar2,  

               o_澳门新濠3559,result_msg out varchar2  

        )  

     as  

     v_price varchar2(100);   

     e_myException exception;  

  

     begin  

        insert into book(id,bookname,price) values (1,2,3);  --这里会报错  

        o_result_msg := 'success';  

     exception  

        when others then  

             rollback;  

             --o_result_msg := substr(sqlerrm, 1, 200);  --返回完整错误信息  

             o_result_msg := substr(sqlcode, 1, 200);    --返回错误码  

  

     end;        

一、代码执行结构

  • 顺序结果
  • 分支结构
  • 循环结构

1.分支结构

分支结构:实现多个代码块,按照条件选择性执行某段代码
在MySQL中只有if分支

基本语法:

if 条件判断 then
-- 满足条件要执行的代码
else
-- 不滿足条件要执行的代码
end if;

触发器结合if分支

澳门新濠3559 25

触发器结合if分支

澳门新濠3559 26

影响

2.循环结构

循环结构:某段代码在指定条件下执行重复。

while循环

基本语法

while 条件判断 do
-- 满足条件要执行的代码
-- 变更循环条件
end while;

(1)循环控制

在循环内部进行循环判断和控制

iterate:迭代,类似continue,后面代码不执行,循环重新来过
leave:离开,类似break,整个循环接收

使用方式:
iterate/leave 循环名字;

定义循环名字
循环名字:while 条件 do
-- 循环体
-- 循环控制
leave/iterate 循环名字;
end while;

补充:

  • 与触发器类似,如果在命令行模式下进行存储过程创建,需要修改命令结束符。
  • 还可以有一些特别的选项,特别的选项写在([参数列表])  之后,begin之前
    • comment:是这个存储过程的一个描述

      create procedure myselect2()
      comment "我的一系列sql语句"
      begin 
          select * from student;
          select * from class;
      end;
      show create procedure myselect2;
      
    • 还有一些比如sql security等选项,有兴趣可以自行百度。这里不讲解,仅一提有此知识点。

 

 


/***************** 既做输入又做输出参数的存储过程 ***************************************/

4、loop方式遍历游标

    declare

          v_bookname  varchar2(100);

          cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

        Open  c_book(i_id);

        Loop

            Fetch c_book into v_bookname;

            exit when c_student%notfound;

              update book set price = '33' where bookname = v_bookname;

        End Loop;

        Close c_book;

    end;

    或

    declare

          v_bookname  varchar2(100);

          cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

        Open  c_book(i_id);

          Fetch c_book into v_bookname;

          While c_book%Found

          Loop

              update book set price = '33' where bookname = v_bookname;

          Fetch  c_book into v_bookname;

          End Loop;

        Close c_book;

    end;  


begin

5、Oracle存储过程

  存储过程---就像数据库中运行方法(函数),和C#方法一样,由存储过程名/存储过程参数组成,可以有返回结果。

  优点:

    -- 执行速度更快(在数据库中保存的存储过程语句都是编译过的)

    -- 允许模块化程序设计(类似方法的复用)

    -- 提高系统安全性(防止sql注入)

    -- 减少网络流通量(只要传输存储过程的名称)

  一般,以sp_、xp_开头的都是系统存储过程,用户自定义存储过程usp_

什么是存储过程:

 

  • 存储过程存储了一系列sql语句
  • 存储过程的需求场景:下边是一个经典的需求场景,很多Mysql的书都有:澳门新濠3559 27
  • 存储过程存储了一系列sql语句,使得简化了操作,不要求重复执行一系列操作。只需要在需要的时候调用一下存储过程就行了。
  • 一般来说,可以认为存储过程的功能与函数的功能类似(应该都学过函数吧),但只是要注意存储过程没有返回值,所以可以依据函数可用场景来理解存储过程。

 

 

DETERMINISTIC

5.1.3 多个参数的存储过程

     create or replace procedure say_hello(

        v_first_name in varchar2,

        v_last_name in varchar2)

     as

     begin

        dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);

     end;

out输出参数,用于利用存储过程给一个或多个变量赋值,类似于返回值

     create or replace procedure say_hello(

        v_name in varchar2,

        v_content out varchar2

     )

     begin

        v_content:='hello'||v_name;

     end;

     调用:

     declare

        v_con varchar2(200);             --声明变量时带长度

        v_in varchar2(20):='wang';      --赋值

     begin

        say_hello(v_in,v_con);

        dbms_output.put_line(v_con);

     end;

修改存储过程:

  • 修改存储过程只能修改那些选项(这里不讲解那些具体选项,想了解的可以自行百度),并不能修改传入传出参数或者sql语句。
  • alter procedure 存储过程名 选项;
    • 澳门新濠3559 28

call sp4(@pp)//

 --loop循环,注意推出exit是退出循环,而不是推出整个代码块

        declare

                v_i binary_integer :=0;

        begin

                 loop

                     exit when v_i>10;

                         v_i :=v_i+1;

                         dbms_output.put_line('hehe');

                 end loop;

                         dbms_output.put_line('over');

        end;

查看存储过程:

  • 查看存储过程的创建语句:show create procedure 存储过程名;
  • 查看存储过程状态:show procedure status;【显示的内容包括创建时间、注释、定义的用户、安全类型等等】

 


else

2、update/delete------------------------------------------------------------------------------------

        create or replace procedure say_hello(  

                i_name in varchar2,  

                o_result_msg out varchar2  

        )  

     as  

     v_price varchar2(100);   

     e_myException exception;  

  

     begin  

        update book set price = '55' where bookname = i_name;  

        delete from book where bookname = i_name;  

        if sql%notfound then  

           raise e_myException;   

        end if;  

        o_result_msg := 'success';  

     exception  

        when e_myException then  

             rollback;  

             o_result_msg := 'update or delete dail';  

     end;         

补充:

  • 存储过程与触发器的区别:触发器触发事件就执行一系列语句;而存储过程是调用,而且存储过程还要根据情况考虑执行“另外一系列语句”。
  • 存储过程与函数的区别:函数有返回值,而存储过程没有【所以不能使用在select语句中】

 


set @v = 10;

1、基础语法

   (%type、%rowtype、ifif elseif elseif else、while、do..while、游标、异常、函数、过程)

变量的使用:

  • out和inout能修改会被存储过程修改的变量,但这个修改会在存储过程调用结束后才会成功修改【就好像如果在过程内部现有一条命令是修改的,可以在此命令后面查看一下变量,发现全局变量并没有改变,改变的只是局部变量】。
  • 所有MySQL变量都必须以 @ 开始
  • 存储过程中,使用局部变量可以使用select 变量名;使用全局变量可以使用select @变量名;
  • 关于具体变量的定义与使用将在我的另外一篇博文中讲解。超链接:mysql之变量

 


begin

2、 PLSQL流程控制

存储过程的创建:

  • create procedure 存储过程名 ([参数列表])  begin   sql 语句  end;
    • 参数列表的格式:[类型限定 变量名 数据类型]
      • 参数列表有自己的类型限定,这个类型限定与数据类型不同,它是限定参数的作用范围
        • in:限定这个参数是传值给存储过程,既然是传值,所以可以是变量或常量数据【in修饰的参数一般是传入存储过程中作为某些条件的,不会被存储过程修改
        • out:限定这个参数是存储过程传出的一个值,因为有值的返回,所以这个参数必须是一个变量【存储过程中会给out修饰的变量赋值,使得过程外部可以获取这个更改的值】
        • inout:inout是上面两者的叠加,既可以被存储过程内部使用,又可以修改后被外部使用,因为有值的返回,所以这个参数必须是一个变量

 

理论上,对于希望简洁代码的地方都可以使用存储过程来处理,比如希望快速使用多条select,又比如希望从数据中取出多个值赋值给变量;所以下面只给出用法,应用场景就不讲述了。

  • 1:不传入参数,只执行某些特定代码
  • 2.传入参数,并利用参数作为条件执行代码
  • 3.传入参数,并利用参数作为条件执行代码,同时利用变量获取结果。
  • 【下面的call是调用过程】

    -- 最简单的例子 create procedure myselect() begin

    select @@version;
    

    end; create procedure getInfo(in mname varchar(15)) begin select mname; end; call myselect(); call getInfo("lilie"); -- 能通过传参来获取指定内容的 create procedure getInfo2(in mname varchar(15)) begin select * from student where name =mname; end; call getInfo2("lilei"); -- 将结果赋值给一个变量传到外部 create procedure getInfo3(in mname varchar(15),out oname varchar(15)) begin select name from student where name =mname into oname; end; call getInfo3("lilei",@mname); select @mname;

 

 

//

4、 PLSQL游标

end

 --if判断---

        declare

             v_b boolean:=true;   -- := 是赋值

        begin 

                 if v_b then

                      dbms_output.put_line('ok');

                end if;

        end;

/* 定义一个整形变量 */

6、 for循环遍历

  - 最简单,用的最多,不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标,实现遍历游标最高效方式)

    declare

         cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

         for cur in c_book(i_id) --直接将入参i_id传入cursor即可

         loop

             update book set price = '53' where bookname = cur.bookname;

         end loop;

    end;

create procedure sp4(inout p4 int)

 --if else判断---

        declare

             v_b boolean:=true;

        begin

             if v_b then

                  dbms_output.put_line('ok');

             else

                  dbms_output.put_line('false');

             end if;

        end;

/*这里的DETERMINISTIC子句表示输入和输出的值都是确定的,不会再改变.我一同事说目前mysql并没有实现该功能,因此加不加都是NOT DETERMINISTIC的*/

4.2 游标的使用

        游标的使用,一定别忘了关游标。

                declare

                     v_student book%rowtype;

                     cursor c_student(v_id binary_integer) is select * from book where id>v_id;

                begin

                     open c_student(10);

                     fetch c_student into v_student;

                     close c_student;

                     dbms_output.put_line(v_student.name);

                end;

 

        如何遍历游标fetch

            游标的属性 %found,%notfound,%isopen,%rowcount。

            %found:若前面的fetch语句返回一行数据,则%found返回true,如果对未打开的游标使用则报ORA-1001异常。

            %notfound,与%found行为相反。

            %isopen,判断游标是否打开。

            %rowcount:当前游标的指针位移量,到目前位置游标所检索的数据行的个数,若未打开就引用,返回ORA-1001。

        注:

        no_data_found和%notfound的用法是有区别的,小结如下

        1)SELECT . . . INTO 语句触发 no_data_found;

        2)当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound;

        3)当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound;

        4)在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件,不要用no_data_found。

select * from test//

-- for循环,注意不需要声明变量

        begin

                 for v_i in 0..10 loop

                         dbms_output.put_line('hello'||v_i);

                 end loop;

                         dbms_output.put_line('over');

        end;                       

/****************** 带输出参数的存储过程 ************************/

1.3 只可以增删改  

     -- insert,update,delete,select都可以,create table,drop table不行。DPL,DML,和流程控制语句可以在pl/sql里用但DDL语句不行,表定义语言不可以在plsql中改变

    

    declare

       v_name student.name%type:='wang'; --声明变量并赋值

    begin

       insert into student(id,name,age) values(2,v_name,26);  --插入数据

    end;

  --------------------------------------------

    declare

       v_name student.name%type:='hexian';              --更新数据

    begin

       update student set name=v_name where id=1;

    end;

    begin

       update student set name='qinaide' where id=2;

    end;

  ------------------------------------------------- --------------

/* 查询刚刚在存储过程中使用到的变量 */

6、 PLSQL中的function

        FUNCTION和PROCEDURE的区别

        1、函数有返回值,过程没有

        2、函数调用在一个表达式中,过程则是作为pl/sql程序的一个语句

            过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程

            有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的

            主要区别不在于返回值,而在于他们的调用方式,过程是作为一个独立执行语句调用的,函数以合法的表达

            式的方式调用

    create or replace function func(v_name in varchar2)

    return varchar2

    is

    begin

       return(v_name||' hello');

    end;

    --调用:

    declare

       v_name varchar2(20);

    begin

       v_name:=func('cheng');

       dbms_output.put_line(v_name);

    end;

带out参数的函数

    create or replace function func(

       v_name in varchar2,

       v_content out varchar2

    )

    return varchar2

    is

    begin

       v_content:=v_name||' hello';

       return v_content;

    end;

    调用:

    declare

       v_name varchar2(20);

       v_name1 varchar2(20);

    begin

       v_name1:=func('susu',v_name);--返回v_name值

       dbms_output.put_line(v_name1);--打印func结果

       dbms_output.put_line(v_name);--打印v_name结果

    end;

带in out 参数的函数

    create or replace function func(

       v_name in out varchar2)

    return varchar2

    is

    begin

       v_name:=v_name||' hello';

       return 'cheng';

    end;

    调用:

    declare

       v_inout varchar2(20):='world';

       v_ret varchar2(20);

    begin

       v_ret:=func(v_inout);--返回调用v_inout值(作为出参)

       dbms_output.put_line(v_ret);--打印func结果     

       dbms_output.put_line(v_inout);--返回v_name结果

    end;

        

        

        

        

        

        

        

if p1 = 1 then

5.1.2  带一个参数的存储过程

   输入参数in,输入参数不能进行 :=赋值,但可以将它赋给as后面定义的变量;

   输入参数in,可以作为变量进行条件判断;

   默认不写就是in;

   存储过程没有重载,这个有参的say_hello会替代已经存在的无参say_hello。

     create or replace procedure say_hello(v_name in varchar2)

     as

     begin

        --v_name:='a';    --存储过程入参v_name不能做为赋值目标

        dbms_output.put_line('hello '||v_name); 

     end;

存储过程输入参数作为变量进行条件判断

     create or replace procedure say_hello(

        i_opFlag in number

     )

     as

        v_name varchar2(100);

     begin

        if i_opFlag = 1 then

           v_name :='0'; 

        else

           v_name :='haha';

        end if; 

        dbms_output.put_line('hello '||v_name); 

     end;

 

利用存储过程中定义的变量对入参的空值处理:

     create or replace procedure say_hello(

        i_name in varchar2

     )

     as

        v_name varchar2(100);

     begin

        if i_name is null then

    v_name :='0'; 

        else

    v_name :=i_name;--将入赋值给定义变量

        end if; 

        dbms_output.put_line('hello '||v_name); 

     end;

MySql存储过程简单实例:

 -- while循环

        declare

                     v_i binary_integer:=0;

        begin

                     while v_i<10 loop

                                 dbms_output.put_line('hello'||v_i );

                                 v_i:=v_i+1;

                     end loop;

                     dbms_output.put_line('over');

        end;

/* 语句体内可以执行多条sql,但必须以分号分隔 */

5、while循环遍历游标,注意,第一次游标刚打开就fetch,%found为null,进不去循环

解决方法:while nvl(c_student%found,true) loop

    declare

         v_bookname  varchar2(100);

         cursor c_book(i_id number) is select bookname from book where id = i_id;

    begin

         Open  c_book(i_id);

         while nvl(c_book%found,true) --或这种写法:while c_book%found is null or c_book%found loop    

             Fetch c_book into v_bookname;

             update book set price = '33' where bookname = v_bookname;

         End Loop;

         Close c_book;

    end;

set @pp = 4//

5.1.4 in out参数,既赋值又取值

     create or replace procedure say_hello(v_name in out varchar2)

     as

     begin

        v_name:='hi '||v_name;

     end;

     调用:

     declare

        v_inout varchar2(20):='wangsu';

     begin

        say_hello(v_inout);

        dbms_output.put_line(v_inout);    --直接输入参数

     end;

对存储过程入参赋缺省值

     create or replace procedure say_hello(

        v_name varchar2 default 'susu',

        v_content varchar2 default 'hello'

     )

     as

     begin

        dbms_output.put_line(v_name||' '||v_content);

     end;

     调用:(用指明形参名的方式调用更好)

     begin

        say_hello();

     end;

     或

     begin

        say_hello('cheng');

     end;

     或

     begin

     say_hello(v_name=>'cheng');

    end;

select @pv//

4.1 游标定义

        备注:下面提到的游标为静态cursor,包括显示和隐式。

        游标,从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的,显式cursor是静态cursor,她的作用域是全局的,但也必须明白,静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。

        cursor 游标(结果集)用于提取多行数据,定义后不会有数据,使用后才有。一旦游标被打开,就无法再次打开(可以先关闭,再打开)。

                declare

                     cursor c_student is  select * from book;

                begin

                     open c_student;

                     close c_student;

                end;

        第二种游标的定义方式,用变量控制结果集的数量。

                declare

                      v_id binary_integer;

                      cursor c_student is select * from book where id>v_id;      

                begin

                      v_id:=10;

                      open c_student;

                      close c_student;

                end;

        第三种游标的定义方式,带参数的游标,用的最多。

                declare

                     cursor c_student(v_id binary_integer) is select * from book where id>v_id;

                begin

                     open c_student(10);

                     close c_student;

                end;

drop procedure if exists sp//

3、select-------------------------------------------------------------------------------------------------------------------

     create or replace procedure say_hello(  

          i_name in varchar2,  

          o_result_msg out varchar2  

      )  

     as  

     v_price varchar2(100);   

     e_myException exception;  

  

     begin  

        select price into v_price from book where bookname = i_name;  

        o_result_msg := 'success';  

     exception  

        when no_data_found then  

             rollback;  

             o_result_msg := 'select into dail';  

     end;  

/* 去数据库查看调用之后的结果 */

1.2 %type、%rowtype、select...into

        -- %type 变量的类型和数据表中的字段的数据类型一致

        -- %rowtype  变量的类型和数据表中的一行记录数据类型一致

        --  select ... into 变量     表中查询数据并赋值,可以一次性给多个变量赋值

    declare 

      v_object CUSTOMER%Rowtype; 

    begin

      select * into v_object from customer where rownum=1;

      dbms_output.put_line(v_object.Enterprise_Code ||','||v_object.CreateDate);

    end;

create procedure sp2(out p int)

5.1.1 存储过程案例

存储过程入参,不论类型,缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间,同时,存储过程中可以再调用其它的存储过程,如果要保证存储过程之间的事务处理不受影响,可以定义为自治事务。

     create or replace procedure say_hello(

       v_name in varchar2,     --入参 ,入参和出参数据不能有长度

       v_flag number,

       o_ret out number   --出参

     )

     as

     begin

       if v_name is null and v_flag is null then       --v_name和v_flag都等于null

           o_ret := 10;

       else

           o_ret := 100; 

       end if;

     end;

对于入参为null情况下给予缺省值

     create or replace procedure say_hello(

       i_name in varchar2,

       i_flag number,

       o_ret out number

     )

     as

       v_name  varchar2(100);    --定义变量在as和begin之间,需要长度

     begin

       if i_name is null then 

          v_name := '0';

       else

          v_name := i_name;

       end if;

       insert into phone(..,wname..,) values(..,v_name,..);  

     end;

或直接在insert语句中调用nvl函数赋缺省值

     insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值

call sp1(1)//

4.3 游标实例:

        create table BOOK

        (

            ID       VARCHAR2(10) not null,

            BOOKNAME VARCHAR2(10) not null,

            PRICE    VARCHAR2(10) not null,

            CID      VARCHAR2(10) not null

        );

        -- %rowcount是SQL的属性表示影响了多少条记录       

//

1.1 声明变量赋值并输出

 set serveroutput on --设置数据库输出,默认为关闭,每次重新打开窗口需要重新设置。

    Declare

       result integer;  --声明变量【变量名 变量类型】

    begin

       result:=10+3*4-20+5**2;  --给变量赋值【:=】

       dbms_output.put_line('运算结果是:'||to_char(result));

    end;

 --------------------------------------------------------------------------------------------------

    dbms_output.put_line函数输出只能是字符串,因此利用to_char函数将数值型结果转换为字符型。

    运算的优先次序为NOT、AND和OR。

    To_char:将其他类型数据转换为字符型。

    To_date:将其他类型数据转换为日期型。

    To_number:将其他类型数据转换为数值型。

select max(id) into p2 from test;

/******************** 带输入和输出参数的存储过程 ***********************/

drop procedure if exists sp1 //

/* 执行插入操作 */

id int(11) NULL

end

/* 将输入参数的值赋给变量 */

select @pg;

end

//

将下面的语句复制粘贴可以一次性执行完,我已经测试过,没有问题!

CREATE TABLE test(

create procedure sp1(in p int)

call sp2(@pv)//

DROP TABLE if exists test //

select @ret//

insert into test(id) values(@v);

/********************* 创建表 *****************************/

drop procedure if exists sp2 //

set @pg = 400;

drop procedure if exists sp3 //

set @pg = 500;

comment 'insert into a int value'

/* 调用该存储过程,注意:输出参数必须是一个带@符号的变量 */

call sp4(@pp)//

/********************************************************/

else

begin

create procedure sp3(in p1 int , out p2 int)

set v1 = p;

/* 这里需要先设置一个已赋值的变量,然后再作为参数传入 */

drop procedure if exists sp4 //

begin

end if;

) //

delimiter //

call sp()//

end//

CREATE PROCEDURE sp() select 1 //

set @v = 20;

//转载  http://www.cnblogs.com/zhuawang/p/4185302.html

end if;

if p4 = 4 then

declare v1 int;

insert into test(id) values(v1);

/* 用@符号加变量名的方式定义一个变量,与declare类似 */

call sp3(1,@ret)//

/********************** 最简单的一个存储过程 **********************/

/* 调用这个存储过程  */

编辑:数据库 本文来源:比如希望快速使用多条select,分支结构

关键词: