oracle 行转列的通用过程

经常遇到发帖求行列转换的代码，用max(decode(..))回复后，十有会再问一句：如果列名不固定，或者列数不固定怎么办。就要用存储过程来写，这些存储过程的代码都大同小异，我就想能不能写个通用点的过程

试了一下，把结果发出来

SQL code

create or replace procedure proc(tabname in varchar2, col1 in varchar2, col2 in varchar2, col3 in varchar2,

viewname in varchar2 default 'v_tmp') as

sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' '; c1 sys_refcursor; v1 varchar2(100); begin

open c1 for 'select distinct to_char('||col2||') from '||tabname; loop

fetch c1 into v1;

exit when c1%notfound; sqlstr:=sqlstr||'

,max(decode('||col2||','''||v1||''','||col3||'))\"'||v1||'\"'; end loop; close c1;

sqlstr:=sqlstr||' from '||tabname||' group by '||col1; execute immediate sqlstr; end proc;

这里的几个参数，tabname指的是需要进行行列转换的表名，col1是这个表中行列转换以后要根据哪一列进行分组，那一列的列名。col2传入的是要将行转成列的那一列的列名，col3表示需要进行统计的数据列的列名

viewname传入希望建立的视图的名称，可以不填，默认为v_tmp 这么说很难让人明白..举个例子，引用一个帖子的数据

create table tab (

counter varchar(20), -- 参加考试人数 subject varchar(20), -- 科目 class varchar(20) -- 班级 )

表数据：

counter subject class

36 英语 一班 44 英语 二班 44 数学 二班 33 语文 一班 39 语文 三班

转换后：

一班 二班 三班 英语 36 44 0

数学 0 44 0 语文 33 0 39

编译好过程后，执行

SQL code begin

proc('tab','subject','class','counter'); end;

--结果

select * from v_tmp;

SUBJECT 一班 三班 二班 数学 44 英语 36 44 语文 33 39

如果对这个结果不是很满意，需要自己进行一些修改，比如空值的地方用0代替，或者需要用别的函数聚合而不是max。可以将过程中的execute immediate那句改成

dbms_output.put_line(sqlstr); 重新编译，执行，输出代码

如果用的是pl/sql dev的sql窗口，到output窗口查看

SQL code

--看到生成的代码

create or replace view v_tmp as select subject ,max(decode(class,'一班',counter))\"一班\" ,max(decode(class,'三班',counter))\"三班\"

,max(decode(class,'二班',counter))\"二班\" from tab group by subject

再加入nvl()，达到修改的目的

如果不想创建这样一个过程，则改成匿名块，需要时运行

SQL code declare

tabname varchar2(20):='XXX';--'XXX'分别用相应的表名和字段名代替 col1 varchar2(10):='XXX'; col2 varchar2(10):='XXX'; col3 varchar2(10):='XXX';

viewname in varchar2(10):= 'v_tmp';

sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' '; c1 sys_refcursor; v1 varchar2(100); begin

open c1 for 'select distinct to_char('||col2||') from '||tabname; loop

fetch c1 into v1;

exit when c1%notfound; sqlstr:=sqlstr||'

,max(decode('||col2||','''||v1||''','||col3||'))\"'||v1||'\"'; end loop; close c1;

sqlstr:=sqlstr||' from '||tabname||' group by '||col1; --execute immediate sqlstr; dbms_output.put_line(sqlstr); end;

---------------------------------------------------------------------------------------------------------- oracle行转列（动态行转不定列)

---------------------------------------------------------------建表 ----------------判断表是否存在 declare num number; begin

select count(1) into num from user_tables where table_name='TEST'; if num>0 then

execute immediate 'drop table TEST'; end if; end;

----------------建表

CREATE TABLE TEST(

WL VARCHAR2(10), XYSL INTEGER,

XYCK VARCHAR2(10), XCLCK VARCHAR2(10), XCLCKSL INTEGER, PC INTEGER );

----------------第一部分测试数据

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222); INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333); INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223); COMMIT;

--select * from test;

---------------------------------------------------------------行转列的存储过程 CREATE OR REPLACE PROCEDURE P_TEST IS V_SQL VARCHAR2(2000);

CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;

BEGIN

V_SQL := 'SELECT WL,XYSL,XYCK';

FOR V_XCLCK IN CURSOR_1 LOOP

V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK || ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK; END LOOP;

V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';

--DBMS_OUTPUT.PUT_LINE(V_SQL);

V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL; --DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE V_SQL; END;

----------------------------------------------------------------结果 ----------------执行存储过程，生成视图 BEGIN

P_TEST;

END;

----------------结果

SELECT * FROM RESULT T;

WL XYSL XYCK C1 C2 C3 C4 C5

