postgresql数据库中,如何写类似sql server中的存储过程 比如 在 sql server中
create proc proc_organ
(
@strWhere varchar(800)
)
as
declare @sql varchar(8000)
set @sql = 'select oid '
select @sql = @sql + ' , max(case tid when ''' + convert(varchar(5),tid) + ''' then score else 0 end) [' + convert(varchar(5),tid) + ']'
from (select distinct tid from (select o.name as oid,t.name as tid,ot.score from organ as o,Typename as t,ot where o.id=ot.oid and t.id=ot.tid) as d) as a
set @sql = @sql + ' from (select o.name as oid,t.name as tid,ot.score from organ as o,Typename as t,ot where o.id=ot.oid and t.id=ot.tid) as c group by oid'
set @sql = 'select oid,'+@strWhere+' from (' + @sql +') as abc '
exec(@sql)exec proc_organ '安全,环保,质量'在postgresql 里面应该如何写呢?
create proc proc_organ
(
@strWhere varchar(800)
)
as
declare @sql varchar(8000)
set @sql = 'select oid '
select @sql = @sql + ' , max(case tid when ''' + convert(varchar(5),tid) + ''' then score else 0 end) [' + convert(varchar(5),tid) + ']'
from (select distinct tid from (select o.name as oid,t.name as tid,ot.score from organ as o,Typename as t,ot where o.id=ot.oid and t.id=ot.tid) as d) as a
set @sql = @sql + ' from (select o.name as oid,t.name as tid,ot.score from organ as o,Typename as t,ot where o.id=ot.oid and t.id=ot.tid) as c group by oid'
set @sql = 'select oid,'+@strWhere+' from (' + @sql +') as abc '
exec(@sql)exec proc_organ '安全,环保,质量'在postgresql 里面应该如何写呢?
有三个张organ,typename,ot表
organ表
id name
1 腾讯
2 盛大
typename表
id name
1 安全
2 环保
3 社会
4 历史
5 质量
ot表关联上两张表
id oid tid score
1 1 1 53
2 1 2 54
3 2 1 56
4 2 2 57
....
想要这样的结果
oid 安全 环保
百度 63 89
盛大 55 56
腾讯 53 54
以下是sql储存过程的写法ALTER proc [dbo].[proc_organ]
(
@strWhere varchar(800)
)
as
declare @sql varchar(8000)
set @sql = 'select oid '
select @sql = @sql + ' , max(case tid when ''' + convert(varchar(5),tid) + ''' then score else 0 end) [' + convert(varchar(5),tid) + ']'
from (select distinct tid from (select o.name as oid,t.name as tid,ot.score from organ as o,Typename as t,ot where o.id=ot.oid and t.id=ot.tid) as d) as a
set @sql = @sql + ' from (select o.name as oid,t.name as tid,ot.score from organ as o,Typename as t,ot where o.id=ot.oid and t.id=ot.tid) as c group by oid'
set @sql = 'select oid,'+@strWhere+' from (' + @sql +') as abc '
exec(@sql)exec proc_organ '安全,环保'
参数传的是typename的name字段
比如传 '安全'
显示
oid 安全
传 '安全,环保'
显示
oid 安全 环保以上sql 储存过程如何转成postgresql的