尝试理解 PLSQL 函数

Question

我是 PLSQL 的新手，我有一个巨大的 plsql 函数，我试图理解它，但很难理解它的流程，所以如果有人能帮我完成这些大的部分，我将非常感激。可以理解流程。指导将受到高度赞赏。

      FUNCTION analysis(            
        REGION_ID_P                 VARCHAR2,
        COUNTRY_ID_P            VARCHAR2 ,
        SUB_REGION_ID_P         VARCHAR2 ,
        CUSTOMER_TYPE_ID_P          VARCHAR2 ,
        RECEIVED_FROM_DATE_P        VARCHAR2 ,
        RECEIVED_TO_DATE_P          VARCHAR2,
        CUSTOMER_ID_P           VARCHAR2 ,
        PRIORITY_ID_P               VARCHAR2,
        WORK_GROUP_ID_P       VARCHAR2,
        CITY_ID_P VARCHAR2,
        USER_ID_P               VARCHAR2            
  )  RETURN ANALYSIS_REPORT_TAB_TYPE pipelined
  IS
          with_sql LONG;
          e_sql LONG;
          where_sql LONG;
          group_by_sql LONG;
          curent_date Date;
      v_row ANALYSIS_REPORT_ROW_TYPE := ANALYSIS_REPORT_ROW_TYPE(
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL
      );
       TYPE rectyp IS REF CURSOR;                                                                                                                                                                                                   -- define weak REF CURSOR type
       rrc_rectyp                    rectyp;

       TYPE recordvar IS RECORD(
    MONTHS        VARCHAR2(100),
  ORDERBY_MONTHS VARCHAR2(100),
    REQ_RECEIVED  NUMBER(9,2),
    REQ_STILL_OPEN NUMBER(9,2),
    REQ_AWAIT_ACCEPTANCE NUMBER(9,2),
    REQ_WITH_ATT NUMBER(9,2),
    REQ_CLOSED NUMBER(9,2),
    REQ_CANCELLED NUMBER(9,2)
       );
       res_rec                       recordvar;
  BEGIN

    select sysdate +substr(to_char(systimestamp, 'tzr'),3,1)/24 into curent_date from dual;
                where_sql := ' AND 1=1 ';
        IF COUNTRY_ID_P IS NOT NULL THEN
                  where_sql := where_sql ||' AND x.country_id ='|| COUNTRY_ID_P;
                END IF;
                IF SUB_REGION_ID_P IS NOT NULL THEN
                  where_sql := where_sql ||' AND x.SUB_REGION_ID ='|| SUB_REGION_ID_P;
                END IF;  
                IF CUSTOMER_TYPE_ID_P IS NOT NULL THEN
                  where_sql := where_sql ||' AND x.CUSTOMER_TYPE_ID ='|| CUSTOMER_TYPE_ID_P;
                END IF;
                IF RECEIVED_FROM_DATE_P IS NOT NULL THEN
                        where_sql := where_sql||' AND convert_time(received_date, ''GMT'', ''GMT'') >= convert_time(trunc(to_date('''||RECEIVED_FROM_DATE_P||''',''dd/mm/yyyy HH24:MI:SS'')), ''Europe/Paris'', ''GMT'')';
                END IF;
                IF RECEIVED_TO_DATE_P IS NOT NULL  THEN
                        where_sql := where_sql||' AND convert_time(received_date, ''GMT'', ''GMT'') <= convert_time(trunc(to_date('''||RECEIVED_TO_DATE_P||''',''dd/mm/yyyy HH24:MI:SS'')), ''Europe/Paris'', ''GMT'')';
                END IF;
                IF CUSTOMER_ID_P IS NOT NULL THEN
                  where_sql := where_sql||' AND x.CUSTOMER_ID in(select CUSTOMER_ID from lk_customer where upper(CUSTOMER_NAME) like upper('''||CUSTOMER_ID_P||'%''))';
                END IF;  
                IF PRIORITY_ID_P IS NOT NULL THEN
                   where_sql := where_sql ||' AND x.PRIORITY_ID ='|| PRIORITY_ID_P;
                END IF;    
                IF WORK_GROUP_ID_P IS NOT NULL THEN
                   where_sql := where_sql ||' AND x.WORKGROUP_ID ='|| WORK_GROUP_ID_P;
                END IF;                     
                IF CITY_ID_P IS NOT NULL THEN
                  where_sql := where_sql ||' AND x.CITY_ID = ' || CITY_ID_P;
                END IF;     
    group_by_sql := ' group by to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''mm/YYYY''),to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''yyyy/mm'')';                    

    with_sql := 'with
               b AS (select cep_work_item_no from ap_main where req_accept_date is null and  ecep_ap_utils.f_business_days(received_date,'''||curent_date||''')>30),
           e AS (select cep_work_item_no from ap_main where status_id=1 and req_accept_date is not null and stage_ID != 10 and stage_Id !=4 and ecep_ap_utils.f_business_days(received_date,'''||curent_date||''')>30),
           --f AS  (select cep_work_item_no from ap_main  where received_date is not null),
           m AS (select cep_work_item_no from ap_main  where received_date is not null and status_id=1), 
         n AS (select cep_work_item_no from ap_main  where  status_id=2),
         o AS (select cep_work_item_no from ap_main  where  status_id=3)';

