Description:
I've got a big problem with cursors in procedure
My procedure dosn't work and has an error :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'Declare kurs CURSOR for select saldo from konto where nrkonta=1;
insert into r' at line 12
PROCEDURE :
delimiter //
CREATE PROCEDURE przelew
(odbiorca DEC (20,0), kwota DEC (10,2))
BEGIN
Declare a DEC (10,2);
Declare nadawca DEC (20,0);
Declare nrpesel DEC (11,0);
Declare kurs01 CURSOR for
select pesel from klient where uzytkownik
=current_user;
open kurs01;
fetch kurs01 into nrpesel;
Declare kurs02 cursor for
select nrkonta from konto where wlasciciel=nrpesel;
open kurs02;
fetch kurs02 into nadawca;
Declare kurs CURSOR for
select saldo from konto where nrkonta=nadawca;
Open kurs;
Fetch kurs INTO a;
if a>=kwota then
update konto set saldo =saldo -kwota
where nrkonta=nadawca;
update konto set saldo=saldo + kwota
where NRkonta=odbiorca;
insert into rejestr values(Now(),nadawca,odbiorca,kwota);
end if;
close kurs02;
close kurs01;
close kurs;
END
//
delimiter ;
TABLES:
create table klient
(
uzytkownik varchar(20),
nazwisko varchar (30),
imie varchar (20),
pesel int not null primary key UNIQUE,
adres varchar (40)
)
type =InnoDB;
create table konto
(
saldo DEC (10,2) DEFAULT 0,
nrkonta DEC (20,0) UNIQUE ,
wlasciciel int not null UNIQUE references klient(pesel),
CONSTRAINT wieksze check (saldo>=0)
)
type =InnoDB;
create table rejestr
(
dataczas timestamp,
nadawca DEC (20,0) ,
odbiorca DEC (20,0) ,
kwota DEC (10,2) NOT NULL,
CONSTRAINT wieksze check (nadawca>=0),
CONSTRAINT odbiorca check (saldo>=0),
CONSTRAINT wieksze check (kwota>=0)
)
type =InnoDB;
How to repeat:
As U want
Suggested fix:
Right now I know that first cursor work but when I want to declare second my procedure brokes
For example this part works corettlly :
delimiter //
CREATE PROCEDURE przelew
(odbiorca DEC (20,0), kwota DEC (10,2))
BEGIN
Declare a DEC (10,2);
Declare nadawca DEC (20,0);
Declare nrpesel DEC (11,0);
Declare kurs01 CURSOR for
select pesel from klient where uzytkownik
=current_user;
open kurs01;
fetch kurs01 into nrpesel;
close kurs01;
insert into rejestr values(Now(),nrpesel,odbiorca,kwota);
END
//
delimiter ;
Description: I've got a big problem with cursors in procedure My procedure dosn't work and has an error : ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Declare kurs CURSOR for select saldo from konto where nrkonta=1; insert into r' at line 12 PROCEDURE : delimiter // CREATE PROCEDURE przelew (odbiorca DEC (20,0), kwota DEC (10,2)) BEGIN Declare a DEC (10,2); Declare nadawca DEC (20,0); Declare nrpesel DEC (11,0); Declare kurs01 CURSOR for select pesel from klient where uzytkownik =current_user; open kurs01; fetch kurs01 into nrpesel; Declare kurs02 cursor for select nrkonta from konto where wlasciciel=nrpesel; open kurs02; fetch kurs02 into nadawca; Declare kurs CURSOR for select saldo from konto where nrkonta=nadawca; Open kurs; Fetch kurs INTO a; if a>=kwota then update konto set saldo =saldo -kwota where nrkonta=nadawca; update konto set saldo=saldo + kwota where NRkonta=odbiorca; insert into rejestr values(Now(),nadawca,odbiorca,kwota); end if; close kurs02; close kurs01; close kurs; END // delimiter ; TABLES: create table klient ( uzytkownik varchar(20), nazwisko varchar (30), imie varchar (20), pesel int not null primary key UNIQUE, adres varchar (40) ) type =InnoDB; create table konto ( saldo DEC (10,2) DEFAULT 0, nrkonta DEC (20,0) UNIQUE , wlasciciel int not null UNIQUE references klient(pesel), CONSTRAINT wieksze check (saldo>=0) ) type =InnoDB; create table rejestr ( dataczas timestamp, nadawca DEC (20,0) , odbiorca DEC (20,0) , kwota DEC (10,2) NOT NULL, CONSTRAINT wieksze check (nadawca>=0), CONSTRAINT odbiorca check (saldo>=0), CONSTRAINT wieksze check (kwota>=0) ) type =InnoDB; How to repeat: As U want Suggested fix: Right now I know that first cursor work but when I want to declare second my procedure brokes For example this part works corettlly : delimiter // CREATE PROCEDURE przelew (odbiorca DEC (20,0), kwota DEC (10,2)) BEGIN Declare a DEC (10,2); Declare nadawca DEC (20,0); Declare nrpesel DEC (11,0); Declare kurs01 CURSOR for select pesel from klient where uzytkownik =current_user; open kurs01; fetch kurs01 into nrpesel; close kurs01; insert into rejestr values(Now(),nrpesel,odbiorca,kwota); END // delimiter ;