oracle交叉表查询存储过程不能编译

Question

自己写的oracle交叉表查询存储过程，不能编译成功，请大虾们指教！
 1.
   CREATE TABLE Try (
NameId varchar2(20),
NewName varchar2(20),
Years varchar2(20),
Counts number(3) NULL 
       ); 2.
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_1','A','2001',1);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_2','B','2001',1);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_3','C','2002',1);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_4','D','2003',1);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_5','E','2004',1);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_1','A','2001',2);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_2','B','2000',3);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_1','A','2001',5);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_3','C','2000',2);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_4','D','2003',6);
        INSERT INTO  try   (  NameId,NewName,Years,Counts  ) values ('x_5','E','2004',1); 3. 表中数据
              NameId   NewName   Years   Counts
    x_1 A 2001 1
x_2 B 2001 1
x_3 C 2002 1
x_4 D 2003 1
x_5 E 2004 1
x_1 A 2001 2
x_2 B 2000 3
x_1 A 2001 5
x_3 C 2000 2
x_4 D 2003 6
x_5 E 2004 1
 4.实现目的
      
1) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
"from try group by newname",'try'
                
        
     类别\年份 2000    2001   2002    2003    2004                 
A 0 8 0 0 0
B 3 1 0 0 0
C 2 0 1 0 0
D 0 0 0 7 0
E 0 0 0 0 2


2) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",sum(counts) as ' 合 计 ' from try group by newname",'try'
                
        
     类别\年份 2000    2001   2002    2003    2004     合  计                 
A 0 8 0 0 0   8
B 3 1 0 0 0   4
C 2 0 1 0 0   3
D 0 0 0 7 0   7
E 0 0 0 0 2   2


3) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",Count(counts) as ' 计 数 ' from try group by newname",'try'
                
        
     类别\年份 2000    2001   2002    2003    2004      计 数                 
A 0 8 0 0 0   3
B 3 1 0 0 0   2
C 2 0 1 0 0   2
D 0 0 0 7 0   2
E 0 0 0 0 2   2


4) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",sum(counts) as ' 合  计 ' from try group by newname",'try'
                
        
     类别\年份 2000   2001    2002    2003    2004      合  计                 
A 0 8 0 0 0   8
B 3 1 0 0 0   4
C 2 0 1 0 0   3
D 0 0 0 7 0   7
E 0 0 0 0 2   2
5) MyCrossTable "newname as '类 别'" ,'years','counts', 
",Count(counts) as ' 计  数 ' from try group by newname,Nameid",
'try',"Nameid as '编 号'," ,",SUM(counts) as '合  计'"                  编 号 类 别  合  计  2000    2001   2002    2003    2003   计 数
                   x_1    A 8 0 8 0 0 0 3
                   x_2    B 4 3 1 0 0 0 2
                   x_3    C 3 2 0 1 0 0 2
                   x_4    D 7 0 0 0 7 0 2
                   x_5    E 2 0 0 0 0 2 2


6) MyCrossTable "newname as '类 别'" ,'years','counts', 
",Count(counts) as ' 计  数 ' from try group by newname,Nameid",
'try','',",SUM(counts) as '合  计'"           类 别 合 计  2000   2001   2002    2003    2003   计 数
              A    8  0 8 0 0 0 3
              B    4  3 1 0 0 0 2
              C    3  2 0 1 0 0 2
              D    7  0 0 0 7 0 2
              E    2  0 0 0 0 2 2

        
        7) MyCrossTable "Years as '年份\类别'" ,'NewName','counts', 
"from try group by Years",'try'

     年份\类别 A       B       C       D       E
2000 0 3 2 0 0
2001 8 1 0 0 0
2002 0 0 1 0 0
2003 0 0 0 7 0
2004 0 0 0 0 2


8) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",sum(counts) as ' 合  计 ' from try group by newname",'try','','','null'
      (设置第 8 个参数,此参数只能为 null 或 0,默认为 0)          
        
     类别\年份 2000   2001     2002     2003    2004    合  计                 
A NULL 8 NULL NULL NULL   8
B 3 1 NULL NULL NULL   4
C 2 NULL 1 NULL NULL   3
D NULL NULL NULL 7 NULL   7
E NULL NULL NULL NULL 2   2
 5.存储过程
     create or replace PROCEDURE MyCrossTable (
       FirstColumn in varchar2(30),
       NewColumn in varchar2(30), 
       SumColumn  in varchar2(30),
       Other in varchar2(300),
                               TableName in varchar2(30), 
                               ZeroColumn  in varchar2(30) := '', 
                               SecondColumn  in varchar2(30) := '',
                               Empty in varchar2(4) := '0'
                              )  
