Orcale 使用存储过程动态行转列

Orcale11g 动态行转列

在一张表里有200多个收费项目需要行转列，本人太懒实在不想一个个敲，就想到了动态实现行转列。
1、使用了一丢丢的存储过程
2、开始行转列

一、介绍基本的存储过程使用方法

1、存储过程的语法

Create or replace procedure 存储过程名称（参数名称 {in|out|in out} datatype,参数名称 out sys_refcursor）
is|as
变量名称 datatype(lenth);
begin
body;
end;

其中各项参数介绍如下：
create or replace :创建或者更新，如果存在已有存储过程就更新。

{in|out|in out}：in 输入参数，out 输出参数，in out 可输入可输出。默认是in输入模式，在这里定义参数可以不用加数据长度。

sys_refcursor：Orcale 自带的输出游标，把结果集一把输出，不需遍历输出，个人感觉用来输出结果非常方便。

is|as：连接词。

datatype(lenth)：数据类型（长度）eg: name varchar2(200)。

2、游标

接下来介绍3种游标
1、sys_refcursor：orcale自带的函数，可以一把输出结果集，不需遍历，不需要初始化赋值。

2、ref cursor：需要先说明 eg：type weak_ref_cursor is ref cursor;
CURSOR_1 weak_ref_cursor;不需要初始化赋值。

3、CURSOR：必须初始化赋值 eg：CURSOR CURSOR_1 IS SELECT NAME FROM SKFS WHERE CODE<10;
具体在行转列的实现展示。

二、开始实现行转列

1、通过收款方式进行动态行转列

这是原表：
这是效果表：

2、具体实现

如果只需要知道怎么行转列，具体看这一块就可以了
以下是全部SQL语句：
CREATE OR REPLACE PROCEDURE P_TEST( P_FD in varchar2,
P_BM in varchar2,
P_SKTNO in varchar2,
P_SKFS in varchar2,
P_YCBJ in varchar2,
P_NAME in varchar2,
P_SPCODE in varchar2,
P_JLBH_F in varchar2,
P_JLBH_L in varchar2,
P_JZRQ_F in varchar2,
P_JZRQ_L in varchar2,
P_sklx in varchar2,
item out sys_refcursor)
AS
V_SQL VARCHAR2(4000); --用来存储需要行转列的SQL语句
V_LinkSQL VARCHAR2(4000); --用来存放行转列的项
V_LinkWhere VARCHAR2(4000);–需行转列的SQL语句过滤条件
V_SKFS VARCHAR2(4000); --获取到的收款方式
type weak_ref_cursor is ref cursor;–定义一个游标
CURSOR_1 weak_ref_cursor; --声明weak_ref_cursor 为CURSOR_1
V_XCLCK VARCHAR2(4000);
–CURSOR CURSOR_1 IS SELECT NAME FROM SKFS WHERE CODE<10;

BEGIN
  V_SQL := ' SELECT (C.INX+1) INX,X.FDBH,X.SKTNO SKTNO,X.JLBH JLBH,S.SPCODE,X.JYSJ,S.NAME,

C.XSSL,C.XSJE,C.YHJE,C.ZKJE,
B.DEPT_NAME ‘;
V_LinkSQL := ’ ‘;
V_LinkWhere:=’ ‘;
–Where条件拼接
–分店
IF P_FD IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND X.FDBH=’ || P_FD;
ELSE
V_LinkWhere:=V_LinkWhere || ’ ‘;
END IF;
–部门
IF P_BM IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND T.DEPTID=’ || P_BM;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–收款台
IF P_SKTNO IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND X.sktno=’ || P_SKTNO;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–收款方式
IF P_SKFS IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND XS.SKFS=’ || P_SKFS;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–销售类型
IF P_YCBJ =‘1’ then
V_LinkWhere:=V_LinkWhere || ’ AND X.YCBJ=11’;
ELSIF P_YCBJ =‘2’ then
V_LinkWhere:=V_LinkWhere || ’ AND NVL(X.YCBJ,0)<>11’;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–商品名称
IF P_NAME IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND S.NAME LIKE ‘’’|| ‘%’||P_NAME||’%’’’ ;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–商品编码
IF P_SPCODE IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND S.SPCODE=’ || P_SPCODE;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–记录编号F
IF P_JLBH_F IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND X.JLBH>=’ || P_JLBH_F;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–记录编号L
IF P_JLBH_L IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND X.JLBH<=’ || P_JLBH_L;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–记账日期F
IF P_JZRQ_F IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND X.JZRQ>=TO_DATE’||’(’’’||P_JZRQ_F||’’’,’’’||‘YYYY-MM-DD’||’’’)’;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–记账日期L
IF P_JZRQ_L IS NOT NULL then
V_LinkWhere:=V_LinkWhere || ’ AND X.JZRQ<=TO_DATE’||’(’’’||P_JZRQ_L||’’’,’’’||‘YYYY-MM-DD’||’’’)’;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;
–收款类型
IF P_SKLX =‘1’ then
V_LinkWhere:=V_LinkWhere || ’ AND S.SKLX=0’;
ELSIF P_SKLX =‘2’ then
V_LinkWhere:=V_LinkWhere || ’ AND S.SKLX=1’;
ELSE
V_LinkWhere:=V_LinkWhere;
END IF;

  --获取对应的收款方式
  V_SKFS:=' SELECT DISTINCT(SK.NAME) NAME 

