Home Database Mysql Tutorial 简单sql存储过程实例、储过程实战

简单sql存储过程实例、储过程实战

Jun 07, 2016 pm 04:19 PM
storage Example Actual combat Simple process

实例1:只返回单一记录集的存储过程。 银行存款表(bankMoney)的内容如下 Id userID Sex Money 001 Zhangsan 男 30 002 Wangwu 男 50 003 Zhangsan 男 40 要求1:查询表bankMoney的内容的存储过程 create procedure sp_query_bankMoney as select * from ba

 实例1:只返回单一记录集的存储过程。

银行存款表(bankMoney)的内容如下

Id

userID

Sex

Money

001

Zhangsan

30

002

Wangwu

50

003

Zhangsan

40

要求1:查询表bankMoney的内容的存储过程

create procedure sp_query_bankMoney
as
select * from bankMoney
go
exec sp_query_bankMoney

注* 在使用过程中只需要把中的SQL语句替换为存储过程名,就可以了很方便吧!

实例2(向存储过程中传递参数):

加入一笔记录到表bankMoney,,并查询此表中userID= Zhangsan的所有存款的总金额。

Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
with encryption ---------加密
as
insert bankMoney (id,userID,sex,Money)
Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
go
在SQL Server查询分析器中执行该存储过程的方法是:
declare @total_price int
exec insert_bank '004','Zhangsan','男',100,@total_price output
print '总余额为'+convert(varchar,@total_price)
go

在这里再啰嗦一下存储过程的3种传回值(方便正在看这个例子的朋友不用再去查看语法内容):

1.以Return传回整数

2.以output格式传回参数

3.Recordset

传回值的区别:

output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。

实例3:使用带有复杂 SELECT 语句的简单过程

  下面的存储过程从四个表的联接中返回所有作者(提供了姓名)、出版的书籍以及出版社。该存储过程不使用任何参数。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO

  au_info_all 存储过程可以通过以下方法执行:

EXECUTE au_info_all

实例4:使用带有参数的简单过程

CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO

  au_info 存储过程可以通过以下方法执行:

EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'

实例5:使用带有通配符参数的简单过程

CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO

  au_info2 存储过程可以用多种组合执行。下面只列出了部分组合:

EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

The easiest way to query the hard drive serial number The easiest way to query the hard drive serial number Feb 26, 2024 pm 02:24 PM

The hard disk serial number is an important identifier of the hard disk and is usually used to uniquely identify the hard disk and identify the hardware. In some cases, we may need to query the hard drive serial number, such as when installing an operating system, finding the correct device driver, or performing hard drive repairs. This article will introduce some simple methods to help you check the hard drive serial number. Method 1: Use Windows Command Prompt to open the command prompt. In Windows system, press Win+R keys, enter "cmd" and press Enter key to open the command

Huawei will launch innovative MED storage products next year: rack capacity exceeds 10 PB and power consumption is less than 2 kW Huawei will launch innovative MED storage products next year: rack capacity exceeds 10 PB and power consumption is less than 2 kW Mar 07, 2024 pm 10:43 PM

This website reported on March 7 that Dr. Zhou Yuefeng, President of Huawei's Data Storage Product Line, recently attended the MWC2024 conference and specifically demonstrated the new generation OceanStorArctic magnetoelectric storage solution designed for warm data (WarmData) and cold data (ColdData). Zhou Yuefeng, President of Huawei's data storage product line, released a series of innovative solutions. Image source: Huawei's official press release attached to this site is as follows: The cost of this solution is 20% lower than that of magnetic tape, and its power consumption is 90% lower than that of hard disks. According to foreign technology media blocksandfiles, a Huawei spokesperson also revealed information about the magnetoelectric storage solution: Huawei's magnetoelectronic disk (MED) is a major innovation in magnetic storage media. First generation ME

How to write a simple student performance report generator using Java? How to write a simple student performance report generator using Java? Nov 03, 2023 pm 02:57 PM

How to write a simple student performance report generator using Java? Student Performance Report Generator is a tool that helps teachers or educators quickly generate student performance reports. This article will introduce how to use Java to write a simple student performance report generator. First, we need to define the student object and student grade object. The student object contains basic information such as the student's name and student number, while the student score object contains information such as the student's subject scores and average grade. The following is the definition of a simple student object: public

PHP Practical: Code Example to Quickly Implement Fibonacci Sequence PHP Practical: Code Example to Quickly Implement Fibonacci Sequence Mar 20, 2024 pm 02:24 PM

PHP Practice: Code Example to Quickly Implement the Fibonacci Sequence The Fibonacci Sequence is a very interesting and common sequence in mathematics. It is defined as follows: the first and second numbers are 0 and 1, and from the third Starting with numbers, each number is the sum of the previous two numbers. The first few numbers in the Fibonacci sequence are 0,1,1.2,3,5,8,13,21,...and so on. In PHP, we can generate the Fibonacci sequence through recursion and iteration. Below we will show these two

Git installation process on Ubuntu Git installation process on Ubuntu Mar 20, 2024 pm 04:51 PM

Git is a fast, reliable, and adaptable distributed version control system. It is designed to support distributed, non-linear workflows, making it ideal for software development teams of all sizes. Each Git working directory is an independent repository with a complete history of all changes and the ability to track versions even without network access or a central server. GitHub is a Git repository hosted on the cloud that provides all the features of distributed revision control. GitHub is a Git repository hosted on the cloud. Unlike Git which is a CLI tool, GitHub has a web-based graphical user interface. It is used for version control, which involves collaborating with other developers and tracking changes to scripts and

How to write a simple music recommendation system in C++? How to write a simple music recommendation system in C++? Nov 03, 2023 pm 06:45 PM

How to write a simple music recommendation system in C++? Introduction: Music recommendation system is a research hotspot in modern information technology. It can recommend songs to users based on their music preferences and behavioral habits. This article will introduce how to use C++ to write a simple music recommendation system. 1. Collect user data First, we need to collect user music preference data. Users' preferences for different types of music can be obtained through online surveys, questionnaires, etc. Save data in a text file or database

How to correctly use sessionStorage to protect sensitive data How to correctly use sessionStorage to protect sensitive data Jan 13, 2024 am 11:54 AM

How to correctly use sessionStorage to store sensitive information requires specific code examples. Whether in web development or mobile application development, we often need to store and process sensitive information, such as user login credentials, ID numbers, etc. In front-end development, using sessionStorage is a common storage solution. However, since sessionStorage is browser-based storage, some security issues need to be paid attention to to ensure that the stored sensitive information is not maliciously accessed and used.

Golang Practical Combat: Sharing of Implementation Tips for Data Export Function Golang Practical Combat: Sharing of Implementation Tips for Data Export Function Feb 29, 2024 am 09:00 AM

The data export function is a very common requirement in actual development, especially in scenarios such as back-end management systems or data report export. This article will take the Golang language as an example to share the implementation skills of the data export function and give specific code examples. 1. Environment preparation Before starting, make sure you have installed the Golang environment and are familiar with the basic syntax and operations of Golang. In addition, in order to implement the data export function, you may need to use a third-party library, such as github.com/360EntSec

See all articles