sql stored procedure summary

1. Overview of Sql stored procedures

In large database systems, stored procedures and triggers play a very important role. Whether it is a stored procedure or a trigger, it is a collection of SQL statements and flow control statements. In essence, a trigger is also a stored procedure. Stored procedures generate execution modes during operation, so they execute very quickly when they are run again later. SQL Server 2000 not only provides the function of user-defined stored procedures, but also provides many system stored procedures that can be used as tools

1.1 Concept

Stored Procedure (Stored Procedure) is a set of SQL to complete specific functions The statement set is compiled and stored in the database. The user executes a stored procedure by specifying its name and giving parameters (if the stored procedure has parameters).

In the series versions of SQL Server, stored procedures are divided into two categories: system-provided stored procedures and user-defined stored procedures. System procedures are mainly stored in the master database and are prefixed with sp_, and system stored procedures mainly obtain information from system tables, thereby providing support for system administrators to manage SQL Server. Through system stored procedures, many administrative or informational activities in MS SQL Server (such as understanding database objects and database information) can be completed smoothly and effectively. Although these system stored procedures are placed in the master database, they can still be called in other databases without adding the database name before the stored procedure name when calling. And when a new database is created, some system stored procedures will be automatically created in the new database. A user-defined stored procedure is a stored procedure created by the user and capable of completing a specific function (such as querying the data information required by the user). The stored procedures involved in this chapter mainly refer to user-defined stored procedures.

1.2 Advantages of Stored Procedures

When creating an application using MS SQL Server, Transaction-SQL is a primary programming language. If you use Transaction-SQL for programming, there are two methods. One is to store the Transaction-SQL program locally and create an application to send commands to SQL Server to process the results. The second is that you can store some programs written in Transaction-SQL as stored procedures in SQL Server, and create applications to call the stored procedures and process the data results. The stored procedures can return the result set to the caller by receiving parameters. The format of the result set is determined by the caller; returns a status value to the caller, indicating whether the call is successful or failed; includes operation statements for the database, and can call another stored procedure in a stored procedure.

We usually prefer to use the second method, which is to use stored procedures in SQL Server rather than calling a program written in Transaction-SQL on the client computer. The reason is that stored procedures have the following advantages:
1) Stored procedures allow Standard component programming

After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Moreover, database professionals can modify the stored procedure at any time, but it will have no impact on the application source code (because the application source code only contains the call statements of the stored procedure), thus greatly improving the portability of the program.

(2) Stored procedures can achieve faster execution speed

If an operation contains a large amount of Transaction-SQL code or is executed multiple times, then the stored procedure will execute much faster than batch processing. Because stored procedures are precompiled, when a stored procedure is run for the first time, the query optimizer analyzes and optimizes it, and gives an execution plan that is ultimately stored in the system table. Batch Transaction-SQL statements must be compiled and optimized each time they are run, so the speed is relatively slower.

(3) Stored procedures can reduce network traffic

For the same operation (such as query, modification) on the database object, if the Transaction-SQL statements involved in this operation are organized into a stored procedure, then when When the stored procedure is called on the client computer, only the calling statement is transmitted over the network, otherwise it will be multiple SQL statements, thus greatly increasing network traffic and reducing network load.

(4) Stored procedures can be fully utilized as a security mechanism

By restricting the permissions to execute a certain stored procedure, the system administrator can limit the corresponding data access permissions and avoid unauthorized users Access to data and ensure data security. (We will give a clearer introduction to this application of stored procedures in Chapter 14 "User and Security Management of SQL Server")

Note: Although stored procedures have both parameters and return values, they are different from functions . The return value of a stored procedure only indicates whether the execution is successful, and it cannot be called directly like a function. That is, when calling a stored procedure, there must be the EXEC reserved word before the stored procedure name.

2. Basic syntax 2.1 Create stored procedure

Create procedure sp_name //sp_name The name you give the stored procedure

