MySql 5.1 stored procedures

2011-01-18  来源:本站原创  分类:Database  人气:83 

Here is mysql5.1 stored procedures and functions of the basic syntax:
(1) stored procedure
CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter [,...]])
[Characteristic ...] routine_body
(2) function
CREATE
[DEFINER = {user | CURRENT_USER}]
FUNCTION sp_name ([func_parameter [,...]])
RETURNS type
[Characteristic ...] routine_body
(3) Parameters
proc_parameter: [IN | OUT | INOUT] param_name type

func_parameter: param_name type

type: Any valid MySQL data type

characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}

routine_body: Valid SQL routine statement
(4) in \ out \ inout introduce three types
in: pass parameters to the stored procedure inside a stored procedure may modify the variable, but when returned from a stored procedure, it does not modify the parameters of the caller is not visible;
out: pass parameters to stored procedures, the initial value is NULL, when returned from a stored procedure, the value of the caller can be seen;
inout: passing parameters to a stored procedure, the initial value can be set by the caller, you can modify the stored procedure variables, and modify the results of the caller to see;
(5) introduce a simple command-line definition of the stored procedure process:
mysql> delimiter / /
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT (*) INTO param1 FROM t;
-> END / /
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter;

mysql> CALL simpleproc (@ a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @ a;
+------+
| @ A |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
A brief introduction of these statements:
delimiter / / we will delimiter (delimiter) from the ";" to "//", can make the stored procedure ";" can not be resolved by the interpreter, which can be passed to the server for execution;
(7) a brief look at function execution
mysql> CREATE FUNCTION hello (s CHAR (20))
mysql> RETURNS CHAR (50) DETERMINIS TIC
-> RETURN CONCAT ('Hello,', s ,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello ('world');
+----------------+
| Hello ('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
A brief introduction of these statements:
returns char (50) deterministic: the return type is defined
return concat (): function return statement, if the return type and returns a different definition of the type of return, it will be converted to the type of returns, for example, returns the type is set or enum, and return type to integer, so will its forced into a corresponding set and the corresponding string enum
(8) routine_body: execution, including the use of begin and end, can include DDL statements, such as create and drop, etc. You can also use stored procedures and transaction of the commit statement;
(9) can use the stored procedure result set to accept the return results, but can not use stored function; can not be used in routine use, there are implicit declaration of use database;

相关文章
  • MySQL basic statements (stored procedures) 2010-06-14

    # Creating and selecting a database create database mydata; use mydata # Displays the database show databases; # Show table show tables: # View the table desc Table name ; # The customer table create table customers ( cust_id int primary key, # Prima

  • Mysql triggers and stored procedures review the basic grammar 2011-05-12

    Do not operate them all day long, somewhat forgotten, review, and notes about mysql triggers, stored procedures; content transferred from another site, belongs to original author. A trigger 1. Syntax : Naming CREATE TRIGGER < Trigger name > <-- {

  • mysql view all stored procedures, functions, views, triggers, table 2011-05-27

    Query the database stored procedures and functions Method One: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE' // Stored procedure select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION' // Fun

  • MySql 5.1 stored procedures 2011-01-18

    Here is mysql5.1 stored procedures and functions of the basic syntax: (1) stored procedure CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE sp_name ([proc_parameter [,...]]) [Characteristic ...] routine_body (2) function CREATE [DEFINER = {user | C

  • Mysql triggers and stored procedures review basic grammar 2011-05-12

    These do not operate all day long, a little bit forgotten, review and notes about mysql triggers, stored procedures; content transferred from another site, belongs to original author. A trigger 1. Grammar : Naming CREATE TRIGGER < Trigger name > <

  • Turn: mysql stored procedures Detailed 2010-11-30

    Detailed MySQL stored procedure Transfer: http://www.owe-love.com/myspace/?action=show&id=187 1. Stored Procedures Introduction We used to manipulate the database language SQL statement executed when the need first compiled and then executed, and sto

  • Question: Navicat for MySQL stored procedures on 2010-08-13

    Navicat for MySQL How to create a stored procedure with parameters? Not use the console to write stored procedures, but through the interface that creates a stored procedure to create! Solution! Thank you!

  • mysql bulk insert data in stored procedures 2010-09-09

    mysql bulk insert data in stored procedures <Date: 2008-01-17> <Author: admin> <Category: mysql> View comments mysql bulk insert data in stored procedures DELIMITER $ $ DROP PROCEDURE IF EXISTS `test`. `Sp_insert_batch` $ $ CREATE DEFINE

  • mysql how to learn functions and stored procedures 2011-05-05

    1. Create a stored procedure, use the delimiter $ $ to define a statement terminator, to create complete, restore the statement terminator delimiter; The default due to MySQL ";" as the delimiter, then the process of the body to every word MySQL

  • php + mysql stored procedures in the performance of a simple comparison 2011-05-03

    PHP + MYSQL, using MYSQL stored procedure is actually very good, but will make it fast, efficient, Here, the summary of the usage of MYSQL it under review, the use of the PDO 1 / / Do not use stored procedures $ Time = microtime (TRUE); $ Mem = memor

  • Mysql stored procedures first test, record what 2011-07-19

    Today the first trial mysql stored procedure, and eventually found not suitable for my needs, stored procedures can not pass an array as a parameter ... The following snippet of code to record what the learning process: delimiter // drop procedure if

  • mysql stored procedures Study Notes - Error Handling Collection 2011-01-06

    mysql stored procedures Study Notes - Error handling collection definition: DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code | MySQL error code | condition_name} handler_actions The definition includes: · Handler type (CONTINUE, EXIT) /

  • mysql stored procedures first test 2011-01-10

    Since 5.0, mysql support stored procedures, nothing small test last weekend, in this preliminary record their own learning process. C:\Users\michael>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 S

  • (R) MySQL stored procedures, and include transactions, parameters, nested calls, cursor, loop, etc. 2011-04-20

    MySQL stored procedures, and include services, parameters, nested calls, cursors, loops, etc., read the MySQL stored procedures, and include services, parameters, nested calls, cursors, loops, etc., view plaincopy to clipboardprint? Drop procedure if

  • Create MySql Stored Procedures 2011-05-10

    Summary: Let me talk about the advantages of database stored procedures. Then MySql, for example, instructions to create a stored procedure method. Body: <1> create a database stored procedure has at least two distinct advantages: 1 SQL statements s

  • MySQL stored procedures and functions of the difference 2011-06-23

    Transfer: http://www.gooseeker.com/cn/node/Fuller/2010061201 MySQL stored procedures and functions of the difference MySQL stored procedure (stored procedure) and functions (stored function) collectively referred to as stored routines, whether stored

  • Introduction to stored procedures mysql 2011-06-30

    mysql stored procedures started practice - create the same cycle structure of the table Description Background: There are multiple applications have the same structure of the table, or if you simply create a copy, paste + change; can also be done, mo

  • First test mysql stored procedures, records about 2011-07-19

    Today the first trial mysql stored procedure, and eventually found not suitable for my needs, stored procedures can not pass an array as a parameter ... The following snippet of code to record what the learning process: delimiter // drop procedure if

  • mysql stored procedures commonly used commands 2011-10-25

    See the wording on the stored procedure: http://trinea.iteye.com/blog/977141 mysql Chinese reference documentation see: http://dl.iteye.com/topics/download/82db8bb1-7c55-3e74-9f56-de5f8e004ded 0, stored procedures need to pay attention to the best an

  • MYSQL query the parent node of all nodes, sorted by level of stored procedures 2011-10-12

    Query the parent node of all nodes, sorted by level of stored procedures Parent-child relationship table structure: `Parentnode` int (11), - the parent node `Node` int (11), - node `Isparent` int (11) - whether the parent node drop PROCEDURE if EXIST