簡單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£º
Ïà¹ØÎĵµ£º
²é¿´»ØÊÕÕ¾Öбí
select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;
»Ö¸´±í
SQL
>flashback table test_drop to before drop;»ò
SQL
>flashback table "BIN$b+XkkO1RS5K10uKo9BfmuA==$0" to befor ......
Êý¾Ý¿â¿ìÕÕÊÇMSSQL2005µÄй¦ÄÜ£¬½öÔÚ Microsoft SQL Server 2005 Enterprise Edition ÖпÉÓ᣶øÇÒSQL Server Management Studio ²»Ö§³Ö´´½¨Êý¾Ý¿â¿ìÕÕ£¬´´½¨¿ìÕÕµÄΨһ·½Ê½ÊÇʹÓà Transact-SQL¡£
Êý¾Ý¿â¿ìÕÕÊÇÊý¾Ý¿â£¨³ÆΪ“Ô´Êý¾Ý¿â”£©µÄÖ»¶Á¾²Ì¬ÊÓͼ¡£ÔÚ´´½¨Ê±£¬Ã¿¸öÊý¾Ý¿â¿ìÕÕÔÚÊÂÎñÉ϶¼ÓëÔ´Êý¾Ý¿âÒ»Ö ......
SQlÊý¾ÝÀàÐÍ(-_-ѧSQL¾Í´ÓÊý¾ÝÀàÐÍ¿ªÊ¼°É£¡)
bit ÕûÐÍ
ÆäÖµÖ»ÄÜÊÇ0¡¢1»ò¿ÕÖµ¡£
ÕâÖÖÊý¾ÝÀàÐÍÓÃÓÚ´æ´¢Ö»ÓÐÁ½ÖÖ¿ÉÄÜÖµµÄÊý¾Ý£¬ÈçYes »òNo¡¢True »òFalse ¡¢On »òOff.
int ÕûÐÍ 4¸ö×Ö½Ú
´Ó- 2^31(-2147483648)µ½2^31 (2147483 647)Ö®¼äµÄÕûÊý¡£
´æ´¢µ½Êý¾Ý¿âµÄ¼¸ºõËùÓÐÊýÖµÐ͵ÄÊý¾ ......
»ù±¾µÄSql±àдעÒâÊÂÏî
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔÓÃNOT EXISTS»òÕßÍâÁ¬½Ó+Ìæ´ú¡£
OracleÔÚÖ´ÐÐIN×Ó²éѯʱ£¬Ê×ÏÈÖ´ÐÐ×Ó²éѯ£¬½«²éѯ½á¹û·ÅÈëÁÙʱ±íÔÙÖ´ÐÐÖ÷²éѯ¡£¶øEXISTÔòÊÇÊ×Ïȼì²éÖ÷²éѯ£¬È»ºóÔËÐÐ×Ó²éѯֱµ½ÕÒµ½
µÚÒ»¸öÆ¥ÅäÏî¡£NOT EXISTS±ÈNOT INЧÂÊÉ ......