精华内容
下载资源
问答
  • mysql的存储过程 删除数据库所有表的存储过程
  • 今天为了实现SQLServer/” target=”_blank”>sqlserver的复制功能,因为以前删除了很多的sqlserver的一些会导致... 一, 将有安全问题的SQL过程删除.比较全面.一切为了安全! 删除了调用shell,注册表,COM组件的破坏权
  • 一、 实验目的 1. 掌握T-SQL流控制语句。 2. 掌握创建存储过程的方法。...要求在创建存储过程前请判断该存储过程是否已创建,若已创建则先删除,并给出“已删除!”信息,否则就给出“不存在,可创建!”的信息。
  • 一、实验目的 1.了解存储过程的概念、优点 2.熟练掌握创建存储过程的方法 3.熟练掌握存储过程的调用方法 4.了解触发器的概念、优点 5.掌握触发器的方法和步骤 6.掌握触发器的使用
  • 文章目录符号表示创建存储过程执行存储过程查看存储过程修改存储过程删除存储过程例子 存储过程是一种数据库对象,是为了实现某个特定任务,将一组预编译的SQL语句以一个存储单元的形式存储在服务器上,供用户调用。...
  • 数据库存储过程

    万次阅读 多人点赞 2017-12-02 19:07:18
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...

    转载自:https://www.cnblogs.com/mark-chan/p/5384139.html

    存储过程简介

    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

    存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。

    存储过程的优点:

    (1).增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

    (2).标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。

    (3).较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

    (4).减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。

    (5).作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

    MySQL的存储过程

    存储过程是数据库的一个重要的功能,MySQL 5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

    MySQL存储过程的创建

    语法

    CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

    DELIMITER //
    CREATE PROCEDURE myproc(OUT s int)
    BEGIN
    SELECT COUNT(*) INTO s FROM students;
    END
    //
    DELIMITER ;
    分隔符

    MySQL默认以";"为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER //”声明当前段分隔符,让编译器把两个"//"之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER ;”的意为把分隔符还原。

    参数

    存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:

    • IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
    • OUT:该值可在存储过程内部被改变,并可返回
    • INOUT:调用时指定,并且可被改变和返回

      过程体

      过程体的开始与结束使用BEGIN与END进行标识。

      IN参数例子

      DELIMITER //
      CREATE PROCEDURE in_param(IN p_in int)
      BEGIN
      SELECT p_in;
      SET p_in=2;
      SELECT p_in;
      END;
      //
      DELIMITER ;
      #调用
      SET @p_in=1;
      CALL in_param(@p_in);
      SELECT @p_in;
      执行结果:
      image
      image
      image
      以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值
       
      OUT参数例子
      #存储过程OUT参数
      DELIMITER //
      CREATE PROCEDURE out_param(OUT p_out int)
      BEGIN
      SELECT p_out;
      SET p_out=2;
      SELECT p_out;
      END;
      //
      DELIMITER ;
      #调用
      SET @p_out=1;
      CALL out_param(@p_out);
      SELECT @p_out;
      执行结果:
      image
      image
      image
       
      INOUT参数例子
      #存储过程INOUT参数
      DELIMITER //
      CREATE PROCEDURE inout_param(INOUT p_inout int)
      BEGIN
      SELECT p_inout;
      SET p_inout=2;
      SELECT p_inout;
      END;
      //
      DELIMITER ;
      #调用
      SET @p_inout=1;
      CALL inout_param(@p_inout) ;
      SELECT @p_inout;
      执行结果:
      image
      image
      image

       

      变量

      语法:DECLARE 变量名1[,变量名2...] 数据类型 [默认值];

      数据类型为MySQL的数据类型:

      数值类型

      image

      日期和时间类型

      image

      字符串类型

      image

      变量赋值

      语法:SET 变量名 = 变量值 [,变量名= 变量值 ...]

      用户变量

      用户变量一般以@开头

      注意:滥用用户变量会导致程序难以理解及管理

      #在MySQL客户端使用用户变量
      SELECT 'Hello World' into @x;
      SELECT @x;

      SET @y='Goodbye Cruel World';
      SELECT @y;
      SET @z=1+2+3;
      SELECT @z;
      执行结果:
      image
      image
      image
       

      #在存储过程中使用用户变量
      CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,' World');
      SET @greeting='Hello';
      CALL GreetWorld();

      执行结果:

      image

       

      #在存储过程间传递全局范围的用户变量

      CREATE PROCEDURE p1() SET @last_proc='p1';
      CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
      CALL p1();
      CALL p2();

      执行结果:

      image

       

      注释

      MySQL存储过程可使用两种风格的注释:

    • 双杠:--,该风格一般用于单行注释
    • C风格: 一般用于多行注释

      MySQL存储过程的调用

      用call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。

      MySQL存储过程的查询

      #查询存储过程
      SELECT name FROM mysql.proc WHERE db='数据库名';
      SELECT routine_name FROM information_schema.routines WHERE routine_schema='数据库名';
      SHOW PROCEDURE STATUS WHERE db='数据库名';

      #查看存储过程详细信息
      SHOW CREATE PROCEDURE 数据库.存储过程名;
       

      MySQL存储过程的修改

      ALTER PROCEDURE 更改用CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。

      ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
      characteristic:
      { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
      | COMMENT 'string'
    • sp_name参数表示存储过程或函数的名称;
    • characteristic参数指定存储函数的特性。
    • CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;
    • NO SQL表示子程序中不包含SQL语句;
    • READS SQL DATA表示子程序中包含读数据的语句;
    • MODIFIES SQL DATA表示子程序中包含写数据的语句。
    • SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行,DEFINER表示只有定义者自己才能够执行;INVOKER表示调用者可以执行。
    • COMMENT 'string'是注释信息。

      实例:

      #将读写权限改为MODIFIES SQL DATA,并指明调用者可以执行。
      ALTER PROCEDURE num_from_employee
      MODIFIES SQL DATA
      SQL SECURITY INVOKER ;
      #将读写权限改为READS SQL DATA,并加上注释信息'FIND NAME'。
      ALTER PROCEDURE name_from_employee
      READS SQL DATA
      COMMENT 'FIND NAME' ;
       

      MySQL存储过程的删除

      DROP PROCEDURE [过程1[,过程2…]]

      从MySQL的表格中删除一个或多个存储过程。

       

      MySQL存储过程的控制语句

      变量作用域

      内部变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失,不再可见了,在存储
      过程外再也找不到这个内部变量,但是可以通过out参数或者将其值指派给会话变量来保存其值。

      #变量作用域
      DELIMITER //
      CREATE PROCEDURE proc()
      BEGIN
      DECLARE x1 VARCHAR(5) DEFAULT 'outer';
      BEGIN
      DECLARE x1 VARCHAR(5) DEFAULT 'inner';
      SELECT x1;
      END;
      SELECT x1;
      END;
      //
      DELIMITER ;
      #调用
      CALL proc();
      执行结果:
      image
      image
       

      条件语句

      IF-THEN-ELSE语句

      #条件语句IF-THEN-ELSE
      DROP PROCEDURE IF EXISTS proc3;
      DELIMITER //
      CREATE PROCEDURE proc3(IN parameter int)
      BEGIN
      DECLARE var int;
      SET var=parameter+1;
      IF var=0 THEN
      INSERT INTO t VALUES (17);
      END IF ;
      IF parameter=0 THEN
      UPDATE t SET s1=s1+1;
      ELSE
      UPDATE t SET s1=s1+2;
      END IF ;
      END ;
      //
      DELIMITER ;

      CASE-WHEN-THEN-ELSE语句

      #CASE-WHEN-THEN-ELSE语句
      DELIMITER //
      CREATE PROCEDURE proc4 (IN parameter INT)
      BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
      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 ;
      //
      DELIMITER ;
       
      循环语句
      WHILE-DO…END-WHILE
      DELIMITER //
      CREATE PROCEDURE proc5()
      BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
      INSERT INTO t VALUES (var);
      SET var=var+1;
      END WHILE ;
      END;
      //
      DELIMITER ;
       
      REPEAT...END REPEAT
      此语句的特点是执行操作后检查结果
      DELIMITER //
      CREATE PROCEDURE proc6 ()
      BEGIN
      DECLARE v INT;
      SET v=0;
      REPEAT
      INSERT INTO t VALUES(v);
      SET v=v+1;
      UNTIL v>=5
      END REPEAT;
      END;
      //
      DELIMITER ;
       
      LOOP...END LOOP
      DELIMITER //
      CREATE PROCEDURE proc7 ()
      BEGIN
      DECLARE v INT;
      SET v=0;
      LOOP_LABLE:LOOP
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v >=5 THEN
      LEAVE LOOP_LABLE;
      END IF;
      END LOOP;
      END;
      //
      DELIMITER ;
       
      LABLES标号

      标号可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。

      ITERATE迭代

      通过引用复合语句的标号,来从新开始复合语句

      #ITERATE
      DELIMITER //
      CREATE PROCEDURE proc8()
      BEGIN
      DECLARE v INT;
      SET v=0;
      LOOP_LABLE:LOOP
      IF v=3 THEN
      SET v=v+1;
      ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
      LEAVE LOOP_LABLE;
      END IF;
      END LOOP;
      END;
      //
      DELIMITER ;
       
      MySQL存储过程的基本函数
      字符串类

      CHARSET(str) //返回字串字符集
      CONCAT (string2 [,... ]) //连接字串
      INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
      LCASE (string2 ) //转换成小写
      LEFT (string2 ,length ) //从string2中的左边起取length个字符
      LENGTH (string ) //string长度
      LOAD_FILE (file_name ) //从文件读取内容
      LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
      LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
      LTRIM (string2 ) //去除前端空格
      REPEAT (string2 ,count ) //重复count次
      REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
      RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
      RTRIM (string2 ) //去除后端空格
      STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
      SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
      注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1

      SELECT SUBSTRING('abcd',0,2);
      结果:
      image
       
      SELECT SUBSTRING('abcd',1,2);
      结果:
      image
       

      TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
      UCASE (string2 ) //转换成大写
      RIGHT(string2,length) //取string2最后length个字符
      SPACE(count) //生成count个空格

      数学类

      ABS (number2 ) //绝对值
      BIN (decimal_number ) //十进制转二进制
      CEILING (number2 ) //向上取整
      CONV(number2,from_base,to_base) //进制转换
      FLOOR (number2 ) //向下取整
      FORMAT (number,decimal_places ) //保留小数位数
      HEX (DecimalNumber ) //转十六进制
      注:HEX()中可传入字符串,则返回其ASC-11码,如HEX('DEF')返回4142143
      也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19
      LEAST (number , number2 [,..]) //求最小值
      MOD (numerator ,denominator ) //求余
      POWER (number ,power ) //求指数
      RAND([seed]) //随机数
      ROUND (number [,decimals ]) //四舍五入,decimals为小数位数] 注:返回类型并非均为整数,如:

      #默认变为整型值
      SELECT ROUND(1.23);
      image
      SELECT ROUND(1.56);
      image
      #设定小数位数,返回浮点型数据
      SELECT ROUND(1.567,2);
      image
       
      SIGN (number2 ) // 正数返回1,负数返回-1
       
      日期时间类

      ADDTIME (date2 ,time_interval ) //将time_interval加到date2
      CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
      CURRENT_DATE ( ) //当前日期
      CURRENT_TIME ( ) //当前时间
      CURRENT_TIMESTAMP ( ) //当前时间戳
      DATE (datetime ) //返回datetime的日期部分
      DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
      DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
      DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
      DATEDIFF (date1 ,date2 ) //两个日期差
      DAY (date ) //返回日期的天
      DAYNAME (date ) //英文星期
      DAYOFWEEK (date ) //星期(1-7) ,1为星期天
      DAYOFYEAR (date ) //一年中的第几天
      EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
      MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
      MAKETIME (hour ,minute ,second ) //生成时间串
      MONTHNAME (date ) //英文月份名
      NOW ( ) //当前时间
      SEC_TO_TIME (seconds ) //秒数转成时间
      STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
      TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
      TIME_TO_SEC (time ) //时间转秒数]
      WEEK (date_time [,start_of_week ]) //第几周
      YEAR (datetime ) //年份
      DAYOFMONTH(datetime) //月的第几天
      HOUR(datetime) //小时
      LAST_DAY(date) //date的月的最后日期
      MICROSECOND(datetime) //微秒
      MONTH(datetime) //月
      MINUTE(datetime) //分返回符号,正负或0
      SQRT(number2) //开平方

    展开全文
  • 数据库事务&存储过程

    千次阅读 2019-02-28 15:58:24
    一、【事务概念】 事务(Transaction)是指构成单一逻辑...2,同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。 二、【事务举例】 用一个常用的“A 账户向 B 账户汇钱”的例子来说...

    一、【事务概念】

    事务(Transaction)是指构成单一逻辑工作单元的操作集合,要么完整地执行,要么完全不执行。

    1,如果事务中有的操作没有成功完成,则事务中的所有操作都需要被回滚,回到事务执行前的状态(要么全执行,要么全都不执行);

    2,同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。

    二、【事务举例】

    用一个常用的“A 账户向 B 账户汇钱”的例子来说明如何通过数据库事务保证数据的准确性和完整性。熟悉关系型数据库事务的都知道从账户 A 到账户 B 需要 6 个操作,分别为:

    1.从 A 账户中把余额读出来(500)

    2. 对 A 账户做减法操作(500 - 100)

    3. 把结果写回 A 账户中(400)

    4. 从 B 账户中把余额读出来(500)

    5. 对 B 账户做加法操作(500 + 100)

    6. 把结果写回 B 账户中(600)

    三、【事务特性】

    并非任意的对数据库的操作序列都是数据库事务,事务应该具有 4 个属性,分别为:原子性、一致性、隔离性和持久性。这四个属性通常称为ACID特性。

    1.原子性(Atomicity):一个事务对数据库的所有操作,是一个不可分割的工作单元,这些操作要么全部被执行,要么都不执行;

    保证 1-6 所有过程要么都执行,要么都不执行。

    一旦在执行某一步骤的过程中发生问题,就需要执行回滚操作。

    假如执行到第 5 步的时候,B 账户突然不可用(比如被注销),那么之前的所有操作都应该回滚到执行事务之前的状态。

    2.一致性(Consistency):事务应确保数据库的状态从一个一致状态转为另一个一致状态,一致状态的含义是数据库中的数据应满足完整性约束;

    在转账之前,A 和 B 的账户中共有 500 + 500 = 1000 元钱。

    在转账之后,A 和 B 的账户中也应该共有 400 + 600 = 1000 元钱。

    也就是说,数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。

    注:一致性与原子性是密切相关的,原子性的破坏可能导致数据库的不一致,数据的一致性问题并不都和原子性有关。

    比如上面的例子,在第5 步的时候,对 B 账户做加法时只加了 50 元。那么,该过程可以符合原子性,但是数据的一致性就出现了问题。因此,事务的原子性与一致性缺一不可。

    3.隔离性(Isolation):多个事务并发执行时,一个事务的执行不应影响其他事务的执行;

    在 A 向 B 转账的整个过程中,只要事务还没有提交(commit),查询 A 账户和 B 账户的时候,两个账户里面的钱的数量都不会有变化。

    如果在 A 给 B 转账的同时,有另外一个事务执行了 C 给 B 转账的操作,那么当两个事务都结束的时候,B 账户里面的钱应该是 A 转给 B 的钱加上 C 转给 B 的钱再加上自己原有的钱。

    4.持久性(Durability):一个事务一旦提交,它对数据库的修改应该永久保存在数据库中。

    例如我们在数据库,用update语句更新某条记录时,会默认开启一个数据库事务,当我们执行(F8)这条语句后,再次查询这条记录,已经被更新,但没有提交事务,事务并没有对数据库产生影响。此时如果关闭查询窗口(出现故障),并未对数据库记录产生影响,只有将事务提交之后,这条记录才会真正并永久的更新。

    四、【事务并发】

    事务并发:一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库,数据库中的相同数据可能同时被多个事务访问,如果没有采取必要的隔离措施,就会导致各种并发问题,破坏数据的完整性。

    并发问题归结:数据问题(脏读、幻读、不可重复读)、数据更新问题(更新丢失)

     

     

    一、【存储过程的概念】

    ① 存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中。

    ② 存储过程是数据库中的一个重要对象,用户通过指定存储过程的名字并给出参数(可以有参数,也可以没有)来执行它。

    ③ 存储过程是由 流控制 和 SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中。

    ④ 存储过程 可由应用程序通过一个调用来执行,而且允许用户声明变量。

    ⑤ 同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。

    二、【存储过程的优点】

    ① 存储过程的使用大大增强了SQL语言的功能和灵活性。

    存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

    ② 可保证数据的安全性和完整性。

    通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。

    通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。(就像事务的原子性:要么事务内的所有SQL语句全部执行成功,要么全部不成功)

    ③ 在运行存储过程前,数据库已对其进行了语法和句法分析,并给出了优化执行方案。

    这种已经编译好的过程可极大地改善SQL语句的性能。

    由于执行SQL语句的大部分工作已经完成(因为已经提前经过编译),所以存储过程能以极快的速度执行。

    ④ 可以降低网络的通信量。

    客户端调用存储过程只需要传存储过程名和相关参数即可,与传输SQL语句相比自然数据量少了很多(在远程访问时体现出来)。

    ⑤ 存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

    ⑥ 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

    比如每一步对数据库的操作用一个事务来完成,把这些事务全都放在一个存储过程中。

    ⑦ 存储过程可以重复使用,可减少数据库开发人员的工作量。

    ⑧ 安全性高,可设定只有某些用户才具有对指定存储过程的使用权

    三、【存储过程缺点】

    ① 调试麻烦:但是用 PL/SQL Developer 调试很方便!弥补这个缺点。

    ② 移植问题:数据库端代码当然是与数据库相关的。但是如果是做工程型项目,基本不存在移植问题。

    ③ 重新编译问题:因为后端代码是运行前编译的,如果带有引用关系的对象发生改变时,受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。

    比如A存储过程调用B存储过程,使用B的返回值作为参数,如果B的参数或返回值发生改变时,会对调用她的A产生影响,此时存储过程就要重新编译,设置成运行时刻自动编译。

    ④ 维护比较困难:如果在一个程序系统中大量的使用存储过程,到程序交付使用的时候随着用户需求的增加会导致数据结构的变化,接着就是系统的相关问题了,最后如果用户想维护该系统可以说是很难很难、而且代价是空前的,维护起来更麻烦。

    四、【存储过程的种类】

    ① 系统存储过程:一般以sp_开头,用来进行系统的各项设定,获取配置信息,相关管理工作。

    ② 本地存储过程:用户创建的存储过程是由用户创建并完成某一特定功能的存储过程,事实上一般所说的存储过程就是指本地存储过程。

    ③ 临时存储过程:分为两种存储过程:

    一是本地临时存储过程,以井字号(#)作为其名称的第一个字符,则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程,且只有创建它的用户才能执行它;

    二是全局临时存储过程,以两个井字号(##)号开始,则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程,全局临时存储过程一旦创建,以后连接到服务器的任意用户都可以执行它,而且不需要特定的权限。

    ④ 远程存储过程:在SQL Server2005中,远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程,通常可以使用分布式查询和EXECUTE命令(在SQL*Plus命令行窗口中)执行一个远程存储过程。

    ⑤ 扩展存储过程:扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程,而且扩展存储过程的名称通常以xp_开头。

    五、【存储过程的特性】

    1.存储过程与函数的区别

    ①返回值:函数只能返回一个变量,而存储过程可以返回多个。对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象

     ②存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。 

    ③存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强,比较单一。

    2.存储过程与事务的区别

    ①存储位置:事务在程序中被调用,保存在调用以及实现它的代码中,存储过程可以在数据库客户端直接被调用,经编译后存储在数据库中。

    ②运行方式:事务在每次被调用的时候执行其中的SQL语句,存储过程预先经过编译,并不是每次被调用时都会执行一遍其中的SQL语句。

    ③事务有严格的一致性和原子性,使用的安全性高,存储过程则没有这些特性,在进行一些复杂的操作时,为了保证操作的准确性,可以在存储过程中调用事务,然后判断事务的执行结果是否成功来确保操作的准确性。

    3.触发器

    ①概念及作用

    触发器是一种特殊类型的存储过程,它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的,而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如Update、 Insert、 Delete 这些操作时,SQL Server就会自动执行触发器所定义的SQL 语句,从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

    触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外,触发器还有其它许多不同的功能:

    (1) 强化约束(Enforce restriction)

    触发器能够实现比CHECK 语句更为复杂的约束。

    (2) 跟踪变化(Auditing changes)

    触发器可以侦测数据库内的操作,从而不允许数据库中未经许可的指定更新和变化。

    (3) 级联运行(Cascaded operation)。

    触发器可以侦测数据库内的操作,并自动地级联影响整个数据库的各项内容。例如,某个表上的触发器中包含有对另外一个表的数据操作(如删除,更新,插入)而该操作又导致该表上触发器被触发。

    (4) 存储过程的调用(Stored procedure invocation)。

    为了响应数据库更新,触发器可以调用一个或多个存储过程,甚至可以通过外部过程的调用而在DBMS(数据库管理系统)本身之外进行操作。

    由此可见,触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如,触发器能够找出某一表在数据修改前后状态发生的差异,并根据这种差异执行一定的处理。此外一个表的同一类(Insert、 Update、Delete)的多个触发器能够对同一种数据操作采取多种不同的处理。

    总体而言,触发器性能通常比较低。当运行触发器时,系统处理的大部分时间花费在参照其它表的这一处理上,因为这些表既不在内存中也不在数据库设备上,而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。

     

     

     

    展开全文
  • if(exists(select * from sys.objects where name=’proc_location_Paging’)) –如果这个proc_location_paging存储过程存在则删除  drop proc proc_location_Paging  go    create proc proc_location_Paging ...
  • MySQL数据库-存储过程详解

    万次阅读 多人点赞 2017-12-14 15:36:51
    在我看来, 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表,更新数据, 删除等等。 MySQL基础教程之存储过程 存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将...

    存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件,虽然它们的作用不仅限于批处理。在我看来, 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表,更新数据, 删除等等。

    MySQL基础教程之存储过程

    存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批件,虽然它们的作用不仅限于批处理。在我看来, 存储过程就是有业务逻辑和流程的集合, 可以在存储过程中创建表,更新数据, 删除等等。


    为什么要使用存储过程

    1. 通过把处理封装在容易使用的单元中,简化复杂的操作(正如前面例子所述)。
    2. 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能性就越大。防止错误保证了数据的一致性。
    3. 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

    一个简单的存储过程

      
    1. create   procedure  porcedureName () 
    2. begin 
    3.     select name from user
    4. end 

    存储过程用create procedure 创建, 业务逻辑和sql写在begin和end之间。mysql中可用call porcedureName ();来调用过程。

      
    1. -- 调用过程 
    2. call porcedureName ();  

    该存储过程没有参数, 只是在调用的时候查询了用户表的用户名而已, 调用结果如下

    name
    admin
    admin1
    admin2
    admin3

    删除存储过程

      
    1. DROP PROCEDURE IF EXISTS porcedureName; -- 没有括号() 

    在这里,我们还要了解三个特别的字段in,out以及inout的区别,那么我们可以从下面的例子去体会,然后再看详细的使用方式:

    1.参数in的使用(代表输入,意思说你的参数要传到存过过程的过程里面去)
    //为了避免存储过程中分号(";")结束语句,我们使用分隔符告诉mysql解释器,该段命令是否已经结束了。
    /**
    案例功能:求1-n的和
    */
    delimiter $
    create procedure p1(in n int)
    begin
    declare total int default 0;
    declare num int default 0;
    while num < n do
    set num:=num+1;
    set total:=total+num;
    end while;
    select total;
    end$

    call p1(10)$

    创建并执行完存储过程,运行结果如下:

    2.参数out的使用(代表往外输出)
    //这里还要注意一点的就是我们的输出参数一定要设置相应类型的初始,否则不管你怎么计算得出的结果都为NULL值
    /**
    案例功能:求1-n的和
    */
    create procedure p2(in n int,out total int)
    begin
    declare num int default 0;
    set total:=0;
    while num < n do
    set num:=num+1;
    set total:=total+num;
    end while;
    end$
    注意:对于第一个输入参数我们可以理解,但是第二个输出参数我们到底应该怎么输?
    这里我们需要对第二个参数定义一个变量名(更形象点就是你输入一个输入类型的参数n,由输出参数total往外发射输出我们只需要定义一个变量名来接收这个输出值即可)
    call p2(100,@sum)$//这里的@sum就是我定义用来接收处处total的值
    select @sum$
    创建并执行完存储过程(查询定义的变量值),运行结果如下:

    总结in、out区别:
    in:表示输入一个值,你需要一个值,我给你一个值
    out:你往外输出一个值,你输出的那个值我就拿一个变量来接收你给我输出的那个值

    3.参数inout的使用(既能输入一个值又能传出来一个值)
    /**
    功能:传一个年龄,自动让年龄增长10岁
    */
    create procedure p3(inout age int)
    begin
    set age:=age+10;
    end$
    注意:调用的时候,我这里需要和大家声明一下,inout型的参数值既是输入类型又是输出类型,你给它一个值,值不是变量,不是变量那out的时候它怎么赋给这个值是不是?
    因此我们需要先设置一个变量并初始化这个值,调用的时候直接传这个变量即可。
    set @currentAge=8$
    call p3(@currentAge)$
    select @currentAge$
    创建并执行完存储过程,运行结果如下:



    使用参数的存储过程(备注:decimal(8,2)意思就是总共有8位,小数点后留两位

     
    1. create procedure procedureName( 
    2.     out min decimal(8,2), 
    3.     out avg decimal(8,2), 
    4.     out max decimal(8,2) 
    5. BEGIN 
    6.     select MIN(price) INTO min from order
    7.     select AVG(price) into avg from order
    8.     select MAX(price) into max from order
    9. END 

    此过程接受三个参数, 分别用于获取订单表的最小、平均、最大价格。每个参数必须具有指定的类

    型,这里使用十进制值(decimal(8,2)), 关键字OUT指出相应的参数用来从存储过程传出

    一个值(返回给调用者)

    MySQL支持IN(传递给存储过程)、OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内,如前所见,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)

    为调用此修改过的存储过程,必须指定3个变量名,如下所示:(所有MySQL变量都必须以@开始。)

     
    1. -- 由于过程指定三个参数, 故调用必须要参数匹配 
    2. call procedureName(@min, @avg, @max);  

    该调用并没有任何输出, 只是把调用的结果赋给了调用时传入的变量(@min, @avg, @max)。然后即可调用显示该变量的值。

     
    1. select @min, @avg, @max

    结果如下

    @min @avg @max
    42.00 601.00 2222.00

    使用in参数, 输入一个用户id, 返回该用户所有订单的总价格。

     
    1. create procedure getTotalById ( 
    2.     in userId int
    3.     out total decimal(8,2) 
    4. BEGIN 
    5.     select SUM(r.price) from order r 
    6.     where r.u_id = userId 
    7.     into total; 
    8. END 

    调用存储过程

     
    1. call getTotalById(1, @total); 
    2. select @total;  

    结果将返回该用户所有订单的合计价格。

    复杂一点的过程, 根据用户id获取该用户的所有订单价格, 并动态的选择是否加税。代码设计如下

     
    1. create procedure getTotalByUser2( 
    2.     in userId int
    3.     in flag boolean, -- 是否加税标记 
    4.     out total decimal(8,2) 
    5. begin 
    6.     DECLARE tmptotal DECIMAL(8,2); 
    7.     DECLARE taxrate int DEFAULT 6;-- 默认的加税的利率 
    8.      
    9.     select SUM(r.price) from order r 
    10.     where r.u_id = userId 
    11.     into tmptotal; 
    12.      
    13.     if flag then 
    14.         select tmptotal + (tmptotal/1000*taxrate) into tmptotal; 
    15.     end if; 
    16.      
    17.     select tmptotal into total; 
    18. END 

    该过程传入三个参数, 用户id, 是否加税以及返回的总价格,在过程内部, 定义两个局部变量tmptotal和taxrate,把查询出来的结果赋给临时变量, 在判断是否加税。最后把局部变量的值赋给输出参数。

     
    1. call getTotalByUser2(1, false, @total); -- 不加税 
    2. call getTotalByUser2(1, true, @total);  -- 加税 
    3. select @total; 


    展开全文
  • mysql数据库储存过程

    千次阅读 2019-08-28 20:26:23
    mysql数据库储存过程 mysql数据库的 ‘储存过程’ 其实就是把多个select操作的语句封装起来,再给他们一个名称用来以后的调用,你可以理解为python中的函数,差不多 优点 1 通过吧处理封装在容易使用的单元中,简化...

    mysql数据库储存过程

    mysql数据库的 ‘储存过程’ 其实就是把多个select操作的语句封装起来,再给他们一个名称用来以后的调用,你可以理解为python中的函数,差不多

    优点

    1 通过吧处理封装在容易使用的单元中,简化复杂的操作

    2 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程,则所使用的代码是相同的。还有就是防止错误,需要执行的步骤越多,出错的可能性越大。防止错误保证了数据的一致性。

    3 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码,使用它的人员不会改自己的代码了都。

    4 提高性能,因为使用存储过程比使用单条SQL语句要快

    5 存在一些职能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码

    换句话说3个主要好处简单、安全、高性能

    缺点

    1 一般来说,存储过程的编写要比基本的SQL语句复杂,编写存储过程需要更高的技能,更丰富的经验。

    2 你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建,允许用户使用存储过程,但不允许创建存储过程

    存储过程是非常有用的,应该尽可能的使用它们

    创建储存过程:
    CREATE PROCEDURE 存储过程名()

           一个例子说明:一个返回产品平均价格的存储过程如下代码:
    
           CREATE  PROCEDURE  productpricing()
    
           BEGIN
    
            SELECT Avg(prod_price)  AS priceaverage
    
           FROM products;
    
           END;
    

    //创建存储过程名为productpricing,如果存储过程需要接受参数,可以在()中列举出来。即使没有参数后面仍然要跟()。BEGIN和END语句用来限定存储过程体,过程体本身是个简单的SELECT语句

    在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。

    Mysql命令行客户机的分隔符

    默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ; 字符,则他们最终不会成为存储过程的成分,这会使存储过程中的SQL出现句法错误
    解决方法是临时更改命令实用程序的语句分隔符

            DELIMITER //    //定义新的语句分隔符为//
    
            CREATE PROCEDURE productpricing()
    
            BEGIN
    
            SELECT Avg(prod_price) AS priceaverage
    
            FROM products;
    
            END //
    
            DELIMITER ;    //改回原来的语句分隔符为 ;
    
            除\符号外,任何字符都可以作为语句分隔符
    
        CALL productpricing();  //使用productpricing存储过程
    
        执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数,所以存储过程名后面要有()符号
    

    删除储存过程

    DROP PROCEDURE productpricing ;    
     //删除存储过程后面不需要跟(),只给出存储过程名
    
    为了删除存储过程不存在时删除产生错误,可以判断仅存储过程存在时删除
    
        DROP PROCEDURE IF EXISTS
    

    一般存储过程并不显示结果,而是把结果返回给你指定的变量
    在这里插入图片描述

    CREATE PROCEDURE productpricing(
    
            OUT p1 DECIMAL(8,2),
    
            OUT ph DECIMAL(8,2),
    
            OUT pa DECIMAL(8,2),
    
            )
    
            BEGIN
    
            SELECT Min(prod_price)
    
            INTO p1
    
            FROM products;
    
            SELECT Max(prod_price)
    
            INTO ph
    
            FROM products;
    
            SELECT Avg(prod_price)
    
            INTO pa
    
            FROM products;
    
            END;
    

    此存储过程接受3个参数,p1存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格。每个参数必须指定类型,这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传给一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程中传出、如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内,如前所见,它们是一些列SELECT语句,用来检索值,然后保存到相应的变量(通过INTO关键字)
    调用修改过的存储过程必须指定3个变量名:

        CALL productpricing(@pricelow , @pricehigh , @priceaverage);
    
        这条CALL语句给出3个参数,它们是存储过程将保存结果的3个变量的名字
    
    变量名  所有的MySQL变量都必须以@开始
    
    使用变量
    
        SELECT @priceaverage ;
    
        SELECT @pricelow , @pricehigh , @priceaverage ;   //获得3给变量的值
    

    下面是另一个例子,这次使用IN和OUT参数。ordertotal接受订单号,并返回该订单的合计

            CREATE PROCEDURE ordertotal(
    
           IN onumber INT,
    
           OUT ototal DECIMAL(8,2)
    
            )
    
            BEGIN
    
            SELECT Sum(item_price*quantity)
    
            FROM orderitems
    
            WHERE order_num = onumber
    
            INTO ototal;
    
            END;
    
            //onumber定义为IN,因为订单号时被传入存储过程,ototal定义为OUT,因为要从存储过程中返回合计,SELECT语句使用这两个参数,WHERE子句使用onumber选择正确的行,INTO使用ototal存储计算出来的合计
    
    为了调用这个新的过程,可以使用下列语句:
    
        CALL ordertotal(2005 , @total);   //这样查询其他的订单总计可直接改变订单号即可
    
        SELECT @total;
    

    基本的简单应用这样就是可以了,如果你还要细看的话,可以借鉴一下这个博客:
    https://www.cnblogs.com/l5580/p/5993238.html

    展开全文
  • SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • 您可能感兴趣的文章:Oracle存储过程游标用法分析oracle存储过程常用的技巧(详)asp.net中调用oracle存储过程的方法C#调用Oracle存储过程的方法oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)Oracle存储...
  • 用代码找回被误删的数据库的数据,很方便。直接用用存储过程找回数据库删除的数据
  • c# Dapper高级编程; 添加方法3个,修改方法2个,删除方法2个,查询方法4个(含Join),事务方法3个,存储过程方法4个,含数据库,含sql存储过程
  • 通过存储过程删除Mysql数据库表中的重复记录,只保留一条;
  • MySQL数据库存储过程讲解与实例

    万次阅读 多人点赞 2018-06-03 00:48:17
    SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。...
  • 存储过程是在大型的数据库系统中,为了完成特定功能的SQL语句集合,一次编译好,一直都可以使用,用户通过存储过程的名字,并且给定参数,然后就可以调用执行了。 这个存储过程是在MySQL中存放和执行的,也就是说,...
  • 人们利用SQL Server数据库存储数据的最终目的是开发各种应用软件来对这些数据进行处理和管理,能够对SQL Server数据库执行操作的只有T-SQL语句,所以各种前台开发工具,例如VB、C#、Java等都是通过调用T-SQL语句来...
  • 这个存储过程需要配置在作业中,并开启xp_cmdshell,有调用实例。
  • 数据库存储过程及其调用

    千次阅读 2017-12-18 17:37:05
    内容: - 存储过程格式 - 定时调用存储过程
  • 获取指定数据中删除所有表的命令: SELECT CONCAT("DROP TABLE IF EXISTS ", TABLE_NAME, ";") FROM information_schema.TABLES WHERE table_schema = "gamedb"; 输出为: +--------------------------------------...
  • SQL Server数据库基础知识——数据库存储过程怎么写

    千次阅读 多人点赞 2020-05-22 04:45:56
    如何创建、修改、删除、调用存储过程存储过程的优缺点 存储过程和触发器的区别? 存储过程和函数的区别? 存储过程的使用 1. 什么是存储过程存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只...
  • 前言: 有时候,一个数据库有多个帐号,包括数据库管理员,开发人员,运维支撑人员等,可能有很多帐号都有比较大的权限,例如DDL操作权限(创建,修改,删除存储过程,创建,修改,删除表等),账户多了,管理起来就...
  • 数据库实验七 存储过程实验 掌握数据库存储过程的设计和使用方法。 存储过程的定义,存储过程运行,存储过程更名,存储过程删除
  •  存储过程是一组为了完成某项特定功能的SQL语句集,它可以由声明式SQL语句和过程式SQL语句组成,这组语句经过编译后会存储数据库中,用户只需要通过指定存储过程的名字并给定参数,即可随时调用并执行它,而不必...
  • 数据库中的存储过程

    2021-11-20 21:47:38
    之前介绍了 数据库基本常识的简介及mysql安装 结构化查询语言sql 数据库表介绍 数据库的数据类型 数据操作语言DML——数据的增删改查 数据库对象操作语言DDL...接下来介绍最后一个存储过程。当然,数据库中的东西还
  • 一般不需要这一步,直接配置即可)本文给出一个使用实例,结合存储过程和Job来实现定时从数据库发送邮件:1、创建存储过程,在新建的存储过程中调用sp_send_dbmail存储过程(注:代码中的\’只是添加代码时自动添加...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 499,164
精华内容 199,665
关键字:

存储过程删除数据库

友情链接: PerlTreasury.rar