Begin

……

End

Create Proc dbo. Stored procedure name
Stored procedure parameters
AS
Execution statement
RETURN
Execute the stored procedure
GO

Eg:

-- The database where the stored procedure is to be created

Use Test

-- Determine whether the stored procedure name to be created exists

if Exists(Select name From sysobjects Where name='csp_AddInfo' And

type='P')

-- Delete stored procedure

Drop Procedure dbo.csp_AddInfo

Go

-- Create stored procedure

Create Proc dbo .csp_AddInfo

- - Stored procedure parameters

@UserName varchar(16),

@Pwd varchar(50),

@Age smallint,

@*** varchar(6)

AS

-- Stored procedure statement body

insert into Uname (UserName,Pwd,Age,***)

values ​​(@UserName,@Pwd,@Age,@***)

RETURN

-- Execute

GO

-- Execute the stored procedure

EXEC csp_AddInfo 'Junn.A','123456',20,'Male'

The name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique to the database and its owner.
　 To create a local temporary procedure, you can add a number character (#procedure_name) in front of procedure_name. To create a global temporary procedure, you can add two number characters (##procedure_name) in front of procedure_name. The complete name (including # or ##) cannot exceed 128 characters. Specifying the name of the process owner is optional.

2.2 Call stored procedure

Call procedure sp-name ()

Note: Parentheses must be added after the stored procedure name, even if the stored procedure has no parameters passed

1) First method: Use output parameters

USE AdventureWorks;

GO

IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL

DROP PROCEDURE Production.usp_GetList;

GO

CREATE PROCEDURE Production. usp_GetList

@product varchar(40)

, @maxprice money

, @compareprice money OUTPUT

, @listprice money OUT

AS

SELECT p.name AS Product, p.ListPrice AS 'List Price'

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.name LIKE @product AND p.ListPrice < @maxprice;

-- Populate the output variable @ listprice.

SET @listprice = (SELECT MAX(p.ListPrice)

FROM Production.Product p

JOIN Production.ProductSubcategory s

ON p.ProductSubcategoryID = s.ProductSubcategoryID

WHERE s.name L IKE @product AND p.ListPrice < @maxprice);

-- Populate the output variable @compareprice.

SET @compareprice = @maxprice;

GO

--When another stored procedure is called:

Create Proc Test

as

DECLARE @compareprice money, @cost money

EXECUTE Production.usp_GetList '%Bikes%', 700,

@compareprice OUT,

@cost OUTPUT

IF @cost < = @compareprice

BEGIN

PRINT 'These products can be purchased for less than

$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'

END

ELSE

PRINT ' The prices for all products in this category exceed

$'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'

2) Second method: Create a temporary table

create proc GetUserName
as
begin
select 'UserName'
end

Create table #tempTable (userName nvarchar(50))
insert into #tempTable(userName)
exec GetUserName

select #tempTable

--run out After that, the temporary table needs to be cleared
drop table #tempTable--It should be noted that this method cannot be nested. For example:

procedure a
begin
...
insert #table exec b
end

procedure b
begin
...
insert #table exec c
select * from #table
end

procedure c
begin
...
select * from sometable
end

--here a adjusts the result set of b, and there is also such an application in b where b adjusts the result set of c. This is not allowed,
--will report " INSERT EXEC statements cannot be nested" error. This type of application should be avoided in actual applications.

3) The third method: declare a variable and execute it with exec(@sql):

2.3 Delete stored procedure,

drop procedure sp_name//

Note: You cannot delete another stored procedure in one stored procedure Procedure, you can only call another stored procedure

2.4 View stored procedure information

1.show procedure status
Displays the basic information of all stored procedures in the database, including the database to which it belongs, the name of the stored procedure, the creation time, etc.

2. show create procedure sp_name
Display detailed information of a mysql stored procedure

2.5 Comments

mysql stored procedures can use two styles of comments
Double horizontal bars: --

