sql 关联使用id还是code_【PL/SQL 集市】存储语句优化+报告展示

看完这章你会学习到以下内容：

1. 从DW（数据集市）到RPT（报告）的过程
2. 存储过程SQL的优化
3. FineReport 如何给到决策权限给不同的人

---

首先已经有三张表存在DW层，订单表，订单明细表，产品表。
1.订单表（汇总一张订单的数量和金额）

2.订单明细表（细分到每张订单下买了什么东西）

3.产品明细表（产品属性介绍）

---

从DW层到RPT层的步骤操作（orcl作为我们的报表层）：

一、每个需求建立一个目标表
二、数据字典查看DM表和字段
三、确定指标口径
四、通过存储过程将数据统计到建立的目标表中（存储过程和目标表在同一层）
五、帆软画出2张报表
六、测试
七、开发脚本和报表模板上传到SVN
八、上线
九、维护

---

第一步：首要要确定好报表展示的需求。例如：报表的字段，度量值，图表样式等等》
而需求通常有两种，用Excel文档给出或者用文字图形给出。
*************************************************************************
【经验】我们关注的有两点：
1.需要的字段有哪些
2.通常在创建表的后面添加两列：
ETL_date(修改时间) 和 Source_data(数据来源) **************************************************************************
A,按天统计各个产品的销售情况（产品名称、产品销售总量、产品销售总额）
说明：产品名称中包括各个产品和每个产品大类两种情况
标识 产品名称 产品销售总量 产品销售总额

1 饼干 20 100

1 巧克力 10 100

2 零食类 30 200

2 纸类 .. ..

B.按天统计每个客户的购买产品情况（产品总量、产品总额）
时间 客户 产品总量 产品总额

---

第二第三步：看数据字典（表的说明文档），如果没有则跳过这一步。
**************************************************************************
【经验】我们关注的有三点：
1. 要用什么数据类型创建新表，数据口径（细分到年，月，日？）
2. 时效要求（T+？）
3. 数据加载方式（增量/全量） 是否需要支持重跑？
**************************************************************************

目的表名字1

目的表名字2

---

第四步：通过存储过程将数据统计到建立的目标表中。
************************************************************************** 【经验】我们关注有以下：
1. 存储过程要用SQL，插入建表要简洁（不关联无关紧要的表）
2. 插入数据到新表，可以不用游标，直接Insert Into即可。
3. 存储过程写在目标表的库中（orcl/rpt），并非表格原来存储的库里（dw）。
4. 要调用不同的库中的表，需要用Database.link。
**************************************************************************
首先按照数据字典的数据类型，创建一张新表在目标库中。

CREATE TABLE T_ProSAL_INFO_GAN(
PURCHASEDATE  VARCHAR(20),
PNAME VARCHAR(25),
CNT NUMBER(10),
AMOUNT NUMBER(20,2),
PID NUMBER(2),
ETL_DATE DATE DEFAULT SYSDATE) 

未优化的存储过程SQL代码！

CREATE OR REPLACE PROCEDURE SP_ProSAL_INFO_GAN IS
 CURSOR C_1 IS
 SELECT TO_CHAR(TDE.ORDER_TIME,'MMDD') AS 时间,
        TPRO.PRODUCT_NAME AS 产品名称,
        TOR.CNT   AS      产品销售总量,
        TOR.AMOUNT  AS    产品销总额,
        (CASE WHEN TPRO.PRODUCT_NAME IS NOT NULL THEN 1 ELSE NULL END) AS 标识
   FROM T_ORDER_DETAIL@DB_DM TDE
   JOIN T_PRODUCT@DB_DM  TPRO
     ON TDE.PRODUCT_NO = TPRO.PRODUCT_NO
   JOIN T_ORDER@DB_DM TOR
     ON TDE.ORDER_ID = TOR.ORDER_ID
 UNION ALL
                
 SELECT T1.*,(CASE WHEN 产品名称 IS NOT NULL THEN 2 ELSE NULL END) AS 标识 FROM
       (
        SELECT TO_CHAR(TDE.ORDER_TIME, 'MMDD') AS 时间,
                TPRO.PRODUCT_RNK AS 产品名称,
                 SUM(TOR.CNT) AS 总数,
                SUM(TOR.AMOUNT) AS 总额
           FROM T_ORDER_DETAIL@DB_DM TDE
           JOIN T_PRODUCT@DB_DM TPRO
             ON TDE.PRODUCT_NO = TPRO.PRODUCT_NO
           JOIN T_ORDER@DB_DM TOR
             ON TDE.ORDER_ID = TOR.ORDER_ID
          GROUP BY TO_CHAR(TDE.ORDER_TIME, 'MMDD'), TPRO.PRODUCT_RNK
         ) T1
  ORDER BY 时间,标识;

  V_1 C_1%ROWTYPE;

BEGIN
 --  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ProSAL_INFO_GAN';  -- 不是增量
   DELETE FROM T_CUSPUR_INFO_GAN;   --  支持重跑
   FOR V_1 IN C_1 LOOP
     INSERT INTO /*+append*/
     T_ProSAL_INFO_GAN(PURCHASEDATE,PNAME,CNT,AMOUNT,PID) VALUES(V_1.时间,V_1.产品名称,V_1.产品销售总量,V_1.产品销总额,V_1.标识);
   END LOOP;
   COMMIT;   -- 要提交
END;

