Stored Procedure é uma coleção de comandos em SQL para otimização de Banco de dados. Encapsula tarefas repetitivas, aceita parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na execução). O procedimento armazenado pode reduzir o tráfego na rede, melhorar a performance, criar mecanismos de segurança, etc, e estão disponíveis a partir da versão 5.0 do MySQL.

Exemplo de Stored Procedure a ser criada

1. Acesse o phpMyAdmin de sua base MySQL.

2. Vamos criar uma tabela contendo todos os estados do Brasil com a query abaixo:

DELIMITER //    CREATE TABLE rsestados (  uf CHAR(2) NOT NULL,  nome VARCHAR(19) NOT NULL,  UNIQUE KEY incideuf (uf)  )   ENGINE=MyISAM DEFAULT CHARSET=latin1  END //   DELIMITER ;

3 – Vamos inserir os dados na tabela rsestados

 INSERT INTO rsestados VALUES ('AC', 'Acre');  INSERT INTO rsestados VALUES ('AL', 'Alagoas');  INSERT INTO rsestados VALUES ('AM', 'Amazonas');  INSERT INTO rsestados VALUES ('AP', 'Amapa');  INSERT INTO rsestados VALUES ('BA', 'Bahia');  INSERT INTO rsestados VALUES ('CE', 'Ceara');  INSERT INTO rsestados VALUES ('DF', 'Distrito Federal');  INSERT INTO rsestados VALUES ('ES', 'Espirito Santo');  INSERT INTO rsestados VALUES ('GO', 'Goias');  INSERT INTO rsestados VALUES ('MA', 'Maranhao');  INSERT INTO rsestados VALUES ('MG', 'Minas Gerais');  INSERT INTO rsestados VALUES ('MS', 'Mato Grosso do Sul');  INSERT INTO rsestados VALUES ('MT', 'Mato Grosso');  INSERT INTO rsestados VALUES ('PA', 'Para');  INSERT INTO rsestados VALUES ('PB', 'Paraiba');  INSERT INTO rsestados VALUES ('PE', 'Pernambuco');  INSERT INTO rsestados VALUES ('PI', 'Piaui');  INSERT INTO rsestados VALUES ('PR', 'Parana');  INSERT INTO rsestados VALUES ('RJ', 'Rio de Janeiro');  INSERT INTO rsestados VALUES ('RN', 'Rio Grande do Norte');  INSERT INTO rsestados VALUES ('RO', 'Rondonia');  INSERT INTO rsestados VALUES ('RR', 'Roraima');  INSERT INTO rsestados VALUES ('RS', 'Rio Grande do Sul');  INSERT INTO rsestados VALUES ('SC', 'Santa Catarina');  INSERT INTO rsestados VALUES ('SE', 'Sergipe');  INSERT INTO rsestados VALUES ('SP', 'Sao Paulo');  INSERT INTO rsestados VALUES ('TO', 'Tocantins');

4 – Para a criação da Stored Procedure, o delimitador não pode ser o caractere ponto-e-vírgula “;”, então defina outro delimitador, como “//” e execute a query

DELIMITER //    CREATE PROCEDURE totalEstados (OUT parametro INT)  BEGIN  SELECT COUNT(*) INTO parametro FROM rsestados;  END //   DELIMITER ;

– Neste exemplo foi criado uma SP que conta quantos registros há na tabela.


5 – Para executar a Stored Procedure criada:

CALL totalEstados(@a); SELECT @a; 

–> aparecerá:

+------+ |  @a  | +------+ |  27  | +------+ 

Para mais informações sobre como trabalhar com Stored Procedures no MySQL, clique no link: http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-syntax.html

Veja também


Ativando um base de dados MySQL

Contratando um plano de Hospedagem você tem direito a utilizar bases MySQL desejar já inclusas no preço de seu plano.

Para ativar as bases MySQL, siga estas instruções:

Stored procedures Acesse o Painel de Controle e entre com seu login de cliente e senha.

Stored procedures Em Hospedagem de Sites, clique no link Site, da hospedagem desejada, conforme imagem abaixo:
Stored procedures

Stored procedures Em Serviços Inclusos no Plano, clique em Administrar na área Banco de Dados.

Stored procedures

Stored procedures Na nova página clique em Instalar e confirme a operação

Stored procedures

Stored procedures Aguarde a finalização da operação

Stored procedures

Obs.: Você também poderá Encerrar qualquer base MySQL que já estiver instalada.

O endereço do Servidor/Host que utilizará em sua aplicação terá o formato mysqlxx.meudominio em que o xx é o número da base ativada (mysql01, mysql02, …, mysql99, etc) e não localhost’
para acessar o PhpMyAdmin

