Mysql:Mysql 5.7 FAQ:A.4 Stored Procedures and Functiions
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql:Mysql 5.7 FAQ:A.4 Stored Procedures and Functiions,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含11194字,纯文字阅读大概需要16分钟。
内容图文
- A.4.1. Does MySQL 5.7 support stored procedures and functions?
- A.4.2. Where can I find documentation for MySQL stored procedures and stored functions?
- A.4.3. Is there a discussion forum for MySQL stored procedures?
- A.4.4. Where can I find the ANSI SQL 2003 specification for stored procedures?
- A.4.5. How do you manage stored routines?
- A.4.6. Is there a way to view all stored procedures and stored functions in a given database?
- A.4.7. Where are stored procedures stored?
- A.4.8. Is it possible to group stored procedures or stored functions into packages?
- A.4.9. Can a stored procedure call another stored procedure?
- A.4.10. Can a stored procedure call a trigger?
- A.4.11. Can a stored procedure access tables?
- A.4.12. Do stored procedures have a statement for raising application errors?
- A.4.13. Do stored procedures provide exception handling?
- A.4.14. Can MySQL 5.7 stored routines return result sets?
- A.4.15. Is WITH RECOMPILE supported for stored procedures?
- A.4.16. Is there a MySQL equivalent to using mod_plsql as a gateway on Apache to talk directly to a stored procedure in the database?
- A.4.17. Can I pass an array as input to a stored procedure?
- A.4.18. Can I pass a cursor as an IN parameter to a stored procedure?
- A.4.19. Can I return a cursor as an OUT parameter from a stored procedure?
- A.4.20. Can I print out a variable‘s value within a stored routine for debugging purposes?
- A.4.21. Can I commit or roll back transactions inside a stored procedure?
- A.4.22. Do MySQL 5.7 stored procedures and functions work with replication?
- A.4.23. Are stored procedures and functions created on a master server replicated to a slave?
- A.4.24. How are actions that take place inside stored procedures and functions replicated?
- A.4.25. Are there special security requirements for using stored procedures and functions together with replication?
- A.4.26. What limitations exist for replicating stored procedure and function actions?
- A.4.27. Do the preceding limitations affect the ability of MySQL to do point-in-time recovery?
- A.4.28. What is being done to correct the aforementioned limitations?
A.4.1. |
Does MySQL 5.7 support stored procedures and functions? |
Yes. MySQL 5.7 supports two types of stored routines, stored procedures and stored functions. |
|
A.4.2. |
Where can I find documentation for MySQL stored procedures and stored functions? |
A.4.3. |
Is there a discussion forum for MySQL stored procedures? |
Yes. See https://forums.mysql.com/list.php?98. |
|
A.4.4. |
Where can I find the ANSI SQL 2003 specification for stored procedures? |
Unfortunately, the official specifications are not freely available (ANSI makes them available for purchase). However, there are books, such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer, that provide a comprehensive overview of the standard, including coverage of stored procedures. |
|
A.4.5. |
How do you manage stored routines? |
It is always good practice to use a clear naming scheme for your stored routines. You can manage stored procedures with |
|
A.4.6. |
Is there a way to view all stored procedures and stored functions in a given database? |
Yes. For a database named SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA=‘
For more information, see Section 24.21, “The INFORMATION_SCHEMA ROUTINES Table”. The body of a stored routine can be viewed using SHOW CREATE FUNCTION (for a stored function) or SHOW CREATE PROCEDURE (for a stored procedure). See Section 13.7.5.9, “SHOW CREATE PROCEDURE Statement”, for more information. |
|
A.4.7. |
Where are stored procedures stored? |
In the You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.5.9, “SHOW CREATE PROCEDURE Statement”. |
|
A.4.8. |
Is it possible to group stored procedures or stored functions into packages? |
No. This is not supported in MySQL 5.7. |
|
A.4.9. |
Can a stored procedure call another stored procedure? |
Yes. |
|
A.4.10. |
Can a stored procedure call a trigger? |
A stored procedure can execute an SQL statement, such as an UPDATE, that causes a trigger to activate. |
|
A.4.11. |
Can a stored procedure access tables? |
Yes. A stored procedure can access one or more tables as required. |
|
A.4.12. |
Do stored procedures have a statement for raising application errors? |
Yes. MySQL 5.7 implements the SQL standard |
|
A.4.13. |
Do stored procedures provide exception handling? |
MySQL implements HANDLER definitions according to the SQL standard. See Section 13.6.7.2, “DECLARE ... HANDLER Statement”, for details. |
|
A.4.14. |
Can MySQL 5.7 stored routines return result sets? |
Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or higher) client/server protocol for this to work. This means that, for example, in PHP, you need to use the |
|
A.4.15. |
Is |
Not in MySQL 5.7. |
|
A.4.16. |
Is there a MySQL equivalent to using |
There is no equivalent in MySQL 5.7. |
|
A.4.17. |
Can I pass an array as input to a stored procedure? |
Not in MySQL 5.7. |
|
A.4.18. |
Can I pass a cursor as an |
In MySQL 5.7, cursors are available inside stored procedures only. |
|
A.4.19. |
Can I return a cursor as an |
In MySQL 5.7, cursors are available inside stored procedures only. However, if you do not open a cursor on a SELECT, the result will be sent directly to the client. You can also |
|
A.4.20. |
Can I print out a variable‘s value within a stored routine for debugging purposes? |
Yes, you can do this in a stored procedure, but not in a stored function. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You will need to use the MySQL 4.1 (or above) client/server protocol for this to work. This means that, for example, in PHP, you need to use the |
|
A.4.21. |
Can I commit or roll back transactions inside a stored procedure? |
Yes. However, you cannot perform transactional operations within a stored function. |
|
A.4.22. |
Do MySQL 5.7 stored procedures and functions work with replication? |
Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 23.7, “Stored Program Binary Logging”. |
|
A.4.23. |
Are stored procedures and functions created on a master server replicated to a slave? |
Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. |
|
A.4.24. |
How are actions that take place inside stored procedures and functions replicated? |
MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated. Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function. |
|
A.4.25. |
Are there special security requirements for using stored procedures and functions together with replication? |
Yes. Because a slave server has authority to execute any statement read from a master‘s binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:
|
|
A.4.26. |
What limitations exist for replicating stored procedure and function actions? |
Nondeterministic (random) or time-based actions embedded in stored procedures may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced, and therefore, random actions replicated to a slave will not mirror those performed on a master. Declaring stored functions to be In addition, time-based actions cannot be reproduced on a slave because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints. Finally, nontransactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that a master may be partially updated from DML activity, but no updates are done to the slave because of the errors that occurred. A workaround is for a function‘s DML actions to be carried out with the |
|
A.4.27. |
Do the preceding limitations affect the ability of MySQL to do point-in-time recovery? |
The same limitations that affect replication do affect point-in-time recovery. |
|
A.4.28. |
What is being done to correct the aforementioned limitations? |
You can choose either statement-based replication or row-based replication. The original replication implementation is based on statement-based binary logging. Row-based binary logging resolves the limitations mentioned earlier. Mixed replication is also available (by starting the server with --binlog-format=mixed). This hybrid form of replication “knows” whether statement-level replication can safely be used, or row-level replication is required. For additional information, see Section 16.2.1, “Replication Formats”. |
原文:https://www.cnblogs.com/jinzhenshui/p/12565820.html
内容总结
以上是互联网集市为您收集整理的Mysql:Mysql 5.7 FAQ:A.4 Stored Procedures and Functiions全部内容,希望文章能够帮你解决Mysql:Mysql 5.7 FAQ:A.4 Stored Procedures and Functiions所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。