--e_sql := ' SELECT MONTHS, REQ_RECEIVED,REQ_STILL_OPEN, REQ_AWAIT_ACCEPTANCE, REQ_WITH_ATT from (';
--e_sql := with_sql;
    e_sql := with_sql||' select   to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''mm/YYYY'') MONTHS, to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''yyyy/mm'') ORDERBY_MONTHS,
        count(x.cep_work_item_no)  REQ_RECEIVED,
        count(m.cep_work_item_no) REQ_STILL_OPEN,count(b.cep_work_item_no) REQ_AWAIT_ACCEPTANCE,count(e.cep_work_item_no) REQ_WITH_ATT,
  count(n.cep_work_item_no) REQ_CLOSED, count(o.cep_work_item_no) REQ_CANCELLED
        from ap_main x,m,b,e,n,o where  x.cep_work_item_no=m.cep_work_item_no(+)
        and x.cep_work_item_no = b.cep_work_item_no(+) and x.cep_work_item_no=e.cep_work_item_no(+) and 
  x.cep_work_item_no=n.cep_work_item_no(+) and x.cep_work_item_no=o.cep_work_item_no(+)
        and x.received_date is not null';
e_sql := e_sql|| where_sql||group_by_sql;

           OPEN rrc_rectyp FOR e_sql;
              LOOP
                 FETCH rrc_rectyp INTO  res_rec;
                 EXIT WHEN rrc_rectyp%NOTFOUND;
                      v_row.MONTHS      := res_rec.MONTHS ;
                      v_row.ORDERBY_MONTHS      := res_rec.ORDERBY_MONTHS ;
                      v_row.REQ_RECEIVED        := res_rec.REQ_RECEIVED;
                      v_row.REQ_STILL_OPEN      := res_rec.REQ_STILL_OPEN;
                      v_row.REQ_AWAIT_ACCEPTANCE        := res_rec.REQ_AWAIT_ACCEPTANCE;
                      v_row.REQ_WITH_ATT        := res_rec.REQ_WITH_ATT;
                      v_row.REQ_CLOSED      := res_rec.REQ_CLOSED;
                      v_row.REQ_CANCELLED       := res_rec.REQ_CANCELLED;
                  pipe ROW(v_row);

              END LOOP;
              RETURN;                    
  END analysis;

如果有人能让我知道这里使用的重要 plsql 概念，以便我可以继续以更好的方式理解它们，并且一些小的解释会大有帮助，我也将不胜感激。

问题：

根据您的经验，上述方法是编写报告函数的通用方法吗？还是有一些最佳实践？

Answer 1

