从 Select 语句调用 Oracle 存储过程?

我有一个 Oracle 存储过程，它获取 2 个参数并返回 2 个参数(状态和消息)。

我正在对这个只能执行选择语句的旧应用程序进行更改，

我的问题是是否可以用某个函数或其他存储过程或视图或任何其他我可能不知道的对象来包装存储过程，那么我可以使用简单的 select 语句执行存储过程吗？

正确的执行代码如下所示：

DECLARE
PRINTER_ID VARCHAR2(200);
O_STATUS VARCHAR2(200);
O_MESSAGE VARCHAR2(200);
BEGIN
PRINTER_ID := '551555115';
IMPL_XEROX_PRINTER_CHECK(    PRINTER_ID => PRINTER_ID,    O_STATUS => O_STATUS,    O_MESSAGE => O_MESSAGE  );
DBMS_OUTPUT.PUT_LINE('O_STATUS = ' || O_STATUS);
DBMS_OUTPUT.PUT_LINE('O_MESSAGE = ' || O_MESSAGE);
END;

我想要得到的是这样的：

Select O_STATUS,O_MESSAGE from IMPL_XEROX_PRINTER_CHECk_WRAPPER where PRINTER_ID = '551555115';

问题是 SP 正在将一些数据插入到临时表中... 下表如下：

CREATE TABLE "TEST_PRNT_DATA"    ( "COLUMN1" VARCHAR2(20 BYTE),    "COLUMN2" VARCHAR2(20 BYTE),    "COLUMN3" VARCHAR2(20 BYTE)   )

这是存储过程：

CREATE OR REPLACE PROCEDURE IMPL_XEROX_PRINTER_CHECK 
(
PRINTER_ID IN VARCHAR2 
, O_STATUS  OUT VARCHAR2
, O_MESSAGE OUT VARCHAR2
)  AS 
PROC_STATUS         VARCHAR2(10);
PROC_ERROR_MESSAGE  VARCHAR2(4000);
rand_num number; 
BEGIN 
dbms_output.put_line('IMPL_XEROX_PRINTER_CHECK ');
select round(dbms_random.value(1,10)) into rand_num     from dual;   
insert into TEST_PRNT_DATA values(1,2,3);
IF rand_num < 5 THEN
PROC_STATUS  := 'TRUE';
O_STATUS:= 'TRUE';
PROC_ERROR_MESSAGE := 'ALL IS GOOD';
O_MESSAGE:= 'ALL IS GOOD';
ELSE
PROC_STATUS  := 'FALSE';
O_STATUS:= 'FALSE';
PROC_ERROR_MESSAGE := 'SOMTHING WENT WRONG!!! ';
O_MESSAGE:= 'SOMTHING WENT WRONG!!! ';
END IF;
END IMPL_XEROX_PRINTER_CHECK;

您可以使用管道表函数创建一个包：

CREATE OR REPLACE 
PACKAGE PACKAGE1 
AS 
type status_t is record ( o_status varchar2(10)
, o_message varchar2(4000));
type status_tt is table of status_t;
function impl_xerox_printer_check_w(printer_id varchar2) RETURN status_tt PIPELINED;
END PACKAGE1;
/

通过以下实现：

CREATE OR REPLACE
PACKAGE BODY PACKAGE1 AS
function impl_xerox_printer_check_w(printer_id varchar2) RETURN status_tt PIPELINED AS
status status_t;
BEGIN
impl_xerox_printer_check(printer_id, status.o_status, status.o_message);
PIPE ROW (status);
RETURN;
END impl_xerox_printer_check_w;
END PACKAGE1;
/

并像这样使用它：

with printers as (
select dbms_random.string('X',10) printer from dual connect by level <=5
)
select * 
from printers
cross apply table(package1.impl_xerox_printer_check_w(printers.printer));

示例输出或查看 db<>fiddle：

PRINTER         O_STATUS   O_MESSAGE                     
--------------- ---------- ------------------------------
55FBCMHYOS      TRUE       ALL IS GOOD                   
0Z37VPOSLK      TRUE       ALL IS GOOD                   
XK1QKTZ8X2      FALSE      SOMTHING WENT WRONG!!!        
K0Y6TN9YTR      FALSE      SOMTHING WENT WRONG!!!        
8D0505711L      TRUE       ALL IS GOOD

基于Alex的几个答案(sys.odcivarchar2list集合和函数)的组合，以下是主题的几个变体：

第一个与大多数示例一样，通过在最后一个查询中使用透视返回单行：

with function wrap(printer_id in varchar2) return sys.odcivarchar2list as
status sys.odcivarchar2list;
begin
status := new sys.odcivarchar2list();
status.extend(2);
impl_xerox_printer_check(printer_id, status(1), status(2));
return status;
end;
t1 as (
select rownum r, column_value
from wrap('551555115')
)
select * 
from t1
pivot (max(column_value) 
for r in ( 1 as status
, 2 as message));
/

示例输出：

STATUS   MESSAGE                  
-------- -------------------------
FALSE    SOMTHING WENT WRONG!!!

第二个示例演示如何使用 CROSS APPLY 一次获取多台打印机的状态：

with function wrap(printer_id in varchar2) return sys.odcivarchar2list as
status sys.odcivarchar2list;
begin
status := new sys.odcivarchar2list();
status.extend(2);
impl_xerox_printer_check(printer_id, status(1), status(2));
return status;
end;
printers as (
select dbms_random.string('X',10) printer from dual connect by level <=5
), t1 as (
select printer, mod(rownum-1,2) r,  w.*
from printers
cross apply wrap(printers.printer) w
)
select * 
from t1
pivot (max(column_value) for r in (0 as status, 1 as message));
/

示例输出：

PRINTER    STATUS   MESSAGE                  
---------- -------- -------------------------
M6N6MZ5NG6 TRUE     ALL IS GOOD              
4H2WKK52V7 TRUE     ALL IS GOOD              
6MB7B9FRWV TRUE     ALL IS GOOD              
389KALS4U9 FALSE    SOMTHING WENT WRONG!!!   
6Y1ACVUHY6 TRUE     ALL IS GOOD

这取决于您的应用程序可以处理的内容。你可以有一个返回 ref 游标的包装函数：

create or replace function impl_xerox_printer_check_wrap (
printer_id in varchar2 
)
return sys_refcursor as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return o_refcursor;
end;
/
select impl_xerox_printer_check_wrap('551555115') from dual;
IMPL_XEROX_PRINTER_C
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
STATUS     MESSAGE                       
---------- ------------------------------
TRUE       ALL IS GOOD

(输出如 SQL 开发人员所示，作为脚本运行)。但是您的应用程序可能不知道如何处理它。

您可以使用集合或对象类型，但除非您在架构级别定义自己的集合或对象类型，否则解释起来有点痛苦：

create or replace function impl_xerox_printer_check_wrap (
printer_id in varchar2 
)
return sys.odcivarchar2list as
o_result sys.odcivarchar2list;
begin
o_result := new sys.odcivarchar2list();
o_result.extend(2);
impl_xerox_printer_check(printer_id => printer_id, o_status => o_result(1), o_message => o_result(2));
return o_result;
end;
/
select * from table (impl_xerox_printer_check_wrap('551555115'));
Result Sequence                                  
------------------------------------------------
TRUE
ALL IS GOOD

或者你可以通过XML，这听起来很奇怪，但给出了一个不错的结果：

create or replace function impl_xerox_printer_check_wrap (
printer_id in varchar2 
)
return xmltype as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return xmltype(o_refcursor);
end;
/
select impl_xerox_printer_check_wrap('551555115') from dual;
IMPL_XEROX_PRINTER_CHECK_WRAP('551555115')                                      
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<STATUS>FALSE</STATUS>
<MESSAGE>SOMTHING WENT WRONG!!! </MESSAGE>
</ROW>
</ROWSET>

好吧，这看起来不是很有帮助...但随后您提取值：

select status, message
from xmltable(
'/ROWSET/ROW'
passing impl_xerox_printer_check_wrap('551555115')
columns status varchar2(200) path 'STATUS',
message varchar2(200) path 'MESSAGE'
);
STATUS     MESSAGE                       
---------- ------------------------------
FALSE      SOMTHING WENT WRONG!!!

数据库<>小提琴

您的应用程序可以运行该查询(当然，将打印机 ID 作为绑定变量传递)，并将返回一个简单的结果集。

由于您使用的是 12c，因此您可以使用添加到子查询分解中的 PL/SQL 功能，因此您根本不需要创建永久函数(尽管您可能仍然更喜欢)：

drop function IMPL_XEROX_PRINTER_CHECK_WRAP;
with
function impl_xerox_printer_check_wrap (
printer_id in varchar2 
)
return xmltype as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return xmltype(o_refcursor);
end;
select impl_xerox_printer_check_wrap('551555115')
from dual
/

如果你想要XML(根据注释)，或者如果你不需要XMLTable：

IMPL_XEROX_PRINTER_CHECK_WRAP('551555115')                                      
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<STATUS>TRUE</STATUS>
<MESSAGE>ALL IS GOOD</MESSAGE>
</ROW>
</ROWSET>
with
function impl_xerox_printer_check_wrap (
printer_id in varchar2 
)
return xmltype as
o_status varchar2(200);
o_message varchar2(200);
o_refcursor sys_refcursor;
begin
impl_xerox_printer_check(printer_id => printer_id, o_status => o_status, o_message => o_message);
open o_refcursor for select o_status as status, o_message as message from dual;
return xmltype(o_refcursor);
end;
select status, message
from xmltable(
'/ROWSET/ROW'
passing impl_xerox_printer_check_wrap('551555115')
columns status varchar2(200) path 'STATUS',
message varchar2(200) path 'MESSAGE'
)
/
STATUS     MESSAGE                       
---------- ------------------------------
FALSE      SOMTHING WENT WRONG!!!

问题是SP正在向临时表插入一些数据

这是一个相当关键的遗漏。不能从选择在函数调用中执行插入或更新。该文档列出了对从 SQL 调用的函数的限制，并在此警告中详细介绍了：

因为 SQL 是一种声明性语言，而不是命令式(或过程式)语言，所以您无法知道 SQL 语句调用的函数将运行多少次 — 即使该函数是用命令式语言 PL/SQL 编写的。

如果允许该函数执行 DML，则无法控制执行该 DML 的次数。例如，如果它正在执行插入，它可能会尝试插入同一行两次，然后重复数据或违反约束。

从技术上讲，您可以使用pragma autonomous_transaction声明函数，就像在这个修改后的 db<>fiddle 中一样，但这是一个可怕的黑客，由于上面显示的原因，最终可能会导致比它解决的问题更多的问题。如果您只像示例中那样进行单行调用，您可能会侥幸逃脱，但即便如此，也不能保证它有效;即使它现在有效，将来也可能破裂。

创建一个新的 Sql 触发器，用于监视表Table_legacyInputOutput。在打印机 ID PRINTER_ID = '551555115' 的表格中插入您的输入然后触发器将调用存储过程并更新表对于O_STATUS和O_MESSAGE. 我认为您的旧版应用程序至少可以插入和选择。它只是无法调用 SP 并检查返回参数

Table_legacyInputOutput structure.
PRINTER         O_STATUS   O_MESSAGE

相关内容

最新更新

热门标签：