--ZMAZANIE TABULKY ADRESAR

--DROP TABLE ADRESAR;

--VYTVORENIE TABULKY ADRESAR

CREATE TABLE ADRESAR(

IDADRESAR INT NOT NULL PRIMARY KEY, 

NAME VARCHAR(50), 

ADDRESS VARCHAR(80),

IDPRAVNAFORMA INT, 

CREATE_DATE DATE);

INSERT INTO ADRESAR (IDADRESAR, "NAME", ADDRESS, IDPRAVNAFORMA)

VALUES (2, 'Ján Kolenička', 'THK 33', 2);

INSERT INTO ADRESAR (IDADRESAR, "NAME", ADDRESS, IDPRAVNAFORMA)

VALUES (3, 'Karel Čapek', 'Šalgotarianska 22', 1);

INSERT INTO ADRESAR(IDADRESAR, "NAME", ADDRESS, IDPRAVNAFORMA)

VALUES (4, 'Helena Mociková', 'Rudná 2', 2);

--ZMAZANIE TABULKY PRAVFORM

--DROP TABLE PRAVFORM;

--VYTVORENIE TABULKY PRAVFORM

CREATE TABLE PRAVFORM(

IDPRAVFORM INT NOT NULL PRIMARY KEY, 

NAME VARCHAR(50)

);

INSERT INTO PRAVFORM

 VALUES(1, 'Právnická osoba');

INSERT INTO PRAVFORM

 VALUES(2, 'Fyzická osoba');

--VYTVORENIE TABULKY PRE SEQUENCIU, GENEROVANIE NOVEHO ID PRE MYSQL

create table All_Sequence(

  seq_name varchar(30),

  start int, 

  increment int, 

  currval int,

  flag char(1)); 

--VYTVORENIE PROCEDURY SEQUENCIE

CREATE DEFINER = 'root'@'localhost'

PROCEDURE Get_Sequence(

        in SeqName varchar(255),

        out NextVal int

    )

    NOT DETERMINISTIC

    CONTAINS SQL

    SQL SECURITY DEFINER

    COMMENT ''

begin 

declare rowcount int;

declare v_flag char(1); 

select Count(*) into rowcount from All_Sequence where seq_name=SeqName; 

if rowcount=0 then

  insert into All_Sequence values(SeqName,1,1,1,'A');

  set NextVal = 1; 

else

  set v_flag='N'; 

  while (v_flag='N') do 

    select flag into v_flag from All_Sequence where seq_name=SeqName; 

  end while; 

  update All_Sequence set flag='N' where seq_name=SeqName; 

  select currval+INCREMENT into NextVal from All_Sequence where seq_name=SeqName; 

  update All_Sequence set flag='A',currval=NextVal where seq_name=SeqName; 

 end if;

end;

COMMIT;