Example 1: Querying a student table in a database through a stored procedure
We know that in SQL, we query a table, which can be queried through the select * from student, what should we write in the stored procedure?
Solution:
Fi
to automatically generate the following code segment:
[Function(Name="dbo.[Customers By City]")]public ISingleResult
We use the following code to call:
ISingleResult
Statement Description: This instance uses the stored procedure to return the customer's CustomerID and City in London.3. Multiple Single result sets with possible shapes
When a stored
SQL stored procedure
I. Concepts, advantages and syntax of SQL stored proceduresBefore learning the program process, let's first understand what a stored procedure is? What adv
returned.Suppose there are the following stored processes (the purpose of this article is not to describe the T-SQL syntax, so the stored process only gives code, not to mention ):
/* SP1 */Create procedure DBO. getuserlistAsSet nocount onBeginSelect * From DBO. [userinfo]EndGo
The preceding
Definition of stored procedures,
Stored Procedure (Stored Procedure) is a set of SQL statements for specific functions. The set is compiled and Stored in the database. You can specify t
need to output in the result, the item must be an output, and if it is only used for outputting parameters, you can use the cursor , and when you use this parameter, you must specify both the varying and the output statements. Example:
12345678
CREATEPROCEDUREorder_tot_amt@o_idint,@p_totintoutputASSELECT@p_tot=sum(Unitprice*Quantity)FROMorderdetails[emailprotected]_idGO
Example
The collector can easily invoke the stored procedure of the database, here is an example to see the specific program.
Call a stored procedure with no return value
using Oracle 's stored procedures as an
):
Add a record to the bankMoney table and query the total amount of all deposits in the table userID = Zhangsan.
Create proc insert_bank @ param1 char (10), @ param2 varchar (20), @ param3 varchar (20), @ param4 int, @ param5 int outputWith encryption --------- encryptionAsInsert bankMoney (id, userID, sex, Money)Values (@ param1, @ param2, @ param3, @ param4)Select @ param5 = sum (Money) from bankMoney where userID = 'hangsan'GoYou can run this stored
Recently, due to work needs, from an applicationProgramExport a CSV data table and then import it to the ms SQL data table of another application environment, because it involves the import of multiple CSV export filesTherefore, a stored procedure is written to give it some flexibility. You can specify the path where the CSV file is
Let's take a look at the knowledge about stored procedures.1. Create a stored procedureCreate procedure stored procedure nameParameter listAsBegin......EndII. Call the stored procedureCall
What is a stored procedure?
Store common or complex tasks in advance with SQL statements and a specified name, when you call the database tutorial to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically c
analyzer to check the SQL statement query plan and evaluate and analyze whether the SQL statement is optimized. Generally, 20% Code It occupies 80% of the resources, and our optimization focuses on these slow points.24. If the in or query is not indexed, use the display statement to specify the index: Select * From personmember (Index = ix_title) Where processid in ('male ', female ')25. Pre-calculate the
procedure
(1 ).Format
Format of MySQL stored PROCEDURE creation: create procedure process name ([process parameter [,...])[Features...] process body
Here is an example:
Note:
(1) Here we need to note that DELIMITER // and DELIMITER; the DELIMITER means DELIMITER, because
course, in the MySQL stored procedure, there must be a name in the loop structure, and everything else is the same.Example 3: Use multiple cursors in the mysql stored procedure
Create a table and insert some test data:Drop table if exists netingcn_proc_test;Create table 'netingcn _ proc_test '('Id' INTEGER (11) not nu
What is a stored procedure?Store common or complex tasks in advance with SQL statements and a specified name, when you call the database tutorial to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically co
Tags: Network A large number of T-SQL Analysis database requires mechanisms exist to saveOne, stored proceduresA stored procedure is a set of T-SQL statements that are compiled in a single execution planStored procedures: Like functions are saved in the database (programmabi
;DECLARE tpass varchar (50) default null;-- Declare the SQL statement corresponding to the cursorDECLARE cur CURSORSelect name, password from netingcn_proc_test;-- The done is set to 1 at the end of the cursor loop.Declare continue handler for not found set done = 1;-- Execute QueryOpen cur;-- Traverses each row of the cursorREPEAT-- Store the information of a row in the corresponding variableFETCH cur INTO tname, tpass;If not done then-- The tname an
data table, we need to create a stored procedure before using it. Its concise syntax is:
Example:
The create proc statement (the complete statement is create procedure) tells SQL SERVER that a stored
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.