--如果存在该存储过程则先删除该存储过程
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_byqtj')
BEGIN
PRINT 'Dropping Procedure sp_byqtj'
DROP Procedure sp_byqtj
ENDGO create procedure sp_byqtj
AS
SET NOCOUNT ON
--设置数据参数让我们可以读取没有提交的数据
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--创建临时表保存统计结果
create table #byq_tj
(
num int,
rl int,
wztype varchar(10),
whq varchar(12),
)--从数据库中查询出所有记录插入的临时表中
insert into #byq_tj select Count(*) as num ,rl,wztype,whq from ld_gsbyq where
exists(select null from ADAM_ENTITY_DATAS where TABLE_NAME='LD_GSBYQ' and ld_gsbyq.DATA_GUID = ADAM_ENTITY_DATAS.DATA_GUID)
group by whq,wztype,rl
--对于rl为空的需要做一下处理
insert into #byq_tj select sum(num),2000 ,'null',whq from #byq_tj where rl is null group by whq--将临时表中记录按报表格式输出
declare @sql varchar(2000)
select @sql='select whq as nm'
select @sql=@sql+',[a1]= max(case when rl='+'30'+' and wztype ='+'''杆上'''+'
then num
else '''' end)' select @sql=@sql+',[a2]= max(case when rl='+'50'+' and wztype ='+'''杆上'''+'
then num
else '''' end)' select @sql=@sql+',[a3]= max(case when rl='+'63'+' and wztype ='+'''杆上'''+'
then num
else '''' end)' select @sql=@sql+',[a4]= max(case when rl='+'100'+' and wztype ='+'''杆上'''+'
then num
else '''' end)'select @sql=@sql+',[a5]= max(case when rl='+'30'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'
select @sql=@sql+',[a6]= max(case when rl='+'50'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'select @sql=@sql+',[a7]= max(case when rl='+'63'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'select @sql=@sql+',[a8]= max(case when rl='+'100'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'select @sql=@sql+',[a9]= max(case when rl='+'2000'+'
then num
else '''' end)'select @sql=@sql+' from #byq_tj group by whq '
--创建统计结果中第二部分的临时表
create table #byq_tj1
(
num int,
xh varchar(100),
whq varchar(12),
)--从数据库中查询出所有记录插入的临时表中
insert into #byq_tj1 select Count(*) as num ,xh,whq from ld_gsbyq where
exists(select null from ADAM_ENTITY_DATAS where TABLE_NAME='LD_GSBYQ' and ld_gsbyq.DATA_GUID = ADAM_ENTITY_DATAS.DATA_GUID)
group by whq,xh--创建一个新的临时表处理前一个临时表里的数据
create table #byq_tj2
(
num int,
xh varchar(100),
whq varchar(12),
) --将前一个临时表的数据经过处理后插入到新的临时表中
insert into #byq_tj2 select sum(num),'DJ',whq from #byq_tj1 where xh like '%DJ%' group by whq
insert into #byq_tj2 select sum(num),'D7',whq from #byq_tj1 where xh like '%D7%' group by whq
insert into #byq_tj2 select sum(num),'D9',whq from #byq_tj1 where xh like '%D9%' group by whq
insert into #byq_tj2 select sum(num),'D11',whq from #byq_tj1 where xh like '%D11%' group by whq
insert into #byq_tj2 select sum(num),'DC',whq from #byq_tj1 where xh like '%DC%' group by whq
insert into #byq_tj2 select sum(num),'DY',whq from #byq_tj1 where xh like '%DY%' group by whq
insert into #byq_tj2 select sum(num),'null',whq from #byq_tj1 where xh is null group by whq--将临时表中的记录按报表格式输出
declare @sql2 varchar(2000)
select @sql2='select whq as nm'
select @sql2=@sql2+',[a10]= max(case when xh like '+'''%DJ%'''+'
then num
else '''' end)' select @sql2=@sql2+',[a11]= max(case when xh like '+'''%D7%'''+'
then num
else '''' end)' select @sql2=@sql2+',[a12]= max(case when xh like '+'''%D9%'''+'
then num
else '''' end)' select @sql2=@sql2+',[a13]= max(case when xh like '+'''%D11%'''+'
then num
else '''' end)'select @sql2=@sql2+',[a14]= max(case when xh = '+'''null'''+'
then num
else '''' end)'select @sql2=@sql2+',[a15]= max(case when xh like '+'''%DC%'''+'
then num
else '''' end)'select @sql2=@sql2+',[a16]= max(case when xh like '+'''%DY%'''+'
then num
else '''' end)'select @sql2=@sql2+' from #byq_tj2 group by whq ' --组合两个临时表输出的数据 declare @sql3 varchar(2000)select @sql3 ='select a.nm,A.a1 , A.a2, A.a3,A.a4, A.a5 , A.a6, A.a7,A.a8, A.a9,B.a10,B.a11,B.a12, B.a13, B.a14 ,B.a15,B.a16
from ('+@sql +') A inner join
('+@sql2+') B on A.nm = B.nm'--在两个临时表组合输出的数据基础上中做一个统计declare @sql4 varchar(3000)select @sql4 = 'select case when (GROUPING(TEMP.nm)=1) then '+'''全市'''+
' ELSE ISNULL(nm, '+'''UNKNOWN'''+') END AS nm,
sum(TEMP.a1) as a1 , sum(TEMP.a2) as a2, sum(TEMP.a3) as a3,
sum(TEMP.a4) as a4, sum( TEMP.a5) as a5, sum(TEMP.a6) as a6, sum(TEMP.a7) as a7, sum(TEMP.a8) as a8, sum(TEMP.a9) as a9,
sum(TEMP.a10) as a10 , sum(TEMP.a11) as a11, sum(TEMP.a12) as a12, sum(TEMP.a13) as a13, sum(TEMP.a14) as a14,
sum(TEMP.a15) as a15 ,sum(TEMP.a16) as a16 from ('+@sql3+') TEMP group by TEMP.nm with ROLLUP'--执行最终的SQL语句
print(@sql4)
exec(@sql4)--删除临时表
drop table #byq_tj
drop table #byq_tj1
drop table #byq_tj2
SET NOCOUNT OFF
go
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_byqtj')
BEGIN
PRINT 'Dropping Procedure sp_byqtj'
DROP Procedure sp_byqtj
ENDGO create procedure sp_byqtj
AS
SET NOCOUNT ON
--设置数据参数让我们可以读取没有提交的数据
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--创建临时表保存统计结果
create table #byq_tj
(
num int,
rl int,
wztype varchar(10),
whq varchar(12),
)--从数据库中查询出所有记录插入的临时表中
insert into #byq_tj select Count(*) as num ,rl,wztype,whq from ld_gsbyq where
exists(select null from ADAM_ENTITY_DATAS where TABLE_NAME='LD_GSBYQ' and ld_gsbyq.DATA_GUID = ADAM_ENTITY_DATAS.DATA_GUID)
group by whq,wztype,rl
--对于rl为空的需要做一下处理
insert into #byq_tj select sum(num),2000 ,'null',whq from #byq_tj where rl is null group by whq--将临时表中记录按报表格式输出
declare @sql varchar(2000)
select @sql='select whq as nm'
select @sql=@sql+',[a1]= max(case when rl='+'30'+' and wztype ='+'''杆上'''+'
then num
else '''' end)' select @sql=@sql+',[a2]= max(case when rl='+'50'+' and wztype ='+'''杆上'''+'
then num
else '''' end)' select @sql=@sql+',[a3]= max(case when rl='+'63'+' and wztype ='+'''杆上'''+'
then num
else '''' end)' select @sql=@sql+',[a4]= max(case when rl='+'100'+' and wztype ='+'''杆上'''+'
then num
else '''' end)'select @sql=@sql+',[a5]= max(case when rl='+'30'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'
select @sql=@sql+',[a6]= max(case when rl='+'50'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'select @sql=@sql+',[a7]= max(case when rl='+'63'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'select @sql=@sql+',[a8]= max(case when rl='+'100'+' and wztype ='+'''配电室'''+'
then num
else '''' end)'select @sql=@sql+',[a9]= max(case when rl='+'2000'+'
then num
else '''' end)'select @sql=@sql+' from #byq_tj group by whq '
--创建统计结果中第二部分的临时表
create table #byq_tj1
(
num int,
xh varchar(100),
whq varchar(12),
)--从数据库中查询出所有记录插入的临时表中
insert into #byq_tj1 select Count(*) as num ,xh,whq from ld_gsbyq where
exists(select null from ADAM_ENTITY_DATAS where TABLE_NAME='LD_GSBYQ' and ld_gsbyq.DATA_GUID = ADAM_ENTITY_DATAS.DATA_GUID)
group by whq,xh--创建一个新的临时表处理前一个临时表里的数据
create table #byq_tj2
(
num int,
xh varchar(100),
whq varchar(12),
) --将前一个临时表的数据经过处理后插入到新的临时表中
insert into #byq_tj2 select sum(num),'DJ',whq from #byq_tj1 where xh like '%DJ%' group by whq
insert into #byq_tj2 select sum(num),'D7',whq from #byq_tj1 where xh like '%D7%' group by whq
insert into #byq_tj2 select sum(num),'D9',whq from #byq_tj1 where xh like '%D9%' group by whq
insert into #byq_tj2 select sum(num),'D11',whq from #byq_tj1 where xh like '%D11%' group by whq
insert into #byq_tj2 select sum(num),'DC',whq from #byq_tj1 where xh like '%DC%' group by whq
insert into #byq_tj2 select sum(num),'DY',whq from #byq_tj1 where xh like '%DY%' group by whq
insert into #byq_tj2 select sum(num),'null',whq from #byq_tj1 where xh is null group by whq--将临时表中的记录按报表格式输出
declare @sql2 varchar(2000)
select @sql2='select whq as nm'
select @sql2=@sql2+',[a10]= max(case when xh like '+'''%DJ%'''+'
then num
else '''' end)' select @sql2=@sql2+',[a11]= max(case when xh like '+'''%D7%'''+'
then num
else '''' end)' select @sql2=@sql2+',[a12]= max(case when xh like '+'''%D9%'''+'
then num
else '''' end)' select @sql2=@sql2+',[a13]= max(case when xh like '+'''%D11%'''+'
then num
else '''' end)'select @sql2=@sql2+',[a14]= max(case when xh = '+'''null'''+'
then num
else '''' end)'select @sql2=@sql2+',[a15]= max(case when xh like '+'''%DC%'''+'
then num
else '''' end)'select @sql2=@sql2+',[a16]= max(case when xh like '+'''%DY%'''+'
then num
else '''' end)'select @sql2=@sql2+' from #byq_tj2 group by whq ' --组合两个临时表输出的数据 declare @sql3 varchar(2000)select @sql3 ='select a.nm,A.a1 , A.a2, A.a3,A.a4, A.a5 , A.a6, A.a7,A.a8, A.a9,B.a10,B.a11,B.a12, B.a13, B.a14 ,B.a15,B.a16
from ('+@sql +') A inner join
('+@sql2+') B on A.nm = B.nm'--在两个临时表组合输出的数据基础上中做一个统计declare @sql4 varchar(3000)select @sql4 = 'select case when (GROUPING(TEMP.nm)=1) then '+'''全市'''+
' ELSE ISNULL(nm, '+'''UNKNOWN'''+') END AS nm,
sum(TEMP.a1) as a1 , sum(TEMP.a2) as a2, sum(TEMP.a3) as a3,
sum(TEMP.a4) as a4, sum( TEMP.a5) as a5, sum(TEMP.a6) as a6, sum(TEMP.a7) as a7, sum(TEMP.a8) as a8, sum(TEMP.a9) as a9,
sum(TEMP.a10) as a10 , sum(TEMP.a11) as a11, sum(TEMP.a12) as a12, sum(TEMP.a13) as a13, sum(TEMP.a14) as a14,
sum(TEMP.a15) as a15 ,sum(TEMP.a16) as a16 from ('+@sql3+') TEMP group by TEMP.nm with ROLLUP'--执行最终的SQL语句
print(@sql4)
exec(@sql4)--删除临时表
drop table #byq_tj
drop table #byq_tj1
drop table #byq_tj2
SET NOCOUNT OFF
go
汉口工区0 9 38 64 0 1 2 53 1 4 23 35 102 1 0 0
汉阳工区2 3 17 67 0 2 11 78 2 3 19 38 120 2 0 0
洪山工区1 7 48 61 1 3 7 81 1 4 26 47 131 1 1 0
桥口工区1 13 54 87 0 1 11 50 0 13 34 18 152 0 0 0
武昌工区2 8 55 68 1 2 10 40 0 10 25 33 112 0 1 1
全市 6 40 212 347 2 9 41 302 4 34 127 171 617 4 5 1
只是有些关键字不同而已sqlserver用case,oracle用decode,sqlserver字符串连接用+,Oracle用||,sqlserver建立临时表用#或##,oracle用
CREATE GLOBAL TEMPORARY TABLE admin_work_areaCREATE OR REPLACE PROCEDURE TM_TEST_RESULT(
TSN in varchar2,
TTERMINALID in varchar2,
TTESTRESULT in varchar2,
TRES OUT VARCHAR2) AS
-- DEFECT CODE = ECATE
TNEXTPROC VARCHAR2(30);
BEGIN
TRES:='OK'; IF TTESTRESULT='PASS' THEN --PASS/FAIL
BEGIN
SAJET.SJ_GO(TTERMINALID, TSN, sysdate, TRES, 0);
EXCEPTION
WHEN OTHERS THEN TRES:='INSERT TEST RESULT FAIL';
END;
ELSE
BEGIN
SAJET.SJ_NOGO(TTERMINALID, TSN, 'ECATE', sysdate, TRES, TNEXTPROC, 0);
EXCEPTION
WHEN OTHERS THEN TRES:='INSERT TEST RESULT FAIL';
END;
END IF;
END;
/
create or replace procedure get_teacher_info
(v_tid in number,v_tname in varchar2)
is
tifo_record teacher%rowtype;
begin
select *
into tifo_record
from teacher
where teacher.t_id=v_tid or teacher.t_name=v_tname;
insert into t_info values(tifo_record.t_id,tifo_record.t_name,tifo_record.t_sex,
tifo_record.salary,tifo_record.age);
exception
when no_data_found then null;
when too_many_rows then raise;
end get_teacher_info;