Задача. Простановка count в цикле.

Есть таблица KK_STUDENTS с именами студентов. 3 Алексея, 2 Ивана, остальных по одному.

Проставить в цикле поле KK_STUDENTS.CNT, равное числу студентов с аналогичным именем.

Создадим таблицу:

create table KK_STUDENTS

(

name VARCHAR2(50),

cnt INTEGER

);

Заполним ее данными:

insert into KK_STUDENTS(NAME) values('Алексей');

insert into KK_STUDENTS(NAME) values('Сергей');

insert into KK_STUDENTS(NAME) values('Иван');

insert into KK_STUDENTS(NAME) values('Андрей');

insert into KK_STUDENTS(NAME) values('Иван');

insert into KK_STUDENTS(NAME) values('Алексей');

insert into KK_STUDENTS(NAME) values('Пётр');

insert into KK_STUDENTS(NAME) values('Алексей');

Напишем анонимный блок кода, который делает это. Внутри блока мы используем неявный курсор, названный z:

begin

for z in (select name, count(1) as cnt from kk_students group by name) loop

update kk_students

set kk_students.cnt = z.cnt

where kk_students.name = z.name;

end loop;

end;

Проверим результат:

select * from kk_students;

Задача. Распределение данных по условию.

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

Создадим таблицы и заполним их данными:

CREATE table kk_CLIENTS (ID integer);

insert into kk_CLIENTS(ID) values(0);

insert into kk_CLIENTS(ID) values(1);

insert into kk_CLIENTS(ID) values(2);

insert into kk_CLIENTS(ID) values(3);

insert into kk_CLIENTS(ID) values(4);

CREATE table kk_CLIENTS1 (ID integer);

CREATE table kk_CLIENTS2 (ID integer);

Напишем решающий задачу анонимный блок кода:

begin

for z in (select id from kk_clients) loop

if mod(z.id, 2) = 1 then

insert into kk_clients1 (id) values (z.id);

else

insert into kk_clients2 (id) values (z.id);

end if;

end loop;

end;

Проверим результат:

select * from kk_clients1;

select * from kk_clients2;

Задача. Использование сиквенсов и dbms_random.

Создать сиквенс и тестовую таблицу с полями ID и VAL типа INTEGER.

Заполнить эту таблицу 100 строками: ID - значениями из сиквенса, VAL - случайными значениями от 0 до 999.

Сиквенсы - это генераторы возрастающей последовательности уникальных целых чисел.

Создадим сиквенс:

CREATE SEQUENCE KK_SEQ

MINVALUE 1

MAXVALUE 9999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

Из сиквенса можно выбирать следующее и текущее значение:

SELECT KK_SEQ.NEXTVAL FROM DUAL;

SELECT KK_SEQ.CURRVAL FROM DUAL;

Создадим тестовую таблицу:

CREATE TABLE KK_TAB(ID INTEGER, VAL INTEGER);

Случайное число от 0 до 1 можно выбирать с помощью функции DBMS_RANDOM.VALUE():

SELECT DBMS_RANDOM.VALUE() FROM DUAL;

Этот блок решает задачу:

DECLARE

I INTEGER;

BEGIN

FOR I IN 1 .. 100 LOOP

INSERT INTO KK_TAB (ID, VAL)

VALUES

(kk_seq.NEXTVAL, TRUNC(1000 * DBMS_RANDOM.VALUE()));

END LOOP;

END;

Проверим результат:

SELECT * FROM KK_TAB;

Задача. Процедура с out параметрами. Нотации.

Написать процедуру KK_P с двумя IN параметрами - фамилией и именем, а также 2 OUT параметрами - числом букв 'а' в фамилии и числом букв 'б' в имени (не различать большие и маленькие буквы).

В процедуре использовать функцию KK_F, которая подсчитывает число переданных в качестве параметра букв в строке.

Написать код вызова процедуры с выводом OUT параметров в DBMS_OUTPUT.

Создадим функцию с параметрами: входной строкой и символом, количество вхождений которого в строке мы ищем.

CREATE OR REPLACE FUNCTION KK_F(IN_STR IN VARCHAR2, IN_CHAR IN CHAR)

RETURN INTEGER IS

RES INTEGER;

BEGIN

RES := 0;

