MySQL trigger stored procedure function view

Source: Internet
Author: User

This example achieves the following results:
0. The test database has the userinfo user information table and the userinfolog user information log table.
1. When a new record is created for a userinfo table, the trigger adds the new log to userinfolog.
2. Create a stored procedure for adding records to the userinfo table
3. Based on the date of birth field in the userinfo table, we will create a user-defined function for simple age calculation.
4. Create a userinfo view to call the age function.
-------------
0. Prepare related tables
Mysql> use test;
Mysql> Create Table userinfo (userid int, username varchar (10), userbirthday date );
Mysql> Create Table userinfolog (logtime datetime, loginfo varchar (100 ));
Mysql> describe userinfo;
1. Trigger
Mysql> delimiter |
Mysql> Create trigger beforeinsertuserinfo
-> Before insert on userinfo
-> For each row begin
-> Insert into userinfolog values (now (), Concat (New. userid, new. username ));
-> End;
-> |
Mysql> delimiter;
Mysql> show triggers;
2. Stored Procedure
Mysql> delimiter //
Mysql> Create procedure spinsertuserinfo (
-> Puserid int, pusername varchar (10)
->, Puserbirthday date
->)
-> Begin
-> Insert into userinfo values (puserid, pusername, puserbirthday );
-> End;
-> //
Mysql> show procedure status like 'spinsertuserinfo ';
Mysql> call spinsertuserinfo (1, 'hangsan', current_date );
Mysql> select * From userinfo;

3. custom function
mysql> Update userinfo
-> set userbirthday = '2017. 01.01 '
-> where userid = '1';
mysql> drop function if exists fngetage;
mysql> delimiter //
mysql> Create Function fngetage (pbirthday date)
-> Returns integer
-> begin
-> return year (now ()-year (pbirthday );
-> end
-> //
4. view
mysql> Create view viewuserinfo
-> as select *, fngetage (userbirthday) as userage from userinfo;
mysql> select * From viewuserinfo;
clear log records
mysql> truncate table userinfolog;
mysql> Delete from userinfolog;
this article is taken from the "Blue network" http://www.lan27.com/Article/200807/7557.htm

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.