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

Question

我有一个apex应用程序，它由不同的步骤组成，每个步骤由一个表(表project，表send_for_approval，表approval，表start_project，…等)m我想创建一个函数，返回我所处的项目的哪个步骤，这是我迄今为止所尝试的

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;