Materiały na BD egz

INSERT INTO czytelnicy VALUES (1,'Haraszo','Andrzej',TO_DATE('2003/07/09', 'yyyy/mm/dd'));

------------------------       SEKWENCJE

CREATE SEQUENCE sequence_name
    START WITH start_num
    INCREMENT BY increment_num
    MAXVALUE maximum_value || NOMAXVALUE
    MINVALUE minimum_value || NOMINVALUE
    CACHE cache_num || NOCACHE
    CYCLE | NOCYCLE
    ORDER | NOORDER
;

INSERT INTO table_name VALUES (sequence_name.NEXTVAL, other_value);

------------------------           PROCEDURY

CREATE OR REPLACE PROCEDURE emp_sal (dep_id NUMBER, sal_raise NUMBER)
IS
BEGIN
    UPDATE employees SET salary = salary * sal_raise WHERE department_id = dep_id;
END;
/

EXECUTE emp_sal;

--------------------------          FUNKCJE


CREATE OR REPLACE FUNCTION function_name
(Parameter 1, Parameter 2) RETURN datatype
IS
    DECLARE variable, CONSTANT etc.
BEGIN
    Executable Statements
    RETURN (RETURN VALUE)
END;

-------- Funkcja 2.

CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER
IS
    pi CONSTANT NUMBER (7,3) := 3.141;
    area NUMBER(7,3);
BEGIN
    area := pi * ( radius * radius );
    RETURN area;
END;
/

BEGIN
DBMS_OUTPUT.PUT_LINE(circle_area (25));
END;
/

-----------------------------      KURSORY

DECLARE
    v_imie VARCHAR(30);
    CURSOR demo IS
    SELECT imie FROM pracownicy
    WHERE id_pracownika < 105;
BEGIN
    OPEN demo;
    LOOP
        FETCH demo INTO v_imie;
        DBMS_OUTPUT.PUT_LINE(v_imie);
        EXIT WHEN demo%NOTFOUND;
    END LOOP;
    CLOSE demo;
END;


----------- Kursor 2

DECLARE
    zmienna TABELA%ROWTYPE;
CURSOR nazwa_kursora
IS
    SELECT * FROM TABELA;
BEGIN
OPEN nazwa_kursora;
    LOOP
        FETCH nazwa_kursora INTO zmienna;
        EXIT WHEN nazwa_kursora%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Wartosc kolumny: '|| zmienna.kolumna);
    END LOOP;
    DBMS_OUTPUT.PUT_LIBE('Zliczanie ile razy: '|| nazwa_kursora%rowcount)
    CLOSE nazwa_kursora;
END;
/

--------------------------------          PRZEŁĄCZNIKI

CREATE OR REPLACE TRIGGER pracownicy
BEFORE INSERT ON pracownicy
FOR EACH ROW
-- ENABLE
DECLARE
    imie VARCHAR(20) := 'Pawel';
BEGIN
    DBMS_OUTPUT.PUT_LINE ( imie || ', jestes zajebisty i dodales rekord');
END;
/

-------- Przełącznik 2.

CREATE OR REPLACE TRIGGER pracownicy
BEFORE INSERT OR DELETE OR UPDATE ON pracownicy
FOR EACH ROW
-- ENABLE
DECLARE
    imie VARCHAR(20) := 'Pawel';
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE ( imie || ', jestes zajebisty i dodales rekord');
    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE ('Kuuurwa, ale odjebales ' || imie );
    ELSIF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE ('No co, Ty bys tego nie naprawil ' || imie || '?' );
    END IF;
END;
/

UPDATE pracownicy SET imie = 'Janek' WHERE imie = 'Franek';