Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими

• Название: Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
• Анкор: Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных.pdf
• Дата: 16.04.2018
• Размер: 11.62 Mb.
• Имя файла: Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных.pdf
• Тип: Литература; #18127
• страница: 26 из 28

Глава 23
Некоторые приложения
базы данных "
UCHEB"
Электронные версии текстов, приведенных далее функций и пакетов можно получить непосредственно из базы данных "UCHEB", выгрузка которой рас- положена на компакт-диске. В приложении А приведены процедуры загрузки этой базы данных и других приложений на персональный компьютер.
23.1. Функции Человек
и
Decline
Функция
Человек
предназначена для вывода фамилии, имени и отчества че- ловека в задаваемом падеже (
padej
).
CREATE OR REPLACE FUNCTION Человек(chel NUMBER, padej VARCHAR2, priz IN NUMBER DEFAULT 0,nach DATE DEFAULT SYSDATE) RETURN VARCHAR2 IS
-- вывод фамилии, имени, отчества или фамилии и инициалов
-- человека на указанную дату в заданном падеже (padej) fio VARCHAR2(50) := ' '; t_KNR VARCHAR2(60); test NUMBER(9);
BEGIN
-- Проверяем, можно ли склонять его ФИО
SELECT COUNT(*) INTO t_KNR FROM н_люди l WHERE ид = chel
AND (UPPER(l.место_рождения) LIKE 'КНР%' OR UPPER(l.место_рождения)
LIKE 'ВЬЕТНАМ%');
-- Если иминительный падеж или склонять нельзя
IF padej = 'И' OR t_KNR > 0 THEN
IF priz = 0 THEN
SELECT UPPER(RTRIM(фамилия))||' '||DECODE(RTRIM(имя),'.',NULL,
' ',NULL,RTRIM(имя))||' '||DECODE(RTRIM(отчество),'.',NULL,
' ',NULL,RTRIM(отчество))

