内部联接和where子句的PL/SQL过程

Question

我想为以下查询创建PL/SQL存储过程：

SELECT order_id, order_date,  customer_id
FROM Orders 
INNER JOIN Customers ON Orders.customer_id = Customers.customer_id
WHERE order_id = xyz;

我想传递order_id作为存储过程中的输入参数。有人能分享一下PL/SQL代码吗？

Answer 1

尝试此功能

CREATE OR REPLACE FUNCTION fn_test(p1 IN NUMBER)
RETURN VARCHAR2
IS
s_query_stmt     VARCHAR2(1000 CHAR);
r1               VARCHAR2(100 CHAR);
r2               VARCHAR2(100 CHAR);
r3               VARCHAR2(100 CHAR);
BEGIN
s_query_stmt := 'Select order_id, order_date, customer_id 
From Orders inner join Customers 
On Orders.customer_id = Customers.customer_id
WHERE order_id = :x )';
EXECUTE IMMEDIATE s_query_stmt INTO r1, r2, r3 USING p1;
return 'x';
END;

Answer 2

返回这些值的过程的最简单形式如下(：

create or replace procedure retrieve_order_values(
p_order_id IN Orders.order_id%type
, p_order_date OUT Orders.order_date%type
, p_customer_id OUT Orders.customer_id%type
)
is
begin
select order_date, customer_id
into p_order_date, p_customer_id
from orders
where order_id = p_order_id;
end retrieve_order_values;

请注意，您不需要联接到Customers表来检索customer_id。