create or replace function Nosaleshops (ID_SHOP int,NAME_SHOP varchar2)
return int is
count_shops int;
begin
select count (ID_SHOP) into count_shops from SHOPS where ID_SHOP in
(select distinct ID_SHOP from SALES where QUANTITY=0) and ID_SHOP=ID_SHOP;
return count_shops;
end Nosaleshops;

create or replace function Nosaleshops (ID_SHOP int,NAME_SHOP varchar2)
return int is
count_shops int;
begin
select sh.NAME_SHOP,count(sh.ID_SHOP)into count_shops from shops sh inner join SALES s on (s.id_shop=sh.id_shop)where s.id_shop in
(select distinct s.ID_SHOP from SALES s where s.QUANTITY=10)
group by sh.name_shop;
return count_shops;
end Nosaleshops;

create or replace function Nosaleshops
return int is
count_shops int;
begin
select count(sh.NAME_SHOP) 
into count_shops
from shops sh,SALES s where s.id_shop in
(select distinct s.ID_SHOP from SALES s where s.QUANTITY=0 and s.id_shop=sh.id_shop);
return count_shops;
end Nosaleshops;

PLS-00428: an INTO clause is expected in this SELECT statement

create or replace function sale_by_theme
return int is
Saled int;
begin
select t.NAME_THEME,sh.name_shop,count(s.quantity) as Saled from THEMES t,SHOPS sh,BOOKS b,sales s where
b.ID_BOOK=s.id_book and t.ID_THEME=b.ID_THEME and s.ID_SHOP=sh.id_shop
group by t.name_theme,sh.name_shop;
return Saled;
end;

select 'test1' as t1, 'test2' as t2, 123 as t3, -1 as t4 from dual

CREATE USER test1 IDENTIFIED BY p4ssw0rd;

--

CREATE OR REPLACE PACKAGE test1."pkg_test" IS
TYPE tshow_type IS RECORD
(
 "t1" VARCHAR2(30 BYTE) ,
 "t2" VARCHAR2(30 BYTE) ,
 "t3" NUMBER,
 "t4" NUMBER
);
TYPE tshow IS TABLE OF tshow_type;
FUNCTION fn_tshow (parT4 NUMBER) return tshow pipelined;

END;
/

CREATE OR REPLACE PACKAGE BODY test1."pkg_test" IS
FUNCTION fn_tshow (parT4 NUMBER) return tshow pipelined IS
v_obj tshow_type;
BEGIN
FOR e IN (
select 'test1' as "t1", 'test2' as "t2", 123 as "t3", nvl(parT4,-1) as "t4" from dual
 )
LOOP
 v_obj."t1" := e."t1";
 v_obj."t2" := e."t2";
 v_obj."t3" := e."t3";
 v_obj."t4" := e."t4";
PIPE ROW (v_obj);
END LOOP;
RETURN;
end;

END;
/


--

select * from TABLE(test1."pkg_test".fn_tshow(parT4 => 23456))