Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the difference between stored procedures and functions in Mysql, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Three paradigms of database design:

The first paradigm: each column of a database table is an indivisible atomic data item, that is, the column is inseparable. The second paradigm: based on the first paradigm, it requires that each instance or record in the database table must be uniquely distinguishable, that is, a unique identity. The third normal form: based on the second normal form, any non-primary attribute does not depend on other non-primary attributes, that is, referencing the primary key. (recommended: MySQL tutorial)

View

The view is a virtual table that does not store data, but only contains dynamic data of the statement at the time of definition.

Create view view_name as sql query statement

Stored procedure

A collection of one or more sql statements with the following advantages (condensed: simple / secure / high performance):

1. Stored procedures can achieve faster execution speed.

2. Stored procedures allow standard components to be programming.

3. The stored procedure can be written with flow control statements, which has strong flexibility and can complete complex judgments and complex operations.

4. Stored procedures can be fully utilized as a security mechanism.

5. Stored procedures can reduce network traffic

Delimiter delimiter create procedure | proc proc_name () begin sql statement end delimiter delimiter;-- restore the delimiter, in order not to affect the use of the following statements, the default delimiter is; but in order to be reused throughout the stored procedure, you generally need a custom delimiter (except\)

What is the difference between stored procedures and functions?

Similarities: stored procedures and functions are collections of sql statements that operate on the database for repeatable execution.

1) if both stored procedures and functions are compiled once, they will be cached. The next time you use them, you will directly hit the compiled sql statement and do not need to reuse it. Reduce network interaction and network access traffic.

Difference: the identifier is different, the identifier of the function is function, and the stored procedure is proceduce.

1) there is a return value in the function, and there must be a return value, but the procedure does not return a value, but you can implement multiple parameters or return values by setting the parameter type (in,out).

2) the stored function is called by select, and the stored procedure needs to be called by call.

3) select statements can be called in stored procedures, but select statements other than select..into cannot be used in functions.

4) through the in out parameter, the process-related function is more flexible and can return multiple results.

Trigger

There are six kinds of specific operations when changing the table data, which are the operations before and after adding, deleting and changing.

Create trigger trigger_name ALTER | BEFORE select | update | deleteon table for each rowtrigger_stmt

Key points:

Only tables support triggers, views and temporary tables do not support triggers, do not support updates and overrides, modifications must be deleted and then created

Journal

There are four main types of log files for Mysql:

Error log: record problems when starting, running, or stopping mysql

Query log: record all msyql activities

Binary log: all statements that record updated data

Slow query log: record any query that is slow

Thank you for reading this article carefully. I hope the editor will share what is the difference between stored procedures and functions in Mysql. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.