FOR I IN 1 .. LENGTH(IN_STR) LOOP

IF SUBSTR(IN_STR, I, 1) = IN_CHAR THEN

RES := RES + 1;

END IF;

END LOOP;

RETURN RES;

END KK_F;

Создадим основную процедуру. Такой способ вызова процедуры, когда параметры перечислены в том порядке, в каком они указаны в коде процедуры, называется позиционной нотацией:

CREATE OR REPLACE PROCEDURE KK_P(IN_LASTNAME IN VARCHAR2,

IN_FIRSTNAME IN VARCHAR2,

OUT_NUM_A_IN_LASTNAME OUT INTEGER,

OUT_NUM_B_IN_FIRSTNAME OUT INTEGER) IS

BEGIN

OUT_NUM_A_IN_LASTNAME := KK_F(UPPER(IN_LASTNAME), 'А');

OUT_NUM_B_IN_FIRSTNAME := KK_F(UPPER(IN_FIRSTNAME), 'Б');

END KK_P;

Проверим работу процедуры. Такой способ вызова процедуры, когда параметры перечислены в том порядке, в каком они указаны в коде процедуры, называется позиционной нотацией:

declare

n1 integer := 0;

n2 integer := 0;

begin

DBMS_OUTPUT.enable;

kk_p('Анатольев', 'Борис', n1, n2);

DBMS_OUTPUT.put_line('Анатольев Борис ' || n1 || ' ' || n2);

kk_p('Робин', 'Барабек', n1, n2);

DBMS_OUTPUT.put_line('Робин Барабек ' || n1 || ' ' || n2);

kk_p('Атанбаев', 'Ибрагим', n1, n2);

DBMS_OUTPUT.put_line('Атанбаев Ибрагим ' || n1 || ' ' || n2);

end;

Другим способом передачи параметров вызываемой процедуре является именованная нотация:

declare

n1 integer := 0;

n2 integer := 0;

begin

DBMS_OUTPUT.enable;

kk_p(IN_LASTNAME => 'Анатольев',

IN_FIRSTNAME => 'Борис',

OUT_NUM_A_IN_LASTNAME => n1,

OUT_NUM_B_IN_FIRSTNAME => n2);

DBMS_OUTPUT.put_line('Анатольев Борис ' || n1 || ' ' || n2);

kk_p(IN_LASTNAME => 'Робин',

IN_FIRSTNAME => 'Барабек',

OUT_NUM_A_IN_LASTNAME => n1,

OUT_NUM_B_IN_FIRSTNAME => n2);

DBMS_OUTPUT.put_line('Робин Барабек ' || n1 || ' ' || n2);

kk_p(IN_LASTNAME => 'Атанбаев',

IN_FIRSTNAME => 'Ибрагим',

OUT_NUM_A_IN_LASTNAME => n1,

OUT_NUM_B_IN_FIRSTNAME => n2);

DBMS_OUTPUT.put_line('Атанбаев Ибрагим ' || n1 || ' ' || n2);

end;

Именованная нотация позволяет переставлять параметры местами и удобно пользоваться значениями параметров по умолчанию. Немного переделаем процедуру, добавив значения параметров по умолчанию:

CREATE OR REPLACE PROCEDURE KK_P(IN_LASTNAME IN VARCHAR2 default 'Растаманов',

IN_FIRSTNAME IN VARCHAR2 default 'Боромир',

OUT_NUM_A_IN_LASTNAME OUT INTEGER,

OUT_NUM_B_IN_FIRSTNAME OUT INTEGER) IS

BEGIN

OUT_NUM_A_IN_LASTNAME := KK_F(UPPER(IN_LASTNAME), 'А');

OUT_NUM_B_IN_FIRSTNAME := KK_F(UPPER(IN_FIRSTNAME), 'Б');

END KK_P;

Вызовем процедуру другим образом, IN_LASTNAME передастся по умолчанию:

declare

n1 integer := 0;

n2 integer := 0;

begin

kk_p(OUT_NUM_A_IN_LASTNAME => n1,

OUT_NUM_B_IN_FIRSTNAME => n2,

IN_FIRSTNAME => 'Добролюб');

DBMS_OUTPUT.put_line('Растаманов Добролюб ' || n1 || ' ' || n2);

end;