This style is generally used for single-line comments
c Style: /* Comment content */ Generally used for multi-line comments

2.6 Loop statement 2.6.1 If

IF condition

BEGIN
Execution statement
END

ELSE

BEGIN
Execution statement
END

IF @d = 1

BEGIN

--Print
PRINT 'Correct'
END
ELSE BEGIN
PRINT 'Wrong'
END

2.6.2 Multi-condition selection statement

Sql Multi-condition selection statement.

DECLARE @iRet INT, @PKDisp VARCHAR(20)
SET @iRet = 1
Select @iRet =
CASE
WHEN @PKDisp = '一' THEN 1
WHEN @PKDisp = ' Two' THEN 2
WHEN @PKDisp = 'three' THEN 3
WHEN @PKDisp = 'four' THEN 4
WHEN @PKDisp = 'five' THEN 5
ELSE 100
END

2.6.3 Loop statement

WHILE Condition BEGIN
Execution statement
END

Eg: CREATE PROCEDURE [dbo].[P_TestWhilr]

as

DECLARE @i INT

SET @i = 1

WHILE @i< ;1000000 BEGIN

set @i=@i+1

END

-- Print

PRINT @i

exec [dbo].[P_TestWhilr]

3. Parameter variables 3.1 Variables and definitions

Custom variables: DECLARE a INT; SET a=100; can be replaced by the following statement: 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 @. Abuse of user variables will make the program difficult to understand and manage

--Declaration of variables. When declaring variables in SQL, you must add the @ symbol in front of the variable
DECLARE @I INT

--Assignment of variables. When assigning variables, set must be added before the variable
SET @I = 30

--Declaration of multiple variables
DECLARE @s varchar(10),@a INT

3.2 Operators 3.2 .1 Arithmetic operator

+ Add SET var1=2+2; 4
- Subtract SET var2=3-2; 1
* Multiply SET var3=3*2; 6
/ Divide SET var4=10/3; 3.3333
DIV Divisible SET var5=10 DIV 3; 3
% Modulo SET var6=10%3; 1

3.2.2 Comparison Operator

> Greater than 1>2 False
< Less than 2<1 False
<= Less than or equal to 2<=2 True
>= Greater than or equal to 3>=2 True
BETWEEN Between two values ​​5 BETWEEN 1 AND 10 True
NOT BETWEEN Not between two values ​​5 NOT BETWEEN 1 AND 10 False
IN is in the set 5 IN (1,2,3,4) False
NOT IN is not in the set 5 NOT IN (1,2,3,4) True
= is equal to 2=3 False
<>, != Not equal to 2<>3 False
<=> Strictly compares whether two NULL values ​​are equal NULL<=>NULL True
LIKE Simple pattern matching "Guy Harrison" LIKE "Guy%" True
REGEXP Regular expression Match "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL is empty 0 IS NULL False
IS NOT NULL is not empty 0 IS NOT NULL True

3. Logical operators

4. Bit operators
| Or
& and
<< left shift
>> right shift
~ not (unary operation, bitwise negation)

4. Input and output (unknown)

mysql stored procedure Parameters are used in the definition of stored procedures. There are three parameter types, IN, OUT, INOUT
Create procedure | function ([[IN |OUT |INOUT ] parameter name data type...])

IN input parameter
represents The value of this parameter must be specified when calling the stored procedure. If the value of this parameter is modified during the stored procedure, it cannot be returned. It is the default value

OUT Output parameter
This value can be changed inside the stored procedure and can be returned

INOUT Input and output parameters
are specified when calling, and can be changed and returned

--Create a stored procedure to find the maximum value

CREATE PROCEDURE [dbo].[P_Max]

@a int, -- input

@b int, -- input

@Returnc int output -- output

AS

if (@a>@b)

set @Returnc =@a

else

set @Returnc =@b

-- Call

declare @Returnc int