解决此类问题的一个好方法是在调试器中单步执行代码。 Oracle 提供了一个名为 SQL Developer 的免费工具，它带有一个 dubugger，所以你可以使用它。

乍一看，这段代码看起来像是在构建一个动态 SQL 语句来获取一些数据。我所说的动态是指 SQL 语句是在运行时构建的，并且该过程基于传入的参数构建 where 子句。

最后，他们正在做：

打开 rrc_retyp FOR e_sql

基本上将查询结果放入引用游标中，这允许客户端获取结果数据。

顺便说一句，以这种方式使用动态 SQL 对性能来说非常糟糕，因为它会导致硬解析。您可以在 此链接。解决方案是使用上下文，因此您最终会获得绑定变量的优点并避免硬解析（这在该链接中进行了讨论）。

编辑
实际上，他们正在将结果数据通过管道传输到集合变量中。请参阅此链接，然后搜索“分配表函数的结果”。

Answer 2

它看起来像一个报告功能。它构建一个包含一些条件的 SQL 语句（WHERE 中的某些元素取决于参数）。

查询本身看起来相当复杂。它使用 with 构造，让您可以在查询中定义内联视图的类型。这本身更多的是 SQL（也许是 Oracle SQL）功能，而不是 PLSQL。

然后，在游标中打开查询（在字符串变量中构建）。游标可以看作是一种遍历查询结果的工具，查询是在循环中完成的。

然后，游标中的变量被放入 v_row 的属性中。 v_row 被声明为记录类型。它是一个可以代表记录的对象。该对象通过管道传送到输出，这意味着该函数实际上返回一个记录集，这意味着您可以在查询中调用它，如下所示：

select * from table(monthly_analysis(<parameters>))

[编辑]

按请求添加：如何执行的示例plsql 中的查询，获取结果并返回它们，而不将查询构建为字符串。功能是从心打出来的，以原创为基础。我当然无法测试它，因为我没有正确的数据库。实际上，我现在根本没有数据库或编辑器，所以请阅读拼写错误。 ;)

create function Analysis2(
  REGION_ID_P            VARCHAR2,
  COUNTRY_ID_P           VARCHAR2,
  SUB_REGION_ID_P        VARCHAR2,
  CUSTOMER_TYPE_ID_P     VARCHAR2,
  RECEIVED_FROM_DATE_P   VARCHAR2,
  RECEIVED_TO_DATE_P     VARCHAR2,
  CUSTOMER_ID_P          VARCHAR2,
  PRIORITY_ID_P          VARCHAR2,
  WORK_GROUP_ID_P        VARCHAR2,
  CITY_ID_P              VARCHAR2,
  USER_ID_P              VARCHAR2)
return
  ANALYSIS_REPORT_TAB_TYPE
is
  V_RESULTSET ANALYSIS_REPORT_TAB_TYPE;
