精华内容
下载资源
问答
  • 一个是存储过程下面,还有一个是用户定义的函数下面,下面我就这2个下面的存储过程分别说下他们的区别和联系。 对于java代码来说,这2个下面的调用方法都是一样的。(试想下,如果不一样的话,那么岂不是乱了。就是...
     
    
    Sqlserver中的存储过程可以写在2个地方。一个是存储过程下面,还有一个是用户定义的函数下面,下面我就这2个下面的存储过程分别说下他们的区别和联系。
     
    对于java代码来说,这2个下面的调用方法都是一样的。(试想下,如果不一样的话,那么岂不是乱了。就是为了减少代码的修改次数才写得存储过程。)这2个下面的存储过程下面分别给了1个例子。
     
    1 用户定义的函数:
    CREATE function fun_insert(@count varchar(100),@fnames varchar(100),@faddr varchar(100))
    returns varchar(100)
     
    AS
    begin
     
    declare @tcount integer
    declare @str varchar(100)
    select @tcount=count(*)+1 from men
    set @str = 'a你输入的是'+@fnames+@faddr+'一共有'+str(@count)+'条数据'
    return @str
    end
     
    2 存储过程
    CREATE PROCEDURE InsertUser
    @loginName varchar(50),
    @realName varchar(50),
    @password varchar(50),
    @userId int output
     
    As
    Set NOCOUNT ON
    If Exists (select userID from tsysuser Where loginName = @loginName)
    RETURN 0
    ELSE
    Begin
    INSERT INTO tsysuser (loginName,realName,password) VALUES(@loginName,@realName,@password)
    SET @userID = @@IDENTITY
    RETURN @userID
    Set NOCOUNT OFF
    End
    GO
     
    这里先说下小弟苦苦找了半天的一个错。就是函数里只能使用insert,update,delete来操作函数内定义的[[表变量]],不能操作[[表对象]].。所以,在用户定地的函数中不能直接操作实体表。只可以操作内建表。这是首先要了解的。我就是在这上面找了快半天了。
     
    声明方面:
    除了create后面的那个关键字不同之外。用户自定义函数需要用括号包住输入的参数,并且返回值需要在表头中声明。而存储过程不加括号,并且不声明返回值。也就是说,存储过程的返回值是需要在里面中体现的。单看表头并不知道返回值的类型。
     
    包体方面:
    声明后面都是as关键字,这是一样的。但是后面就不太一样了。
    用户自定义的函数是由一对begin和end包住中间的代码体。Begin代表存储过程的开始。End代表存储过程的结束。
    存储过程虽然也是as开始。但是有一对Set NOCOUNT ON和Set NOCOUNT OFF。并且最后在end后面还加上了一个GO关键字。Set NOCOUNT ON和Set NOCOUNT OFF是优化存储过程代码的标示。具体不细说。Baidu下就知道了。
     
    相同的特点:
    声明变量的时候都是用@符号开始。赋值用Set关键字。这是2个不同包下面代码的相同点。也是和oracle不同的地方。具体的语法还是baidu下吧。这里只是简单的介绍下。其实很多东西只要知道有救行。具体的还得实际操作。知道大概其的规则了,遇到问题了再细化也很快的。行,先写这么多。上班没活干啊。。。睡觉 Zzzzzz….
    展开全文
  • 存储过程和函数中,可以定义和使用变量。用户可以使用关键字DECLARE来定义变量,然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。 1. 定义变量 在MySQL中,可以使用DECLARE关键字来定义变量。定义变量...

    在存储过程中使用变量

    在存储过程和函数中,可以定义和使用变量。用户可以使用关键字DECLARE来定义变量,然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。

    1. 定义变量

    在MySQL中,可以使用DECLARE关键字来定义变量。定义变量的基本语法如下:

    	DECLARE var_name[,…] type [DEFAULT value]   
    

    其中,关键字DECLARE是用来声明变量的;参数var_name是变量的名称,可以同时定义多个变量;参数type用来指定变量的类型;DEFAULT value子句将变量默认值设置为value,没有使用DEFAULT子句时,默认值为NULL。
    定义变量cid,数据类型为INT型,默认值为10,代码如下:

    DECLARE cid INT DEFAULT 10;    
    

    2. 为变量赋值

    在MySQL中可以使用关键字SET来为变量赋值,SET语句的基本语法如下:

    SET var_name=expr[,var_name=expr]…
    

    其中,关键字SET用来为变量赋值;参数var_name是变量的名称;参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开。
    例如,将变量tmp_id赋值为88,代码如下:

    SET tmp_id = 88;
    

    在MySQL中,还可以使用SELECT…INTO语句为变量赋值。其基本语法如下:

     SELECT col_name[,…] INTO var_name[,…]     
     FROM table_name WHERE condition         
    

    其中,参数col_name表示查询的字段名称;参数var_name是变量的名称;参数table_name指表的名称;参数condition指查询条件。

    【示例11-2】从表employee中查询id为3的记录,将该记录的id值赋给变量tmp_id,代码如下:

    SELECT id INTO tmp_id
    FROM grade WEHRE id=sid;
    
    mysql>  use school;   #选择数据库school                                             
    mysql>  drop  PROCEDURE if exists query_student_class_info;                                                                             
    mysql> DELIMITER $$                                                                                
    mysql> create procedure  query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount  int)                                   
            BEGIN   
                declare tmp_name varchar(128);
                declare tmp_count int;
                declare tmp_cid  int;
                select class_id into tmp_cid from student where id = sid;         
                select name, count into tmp_name, tmp_count from class where id = tmp_cid;
                set cname = tmp_name, ccount = tmp_count;
             END;    
             $$                                                                           
             DELIMITER ;                                                                 
    mysql>  call query_student_class_info(4, @name, @count);    #调用存储过程  
    mysql>  select @name, @count;
    

    具体操作

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    结语:

    时间: 2020-07-14

    展开全文
  • 存储过程定义开始

    千次阅读 2013-03-19 11:53:25
    1. 使用存储过程的优点有: (1)存储过程在服务器端运行,执行速度快。 (2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行...


    1. 使用存储过程的优点有:

    (1)存储过程在服务器端运行,执行速度快。

    (2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。

    (3)确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上述操作对数据库信息访问的权限。

    2.创建存储过程可以使用create procedure语句。

    要在MySQL 5.1中创建存储过程,必须具有CREATE routine权限。要想查看数据库中有哪些存储过程,可以使用SHOW PROCEDURE STATUS命令。要查看某个存储过程的具体信息,可使用SHOWCREATE PROCEDURE sp_name命令,其中sp_name是存储过程的名称。

    CREATE PROCEDURE的语法格式:

    CREATE PROCEDURE sp_name ([proc_parameter[,...]])

       [characteristic ...] routine_body

    其中,proc_parameter的参数如下:

    [ IN | OUT | INOUT ] param_name type

    characteristic特征如下:

      language SQL

     | [NOT] DETERMINISTIC

     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

     | SQL SECURITY { DEFINER | INVOKER }

     | COMMENT 'string'

    说明:

    ●   sp_name:存储过程的名称,默认在当前数据库中创建。需要在特定数据库中创建存储过程时,则要在名称前面加上数据库的名称,格式为:db_name.sp_name。值得注意的是,这个名称应当尽量避免取与MySQL的内置函数相同的名称,否则会发生错误。

     ●   proc_parameter:存储过程的参数,param_name为参数名,type为参数的类型,当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候,存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数,但是名称后面的括号是不可省略的。

    注意:参数的名字不要等于列的名字,否则虽然不会返回出错消息,但是存储过程中的SQL语句会将参数名看做列名,从而引发不可预知的结果。

    characteristic:存储过程的某些特征设定,下面一一介绍:

    language sql表明编写这个存储过程的语言为SQL语言,目前来讲,MySQL存储过程还不能用外部编程语言来编写,也就是说,这个选项可以不指定。将来将会对其扩展,最有可能第一个被支持的语言是PHP

    deterministic设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果,设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。

    contains SQL表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句,但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定,默认的是CONTAINS SQL

    SQL SECURITY:SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户(DEFINER)的许可来执行,还是使用调用者(INVOKER)的许可来执行。默认值是DEFINER。

    COMMENT 'string':对存储过程的描述,string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。

    ●   routine_body:这是存储过程的主体部分,也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句,这个部分总是以begin开始,以end结束。当然,当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。

    3.   在开始创建存储过程之前,先介绍一个很实用的命令,即delimiter命令。在MySQL中,服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候,存储过程体中可能包含多个SQL语句,每个SQL语句都是以分号为结尾的,这时服务器处理程序的时候遇到第一个分号就会认为程序结束,这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。

    DELIMITER语法格式为:DELIMITER $$

    说明:$$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”,一个“¥”、数字、字母等都可以。当使用DELIMITER命令时,应该避免使用反斜杠(“\”)字符,因为那是MySQL的转义字符。

    例:创建存储过程,实现的功能是删除一个特定学生的信息。

    DELIMITER $$

    CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))

    BEGIN

    DELETE FROM XS WHERE 学号=XH;

    END $$

    DELIMITER ;

    说明:当调用这个存储过程时,MySQL根据提供的参数XH的值,删除对应在XS表中的数据。

    在关键字BEGIN和END之间指定了存储过程体,当然,BEGIN-END复合语句还可以嵌套使用。

    4.  局部变量

    在存储过程中可以声明局部变量,它们可以用来存储临时结果。要声明局部变量必须使用declare语句。在声明局部变量的同时也可以对其赋一个初始值。

    DECLARE语法格式:DECLARE var_name[,...] type [DEFAULT value]

    说明:var_name为变量名;type为变量类型;default子句给变量指定一个默认值,如果不指定默认为NULL的话。可以同时声明多个类型相同的局部变量,中间用逗号隔开。

    例: 声明一个整型变量和两个字符变量。

    DECLARE num INT(4);

    DECLARE str1, str2 VARCHAR(6);

    declare n char(10) default ‘abcdefg’;

    说明:局部变量只能在BEGIN…END语句块中声明。

    局部变量必须在存储过程的开头就声明,声明完后,可以在声明它的BEGIN…END语句块中使用该变量,其他语句块中不可以使用它。

    在存储过程中也可以声明用户变量,不过千万不要将这两个混淆。局部变量和用户变量的区别在于:局部变量前面没有使用@符号,局部变量在其所在的BEGIN…END语句块处理完后就消失了,而用户变量存在于整个会话当中。

    5.  使用SET语句赋值

    要给局部变量赋值可以使用SET语句,SET语句也是SQL本身的一部分。语法格式为:SET  var_name = expr [,var_name = expr] ...

    例: 在存储过程中给局部变量赋值。

    SET num=1, str1= 'hello';

    说明:与声明用户变量时不同,这里的变量名前面没有@符号。注意,例中的这条语句无法单独执行,只能在存储过程和存储函数中使用。

    6. SELECT...INTO语句(重点)

    使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此,返回的结果只能有一行。语法格式为:

    SELECT col_name[,...] INTO var_name[,...]  table_expr

    说明:col_name是列名,var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分,这里不再叙述。

    例: 在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。

    SELECT 姓名,专业名 INTO name, project

       FROMXS;  WHERE 学号= '081101';

    7.  流程控制语句

    在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。例如:IF语句、CASE语句、LOOP语句、WHILE语句、iterate语句和LEAVE语句。

    (1)IF语句

    IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

    语法格式为:

    IF 判断的条件THEN 一个或多个SQL语句

    [ELSEIF判断的条件THEN一个或多个SQL语句] ...

    [ELSE一个或多个SQL语句]

    END IF

    说明:当判断条件为真时,就执行相应的SQL语句。

    IF语句不同于系统的内置函数IF()函数,IF()函数只能判断两种情况,所以请不要混淆。

    例: 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。

    DELIMITER $$

    CREATE PROCEDURE XSCJ.COMPAR

    (IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )

    BEGIN

    IFK1>K2 THEN

        SET K3= '大于';

    ELSEIFK1=K2 THEN

        SET K3= '等于';

    ELSE

        SET K3= '小于';

    ENDIF;

    END$$

    DELIMITER ;

    说明:存储过程中K1和K2是输入参数,K3是输出参数。

    (2)CASE语句

    前面已经介绍过了,这里介绍CASE语句在存储过程中的用法,与之前略有不同。语法格式为:

    CASE case_value

       WHEN when_value THEN statement_list

       [WHEN when_value THEN statement_list] ...

       [ELSE statement_list]

    END CASE

    或者:

    CASE

       WHEN search_condition THEN statement_list

       [WHEN search_condition THEN statement_list] ...

       [ELSE statement_list]

    END CASE

    说明:一个CASE语句经常可以充当一个IF-THEN-ELSE语句。

    第一种格式中case_value是要被判断的值或表达式,接下来是一系列的WHEN-THEN块,每一块的when_value参数指定要与case_value比较的值,如果为真,就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END CASE结束。

    第二种格式中CASE关键字后面没有参数,在WHEN-THEN块中,search_condition指定了一个比较表达式,表达式为真时执行THEN后面的语句。与第一种格式相比,这种格式能够实现更为复杂的条件判断,使用起来更方便。

    例: 创建一个存储过程,针对参数的不同,返回不同的结果。

    DELIMITER $$

    CREATE PROCEDURE XSCJ.RESULT

    (IN str VARCHAR(4), OUT sex VARCHAR(4) )

    BEGIN

     CASE str

       WHEN'M' THEN SET sex='男';

       WHEN'F' THEN SET sex='女';

       ELSE  SET sex='无';

       ENDCASE;

    END$$

    DELIMITER ;

    例: 用第二种格式的CASE语句创建以上存储过程。程序片段如下:

    CASE

       WHENstr='M' THEN SET sex='男';

       WHENstr='F' THEN SET sex='女';

       ELSE  SET sex='无';

    END CASE;

    (3)循环语句

    MySQL支持3条用来创建循环的语句:while、repeat和loop语句。在存储过程中可以定义0个、1个或多个循环语句。

    ●   WHILE语句语法格式为:

    [begin_label:] WHILE search_condition  DO

    statement_list

    END WHILE [end_label]

    说明:语句首先判断search_condition是否为真,不为真则执行statement_list中的语句,然后再次进行判断,为真则继续循环,不为真则结束循环。begin_label和end_label是WHILE语句的标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们的名字必须是相同的。

    例: 创建一个带WHILE循环的存储过程。

    DELIMITER $$

    CREATE PROCEDURE dowhile()

    BEGIN

       DECLARE v1 INT DEFAULT5;

       WHILE  v1 > 0 DO

             SET v1 = v1-1;

       END WHILE;

    END $$

    DELIMITER ;

    ●   repeat语句格式如下:

    [begin_label:] REPEAT

         statement_list

    UNTIL search_condition

    END REPEAT [end_label]

    说明:REPEAT语句首先执行statement_list中的语句,然后判断search_condition是否为真,为真则停止循环,不为真则继续循环。REPEAT也可以被标注。

    例: 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下:

    REPEAT

        v1=v1-1;

        UNTIL v1<1;

    END REPEAT;

    说明:REPEAT语句和WHILE语句的区别在于:REPEAT语句先执行语句,后进行判断;而WHILE语句是先判断,条件为真时才执行语句。

    ●   LOOP语句语法格式如下:

    [begin_label:] LOOP

             statement_list

    END LOOP [end_label]

    说明:LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造,statement_list是需要重复执行的语句。在循环内的语句一直重复至循环被退出,退出时通常伴随着一个LEAVE 语句。

    LEAVE语句经常和BEGIN...END或循环一起使用。结构如下:

    LEAVE label ; label是语句中标注的名字,这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

    例: 创建一个带LOOP语句的存储过程。

    DELIMITER $$

    CREATE PROCEDURE doloop()

    BEGIN

        SET @a=10;

        Label: LOOP

              SET @a=@a-1;

              IF @a<0 THEN

                  LEAVELabel;

              END IF;

        END LOOPLabel;

    END$$

    DELIMITER ;

    循环语句中还有一个iterate语句,它只可以出现在LOOP、REPEAT和WHILE语句内,意为“再次循环”。它的格式为:ITERATE label

    说明:该语句格式与LEAVE差不多,区别在于:LEAVE语句是离开一个循环,而ITERATE语句是重新开始一个循环。

     

    8.我们调用此存储过程来查看最后结果。调用该存储过程使用如下命令:CALL doloop();

    接着,查看用户变量的值:  SELECT@a;

       语法格式:CALL sp_name([parameter[,...]])

    说明:sp_name为存储过程的名称,如果要调用某个特定数据库的存储过程,则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数,这条语句中的参数个数必须总是等于存储过程的参数个数。

    例:创建一个存储过程,有两个输入参数:XH和KCM,要求当某学生某门课程的成绩小于60分时将其学分修改为零,大于等于60分时将学分修改为此课程的学分。

    DELIMITER $$

    CREATE PROCEDURE XSCJ.DO_UPDATE(IN XHCHAR(6), IN KCM CHAR(16))

    BEGIN

       DECLARE  KCH CHAR(3);

       DECLARE  XF TINYINT;

       DECLARE  CJ TINYINT;

     SELECT课程号, 学分 INTO KCH, XFFROM KC WHERE 课程名=KCM;

     SELECT成绩 INTO CJ FROM XS_KC WHERE 学号=XH AND 课程号=KCH;

       IF CJ<60 THEN

         UPDATE XS_KC SET 学分=0 WHERE 学号=XH AND 课程号=KCH;

       ELSE

         UPDATE XS_KC SET 学分=XF WHERE 学号=XH AND 课程号=KCH;

       END IF;

    END$$

    DELIMITER ;

    9.        存储过程创建后需要删除时使用DROP PROCEDURE语句。

    在此之前,必须确认该存储过程没有任何依赖关系,否则会导致其他与之关联的存储过程无法运行。

    语法格式为:  DROPPROCEDURE  [IF EXISTS] sp_name

    说明:sp_name是要删除的存储过程的名称。IF EXISTS子句是MySQL的扩展,如果程序或函数不存在,它防止发生错误。

    例: 删除存储过程dowhile:DROP PROCEDURE IF EXISTS dowhile;

    10.  使用ALTER PROCEDURE语句可以修改存储过程的某些特征。

    语法格式为:ALTER PROCEDURE sp_name [characteristic ...]

    其中,characteristic为:

    { CONTAINS SQL | NO SQL | READS SQLDATA | MODIFIES SQL DATA }

    | SQL SECURITY { DEFINER | INVOKER }

    | COMMENT 'string'

    说明:characteristic是存储过程创建时的特征,在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值,存储过程的特征就随之变化。

    如果要修改存储过程的内容,可以使用先删除再重新定义存储过程的方法。

    例: 使用先删除后修改的方法修改例7.12中的存储过程。

    DELIMITER $$

    DROP PROCEDURE IF EXISTS DO_QUERY;

    CREATE PROCEDURE DO_QUERY()

    BEGIN

    SELECT * FROM XS;

    END$$

    DELIMITER ;

      ***11  往后为选看内容。。非重点!!

    11.  SQL语句中的错误提示

    在存储过程中处理SQL语句可能导致一条错误消息。例如,向一个表中插入新的行而主键值已经存在,这条INSERT语句会导致一个出错消息,并且MySQL立即停止对存储过程的处理。每一个错误消息都有一个唯一代码和一个SQLSTATE代码。例如,SQLSTATE 23000属于如下的出错代码:

    Error 1022, "Can't write;duplicate(重复) key intable"

    Error 1048, "Column cannot benull"

    Error 1052, "Column is ambiguous(歧义)"

    Error 1062, "Duplicate entry forkey"

    MySQL手册的“错误消息和代码”一章中列出了所有的出错消息及它们各自的代码。

    为了防止MySQL在一条错误消息产生时就停止处理,需要使用到DECLAREhandler语句。该语句语句为错误代码声明了一个所谓的处理程序,它指明:对一条SQL语句的处理如果导致一条错误消息,将会发生什么。

    DECLARE HANDLER语法格式为:

    DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement

    其中,handler_type为:

     Continue

    | EXIT

    | UNDO

    condition_value

     SQLstate [VALUE] sqlstate_value

    | condition_name

    | SQLwarning

    | NOT FOUND

    | SQLexception

    | mysql_error_code

    说明:

    ●   handler_type:处理程序的类型,主要有三种:CONTINUE、EXIT和UNDO。对CONTINUE处理程序,MySQL不中断存储过程的处理。对于EXIT处理程序,当前   BEGIN...END复合语句的执行被终止。UNDO处理程序类型语句暂时还不被支持。

    ●  condition_value:给出SQLSTATE的代码表示。

       condition_name是处理条件的名称,接下来会讲到。

       SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

    mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值,MySQL错误代码也被支持,表示的形式为:ERROR= 'xxxx'。

    ●   sp_statement:处理程序激活时将要执行的动作。

    例: 创建一个存储过程,向XS表插入一行数据('081101', '王民', '计算机', 1, '1990-02-10',50 , NULL, NULL),已知学号081101在XS表中已存在。如果出现错误,程序继续进行。

    USE XSCJ;

    DELIMITER $$

    CREATE PROCEDURE MY_INSERT ()

    BEGIN

       DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;

       SET@x=2;

       INSERTINTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

       SET@x=3;

    END$$

    DELIMITER ;

    说明:在调用存储过程后,未遇到错误消息时处理程序未被激活,当执行INSERT语句出现出错消息时,MySQL检查是否为这个错误代码定义了处理程序。如果有,则激活该处理程序,本例中,INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附加语句(SET @x2=1)。此后,MySQL检查处理程序的类型,这里的类型为CONTINUE,因此存储过程继续处理,将用户变量x赋值为3。如果这里的INSERT语句能够执行,处理程序将不被激活,用户变量x2将不被赋值。

    注意:不能为同一个出错消息在同一个BEGIN-END语句块中定义两个或更多的处理程序。

    为了提高可读性,可以使用DECLARE CONDITION语句为一个SQLSTATE或出错代码定义一个名字,并且可以在处理程序中使用这个名字。

    DECLARE CONDITION语法格式为:

    DECLARE condition_name CONDITION FORcondition_value

    其中,condition_value:

     SQLSTATE [VALUE] sqlstate_value

    | mysql_error_code

    说明:condition_name是处理条件的名称,condition_value为要定义别名的SQLSTATE或出错代码。

    例: 修改上例中的存储过程,将SQLSTATE '23000' 定义成NON_UNIQUE,并在处理程序中使用这个名称。程序片段为:

    BEGIN

       DECLARE NON_UNIQUE CONDITION FOR SQLSTATE '23000';

       DECLARE CONTINUE HANDLER FOR NON_UNIQUE SET @x2=1;

       SET @x=2;

       INSERT INTO XS VALUES('081101', '王民', '计算机', 1, '1990-02-10', 50 , NULL, NULL);

       SET @x=3;

    END;

    12.  游标

    一条SELECT...INTO语句返回的是带有值的一行,这样可以把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据,如果要处理它需要引入游标这一概念。MySQL支持简单的游标。在MySQL中,游标一定要在存储过程或函数中使用,不能单独在查询中使用。

    使用一个游标需要用到4条特殊的语句:DECLARE CURSOR(声明游标)、OPEN CURSOR(打开游标)、FETCH CURSOR(读取游标)和CLOSE CURSOR(关闭游标)。

    如果使用了DECLARE CURSOR语句声明了一个游标,这样就把它连接到了一个由SELECT语句返回的结果集中。使用OPEN CORSOR语句打开这个游标。接着,可以用FETCH CURSOR语句把产生的结果一行一行地读取到存储过程或存储函数中去。游标相当于一个指针,它指向当前的一行数据,使用FETCH CORSOR语句可以把游标移动到下一行。当处理完所有的行时,使用CLOSECURSOR语句关闭这个游标。

    (1)声明游标

    语法格式:DECLAREcursor_name cursor for select_statement

    说明:cursor_name是游标的名称,游标名称使用与表名同样的规则。select_statement是一个SELECT语句,返回的是一行或多行的数据。这个语句声明一个游标,也可以在存储过程中定义多个游标,但是一个块中的每一个游标必须有唯一的名字。

    注意:这里的SELECT子句不能有INTO子句。

    下面的定义符合一个游标声明:

    DECLARE XS_CUR1 CURSOR FOR

       SELECT 学号,姓名,性别,出生日期,总学分

           FROM XS

           WHERE 专业名 = '计算机';

    注意:游标只能在存储过程或存储函数中使用,例中语句无法单独运行。

    (2)打开游标

    声明游标后,要使用游标从中提取数据,就必须先打开游标。在MySQL中,使用OPEN语句打开游标,其格式为:OPEN cursor_name

    在程序中,一个游标可以打开多次,由于其他的用户或程序本身已经更新了表,所以每次打开结果可能不同。

    (3)读取数据

    游标打开后,就可以使用fetch…into语句从中读取数据。

    语法格式:FETCH cursor_nameINTO var_name [, var_name] ...

    说明:FETCH ...INTO语句与SELECT...INTO语句具有相同的意义,FETCH语句是将游标指向的一行数据赋给一些变量,子句中变量的数目必须等于声明游标时SELECT子句中列的数目。var_name是存放数据的变量名。

    (4)关闭游标

    游标使用完以后,要及时关闭。关闭游标使用CLOSE语句,格式为:

    CLOSE cursor_name语句参数的含义与OPEN语句中相同。

    例如: CLOSE XS_CUR2  将关闭游标XS_CUR2。

    例: 创建一个存储过程,计算XS表中行的数目。

    DELIMITER $$

    CREATE PROCEDURE compute (OUT NUMBERINTEGER)

    BEGIN

       DECLAREXH CHAR(6);

       DECLAREFOUND BOOLEAN DEFAULT TRUE;

       DECLARENUMBER_XS CURSOR FOR

         SELECT学号 FROM XS;

       DECLARECONTINUE HANDLER FOR NOT FOUND

         SETFOUND=FALSE;

       SETNUMBER=0;

       OPENNUMBER_XS;

       FETCHNUMBER_XS INTO XH;

       WHILEFOUND DO

         SETNUMBER=NUMBER+1;

         FETCHNUMBER_XS INTO XH;

       ENDWHILE;

       CLOSENUMBER_XS;

    END$$

    DELIMITER ;


    展开全文
  • 如何授予某个用户只能查看某些存储过程定义权限,而不能让用户去修改、执行存储过程。看似简单的问题,却因为从没有碰到这样的需求。花了点时间才梳理、总结清楚。 关于ORACLE账号的权限问题,一般分为两种权限...

    如何只授予用户查看存储过程定义的权限
    如何授予某个用户只能查看某些存储过程的定义权限,而不能让用户去修改、执行存储过程。看似简单的问题,却因为从没有碰到这样的需求。花了点时间才梳理、总结清楚。

    关于ORACLE账号的权限问题,一般分为两种权限:

    系统权限: 允许用户执行特定的数据库动作,如创建表、创建索引、创建存储过程等

    对象权限: 允许用户操纵一些特定的对象,如读取视图,可更新某些列、执行存储过程等

    像这种查看存储过程定义的权限为对象权限,但是我们还是首先来看看关于存储过程的系统权限吧:

    PRIVILEGE NAME PROPERTY ——— —————————————- ———- -140 CREATE PROCEDURE 0 -141 CREATE ANY PROCEDURE 0 -142 ALTER ANY PROCEDURE 0 -143 DROP ANY PROCEDURE 0 -144 EXECUTE ANY PROCEDURE 0 -241 DEBUG ANY PROCEDURE 0
    如上所示,关于存储过程的系统权限一般有六种: CREATE PROCEDURE、CREATE ANY PROCEDURE、 ALTER ANY PROCEDURE、DROP ANY PROCEDURE、 EXECUTE ANY PROCEDURE、DEBUG ANY PROCEDURE. 那么关于存储过程的对象权限又有那些呢? 如下例子所示,在用户ESCMUSER下创建存储过程PROC_TEST

    CREATE OR REPLACE PROCEDURE ESCMUSER.PROC_TEST AS BEGIN DBMS_OUTPUT.PUT_LINE(‘It is only test’); END;
    使用system用户创建用户TEMP,如下所示

    SQL> create user temp identified by temp; User created. SQL> grant connect,resource to temp; Grant succeeded.
    在用户ESCMUSER下将存储过程PROC_TEST的所有权限授予给用户TEMP。 那么我们发现存储过程的对象权限只有EXECUTE、DEBUG权限

    SQL> COL GRANTEE FOR A12; SQL> COL TABLE_NAME FOR A30; SQL> COL GRANTOR FOR A12; SQL> COL PRIVILEGE FOR A8; SQL> SELECT * FROM USER_TAB_PRIVS_MADE WHERE GRANTEE=’TEMP’; GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE ———- ——————— ———— ————————— — — TEMP PROC_TEST ESCMUSER DEBUG NO NO TEMP PROC_TEST ESCMUSER EXECUTE NO NO SQL>

    将存储过程PORC_TEST的权限从TEMP用户收回,然后授予用户TEMP关于存储过程PROC_TEST的DEBUG权限

    SQL>REVOKE ALL ON PROC_TEST FROM TEMP; SQL>GRANT DEBUG ON PROC_TEST TO TEMP; 那么TEMP用户此时执行存储过程报权限不足

    SQL> SET SERVEROUT ON; SQL> EXEC escmuser.proc_test; begin escmuser.proc_test; end; ORA-06550: line 2, column 16: PLS-00904: insufficient privilege to access object ESCMUSER.PROC_TEST ORA-06550: line 2, column 7: PL/SQL: Statement ignored 此时,如果修改存储过程PROC_TEST就会ORA-01031权限不足问题。但是你可以在PL/SQL Developer工具或使用下面视图查看存储过程的定义。如下所示。

    SELECT * FROM ALL_SOURCE WHERE NAME=’PROC_TEST’

    所以,只需要授予存储过程的DEBUG权限给某个用户,就可以实现只授予用户查看存储过程定义的权限,而限制用户修改、执行存储过程。从而达到只授权用户查看存储过程定义的权限

    展开全文
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...
  • oracle存储过程中游标定义

    千次阅读 2019-04-16 15:39:09
    游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或...
  • MySQL存储过程定义及常见函数详解

    千次阅读 2018-07-04 11:05:06
    存储过程简介SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行...
  • 二者本质上没有什么区别。当存储过程和函数被执行的时候,SQL Manager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQL Manager就会对存储过程和函数进行编译。
  • 存储过程、触发器和用户自定义函数实验 实验内容一 练习教材中存储过程、触发器和用户自定义函数的例子。教材中的BookSales数据库,在群共享中,文件名为BookSales.bak。 实验内容二 针对附件1中的教学活动数据库,...
  • MySQL数据库存储过程讲解与实例

    万次阅读 多人点赞 2018-06-03 00:48:17
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • MySQL--存储过程、局部变量与用户变量[@]1、什么是存储过程1.1存储过程定义与作用1.1存储过程的创建与调用2、局部变量与用户变量2.1 局部变量2.2 用户变量3、题目部分4、文末彩蛋--轻松一刻    &...
  • sql 存储过程

    千次阅读 2018-11-11 21:16:54
    存储过程是存放在数据库服务器上的预先定义与编译好的T-SQL语句集合,是一个独立的数据库对象。 2.特点 实现了模块化编程。 存储过程具有对数据库立即访问的功能。 使用存储过程可以加快程序的运行速度。 使用存储...
  • 在使用时候,用户通过指定已经定义存储过程名字并给出相应的存储过程参数来调用并执行它,从而完成一个或一系列的数据库操作。2、存储过程的创建Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常...
  • Oracle存储过程介绍

    千次阅读 2017-03-30 22:58:10
    用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程存储过程是由流控制和SQL 语句书写的过程,...
  • Oracle存储过程

    千次阅读 2014-01-04 23:16:18
    一、 存储过程 ...1、定义 ...所谓存储过程(Stored Procedure),就是一组用于完成特定数据库功能的SQL语句集,该SQL...在使用时候,用户通过指定已经定义存储过程名字并给出相应的存储过程参数 来调用并执行它
  • 数据库存储过程

    千次阅读 2019-02-18 14:44:00
     存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。 用来执行管理任务或应用复杂的业务规则。...
  • MySql存储过程与函数

    千次阅读 2015-08-06 08:21:26
    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中...
  • MySQL 存储过程

    千次阅读 多人点赞 2018-12-10 20:55:26
    MySQL 存储过程 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象 存储过程就是具有名字的一段代码,用来完成一个特定的功能。 创建的存储过程保存在数据库的数据...
  • 存储过程简介

    千次阅读 2016-12-16 10:45:52
    用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量 。同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以...
  • oracle 存储过程

    千次阅读 2014-01-21 13:59:42
    创建和删除存储过程   创建存储过程,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下: CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数...
  • 面试篇——存储过程和触发器

    千次阅读 多人点赞 2019-01-01 21:39:38
    (1)存储过程是procedure用户定义的一系列sql语句的集合,涉及特定表或其他对象的任务,用户可以调用存储过程。 (2)函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。 (3)...
  • 存储过程

    千次阅读 2010-02-04 16:10:00
    ----start 一:简介  存储过程是一个能够封装SQL 语句和业务逻辑的数据库应用对象,DB2 存储过程... 存储过程可以被客户机应用程序、其他存储过程用户定义函数或触发器调用,在 DB2 v9.5 中,一次最多可以嵌套
  • Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。 一个存储过程是一个可编程的函数,它在数据库中创建...
  • SQL Server存储过程

    千次阅读 2018-12-18 18:48:59
    用户通过制定存储过程的名称并给出参数来执行它; SQL Server为了实现特定任务,而将一些多次调用的固定操作语句编写成程序段,这些程序段存储在服务器上,由数据库服务器来调用; 命名前缀:proc_名称,proc是...
  • 存储过程用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。    存储过程和函数...
  • mysql中存储过程和函数区别

    千次阅读 2018-05-23 18:11:25
    用户定义函数不能用于执行一组修改全局数据库状态的操作。2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,IN...
  • 存储过程详解

    千次阅读 2017-05-01 09:53:54
    什么是存储过程存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代码块取一个名字,在用到这个功能的时候...
  • mysql 储存过程

    千次阅读 2018-06-13 23:03:30
    定义存储过程存储在数据库目录中的一段声明性SQL语句。 触发器,其他存储过程以及Java,Python,PHP等应用程序可以调用存储过程。自身的存储过程称为递归存储过程。大多数数据库管理系统支持递归存储过程。 但是...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 384,569
精华内容 153,827
关键字:

存储过程只能用户自己定义