************************************************************************** 可优化与注意的地方：
1. 不用三张表都全部关联的，尽量不要用三张关联。
2. 创建字段除了可以用Case When效果可以出来，但是要循环判断，效率还是相对较低。直接用常量作为一个新字段 例如： 1 AS FLAG, 2 AS FLAG，相当于在原表插入一条常量的字段!
3. 当插入的数据比较大时可以考虑Append加速，或者使用BULK COLLECT+FORALL加速批量提交。（更优的选择）
4. 所有的增删改减，一定要加Commit；
**************************************************************************
优化后存储过程的第一张表。

CREATE OR REPLACE PROCEDURE SP_PRODUCT_SALE
IS
BEGIN
  -- 清除数据支持重跑
  DELETE FROM T_PRODUCT_SALE;
  -- 插入目标表数据
  INSERT INTO T_PRODUCT_SALE(P_FLAG,
								 ORDER_DATE,
								 PRODUCT_NAME,
								 PRODUCT_CNT,
								 PRODUCT_AMOUNT,
								 ETL_DT) 
	  SELECT 1 AS P_FLAG,
	         TO_CHAR(B.ORDER_TIME, 'YYYY-MM-DD') AS ORDER_DATE,
		     A.PRODUCT_NAME,   -- 各个产品
		     COUNT(B.PRODUCT_NO) AS PRODUCT_CNT,
		     SUM(B.PRICE) AS PRODUCT_AMOUNT,
			 SYSDATE AS ETL_DT
	    FROM T_PRODUCT@DB_DM A
	    JOIN T_ORDER_DETAIL@DB_DM B
		  ON A.PRODUCT_NO = B.PRODUCT_NO
	   GROUP BY TO_CHAR(B.ORDER_TIME, 'YYYY-MM-DD'),
			    A.PRODUCT_NAME
	  UNION ALL
	  SELECT 2 AS P_FLAG,
	         TO_CHAR(B.ORDER_TIME, 'YYYY-MM-DD') AS ORDER_DATE,
		     A.PRODUCT_RNK||'类' AS PRODUCT_NAME,  -- 产品大类
			 COUNT(B.PRODUCT_NO) AS PRODUCT_CNT,
			 SUM(B.PRICE) AS PRODUCT_AMOUNT,
			 SYSDATE AS ETL_DT
	    FROM T_PRODUCT@DB_DM A
	    JOIN T_ORDER_DETAIL@DB_DM B
		  ON A.PRODUCT_NO = B.PRODUCT_NO
	   GROUP BY TO_CHAR(B.ORDER_TIME, 'YYYY-MM-DD'),A.PRODUCT_RNK
	   ORDER BY P_FLAG,ORDER_DATE,PRODUCT_NAME;
   COMMIT;
END;

第二张目标表的存储过程！

CREATE OR REPLACE PROCEDURE SP_CUSTOMER_PRODUCT_SALE
IS
BEGIN
  -- 清除数据支持重跑
  DELETE FROM T_CUSTOMER_PRODUCT_SALE;
  -- 插入目标表数据
  INSERT INTO T_CUSTOMER_PRODUCT_SALE(ORDER_DATE,
                                        CUSTOMER_CODE,
                                        PRODUCT_CNT,
                                        PRODUCT_AMOUNT,
									    ETL_DT) 
    SELECT TO_CHAR(ORDER_TIME, 'YYYY-MM-DD') AS ORDER_DATE,
           CUSTOMER_CODE,
           SUM(CNT) AS PRODUCT_CNT,
           SUM(AMOUNT) AS PRODUCT_AMOUNT,
		   SYSDATE AS ETL_DT
      FROM T_ORDER@DB_DM
     GROUP BY TO_CHAR(ORDER_TIME, 'YYYY-MM-DD'),
              CUSTOMER_CODE;
   COMMIT;
END;

BEGIN
  SP_CUSTOMER_PRODUCT_SALE;
END;

SELECT * FROM T_CUSTOMER_PRODUCT_SALE;

最终两张表的结果如下：

---

第四步： 打开帆软，连接数据库，并将数据库里的表导入到FineReport中。
************************************************************************** 【经验】我们关注有以下：
1. 打开服务器 - 选择定义数据连接
2. URL上不要缺少@字符 ，后面接着服务器的IP地址以及库名（orcl/rpt）
3. 注意用户名大小写和确保密码正确
4. 输入完毕，点击测试连接，确保连接成功？
**************************************************************************

不要漏了@ 加上IP地址

连接成功！

---

第五步： 画出两张表（普通报表）
************************************************************************** 【经验】我们关注有以下：
1. 选择模板数据库上的“+”号，添加外部数据库。
2. 只能写Select查询语句，不要Update，delete等。
3. 写完查询语句，记得点击右上角的阅览图表。 4. 如果最终显示只有一部分，可以调整
**************************************************************************

两张表都添加到FineReport 上面！

将需要的字段都添加放进单元格里，并添加图表润色。

如果最终演示效果不完整，则可以在模板 - 页面设置中设置大小。
就像Excel里面的分页符，可以在选项里剔除或者更改！

---

最后的效果简单如下：
1） 添加了筛选，将日期进行筛选
2） 对金额大于平均值，自动跟踪便凸显颜色
************************************************************************** 【经验】我们关注有以下：
1. 删除？问号后面的字符串，则进去决策系统模块！
2. 将图表保存设置在你的图表画布上，然后就可以分配权限给别人阅览。
**************************************************************************

PS： 关于这两张图如何制作，将会接下来的文章里写！