时间戳

首页 > 解决方案 > 使功能适应程序

sql - 使功能适应程序

问题描述

我有以下功能，我想把它变成一个程序。我需要做哪些改变才能完成它？我想把它变成一个程序，因为它是一种单一用途的功能，我更愿意把它和我的其他程序一起放在我的主包中。

我想而不是返回表，而是在过程本身内部创建它。

CREATE OR REPLACE FUNCTION pick_values RETURN t1_prueba_table
    PIPELINED
IS
    TYPE t2_type IS
        TABLE OF t2%rowtype;
    t2_data   t2_type;
    v_pc      t1.pc%TYPE;
BEGIN
  -- https://stackoverflow.com/a/67516191/1509264
  -- License: CC BY-SA 4.0
  FOR cur IN (
    SELECT *
    FROM   t1
    ORDER BY pc, r
  ) LOOP
    IF v_pc IS NULL OR v_pc <> cur.pc THEN
      v_pc := cur.pc;
      SELECT *
      BULK COLLECT INTO t2_data
      FROM   t2
      WHERE  pc = cur.pc;
    END IF;

    DECLARE
      a_freqs    int_list := int_list();
      cum_freq   INT := 0;
      taken      string_list := split_string(cur.an, ', ');
      idx        INT;
      c          t2.a%TYPE;
      
    BEGIN
      a_freqs.extend(t2_data.count);
      FOR i IN 1..t2_data.count LOOP
        IF     t2_data(i).a = cur.ao
           AND t2_data(i).c > 0
        THEN
          -- If there is an "ao" value and it has capacity then assign it to "c"
          c := t2_data(i).a;
          -- Decrement the appropriate "t2_data" row to show it has been used.
          t2_data(i).c := t2_data(i).c - 1;
          -- Set the "cum_freq" to 0 so the loop where values are randomly assigned is skipped.
          cum_freq := 0;
          -- Exit the loop
          EXIT;
        ELSIF ( 
                t2_data(i).a = cur.ay
            AND t2_data(i).c > 0
          ) OR (
                cur.ay IS NULL
            AND t2_data(i).a NOT MEMBER OF taken
            AND t2_data(i).c > 0
          )
        THEN
          a_freqs(i) := cum_freq + t2_data(i).c;
          cum_freq := cum_freq + t2_data(i).c;
        ELSE
          a_freqs(i) := cum_freq;
        END IF;
      END LOOP;

      IF cum_freq > 0 THEN
        idx := floor(dbms_random.value(0, cum_freq));
        FOR i IN 1..t2_data.count LOOP
          IF idx < a_freqs(i) THEN
            c := t2_data(i).a;
            t2_data(i).c := t2_data(i).c - 1;
            EXIT;
          END IF;
        END LOOP;
      END IF;

      PIPE ROW (
        t1_prueba_data(cur.pc, cur.vk, cur.ay, cur.ao, cur.an, cur.r, c)
      );
    END;
  END LOOP;
END;

如果需要任何其他信息，请告诉我。

标签： sqloracleplsql

解决方案

一个包可以包含过程和函数。

无需将其从函数转换为过程以将其包含在包中。

例如

create or replace package test AS

  PROCEDURE test_procedure (in_test in varchar2(100));

  FUNCTION assessment_newid_f RETURN assessments.assessment_id%TYPE;

END;

create or replace package body test AS

  PROCEDURE test_procedure (in_test in varchar2(100)) IS
  BEGIN
   ...
  END;

  FUNCTION assessment_newid_f RETURN assessments.assessment_id%TYPE IS
  BEGIN
    ...
    RETURN ...;
  END;

END;

推荐阅读