exec P_Max 2,3, @Returnc output

select @Returnc

5. Function library

mysql stored procedure basic functions include: string type, Numeric type, date type

5.1 String class

CHARSET(str) //Return the string character set
CONCAT (string2 [,… ]) //Connect the string
INSTR (string, substring) // Returns the position where substring first appears in string. If it does not exist, returns 0
LCASE (string2) //Convert to lowercase
LEFT (string2, length) //Take length characters from the left in string2
LENGTH (string) // String length
LOAD_FILE (file_name) //Read content from the file
LOCATE (substring, string [,start_position]) Same as INSTR, but you can specify the starting position
LPAD (string2, length, pad) //Repeatedly add pad to string From the beginning, until the string length is length
LTRIM (string2) //Remove leading spaces
REPEAT (string2, count) //Repeat count times
REPLACE (str, search_str, replace_str) //Replace search_str with replace_str in str
RPAD (string2, length, pad) //Add pad after str until the length is length
RTRIM (string2) //Remove back-end spaces
STRCMP (string1, string2) //Compare the size of the two strings character by character,
SUBSTRING (str, position [,length]) //Start from the position of str, take length characters,
Note: When processing 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 at the specified position
UCASE (string2) //Convert to uppercase
RIGHT(string2,length) //Get the last length characters of string2
SPACE(count) //Generate count spaces

5.2 Numeric type

ABS (number2) //Absolute value
BIN (decimal_number) //Convert from decimal to binary
CEILING (number2) //Round up
CONV(number2, from_base,to_base) //Hexadecimal conversion
FLOOR (number2) //Round down
FORMAT (number,decimal_places) //Retain the number of decimal places
HEX (DecimalNumber) //Convert to hexadecimal
Note: HEX( ) can be passed in a string, and its ASC-11 code will be returned. For example, HEX('DEF') returns 4142143
You can also pass in a decimal integer and its hexadecimal code will be returned. For example, HEX(25) returns 19
LEAST (number, number2 [,..]) //Find the minimum value
MOD (numerator,denominator) //Find the remainder
POWER (number,power) //Find the exponent
RAND([seed]) //Random number
ROUND (number [,decimals ]) //Rounding, decimals is the number of decimal places]

Note: Not all return types are integers, such as:

(1) defaults to an integer 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 and 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 sign, positive or negative or 0
SQRT(number2) //Square root

5.3 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 Timestamp
DATE (datetime) //Return the date part of datetime
DATE_ADD (date2, INTERVAL d_value d_type) //Add date or time to date2
DATE_FORMAT (datetime,FormatCodes) //Use formatcodes format to display datetime
DATE_SUB ( date2, INTERVAL d_value d_type) //Subtract a time from date2
DATEDIFF (date1, date2) //Difference between two dates
DAY (date) //Return the day of the date
DAYNAME (date) //English weekday
DAYOFWEEK (date) //Weekday (1-7), 1 is Sunday
DAYOFYEAR (date) //The day of the year
EXTRACT (interval_name FROM date) //Extract the specified part of the date from date
MAKEDATE (year , day ) //Given the year and the day of the year, generate a date string
MAKETIME (hour, minute, second) //Generate a time string
MONTHNAME (date) //English month name
NOW () //Current Time
SEC_TO_TIME (seconds) //Convert seconds to time
STR_TO_DATE (string, format) //Convert string to time and display in format format
TIMEDIFF (datetime1, datetime2) //Difference between two times
TIME_TO_SEC (time) / /Time to seconds]
WEEK (date_time [,start_of_week ]) //The number of weeks
YEAR (datetime) //The year
DAYOFMONTH(datetime) //The day of the month
HOUR(datetime) //The hour
LAST_DAY (date) //The last date of the month of date
MICROSECOND(datetime) //Microseconds
MONTH(datetime) //Month
MINUTE(datetime) //Minute

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];
Among them, datatype is the data type of mysql, such as: INT, FLOAT, DATE, VARCHAR(length)

