PL SQL задачки, упражнения, у кого есть?

← →
BorisUK (2007-01-25 08:20) [0]

...из книг по сабжу, или с курсов. Скиньте плз у кого что есть, интересно потренироваться... В инете ищу, попадаю тока на магазины книжные или программы курсов. а мне ж не много надо, хоть несколько :)

← →
evvcom © (2007-01-25 09:20) [1]

Ну ссылок у меня нет, а так кинуть пару навскидку могу. Тебе попроще или посложнее?
1. Попроще. Одним селектом с параметром N вернуть N записей в наборе. Неважно, что в столбцах, главное N записей. Это, конечно, не PLSQL, а чистый оракловый SQL, но тем не менее, если не знаешь, есть над чем подумать.
2. Посложнее. Допустим надо написать "табель учета рабочего времени". Имеем список работников. Имеем табличку с временами выхода сотрудников на работу (поля "с" и "по"). Реализовать обработку вставки/обновления записи таким образом, чтобы работало не только через какую-либо хранимую процедуру, но и при прямой вставке/апдейте таблицы через удобные средства разработки (PL/SQL Developer, TOAD и пр., т.е. через прямые insert/update). Чтобы это работало с учетом многопользовательской работы. Т.е. ошибкой является запись с диапазоном времени пересекающимся с другим диапазоном, уже присутствующим в таблице, для одного и того же работника. Учесть также, что данные в одно и то же время могут вводить 2 разных табельщика (здесь маловероятно, но в другой задаче вполне такое возможно).

Пока такое решишь, с очень многим придется по пути разобраться. Дерзай.
Результаты для проверки потом можешь выложить тут. Обсудим.

P.S. Я с этими задачами уже разобрался, так что решение точно имеется. :)

← →
BorisUK (2007-01-25 10:59) [2]

1 rownum
2 trigger
да? :)
А можно посложнее.. с курсов подготовки по pl sql и из книг по этой теме

← →
Sergey13 © (2007-01-25 11:07) [3]

> [0] BorisUK (25.01.07 08:20)

Готовых я тоже не видел.
Посоветовал бы взять свою уже готовую задачу и реализовать какую либо ее часть на сервере. И практика и что-то полезное может получиться.

← →
evvcom © (2007-01-25 11:08) [4]

> [2] BorisUK (25.01.07 10:59)

Что rownum? Что trigger? Это уже рабочие запросы/программы? Нет. Ответ неверный.
Если 1-й действительно попроще и пишется одним селектом, можно туда и rownum прикрутить, то это еще не значит, что ответ тобою уже дан. Что же касается 2-го, то такое и на курсах вряд ли дают. За все ручаться не буду, я был только на двух, но, думаю, что не дают. И задача эта будет посложнее, чем элементарные примерчики в методичках. Это задачка из моей реальной практики. Триггер? Да есть там триггер, и не один, и кроме триггеров еще и пакет используется. Только это все слова, а код где?

← →
alles © (2007-01-25 11:11) [5]

А можно посложнее.
сделай один MERGE только в нем использовать только одну таблицу. Т.е, Если нету запись в таблице, добавь ее, а если есть делай UPDATE

← →
evvcom © (2007-01-25 11:13) [6]

> [5] alles © (25.01.07 11:11)

Ну так для этого достаточно просто в документацию по синтаксису MERGE заглянуть.

← →
evvcom © (2007-01-25 11:16) [7]

Могу еще подкинуть.
3. Напиши VIEW параметризированный :)

← →
alles © (2007-01-25 11:16) [8]

evvcom © (25.01.07 11:13) [6]
я видел в доках использование MERGE только с 2-мя таблицами, с одной нет. Может плохо искал, не знаю :)

← →
evvcom © (2007-01-25 11:23) [9]

> [8] alles © (25.01.07 11:16)