begin
  -- I hope the 'with' construct is supported within PLSQL. I don't have it here on my home laptop so I can't test it.
  with
    b AS (select cep_work_item_no from ap_main where req_accept_date is null and  ecep_ap_utils.f_business_days(received_date,''''||curent_date||'''')>30),
    e AS (select cep_work_item_no from ap_main where status_id=1 and req_accept_date is not null and stage_ID != 10 and stage_Id !=4 and
    ecep_ap_utils.f_business_days(received_date,''''||curent_date||'''')>30),
    --f AS  (select cep_work_item_no from ap_main  where received_date is not null),
    m AS (select cep_work_item_no from ap_main  where received_date is not null and status_id=1), 
    n AS (select cep_work_item_no from ap_main  where  status_id=2),
    o AS (select cep_work_item_no from ap_main  where  status_id=3)
  select
    -- You can actually use the record type constructor here to return 
    -- a specific record type instead of a bunch of loose fields
    ANALYSIS_REPORT_REC_TYPE(
      to_char(convert_time(received_date, 'GMT', 'Europe/Paris'),'mm/YYYY') MONTHS, 
      to_char(convert_time(received_date, 'GMT', 'Europe/Paris'),'yyyy/mm') ORDERBY_MONTHS,
      count(x.cep_work_item_no) REQ_RECEIVED,
      count(m.cep_work_item_no) REQ_STILL_OPEN,
      count(b.cep_work_item_no) REQ_AWAIT_ACCEPTANCE,
      count(e.cep_work_item_no) REQ_WITH_ATT,
      count(n.cep_work_item_no) REQ_CLOSED, 
      count(o.cep_work_item_no) REQ_CANCELLED)
  bulk collect into
    V_RESULTSET
  from 
    ap_main x,m,b,e,n,o 
  where 
    x.cep_work_item_no=m.cep_work_item_no(+)
    and x.cep_work_item_no = b.cep_work_item_no(+) and x.cep_work_item_no=e.cep_work_item_no(+) and 
    x.cep_work_item_no=n.cep_work_item_no(+) and x.cep_work_item_no=o.cep_work_item_no(+)
    and x.received_date is not null
    /* Additional where, based on input goes below. I did two, but you get the point */
    AND (COUNTRY_ID_P is null or x.country_id = COUNTRY_ID_P)
    AND (SUB_REGION_ID_P is null or x.SUB_REGION_ID = SUB_REGION_ID_P)
    -- and etc
  group by 
    to_char(convert_time(received_date, 'GMT', 'Europe/Paris'),'mm/YYYY'),
    to_char(convert_time(received_date, 'GMT', 'Europe/Paris'),'yyyy/mm');

  -- The entire resultset of the query is now stored in V_RESULTSET
  -- It can actually be looped using a loop like this:
  -- for i in V_RESULTSET.first..V_RESULTSET.last loop
  --   DBMS_OUTPUT.PUT_LINE(V_RESULTSET(i).Whateverfield);
  -- end loop;

  -- But its not needed. The actual query is all this function does, so return its result

  return V_RESULTSET;
end;

Answer 3

您确定您已发布所有内容吗？因为按照现在的情况，它永远不会运行成功。许多变量被声明但从未使用。例如，e_sql 正在执行，但从未分配值。

我希望你不要试图通过查看这段代码来学习 PL/SQL，因为几乎每一行代码都让我感到畏缩。特别是将变量声明为 LONG（您不应该再使用它）、该记录的使用以及笨拙的日期处理。哎哟，哎哟，哎哟！最重要的是，如果有人编写这样的代码，那么有人肯定需要学会评论他正在做的事情。

---

更新

我重写了该函数，现在它已经完成了。我用这些辅助对象对其进行了测试：

SQL> create table ap_main
  2  ( cep_work_item_no number
  3  , received_date    date
  4  , req_accept_date  date
  5  , status_id        number
  6  , stage_id         number
  7  , country_id       number
  8  , sub_region_id    number
  9  , customer_type_id number
 10  , customer_id      number
 11  , priority_id      number
 12  , workgroup_id     number
 13  , city_id          number
 14  )
 15  /

Table created.

SQL> insert into ap_main
  2  select 1,sysdate,sysdate,1,4,1,1,1,1,1,1,1 from dual union all
  3  select 2,sysdate,sysdate,1,4,1,1,1,1,1,1,1 from dual union all
  4  select 3,sysdate,sysdate,1,5,1,1,1,1,1,1,1 from dual union all
  5  select 4,sysdate,sysdate,1,5,1,1,1,1,1,1,1 from dual union all
  6  select 5,sysdate,sysdate,2,5,1,1,1,1,1,1,1 from dual union all
  7  select 6,sysdate-31,sysdate-31,1,5,1,1,1,1,1,1,1 from dual union all
  8  select 7,sysdate-31,sysdate-31,1,5,1,1,1,1,1,1,1 from dual union all
  9  select 8,sysdate-31,sysdate-31,3,5,1,1,1,1,1,1,1 from dual
 10  /

8 rows created.

SQL> create table lk_customer (customer_id,customer_name)
  2  as
  3  select 1, 'Anna' from dual union all
  4  select 2, 'Bob' from dual
  5  /

Table created.

SQL> create type analysis_report_row_type as object
  2  ( months               varchar2(7)
  3  , orderby_months       varchar2(7)
  4  , req_received         number
  5  , req_still_open       number
  6  , req_await_acceptance number
  7  , req_with_att         number
  8  , req_closed           number
  9  , req_cancelled        number
 10  )
 11  /

Type created.

SQL> create type analysis_report_tab_type as table of analysis_report_row_type
  2  /

Type created.

SQL> create function convert_time
  2  ( p1 in date
  3  , p2 in varchar2
  4  , p3 in varchar2
  5  ) return date
  6  is
  7  begin
  8    return p1;
  9  end;
 10  /

Function created.

SQL> create package ecep_ap_utils
  2  as
  3    function f_business_days(p1 in date,p2 in date) return number;
  4  end ecep_ap_utils;
  5  /

Package created.

SQL> create package body ecep_ap_utils
  2  as
  3    function f_business_days(p1 in date,p2 in date) return number
  4    is
  5    begin
  6          return p2 - p1;
  7    end f_business_days;
  8  end ecep_ap_utils;
  9  /

Package body created.

您的函数的两个参数未使用，因此我删除了它们。所有参数的类型似乎都有错误，所以我也修复了这个问题。此外，我删除了所有不必要的变量，并使您的查询使用绑定变量。这很重要，因为 Oracle 将每个唯一的解析语句存储在共享池中以供重用。但是通过粘贴参数，您使每个语句都是唯一的，从而导致硬解析并填满共享池。

您的函数是一个管道函数，在您的情况下这似乎有点矫枉过正，因为您的结果集不会很大，因为您是按月分组的。所以你每个月只能得到一行。我把它留在原处。该查询访问了 ap_main 表六次，其中一次就足够了。您可能会通过性能提升注意到这一点。我仍然担心的是日期处理。最初的编码员无法决定是否要使用字符串或日期来处理日期。当然，您应该使用日期来处理日期。许多被调用的转换例程可能会以某种方式被跳过。无论如何...这是新函数：

SQL> create function analysis
  2  ( country_id_p         in number
  3  , sub_region_id_p      in number
  4  , customer_type_id_p   in number
  5  , received_from_date_p in date
  6  , received_to_date_p   in date
  7  , customer_id_p        in number
  8  , priority_id_p        in number
  9  , work_group_id_p      in number
 10  , city_id_p            in number
 11  ) return analysis_report_tab_type pipelined
 12  is
 13    l_current_date        date;
 14    l_refcursor           sys_refcursor;
 15    l_analysis_report_row analysis_report_row_type := analysis_report_row_type(null,null,null,null,null,null,null,null);
 16  begin
 17    select sysdate + to_number(to_char(systimestamp, 'tzh')) / 24
 18      into l_current_date
 19      from dual
 20    ;
 21    open l_refcursor for
 22      'select analysis_report_row_type
 23              ( to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''mm/yyyy'')
 24              , to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''yyyy/mm'')
 25              , count(cep_work_item_no)
 26              , count(case when received_date is not null and status_id=1 then 1 end)
 27              , count(case when req_accept_date is null and ecep_ap_utils.f_business_days(received_date,:p_current_date)>30 then 1 end)
 28              , count(case when req_accept_date is not null and status_id = 1 and stage_ID not in (4,10) and ecep_ap_utils.f_business_days(received_date,:p_current_date)>30 then 1 end)
 29              , count(case when status_id = 2 then 1 end)
 30              , count(case when status_id = 3 then 1 end)
 31              )
 32         from ap_main
 33        where received_date is not null ' ||
 34      case
 35      when country_id_p is null then
 36        ' and (1=1 or :p_country_id is null)'
 37      else
 38        ' and country_id = :p_country_id'
 39      end ||
 40      case
 41      when sub_region_id_p is null then
 42        ' and (1=1 or :p_sub_region_id is null)'
 43      else
 44        ' and sub_region_id = :p_sub_region_id'
 45      end ||
 46      case
 47      when customer_type_id_p is null then
 48        ' and (1=1 or :p_customer_type_id is null)'
 49      else
 50        ' and customer_type_id = :p_customer_type_id'
 51      end ||
 52      case
 53      when received_from_date_p is null then
 54        ' and (1=1 or :p_received_from_date is null)'
 55      else
 56        ' and convert_time(received_date, ''GMT'', ''GMT'') >= convert_time(trunc(:p_received_from_date), ''Europe/Paris'', ''GMT'')'
 57      end ||
 58      case
 59      when received_to_date_p is null then
 60        ' and (1=1 or :p_received_to_date is null)'
 61      else
 62        ' and convert_time(received_date, ''GMT'', ''GMT'') <= convert_time(trunc(:p_received_to_date), ''Europe/Paris'', ''GMT'')'
 63      end ||
 64      case
 65      when customer_id_p is null then
 66        ' and (1=1 or :p_customer_id is null)'
 67      else
 68        ' and customer_id in (select customer_id from lk_customer where upper(customer_name) like upper(:p_customer_id || ''%''))'
 69      end ||
 70      case
 71      when priority_id_p is null then
 72        ' and (1=1 or :p_priority_id is null)'
 73      else
 74        ' and priority_id = :p_priority_id'
 75      end ||
 76      case
 77      when work_group_id_p is null then
 78        ' and (1=1 or :p_workgroup_id is null)'
 79      else
 80        ' and workgroup_id = :p_workgroup_id'
 81      end ||
 82      case
 83      when city_id_p is null then
 84        ' and (1=1 or :p_city_id is null)'
 85      else
 86        ' and city_id = :p_city_id'
 87      end ||
 88      ' group by to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''mm/yyyy'')
 89            , to_char(convert_time(received_date, ''GMT'', ''Europe/Paris''),''yyyy/mm'')'
 90    using l_current_date
 91    ,     l_current_date
 92    ,     country_id_p
 93    ,     sub_region_id_p
 94    ,     customer_type_id_p
 95    ,     received_from_date_p
 96    ,     received_to_date_p
 97    ,     customer_id_p
 98    ,     priority_id_p
 99    ,     work_group_id_p
100    ,     city_id_p
101    ;
102    loop
103      fetch l_refcursor into l_analysis_report_row;
104      exit when l_refcursor%notfound;
105      pipe row (l_analysis_report_row);
106    end loop;
107    return;
108  end analysis;
109  /

Function created.

并证明新函数有效：

SQL> select * from table(analysis(1,1,1,null,null,1,1,1,1))
  2  /

no rows selected

SQL> select * from table(analysis(null,null,null,null,null,null,null,null,null))
  2  /

MONTHS  ORDERBY REQ_RECEIVED REQ_STILL_OPEN REQ_AWAIT_ACCEPTANCE REQ_WITH_ATT REQ_CLOSED REQ_CANCELLED
------- ------- ------------ -------------- -------------------- ------------ ---------- -------------
12/2010 2010/12            5              4                    0            0          1             0
11/2010 2010/11            3              2                    0            2          0             1

2 rows selected.

---

更新 2

这里有两个链接，指向此处使用的两个关键结构：

OPEN FOR 语句： http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/openfor_statement。 htm#sthref1703

管道函数：http ://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/tuning.htm#sthref1129

正如您在 OPEN FOR 语句文档中看到的，在 FOR 之后指定一个查询，我将其动态构建。您的原始代码中也做了同样的事情。不同之处在于我使用本机动态 SQL，因此我可以使用绑定变量（以“:p_”开头的变量）。我这样做的方式是，无论我提供什么输入值，所有绑定变量都出现在查询中。这里很好地解释了原因和方法： http ://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

如果您还有其他问题，请随时提问。

问候，
抢。