ug5yRv4w

1Drop table articles;
2Drop table OPERATIONS;
3Drop table BALANCE;
4
5
6CREATE TABLE ARTICLES (
7    id NUMBER(*,0) not NULL,
8    NAME VARCHAR2(50 BYTE) ,
9    CONSTRAINT articles_pk PRIMARY KEY (id)
10);
11
12create SEQUENCE id MINVALUE 1 start WITH 1 CACHE 10;
13
14CREATE TABLE BALANCE (
15    id NUMBER(*,0) not NULL,
16    CREATE_DATA TIMESTAMP(3),
17    DEBIT number(18, 2),
18    CREDIT number(18, 2),
19    AMOUNT number(18, 2),
20    CONSTRAINT balance_pk PRIMARY KEY (id)
21);
22
23CREATE TABLE OPERATIONS (
24    id NUMBER(*,0) not NULL,
25    ARTICLE_ID number(*,0) REFERENCES ARTICLES(id),
26    DEBIT number(18, 2),
27    CREDIT number(18, 2),
28    CREDIT_DATE timestamp(3),
29    BALANCE_ID NUMBER(*,0) REFERENCES BALANCE(id),
30    CONSTRAINT operations_pk PRIMARY KEY (id)
31);
32
33INSERT INTO articles(name) VALUES ('New1');
34INSERT INTO OPERATIONS(ARTICLE_ID, DEBIT, CREDIT, CREDIT_DATE, BALANCE_ID) VALUES (1, 120, 300, '26.09.2006 16:24:43', 500);
35
36
37SELECT * FROM OPERATIONS
38
39-- Выборка данных
40-- Посчитать прибыль за заданную дату. - ok
41SELECT 
42    (SUM(operations.debit) - SUM(operations.credit))
43FROM 
44    operations
45WHERE 
46    trunc(operations.credit_date) = to_date('17.01.2021', 'DD.MM.YYYY');    
47    
48-- Вывести наименования всех статей, в рамках которых не проводилось операций за заданный период времени.
49--ok
50SELECT 
51    articles.name
52FROM
53    articles
54WHERE 
55    NOT EXISTS(
56        SELECT 
57            *
58        FROM 
59            operations 
60        WHERE 
61            operations.article_id = articles.id
62            AND trunc(operations.credit_date) BETWEEN 
63                to_date('17.01.2021') AND to_date('18.01.2021')
64    );    
65    
66-- Вывести операции и наименования статей, включая статьи, в рамках  которых не проводились операции.
67--ok
68SELECT 
69    articles.name,
70    operations.debit,
71    operations.credit,
72    operations.credit_date
73FROM 
74    articles
75LEFT JOIN
76    operations
77ON
78    articles.id = operations.article_id;    
79    
80-- Вывести число балансов, в которых учтены операции принадлежащие статье с заданным наименованием. 
81--ok
82SELECT DISTINCT
83    COUNT(balance.id)
84FROM
85    balance
86JOIN
87    operations
88ON
89    balance.id = operations.balance_id
90JOIN
91    articles
92ON
93    operations.article_id = articles.id
94WHERE 
95    articles.name = 'Зарплата'
96GROUP BY
97    balance.id;    
98    
99--Вывести сумму расходов по заданной статье, агрегируя по сформированным балансам за указанный период времени.
100--ok
101SELECT
102    SUM(operations.credit)
103FROM 
104    operations
105JOIN
106    articles
107ON
108    operations.article_id = articles.id
109JOIN
110    balance
111ON
112    balance.id = operations.balance_id
113WHERE
114    articles.name = 'Продукты'
115    AND trunc(operations.credit_date) BETWEEN 
116        to_date('16.01.2021', 'DD.MM.YYYY') AND to_date('16.02.2021', 'DD.MM.YYYY')
117GROUP BY
118    balance.id;
119    
120-- Вставка данных
121
122--Добавить новую статью.
123--ok
124DECLARE
125    new_article_id NUMBER;
126BEGIN
127    INSERT INTO articles
128        (name)
129    VALUES
130        ('Зарплата')
131    returning
132        articles.id
133    INTO 
134        new_article_id;
135        
136    INSERT INTO operations
137        (debit, credit, credit_date, article_id)
138    VALUES
139        (1000, 0, sysdate, new_article_id);
140END;
141
142
143--Сформировать баланс. Если сумма прибыли меньше некоторой суммы – транзакцию откатить.
144SET SERVEROUTPUT ON 
145DECLARE
146    credit NUMBER;
147    debit NUMBER;
148    amount NUMBER;
149    date_from DATE := to_date('01.01.2021', 'DD.MM.YYYY');
150    date_to DATE := to_date('31.01.2021', 'DD.MM.YYYY');
151    new_balance_id NUMBER;
152BEGIN 
153    SELECT
154        SUM(operations.credit), SUM(operations.debit), SUM(operations.debit) - SUM(operations.credit) 
155    INTO 
156        credit, debit, amount
157    FROM
158        operations
159    WHERE
160        trunc(operations.credit_date) BETWEEN 
161            date_from AND date_to;
162            
163    DBMS_OUTPUT.PUT_LINE (credit || ' ' || debit || ' ' || amount);
164    
165    INSERT INTO balance
166        (credit_date, debit, credit, amount)
167    VALUES
168        (sysdate, debit, credit, amount)
169    returning
170        balance.id
171    INTO 
172        new_balance_id;
173        
174    IF debit < 1000
175    THEN
176        DBMS_OUTPUT.PUT_LINE ('aborted');
177        ROLLBACK;
178    ELSE
179        DBMS_OUTPUT.PUT_LINE ('accepted');
180        UPDATE
181            operations
182        SET
183            operations.balance_id = new_balance_id;
184        WHERE
185            operations.credit_date BETWEEN date_from AND date_to;
186        commit;
187    END IF;
188    
189END;
190
191--Удалить статью и операции, выполненные в ее рамках.
192--OK
193DECLARE
194    article_name varchar2(50) := 'Другое';
195BEGIN
196 
197    DELETE FROM
198        operations
199    WHERE
200        operations.article_id = (
201            SELECT
202                articles.id
203            FROM
204                articles
205            WHERE
206                articles.name = article_name
207        );
208    
209    DELETE FROM 
210        articles
211    WHERE
212        articles.name = 'Другое';
213 
214END;
215
216--Удалить в рамках транзакции самый убыточный баланс и операции.
217--ок
218DECLARE
219    balance_id NUMBER;
220    min_amount NUMBER;
221BEGIN
222 
223    SELECT
224        balance.id, MIN(balance.amount)
225    INTO
226        balance_id, min_amount
227    FROM
228        balance
229    GROUP BY
230        balance.id;
231    
232    DELETE FROM
233        operations
234    WHERE
235        operations.id IN (
236            SELECT
237                operations.id
238            FROM
239                operations
240            WHERE
241                operations.balance_id = balance_id
242        );
243        
244    DELETE FROM
245        balance
246    WHERE 
247        balance.id = balance_id;
248        
249    commit;
250END;
251 
252--если в удаленном балансе использовались статьи,
253--операции в рамках которых больше нигде не проводились – транзакцию
254--откатить.
255--ок
256DECLARE
257    balance_id NUMBER;
258    empty_articles_count NUMBER;
259BEGIN
260    SELECT 
261        "ordered_balances"."balance_id"
262    INTO 
263        balance_id
264    FROM (
265        SELECT
266            balance.id "balance_id"
267        FROM
268            balance
269        ORDER BY
270            balance.amount ASC
271    ) "ordered_balances"
272    WHERE
273        rownum = 1;
274        
275    DELETE FROM
276        balance
277    WHERE 
278        balance.id = balance_id;
279        
280    SELECT 
281        COUNT(articles.id)
282    INTO
283        empty_articles_count
284    FROM
285        articles
286    WHERE 
287        NOT EXISTS(
288            SELECT 
289                0
290            FROM 
291                operations 
292            WHERE 
293                operations.article_id = articles.id 
294                AND operations.balance_id != balance_id
295        ) AND EXISTS(
296            SELECT 
297                0
298            FROM 
299                operations 
300            WHERE 
301                operations.article_id = articles.id 
302                AND operations.balance_id = balance_id
303        );
304        
305    IF empty_articles_count > 0
306    THEN
307        DBMS_OUTPUT.PUT_LINE ('aborted');
308        ROLLBACK;
309    ELSE
310        DBMS_OUTPUT.PUT_LINE ('accepted');
311        
312        DELETE FROM
313            operations
314        WHERE
315            operations.id IN (
316                SELECT
317                    operations.id
318                FROM
319                    operations
320                WHERE
321                    operations.balance_id = balance_id
322            );
323        commit;
324    END IF;
325END;
326
327--Увеличить сумму расхода операций, выполненных в рамках статьи,
328--заданной по наименованию, на заданную величину. Расход и прибыль
329--сформированных на основании модифицируемых операций балансов
330--должны быть пересчитаны.
331--ok
332DECLARE
333    credit_amount NUMBER := 100;
334    article_name varchar2(20) := 'Продукты';
335BEGIN
336    UPDATE 
337        operations
338    SET
339        operations.credit = operations.credit + credit_amount
340    WHERE
341        operations.article_id = (
342            SELECT
343                articles.id
344            FROM
345                articles
346            WHERE
347                articles.name = article_name
348        );
349    UPDATE
350        balance
351    SET
352        balance.credit = balance.credit + credit_amount * (
353            SELECT
354                COUNT(operations.id)
355            FROM
356                operations
357            JOIN
358                articles
359            ON
360                articles.id = operations.article_id
361            WHERE
362                operations.balance_id = balance.id
363                AND articles.name = article_name
364        );
365    commit;
366    UPDATE
367        balance
368    SET
369        balance.amount = balance.debit - balance.credit;
370    commit;
371END;
372
373
374--В рамках транзакции поменять заданную статью во всех операциях на
375--другую и удалить ее.
376--ок
377DECLARE
378    article_name_from varchar2(30) := 'Зарплата';
379    article_name_to varchar2(30) := 'Транспорт';
380    article_id_to NUMBER;
381    article_id_from NUMBER;
382BEGIN
383    SELECT
384        articles.id
385    INTO
386        article_id_to
387    FROM 
388        articles
389    WHERE
390        articles.name = article_name_to;
391        
392    SELECT
393        articles.id
394    INTO
395        article_id_from
396    FROM 
397        articles
398    WHERE
399        articles.name = article_name_from;
400        
401    UPDATE
402        operations
403    SET
404        operations.article_id = article_id_to
405    WHERE
406        operations.article_id = article_id_from;
407    
408    DELETE
409        articles
410    WHERE
411        articles.id = article_id_from;
412    commit;
413END;
414
415
416
417