AS

SelectSring  varchar2(4000);

ColumnName varchar2(4000);

Datas  varchar2(100);

DistinctStr varchar2(100); TYPE Cur IS REF CURSOR;                abc Cur;
 
begin
DistinctStr := 'SELECT DISTINCT ' ||  NewColumn || ' FROM ' || TableName || ' ORDER BY ' || NewColumn;

OPEN abc FOR DistinctStr;

LOOP
FETCH abc INTO Datas;
EXIT WHEN abc%NOTFOUND; ColumnName = ColumnName || ',sum(case ' || NewColumn || ' when ''' || Datas || ''' then '                                      || SumColumn || ' else ' || Empty || ' end) as ''' || Datas || ''';    
END LOOP;
                
                CLOSE abc;
  
        SelectSring = 'select ' || ZeroColumn || ' ' || FirstColumn || ' ' || SecondColumn || ' ' || ColumnName                               || ' ' || Other;

execute immediate SelectSring;

end MyCrossTable;
 6.2.  参数说明

1）@FirstColumn   varchar(30) 
指定为行标头的列名

2) @NewColumn    varchar(30)
指定为列标头的列名

3) @SumColumn    varchar(30)
被计算的列的列名，此列必须为数字类型字段

4) @TableName   varchar(30)
表名

5) @Other         varchar(100)
结尾部分的 Select 语句

6) @ZeroColumn   varchar(30)
是否选择第 0 列，默认为 ''.

7) @SecondColumn  varchar(30)
是否选择第 2 列，默认为 ''.

8) @Empty   varchar(4)
在null 值时如何被填充，默认为 0.

Answer 1

这是叉表查询存储过程的 sql server 版本，无任何问题自己写的 Sql Server 通用交叉表存储过程


1. 存储过程代码

CREATE PROCEDURE MyCrossTable (
       @FirstColumn varchar(30),
       @NewColumn varchar(30), 
       @SumColumn  varchar(30),
       @Other varchar(300),
                               @TableName varchar(30), 
                               @ZeroColumn  varchar(30) = '', 
                               @SecondColumn  varchar(30) = '',
                               @Empty varchar(4) = '0'
                              )  
        AS

DECLARE @SelectSring  varchar(8000)

DECLARE @ColumnName varchar(8000)

DECLARE @Datas  varchar(100)

DECLARE @DistinctStr varchar(100)

set @DistinctStr = 'select DISTINCT ' +  @NewColumn + ' FROM '  + @TableName  + ' order by ' + @NewColumn

set @ColumnName = ''

EXEC ('DECLARE  abc  CURSOR  For ' +  @DistinctStr)

OPEN  abc

FETCH NEXT FROM abc into @Datas 
while @@fetch_status = 0

begin
set @ColumnName = @ColumnName + ",sum(case "+ @NewColumn + " when '" +  @Datas  + "' then "  + @SumColumn + " else " + @Empty + " end) as '" + @Datas + "'"  
 
fetch next from abc into @Datas 
end

close abc
deallocate abc

set @SelectSring = 'select ' + @ZeroColumn + ' ' + @FirstColumn + ' ' + @SecondColumn + ' '  + @ColumnName + ' ' + @Other

exec (@SelectSring)
GO


2.  参数说明

1）@FirstColumn   varchar(30) 
指定为行标头的列名

2) @NewColumn    varchar(30)
指定为列标头的列名

3) @SumColumn    varchar(30)
被计算的列的列名，此列必须为数字类型字段

4) @TableName   varchar(30)
表名

5) @Other         varchar(100)
结尾部分的 Select 语句

6) @ZeroColumn   varchar(30)
是否选择第 0 列，默认为 ''.

7) @SecondColumn  varchar(30)
是否选择第 2 列，默认为 ''.

8) @Empty   varchar(4)
在null 值时如何被填充，默认为 0.


3. 执行

1)在查询分析器里运行
  
--删表
--在查询分析器里运行：
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Try')
BEGIN
PRINT 'Dropping Table Try'
DROP TABLE Try
END    
 
GO


--建表：
--在查询分析器里运行：

CREATE TABLE [Try] (
[NameId] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[NewName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Years] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Counts] [int] NULL 
       )

GO

INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_1','A','2001',1)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_2','B','2001',1)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_3','C','2002',1)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_4','D','2003',1)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_5','E','2004',1)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_1','A','2001',2)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_2','B','2000',3)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_1','A','2001',5)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_3','C','2000',2)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_4','D','2003',6)
INSERT INTO [try] ([NameId],[NewName],[Years],[Counts]) values ('x_5','E','2004',1)

Go


2)在Sql Server 中运行

将存储过程代码复制到 Sql Server 中 


4. 例表数据
   
      NameId  NewName  Years  Counts
    x_1 A 2001 1
x_2 B 2001 1
x_3 C 2002 1
x_4 D 2003 1
x_5 E 2004 1
x_1 A 2001 2
x_2 B 2000 3
x_1 A 2001 5
x_3 C 2000 2
x_4 D 2003 6
x_5 E 2004 1


5. 执行结果 (在查询分析器里运行)

1) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
"from try group by newname",'try'
                
        
     类别\年份 2000    2001   2002    2003    2004                 
A 0 8 0 0 0
B 3 1 0 0 0
C 2 0 1 0 0
D 0 0 0 7 0
E 0 0 0 0 2


2) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",sum(counts) as ' 合 计 ' from try group by newname",'try'
                
        
     类别\年份 2000    2001   2002    2003    2004     合  计                 
A 0 8 0 0 0   8
B 3 1 0 0 0   4
C 2 0 1 0 0   3
D 0 0 0 7 0   7
E 0 0 0 0 2   2


3) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",Count(counts) as ' 计 数 ' from try group by newname",'try'
                
        
     类别\年份 2000    2001   2002    2003    2004      计 数                 
A 0 8 0 0 0   3
B 3 1 0 0 0   2
C 2 0 1 0 0   2
D 0 0 0 7 0   2
E 0 0 0 0 2   2


4) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",sum(counts) as ' 合  计 ' from try group by newname",'try'
                
        
     类别\年份 2000   2001    2002    2003    2004      合  计                 
A 0 8 0 0 0   8
B 3 1 0 0 0   4
C 2 0 1 0 0   3
D 0 0 0 7 0   7
E 0 0 0 0 2   2
5) MyCrossTable "newname as '类 别'" ,'years','counts', 
",Count(counts) as ' 计  数 ' from try group by newname,Nameid",
'try',"Nameid as '编 号'," ,",SUM(counts) as '合  计'"                  编 号 类 别  合  计  2000    2001   2002    2003    2003   计 数
                   x_1    A 8 0 8 0 0 0 3
                   x_2    B 4 3 1 0 0 0 2
                   x_3    C 3 2 0 1 0 0 2
                   x_4    D 7 0 0 0 7 0 2
                   x_5    E 2 0 0 0 0 2 2


6) MyCrossTable "newname as '类 别'" ,'years','counts', 
",Count(counts) as ' 计  数 ' from try group by newname,Nameid",
'try','',",SUM(counts) as '合  计'"           类 别 合 计  2000   2001   2002    2003    2003   计 数
              A    8  0 8 0 0 0 3
              B    4  3 1 0 0 0 2
              C    3  2 0 1 0 0 2
              D    7  0 0 0 7 0 2
              E    2  0 0 0 0 2 2

        
        7) MyCrossTable "Years as '年份\类别'" ,'NewName','counts', 
"from try group by Years",'try'

     年份\类别 A       B       C       D       E
2000 0 3 2 0 0
2001 8 1 0 0 0
2002 0 0 1 0 0
2003 0 0 0 7 0
2004 0 0 0 0 2


8) MyCrossTable "newname as '类别\年份'" ,'years','counts', 
",sum(counts) as ' 合  计 ' from try group by newname",'try','','','null'
      (设置第 8 个参数,此参数只能为 null 或 0,默认为 0)          
        
     类别\年份 2000   2001     2002     2003    2004    合  计                 
A NULL 8 NULL NULL NULL   8
B 3 1 NULL NULL NULL   4
C 2 NULL 1 NULL NULL   3
D NULL NULL NULL 7 NULL   7
E NULL NULL NULL NULL 2   2

6.备注 select NewName,sum(case  Years  when '2000' 
then Counts else 0 end) as '2000',sum(case  Years  when '2001' 
then Counts else 0 end) as '2001',sum(case  Years  when '2002' 
then Counts else 0 end) as '2002',sum(case  Years  when '2003' 
then Counts else 0 end) as '2003',sum(case  Years  when '2004' 
then Counts else 0 end) as '2004'from try group by NewName
      

Answer 2

EXIT WHEN abc%NOTFOUND; ColumnName = ColumnName || ',sum(case ' || ---------
EXIT WHEN abc%NOTFOUND; ColumnName :=' ColumnName || ',sum(case ' ||

Answer 3

上面的不对，是这句有问题
ColumnName = ColumnName || ',sum(case ' || NewColumn || ' when ''' || Datas || ''' then '                                      || SumColumn || ' else ' || Empty || ' end) as ''' || Datas || ''';

Answer 4

已能编译
create or replace PROCEDURE MyCrossTable (
  FirstColumn in varchar2,
  NewColumn in varchar2, 
SumColumn in varchar2,
  Other in varchar2,
  TableName in varchar2, 
ZeroColumn in varchar2 default '', 
  SecondColumn in varchar2 default '',
  Empty in varchar2 default '0'
  ) 
 AS

SelectSring  varchar2(4000);

ColumnName varchar2(4000);

Datas  varchar2(100);

DistinctStr varchar2(100); TYPE Cur IS REF CURSOR;                abc Cur;
 
begin
DistinctStr := 'SELECT DISTINCT ' ||  NewColumn || ' FROM ' || TableName || ' ORDER BY ' || NewColumn;

OPEN abc FOR DistinctStr;

LOOP
FETCH abc INTO Datas;
EXIT WHEN abc%NOTFOUND; ColumnName := ColumnName || ',sum(case ' || NewColumn || ' when ''' || Datas || ''' then '                                      || SumColumn || ' else ' || Empty || ' end) as ''' || Datas || '''';    END LOOP;
                
                CLOSE abc;
  
        SelectSring := 'select ' || ZeroColumn || ' ' || FirstColumn || ' ' || SecondColumn || ' ' || ColumnName                               || ' ' || Other;

execute immediate SelectSring;

end MyCrossTable;
但不能执行
     exec MyCrossTable 'newname' ,'years','counts', 
',sum(counts) ' from try group by newname','try'
如下语句也不能执行
    select NewName,sum(case  Years  when '2000' 
then Counts else 0 end) as '2000',sum(case  Years  when '2001' 
then Counts else 0 end) as '2001',sum(case  Years  when '2002' 
then Counts else 0 end) as '2002',sum(case  Years  when '2003' 
then Counts else 0 end) as '2003',sum(case  Years  when '2004' 
then Counts else 0 end) as '2004'from try group by NewName

Answer 5

exec MyCrossTable 'newname' ,'years','counts', 
',sum(counts) ' from try group by newname','try'
参数个数都不对，而且也少引号

Answer 6

在 sqlserver 里 
exec MyCrossTable 'newname' ,'years','counts', 
',sum(counts)  from try group by newname','try' 是可以执行de

Answer 7

在sqlplus中执行
SQL> exec MyCrossTable 'newname' ,'years','counts', ',sum(counts) from try group by newname','try';
BEGIN MyCrossTable 'newname' ,'years','counts', ',sum(counts) from try group by newname','try'; END;                   *
ERROR 位于第 1 行:
ORA-06550: 第 1 行, 第 20 列:
PLS-00103: 出现符号 "newname"在需要下列之一时：
:= . ( @ % ;
符号 "(" 被替换为 "newname" 后继续。
ORA-06550: 第 1 行, 第 95 列:
PLS-00103: 出现符号 ";"在需要下列之一时：
. ( ) , * @ % & | = - + < / >
at in is mod not range rem => .. <an exponent (**)>
<> or != or ~= >= <= <> and or like between ||
符号 ")" 被替换为 ";" 后继续。

Answer 8

exec MyCrossTable ('newname' ,'years','counts', ',sum(counts) from try group by newname','try');

Answer 9

    真的感谢 waterfirer(水清)!!!......    在sqlplus中能正常编译及执行,但存储过程中没有dbms_output.put_line语句,但不知结果如何.
正在用下载PLSql进行调试.

Answer 10

执行 execute immediate SelectSring; 语句后,结果怎么不在 sqlplus 中显示,在sqlplus 中执行语句 select NewName,sum(case  Years  when '2000'
then Counts else 0 end) as "2000",sum(case  Years  when '2001' 
then Counts else 0 end) as "2001",sum(case  Years  when '2002' 
then Counts else 0 end) as "2002",sum(case  Years  when '2003' 
then Counts else 0 end) as "2003",sum(case  Years  when '2004' 
then Counts else 0 end) as "2004" from try group by NewName
可以显示结果. 如果要在sqlplus 中显示结果,怎样写dbms_output.put_line语句?
                 
                                           谢!.....