FROM XSJL X,XSJLC C,WY_SPXX W,SPXX S,XSJLT T,BM B,XSJLC_SKFS XS,SKFS SK
WHERE X.JLBH=C.JLBH AND X.SKTNO=C.SKTNO
AND C.SP_ID=W.SP_ID AND X.FDBH=W.FDBH AND W.HTH=S.HTH AND W.SP_ID=S.SP_ID
AND C.JLBH=T.JLBH AND C.SKTNO=T.SKTNO AND C.TCKT_INX=T.TCKT_INX
AND T.DEPTID=B.DEPTID AND X.FDBH=B.FDBH
AND C.SKTNO=XS.SKTNO AND C.JLBH=XS.JLBH AND C.TCKT_INX=XS.TCKT_INX AND C.INX=XS.INX AND C.SP_ID=XS.SP_ID AND XS.DEPTID=B.DEPTID
AND SK.CODE=XS.SKFS ’ || V_LinkWhere ;
–打开游标
open CURSOR_1 for V_SKFS;
–循环遍历收款方式
loop
fetch CURSOR_1 into V_XCLCK;
exit when CURSOR_1%notfound; --游标找不到值了退出
V_LinkSQL := V_LinkSQL || ‘,’ || ’ SUM(DECODE(SK.NAME,’’’ || V_XCLCK||’’’,XS.SKJE,0)) AS ’ || V_XCLCK; --用decode行转列
end loop;
close CURSOR_1; --关闭游标
–execute immediate V_SKFS into CURSOR_1;
/*
第三种游标
FOR V_XCLCK IN CURSOR_1
LOOP
V_LinkSQL := V_LinkSQL || ‘,’ || ’ DECODE(SK.NAME,’’’ || V_XCLCK.NAME ||’’’,XS.SKJE,0) AS ’ || V_XCLCK.NAME;
END LOOP;*/

  --拼接SQL语句，固定的不需要转换的头V_SQL拼接行转列语句V_LINKSQL组成最后的SQL语句。
  V_SQL := V_SQL || V_LinkSQL || ' FROM XSJL X,XSJLC C,WY_SPXX W,SPXX S,XSJLT T,BM B,XSJLC_SKFS XS,SKFS SK

WHERE X.JLBH=C.JLBH AND X.SKTNO=C.SKTNO
AND C.SP_ID=W.SP_ID AND X.FDBH=W.FDBH AND W.HTH=S.HTH AND W.SP_ID=S.SP_ID
AND C.JLBH=T.JLBH AND C.SKTNO=T.SKTNO AND C.TCKT_INX=T.TCKT_INX
AND T.DEPTID=B.DEPTID AND X.FDBH=B.FDBH
AND C.SKTNO=XS.SKTNO AND C.JLBH=XS.JLBH AND C.TCKT_INX=XS.TCKT_INX AND C.INX=XS.INX AND C.SP_ID=XS.SP_ID AND XS.DEPTID=B.DEPTID
AND SK.CODE=XS.SKFS ’ || V_LinkWhere ||’ GROUP BY X.SKTNO,X.JLBH,C.INX,X.FDBH,S.SPCODE,X.JYSJ,S.NAME,C.XSSL,C.XSJE,C.YHJE,C.ZKJE, B.DEPT_NAME
ORDER BY JYSJ,SKTNO,JLBH,INX ASC’ ;

  --用游标循环打印结果
  --DBMS_OUTPUT.PUT_LINE(V_SQL);
  --创建视图
-- V_SQL := 'CREATE OR REPLACE VIEW RESULT  AS '||  V_SQL;
--item是SYS_REFCURSOR游标,直接用来输出结果

OPEN item FOR V_SQL;

END;

调试存储过程

至此就实现了我想要的动态行转列结果：

感谢观看，欢迎留言~共同进步！