Одну таблицу изменяешь, а вторую ты имеешь в виду в подзапросе? Так в этот подзапрос ты можешь запхнуть, что хочешь. Можешь ту же таблицу, но, наверное, как-то данные изменить, иначе и обновлять/вставлять будет нечего :) ; можешь данные из другой таблицы; можешь чисто переданные параметры, но тогда таблица вторая все равно нужна будет: хоть DUAL, хоть та же с rownum, хоть виртуальная nested table. Или ты все ж что-то другое имеешь ввиду?

← →
alles © (2007-01-25 11:38) [10]

← →
shikitomedo2 © (2007-01-25 11:45) [11]

на скуле была неплохая веточка
http://www.sql.ru/forum/actualthread.aspx?bid=3&tid=350768&pg=1

← →
evvcom © (2007-01-25 12:08) [12]

> [10] alles © (25.01.07 11:38)
> сделать UPDATE на поле UPDATEDBY

Не совсем понял, что ты имел ввиду. Понял так:
MERGE INTO tbl USING (SELECT :userid as userid, :roleid as roleid FROM dual) sq ON (tbl.userid = sq.userid AND tbl.roleid = sq.roleid) WHEN MATCHED THEN UPDATE SET updatedby = :updatedby /* ну и видимо UPDATED тоже надо? Тогда , updated = sysdate */ WHEN NOT MATCHED THEN INSERT (userid, roleid, updated, updatedby) VALUES (:userid, :roleid, sysdate, :updatedby)
В подзапросе используется вторая таблица DUAL, если для тебя важно, чтобы можно было только ту же tbl, то можно ... FROM tbl WHERE rownum = 1, но надо быть уверенным, что в этой tbl есть хотя бы одна запись :) Лучше все ж DUAL.

← →
BorisUK (2007-01-25 12:08) [13]

CREATE VIEW myview (parameter <datatype>)
AS SELECT * FROM <table_name> WHERE <table_name.column_name> > = parameter

MERGE INTO <table_name>
WHEN MATCHED THEN UPDATE
...
WHEN NOT MATCHED THEN INSERT
...
но в 8i этого еще нет приходится вывертываться считая предварительно count(*) into n where условие всталяемой в таблицу записи
и потом if n=0 then insert else update endif;
можно также проверять sql%found sql%rowcount после запроса.. и тоже if...

← →
alles © (2007-01-25 12:14) [14]

я по другому сделал, работает и когда нету данных. Только не знаю если можно по проще сделать

merge into pay_rights p
USING (select * from
(select userid, roleid,rownum from pay_rights where userid=UsrId and roleid = RolId union all select -1, -1,rownum from dual ) where rownum=1)
on (userid =UsrId and roleid =RolId)
when matched then update set UPDATEDBY =UpdateBy
when not matched then insert(userid,roleid,updated,updatedby)values(UsrId,RolId,sysdate,UpdateBy);

UsrId, RolId, UpdateBy - переменные из процедуры

← →
evvcom © (2007-01-25 12:18) [15]

> [13] BorisUK (25.01.07 12:08)
> CREATE VIEW myview (parameter <datatype>)

Ух ты какой шустрый! Ты издеваешься? Ты где такое нарыл? В 9-ке точно такого нет. В 10-ке сейчас в доке посмотрел, тоже не нашел, а про 8-ку вообще молчу. Видимо, вопрос ты точно задал, чтобы потрындеть и не более того.

> но в 8i этого еще нет приходится вывертываться считая предварительно
> count(*) into n where условие всталяемой в таблицу записи
> и потом if n=0 then insert else update endif;
> можно также проверять sql%found sql%rowcount после запроса..
> и тоже if...

Таким образом, остается ненулевая вероятность, что между if и insert другой пользователь успеет вставить такую запись. От незадачка!

← →
evvcom © (2007-01-25 12:28) [16]

> [14] alles © (25.01.07 12:14)

