SQL

SELECT * FROM SYS.USER_ERRORS;


/* Tworzenie tabel testowych */

DROP TABLE Client;

CREATE TABLE Client
(
	client_id NUMBER(4) NOT NULL PRIMARY KEY,
	name VARCHAR(30),
	email VARCHAR(30)
);

DROP TABLE Order_;

CREATE TABLE Order_
(
	order_id NUMBER(4) NOT NULL PRIMARY KEY,
	client_id NUMBER(4) NOT NULL,
	order_date DATE,
	shipment_date DATE
);

DROP TABLE Product;

CREATE TABLE Product
(
	product_id NUMBER(4) NOT NULL PRIMARY KEY,
	order_id NUMBER(4) NOT NULL,
	product_name VARCHAR(30),
	price NUMBER(5,2)
);



======= 1 ==========

CREATE SEQUENCE product_primary_key_generator
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;


/* Sprawdzenie */
SET SERVEROUTPUT ON
DECLARE
	val number(4);
BEGIN
	SELECT product_primary_key_generator.nextval
	INTO val
	FROM dual;
	dbms_output.put_line(val);
END;
/

/* Wynik: */
1



======= 2 ==========

/* Tworzenie funkcji wstawiającej dane do tabeli Product */

CREATE OR REPLACE FUNCTION insertProduct(name IN varchar, price IN number, orderId IN number)
RETURN number IS
   newIndex number(4) := 0;
BEGIN
	SELECT product_primary_key_generator.nextval
	INTO newIndex
	FROM dual;

	INSERT INTO Product (product_id, order_id, product_name, price) VALUES (newIndex, orderId, name, price);
	RETURN newIndex;
END;
/





/* Sprawdzenie */
DECLARE
   newIndex number;
BEGIN
   newIndex:= insertProduct('nowy produkt', 50, 2);
   dbms_output.put_line('Dodano wiersz do tabeli Upust o id ' || newIndex);
END;
/

SELECT * FROM Product;


=== WYNIK ===

Dodano wiersz do tabeli Upust o id 2

PRODUCT_ID   ORDER_ID PRODUCT_NAME                        PRICE
---------- ---------- ------------------------------ ----------
         2          2 nowy produkt                           50







======= 3 =======

CREATE OR REPLACE VIEW Client_products AS
SELECT c.name, c.email, o.order_date, o.shipment_date, p.product_name, p.price
FROM Client c, Order_ o, Product p
WHERE c.client_id = o.client_id
      AND o.order_id = p.order_id;



4]
/* Tworzenie sekwencji do pobierania kolejnych id tabeli Order */

CREATE SEQUENCE order_primary_key_generator
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

/* Tworzenie danych testowych */

INSERT INTO Client (client_id, name, email) VALUES (1, 'Client1', 'client@gmail.com');

/* Wyzwalacz */

CREATE OR REPLACE TRIGGER Client_products_insert
INSTEAD OF INSERT ON Client_products
FOR EACH ROW
DECLARE
  clientId Client.client_id%TYPE;
  newOrderindex number(4);
  newProductindex number(4);

BEGIN
  SELECT client_id INTO clientId
  FROM Client c
  WHERE c.name = :NEW.name
        AND c.email = :NEW.email;


  BEGIN
      SELECT order_id INTO newOrderindex
      FROM Order_ o
      WHERE o.client_id = clientId
            AND o.order_date = :NEW.order_date
            AND  o.shipment_date = :NEW.shipment_date;

      RAISE DUP_VAL_ON_INDEX;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
	SELECT order_primary_key_generator.nextval
	INTO newOrderindex
	FROM dual;

        INSERT INTO Order_ (order_id, client_id, order_date, shipment_date)
                    VALUES (newOrderindex, clientId, :NEW.order_date, :NEW.shipment_date);

	SELECT product_primary_key_generator.nextval
	INTO newProductindex
	FROM dual;

	INSERT INTO Product (product_id, order_id, product_name, price)
                    VALUES (newProductindex, newOrderindex, :NEW.product_name, :NEW.price);

    WHEN DUP_VAL_ON_INDEX THEN
    	dbms_output.put_line('Zamowienie zostalo juz zdefiniowane wczesniej');

  END;


  EXCEPTION
      WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('Uzytkownik o podanych danych nie istnieje');
