需要通过 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 的记录被选中并返回公寓号码。如果输入数据集中有多个元素包含在两个带有数据的表中,则应返回所有相应的公寓号码。
例如,您可以这样:
运行和结果: