PLSQL TYPE IS RECORD TYPE IS TABLE OF

Структурный тип данных в PL/SQL • • • Запись TYPE имя IS RECORD (список полей и их типов); Таблица TYPE имя IS TABLE OF тип данных ; Массив TYPE имя IS VARRAY(max) OF тип данных; Пример …. TYPE type 1 is record(a integer, b varchar 2(10)); TYPE type 2 is table of date; my_record type 1; my_collection type 2; …… my_record. a : = 1; my_record. b : = ‘aaaaaa’; my_collection : = type 2(SYSDATE, SYSDATE-1, SYSDATE-2); dbms_output. put_line(my_record. b|| ‘ ‘||my_collection(3)); my_collection. delete(3); dbms_output. put_line(my_collection(1)|| ‘ ‘||my_collection(2)); ……

Условные операторы IF - THEN - END IF; IF - THEN - ELSE - END IF; IF - THEN - ELSIF - ELSE - END IF; Пример: If rating between 100 and 200 then bonus : = 15; else bonus : = 5; end if; CASE - WHEN - THEN - END CASE; CASE - WHEN - THEN - ELSE - END; Пример: CASE flag WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' ELSE 'NULL' END;

Операторы цикла Безусловный (LOOP - END LOOP) Пример: i integer : = 1; …… LOOP dbms_output. put_line(‘i= ‘ ||to_char(i)); i: = i+1; EXIT WHEN i>10; END LOOP; С предусловием (WHILE - LOOP - END LOOP) Пример: i integer : = 1; …… WHILE i<=10 LOOP dbms_output. put_line(‘i= ‘ ||to_char(i)); i: = i+1; END LOOP;

Пример процедуры Create or replace procedure calc_bonus( id_local in person. id%TYPE, bonus out person. sal%TYPE) IS salary person. sal%TYPE : = 0. 0; BEGIN Select Person. sal into salary from person where person. id = id_local; if salary != 0. 0 then bonus : = salary* 0. 15; else bonus : =-1; end if; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output. put_line('Error ID !'); bonus : = 0. 0; END calc_bonus;

Пример функции Create or replace function count_person( salary in person. sal%TYPE ) RETURN integer IS count_salary integer : = 0; no exception; BEGIN select count(*) into count_salary from person where person. sal > salary; if count_salary = 0 then raise no; else end if; return count_salary; EXCEPTION WHEN NO THEN dbms_output. put_line('No DATA !'); return -1; END count_person;

Пример триггера Create or replace trigger cheker BEFORE UPDATE ON person for each row declare BEGIN if : new. sal < : old. sal then raise_application_error(-20125, 'New salary must be more then old salary'); end if; end cheker;

Курсоры Простой CURSOR имя IS запрос Пример: cursor c 1 is select sal from person where person. id = enter; Параметрический CURSOR имя (пар1 тип 1, пар2 тип 2, …. ) IS запрос Пример: cursor c 2 (enter in integer) is select sal from person where person. id = enter; Возвращающий данные CURSOR имя RETURN таблица%ROWTYPE IS запрос Пример: cursor c 3 return persona%ROWTYPE is select * from person where person. id = 1;

Пример курсора declare CURSOR c 1 IS SELECT sal FROM person; summa person. sal%TYPE : = 0. 0; salary person. sal%TYPE; BEGIN OPEN c 1; loop FETCH c 1 INTO salary; EXIT WHEN c 1%notfound; summa : = summa + salary; end loop; CLOSE c 1; dbms_output. put_line(to_char(summa)); END;