精华内容
下载资源
问答
  • 如果在存储过程中没有指定参数的默认值,并且调用程序也没有在执行存储过程时为该参数提供,那么会返回系统错误,因此指定默认值是必要的。 如果不能为参数指定合适的默认值,则可以指定NULL 作为参数的默认值,...

    下面三句话是Microsoft相关网站的专业解释。

    通过为可选参数指定默认值,可创建带有可选参数的存储过程。执行该存储过程时,如果未指定其他值,则使用默认值。

    如果在存储过程中没有指定参数的默认值,并且调用程序也没有在执行存储过程时为该参数提供值,那么会返回系统错误,因此指定默认值是必要的。

    如果不能为参数指定合适的默认值,则可以指定NULL 作为参数的默认值,并在未提供参数值而执行存储过程的情况下,使存储过程返回一条自定义消息。

    create PROCEDURE [dbo].[US_Patient_Practice]

        @FAgentIDint,

        @FSortvarchar(50)='FFirstSite',

        @FDescvarchar(4)='ASC',

        @FBinDatevarchar(50),--开始时间

        @FEndDatevarchar(50)--结束时间

     

    as

    declare @SSQL varchar(4000)

    --US_Patient_Practice14,'FFirstSite','asc'

    如上述存储过程中开始时间和结束时间是没有默认值的,下面执行存储过程语句是没有给开始时间参数中和结束时间赋值的,此时发生如下错误

    消息 201,级别 16,状态 4,过程US_Patient_Practice,第 18 行

    过程或函数'US_Patient_Practice' 需要参数 '@FBinDate',但未提供该参数。

    在程序中调用该存储过程时,即使我们在页面上未对该参数进行赋值,只要在调用存储过程语句时,给参数赋值,就不会发生错误。

    VSQL = String.Format("US_Patient_SelectPart{0},'{1}','{2}','{3}','{4}'", ddlFAgent.SelectedValue, sortField,sortDirection, tbx_FBegDate.Text, tbx_FEndDate.Text)

    在vb语句中上述语句中tbx_FBegDate.Text和tbx_FEndDate.Text,即使我们在页面中没有对这俩个日期控件选择,此时其实debug下可观察到其值为””即为空字符串。

     

     

    展开全文
  • 函数与存储过程最大的区别就是函数调用返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可IN,OUT,INOUT三个参数前的关键词只适用于存储过程,对函数而言所有的参数默认都是输入参数IN输入参数...

    函数与存储过程最大的区别就是函数调用有返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可

    IN,OUT,INOUT三个参数前的关键词只适用于存储过程,对函数而言所有的参数默认都是输入参数

    IN输入参数用于把数值传入到存储过程中;OUT输出参数将数值传递到调用者,初始值是NULL;INOUT输入输出参数把数据传入到存储过程,在存储过程中修改之后再传递到调用者

    mysql> select * fromstudents;+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | 0 | 1 |

    | 2 | Andy | 0 | 1 |

    | 3 | Bob | 0 | 1 |

    | 4 | Ruth | 1 | 2 |

    | 5 | Mike | 0 | 2 |

    | 6 | John | 0 | 3 |

    | 7 | Cindy | 1 | 3 |

    | 8 | Susan | 1 | 3 |

    +-----+--------+--------+---------+

    8 rows in set (0.00sec)

    mysql> delimiter //mysql> create procedure simpleproc(IN param1 int,OUT param2 INT)->BEGIN-> SELECT COUNT(*) INTO param2 FROM students where sid >param1;-> END //Query OK, 0 rows affected (0.42sec)

    mysql>delimiter ;

    mysql> call simpleproc(1,@a);

    Query OK,1 row affected (0.00sec)

    mysql> select@a;+------+

    | @a |

    +------+

    | 7 |

    +------+

    1 row in set (0.00sec)

    mysql> select count(*) from students where sid>1;+----------+

    | count(*) |

    +----------+

    | 7 |

    +----------+

    1 row in set (0.00sec)

    mysql> call simpleproc(3,@a);

    Query OK,1 row affected (0.00sec)

    mysql> select@a;+------+

    | @a |

    +------+

    | 5 |

    +------+

    1 row in set (0.00sec)

    mysql> delimiter //mysql>create procedure simpleproc2()->BEGIN-> SELECT COUNT(*) FROM students where sid > 1;-> END //Query OK, 0 rows affected (0.07sec)

    mysql>delimiter ;

    mysql>call simpleproc2();+----------+

    | COUNT(*) |

    +----------+

    | 7 |

    +----------+

    1 row in set (0.00sec)

    Query OK,0 rows affected (0.00sec)

    mysql>show create procedure simpleproc2;+-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |

    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()

    BEGIN

    SELECT COUNT(*) FROM students where sid > 1;

    END| utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    1 row in set (0.00 sec)

    Delimiter命令是改变语句的结束符,MySQL默认的结束符为;号,由于procedure和function中的;号并不代表创建的结束,所以要替换成另外的结束符以便表示创建的结束

    mysql> delimiter //mysql> CREATE FUNCTION hello (s CHAR(20))-> RETURNS CHAR(50)-> RETURN CONCAT('Hello',s,'!');-> //ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might*want to use the less safe log_bin_trust_function_creators variable)

    mysql>delimiter ;

    mysql> show variables like '%trust%';+---------------------------------+-------+

    | Variable_name | Value |

    +---------------------------------+-------+

    | log_bin_trust_function_creators | OFF |

    +---------------------------------+-------+

    1 row in set (0.01sec)

    mysql> set global log_bin_trust_function_creators=on;

    Query OK,0 rows affected (0.00sec)

    mysql> delimiter //mysql> CREATE FUNCTION hello (s CHAR(20))-> RETURNS CHAR(50)-> RETURN CONCAT('Hello,',s,'!');-> //Query OK, 0 rows affected (0.06sec)

    mysql>delimiter ;

    mysql> select hello('a');+------------+

    | hello('a') |

    +------------+

    | Hello, a! |

    +------------+

    1 row in set (0.00sec)

    mysql> select * fromteacher;+----+-----------+---------+

    | id | name | dept_id |

    +----+-----------+---------+

    | 1 | Zhang san | 1 |

    | 2 | Li si | 1 |

    | 3 | Wang wu | 2 |

    | 4 | Liu liu | 3 |

    | 5 | Ding qi | 3 |

    +----+-----------+---------+

    5 rows in set (0.03sec)

    mysql> select hello(name) fromteacher;+-------------------+

    | hello(name) |

    +-------------------+

    | Hello, Zhang san! |

    | Hello, Li si! |

    | Hello, Wang wu! |

    | Hello, Liu liu! |

    | Hello, Ding qi! |

    +-------------------+

    5 rows in set (0.00sec)

    mysql>start transaction;

    Query OK,0 rows affected (0.00sec)

    mysql> update teacher set name=hello(name);

    Query OK,5 rows affected (0.00sec)

    Rows matched:5 Changed: 5 Warnings: 0mysql> select * fromteacher;+----+-------------------+---------+

    | id | name | dept_id |

    +----+-------------------+---------+

    | 1 | Hello, Zhang san! | 1 |

    | 2 | Hello, Li si! | 1 |

    | 3 | Hello, Wang wu! | 2 |

    | 4 | Hello, Liu liu! | 3 |

    | 5 | Hello, Ding qi! | 3 |

    +----+-------------------+---------+

    5 rows in set (0.00sec)

    mysql>rollback;

    Query OK,0 rows affected (0.01sec)

    mysql> delimiter //mysql>create procedure simpleproc2()-> SELECT COUNT(*) FROM students where sid > 1;-> //Query OK, 0 rows affected (0.05sec)

    mysql> delimiter ;

    通过begin…end将这多个SQL语句包含在一起,Begin…end语句通常出现在存储过程、函数和触发器中,其中可以包含一个或多个语句,每个语句用;号隔开

    mysql> delimiter //mysql>create procedure simpleproc2()-> SELECT COUNT(*) FROM students where sid > 1;-> SELECT COUNT(*) FROM students where sid > 2;-> //Query OK, 0 rows affected (0.09sec)+----------+

    | COUNT(*) |

    +----------+

    | 6 |

    +----------+

    1 row in set (0.09sec)

    mysql>delimiter ;

    mysql>call simpleproc2();+----------+

    | COUNT(*) |

    +----------+

    | 7 |

    +----------+

    1 row in set (0.00sec)

    Query OK,0 rows affected (0.00sec)

    mysql>show create procedure simpleproc2;+-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |

    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()

    SELECT COUNT(*) FROM students where sid > 1; | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    1 row in set (0.00sec)

    mysql>drop procedure simpleproc2;

    Query OK,0 rows affected (0.13sec)

    mysql> delimiter //mysql>create procedure simpleproc2()->BEGIN-> SELECT COUNT(*) FROM students where sid > 1;-> SELECT COUNT(*) FROM students where sid > 2;->END-> //Query OK, 0 rows affected (0.06sec)

    mysql>delimiter ;

    mysql>call simpleproc2();+----------+

    | COUNT(*) |

    +----------+

    | 7 |

    +----------+

    1 row in set (0.00sec)+----------+

    | COUNT(*) |

    +----------+

    | 6 |

    +----------+

    1 row in set (0.00sec)

    Query OK,0 rows affected (0.00sec)

    mysql>show create procedure simpleproc2;+-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |

    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()

    BEGIN

    SELECT COUNT(*) FROM students where sid > 1;

    SELECT COUNT(*) FROM students where sid > 2;

    END| utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

    1 row in set (0.00sec)

    mysql>use information_schema ;

    Reading table informationforcompletion of table and column names

    You can turn offthis feature to get a quicker startup with -A

    Database changed

    mysql> select * from routines where routine_schema='course'

    ->;+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

    | SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED | LAST_ALTERED | SQL_MODE | ROUTINE_COMMENT | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |

    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

    | hello | def | course | hello | FUNCTION | char | 50 | 200 | NULL | NULL | NULL | utf8mb4 | utf8mb4_0900_ai_ci | char(50) | SQL | RETURN CONCAT('Hello,',s,'!') | NULL | SQL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2019-04-01 10:24:44 | 2019-04-01 10:24:44 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

    | simpleproc | def | course | simpleproc | PROCEDURE | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SQL |BEGIN

    SELECT COUNT(*) INTO param2 FROM students where sid >param1;

    END| NULL | SQL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2019-04-01 10:05:28 | 2019-04-01 10:05:28 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

    | simpleproc2 | def | course | simpleproc2 | PROCEDURE | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | SQL |BEGIN

    SELECT COUNT(*) FROM students where sid > 1;

    SELECT COUNT(*) FROM students where sid > 2;

    END| NULL | SQL | SQL | NO | CONTAINS SQL | NULL | DEFINER | 2019-04-01 10:38:17 | 2019-04-01 10:38:17 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |

    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

    3 rows in set (0.00sec)

    mysql> select routine_name from routines where routine_schema='course'

    ->;+--------------+

    | ROUTINE_NAME |

    +--------------+

    | hello |

    | simpleproc |

    | simpleproc2 |

    +--------------+

    3 rows in set (0.00sec)

    mysql> select routine_name,routine_type from routines where routine_schema='course';+--------------+--------------+

    | ROUTINE_NAME | ROUTINE_TYPE |

    +--------------+--------------+

    | hello | FUNCTION |

    | simpleproc | PROCEDURE |

    | simpleproc2 | PROCEDURE |

    +--------------+--------------+

    3 rows in set (0.00sec)

    mysql>use course;

    Reading table informationforcompletion of table and column names

    You can turn offthis feature to get a quicker startup with -A

    标签label可以加在begin…end语句以及loop, repeat和while语句中通过iterate和leave来控制流程,iterate表示返回指定标签位置,leave表示跳出标签

    Database changed

    mysql> delimiter //mysql> CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)->BEGIN->label1: LOOP-> SET p1 = p1 + 1;-> IF p1 < 10THEN ITERATE label1; END IF;->LEAVE label1;->END LOOP label1;-> set p2=p1;->END;-> //Query OK, 0 rows affected (0.01sec)

    mysql>delimiter ;

    mysql>delimiter ;

    mysql> call doiterate(1,@x);

    Query OK,0 rows affected (0.00sec)

    mysql> select@x;+------+

    | @x |

    +------+

    | 10 |

    +------+

    1 row in set (0.00 sec)

    Drop procedure/function语句用来删除指定名称的存储过程或函数

    mysql> drop procedure simpleproc;

    Query OK, 0 rows affected (0.16 sec)

    Declare语句通常用来声明本地变量、游标、条件或者handler

    Declare语句只允许出现在begin … end语句中而且必须出现在第一行

    Declare的顺序也有要求,通常是先声明本地变量,再是游标,然后是条件和handler

    mysql> delimiter //mysql>create procedure simpleproc(OUT param2 INT)->BEGIN-> declare n int default 10; #必须在第一行-> SELECT COUNT(*) INTO param2 FROM students where sid >n;-> END //Query OK, 0 rows affected (0.03sec)

    mysql>delimiter ;

    mysql>call simpleproc(@b);

    Query OK,1 row affected (0.00sec)

    mysql> select@b;+------+

    | @b |

    +------+

    | 0 |

    +------+

    1 row in set (0.00sec)

    mysql> selectn;

    ERROR1054 (42S22): Unknown column 'n' in 'field list'mysql>desc students;+---------+-------------+------+-----+---------+----------------+

    | Field | Type | Null | Key | Default | Extra |

    +---------+-------------+------+-----+---------+----------------+

    | sid | int(11) | NO | PRI | NULL | auto_increment |

    | sname | varchar(64) | YES | | NULL | |

    | gender | varchar(12) | YES | | NULL | |

    | dept_id | int(11) | YES | MUL | NULL | |

    +---------+-------------+------+-----+---------+----------------+

    4 rows in set (0.31sec)

    mysql> select * fromstudents;+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | 0 | 1 |

    | 2 | Andy | 0 | 1 |

    | 3 | Bob | 0 | 1 |

    | 4 | Ruth | 1 | 2 |

    | 5 | Mike | 0 | 2 |

    | 6 | John | 0 | 3 |

    | 7 | Cindy | 1 | 3 |

    | 8 | Susan | 1 | 3 |

    +-----+--------+--------+---------+

    8 rows in set (0.00sec)

    mysql> delimiter //mysql> CREATE PROCEDURE sp1 (v_sid int)->BEGIN-> DECLARE xname VARCHAR(64) DEFAULT 'bob';->DECLARE xgender INT;->SELECT sname, gender INTO xname, xgender-> FROM students WHERE sid=v_sid;->SELECT xname,xgender;->END;-> //Query OK, 0 rows affected (0.04sec)

    mysql>delimiter ;

    mysql> call sp1(1);+--------+---------+

    | xname | xgender |

    +--------+---------+

    | Andrew | 0 |

    +--------+---------+

    1 row in set (0.00sec)

    Query OK,0 rows affected (0.00sec)

    mysql> call sp1(2);+-------+---------+

    | xname | xgender |

    +-------+---------+

    | Andy | 0 |

    +-------+---------+

    1 row in set (0.00sec)

    Query OK,0 rows affected (0.00sec)

    mysql> select case gender when 0 then 'male' when 1 then 'female' else 'unknown' end fromstudents;+------------------------------------------------------------------------+

    | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |

    +------------------------------------------------------------------------+

    | male |

    | male |

    | male |

    | female |

    | male |

    | male |

    | female |

    | female |

    +------------------------------------------------------------------------+

    8 rows in set (0.00sec)

    mysql> select gender,case gender when 0 then 'male' when 1 then 'female' else 'unknown' end fromstudents;+--------+------------------------------------------------------------------------+

    | gender | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |

    +--------+------------------------------------------------------------------------+

    | 0 | male |

    | 0 | male |

    | 0 | male |

    | 1 | female |

    | 0 | male |

    | 0 | male |

    | 1 | female |

    | 1 | female |

    +--------+------------------------------------------------------------------------+

    8 rows in set (0.00sec)

    mysql> select gender,case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end fromstudents;+--------+-------------------------------------------------------------------------------+

    | gender | case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end |

    +--------+-------------------------------------------------------------------------------+

    | 0 | unknown |

    | 0 | unknown |

    | 0 | unknown |

    | 1 | male |

    | 0 | unknown |

    | 0 | unknown |

    | 1 | male |

    | 1 | male |

    +--------+-------------------------------------------------------------------------------+

    8 rows in set (0.00 sec)

    本地变量可以通过declare语句进行声明

    声明后的变量可以通过select … into var_list进行赋值,或者通过

    set语句赋值,或者通过定义游标并使用fetch … into var_list赋值

    声明的变量作用范围为被声明的begin … end语句块之间

    声明的变量和被引用的数据表中的字段名要区分开来

    第一个语句中case_value与后面各句的when_value依次做相等的对比,如果碰到相等的,则执行对应的后面的statement_list,否则接着对比,

    如果都没有匹配,则执行else后面的statement_list

    第二个语句中当search_condition满足true/1的结果时,则执行对应的statement_list,否则执行else对应的statement_list

    mysql> delimiter //mysql> CREATE PROCEDURE exp_case(v_sid int)->BEGIN-> DECLARE v INT DEFAULT 1;-> select gender into v from students where sid=v_sid;->CASE v-> WHEN 0 THEN update students set gender=1 where sid=v_sid;-> WHEN 1 THEN update students set gender=0 where sid=v_sid;->ELSE-> update students set gender=-1 where sid=v_sid;->END CASE;->END;-> //Query OK, 0 rows affected (0.33sec)

    mysql>delimiter ;

    mysql> select * from students where sid in (1,2);+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | 0 | 1 |

    | 2 | Andy | 0 | 1 |

    +-----+--------+--------+---------+

    2 rows in set (0.00sec)

    mysql> call exp_case(1);

    Query OK,1 row affected (0.03sec)

    mysql> call exp_case(2);

    Query OK,1 row affected (0.04sec)

    mysql> select * fromstudents;+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | 1 | 1 |

    | 2 | Andy | 1 | 1 |

    | 3 | Bob | 0 | 1 |

    | 4 | Ruth | 1 | 2 |

    | 5 | Mike | 0 | 2 |

    | 6 | John | 0 | 3 |

    | 7 | Cindy | 1 | 3 |

    | 8 | Susan | 1 | 3 |

    +-----+--------+--------+---------+

    8 rows in set (0.00sec)

    mysql> call exp_case(1);

    Query OK,1 row affected (0.06sec)

    mysql> call exp_case(2);

    Query OK,1 row affected (0.02sec)

    mysql> select * fromstudents;+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | 0 | 1 |

    | 2 | Andy | 0 | 1 |

    | 3 | Bob | 0 | 1 |

    | 4 | Ruth | 1 | 2 |

    | 5 | Mike | 0 | 2 |

    | 6 | John | 0 | 3 |

    | 7 | Cindy | 1 | 3 |

    | 8 | Susan | 1 | 3 |

    +-----+--------+--------+---------+

    8 rows in set (0.00sec)

    另外的写法

    delimiter//CREATE PROCEDURE exp_case2(v_sid int)

    BEGIN

    DECLARE v INT DEFAULT1;select gender into v from students where sid=v_sid;

    CASE

    WHEN v=0 THEN update students set gender=1 where sid=v_sid;

    WHEN v=1 THEN update students set gender=0 where sid=v_sid;

    ELSE

    update studentsset gender=-1 where sid=v_sid;

    END CASE;

    END;//delimiter ;

    MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句,另外return语句也是函数中的特定流程控制语句

    IF语句在存储过程或函数中表明了基础的条件选择语句IF语句中如果search_condition满足true/1的条件,则执行对应的statement_list,否则再判断elseif中的search_condition是否满足

    true/1的条件,如果都不满足则执行else中的statement_list语句

    mysql> DELIMITER //mysql>CREATE FUNCTION SimpleCompare(n INT, m INT)-> RETURNS VARCHAR(20)->BEGIN-> DECLARE s VARCHAR(20);-> IF n > m THEN SET s = '>';-> ELSEIF n = m THEN SET s = '=';-> ELSE SET s = 'END IF;-> SET s = CONCAT(n, ' ', s, ' ', m);->RETURN s;-> END //Query OK, 0 rows affected (0.07sec)

    mysql>DELIMITER ;

    mysql> select SimpleCompare(1,2);+--------------------+

    | SimpleCompare(1,2) |

    +--------------------+

    | 1 < 2 |

    +--------------------+

    1 row in set (0.00sec)

    mysql> select SimpleCompare(3,2);+--------------------+

    | SimpleCompare(3,2) |

    +--------------------+

    | 3 > 2 |

    +--------------------+

    1 row in set (0.00sec)

    mysql> select SimpleCompare(3,3);+--------------------+

    | SimpleCompare(3,3) |

    +--------------------+

    | 3 = 3 |

    +--------------------+

    1 row in set (0.00sec)

    mysql> DELIMITER //mysql>CREATE FUNCTION VerboseCompare (n INT, m INT)-> RETURNS VARCHAR(50)->BEGIN-> DECLARE s VARCHAR(50);-> IF n = m THEN SET s = 'equals';->ELSE-> IF n > m THEN SET s = 'greater';-> ELSE SET s = 'less';->END IF;-> SET s = CONCAT('is', s, 'than');->END IF;-> SET s = CONCAT(n, ' ', s, ' ', m, '.');->RETURN s;-> END //Query OK, 0 rows affected (0.08sec)

    mysql>DELIMITER ;

    mysql> select VerboseCompare(1,2);+---------------------+

    | VerboseCompare(1,2) |

    +---------------------+

    | 1 is less than 2. |

    +---------------------+

    1 row in set (0.00sec)

    mysql> select VerboseCompare(2,2);+---------------------+

    | VerboseCompare(2,2) |

    +---------------------+

    | 2 equals 2. |

    +---------------------+

    1 row in set (0.00 sec)

    repeat语句是存储过程或函数中表达循环执行的一种方式

    Repeat语句中statement_list一直重复执行直到search_condition条件满足

    Statement_list可以包含一个或多个SQL语句

    mysql> delimiter //mysql>CREATE PROCEDURE dorepeat(p1 INT)->BEGIN-> SET @x = 0;->REPEAT-> SET @x = @x + 1;-> UNTIL @x >p1 END REPEAT;->END-> //Query OK, 0 rows affected (0.04sec)

    mysql>delimiter ;

    mysql> call dorepeat(10);

    Query OK,0 rows affected (0.00sec)

    mysql> select@x;+------+

    | @x |

    +------+

    | 11 |

    +------+

    1 row in set (0.01 sec)

    while语句是存储过程或函数中表达循环执行的一种方式

    当search_condition返回为true时,则循环执行statement_list中的语句,直到search_condition的结果返回为false

    mysql> DELIMITER //mysql>CREATE PROCEDURE dowhile()->BEGIN-> DECLARE v1 INT DEFAULT 5;-> WHILE v1 > 0DO-> update students set gender=-1 where sid=v1;-> SET v1 = v1 - 1;->END WHILE;->END;-> //Query OK, 0 rows affected (0.07sec)

    mysql>DELIMITER ;

    mysql> select * fromstudents;+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | 0 | 1 |

    | 2 | Andy | 0 | 1 |

    | 3 | Bob | 0 | 1 |

    | 4 | Ruth | 1 | 2 |

    | 5 | Mike | 0 | 2 |

    | 6 | John | 0 | 3 |

    | 7 | Cindy | 1 | 3 |

    | 8 | Susan | 1 | 3 |

    +-----+--------+--------+---------+

    8 rows in set (0.01sec)

    mysql>call dowhile();

    Query OK,1 row affected (0.02sec)

    mysql> select * fromstudents;+-----+--------+--------+---------+

    | sid | sname | gender | dept_id |

    +-----+--------+--------+---------+

    | 1 | Andrew | -1 | 1 |

    | 2 | Andy | -1 | 1 |

    | 3 | Bob | -1 | 1 |

    | 4 | Ruth | -1 | 2 |

    | 5 | Mike | -1 | 2 |

    | 6 | John | 0 | 3 |

    | 7 | Cindy | 1 | 3 |

    | 8 | Susan | 1 | 3 |

    +-----+--------+--------+---------+

    8 rows in set (0.00sec)

    在函数中必须要有至少一个return语句,当有多个return语句时则表明函数有多种退出的方式

    mysql> delimiter //mysql>create function doreturn()-> returns int

    ->begin-> select gender into @a from students where sid=1;-> if @a=1 then return 1;-> elseif @a=0 then return 0;-> else return 999;-> end if;->end;-> //Query OK, 0 rows affected (0.06sec)

    mysql>delimiter ;

    mysql> selectdoreturn();+------------+

    | doreturn() |

    +------------+

    | 999 |

    +------------+

    1 row in set (0.00 sec)

    展开全文
  • mysql 存储过程和函数

    2019-04-08 18:57:00
    函数与存储过程最大的区别就是函数调用返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可IN,OUT,INOUT三个参数前的关键词只适用于存储过程,对函数而言所有的参数默认都是输入参数IN输入参数...

    函数与存储过程最大的区别就是函数调用有返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可
    IN,OUT,INOUT三个参数前的关键词只适用于存储过程,对函数而言所有的参数默认都是输入参数
    IN输入参数用于把数值传入到存储过程中;OUT输出参数将数值传递到调用者,初始值是NULL;INOUT输入输出参数把数据传入到存储过程,在存储过程中修改之后再传递到调用者

    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    
    mysql> delimiter //
    mysql> create procedure simpleproc(IN param1 int,OUT param2 INT)
        -> BEGIN
        -> SELECT COUNT(*) INTO param2 FROM students where sid > param1;
        -> END //
    Query OK, 0 rows affected (0.42 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc(1,@a);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @a;
    +------+
    | @a   |
    +------+
    |    7 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from students where sid>1;
    +----------+
    | count(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> call simpleproc(3,@a);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @a;
    +------+
    | @a   |
    +------+
    |    5 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> BEGIN
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> END //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc2();
    +----------+
    | COUNT(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    
    mysql> show create procedure simpleproc2;
    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                                   | character_set_client | collation_connection | Database Collation |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()
    BEGIN
    SELECT COUNT(*) FROM students where sid > 1;
    END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)

    Delimiter命令是改变语句的结束符,MySQL默认的结束符为;号,由于procedure和function中的;号并不代表创建的结束,所以要替换成另外的结束符以便表示创建的结束

    mysql> delimiter //
    mysql> CREATE FUNCTION hello (s CHAR(20))
        -> RETURNS CHAR(50)
        -> RETURN CONCAT('Hello',s,'!');
        -> //
    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
    mysql> delimiter ;
    mysql> show variables like '%trust%';
    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | log_bin_trust_function_creators | OFF   |
    +---------------------------------+-------+
    1 row in set (0.01 sec)
    
    mysql> set global log_bin_trust_function_creators=on;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE FUNCTION hello (s CHAR(20))
        -> RETURNS CHAR(50)
        -> RETURN CONCAT('Hello, ',s,'!');
        -> //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> select hello('a');
    +------------+
    | hello('a') |
    +------------+
    | Hello, a!  |
    +------------+
    1 row in set (0.00 sec)
    
    mysql> select * from teacher;
    +----+-----------+---------+
    | id | name      | dept_id |
    +----+-----------+---------+
    |  1 | Zhang san |       1 |
    |  2 | Li si     |       1 |
    |  3 | Wang wu   |       2 |
    |  4 | Liu liu   |       3 |
    |  5 | Ding qi   |       3 |
    +----+-----------+---------+
    5 rows in set (0.03 sec)
    
    mysql> select hello(name) from teacher;
    +-------------------+
    | hello(name)       |
    +-------------------+
    | Hello, Zhang san! |
    | Hello, Li si!     |
    | Hello, Wang wu!   |
    | Hello, Liu liu!   |
    | Hello, Ding qi!   |
    +-------------------+
    5 rows in set (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update teacher set name=hello(name);
    Query OK, 5 rows affected (0.00 sec)
    Rows matched: 5  Changed: 5  Warnings: 0
    
    mysql> select * from teacher;
    +----+-------------------+---------+
    | id | name              | dept_id |
    +----+-------------------+---------+
    |  1 | Hello, Zhang san! |       1 |
    |  2 | Hello, Li si!     |       1 |
    |  3 | Hello, Wang wu!   |       2 |
    |  4 | Hello, Liu liu!   |       3 |
    |  5 | Hello, Ding qi!   |       3 |
    +----+-------------------+---------+
    5 rows in set (0.00 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> //
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> delimiter ;

    通过begin…end将这多个SQL语句包含在一起,Begin…end语句通常出现在存储过程、函数和触发器中,其中可以包含一个或多个语句,每个语句用;号隔开

    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> SELECT COUNT(*) FROM students where sid > 2;
        -> //
    Query OK, 0 rows affected (0.09 sec)
    
    +----------+
    | COUNT(*) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.09 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc2();
    +----------+
    | COUNT(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show create procedure simpleproc2;
    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                         | character_set_client | collation_connection | Database Collation |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()
    SELECT COUNT(*) FROM students where sid > 1; | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql> drop procedure simpleproc2;
    Query OK, 0 rows affected (0.13 sec)
    
    mysql> delimiter //
    mysql> create procedure simpleproc2()
        -> BEGIN
        -> SELECT COUNT(*) FROM students where sid > 1;
        -> SELECT COUNT(*) FROM students where sid > 2;
        -> END
        -> //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc2();
    +----------+
    | COUNT(*) |
    +----------+
    |        7 |
    +----------+
    1 row in set (0.00 sec)
    
    +----------+
    | COUNT(*) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show create procedure simpleproc2;
    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure   | sql_mode                                                                                                              | Create Procedure                                                                                                                                                | character_set_client | collation_connection | Database Collation |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | simpleproc2 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc2`()
    BEGIN
    SELECT COUNT(*) FROM students where sid > 1;
    SELECT COUNT(*) FROM students where sid > 2;
    END | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +-------------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql> use information_schema ;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from routines where routine_schema='course'
        -> ;
    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
    | SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME     | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                                                                                  | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                              | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
    | hello         | def             | course         | hello        | FUNCTION     | char      |                       50 |                    200 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_0900_ai_ci | char(50)       | SQL          | RETURN CONCAT('Hello, ',s,'!')                                                                      |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2019-04-01 10:24:44 | 2019-04-01 10:24:44 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    | simpleproc    | def             | course         | simpleproc   | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL               | NULL           | SQL          | BEGIN
    SELECT COUNT(*) INTO param2 FROM students where sid > param1;
    END                             |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2019-04-01 10:05:28 | 2019-04-01 10:05:28 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    | simpleproc2   | def             | course         | simpleproc2  | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL               | NULL           | SQL          | BEGIN
    SELECT COUNT(*) FROM students where sid > 1;
    SELECT COUNT(*) FROM students where sid > 2;
    END |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2019-04-01 10:38:17 | 2019-04-01 10:38:17 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
    +---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+----------------+--------------+-----------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
    3 rows in set (0.00 sec)
    
    mysql> select routine_name from routines where routine_schema='course'
        -> ;
    +--------------+
    | ROUTINE_NAME |
    +--------------+
    | hello        |
    | simpleproc   |
    | simpleproc2  |
    +--------------+
    3 rows in set (0.00 sec)
    
    mysql> select routine_name,routine_type from routines where routine_schema='course';
    +--------------+--------------+
    | ROUTINE_NAME | ROUTINE_TYPE |
    +--------------+--------------+
    | hello        | FUNCTION     |
    | simpleproc   | PROCEDURE    |
    | simpleproc2  | PROCEDURE    |
    +--------------+--------------+
    3 rows in set (0.00 sec)
    
    mysql> use course;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    标签label可以加在begin…end语句以及loop, repeat和while语句中通过iterate和leave来控制流程,iterate表示返回指定标签位置,leave表示跳出标签

    Database changed
    mysql> delimiter //
    mysql> CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int)
        -> BEGIN
        -> label1: LOOP
        -> SET p1 = p1 + 1;
        -> IF p1 < 10 THEN ITERATE label1; END IF;
        -> LEAVE label1;
        -> END LOOP label1;
        -> set p2=p1;
        -> END;
        -> //
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> delimiter ;
    
    mysql> delimiter ;
    mysql> call doiterate(1,@x);
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |   10 |
    +------+
    1 row in set (0.00 sec)

    Drop procedure/function语句用来删除指定名称的存储过程或函数

    mysql> drop procedure simpleproc;
    Query OK, 0 rows affected (0.16 sec)

    Declare语句通常用来声明本地变量、游标、条件或者handler
    Declare语句只允许出现在begin … end语句中而且必须出现在第一行
    Declare的顺序也有要求,通常是先声明本地变量,再是游标,然后是条件和handler

    mysql> delimiter //
    mysql> create procedure simpleproc(OUT param2 INT)
        -> BEGIN
        -> declare n int default 10;   #必须在第一行   
        -> SELECT COUNT(*) INTO param2 FROM students where sid > n;
        -> END //
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> delimiter ;
    mysql> call simpleproc(@b);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select @b;
    +------+
    | @b   |
    +------+
    |    0 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> select n;
    ERROR 1054 (42S22): Unknown column 'n' in 'field list'
    
    
    mysql> desc students;
    +---------+-------------+------+-----+---------+----------------+
    | Field   | Type        | Null | Key | Default | Extra          |
    +---------+-------------+------+-----+---------+----------------+
    | sid     | int(11)     | NO   | PRI | NULL    | auto_increment |
    | sname   | varchar(64) | YES  |     | NULL    |                |
    | gender  | varchar(12) | YES  |     | NULL    |                |
    | dept_id | int(11)     | YES  | MUL | NULL    |                |
    +---------+-------------+------+-----+---------+----------------+
    4 rows in set (0.31 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE PROCEDURE sp1 (v_sid int)
        -> BEGIN
        -> DECLARE xname VARCHAR(64) DEFAULT 'bob';
        -> DECLARE xgender INT;
        -> SELECT sname, gender INTO xname, xgender
        -> FROM students WHERE sid= v_sid;
        -> SELECT xname,xgender;
        -> END;
        -> //
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> delimiter ;
    
    mysql> call sp1(1);
    +--------+---------+
    | xname  | xgender |
    +--------+---------+
    | Andrew |       0 |
    +--------+---------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call sp1(2);
    +-------+---------+
    | xname | xgender |
    +-------+---------+
    | Andy  |       0 |
    +-------+---------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select case gender when 0 then 'male' when 1 then 'female' else 'unknown' end from students;
    +------------------------------------------------------------------------+
    | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |
    +------------------------------------------------------------------------+
    | male                                                                   |
    | male                                                                   |
    | male                                                                   |
    | female                                                                 |
    | male                                                                   |
    | male                                                                   |
    | female                                                                 |
    | female                                                                 |
    +------------------------------------------------------------------------+
    8 rows in set (0.00 sec)
    mysql> select gender,case gender when 0 then 'male' when 1 then 'female' else 'unknown' end from students;
    +--------+------------------------------------------------------------------------+
    | gender | case gender when 0 then 'male' when 1 then 'female' else 'unknown' end |
    +--------+------------------------------------------------------------------------+
    | 0      | male                                                                   |
    | 0      | male                                                                   |
    | 0      | male                                                                   |
    | 1      | female                                                                 |
    | 0      | male                                                                   |
    | 0      | male                                                                   |
    | 1      | female                                                                 |
    | 1      | female                                                                 |
    +--------+------------------------------------------------------------------------+
    8 rows in set (0.00 sec)
    
    mysql> select gender,case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end from students;
    +--------+-------------------------------------------------------------------------------+
    | gender | case when gender>0 then 'male' when gender>1 then 'female' else 'unknown' end |
    +--------+-------------------------------------------------------------------------------+
    | 0      | unknown                                                                       |
    | 0      | unknown                                                                       |
    | 0      | unknown                                                                       |
    | 1      | male                                                                          |
    | 0      | unknown                                                                       |
    | 0      | unknown                                                                       |
    | 1      | male                                                                          |
    | 1      | male                                                                          |
    +--------+-------------------------------------------------------------------------------+
    8 rows in set (0.00 sec)

    本地变量可以通过declare语句进行声明
    声明后的变量可以通过select … into var_list进行赋值,或者通过
    set语句赋值,或者通过定义游标并使用fetch … into var_list赋值

    声明的变量作用范围为被声明的begin … end语句块之间
    声明的变量和被引用的数据表中的字段名要区分开来

    第一个语句中case_value与后面各句的when_value依次做相等的对比,如果碰到相等的,则执行对应的后面的statement_list,否则接着对比,
    如果都没有匹配,则执行else后面的statement_list
    第二个语句中当search_condition满足true/1的结果时,则执行对应的statement_list,否则执行else对应的statement_list

    mysql> delimiter //
    mysql> CREATE PROCEDURE exp_case(v_sid int)
        -> BEGIN
        -> DECLARE v INT DEFAULT 1;
        -> select gender into v from students where sid=v_sid;
        -> CASE v
        -> WHEN 0 THEN update students set gender=1 where sid=v_sid;
        -> WHEN 1 THEN update students set gender=0 where sid=v_sid;
        -> ELSE
        -> update students set gender=-1 where sid=v_sid;
        -> END CASE;
        -> END;
        -> //
    Query OK, 0 rows affected (0.33 sec)
    
    mysql> delimiter ;
    mysql> select * from students where sid in (1,2);
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    +-----+--------+--------+---------+
    2 rows in set (0.00 sec)
    
    mysql> call exp_case(1);
    Query OK, 1 row affected (0.03 sec)
    
    mysql> call exp_case(2);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 1      |       1 |
    |   2 | Andy   | 1      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    mysql> call exp_case(1);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> call exp_case(2);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    另外的写法
    
    delimiter //
    CREATE PROCEDURE exp_case2(v_sid int)
    BEGIN
    DECLARE v INT DEFAULT 1;
    select gender into v from students where sid=v_sid;
    CASE
    WHEN v=0 THEN update students set gender=1 where sid=v_sid;
    WHEN v=1 THEN update students set gender=0 where sid=v_sid;
    ELSE
    update students set gender=-1 where sid=v_sid;
    END CASE;
    END;
    //
    delimiter ;

    MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句,另外return语句也是函数中的特定流程控制语句

    IF语句在存储过程或函数中表明了基础的条件选择语句IF语句中如果search_condition满足true/1的条件,则执行对应的statement_list,否则再判断elseif中的search_condition是否满足
    true/1的条件,如果都不满足则执行else中的statement_list语句

    mysql> DELIMITER //
    mysql> CREATE FUNCTION SimpleCompare(n INT, m INT)
        -> RETURNS VARCHAR(20)
        -> BEGIN
        -> DECLARE s VARCHAR(20);
        -> IF n > m THEN SET s = '>';
        -> ELSEIF n = m THEN SET s = '=';
        -> ELSE SET s = '<';
        -> END IF;
        -> SET s = CONCAT(n, ' ', s, ' ', m);
        -> RETURN s;
        -> END //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> DELIMITER ;
    mysql> select SimpleCompare(1,2);
    +--------------------+
    | SimpleCompare(1,2) |
    +--------------------+
    | 1 < 2              |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> select SimpleCompare(3,2);
    +--------------------+
    | SimpleCompare(3,2) |
    +--------------------+
    | 3 > 2              |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> select SimpleCompare(3,3);
    +--------------------+
    | SimpleCompare(3,3) |
    +--------------------+
    | 3 = 3              |
    +--------------------+
    1 row in set (0.00 sec)
    
    
    mysql> DELIMITER //
    mysql> CREATE FUNCTION VerboseCompare (n INT, m INT)  
        -> RETURNS VARCHAR(50)
        -> BEGIN
        ->   DECLARE s VARCHAR(50);
        ->   IF n = m THEN SET s = 'equals';
        ->   ELSE
        ->       IF n > m THEN SET s = 'greater';
        ->       ELSE SET s = 'less';
        ->       END IF;
        ->       SET s = CONCAT('is ', s, ' than');
        ->   END IF;
        ->   SET s = CONCAT(n, ' ', s, ' ', m, '.');
        ->   RETURN s;
        -> END //
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> DELIMITER ;
    mysql> select VerboseCompare(1,2);
    +---------------------+
    | VerboseCompare(1,2) |
    +---------------------+
    | 1 is less than 2.   |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select VerboseCompare(2,2);
    +---------------------+
    | VerboseCompare(2,2) |
    +---------------------+
    | 2 equals 2.         |
    +---------------------+
    1 row in set (0.00 sec)

    repeat语句是存储过程或函数中表达循环执行的一种方式
    Repeat语句中statement_list一直重复执行直到search_condition条件满足
    Statement_list可以包含一个或多个SQL语句

    mysql> delimiter //
    mysql> CREATE PROCEDURE dorepeat(p1 INT)
        -> BEGIN
        -> SET @x = 0;
        -> REPEAT
        -> SET @x = @x + 1;
        -> UNTIL @x > p1 END REPEAT;
        -> END
        -> //
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> delimiter ;
    mysql> call dorepeat(10);
    Query OK, 0 rows affected (0.00 sec)
    
    
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |   11 |
    +------+
    1 row in set (0.01 sec)

    while语句是存储过程或函数中表达循环执行的一种方式
    当search_condition返回为true时,则循环执行statement_list中的语句,直到search_condition的结果返回为false

    mysql> DELIMITER //
    mysql> CREATE PROCEDURE dowhile()
        -> BEGIN
        -> DECLARE v1 INT DEFAULT 5;
        -> WHILE v1 > 0 DO
        -> update students set gender=-1 where sid=v1;
        -> SET v1 = v1 - 1;
        -> END WHILE;
        -> END;
        -> //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> DELIMITER ;
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | 0      |       1 |
    |   2 | Andy   | 0      |       1 |
    |   3 | Bob    | 0      |       1 |
    |   4 | Ruth   | 1      |       2 |
    |   5 | Mike   | 0      |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.01 sec)
    
    mysql> call dowhile();
    Query OK, 1 row affected (0.02 sec)
    
    mysql> select * from students;
    +-----+--------+--------+---------+
    | sid | sname  | gender | dept_id |
    +-----+--------+--------+---------+
    |   1 | Andrew | -1     |       1 |
    |   2 | Andy   | -1     |       1 |
    |   3 | Bob    | -1     |       1 |
    |   4 | Ruth   | -1     |       2 |
    |   5 | Mike   | -1     |       2 |
    |   6 | John   | 0      |       3 |
    |   7 | Cindy  | 1      |       3 |
    |   8 | Susan  | 1      |       3 |
    +-----+--------+--------+---------+
    8 rows in set (0.00 sec)
    
    在函数中必须要有至少一个return语句,当有多个return语句时则表明函数有多种退出的方式
    mysql> delimiter //
    mysql> create function doreturn()
        -> returns int
        -> begin
        ->  select gender into @a from students where sid=1;
        ->  if @a=1 then return 1;
        ->  elseif @a=0 then return 0;
        -> else return 999;
        -> end if;
        -> end;
        -> //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> select doreturn();
    +------------+
    | doreturn() |
    +------------+
    |        999 |
    +------------+
    1 row in set (0.00 sec)

     

    转载于:https://www.cnblogs.com/Honeycomb/p/10672659.html

    展开全文
  • 传出的参数调用的时候不用给传入传出参数是既可做传入参数,可以传出参数的,他在调用时可以赋值也可以不赋值在调用时通过p(3,4,null,null),是不行的,还是用参数的合适他运行可以用execute 过程名(参数)的...

    我的过程

    如果不些输入输出参数,那么默认为是输入参数,传出的参数调用的时候不用给值,传入传出参数是既可做传入参数,有可以传出参数的,他在调用时可以赋值也可以不赋值

    在调用时通过p(3,4,null,null),是不行的,还是用参数的合适

    他运行可以用execute 过程名(参数值)的形式调用!

    create or replace procedure p(v_a in number,v_b number,v_ret  out number,v_temp in out number)

    is

    begin

    if(v_a >v_b)then

    v_ret := v_a;

    else

    v_ret := v_b;

    end if;

    v_temp := v_temp +1;

    end;

    调用写好的过程

    declare

    v_a number := 3;

    v_b number := 4;

    v_ret number ;

    v_temp number ;

    begin

    p(v_a,v_b,v_ret,v_temp);

    dbms_output.put_line(v_ret);

    dbms_output.put_line(v_temp);

    end;

    参数的顺序很重要,如果不想按照这个顺序可以用名称表示法:

    定义的过程是:procedure update_student(in_age in number,in_name in varchar2)

    begin

    update_student(in_name =>'刘琴',in_age=>19);

    end;

    展开全文
  • oracle 存储过程的的参数问题

    千次阅读 2013-03-09 20:46:06
    如果不些输入输出参数,那么默认为是输入参数,传出的参数调用的时候不用给传入传出参数是既可做传入参数,可以传出参数的,他在调用时可以赋值也可以不赋值 在调用时通过p(3,4,null,null),是不行的,还是...
  • 3) 添加public方法,调用存储过程,具体参考其他自动生成的代码; 4) 在IDAL文件夹下新建分布类文件,添加相应using信息,接口声明如下: public partial interface IDALCalendarNotes 5) 添加新增方法的接口声明; ...
  • 该排行榜参数由用户设置,设置参数可参考 this.rankconfig 变量: class ReportDataNew{ ...... constructor(){ this.rankconfig = { gameID: this.gameID, rankinglistName: "totlal_rank", rankGist: ...
  • 存储过程涉及了 查询操作、返回值和输出参数,我们来看用EXEC 命令如何调用: 复制代码 DECLARE @return_value int, @OutNums int EXEC @return_value = [dbo].[Sp_GetStudent] @Score = 90, @Nums = @...
  • 2.1.6 调用存储过程插入数据 60 2.1.7 通过VALUES插入多个行 62 2.1.8 将VALUES作为表源来使用 62 2.2 UPDATE 63 2.2.1 更新一行 64 2.2.2 根据FROM和WHERE子句更新行 65 2.2.3 更新大数据类型的列 ...
  • 本书以问答的形式组织内容,讨论了学习或使用C语言的过程中经常遇到的一些问题。书中列出了C用户经常问的400多个经典问题,涵盖了初始化、数组、指针、字符串、内存分配、库函数、C预处理器等各个方面的主题,并分别...
  • 本书以问答的形式组织内容,讨论了学习或使用C语言的过程中经常遇到的一些问题。书中列出了C用户经常问的400多个经典问题,涵盖了初始化、数组、指针、字符串、内存分配、库函数、C预处理器等各个方面的主题,并分别...
  • 你必须知道的495个C语言问题

    千次下载 热门讨论 2015-05-08 11:09:25
    5.6 如果NULL定义成#defineNULL((char*)0),不就可以向函数传入不加转换的NULL了吗? 5.7 我的编译器提供的头文件中定义的NULL为0L。为什么? 5.8 NULL可以合法地用作函数指针吗? 5.9 如果NULL和0作为空指针...
  • 3) 添加public方法,调用存储过程,具体参考其他自动生成的代码; 4) 在IDAL文件夹下新建分布类文件,添加相应using信息,接口声明如下: public partial interface IDALCalendarNotes 5) 添加新增方法的接口声明; ...
  •  5.6 如果NULL定义成#define NULL((char *)0) ,不就可以向函数传入不加转换的NULL 了吗?  5.7 我的编译器提供的头文件中定义的NULL为0L。为什么?  5.8 NULL可以合法地用作函数指针吗?  5.9 如果NULL和0...
  • 5.6 如果NULL定义成#define NULL((char *)0) ,不就可以向函数传入不加转换的NULL 了吗? 5.7 我的编译器提供的头文件中定义的NULL为0L。为什么? 5.8 NULL可以合法地用作函数指针吗? 5.9 如果NULL和0作为空指针...
  • 5.6 如果NULL定义成#define NULL((char *)0) ,不就可以向函数传入不加转换的NULL 了吗? 5.7 我的编译器提供的头文件中定义的NULL为0L。为什么? 5.8 NULL可以合法地用作函数指针吗? 5.9 如果NULL和0作为空指针...
  • 5.6 如果NULL定义成#define NULL((char *)0) ,不就可以向函数传入不加转换的NULL 了吗? 57 5.7 我的编译器提供的头文件中定义的NULL为0L。为什么? 57 5.8 NULL可以合法地用作函数指针吗? 57 5.9 如果NULL...
  • 《你必须知道的495个C语言问题》

    热门讨论 2010-03-20 16:41:18
    5.6 如果NULL定义成#define NULL((char *)0) ,不就可以向函数传入不加转换的NULL 了吗? 57 5.7 我的编译器提供的头文件中定义的NULL为0L。为什么? 57 5.8 NULL可以合法地用作函数指针吗? 57 5.9 如果NULL...
  • android sqlite数据库使用

    热门讨论 2011-09-17 00:25:56
    在程序的开发维护过程中,数据库的结构可能会变化,那么这个方法就用处了。在DatabaseHelper这个对象一创建时,就已经把参数 DATABASE_VERSION传入,这样,如果Android发现此版本与现有版本不一致,就会调用这个...
  • // 如果onlyIfAbsent为false、或者之前的valuenull时,才更新value if (!onlyIfAbsent || oldValue == null) e.value = value; // LinkedHashMap的回调函数 afterNodeAccess(e); return...
  • 每个在堆里面的实例对象是存储属性的,比如说,现在堆里面的实例对象里面都存姓名、性 别和年龄。每个属性又都一个地址。 $p1=new Person();等号的右边$p1 是一个引用变量,通过赋值运算符“=”把对象的首地址赋...
  • javascript学习笔记.docx

    2012-05-24 17:10:23
    7) 在函数体内隐含的arguments对象,可取得所有传入的参数,可用类似数组的方式读/写各参数,length属性表示参数的个数。但函数的只读属性length表示函数实际定义的参数个数。一个名为callee的属性代表函数本身。 ...
  • Java EE常用框架.xmind

    2020-06-19 16:08:35
    4,optional属性表示关联的该实体是否能够存在null值,默认为ture,如果设置为false,则该实体不能为null, 5, mapperBy属性:指关系被维护端 1,@JoinColumn注释是保存表与表之间关系的字段 2,如果不设置...
  • Oracle 10g 开发与管理

    2013-01-15 20:47:53
    2.调用存储过程 72 3.修改(替换同名的存储过程) 73 4.参数 73 (1)In 参数:向过程传入一个 73 (2)Out参数: 73 (3)In Out参数: 74 (4)默认值 74 5.局部变量和子过程 74 8.2函数(function) 75 1.创建 75...
  • 3.4.3 过程调用 20 3.5 一个创建并发进程的例子 20 3.5.1 一个顺序执行的C实例 20 3.5.2 程序的开发版本 21 3.5.3 时间分片 22 3.5.4 单线程的进程 23 3.5.5 使各进程分离 23 3.6 执行新的代码 24 3.7 上...
  • 可使用存储过程写分页逻辑,提高效率。 多选 5.根据《阿里巴巴Java开发手册》,以下功能必须进行水平权限控制校验的:ABCD A .订单详情页面。 B .类目管理后台。 C .店铺装修后台。 D .订单付款页面。 ...
  •  注:官方帮助文档的原文中对该事件的描述中这样一句:“If you return 'true' or do not return any value then the upload proceeds.”,从中可以看到既定的设计是当不返回任何的时候应该等同于返回true,...

空空如也

空空如也

1 2 3
收藏数 47
精华内容 18
关键字:

調用存儲過程傳入有null值