簡單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£º
Ïà¹ØÎĵµ£º
»úÆ÷Çé¿ö
p4: 2.4
ÄÚ´æ: 1 G
os: windows 2003
Êý¾Ý¿â: ms sql server 2000
Ä¿µÄ: ²éѯÐÔÄܲâÊÔ,±È½ÏÁ½ÖÖ²éѯµÄÐÔÄÜ
SQL²éѯЧÂÊ step by step
-- setp 1.
-- ½¨±í
create table t_userinfo
(
userid int identity(1,1) primary key nonclustered,
nick varchar(50) not null default '',
classid int not nul ......
ÒÔÏÂÊÇÕª×ÔOracle¹ÙÍø:
¢ñ Oracle SQL Developer ÊÇÒ»¸öÃâ·ÑµÄͼÐλ¯Êý¾Ý¿â¿ª·¢¹¤¾ß¡£Ê¹Óà SQL Developer£¬Äú¿ÉÒÔä¯ÀÀÊý¾Ý¿â¶ÔÏó¡¢ÔËÐÐ SQL Óï¾äºÍ SQL ½Å±¾£¬²¢ÇÒ»¹¿ÉÒԱ༺͵÷ÊÔ PL/SQL Óï¾ä¡£Äú»¹¿ÉÒÔÔËÐÐËùÌṩµÄÈκÎÊýÁ¿µÄ±¨±í£¬ÒÔ¼°´´½¨ºÍ±£´æÄú×Ô¼ºµÄ±¨±í¡£SQL Developer ¿ÉÒÔÌá¸ß¹¤×÷ЧÂʲ¢¼ò»¯Êý¾Ý¿â¿ª·¢ÈÎÎñ¡£ ......
¹¤×÷ÖÐÅöµ½µÄС¼¼ÇÉ£¬±ÈÈç˵ҪÓÃÊý¾Ý±íÀïÈ¡³ö×¢²áÓû§µÄ×ÊÁÏ£¬ÐÔ±ðΪ1ÔòÏÔʾΪÄÐÉú£»ÐÔ±ðΪ2ÔòÏÔʾΪŮÉú£»ÒÔÇ°¶¼ÊÇ´Ó±íÀïÈ¡³öÊý¾Ý£¬È»ºóÔÙÓôúÂë½øÐÐÅжϣ¬Îª1ÔòÏÔʾΪÄÐÉú£»Îª2ÔòÏÔʾΪŮÉú£»Æäʵµ¥ÓÃSQLÓï¾ä¾Í¿ÉÒÔ×öµ½£¬ÈçÏ£º
Ö±½ÓʹÓÃcase when else Óï¾ä
Case¾ßÓÐÁ½ÖÖ¸ñʽ¡£¼òµ¥Caseº¯ÊýºÍCaseË ......
½ñÌìÓÃtime Like '2008-06-01%'Óï¾äÀ´²éѯ¸ÃÌìµÄËùÓÐÊý¾Ý£¬±»ÌáʾÓï¾ä´íÎó¡£²éÁËһϲŷ¢ÏÖ¸ÃÄ£ºý²éѯֻÄÜÓÃÓÚStringÀàÐ͵Ä×ֶΡ£
×Ô¼ºÒ²²éÔÄÁËһЩ×ÊÁÏ¡£¹ØÓÚʱ¼äµÄÄ£ºý²éѯÓÐÒÔÏÂÈýÖÖ·½·¨£º
1.Convertת³ÉString,ÔÚÓÃLike²éѯ¡£
select * from table1 where c ......
PL/SQL×î²îʵ¼ù
http://benben.javaeye.com/blog/278164
1. ³¬³¤µÄPL/SQL´úÂë
Ó°Ï죺¿Éά»¤ÐÔ£¬ÐÔÄÜ
Ö¢×´£º
ÔÚ¸´ ......