Listing the source code file Manual_SQLScript_MySQL.5.1.txt


--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;


Return to original page