IMPORTANTE

  • A base de dados ativada estará disponível para uso em até 1 hora.
  • Sempre que ativar uma nova base de dados, o nome dela estará relacionado ao login administrativo de seu site, ou seja, as novas bases receberão o nome “login1”, “login2”, “login3” e assim por diante. Essa nomenclatura é padrão de nosso sistema de instalação e não pode ser alterada.

Veja também


Aplicação

Você pode atribuir descrições para os seus bancos de dados MySQL. A descrição serve para facilitar a sua identificação e não afeta em nada o funcionamento do seu banco.

Para isto, siga as instruções a seguir:

Adicionando uma descrição à uma base MySQL

Stored procedures Acesse o Painel de controle e entre com seu login de cliente e senha.

Stored procedures Em Hospedagem de Sites, clique no link Site, da hospedagem desejada, conforme imagem abaixo:

Stored procedures

Stored procedures Em Serviços Inclusos No Plano, ache o banco MySQL que deseja alterar.

Stored procedures

Stored procedures Digite uma nova descrição para este banco.

Stored procedures

Stored procedures Clique no botão Alterar e aguarde a mensagem de sucesso.

Quando você acessar o seu painel de controle novamente, a descrição associada será mostrada na listagem dos seus bancos MySQL.

Stored procedures

Veja também


Ativando um base de dados MySQL

Contratando um plano de Hospedagem você tem direito a utilizar bases MySQL desejar já inclusas no preço de seu plano.

Para ativar as bases MySQL, siga estas instruções:

Stored procedures Acesse o Painel de Controle e entre com seu login de cliente e senha.

Stored procedures Em Hospedagem de Sites, clique no link Site, da hospedagem desejada, conforme imagem abaixo:
Stored procedures

Stored procedures Em Serviços Inclusos no Plano, clique em Administrar na área Banco de Dados.

Stored procedures

Stored procedures Na nova página clique em Instalar e confirme a operação

Stored procedures

Stored procedures Aguarde a finalização da operação

Stored procedures

Obs.: Você também poderá Encerrar qualquer base MySQL que já estiver instalada.

O endereço do Servidor/Host que utilizará em sua aplicação terá o formato mysqlxx.meudominio em que o xx é o número da base ativada (mysql01, mysql02, …, mysql99, etc) e não localhost’
para acessar o PhpMyAdmin

IMPORTANTE

  • A base de dados ativada estará disponível para uso em até 1 hora.
  • Sempre que ativar uma nova base de dados, o nome dela estará relacionado ao login administrativo de seu site, ou seja, as novas bases receberão o nome “login1”, “login2”, “login3” e assim por diante. Essa nomenclatura é padrão de nosso sistema de instalação e não pode ser alterada.

Veja também


Esse artigo ajudou você?

