mysql存储过程和函数怎么写?怎么调用?

dutexiaochun 2014-12-10 10:52:39
mysql存储过程和函数怎么写?怎么调用?初学者 对这个还不太懂,哪位大婶能教教我!!最好讲的详细点 有视频教程是极好的!!
...全文
322 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2014-12-10
  • 打赏
  • 举报
回复
如果英文看不懂,则可以使用网上的在线翻译,或者搜索官方的中文帮助文档。
引用
20.2. 存储程序的语法 20.2.1. CREATE PROCEDURE和CREATE FUNCTION 20.2.2. ALTER PROCEDURE和ALTER FUNCTION 20.2.3. DROP PROCEDURE和DROP FUNCTION 20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION 20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS 20.2.6. CALL语句 20.2.7. BEGIN ... END复合语句 20.2.8. DECLARE语句 20.2.9. 存储程序中的变量 20.2.10. 条件和处理程序 20.2.11. 光标 20.2.12. 流程控制构造 存储程序和函数是用CREATE PROCEDURE
.
benluobo 2014-12-10
  • 打赏
  • 举报
回复
先试着自己按照文档列子编写一下
ACMAIN_CHM 2014-12-10
  • 打赏
  • 举报
回复
新手一定要先掌握好百度,和官方帮助文档的使用。 建议楼主下载MYSQL官方免费手册,然后阅读其中相关的章节。
引用
12.1.12. CREATE PROCEDURE and CREATE FUNCTION Syntax CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement These statements create stored routines. By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it. The CREATE FUNCTION statement is also used in MySQL to support UDFs (user-defined functions). See Section 21.3, “Adding New Functions to MySQL”. A UDF can be regarded as an external stored function. However, do note that stored functions share their namespace with UDFs. See Section 8.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions. To invoke a stored procedure, use the CALL statement (see Section 12.2.1, “CALL Syntax”). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation. To execute the CREATE PROCEDURE or CREATE FUNCTION statement, it is necessary to have the CREATE ROUTINE privilege. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. See Section 18.2.2, “Stored Routines and MySQL Privileges”. If binary logging is enabled, the CREATE FUNCTION statement might also require the SUPER privilege, as described in Section 18.6, “Binary Logging of Stored Programs”. The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time, as described later. If the routine name is the same as the name of a built-in SQL function, a syntax error occurs unless you use a space between the name and the following parenthesis when defining the routine or invoking it later. For this reason, avoid using the names of existing SQL functions for your own stored routines. The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. It is always allowable to have spaces after a stored routine name, regardless of whether IGNORE_SPACE is enabled. The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive. Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name. Note Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. (FUNCTION parameters are always regarded as IN parameters.) An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns. For each OUT or INOUT parameter, pass a user-defined variable in the CALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter. The following example shows a simple stored procedure that uses an OUT parameter: 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) The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 18.1, “Defining Stored Programs”. The RETURNS clause may be specified only for a FUNCTION, for which it is mandatory. It indicates the return type of the function, and the function body must contain a RETURN value statement. If the RETURN statement returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an ENUM or SET value in the RETURNS clause, but the RETURN statement returns an integer, the value returned from the function is the string for the corresponding ENUM member of set of SET members. The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters: mysql> CREATE FUNCTION hello (s CHAR(20)) mysql> RETURNS CHAR(50) DETERMINISTIC -> 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) Parameter types and function re
chengchow2001 2014-12-10
  • 打赏
  • 举报
回复
去看MSYQL手册或者MYSQL入门书籍

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