sql - 试图理解 PLSQL 函数-6ren

sql - 试图理解 PLSQL 函数

转载作者：行者123 更新时间：2023-12-01 13:12:37

我是 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 概念 是什么，我将不胜感激，这样我就可以继续以更好的方式理解它们，一些小的解释会很长方式。

问题:

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

最佳答案

它看起来像一个报告功能。它构建了一个带有某些条件的 SQL 语句(WHERE 中的某些元素取决于参数)。

查询本身看起来很复杂。它使用 with 构造，让您可以在查询中定义某种内联 View 。这本身更像是一个 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;

关于sql - 试图理解 PLSQL 函数，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4557599/

文章推荐： r - 了解 ' | ' 和 ' & ' 运算符行为(R 的逻辑运算符)

文章推荐： Java继承: hiding fields

文章推荐： java - 为什么我会收到空堆栈异常？

文章推荐： haskell "Could not deduce", "Non type-variable argument"

详解C语言sscanf()函数、vsscanf()函数、vscanf()函数
C语言sscanf()函数：从字符串中读取指定格式的数据头文件： ?
php - 如何解释at()函数； substr()函数;伪代码中的exist()函数
最近，我有一个关于工作预评估的问题，即使查询了每个功能的工作原理，我也不知道如何解决。这是一个伪代码。下面是一个名为foo()的函数，该函数将被传递一个值并返回一个值。如果将以下值传递给foo函数，
VBS教程：函数-CStr 函数
CStr 函数返回表达式，该表达式已被转换为 String 子类型的 Variant。 CStr(expression) expression 参数是任意有效的表达式。说明通常，可以
VBS教程：函数-CSng 函数
CSng 函数返回表达式，该表达式已被转换为 Single 子类型的 Variant。 CSng(expression) expression 参数是任意有效的表达式。说明通常，可
VBS教程：函数-CreateObject 函数
CreateObject 函数创建并返回对 Automation 对象的引用。 CreateObject(servername.typename [, location]) 参数 serv
VBS教程：函数-Cos 函数
Cos 函数返回某个角的余弦值。 Cos(number) number 参数可以是任何将某个角表示为弧度的有效数值表达式。说明 Cos 函数取某个角并返回直角三角形两边的比值。此比值是
VBS教程：函数-CLng 函数
CLng 函数返回表达式，此表达式已被转换为 Long 子类型的 Variant。 CLng(expression) expression 参数是任意有效的表达式。说明通常，您可以使
VBS教程：函数-CInt 函数
CInt 函数返回表达式，此表达式已被转换为 Integer 子类型的 Variant。 CInt(expression) expression 参数是任意有效的表达式。说明通常，可
VBS教程：函数-Chr 函数
Chr 函数返回与指定的 ANSI 字符代码相对应的字符。 Chr(charcode) charcode 参数是可以标识字符的数字。说明从 0 到 31 的数字表示标准的不可打印的
VBS教程：函数-CDbl 函数
CDbl 函数返回表达式，此表达式已被转换为 Double 子类型的 Variant。 CDbl(expression) expression 参数是任意有效的表达式。说明通常，您可
VBS教程：函数-CDate 函数
CDate 函数返回表达式，此表达式已被转换为 Date 子类型的 Variant。 CDate(date) date 参数是任意有效的日期表达式。说明 IsDate 函数用于判断 d
VBS教程：函数-CCur 函数
CCur 函数返回表达式，此表达式已被转换为 Currency 子类型的 Variant。 CCur(expression) expression 参数是任意有效的表达式。说明通常，
VBS教程：函数-CByte 函数
CByte 函数返回表达式，此表达式已被转换为 Byte 子类型的 Variant。 CByte(expression) expression 参数是任意有效的表达式。说明通常，可以
VBS教程：函数-CBool 函数
CBool 函数返回表达式，此表达式已转换为 Boolean 子类型的 Variant。 CBool(expression) expression 是任意有效的表达式。说明如果 ex
VBS教程：函数-Atn 函数
Atn 函数返回数值的反正切值。 Atn(number) number 参数可以是任意有效的数值表达式。说明 Atn 函数计算直角三角形两个边的比值 (number) 并返回对应角的弧
VBS教程：函数-Asc 函数
Asc 函数返回与字符串的第一个字母对应的 ANSI 字符代码。 Asc(string) string 参数是任意有效的字符串表达式。如果 string 参数未包含字符，则将发生运行时错误。
VBS教程：函数-Array 函数
Array 函数返回包含数组的 Variant。 Array(arglist) arglist 参数是赋给包含在 Variant 中的数组元素的值的列表（用逗号分隔）。如果没有指定此参数，则
VBS教程：函数-Abs 函数
Abs 函数返回数字的绝对值。 Abs(number) number 参数可以是任意有效的数值表达式。如果 number 包含 Null，则返回 Null；如果是未初始化变量，则返回 0。
VBS教程：函数-FormatPercent 函数
FormatPercent 函数返回表达式，此表达式已被格式化为尾随有 % 符号的百分比（乘以 100 ）。 FormatPercent(expression[,NumDigitsAfterD
VBS教程：函数-FormatNumber 函数
FormatNumber 函数返回表达式，此表达式已被格式化为数值。 FormatNumber( expression [,NumDigitsAfterDecimal [,Inc

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - 试图理解 PLSQL 函数