@sql:= 'select 姓名 ' @sql:= @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']' @sql:=' (select distinct 课程 from tb) as a' @sql = @sql + ' from tb group by 姓名' immediate execute @sql; end
楼上的, plsql 里好像不用 @作为变量的标识吧。
参考sql: declare sql varchar(8000); begin sql := 'select 姓名 '; sql := sql || ' , max(case 课程 when ''' || 课程 || ''' then 分数 else 0 end) [' || 课程 || ']' from (select distinct 课程 from tb) as a'; sql := sql || ' from tb group by 姓名'; execute immediate sql; end;
create or replace procedure tt2 as c_sql varchar(8000); begin c_sql := 'select 姓名 '; c_sql := c_sql || ' , max(case 课程 when ''' || 课程 || ''' then 分数 else 0 end) [' || 课程 || ']'' from (select distinct 课程 from tb) as a'; c_sql := c_sql || ' from tb group by 姓名'; DBMS_OUTPUT.put_line (c_sql); end;发现还是不行,会提示 课程 变量未定义。 哎,现动态SQL里的'''' 号搞死人
课程变量可以通过参数数传入进来 create or replace procedure tt2(course in varchar2) as c_sql varchar(8000); begin c_sql := 'select 姓名 '; c_sql := c_sql || ' , max(case 课程 when ''' || course || ''' then 分数 else 0 end) [' || course || ']'' from (select distinct 课程 from tb) as a'; c_sql := c_sql || ' from tb group by 姓名'; DBMS_OUTPUT.put_line (c_sql); end;
中间的课程是什么?参数?还是???? [SQL code] create or replace procedure tt2 as c_sqlvarchar(8000); begin c_sql :='select 姓名'; c_sql := c_sql||' , max(case 课程 when 数学 then 分数 else 0 end) 数学' from (select distinct 课程 from tb) as a'; c_sql := c_sql||' from tb group by 姓名'; DBMS_OUTPUT.put_line (c_sql); end; [/SQL code]
@sql varchar(8000);
begin
@sql:= 'select 姓名 '
@sql:= @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
@sql:=' (select distinct 课程 from tb) as a'
@sql = @sql + ' from tb group by 姓名'
immediate execute @sql;
end
declare
sql varchar(8000);
begin
sql := 'select 姓名 ';
sql := sql || ' , max(case 课程 when ''' || 课程 || ''' then 分数 else 0 end) [' || 课程 || ']'
from (select distinct 课程 from tb) as a';
sql := sql || ' from tb group by 姓名';
execute immediate sql;
end;
create or replace procedure tt2 as
c_sql varchar(8000);
begin
c_sql := 'select 姓名 ';
c_sql := c_sql || ' , max(case 课程 when ''' || 课程 || ''' then 分数 else 0 end) [' || 课程 || ']''
from (select distinct 课程 from tb) as a';
c_sql := c_sql || ' from tb group by 姓名';
DBMS_OUTPUT.put_line (c_sql);
end;发现还是不行,会提示 课程 变量未定义。
哎,现动态SQL里的'''' 号搞死人
create or replace procedure
tt2(course in varchar2) as
c_sql varchar(8000);
begin
c_sql := 'select 姓名 ';
c_sql := c_sql || ' , max(case 课程 when ''' || course || ''' then 分数 else 0 end) [' || course || ']'' from (select distinct 课程 from tb) as a'; c_sql := c_sql || ' from tb group by 姓名';
DBMS_OUTPUT.put_line (c_sql);
end;
[SQL code]
create or replace procedure tt2
as
c_sqlvarchar(8000);
begin
c_sql :='select 姓名';
c_sql := c_sql||' , max(case 课程 when 数学 then 分数 else 0 end) 数学'
from (select distinct 课程 from tb) as a';
c_sql := c_sql||' from tb group by 姓名';
DBMS_OUTPUT.put_line (c_sql);
end;
[/SQL code]