У меня короче и универсальнее :)
Ты, во-первых, лишний раз дергаешь pay_rights в своем подзапросе. Хорошо, если индекс составной построен по userid и roleid, иначе будет full scan скорее всего. Во-вторых, dual ты все же тоже трогаешь, а это уже вторая таблица :-) В третьих rownum как поле в select тебе тоже нафиг не нужен. И, в-четвертых, в условии ты не говорил, что в твоей pay_rights не может быть записи (userid,roleid)=(-1,-1)
Перепиши по моему и оцени.

← →
alles © (2007-01-25 12:56) [17]

evvcom © (25.01.07 12:28) [16]
а как будет работать твой мерже в случае когда пара USERID и ROLEID должны быть уникальны в таблице? Т.е 2 строки с USERID=1 и ROLEID=3 не могут быть в таблице

← →
evvcom © (2007-01-25 13:08) [18]

> [17] alles © (25.01.07 12:56)

А что тебя смущает? Об этом WHEN MATCHED позаботится. Ты смотри условие в USING ON, там есть AND

← →
evvcom © (2007-01-25 13:13) [19]

> [17] alles © (25.01.07 12:56)
> в случае когда пара USERID и ROLEID должны быть уникальны
> в таблице

Кстати, на это уникальный констрейнт вешается. А мой вариант дубликат не вставит. Только если commit затянется, а другой юзер выполнит тот же MERGE и успеет COMMIT. Но от этого и твой вариант не застрахован. А страховка - это соответствующий констрейнт.

← →
alles © (2007-01-25 13:16) [20]

2 evvcom © (25.01.07 13:08) [18]
в SELECT :userid as userid, :roleid as roleid FROM dual
roleid - invalid identifier. Наверно он не находит в дуал их

← →
alles © (2007-01-25 13:17) [21]

А страховка - это соответствующий констрейнт.
констрейнт есть :)

ой, простите 20 пост не читать :)

> [20] alles © (25.01.07 13:16)
> Наверно он не находит в дуал их

:) В дуале только DUMMY и есть. Я написал :roleid - это параметр. Если у тебя ХП, подставь вместо него свой параметр RolId.

все прочитали :) щас как начнем измываться :))

← →
BorisUK (2007-01-25 14:52) [26]

/*Parameterized View */
create or replace view myview
as select * from alb_temp_cell t where (t.accid = userenv("client_info") or userenv("client_info") is null);
....
begin
dbms_application_info.set_client_info("235937486");
end;
....
select * from myview t;

> [26] BorisUK (25.01.07 14:52)

Ну с одним параметром выкрутился. ЗачОт. А для N параметров есть идеи?

← →
BorisUK (2007-01-26 06:30) [28]

> evvcom © (25.01.07 15:25) [27]

будет ли засчитан вариант с создание пакета контекстных переменных?
завязаться на них из вьюхи и устанавливать их значение, при необходимости из сессии (или при её инициализации)

если есть какойто не обходной вариант.. передачи параметров во вьюху напрямую, или еще какойто вариант, то прошу просвятить :)

← →
BorisUK (2007-01-26 08:52) [29]

всетаки если можно, ктонить вспомнит упражнения из каниг по pl sql :)
спрашиваю за неимением самих книг... :)
Обещаю обязательно куплю как тока выберусь в магазин и выберу деньги, но сейчас нету.

> [28] BorisUK (26.01.07 06:30)

Ну да, именно такой в инете и описывается. Хотя, думаю, возможно еще через глобальную временную таблицу аналогичным образом. Но не пробовал так :)

> если есть какойто не обходной вариант..

видимо, нет :(

> [29] BorisUK (26.01.07 08:52)
> всетаки если можно, ктонить вспомнит упражнения из каниг
> по pl sql

А чем 1 и 2 из [1] не устроили? Ну первое действительно простое, если знаешь идею. А второе, даже зная идею, сразу не напишешь так, как написано в задании, с соблюдением всех условий и ограничений. Там много всего вспомнить/изучить придется.

PL SQL задачки, упражнения, у кого есть? Найти похожие ветки