MySQL Forums
Forum List  »  Newbie

Re: Stored Function
Posted by: Barry Galbraith
Date: January 02, 2017 06:30PM

Remove table master (if it exists) from the database
drop table if exists master;

create a table named master, with one field named callno of type int
create table master( callno int );

insert one row into table master
insert into master set callno=1;

select the one row to demonstrate the existing data
select callno from master;

create a stored procedure called callnoincr that increments field callno and returns the value stored
drop function if exists callnoincr;
delimiter go
create function callnoincr() returns int deterministic
begin
  declare n int default 0;
  set n = (select callno from master);
  update master set callno=n+1;
  return (select callno from master);
end;
go
delimiter ;

demonstrate how to use the new stored procedure
select callnoincr();

If your table contains many such fields, you can modify the SP to require a parameter of the field name you want increment, and return the value of that field.

Good luck,
Barry.

Options: ReplyQuote


Subject
Written By
Posted
January 02, 2017 03:18PM
January 02, 2017 04:38PM
January 02, 2017 05:54PM
January 02, 2017 06:13PM
Re: Stored Function
January 02, 2017 06:30PM
January 02, 2017 07:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.