Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET serveroutput ON;
- 1.
- CREATE TABLE nekretnine(
- id_nekretnine int NOT NULL,
- id_vlasnika int NOT NULL,
- grad VARCHAR(255) NOT NULL,
- godina_izgr int NOT NULL,
- datum_kupnje DATE NOT NULL,
- datum_uknj DATE NOT NULL,
- boja VARCHAR(50),
- novo SMALLINT
- );
- INSERT INTO nekretnine
- VALUES(1, 1, 'Zagreb', 1996, DATE '2018-01-01', DATE '2018-01-20', 'bijela', NULL);
- INSERT INTO nekretnine
- VALUES(2, 1, 'Split', 2012, DATE '2018-01-01', DATE '2018-01-20', 'bijela', NULL);
- INSERT INTO nekretnine
- VALUES(3, 2, 'Dubrovnik', 1994, DATE '2018-01-01', DATE '2018-01-20', 'bijela', NULL);
- SELECT * FROM nekretnine;
- 2.
- ALTER TABLE nekretnine
- add nekretnina_zagreb SMALLINT;
- DECLARE
- CURSOR cur_nekretnine IS
- SELECT *
- FROM nekretnine;
- redak cur_nekretnine%ROWTYPE;
- p_nekr SMALLINT;
- BEGIN
- OPEN cur_nekretnine;
- LOOP
- FETCH cur_nekretnine INTO redak;
- EXIT WHEN cur_nekretnine%NOTFOUND;
- IF redak.grad = 'Zagreb' THEN
- p_nekr := 1;
- ELSE
- p_nekr := 0;
- END IF;
- UPDATE nekretnine
- SET nekretnina_zagreb = p_nekr
- WHERE nekretnine.id_nekretnine = redak.id_nekretnine;
- END LOOP;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.put_line('Greška !');
- END;
- 4.
- CREATE OR REPLACE PROCEDURE proc_novo(
- p_id_nekretnine IN NUMBER)
- IS
- p_godina INT;
- BEGIN
- SELECT godina_izgr INTO p_godina
- FROM nekretnine
- WHERE p_id_nekretnine = nekretnine.id_nekretnine;
- IF EXTRACT(YEAR FROM SYSDATE) - p_godina < 5 THEN
- UPDATE nekretnine
- SET novo = 1
- WHERE id = p_id_nekretnine;
- ELSE
- UPDATE nekretnine
- SET novo = 0
- WHERE id = p_id_nekretnine;
- END IF;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.put_line('Greška !');
- BEGIN
- proc_novo(1);
- END;
- 3.
- CREATE OR REPLACE PROCEDURE proc_vlas(
- p_id_nekretnine IN NUMBER,
- p_grad IN VARCHAR(255))
- IS
- BEGIN
- UPDATE nekretnine
- SET grad = p_grad
- WHERE
- id_nekretnine = p_id_nekretnine;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.put_line('Greška!');
- END proc_vlas;
- BEGIN
- proc_vlas(1);
- END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement