主页 / 问题 / 1094316
Accepted
Александр
Asked:2020-03-13 22:28:46 +0000 UTC2020-03-13 22:28:46 +0000 UTC

通过 OUT 参数从过程结果输出

  • 772

需要通过 OUT 参数实现程序的输出，而不使用剪贴板，即dbms_output.put_line.

该程序在包装中。

PROCEDURE array_app
    (street IN Streets.TITLE%TYPE,
    dom IN APARTMENTS.HOUSE%TYPE,
    res OUT Varchar2) 
IS
    CURSOR my_cur IS SELECT DISTINCT idapart FROM POSSESSION;
    CURSOR cur1 (street IN VARCHAR2, dom IN INTEGER)  
    IS
        SELECT APARTMENTS.id, num FROM APARTMENTS, Streets
        WHERE HOUSE = dom AND TITLE = street AND APARTMENTS.IDSTREET = Streets.ID;
    kv INTEGER;
    cnumber number;
    str Varchar2(100);
BEGIN
    res := '';
    OPEN cur1 (street, dom);
    FETCH cur1 INTO cnumber, kv;
        FOR f IN  my_cur  LOOP
            IF f.idapart = cnumber THEN
                str := res;
                select str || ', ' || kv INTO res from dual;
                RETURN;
            END IF;
        END LOOP;
    CLOSE cur1;
END;

和一个过程调用

DECLARE
   res1 Varchar2(100);
BEGIN
   pack.array_app('Татищева', 75, res1);
END;

数据示例：这是一个房屋表，其中的列分别是代码、街道和门牌号以及公寓号：

CREATE TABLE Apartments (id INTEGER PRIMARY KEY, idstreet VARCHAR2(100), house INTEGER NOT NULL, num INTEGER);

INSERT INTO Apartments (id, idstreet, house, num) VALUES (1, 'Татищева', 75, 5);
INSERT INTO Apartments (id, idstreet, house, num) VALUES (2, 'Кирова', 3, 15);
INSERT INTO Apartments (id, idstreet, house, num) VALUES (3, 'Новая', 28, 34);
INSERT INTO Apartments (id, idstreet, house, num) VALUES (4, 'Татищева', 75, 150);

还有一个所有权表，其中包含所有权对象的代码：

CREATE TABLE Possession (id INTEGER PRIMARY KEY, idapart INTEGER NOT NULL REFERENCES Apartments);

INSERT INTO Possession (id, idapart) VALUES (1, 1);
INSERT INTO Possession (id, idapart) VALUES (2, 4);

因此，结果，代码为 1 的记录被选中并返回公寓号码。如果输入数据集中有多个元素包含在两个带有数据的表中，则应返回所有相应的公寓号码。

sql

1 个回答

  1. Best Answer

    例如，您可以这样：

    create or replace package pack1 as
    procedure array_app (street varchar2, house integer, cur1 out sys_refcursor);
end;
/
create or replace package body pack1 as
    procedure array_app (street varchar2, house integer, cur1 out sys_refcursor) is
    begin
        open cur1 for 
            select a.num 
            from apartments a
            where a.house = array_app.house 
            and a.idstreet = array_app.street
            and exists (
                select 1 
                from possession p 
                where p.idapart = a.id
            );
    end array_app;
end pack1;
/

    运行和结果：

    var cur1 refcursor
exec pack1.array_app('Татищева', 75, :cur1)
print cur1

       NUM
----------
       150
         5
    • 4

相关问题