END;
/



INSERT INTO Client_products (name, email, order_date, shipment_date, product_name, price)
VALUES ('zle imie', 'client@gmail.com', SYSDATE, SYSDATE, 'nowy produkt', 100);

> Uzytkownik o podanych danych nie istnieje

INSERT INTO Client_products (name, email, order_date, shipment_date, product_name, price)
VALUES ('Client1', 'client@gmail.com', '17/01/15', '17/01/15', 'kolejny produkt', 100);

> 1 wiersz został utworzony.


SELECT * FROM Order_;

>
  ORDER_ID  CLIENT_ID ORDER_DA SHIPMENT
---------- ---------- -------- --------
         1          1 17/01/15 17/01/15


SELECT * FROM Product;

>
PRODUCT_ID   ORDER_ID PRODUCT_NAME                        PRICE
---------- ---------- ------------------------------ ----------
         2          2 nowy produkt                           50
         3          1 kolejny produkt                       100


INSERT INTO Client_products (name, email, order_date, shipment_date, product_name, price)
VALUES ('Client1', 'client@gmail.com', '17/01/15', '17/01/15', 'kolejny produkt', 100);

> Zamowienie zostalo juz zdefiniowane wczesniej






============ 5 ===========
FOR UPDATE
----------
Dodanie klauzuli FOR UPDATE sprawi że wiersze te zostaną zablokowane dla innych użytkowników do czasu, aż nie zatwierdzimy transakcji (COMMIT), lub jej

nie wycofamy (ROLLBACK).
Po klauzuli „for update” można wymienić tylko te kolumny które będziemy updatować. W przypadku blokady na jednej tabeli i tak zostaną zablokowane całe

wiersze. Jeśli po FOR UPDATE wymienimy kolumny, ale zapytanie oparte jest o więcej niż jedną tabelę, zablokowane zostaną wiersze tylko w tej tabeli

której kolumny wymienimy po FOR UPDATE.


Zastosowanie klauzuli NOWAIT:
-----------------------------
Gdy w czasie trwania blokady inny użytkownik wykona instrukcję DML, jego sesja zostanie niejako „zawieszona”. Zapytanie będzie oczekiwało na wykonanie do

momentu kiedy przez użytkownika blokującego dane nie zostaną odblokowane.

Klauzula NOWAIT powoduje, że użytkownik nie będzie oczekiwał „zawieszony” na zwolnienie blokady, a otrzymam informację że w tej chwili wykonanie operacji

nie jest możliwe.


Przykład
--------

SQL> DELETE FROM Product;
INSERT INTO Product (product_id, order_id, product_name, price) VALUES (301, 1, 'product_1', 100);
INSERT INTO Product (product_id, order_id, product_name, price) VALUES (302, 1, 'product_2', 100);
INSERT INTO Product (product_id, order_id, product_name, price) VALUES (303, 1, 'other1', 300);
INSERT INTO Product (product_id, order_id, product_name, price) VALUES (304, 1, 'other2', 400);
COMMIT;


CREATE OR REPLACE PROCEDURE do_something(p_id NUMBER) IS
     row_locked EXCEPTION;
     PRAGMA EXCEPTION_INIT(row_locked, -54);
 BEGIN
     FOR cc IN (SELECT *
                 FROM Product
                 WHERE product_id = p_id FOR UPDATE NOWAIT) LOOP
      NULL;
      END LOOP;
   EXCEPTION
      WHEN row_locked THEN
         raise_application_error(-20001, 'this row is locked...');

 END do_something;
/




Sesja 1

SELECT product_id FROM Product WHERE product_id = 301 FOR UPDATE;

PRODUCT_ID
----------
       301


Sesja 2

EXEC do_something(301);

BŁĄD w linii 1:
ORA-20001: this row is locked...
ORA-06512: przy "SYSTEM.DO_SOMETHING", linia 12
ORA-06512: przy linia 1


Sesja 1

COMMIT;

Zatwierdzanie zostało ukończone.


Sesja 2
EXEC do_something(301);

Procedura PL/SQL została zakończona pomyślnie.