Stored procedure syntax and examples

Http://www.cnblogs.com/kkcheng/archive/2010/03/19/1689672.html

Stored procedures, like a programming language, also contain data types, process control, input and output, and its own library of functions.

--------------------Basic Syntax--------------------

I. Creating a stored procedure
CREATE PROCEDURE Sp_name ()
Begin
.........
End

two. Call the stored procedure
1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be followed by parentheses, even if the stored procedure has no parameter passing

three. Delete stored procedures
1. Basic grammar:
drop procedure sp_name//

2. Matters needing attention
(1) cannot delete another stored procedure in one stored procedure, can only call another stored procedure

four. Other common commands

1.show Procedure Status
Displays basic information about stored procedures stored in the database, including the owning database, stored procedure name, creation time, and so on

2.show CREATE PROCEDURE Sp_name
Show details of a MySQL stored procedure

--------------------data types and operators--------------------
First, basic data types:
Slightly

Second, variable:

Custom variable: DECLARE a INT;    SET a=100; The following statements can be used instead: DECLARE a INT DEFAULT 100;

Variables are divided into user variables and system variables, and system variables are divided into session and global level variables

User variables: User variable names generally start with @, misuse of user variables can cause programs to be difficult to understand and manage

1. Use user variables in MySQL client
mysql> SELECT ' Hello world ' into @x;
Mysql> SELECT @x;

mysql> SET @y= ' Goodbye cruel World ';
Mysql> Select @y;

Mysql> SET @z=1+2+3;
Mysql> Select @z;

2. Use user variables in stored procedures

Mysql> CREATE PROCEDURE greetworld () SELECT CONCAT (@greeting, ' world ');
mysql> SET @greeting = ' Hello ';
Mysql> call Greetworld ();

3, passing the global scope of user variables between stored procedures
mysql> CREATE PROCEDURE p1 () SET @last_procedure = ' P1 ';
Mysql> CREATE PROCEDURE p2 () SELECT CONCAT (' Last PROCEDURE is ', @last_procedure);
Mysql> call P1 ();
Mysql> call P2 ();

third, Operator:
1. Arithmetic operators
+ Add SET var1=2+2; 4
-Minus SET var2=3-2; 1
* by SET Var3=3*2; 6
/except SET VAR4=10/3; 3.3333
div divisible SET var5=10 Div 3; 3
% modulo SET var6=10%3; 1

2. Comparison operators
> Greater than 1>2 False
< less than 2<1 False
<= is less than or equal to 2<=2 True
>= is greater than or equal to 3>=2 True
BETWEEN 5 BETWEEN 1 and True between two values
Not BETWEEN is not between two values 5 not BETWEEN 1 and False
In the Set 5 in (1,2,3,4) False
Not in the set 5 not in (1,2,3,4) True
= equals 2=3 False
<>!= not equal to 2<>3 False
<=> strictly compare two NULL values for equality Null<=>null True
Like simple pattern matching "guy Harrison" like "guy%" True
REGEXP regular-match "Guy Harrison" REGEXP "[Gg]reg" False
Is Null was NULL 0 is null False
is not NULL isn't NULL 0 is not NULL True

3. Logical operators

4. Bitwise operators
| Or
& and
<< left Shift
>> Right Shift
~ Non (Monocular operation, bitwise reverse)

Comments:

MySQL stored procedures can use two styles of annotations
Double horizontal bar:--

