Задача. Отчет об изменениях зарплаты.

Создать таблицу KK_SALARY(NAME varchar2(50), DT date, WAGE number) и внести туда данные об изменении зарплаты сотрудников.

Выбрать из KK_SALARY:

- последнюю дату изменения зарплаты по всем сотрудникам;

- последнюю (по дате DT) зарплату по всем сотрудникам;

- последнюю + предпоследнюю + пред-пред-последнюю зарплату в одной строке для каждого сотрудника.

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

CREATE TABLE KK_SALARY(NAME varchar2(50), DT date, WAGE number);

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

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Иванов', to_date('01.01.2001','DD.MM.YYYY'), 1000 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Иванов', to_date('01.07.2007','DD.MM.YYYY'), 1400 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Иванов', to_date('01.08.2008','DD.MM.YYYY'), 1600 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Иванов', to_date('01.10.2010','DD.MM.YYYY'), 1500 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Петров', to_date('01.01.2004','DD.MM.YYYY'), 3000 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Петров', to_date('01.01.2005','DD.MM.YYYY'), 2500 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Петров', to_date('01.01.2010','DD.MM.YYYY'), 1700 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Петров', to_date('01.01.2011','DD.MM.YYYY'), 1400 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Рябов', to_date('15.07.2003','DD.MM.YYYY'), 15 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Рябов', to_date('21.04.2009','DD.MM.YYYY'), 1050 );

Insert into KK_SALARY (NAME, DT, WAGE) Values ('Рябов', to_date('11.01.2012','DD.MM.YYYY'), 100500 );

Напишем запрос, решающий задачу, применив аналитические функции.

Во внутреннем подзапросе q1 для каждой строки находим row_number() - номер строки в разбиении по name и сортировке по dt по убыванию.

lag(wage, 1) и lag(wage, 2) соответственно выдают предпоследнюю и пред-пред-последнюю зарплату в разбиении по name и сортировке по dt по возрастанию:

select name,

dt last_salary_change_date,

wage last_wage,

list_of_3_last_wages

from (select name,

dt,

wage,

row_number() over(partition by name order by dt desc) rn,

wage || ' <- ' || lag(wage, 1) over(partition by name order by dt) || ' <- ' || lag(wage, 2) over(partition by name order by dt) list_of_3_last_wages

from kk_salary

order by name, dt desc) q1

where rn = 1;

Задача. Пакет функций о вкладах.

Есть таблица вкладчиков KK_DEPOSIT вида

DEPOSIT_ID	CLIENT_NAME	DT	AMOUNT
1	Карапетян	30.01.2011	123,89
2	Карапетян	31.01.2011	129,8
3	Саахов	24.09.2011	123123,91
4	Саахов	27.11.2011	90,98
5	Саахов	13.05.2010	8000000
6	Растрелли	10.01.2012	1100000

Написать пакет из функций по нахождению:

• последней даты вклада по входному параметру IN_CLIENT_NAME

• суммы всех вкладов по входному параметру IN_CLIENT_NAME

• суммы последних двух вкладов (или 0 или 1 вкладов, если нет двух) по входному параметру IN_CLIENT_NAME

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

CREATE TABLE KK_DEPOSIT(DEPOSIT_ID INTEGER, CLIENT_NAME VARCHAR2(40), DT DATE, AMOUNT NUMBER);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(1,'Карапетян',to_date('30.01.2011','dd.mm.yyyy'),123.89);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(2,'Карапетян',to_date('31.01.2011','dd.mm.yyyy'),129.8);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(3,'Саахов',to_date('24.09.2011','dd.mm.yyyy'),123123.91);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(4,'Саахов',to_date('27.11.2011','dd.mm.yyyy'),90.98);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(5,'Саахов',to_date('13.05.2010','dd.mm.yyyy'),8000000);

insert into kk_deposit(deposit_id,client_name,dt,amount) values(6,'Растрелли',to_date('10.01.2012','dd.mm.yyyy'),1100000);

Напишем заголовок пакета:

CREATE OR REPLACE PACKAGE KK_PKG33 IS

FUNCTION GET_LAST_DEPOSIT_DATE(IN_CLIENT_NAME IN VARCHAR2) RETURN DATE;

FUNCTION GET_SUM_ALL_DEPOSITS(IN_CLIENT_NAME IN VARCHAR2) RETURN NUMBER;

FUNCTION GET_SUM_LAST_2_DEPOSITS(IN_CLIENT_NAME IN VARCHAR2) RETURN NUMBER;

END KK_PKG33;

Напишем тело пакета:

CREATE OR REPLACE PACKAGE BODY KK_PKG33 IS

FUNCTION GET_LAST_DEPOSIT_DATE(IN_CLIENT_NAME IN VARCHAR2) RETURN DATE IS

RES DATE;

BEGIN

SELECT NVL(MAX(DT), TO_DATE('01.01.1900', 'DD.MM.YYYY'))

INTO RES

FROM KK_DEPOSIT

WHERE CLIENT_NAME = IN_CLIENT_NAME;

RETURN RES;

END GET_LAST_DEPOSIT_DATE;

FUNCTION GET_SUM_ALL_DEPOSITS(IN_CLIENT_NAME IN VARCHAR2) RETURN NUMBER IS

RES NUMBER;

BEGIN

SELECT NVL(SUM(AMOUNT), 0)

INTO RES

FROM KK_DEPOSIT

WHERE CLIENT_NAME = IN_CLIENT_NAME;

RETURN RES;

END GET_SUM_ALL_DEPOSITS;

FUNCTION GET_SUM_LAST_2_DEPOSITS(IN_CLIENT_NAME IN VARCHAR2) RETURN NUMBER IS

RES NUMBER;

BEGIN

SELECT NVL(SUM(AMOUNT), 0)

INTO RES

FROM (SELECT AMOUNT, ROW_NUMBER() OVER(ORDER BY DT DESC) RN

FROM KK_DEPOSIT

WHERE CLIENT_NAME = IN_CLIENT_NAME)

WHERE RN IN (1, 2);

RETURN RES;

END GET_SUM_LAST_2_DEPOSITS;

END KK_PKG33;

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

SELECT CLIENT_NAME,

KK_PKG33.GET_LAST_DEPOSIT_DATE(CLIENT_NAME),

KK_PKG33.GET_SUM_ALL_DEPOSITS(CLIENT_NAME),

KK_PKG33.GET_SUM_LAST_2_DEPOSITS(CLIENT_NAME)

FROM (SELECT DISTINCT CLIENT_NAME

FROM KK_DEPOSIT

UNION ALL

SELECT 'Несуществующий' CLIENT_NAME FROM DUAL);