---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------

A1 2 C1 20 50 10 0 0

A2 3 C4 40 0 0 110 70

----------------第二部分测试数据

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' , 20, 124); INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' , 30, 121); INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' , 20, 322); COMMIT;

----------------报告存储过程，生成视图 BEGIN

P_TEST; END;

----------------结果

SELECT * FROM RESULT T;

WL XYSL XYCK C1 C2 C3 C4 C5 C6 C7 C8

----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------

A1 2 C1 20 50 10 0 0 20 0 0

A2 2 C1 0 0 0 0 0 0 30 0

A2 3 C4 40 0 0 110 70 0 0 0

A3 2 C1 0 0 0 0 0 0 0 20

--------------- 删除实体 DROP VIEW RESULT;

DROP PROCEDURE P_TEST; DROP TABLE TEST;

搭建SSH框架

一、 建数据库 二、 建Web工程

打开Eclipse在工程栏——>鼠标右击New——>选择Web project——>project Name输入Demo;勾起Java 5.0单选按钮——>完成——>在Src文件夹下创建dal、bll、entity、

bll.biz、bll.action三个包以及两个子包。 三、 添加框架

1. 添加Struts

选中所建的Web工程——>鼠标右击选择MyEclipse——>选择Add Struts Capabilites…——>Struts specification选择Struts1.2；Base package for new class单击Brouse选择文件路径为dal包；勾掉多选按钮Install Struts TLDs ——>完成

2. 添加Spring

选中所建的Web工程——>鼠标右击选择MyEclipse——>选择Add Spring Capabilites…——>勾起多选按钮Spring 2.5 Aop Libraries；勾起多选按钮Spring 2.5 Core Libraries；勾起多选按钮Spring 2.5 Persistence Core；勾起多选按钮Spring 2.5 Web Libraries；勾起单选按钮Copy checked Library contents…——>单击Browse选择路径为WEB-INF包——>完成 3. 添加Hibernate

选中所建的Web工程——>鼠标右击选择MyEclipse——>选择Add Hibernate Capabilites…——>勾起单选按钮Copy checked Library Jars to…——>选择Spring configuration file——>选择Existing Spring configuration file; SessionFactory Id 输入sessionFactory ——>BeanId输入DemoBean；DB Driver选中自己所建的数据库——>勾掉Create Session Factory class? ——>完成 4. 将SSH架包粘贴到lib文件夹下

5. 展开工程中的Referenced Libraries 文件夹（架包文件夹）——>在展开的文件

中找到asm-2.23.jar文件——>右击Build path——>选择Remove from Build path ——> 删除文件asm-2.23.jar

四、创建数据

单击Eclipse右上角的MyEclipse Hibernate 按钮——>鼠标右键New——> Driver template 下拉选择Microsofe SQLServer 2005；Drever name输入DemoDB；Connection URL 输入jdbc:sqlserver://localhost:1433;User name 输入sa; password输入123456；单击Add JARs 按钮导入jdbc包；勾起Save password多选按钮——>勾起Display the selected schemas单选按钮；单击Add按钮导入数据库——>完成——>鼠标右击数据栏刚才所建的数据——>单击Open Connection ——>选择dbo ——>TABLE——>找到对应的表——>右击Hibernate Reverse Euginnering…——>单击Java src folder 的Brouse按钮选择包entity;勾Creat POJO <>DB…；勾起Java Data Object…；勾起Java Data Access Object…；勾掉Java Data Object…下面的Create abstract class——>在Id Generator 下框中选择native——>勾起Include referenced tables(AB);勾起Include referencing tables(AB);可以给表或者其中的属性起相应的名字，当然也可以不起让其自动生成——>完成

五、配置XML文件 1.配置Struts-config.xml文件 在下面添加标签

注：找到Referenced Libraries 包下面的spring-webmvc-struts . jar包 复制文件即可。 2.配置Web.xml文件

①在设计界面找到标签点击Add——>Choose a type 输入contextLoarderListener

②乱码的处理 Web.xml文件——>设计界面——>标签——>Filters属性单击Add按钮——>Filter-Name输入encoding；Filter-Class单击Browse…——>Chose a type 输入CharacterEncodingFilter——>OK——>Init Params 参数——>Add——>Param-Name输入encoding; Param-Value 输入UTF-8——>完成——>选中标签——>属性Filter Mappings——>Add …——>Filter-Name输入encoding; URL-Pattern输入/* 3．配置Application Spring.xml文件 如果改Spring配置文件名，则要做如下操作： Web.xml文件——>设计界面标签——>Add…——>Param-Name输入contextConfigLocation; Param-Value输入/WEB-INF/Spring*.xml——>完成