分类： Oracle
2012-05-14 10:23:53
sql存储过程的几个例子
例1：
create proc proc_stu
@sname varchar(20),
@pwd varchar(20)
as
select * from ren where and
go
查看结果：proc_stu 'admin','admin'
例2：
下面的存储过程实现用户验证的功能，如果不成功，返回0，成功则返回1.
CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUT
AS
IF EXISTS(SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD)
SELECT @LEGAL = 1
ELSE
SELECT @LEGAL = 0
在程序中调用该存储过程，并根据@LEGAL参数的值判断用户是否合法。
例3：一个高效的数据分页的存储过程 可以轻松应付百万数据
CREATE PROCEDURE pageTest --用于翻页的测试
--需要把排序字段放在第一列
(
@FirstID nvarchar(20)=null, --当前页面里的第一条记录的排序字段的值
@LastID nvarchar(20)=null, --当前页面里的最后一条记录的排序字段的值
@isNext bit=null, --true 1 :下一页；false 0:上一页
@allCount int output, --返回总记录数
@pageSize int output, --返回一页的记录数
@CurPage int --页号（第几页）0：第一页；-1最后一页。
)
AS
if @CurPage=0--表示第一页
begin
--统计总记录数
select @allCount=count(ProductId) from Product_test
set @pageSize=10
--返回第一页的数据
select top 10
ProductId,
ProductName,
Introduction
from Product_test order by ProductId
end
else if @CurPage=-1--表示最后一页
select * from
(select top 10 ProductId,
ProductName,
Introduction
from Product_test order by ProductId desc ) as aa
order by ProductId
else
begin
if @isNext=1
--翻到下一页
select top 10 ProductId,
ProductName,
Introduction
from Product_test where ProductId > @LastID order by ProductId
else
--翻到上一页
select * from
(select top 10 ProductId,
ProductName,
Introduction
from Product_test where ProductId < @FirstID order by ProductId desc) as bb order by ProductId
end
举例如下： drop table test1; drop table test2; create table test1 (a char(4),c char(4)); create table test2 (a char(4),c char(4)); insert into test1 values('aaa','bbb') insert into test1 values('aaa','aaa') insert into test2 values('ccc','aaa') insert into test2 values('aaa','aaa') select 1 as b,* from test1 where a like 'aaa' union select 2 as b,* from test2 where c like 'aaa' order by b 这个就是你那条语句，返回结果为 1 aaa aaa 1 aaa bbb 2 aaa aaa 2 ccc aaa 1，2表示查询出来的记录来源于那一个表格 先在去除重复的范围内选取，举例如下： select 1 as b,* from test1 where a like 'aaa' union select 2 as b,* from test2 where c like 'aaa' and (a+c) not in (select a+c from test1) order by b 返回结果： 1 aaa aaa 1 aaa bbb 2 ccc aaa 具体怎么改你的语句，根据实际情况来。我这个例子简单，就是用(a+c) not in (select a+c from test1)说明一下去除重复的范围。 |
例5. sql server 中 case when then else end 的用法（条件判断语法）
CASE 可能是 SQL 中被误用最多的关键字之一。虽然你可能以前用过这个关键字来创建字段，但是它还具有更多用法。例如，你可以在 WHERE
子句中使用 CASE。
首先让我们看一下 CASE 的语法。在一般的 SELECT 中，其语法如下：
SELECT =
CASE
WHEN
在上面的代码中需要用具体的参数代替尖括号中的内容。下面是一个简单的例子：
USE pubs
GO
SELECT
Title,
'Price Range' =
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END
FROM titles
ORDER BY price
GO
对于informix不能用以上形式，可以如下：
USE pubs
GO
SELECT
Title,
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END
Price Range
FROM titles
ORDER BY price
GO
这是 CASE 的典型用法，但是使用 CASE 其实可以做更多的事情。比方说下面的 GROUP BY 子句中的 CASE：
SELECT 'Number of Titles', Count(*)
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END
GO
你甚至还可以组合这些选项，添加一个 ORDER BY 子句，如下所示：
USE pubs
GO
SELECT
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END AS Range,
Title
FROM titles
GROUP BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END,
Title
ORDER BY
CASE
WHEN price IS NULL THEN 'Unpriced'
WHEN price < 10 THEN 'Bargain'
WHEN price BETWEEN 10 and 20 THEN 'Average'
ELSE 'Gift to impress relatives'
END,
Title
GO
对于informix中形式有所不同，例子
select
case when td_acc1_no[1,3]='610' then get_contrast(td_acc1_no)
else get_contrast(td_td_acct_no)
end sx_acct_no
, sum(td_actu_amt) sx_bal
from dcc_tdacnacn where td_td_acct_no in ('6107111001014107111000050182',
'6107111001014107111000050168')
group by 1
注意，为了在 GROUP BY 块中使用 CASE，查询语句需要在 GROUP BY 块中重复 SELECT 块中的 CASE 块。
除了选择自定义字段之外，在很多情况下 CASE 都非常有用。再深入一步，你还可以得到你以前认为不可能得到的分组排序结果集。
6.同时更新多条记录 （case语法）
drop table T_ABCItem
create table T_ABCItem(F_AutoID int, F_Name varchar(20))
insert into T_ABCItem(F_AutoID,F_Name)values(1,'')
insert into T_ABCItem(F_AutoID,F_Name)values(2,'')
insert into T_ABCItem(F_AutoID,F_Name)values(3,'')
UPDATE T_ABCItem SET F_Name=(case F_AutoID when 1 then '值1' when 2 then '值2' when 3 then '值3' end)
7.SQL Server 从N条记录开始的M条记录语句
1. select top 6 * from KH_Customer WHERE ID NOT IN(SELECT TOP 4 ID FROM KH_Customer)
2. select top 6 *
from KH_Customer
where id> (select max(id) from (select top 4 id from KH_Customer order by id) a)
order by id
两条sql语句都是实现查询从第5条到第10条的记录
但据说 第二条 比较快一点,比如表中有300多万条记录，使用第1条语句执行时间需要9秒多，而用第2条语句3秒多
8.－－－－－－－－－－－－－－
-- ======================================================
--列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息
--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
-- ======================================================
SELECT
(case when a.colorder=1 then d.name else '' end)表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by a.id,a.colorder