38 respostas para “Stored procedures”

  1. Learn More Here legal news central

    I just couldn’t depart your web site before suggesting that I actually enjoyed the standard information a person provide for your visitors? Is going to be back often in order to check up on new posts

    Responder
  2. satchel

    Pretty! This was a really wonderful article. Thank you for supplying this info.

    Responder
  3. Renaldo Doberstein

    What your stating is totally accurate. I know that everyone must say the exact same issue, but I just believe that you set it in a way that absolutely everyone can recognize. I also appreciate the images you place in the following. They suit so properly with what youre hoping to say. Im certain youll achieve so a lot of men and women with what youve obtained to say.

    Responder
  4. Jetta Crippin

    I’m new to your blog and i really appreciate the nice posts and great layout.”,~;*

    Responder
  5. Charlotte exterminators

    If you are looking for the ultimate pest control company, look no further. These guys rock! The best out there.

    Responder
  6. Pretty Gaming

    Excellent site you’ve got here.. It’s difficult to find good quality writing like yours these days. I honestly appreciate individuals like you! Take care!!

    Responder
  7. Domenic Canestrini

    Can I just say thats a relief to get a person that truly knows what theyre discussing on the web. You actually understand how to bring a concern to light and produce it important. The diet need to read this and can see this side with the story. I cant believe youre not more well-liked since you undoubtedly contain the gift.

    Responder
  8. Taylor Anderson

    Hey! I enjoyed your blog post. Keep making articles like this. I’ll be back to see more articles like this one. It was very creative. Till next time!

    Responder
  9. 슬롯

    Best view i have ever seen !

    Responder
  10. Ross Wilhelmy

    An interesting discussion may be valued at comment. I do believe that you ought to write on this topic, may well certainly be a taboo subject but usually individuals are not enough to communicate on such topics. To a higher. Cheers

    Responder
  11. Gabriel Veader

    There are some attention-grabbing deadlines in this article however I don’t know if I see all of them middle to heart. There’s some validity but I will take maintain opinion till I look into it further. Good article , thanks and we want more! Added to FeedBurner as effectively

    Responder
  12. is tax preparation fee tax deductible

    Heya i’m for the first time here. I came across this board and I find It really useful & it helped me out much. I hope to give something back and help others like you aided me.

    Responder
  13. my sources

    I just want to tell you that I am very new to weblog and actually savored your web page. Almost certainly I’m going to bookmark your blog . You definitely have incredible article content. Regards for sharing with us your website.

    Responder
  14. CHRISSON Electric Bikes for sale

    You actually make it appear so easy together with your presentation however I to find this matter to be actually something which I believe I’d never understand. It seems too complex and very large for me. I’m having a look forward in your subsequent post, I¡¦ll try to get the hang of it!

    Responder
  15. foundation companies in san antonio tx

    Pretty section of content. I just stumbled upon your blog and in accession capital to assert that I acquire in fact enjoyed account your blog posts. Anyway I’ll be subscribing to your augment and even I achievement you access consistently quickly.

    Responder
  16. emergency plumber near me browse around here

    whoah this blog is wonderful i love reading your posts. Keep up the good work! You know, many people are looking around for this information, you can help them greatly.

    Responder
  17. 안전토토

    Best view i have ever seen !

    Responder
  18. buy home warranty

    Pretty! This has been an extremely wonderful post.Thank
    you for supplying this info.

    Responder
  19. donate vehicle to charity

    Would love to perpetually get updated outstanding web blog!

    Responder
  20. VitalFlow

    I would like to thank you for the efforts you have put in penning this blog. I’m hoping to check out the same high-grade blog posts from you later on as well. In fact, your creative writing abilities has encouraged me to get my own, personal website now 😉

    Responder
  21. 슬롯 게임

    Best view i have ever seen !

    Responder
  22. 마사지구인구직

    It’s a envious article. It’s very remarkable and new. Who happen to be you to write this particular special article?

    Responder
  23. Robyn

    Way cool! Some very valid points! I appreciate you penning this post and thhe rest of
    the website is also really good.

    Responder
  24. local plumbers Look At This

    You completed certain good points there. I did a search on the theme and found the majority of persons will consent with your blog.

    Responder
  25. home remodeling pictures

    Somebody essentially help to make seriously posts I would state. This is the first time I frequented your website page and thus far? I surprised with the research you made to create this particular publish extraordinary. Wonderful job!

    Responder
  26. porno

    Thanks for expressing your ideas. One thing is that scholars have an option between federal student loan and also a private student loan where it’s easier to choose student loan online debt consolidation than over the federal education loan.

    Responder
  27. Pool table refelting

    I have not checked in here for some time since I thought it was getting boring, but the last several posts are good quality so I guess I’ll add you back to my everyday bloglist. You deserve it my friend 🙂

    Responder
  28. White glove delivery

    Oh my goodness! a tremendous article dude. Thanks Nonetheless I am experiencing issue with ur rss . Don’t know why Unable to subscribe to it. Is there anybody getting an identical rss problem? Anybody who is aware of kindly respond. Thnkx

    Responder
  29. important source

    I just want to say I am newbie to weblog and definitely savored this web page. Most likely I’m likely to bookmark your website . You absolutely have terrific writings. Thanks a lot for sharing with us your web site.

    Responder
  30. digital marketing definition

    Very well written story. It will be beneficial to anyone who employess it, including yours truly :). Keep up the good work – for sure i will check out more posts.

    Responder
  31. visite site

    It’s really a great and useful piece of info. I am glad that you shared this helpful information with us. Please keep us informed like this. Thanks for sharing.

    Responder
  32. alberta car towing

    I believe this web site holds some very superb info for everyone :D. “The test of every religious, political, or educational system is the man that it forms.” by Henri Frdric Amiel.

    Responder
  33. sportsurge

    Wow! Thank you! I constantly needed to write on my website something like that. Can I take a fragment of your post to my blog?

    Responder
  34. Virginia pool table movers

    I just wanted to type a simple comment in order to thank you for these amazing tips and hints you are posting at this site. My extensive internet investigation has now been recognized with reasonable information to go over with my classmates and friends. I would tell you that most of us site visitors are undoubtedly blessed to dwell in a notable network with so many marvellous individuals with beneficial plans. I feel really lucky to have encountered your entire web pages and look forward to some more enjoyable minutes reading here. Thank you once more for everything.

    Responder
  35. discover this info here

    I am curious to find out what blog system you have been using? I’m experiencing some small security problems with my latest blog and I would like to find something more safe. Do you have any recommendations?

    Responder
  36. Mariano Glenna

    I simply want to tell you that I am just all new to blogs and seriously liked your page. Most likely I’m planning to bookmark your website . You definitely have outstanding well written articles. Cheers for revealing your web page.

    Responder

Comente sobre este conteúdo! Escreva o que pensa, complemente, vamos discutir. O conhecimento é livre!