Mesaje recente

Members
Stats
  • Total Posts: 17786
  • Total Topics: 1234
  • Online Today: 10
  • Online Ever: 233
  • (26 June 2007, 07:28)
Users Online
Users: 0
Guests: 8
Total: 8

Author Topic: Stored procedures in Mysql  (Read 891 times)

0 Members and 1 Guest are viewing this topic.

Offline kquizak

  • Membru
  • ***
  • Posts: 181
  • Cookies: 4
  • just tilicar
Stored procedures in Mysql
« on: 10 December 2007, 22:40 »
Imi trebuie proceduri stocate pe mysql, mi se pare 'brainfucking' sintaxa din mysql(fata de oracle..poate si pt ca asta mi se pare naturala daca toata ziua scriu pl/sql ).Ma poate ajuta careva cu ceva simplu?

mysql> create procedure check_user(in pcnp bigint(13), out exista int)
    -> begin
    -> declare v_cnp bigint(13);
    -> declare v_exista int;
    -> declare cursor c for
    -> Select 1 from users where cnp = v_cnp;
    -> set v_cnp = pcnp;
    -> open c;
    -> fetch c into v_exista;
    -> close c;
    -> set exista = v_exista;
    -> end;//
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 'cursor c for
Select 1 from users where cnp = v_cnp;
set v_cnp = pcnp;
open c;
f' at line 5
mysql>       

ce vreau eu e asa
create or replace function check_user(pcnp in number(13))  return boolean is
 cursor c is
  select true from users where cnp = pcnp;
v_exista boolean := false;
begin
  open c;
  fetch c into v_exista;
  close c;
  return v_exista;
end;
network-tools.ath.cx ---nmap yourself

Offline kman

  • God of the code-monkeys
  • Membru
  • ***
  • Posts: 192
  • Cookies: 24
Re: Stored procedures in Mysql
« Reply #1 on: 10 December 2007, 23:17 »
adica simplificat

Code: [Select]
create or replace function check_user(pcnp in number(13)) return boolean is
n number;
begin
  select count(1) into n from users where cnp=pcnp;
  if n>0 then
    return true
  else
    return false;
  end if;
end;

ceea ce s-ar putea sa mearga si pe mysql unde nu ai variabile de tip cursor din cate stiu eu.

PS: posibil sa mai fie ceva imbecilitati in ce am scris mai sus ca nu am mai scris SQL de vreo 6 luni.

Offline Praetor

  • Green,bad and (not so) ugly ogre
  • Membru
  • ***
  • Posts: 142
  • Cookies: 12
Re: Stored procedures in Mysql
« Reply #2 on: 11 December 2007, 12:15 »
Cred ca totusi trebuie procedure si nu function. Si la return sau la un param out merge 0 sau 1 :)