必威官网登录-betway必威官网|体育在线

热门关键词: 必威体育,betway必威官网,必威官网登录,必威app下载

查询表bankMoney的内容的存储过程betway必威官网

作者: 计算机网络  发布:2020-01-10

-------------调换函数---------------------------------------------------------print cast(123 as varchar(20卡塔尔国卡塔尔国+'abc'print convert(varchar(20卡塔尔(قطر‎,123卡塔尔(قطر‎+'abc'print str(123卡塔尔国+'abc'语法使用 CAST:CAST ( expression AS data_type ) 使用 CONVERT:CONVERT (data_type[(length)], expression [, style]卡塔尔参数expression是别的有效的 Microsoft? SQL Server? 表明式。有关越来越多音信,请参见表明式。 data_type指标连串所提供的数据类型,包罗 bigint 和 sql_variant。不可能选拔客户定义的数据类型。有关可用的数据类型的越来越多音讯,请参见数据类型。 lengthnchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。 style日期格式样式,借以将 datetime 或 smalldatetime 数据转变为字符数据;恐怕字符串格式样式,借以将 float、real、money 或 smallmoney 数据调换为字符数据。SQL Server 帮衬使用科威特算法的阿拉伯体制中的数据格式。在表中,左边的两列表示将 datetime 或 smalldatetime 调换为字符数据的 style 值。给 style 值加 100,可拿到富含世纪数位的叁位年份 (yyyy卡塔尔(قطر‎。使用带有 LIKE 子句的 CAST下边包车型地铁演示将 int 列转变为 char(20卡塔尔(قطر‎ 列,以便利用 LIKE 子句。USE pubsGOSELECT title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE '15%' AND type = 'trad_cook'GOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CONVERT(char(20), ytd_sales)LIKE '3%'-----------------case和select语句结合------------------------------------------------------------select 外号=case 列名 when '值' then '新值,哈哈'when '值2'then '作者是值2'else '笔者哪些都以不是' endfrom table综合例子:SELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END, CAST(title AS varchar(25State of Qatar卡塔尔(قطر‎ AS 'Shortened Title',price AS PriceFROM titles WHERE price IS NOT NULL O冠道DEEvoque BY type, price COMPUTE AVG(price卡塔尔(قطر‎ BY type --B. 使用带有轻便 CASE 函数和 CASE 找出函数的 SELECT 语句USE pubsGOSELECT 'Price Category' = CASE WHEN price IS NULL THEN 'Not yet priced' WHEN price 10 THEN 'Very Reasonable Title' WHEN price = 10 and price 20 THEN 'Coffee Table Title' ELSE 'Expensive book!' END, CAST(title AS varchar(20卡塔尔State of Qatar AS 'Shortened Title'FROM titlesO奥德赛DE奇骏 BY priceGO--C. 使用带有 SUBST陆风X8ING 和 SELECT 的 CASE 函数USE pubsSELECT SUBSTSportageING((RT奥迪Q5IM(a.au_fname) + ' '+ RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id, Type = CASE WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business' WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking' WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing' WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology' WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'ENDFROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id---------------------------------------------------------------------------------------goto语句---------------------------------declare@sum int, @count intselect @sum=0, @count=1label_1:select@sum=@sum+@countselect@count=@count+1if @count=5gotolabel_1select流速计的值=@count,和的值=@sum------------------------------------------------------------------某段积攒进程欣赏-----------------------------------------------Create Procedure update_title @title char(20),@title_id varchar(20)='tid111'With encryption,recompileasUpdate titles set Title=@titleWhere title_id=@title_id-----------------------------------------------------------------------------------某段触发器赏识-------------------------------------------------IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR') DROP TRIGGER employee_insupd GOCREATE TRIGGER employee_insupd ON employee FOR INSERT, UPDATE AS DECLARE @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallint SELECT @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id FROM employee e INNER JOIN inserted iON e.emp_id = i.emp_id JOIN jobs j ON j.job_id = i.job_id IF (@job_id = 1) and (@emp_lvl 10)BEGINprint 'Job id 1 expects the defaultlevel of 10.' ROLLBACK TRANSACTION END ELSEIF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)BEGIN print'The level for job_id:'+cast(@job_id as varchar(5))+' should be between '+cast(@min_lvl as varchar(5))+'and '+cast(@max_lvl as varchar(5))ROLLBACK TRANSACTION END /*/////////////////////////////////////////////////////////*/--(c卡塔尔在视图CustomersView上创设一个INSTEAD OF触发器:CREATE T汉兰达IGGE本田CR-V Customers_Update2ON CustomersViewINSTEAD OF UPDATE AS DECLARE @Country nvarchar(15卡塔尔国SET @Country = (SELECT Country FROM Inserted卡塔尔(قطر‎IF @Country = '德文y' BEGIN UPDATE CustomersGer SET CustomersGer.Phone = Inserted.Phone FROM CustomersGer JOIN Inserted ON CustomersGer.CustomerID = Inserted.CustomerID ENDELSEIF @Country = 'Mexico' BEGIN UPDATE CustomersMex SET CustomersMex.Phone = Inserted.Phone FROM CustomersMex JOIN Inserted ON CustomersMex.CustomerID = Inserted.CustomerIDEND-------------------------------------------------------------------create trigger 触发器名 on 视图名 INSTEAD OF insert as print '视图实行了insert操张'--在视图上创建触发器,用instead of------------------------------------------------------------------------sp_helptrigger abc--查看触发器abc的相关音讯-------------------------------------------------------------------------试行存款和储蓄过程的两种办法:exec GetOrderDetails2@enddate='1999-5-30',@startdate='1996-7-1',@country='USA'--或:exec GetOrderDetails2'一九九六-7-1','1997-5-30','USA'----------------------------------------------------------------------------**************游标cursor**********************************************SQL Server补助七种服务器游标类型:(a卡塔尔(قطر‎ static 基本上不监测变化(b卡塔尔(قطر‎dynamic可监测变化(cState of Qatar forward 只好够取后边的多少(d卡塔尔 keyset 介于static 和 dynamic之间---------------------------------------------------------------------DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]----------------------------------------------------------------------------DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]----------定义游标的一个实例------------------------------------------------------------------DECLARE Employee_Cursor CURSOR FOR -----SQL语句SELECT au_lname, au_fname, phone FROM authors WHERE au_lname LIKE 'Ring%'OPEN Employee_Cursor ---打开游标FETCH NEXT FROM Employee_CursorWHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM Employee_Cursor END CLOSE Employee_CursorDEALLOCATE Employee_Cursor --------------------------------------------------------------------FETCH?[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar }?| RELATIVE { n | @nvar }]?FROM]{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]从 Transact-SQL 服务器游标中搜索特定的一整套--------------------------------------------------------------------------declare aa cursor scroll for select * from cursorabc--定义二个能够上下移动的scroll游标open aa --打开游标while @@fetch_status=0--用while循行把全部记录集读收取来fetch next from aafetch absolute 5 from aa --定位到记录集第五行fetch next from aa --定位到下生龙活虎行fetch prior from aa--定位到上大器晚成行fetch first from aa--定位到第生机勃勃行fetch last from aa --定位到结尾风流浪漫行fetch relative -4 from aa--相对后退四条记录if @@FETCH_STATUS =0 print '再次来到0,表明读取记录健康,并从未到结尾一条或第一条记下'elseprint '不是重返0,表达指针到了最后或在最开端,或还未记录集'print '该记录集的总部数为:'+cast(@@cursor_rows as varchar(5卡塔尔(قطر‎State of Qatar--该记录集的分局数close aadeallocate aa----------------------------------------------------------------------------------------------DECLARE @price moneyDECLARE @get_price CURSORSET @get_price = CURSOR FORSELECT price FROM titlesOPEN @get_priceFETCH NEXT FROM @get_price INTO @priceSELECT price FROM titlesWHILE (@@FETCH_STATUS = 0) BEGINIF @Price 20 UPDATE titles SET price = (@price + (@price * .1)) WHERE CURRENT OF @get_priceELSEUPDATE titles SET price = (@price + (@price * .05)) WHERE CURRENT OF @get_priceFETCH NEXT FROM @get_price INTO @priceENDSELECT price FROM titlesCLOSE @get_priceDEALLOCATE @get_price-------------------------------------------------------------------------------------------------SET NOCOUNT ON DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40), @message varchar(80), @title varchar(80) PRINT '---California Authors report ---'DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lname FROM authors WHERE state = 'CA' ORDER BY au_id OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname WHILE @@FETCH_STATUS = 0 BEGIN PRINT '' SELECT @message = '----- Books by Author: ' + @au_fname + ' '

簡單SQL存儲過程實例

摘自:

实例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%'

本文由betway必威官网发布于计算机网络,转载请注明出处:查询表bankMoney的内容的存储过程betway必威官网

关键词: