MySQL preliminary understanding of stored procedures

Source: Internet
Author: User
Tags case statement

Create a simple stored procedure

Basic operations

1   /*querying a database for all stored procedures*/2Showprocedurestatus;3  4  /*queries are a stored procedure*/  5ShowCreate proceduretes1; 6 7  /*create a stored procedure*/8  CREATE PROCEDUREtest1;9  Ten  /*Delete a stored procedure "without parentheses ()"*/ One  DROP PROCEDUREtest1;  A   -  /*executes a stored procedure "must have parentheses, regardless of parameters "*/ -Call Test1 ();

Create

1 DROP PROCEDURE' Proc_test ' (2 inchAint, 3 inchBint, 4Outsum int5 )6 BEGIN7     DECLARECint;8     ifA is NULL  Then SetA= 0; 9     End if;Ten    One     ifB is NULL  Then SetB= 0; A     End if; -  -     Set sum  =A+b; the END;

Perform the above stored results to verify that the results are correct, such as the result OK:

Set @b = 5 ; -- Execute Call Proc_adder (2,@b,@s); Select @s  as sum;

Control statements in a stored procedure

If statement:

1 DROP PROCEDURE IF EXISTSStutse;2 /*use of stored if*/3 CREATE PROCEDUREStutse (4     inchAxINT5 )6 BEGIN7     DECLARECVARCHAR( -);8 9  IFAx= 0  Then Ten     SETC="I am c,ax for 0"; OneELSEIF Ax= 1  Then A  SETC="I am c,ax for 1"; - ELSE -   SETC="I am C"; the END if; -  SELECTC;  -  - END; +  - SET @ax=4; +Call Stutse (@ax);

Case statement:

1 DROP PROCEDURE IF EXISTStest2;2 /*use of stored case*/3 CREATE PROCEDURETest2 (4   inchAINT    5 )6 BEGIN  7  DECLAREBVARCHAR( -);8 9   CaseaTen   when 0  Then  One      SETB=" the"; A   when 1  Then -    SetB="111"; -  ELSE the    SETB="BBBB"; -  END  Case; -  - SELECTb; + END; -  + SET @a=2; ACall Test2 (@a);

The drawbacks of stored procedures

Different database, syntax difference is very big, transplant difficulty, change the database, need to write again;

Bad management, put too much business logic in the stored procedure is not good maintenance, not conducive to hierarchical management, easy chaos, general stored procedures for individual performance requirements of the business, the other need is not very large;

...

MySQL preliminary understanding of stored procedures

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.