This style is typically used for single-line annotations
C Style:/* Comment content/* General use for multiline comment

--------------------Process Control--------------------
First, sequential structure
Second, the branch structure
If
Case

Third, the circulation structure
For loop
While loop
Loop loop
Repeat until cycle

Note:
Block definitions, commonly used
Begin
......
End
You can also alias a block, such as:
Lable:begin
...........
End lable;
You can use leave lable to jump out of blocks and execute code after blocks

Begin and end are like {and} in C language.

--------------------Input and Output--------------------

The parameters of the MySQL stored procedure are used in the definition of stored procedure, there are three kinds of parameter types, in,out,inout
Create procedure|function ([[In | Out | INOUT] Parameter name data class ...]

In input parameters
Indicates that the value of this parameter must be specified when the stored procedure is invoked, and the value that modifies the parameter in the stored procedure cannot be returned, the default value

Out output Parameters
The value can be changed inside the stored procedure and can be returned

INOUT input and output parameters
Specified at call time, and can be changed and returned

In parameter example:
CREATE PROCEDURE sp_demo_in_parameter (in p_in INT)
BEGIN
SELECT p_in; --Query input parameters
SET p_in=2; --Modify
Select p_in;--View modified values
End;

Execution results:
Mysql> Set @p_in =1
Mysql> call Sp_demo_in_parameter (@p_in)
Slightly
Mysql> Select @p_in;
Slightly
As you can see, p_in is modified in the stored procedure, but does not affect the value of @p_id

Out parameter Example
Create:
Mysql> CREATE PROCEDURE sp_demo_out_parameter (out P_out INT)
BEGIN
SELECT p_out;/* View OUTPUT Parameters * *
SET p_out=2;/* Modify Parameter value * *
SELECT p_out;/* See if any changes are made.
End;

Execution results:
mysql> SET @p_out =1
Mysql> call Sp_demo_out_parameter (@p_out)
Slightly

Mysql> SELECT @p_out;
Slightly

InOut Parameter Example:
Mysql> CREATE PROCEDURE sp_demo_inout_parameter (inout p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
End;

Execution results:
Set @p_inout =1
Call Sp_demo_inout_parameter (@p_inout)//
Slightly
Select @p_inout;
Slightly

attached: function library
MySQL stored procedure basic functions include: String type, numeric type, date type

One, String class
CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//Returns the position that substring first appeared in string and does not exist return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length characters from the left-hand side of the string2
Length (string)//string lengths
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) with InStr, but you can specify the start position
Lpad (string2, length, pad)//repeat pad at beginning of string until length of string
LTRIM (string2)//Remove front space
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaced with REPLACE_STR in STR search_str
Rpad (string2, length, pad)//after STR supplemented with pad until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//begins with the position of STR, takes the length character,
Note: When handling strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1
mysql> Select substring (' ABCD ', 0,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 0,2) |
+ ——————— –+
| |
+ ——————— –+
1 row in Set (0.00 sec)

mysql> Select substring (' ABCD ', 1,2);
+ ——————— –+
| SUBSTRING (' ABCD ', 1,2) |
+ ——————— –+
| AB |
+ ——————— –+
1 row in Set (0.02 sec)

TRIM ([[both| Leading| Trailing] [padding] from]string2)//Remove the specified character from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Fetch string2 last length character
Space (count)//Generate Count spaces

Ii. Numerical Types

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal Turn binary
CEILING (number2)//Upward rounding
CONV (Number2,from_base,to_base)//in-process conversion
FLOOR (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//Reserved Decimal places
HEX (Decimalnumber)//Hex
Note: HEX () can pass in a string, then return its ASC-11 code, such as HEX (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) Returning 19
Least (number, number2 [,..])//Minimum value
MOD (numerator, denominator)//remainder
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal places]

Note: The return type is not all integers, such as:

(1) Change the default to a cosmetic value
Mysql> Select round (1.23);
+ ————-+
| Round (1.23) |
+ ————-+
| 1 |
+ ————-+
1 row in Set (0.00 sec)

Mysql> Select round (1.56);
+ ————-+
| Round (1.56) |
+ ————-+
| 2 |
+ ————-+
1 row in Set (0.00 sec)

(2) You can set the number of decimal places, return floating-point data

Mysql> Select round (1.567,2);
+ —————-+
| Round (1.567,2) |
+ —————-+
| 1.57 |
+ —————-+
1 row in Set (0.00 sec)

SIGN (NUMBER2)//return symbol, plus or minus 0
SQRT (NUMBER2)//Open Square

three, date type

Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//Current time stamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time in Date2
Date_format (datetime, Formatcodes)//display datetime using Formatcodes format
Date_sub (Date2, INTERVAL d_value d_type)//Minus one time on date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)//days of return date
Dayname (date)/English week
DayOfWeek (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//To extract the specified portion of a date from
Makedate (year, day)//gives the first days of years and years, generating a date string
Maketime (hour, minute, second)//Generate time series
MonthName (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//sec turns into time
Str_to_date (string, format)//String turn to time, displayed in format format
Timediff (datetime1, datetime2)//two time lag
Time_to_sec (time)//times in seconds]
WEEK (Date_time [, Start_of_week])//first few weeks
Year (datetime)/Years
DayOfMonth (DateTime)//day of the month
HOUR (DateTime)//hour
Last date of the month of Last_day (date)//date
Microsecond (datetime)/microsecond
MONTH (DateTime)//month
MINUTE (datetime)/min

Note: Types available in interval: day, Day_hour, Day_minute, Day_second, HOUR, Hour_minute, Hour_second, MINUTE, Minute_second,month, SECOND, year
DECLARE variable_name [, variable_name ...] datatype [DEFAULT value];
Where datatype is the data type for MySQL, such as: INT, FLOAT, DATE, VARCHAR (length)

Cases:

DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric Numeric (8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT ' 1999-12-31 ';
DECLARE l_datetime datetime DEFAULT ' 1999-12-31 23:59:59 ';
DECLARE L_varchar varchar (255) DEFAULT ' This is not to be padded ';

SQL Server Edition Syntax

For the sake of illustration, the database uses the sample database of SQL Server, Northwind and pubs, and if it is not in SQL Server, you can install it in the following way

1, download SQL2000SampleDb.msi, download the address is:

http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

2, after installation, to the default directory C:\SQL Server Databases Sample instnwnd.sql, instpubs.sql two files

3, you can create your Northwind and pubs database by running these two SQL Servers in SQL Server.

Let's start with the grammar of T-SQL

I. Notes

--Single-line comment, from this to the end of the line for comments, similar to c++,c#//

/*/Multiline comments, similar to c++,c#/* ... *

Two. variables (int, smallint, tinyint, decimal,float,real, Money, smallmoney, text, image, char, varchar ...) ）

Grammar:

DECLARE

{

{@local_variable Data_type}

} [,... N]

For example:

DECLARE @ID INT--Declares a variable named @ID, type int

Three. Print out the value of a variable in the SQL Server window

Grammar:

PRINT ' any ASCII text ' | @local_variable | @ @FUNCTION | string_expr

Four. Variable assignment value

For example:

--Remove the ID of the first row of data from the datasheet and assign the value to the variable @id