SQL创建一个返回我所处步骤的函数

Question

我有一个顶点应用程序，它由不同的步骤组成，每个步骤都有一个表(表项目，表send_for_approval，表批准，表start_project，...我想创建一个函数来返回我所在项目的哪个步骤，这就是我到目前为止所尝试的

CREATE FUNCTION progress(my_id in number) return VARCHAR as
BEGIN
select project.id, send_for_approval.id, approval.id, start_project.id
from project
left join send_for_approval on send_for_approval.id_project=project.id
left join approval on approval.id_project=project.id
left join start_project on start_project.id_project=project.id
where project.id=my_id;
IF start_project.id is NOT NULL THEN
     RETURN 'Current step is the start of the project';
END IF;
IF approval.id is NOT NULL THEN
     RETURN 'Current step is approval';
END IF;
IF send_for_approval.id is NOT NULL THEN
     RETURN 'Current step is send for approval';
END IF;
END

Answer 1

在您的代码中，您将得到一个错误，因为您正在执行一个select查询，而没有将结果提取到变量中；您需要变量来处理id，从而检查变量：

CREATE FUNCTION progress (my_id IN NUMBER)
   RETURN VARCHAR
AS
    project_id              project.id%Type;
    send_for_approval_id    send_for_approval.id%Type;
    approval_id             approval.id%Type;
    start_project_id        start_project.%Type;
BEGIN
   SELECT project.id,
          send_for_approval.id,
          approval.id,
          start_project.id
    INTO project_id,
         send_for_approval_id,
         approval_id,
         start_project_id 
     FROM project
          LEFT JOIN send_for_approval
             ON send_for_approval.id_project = project.id
          LEFT JOIN approval ON approval.id_project = project.id
          LEFT JOIN start_project ON start_project.id_project = project.id
    WHERE project.id = my_id;

   IF start_project_id IS NOT NULL
   THEN
      RETURN 'Current step is the start of the project';
   END IF;

   IF approval_id IS NOT NULL
   THEN
      RETURN 'Current step is approval';
   END IF;

   IF send_for_approval_id IS NOT NULL
   THEN
      RETURN 'Current step is send for approval';
   END IF;
END;

另一种方法是在SQL中实现所有逻辑：

CREATE FUNCTION progress (my_id IN NUMBER)
   RETURN VARCHAR
AS
   result   VARCHAR2 (1000);
BEGIN
   SELECT CASE
             WHEN start_project.id IS NOT NULL
             THEN
                'Current step is the start of the project'
             WHEN approval.id IS NOT NULL
             THEN
                'Current step is approval'
             WHEN send_for_approval.id IS NOT NULL
             THEN
                'Current step is send for approval'
             ELSE
                '...'                              /* do you need an ELSE ? */
          END
     INTO result
     FROM project
          LEFT JOIN send_for_approval
             ON send_for_approval.id_project = project.id
          LEFT JOIN approval ON approval.id_project = project.id
          LEFT JOIN start_project ON start_project.id_project = project.id
    WHERE project.id = my_id;

   RETURN result;
END;

如上所述，这依赖于这样一个假设，即该查询保证始终只给出一行；否则，您将需要处理too_many_rows或no_data_found错误。

Answer 2

您可以完全避免连接

CREATE FUNCTION progress(my_id in number) return VARCHAR as
  l_status varchar2(100);
BEGIN
  SELECT status INTO l_status
  FROM (
    SELECT 'Current step is the start of the project' status
    FROM start_project
    WHERE id_project = progress.my_id

    UNION ALL

    SELECT 'Current step is approval'
    FROM approval
    WHERE id_project = progress.my_id

    UNION ALL

    SELECT 'Current step is send for approval'
    FROM send_for_approval
    WHERE id_project = progress.my_id
  );
  RETURN l_status;
END;