1

Тема: створити функцію (Оракл)

Допоможіть виправити помилки.
створити функцію, яка повертає кількість магазинів, які не продали жодної книги видавництва

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

код помилки

5/1       PL/SQL: SQL Statement ignored
5/34      PL/SQL: ORA-00933: SQL command not properly ended
9/1       PLS-00103: Encountered the symbol "END" 
Errors: check compiler log

2

Re: створити функцію (Оракл)

як перевірити функцію?
помилки виправив і функція відкомпілювалась

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;

3

Re: створити функцію (Оракл)

В першому повідомленні була елементарна помилка - двічі end, при цьому begin лише один раз (ну це ви вже виправили).
В другому повідомленні елементарне питання з такою ж елементарною відповіддю - виконайте сам селект і тоді запустіть функцію, щоб переконатися що результат той самий.

4

Re: створити функцію (Оракл)

переробив функцію і появились помилки:

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;

помилки:
5/1       PL/SQL: SQL Statement ignored
5/55      PL/SQL: ORA-00947: not enough values
Errors: check compiler log

5

Re: створити функцію (Оракл)

select sh.NAME_SHOP,count(sh.ID_SHOP)
into count_shops

Тут ви намагаєтеся вмістити значення двох колонок в одну змінну.

6

Re: створити функцію (Оракл)

тепер коректно працює

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;

7

Re: створити функцію (Оракл)

а ще таку функію:
Многооператорну функцію, яка повертає кількість проданих книг по кожній із тематик і в розрізі кожного магазину.

create or replace function sale_by_theme
return int is
Saled int;
begin
select count(*) into 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;
return Saled;
end;

вона видає кількість продажів (31) з таблиці Продажі (Sales), тобто це не те що потрібно.
Як зробити щоб виводило назву теми, назву магазину і кількість книжок? (3 поля)

8

Re: створити функцію (Оракл)

cам по собі Селект працює, але при компіляції функції видає помилку:
помилка:

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;

9

Re: створити функцію (Оракл)

Як зробити щоб виводило назву теми, назву магазину і кількість книжок? (3 поля)

Для виведення більше ніж одного значення потрібна не просто функція (бо звичайна функція повертає лише одне значення), а наприклад таблична функція (pipelined function).

10

Re: створити функцію (Оракл)

а як це синтаксично описати?

11

Re: створити функцію (Оракл)

Для початку пропоную ґуґлити "oracle pipelined function", при нагоді покажу приклад, з телефону незручно.

12

Re: створити функцію (Оракл)

Можна приклад???

13

Re: створити функцію (Оракл)

Спершу покажіть що наґуґлили...

14

Re: створити функцію (Оракл)

frz написав:

Спершу покажіть що наґуґлили...

Чекатиму, а поки покажу частину коду. Весь код покажу після того, як побачу хоча би спробу розібратися самостійно з боку автора.

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

(скажімо, я хочу вивести 4 колонки, при цьому значення четвертої колонки буде задано вхідним параметром)

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;
/

(це я показав заголовок - header пакету, а найцікавіше, тобто тіло пакету покажу після певних дій топікстартера)

15

Re: створити функцію (Оракл)

create type TypeTestObject as object
(
 object_name varchar2(500),
 object_id  number,
 object_type varchar2(10)
);
/*Создаем коллекцию типа nested table*/ 
create type TypeTestList as table of TypeTestObject;
create or replace function sale_by_theme(pObject_type in varchar2)
return TypeTestList pipelined as
Saled int;
begin
select 
t.NAME_THEME,sh.name_shop,
sum(s.quantity) into 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;

помилка:

PLS-00103: Encountered the symbol "CREATE" 
Errors: check compiler log

16 Востаннє редагувалося frz (14.07.2020 14:04:47)

Re: створити функцію (Оракл)

тіло пакету і приклад виклику:

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))

17

Re: створити функцію (Оракл)

цей код підлягає під виконання даного завдання
Функцію, яка повертає список книг, які відповідають набору критеріїв (ім'я та прізвище автора, тематика), і відсортовані за прізвищем автора в зазначеному в 4-му параметрі напрямку.

frz написав:

тіло пакету і приклад виклику:

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))

18

Re: створити функцію (Оракл)

тобто наскільки я розумію
t1,t2,t3 - назви полів.
nvl(parT4,-1) - як це розуміти? v_obj tshow_type; -і це?

19 Востаннє редагувалося frz (14.07.2020 19:03:19)

Re: створити функцію (Оракл)

nvl(parT4,-1) - як це розуміти?

Це все можна і треба спершу ґуґлити а тоді питати, щоб виглядати менш непрофесійно... :(

Наприклад:

мене цікавить даний елемент використаний в коді, дядько ґуґл каже що це означає [тут описуєте наґуґлене], підкажіть чи саме це ви мали на увазі...

На стаковерфлоу вас за таке питання вже так би замінусували, що можна створювати нового користувача...

Ок, nvl в даному випадку вжито на випадок, якщо значення вхідного параметру parT4 становить null - тоді замість null буде відображатися -1. Схоже на coalesce, однак є відмінності: https://lmgtfy.com/?q=nvl+vs+coalesce&iie=1

v_obj tshow_type; -і це?

Тут v_obj вказує, що результат повинен відповідати попередньо оголошеному в заголовку пакету типу tshow_type. В даному випадку йдеться про те, що кожна колонка вже має свій попередньо заданий тип (і розмір) даних.

цей код підлягає під виконання даного завдання
Функцію, яка повертає список книг, які відповідають набору критеріїв (ім'я та прізвище автора, тематика), і відсортовані за прізвищем автора в зазначеному в 4-му параметрі напрямку.

З цих двох речень я розумію, що ви вимагаєте аби мій приклад відповідав умовам вашої задачі. Пробачте, але це вже розжований приклад і тепер вам доведеться його використати для вашої задачі самостійно. Принаймні спершу покажіть свої спроби. На цьому форумі лінивим студентам готові рішення не дають...

20

Re: створити функцію (Оракл)

Та ні! ви мене неправильно зрозуміли.
Я мав на увазі, що для розв'язання завдання можна використати той приклад, який ви написали.