Example:

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 will not be padded';

6. Application of stored procedures

SQL stored procedure paging method:
Stored procedure:
CREATE procedure p_splitpage
@sql nvarchar(4000), --SQL statement to be executed
@page int=1, --Page number to be displayed
@pageSize int, - -Size of each page
@pageCount int=0 out, --Total number of pages
@recordCount int=0 out --Total number of records
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql ,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1) *@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
GO
Content of ASP page
sql = "Select id, c_s_name from tabNews where deleted<>1 Order By id Desc "
page = cint(page_get)
if page_post<>""then
page = cint(page_post)
end if
if not page > 0 then
page = 1
end if
pagesize=20' per page Number of items
set cmd = server.CreateObject("adodb.command")
cmd.ActiveConnection = conn
cmd.CommandType = 4
cmd.CommandText = "p_SplitPage"
cmd.Parameters.Append cmd.CreateParameter("@sql" ,8,1, 4000, sql)
cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page)
cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1 , 4, pageSize)
cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount)
cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, recordCount )
set rs = cmd.Execute
set rs = rs.NextRecordSet
pageCount = cmd.Parameters("@pageCount").value
recordCount = cmd.Parameters("@recordCount").value
if pageCount = 0 then pageCount = 1
if page>pageCount then
response.Redirect("?page="&pageCount)
end if
set rs = cmd.Execute

Cursor

1. Concept

Cursor (Cursor) It allows users to Access the result set returned by SQL Server row by row.

One of the main reasons for using cursors is to convert collection operations into single record processing. After retrieving data from the database using SQL language, the result is placed in an area of ​​memory, and the result is often a collection containing multiple records. The cursor mechanism allows users to access these records row by row in SQL server and display and process these records according to the user's own wishes.

2. Advantages of cursors

From the cursor definition, we can get the following advantages of cursors, which make cursors play an important role in practical applications:
1) Allow the program to perform the same or different operations on each row in the row set returned by the query statement select, and Instead of performing the same operation on the entire set of rows.
2) Provides the ability to delete and update rows in the table based on the cursor position.
3) Cursors actually serve as a bridge between collection-oriented database management systems (RDBMS) and row-oriented programming, allowing these two processing methods to communicate through cursors.

3. The use of cursors

has talked about the advantages of this multi-cursor, now we will personally reveal the mystery of cursors.

The order of using cursors: declare cursor, open cursor, read data, close cursor, delete cursor

CREATE PROCEDURE PK_Test

AS

--Declare a variable

declare @O_ID nvarchar(20)

declare @A_Salary float

--Declare a cursor mycursor,

declare mycursor cursor for select O_ID,A_Salary from AddSalary

--Open the cursor

open mycurs or

--take out from the cursor The data (the number of parameters in the select statement must be the same as the variable name taken out from the cursor) is assigned to the variable we just declared

fetch next from mycursor into @O_ID,@A_Salary

/*//Judgement The status of the cursor

//0 The fetch statement is successful

//-1 The fetch statement fails or the row is not in the result set

//-2 The fetched row does not exist*/

--Loop again and again Read the cursor and get the value of @O_ID, @A_Salary

while (@@fetch_status=0)

--Start reading

begin

--Regard @O_ID, @A_Salary as a known Use as much as you like.

--Shows the value we fetch every time using the cursor

print 'The cursor successfully fetched a piece of data'

print @O_ID

print @A_Salary

--Use the cursor to fetch a record

fetch next from mycursor into @O_ID,@A_Salary

end

--Close cursor

close mycursor

--Deallocate mycursor

deallocate mycursor

GO

Usage of Top

select Top 5 lngWorkID,strWorkName,strExecHumanName,strBeginDate

from worklist where lngExecHumanID= @lngUserID

Get the first 5 rows of the records obtained using sql statements