Часть
VII.
Пример создания базы данных "UCHEB"
404
INTO fio FROM н_люди WHERE ид = chel;
-- Для формы с подписями
ELSIF priz = 9 THEN
SELECT DECODE(SUBSTR(имя,1,1),'.',NULL,' ',NULL, NULL, NULL,
SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.',
NULL,' ',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')||Фамилия
INTO fio FROM н_люди WHERE ид = chel;
ELSIF priz = 1 THEN
SELECT Фамилия||' '||DECODE(SUBSTR(имя,1,1),'.',NULL,' ',NULL,NULL,
NULL, SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.'
,NULL,' ', NULL, NULL, NULL, SUBSTR(отчество,1,1)||'.')
INTO fio FROM н_люди WHERE ид = chel;
ELSIF priz = 2 THEN
SELECT RTRIM(фамилия)||' '||DECODE(RTRIM(имя),'.',NULL,' ',NULL,
RTRIM(имя))||' '||DECODE(RTRIM(отчество),'.',NULL,' ',NULL,
RTRIM(отчество))
INTO fio FROM н_люди WHERE ид = chel;
ELSE
SELECT UPPER(RTRIM(фамилия))||' '|| DECODE(SUBSTR(имя,1,1),'.' ,
NULL, ' ',NULL, NULL, NULL, SUBSTR(имя,1,1)||'.')||
DECODE(SUBSTR(отчество,1,1),'.',NULL,' ', NULL, NULL, NULL,
SUBSTR(отчество,1,1)||'.')
INTO fio FROM н_люди WHERE ид = chel;
END IF;
ELSE
-- Проверяем, есть ли человек в таблице Н_ЛЮДИ_ПО_ПАДЕЖАМ, если есть,
-- то все берем из нее, если нет, то используем функцию DECLINE
SELECT COUNT(*) INTO test FROM н_люди_по_падежам lp
WHERE lp.члвк_ид = chel AND lp.падеж = padej;
IF test > 0 THEN
IF priz = 0 THEN
SELECT UPPER(RTRIM(фамилия))||' '||DECODE(RTRIM(имя),'.',
NULL,' ', NULL, RTRIM(имя))||' '||DECODE(RTRIM(отчество),'.',
NULL,' ',NULL,RTRIM(отчество))
INTO fio FROM н_люди_по_падежам lp
WHERE lp.члвк_ид = chel AND lp.падеж = padej;
-- Для формы с подписями
ELSIF priz = 9 THEN
SELECT DECODE(SUBSTR(имя,1,1),'.',NULL,' ', NULL, NULL, NULL,

Глава 23. Некоторые приложения базы данных "
UCHEB"
405
SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.',
NULL,' ',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')||Фамилия
INTO fio FROM н_люди_по_падежам lp
WHERE lp.члвк_ид = chel AND lp.падеж = padej;
ELSIF priz = 1 THEN
SELECT Фамилия||' '||DECODE(SUBSTR(имя,1,1),'.',NULL,' ', NULL,
NULL,NULL,SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),
'.',NULL,' ',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')
INTO fio FROM н_люди_по_падежам lp
WHERE lp.члвк_ид = chel AND lp.падеж = padej;
ELSIF priz = 2 THEN
SELECT RTRIM(фамилия)||' '||DECODE(RTRIM(имя),'.',NULL,'
',NULL,RTRIM(имя))||' '||DECODE(RTRIM(отчество),'.',NULL,'
',NULL,RTRIM(отчество))
INTO fio FROM н_люди_по_падежам lp WHERE lp.члвк_ид = chel
AND lp.падеж = padej;
ELSE
SELECT UPPER(RTRIM(фамилия))||'
'||DECODE(SUBSTR(имя,1,1),'.',NULL,'
',NULL,NULL,NULL,SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.',
NULL,' ',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')
INTO fio FROM н_люди_по_падежам lp WHERE lp.члвк_ид = chel
AND lp.падеж = padej;
END IF;
ELSE
IF priz = 0 THEN
SELECT de- cline(UPPER(RTRIM(фамилия))||'#'||DECODE(RTRIM(имя),'.',NULL,'
',NULL,RTRIM(имя))||'#'||DECODE(RTRIM(отчество),'.',NULL,'
',NULL,RTRIM(отчество)),пол,padej)
INTO fio FROM н_люди WHERE ид = chel;
-- Для формы с подписями
ELSIF priz = 9 THEN
SELECT DECODE(SUBSTR(имя,1,1),'.',NULL,'
',NULL,NULL,NULL,SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.',NULL,'
',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')||decline(Фамилия,пол,padej,'Ф')
INTO fio FROM н_люди WHERE ид = chel;
ELSIF priz = 1 THEN
SELECT decline(Фамилия,пол,padej,'Ф')||'
'||DECODE(SUBSTR(имя,1,1),'.',NULL,'
',NULL,NULL,NULL,SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.',
NULL,' ',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')

Часть
VII.
Пример создания базы данных "UCHEB"
406
INTO fio FROM н_люди WHERE ид = chel;
ELSIF priz = 2 THEN
SELECT decline(RTRIM(фамилия)||'#'||RTRIM(имя)||'#'||
RTRIM(отчество),пол,padej)
INTO fio FROM н_люди WHERE ид = chel;
ELSE
SELECT decline(UPPER(RTRIM(фамилия)),пол,padej,'Ф')||'
'||DECODE(SUBSTR(имя,1,1),'.',NULL,'
',NULL,NULL,NULL,SUBSTR(имя,1,1)||'.')||DECODE(SUBSTR(отчество,1,1),'.',
NULL,' ',NULL,NULL,NULL,SUBSTR(отчество,1,1)||'.')
INTO fio FROM н_люди WHERE ид = chel;
END IF;
END IF;
END IF;
RETURN RTRIM(LTRIM(fio));
END Человек;
/
Функция
Decline
предназначена для вывода фамилии, имени и отчества че- ловека в задаваемом падеже (
padej
).
CREATE OR REPLACE FUNCTION Decline(str in varchar2, sex in varchar2, pad in varchar2, lfm in varchar2 default 'ФИО') RETURN varchar2 IS p1 varchar2(1); p number(1); s1 varchar2(1); s number(1); x varchar2(1); l number(2,0); ul varchar2(1) := 'N'; uf varchar2(1) := 'N'; um varchar2(1) := 'N'; lname varchar2(35); fname varchar2(35); mname varchar2(35); fullname varchar2(100) := ltrim(rtrim(str)); pos number; lfm2 varchar2(3) := upper(substr((lfm),1,3));
FUNCTION UppLow(st1 in varchar2, st2 in varchar2, st3 in varchar2)
RETURN varchar2 IS
BEGIN if st2 <> substr(st1,-1) then

Глава 23. Некоторые приложения базы данных "
UCHEB"
407
RETURN UPPER(st3); else
RETURN st3; end if;
END UppLow;
--
FUNCTION complast(s in varchar2, t in varchar2) RETURN BOOLEAN IS
BEGIN if length(s) < length(t) then RETURN FALSE; end if; if substr(lower(s),-length(t)) = t then
RETURN TRUE; else
RETURN FALSE; end if;
END complast;
--
PROCEDURE change(s in out varchar2, n in number
,pzROD in varchar2
,pzDAT in varchar2
,pzVIN in varchar2
,pzTVO in varchar2
,pzPRE in varchar2) IS
BEGIN if p = 1 then null; else
SELECT substr(s,1,length(s) - n) ||
DECODE(p,2,pzROD,3,pzDAT,4,pzVIN,5,pzTVO,6,pzPRE) INTO s
FROM DUAL; end if;
END change;
--
PROCEDURE prepare(fnm in out varchar2, lfm2 in out varchar2) IS pp varchar2(1) := substr(lfm2,1,1); name varchar2(35);
BEGIN pos := instr(fnm, '#'); if pos > 0 then name := substr(fnm,1,pos - 1);

Часть
VII.
Пример создания базы данных "UCHEB"
408
fnm := ltrim(substr(fnm,pos + 1)); pos := instr(fnm, '#'); else name := fnm; fnm := null; end if; if pp = 'Ф' then lname := name; lfm2 := replace(lfm2,'Ф'); elsif pp = 'И' then fname := name; lfm2 := replace(lfm2,'И'); elsif pp = 'О' then mname := name; lfm2 := replace(lfm2,'О'); end if;
END;
BEGIN if fullname is null then RETURN null; end if; if substr(lfm2,1,1) not in ('Ф','И','О') then RETURN str; end if; if NVL(substr(lfm2,2,1),'Ф') not in ('Ф','И','О') then RETURN str; end if; if NVL(substr(lfm2,3,1),'Ф') not in ('Ф','И','О') then RETURN str; end if;
LOOP if fullname is not null then prepare(fullname,lfm2); else exit; end if;
END LOOP; l := length(lname); p1 := upper(substr(pad,1,1));
SELECT DECODE(p1,'И',1,'Р',2,'Д',3,'В',4,'Т',5,'П',6,0) INTO p FROM DUAL; if p = 0 then
BEGIN p := to_number(p1);
EXCEPTION
WHEN OTHERS THEN RETURN str;
END;

Глава 23. Некоторые приложения базы данных "
UCHEB"
409
end if; s1 := upper(substr(sex,1,1));
SELECT DECODE(s1,'М',1,'M',1,'Ж',0,'F',0,-1) INTO s FROM DUAL; if s = -1 then RETURN str; end if; if lname is not null then -- фамилия if UPPER(lname) = lname then ul := 'Y'; end if; x := lower(substr(lname,-1)); if s = 1 then -- мужчины if x not in ('о','е','у','ю','и','х','э','ы') then if x = 'в' then change(lname,0,'а','у','а','ым','е'); elsif x = 'н' and complast(lname,'ин') then change(lname,0,'а','у','а','ым','е'); elsif x in ('б','г','д','ж','з','л','м','н','п'
,'р','с','т','ф','ч','ш','щ') then change(lname,0,'а','у','а','ом','е');
ELSIF x = 'ц' THEN
IF lower(substr(lname,-2)) = 'ец' THEN change(lname,2,'ца','цу','ца','цом','цу');
ELSE change(lname,0,'а','у','а','ом','у');
END IF; elsif x = 'я' and not(complast(lname,'ия') or complast(lname,'ая')) then change(lname,1,'и','е','ю','ей','е'); elsif x = 'а' and not(complast(lname,'иа') or complast(lname,'уа')) then change(lname,1,'и','е','у','ой','е'); elsif x = 'ь' then if (complast(lname,'чь') or complast(lname,'щь')) then change(lname,1,'а','у','а','ем','е');
ELSE change(lname,1,'я','ю','я','ем','е');
END IF; elsif x = 'к' then if l > 4 and complast(lname,'ок') then change(lname,2,'ка','ку','ка','ком','ке'); elsif l > 4 and (complast(lname,'лек') or complast(lname,'рек')) then

Часть
VII.
Пример создания базы данных "UCHEB"
410
change(lname,2,'ька','ьку','ька','ьком','ьке'); else change(lname,0,'а','у','а','ом','е'); end if; elsif x = 'й' then if l > 4 then if (complast(lname,'ский') or complast(lname,'цкий')) then change(lname,2,'ого','ому','ого','им','ом'); elsif complast(lname,'ой') then change(lname,2,'ого','ому','ого','им','ом'); elsif complast(lname,'ей') AND NOT complast(lname,'вей') then change(lname,2,'ея','ею','ея','еем','ее'); elsif complast(lname,'эй') then change(lname,2,'эя','эю','эя','эем','эе'); elsif complast(lname,'ый') then change(lname,2,'ого','ому','ого','ым','ом'); elsif complast(lname,'ий') then
IF complast(lname,'чий') THEN change(lname,2,'его','ему','его','им','ем');
ELSIF complast(lname,'лий') THEN change(lname,1,'я','ю','я','ем','е');
ELSE change(lname,2,'ого','ому','ого','им','ом');
END IF; elsif substr(lname,-3) in
('рий','жий','лий','вий','дий','бий','гий','зий','мий','ний','пий','сий',
'фий','хий') then change(lname,1,'я','ю','я','ем','и'); end if; else change(lname,1,'я','ю','я','ем','е'); end if; end if; end if; elsif s = 0 then -- женщины if lower(substr(lname,-3)) in
('ова','ева','ина','ена','ына','ѐва') then change(lname,1,'ой','ой','у','ой','ой'); elsif complast(lname,'ая') then change(lname,2,'ой','ой','ую','ой','ой');

Глава 23. Некоторые приложения базы данных "
UCHEB"
411
end if; end if; end if; if fname is not null then -- имя if UPPER(fname) = fname then uf := 'Y'; end if; x := lower(substr(fname,-1)); if s = 1 then -- мужчины if x not in ('о','е','и','у') then if x in ('б','в','г','д','ж','к','м','н','п','р','с','т'
,'ф','х','ц','ч','ш','щ') then
IF lower(fname) = 'лев' THEN change(fname,3,'Льва','Льву','Льва','Львом','Льве');
ELSE change(fname,0,'а','у','а','ом','е');
END IF; elsif x = 'я' then if complast(fname,'ья') then change(fname,1,'и','е','ю','ей','е'); else change(fname,0,'и','е','ю','ей','е'); end if; elsif x = 'й' then if complast(fname,'ай') then change(fname,1,'я','ю','я','ем','е'); else if complast(fname,'ей') then change(fname,1,'я','ю','я','ем','е'); else change(fname,1,'я','ю','я','ем','и'); end if; end if; elsif x = 'ь' then change(fname,1,'я','ю','я','ем','е'); elsif x = 'а' then
IF complast(fname,'та') THEN change(fname,1,'ы','е','у','ой','е');
-- типа Данила
ELSIF complast(fname,'ла') THEN change(fname,1,'ы','е','у','ой','е');

Часть
VII.
Пример создания базы данных "UCHEB"
412
ELSE change(fname,1,'и','е','у','ей','е');
END IF; elsif x = 'л' then if complast(fname,'авел') then change(fname,2,'ла','лу','ла','лом','ле'); else change(fname,0,'а','у','а','ом','е'); end if; end if; end if; elsif s = 0 then -- женщины if x = 'а' then if lower(substr(fname,-2)) in
('га','ха','ка','ша','ча','ща','жа') then change(fname,1,'и','е','у','ой','е'); else change(fname,1,'ы','е','у','ой','е'); end if; elsif x = 'я' then if complast(fname,'ия') then change(fname,1,'и','и','ю','ей','и'); else change(fname,1,'и','е','ю','ей','е'); end if; elsif x = 'ь' then if complast(fname,'вь') then change(fname,1,'и','и','ь','ью','и'); else change(fname,1,'и','и','ь','ью','ье'); end if; end if; end if; end if; if mname is not null then -- отчество if UPPER(mname) = mname then um := 'Y'; end if; x := lower(substr(mname,-1)); if s = 1 then -- мужчины if x = 'ч' then

Глава 23. Некоторые приложения базы данных "
UCHEB"
413
change(mname,0,'а','у','а','ем','е'); end if; elsif s = 0 then -- женщины if x = 'а' then change(mname,1,'ы','е','у','ой','е'); end if; end if; end if;
-- окончательная конкатенация lfm2 := upper(substr((lfm),1,3)); pos := 1;
LOOP if pos > 1 then fullname := fullname || ' '; end if;
SELECT fullname ||
DECODE(substr(lfm2,pos,1),'Ф',DECODE(ul,'Y',UPPER(lname),lname)
,'И',DECODE(uf,'Y',UPPER(fname),fname)
,'О',DECODE(um,'Y',UPPER(mname),mname))
INTO fullname FROM DUAL; pos := pos + 1; if pos > length(lfm2) then EXIT; end if;
END LOOP;
RETURN fullname;
END Decline;
23.2. Пакет для просмотра успеваемости
Фрагмент пакета
pk_dek_ocenki
,
предназначеного для обеспечения работы деканатов.
Для работы этого пакета были созданы три рабочих таблицы, не входящих в инфологическую модель (см. рис. 21.7):
CREATE TABLE НТ_ОЦЕНКИ_СТУДЕНТА
( ДИСЦИПЛИНА VARCHAR2(200),
СЕМЕСТР NUMBER(2),
КОНТР VARCHAR2(8),
ОЦЕНКА VARCHAR2(8),
УЧЕБНЫЙ_ГОД CHAR(9),

Часть
VII.
Пример создания базы данных "UCHEB"
414
ВР_ИД NUMBER(9),
СЭС_ИД NUMBER(9),
ВЕД_ИД NUMBER(9) );
CREATE TABLE НТ_ОЦЕНКИ_КАРТОЧЕК_СТУД
( ЧЛВК_ИД NUMBER(9),
ГРУППА VARCHAR2(4),
КУРС NUMBER(1),
СЕМЕСТР NUMBER(2),
ДИСЦИПЛИНА VARCHAR2(20),
ЧАСЫ VARCHAR2(20),
ЭКЗ VARCHAR2(4),
ЗАЧ VARCHAR2(4),
КР VARCHAR2(4),
ЭСТ_ИД NUMBER(9),
ЭКЗ_ИД NUMBER(9),
ЗАЧ_ИД NUMBER(9),
КР_ИД NUMBER(9) );
CREATE TABLE НТ_СПРАВКА_О_ОЦЕНКАХ
( СЕМЕСТР NUMBER(2),
ДИСЦИПЛИНА VARCHAR2(200),
ЧАСЫ VARCHAR2(8),
КОНТР VARCHAR2(8),
ОЦЕНКА VARCHAR2(8),
ДАТА DATE,
ЭСТ_ИД NUMBER(9),
СЭС_ИД NUMBER(9),
БУКВА VARCHAR2(20) );
Спецификация пакета.
CREATE OR REPLACE PACKAGE pk_dek_ocenki IS
/* Заполняет временную таблицу НТ_ОЦЕНКИ_СТУДЕНТА его оценками, полу- ченными за все обучение в университете */
PROCEDURE оценки_студента (п_члвк_ид IN NUMBER, п_вид_обуч_ид IN
NUMBER);
/* Процедура заполняет временную таблицу НТ_СПРАВКА_О_ОЦЕНКАХ оценками студента */
PROCEDURE справка_о_оценках (п_члвк_ид IN NUMBER);
/* Процедура заполняет таблицу НТ_ОЦЕНКИ_КАРТОЧЕК_СТУД выписками оценок для личных карточек студентов */
PROCEDURE оценки_для_карточек_студентов

Глава 23. Некоторые приложения базы данных "
UCHEB"
415
(п_учгод IN VARCHAR2, п_курс IN NUMBER, п_факультет IN NUMBER);
/********************************************************************/
/* Функция возвращает дату проведения итогового контроля */
FUNCTION ведомости_дата (п_группа VARCHAR2, п_сэс_ид NUMBER) RETURN
DATE;
/* Функция возвращает номер семестра по дате */
FUNCTION номер_семестра (п_дата DATE) RETURN NUMBER;
END pk_dek_ocenki;
Тело пакета.
CREATE OR REPLACE PACKAGE BODY pk_dek_ocenki IS
/* Заполняет временную таблицу НТ_ОЦЕНКИ_СТУДЕНТА его оценками, полученными за все обучение в университете */
PROCEDURE оценки_студента (п_члвк_ид NUMBER, п_вид_обуч_ид NUMBER) AS п_учебный_год CHAR(9); п_курс NUMBER(2); п_сем_осень DATE; п_сем_весна DATE;
BEGIN
/* Вставляем все дисциплины, которые должен был сдать студент по цепочке планов */
DELETE FROM нт_оценки_студента;
INSERT INTO нт_оценки_студента (дисциплина,семестр,контр,учебный_ год,вр_ид,сэс_ид)
SELECT дис.наименование, эст.номер_семестра, вр.аббревиатура,план.учебный_год,сэс.вр_ид,сэс.ид
FROM н_планы план, н_строки_планов спл, н_элементы_строк эст, н_содержания_элементов_строк сэс, н_дисциплины дис, н_виды_работ вр
WHERE план.ид = спл.план_ид
AND спл.ид = эст.спл_ид
AND эст.ид = сэс.эст_ид
AND спл.дис_ид = дис.ид
AND сэс.вр_ид = вр.ид
AND спл.ком_ид <> 5
AND сэс.вр_ид IN (5,6,7,8)
AND план.ид IN
(SELECT план.ид FROM н_планы план
CONNECT BY PRIOR план.план_ид = план.ид
START WITH план.ид =

Часть
VII.
Пример создания базы данных "UCHEB"
416
(SELECT план.ид FROM н_ученики учн, н_планы план, н_направления_специал нап
WHERE план.ид = учн.план_ид
AND план.напс_ид = нап.ид
AND учн.члвк_ид = п_члвк_ид
AND учн.вид_обуч_ид = п_вид_обуч_ид
AND нап.квал_ид <> 5
AND признак IN ('обучен','продлен')
AND состояние = 'утвержден'
AND начало =
(SELECT max(начало) FROM н_ученики
WHERE признак IN ('обучен','продлен')
AND состояние = 'утвержден'
AND члвк_ид = учн.члвк_ид
AND вид_обуч_ид = учн.вид_обуч_ид)));
-- Определяем текущий учебный год
SELECT учебный_год INTO п_учебный_год
FROM н_учебные_года WHERE sysdate BETWEEN начало AND конец;
-- Определяем текущий план студента
BEGIN
SELECT план.курс INTO п_курс
FROM н_ученики учн, н_планы план
WHERE учн.план_ид = план.ид
AND члвк_ид = п_члвк_ид
AND вид_обуч_ид = п_вид_обуч_ид
AND sysdate BETWEEN начало AND конец
AND признак IN ('обучен','проден')
AND состояние = 'утвержден'; п_сем_осень := to_date('01.01.'||substr(п_учебный_год,6,9),'DD.MM.YYYY'); п_сем_весна := to_date('01.06.'||substr(п_учебный_год,6,9),'DD.MM.YYYY');
-- Удаляем дисциплины весеннего семестра
IF sysdate <= п_сем_весна THEN
DELETE FROM нт_оценки_студента
WHERE учебный_год = п_учебный_год
AND семестр = 2*п_курс;
END IF;
-- Удаляем дисциплины осеннего семестра
IF sysdate <= п_сем_осень THEN
DELETE FROM нт_оценки_студента

Глава 23. Некоторые приложения базы данных "
UCHEB"
417
WHERE учебный_год = п_учебный_год
AND семестр = 2*п_курс-1;
END IF;
EXCEPTION
WHEN no_data_found THEN null;
END;
-- Проставляем оценки студента
UPDATE нт_оценки_студента t
SET оценка = (SELECT оценка
FROM н_ведомости
WHERE члвк_ид = п_члвк_ид
AND сэс_ид = t.сэс_ид
AND состояние = 'актуальна'
AND дата =
(SELECT max(дата)
FROM н_ведомости
WHERE члвк_ид = п_члвк_ид
AND сэс_ид = t.сэс_ид
AND состояние = 'актуальна')), вед_ид = (SELECT ид
FROM н_ведомости
WHERE члвк_ид = п_члвк_ид
AND сэс_ид = t.сэс_ид
AND состояние = 'актуальна'
AND дата =
(SELECT max(дата)
FROM н_ведомости
WHERE члвк_ид = п_члвк_ид
AND сэс_ид = t.сэс_ид
AND состояние = 'актуальна'));
-- Вставляем оценки, не привязанные к плану
INSERT INTO нт_оценки_студента (дисциплина,семестр,контр,оценка)
SELECT дис.наименование,эст.номер_семестра,вр.аббревиатура,вед.оценка
FROM н_строки_планов спл, н_элементы_строк эст, н_содержания_элементов_строк сэс, н_дисциплины дис, н_виды_работ вр, н_ведомости вед
WHERE спл.ид = эст.спл_ид
AND эст.ид = сэс.эст_ид
AND спл.дис_ид = дис.ид

Часть
VII.
Пример создания базы данных "UCHEB"
418
AND сэс.вр_ид = вр.ид
AND спл.ком_ид <> 5
AND сэс.вр_ид IN (5,6,7,8)
AND сэс.ид = вед.сэс_ид
AND вед.состояние = 'актуальна'
AND вед.члвк_ид = п_члвк_ид
AND NOT EXISTS (SELECT * FROM нт_оценки_студента t WHERE вед_ид = вед.ид);
COMMIT;
END оценки_студента;
/* Процедура заполняет временную таблицу НТ_СПРАВКА_О_ОЦЕНКАХ оценками студента */
PROCEDURE справка_о_оценках (п_члвк_ид IN NUMBER) AS
BEGIN
DELETE FROM нт_справка_о_оценках;
-- Последние оценки по семестрам
INSERT INTO нт_справка_о_оценках
SELECT c.номер_семестра,e.наименование,'',f.аббревиатура,a.оценка,a.дата,c.ид,''
, a.буква
FROM н_ведомости a,н_содержания_элементов_строк b,н_элементы_строк c,н_строки_планов d,н_дисциплины e,н_виды_работ f
WHERE a.сэс_ид = b.ид AND b.эст_ид = c.ид AND c.спл_ид = d.ид AND d.дис_ид = e.ид AND b.вр_ид = f.ид AND члвк_ид = п_члвк_ид
AND a.дата = (SELECT max(дата) FROM н_ведомости WHERE члвк_ид = п_члвк_ид AND сэс_ид = b.ид);
-- Часы
UPDATE нт_справка_о_оценках t SET часы =
(SELECT sum(decode(вр_ид,1,объем,2,объем,3,объем,4,объем,0))||'/'||sum(decode(вр_
ид,1,объем,2,объем,3,объем,0))
FROM н_содержания_элементов_строк WHERE эст_ид = t.эст_ид);
COMMIT;
END справка_о_оценках;
/* Процедура заполняет таблицу НТ_ОЦЕНКИ_КАРТОЧЕК_СТУД выписками оценок для личных карточек студентов */
PROCEDURE оценки_для_карточек_студентов
(п_учгод IN VARCHAR2, п_курс IN NUMBER, п_факультет IN NUMBER) AS п_конец_учгода DATE;
BEGIN
-- Определяем конец учебного года

Глава 23. Некоторые приложения базы данных "
UCHEB"
419
SELECT конец
INTO п_конец_учгода
FROM н_учебные_года
WHERE учебный_год = п_учгод;
-- Вставляем шаблоны выписок без оценок
DELETE FROM нт_оценки_карточек_студ;
INSERT INTO нт_оценки_карточек_студ
(члвк_ид, группа, курс, семестр, дисциплина, часы, эст_ид)
SELECT члвк_ид, учн.группа, курс, номер_семестра, дис.короткое_имя,
SUM(decode( вр_ид,1,объем,2,объем,3,объем,4,объем,0))||'/'||
SUM(decode( вр_ид,1,объем,2,объем,3,объем,0)) Объем, эст.ид
FROM н_планы план, н_строки_планов спл, н_элементы_строк эст, н_содержания_элементов_строк сэс, н_дисциплины дис, н_ученики учн
WHERE план.ид = спл.план_ид
AND спл.ид = эст.спл_ид
AND эст.ид = сэс.эст_ид
AND спл.дис_ид = дис.ид
AND план.ид = учн.план_ид
AND учн.конец = п_конец_учгода
AND учн.состояние = 'утвержден'
AND учн.признак IN ('обучен','продлен')
AND план.отд_ид = п_факультет
AND план.учебный_год = п_учгод
AND план.курс = п_курс
GROUP BY члвк_ид,учн.группа,дис.короткое_имя,курс,номер_семестра,эст.ид;
-- Определяем сэс_ид контролей
UPDATE нт_оценки_карточек_студ окс
SET экз_ид =
(SELECT сэс.ид

Часть
VII.
Пример создания базы данных "UCHEB"
420
FROM н_содержания_элементов_строк сэс
WHERE эст_ид = окс.эст_ид
AND вр_ид = 5
AND сэс.номер_контроля = 1);
UPDATE нт_оценки_карточек_студ окс
SET зач_ид =
(SELECT сэс.ид
FROM н_содержания_элементов_строк сэс
WHERE эст_ид = окс.эст_ид
AND сэс.номер_контроля = 1
AND вр_ид = 6);
UPDATE нт_оценки_карточек_студ окс
SET кр_ид =
(SELECT сэс.ид
FROM н_содержания_элементов_строк сэс
WHERE эст_ид = окс.эст_ид
AND вр_ид IN (7,8));
-- Дисциплины, для которых не предусмотрено соответствующих контролей
UPDATE нт_оценки_карточек_студ SET экз = '-' WHERE экз_ид IS NULL;
UPDATE нт_оценки_карточек_студ SET зач = '-' WHERE зач_ид IS NULL;
UPDATE нт_оценки_карточек_студ SET кр = '-' WHERE кр_ид IS NULL;
-- Вставляем оценки студентов
UPDATE нт_оценки_карточек_студ окс
SET экз =
(SELECT DECODE(оценка,'зачет','зач',оценка)
FROM н_ведомости
WHERE члвк_ид = окс.члвк_ид
AND сэс_ид = экз_ид
AND состояние = 'актуальна'
AND оценка IN ('5','4','3','зачет','осв')
AND дата =
(SELECT max(дата)
FROM н_ведомости
WHERE члвк_ид = окс.члвк_ид
AND сэс_ид = экз_ид
AND состояние = 'актуальна'))
WHERE экз_ид IS NOT NULL;
UPDATE нт_оценки_карточек_студ окс
SET зач =

Глава 23. Некоторые приложения базы данных "
UCHEB"
421
(SELECT DECODE(оценка,'зачет','зач',оценка)
FROM н_ведомости
WHERE члвк_ид = окс.члвк_ид
AND сэс_ид = зач_ид
AND состояние = 'актуальна'
AND оценка IN ('5','4','3','зачет','осв')
AND дата =
(SELECT max(дата)
FROM н_ведомости
WHERE члвк_ид = окс.члвк_ид
AND сэс_ид = зач_ид
AND rownum = 1
AND состояние = 'актуальна'))
WHERE зач_ид IS NOT NULL;
UPDATE нт_оценки_карточек_студ окс
SET кр =
(SELECT DECODE(оценка,'зачет','зач',оценка)
FROM н_ведомости
WHERE члвк_ид = окс.члвк_ид
AND сэс_ид = кр_ид
AND состояние = 'актуальна'
AND оценка IN ('5','4','3','зачет','осв')
AND дата =
(SELECT max(дата)
FROM н_ведомости
WHERE члвк_ид = окс.члвк_ид
AND сэс_ид = кр_ид
AND состояние = 'актуальна'))
WHERE кр_ид IS NOT NULL;
COMMIT;
END оценки_для_карточек_студентов;
/* Функция возвращает дату проведения итогового контроля */
FUNCTION ведомости_дата (п_группа VARCHAR2, п_сэс_ид NUMBER) RETURN
DATE IS п_дата VARCHAR2(200);
BEGIN
SELECT сес.дата
INTO п_дата
FROM н_сессия сес,

Часть
VII.
Пример создания базы данных "UCHEB"
422
н_люди члвк, н_содержания_элементов_строк сэс, н_элементы_строк эст, н_строки_планов спл, н_планы план
WHERE члвк.ид = сес.члвк_ид
AND сес.сэс_ид = сэс.ид
AND сэс.эст_ид = эст.ид
AND спл.план_ид = план.ид
AND спл.ид = эст.спл_ид
AND (план.учебный_год, эст.номер_семестра, спл.дис_ид, эст.отд_ид, сэс.вр_ид, сэс.номер_контроля) =
(SELECT план.учебный_год, эст.номер_семестра, спл.дис_ид, эст.отд_ид, сэс.вр_ид, сэс.номер_контроля
FROM н_планы план, н_строки_планов спл, н_элементы_строк эст, н_содержания_элементов_строк сэс
WHERE план.ид = спл.план_ид
AND спл.ид = эст.спл_ид
AND эст.ид = сэс.эст_ид
AND сэс.ид = п_сэс_ид)
AND группа = п_группа;
RETURN(п_дата);
EXCEPTION
WHEN no_data_found THEN RETURN(null);
END ведомости_дата;
/* Функция возвращает номер семестра по дате */
FUNCTION номер_семестра (п_дата DATE) RETURN NUMBER IS п_номер_семестра NUMBER;
BEGIN
SELECT
DECODE(to_char(п_дата,'MM'),'09',1,'10',1,'11',1,'12',1,'01',1,'02',1,0)
INTO п_номер_семестра
FROM dual;
RETURN(п_номер_семестра);
END;
END pk_dek_ocenki;
******************************************************

Глава 23. Некоторые приложения базы данных "
UCHEB"
423
--EXECUTE pk_dek_ocenki.оценки_студента(126287,1);
--EXECUTE pk_dek_ocenki.справка_о_оценках (126287);
--EXECUTE pk_dek_ocenki.оценки_для_карточек_студентов('2007/2008', 3,
703)
--SELECT pk_dek_ocenki.ведомости_дата (3103,121951 ) FROM dual;
PK_DEK_OCENKI.ВЕДОМОСТИ_ДАТА(3
------------------------------
05.01.2005
SELECT pk_dek_ocenki.номер_семестра('10.05.2008') FROM dual;
PK_DEK_OCENKI.НОМЕР_СЕМЕСТРА('
------------------------------
0