Viii. mysql view, stored procedures, functions, and Time Scheduler

Source: Internet
Author: User
Tags mysql view

1.Create or Replace ViewEmp_view as Select *  fromT4; Create a view named Emp_view for the T4 table2.Drop ViewEmp_view Deleting a view=======================================1. Creating a stored procedure (querying all data)Create procedurep1 () READS SQL DATABEGIN        Select *  fromT4; END2. Create a stored procedure (query pass parameter data)Create procedureP2 (inchTidINT) READS SQL DATABEGIN        Select *  fromT4whereId=Tid; END3. Create a stored procedure (query the parameter data and return name)Create procedureP3 (inchTidINT, Out TnameCHAR(255)) READS SQL DATABEGIN        SelectName fromT4whereId=Tid intoTname; END==========================================1. Create a stored function (the function must have a return value)Create functionP4 (inchTidINT)    RETURNS CHAR(255) Deterministic READS SQL DATABEGIN        DECLARET_nameCHAR(255); SELECTTname fromT4whereId=Tid intoT_name; RETURNT_name; END===========================================1. Defining error conditions and handling scenario 1: For example, the data to be inserted in the process unique key value already existsCreate procedureP5 ()BEGIN        DECLARE CONTINUEHANDLER forSQLSTATE'23000' Set @x2 = 1;//23000 Create an existing or duplicate error code in MySQLSet @x = 1; Insert  into......//Let's say this one doesn't repeat .Set @x = 2; Insert  into......//Let's say this one doesn't repeat .Set @x = 3; Insert  into.....//RepeatENDInvocation: Call P5 ();Select @x,@x2; Will output:3,1Cause: All the previous 2 were executed smoothly,@x = 3, the last repetition, which is thethe @x2 is set to 1;=============================================1the cursor concept (which is actually used for the fetch result set)Create procedureP6 () READS SQL DATA//indicates that the procedure contains only read and no writeBEGIN        DECLARETidint;//Define IDDECLARETnameChar;//Define nameDECLARETcurcursor  for SelectId,name fromT4;//Defining CursorsDECLARE ExitHandler for  notFound Tcur;//exits if the cursor is empty, does not allow execution to continueSet @error = "';//Set return call errorOpentcur; RepeatFetchTcur intoTid,tname; ifTid= 1  Then                        Set @error = 'I'm 1, but I made an error.'; Else                        Set @error = 'I am not 1 ah, really ah!'; End if; Until0 Endrepeat; CloseTcur;//CloseEND;===============================================1. Process Controlif:        if 1=1  ThenI am 1; ElseI am not 1; End if             Case:         Case         when 1=1  ThenI am 1; ElseI am not 1; End  CaseLoop:emp:LOOP #...code .....ENDLOOP EMP; Leave EMP;//indicates that exiting from the callout's process (the EMP means the label of the loop above) is equivalent to break in PHP, except that leave specifies the structure iterate emp that needs to be popped out;//indicates that this loop skips the rest of the code block equivalent to PHP continue repeat:repeat//The loop starts the equivalent of PHP's do ... while... (because he makes the judgment at the end of the loop, so this loop body will be executed at least once) #....code ....//related content until1=2 EndRepeat//exit the Loop if the condition is false while://a concept with while in PHP, just a syntax difference while 1=1Do//Obviously, this is a dead loop #...code ...End  while; ================================================1. Time Trigger (Time Scheduler)1. The default time trigger is off stateSetGLOBAL Event_scheduler= 1Open2. Show variables like 'Scheduler'View Time Scheduler Open/off State3. Show events;//View all schedulers4.AlterEvent emp Disable;//to disable a scheduler named EMP5.DropEvent EMP;//to remove a scheduler named EMP6. Create a schedulerCreateEvent EMP//Scheduler Rank onSchedule every5Second//time 5 Seconds do//StartInsert  intohahaValues(NULL);//Executive Body=================================================PS:: the- -- ,continue to learn ing ... ..

Related Article

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.