In articolul asta o sa vedem cum putem vedea toate rutinele din interiorul bazei noastre de date MySQL.
Funcțiile de mysql, procedurile se numesc rutine. Pentru a vedea informațiile/definitia lor puteți folosi comanda SHOW:
mysql > SHOW PROCEDURE STATUSG
*************************** 1 . row ***************************
Db : BXXX_234
Name : c333
Type : PROCEDURE
Definer : root @ localhost
Modified : 2010 - 03 - 18 13 : 04 : 34
Created : 2010 - 03 - 18 13 : 04 : 34
Security_type : DEFINER
Comment :
*************************** 2 . row ***************************
Db : BXXX_234
Name : m222
Type : PROCEDURE
Definer : root @ localhost
Modified : 2010 - 03 - 18 13 : 04 : 34
Created : 2010 - 03 - 18 13 : 04 : 34
Security_type : DEFINER
Comment :
*************************** 3 . row ***************************
Db : BXXX_234
Name : P321
Type : PROCEDURE
Definer : root @ localhost
Modified : 2010 - 03 - 18 13 : 04 : 34
Created : 2010 - 03 - 18 13 : 04 : 34
Security_type : DEFINER
Comment :
*************************** 4 . row ***************************
Db : BXXX_234
Name : produtos123
Type : PROCEDURE
Definer : root @ localhost
Modified : 2010 - 03 - 18 13 : 04 : 34
Created : 2010 - 03 - 18 13 : 04 : 34
Security_type : DEFINER
Comment :
Sau puteți folosi sintaxa de mai jos pentru a obține definiția pentru o singura rutină:
mysql > SHOW PROCEDURE STATUS like 'remove%' G
*************************** 1 . row ***************************
Db : BASE_XXX
Name : remove_garbage
Type : PROCEDURE
Definer : root @ localhost
Modified : 2010 - 03 - 18 13 : 04 : 34
Created : 2010 - 03 - 18 13 : 04 : 34
Security_type : DEFINER
Comment :
1 row in set ( 0 . 06 sec )
Un alt mod pentru tine de a lista rotinele este de a vedea tabela INFORMATION_SCHEMA.ROUTINES
Veți primi mai multe informații cu privire la rotine, cum ar fi tipul acesteia și definiția ei.
mysql > select * from INFORMATION_SCHEMA . ROUTINESG
*************************** 1 . row ***************************
SPECIFIC_NAME : cria_123
ROUTINE_CATALOG : NULL
ROUTINE_SCHEMA : BASE_xxx
ROUTINE_NAME : c123
ROUTINE_TYPE : PROCEDURE
DTD_IDENTIFIER : NULL
ROUTINE_BODY : SQL
ROUTINE_DEFINITION : BEGIN
IF ( EXISTS ( SELECT 1 FROM pedidos WHERE id = pedidoid AND codigo_pedido IS NULL LIMIT 1 )) THEN
SET autocommit = 0 ;
IF ( EXISTS ( SELECT 1 FROM csras WHERE pedidos_id = pedidoid ) OR
EXISTS ( SELECT 1 FROM csras WHERE id = pedidoid )) THEN
DELETE FROM csras where id = pedidoid ;
DELETE FROM csras WHERE pedidos_id = pedidoid ;
END IF ;
INSERT INTO csras SELECT * FROM pedidos_itens WHERE pedidos_id = pedidoid ;
INSERT INTO csras SELECT * FROM pedidos WHERE id = pedidoid ;
COMMIT ;
if ( EXISTS ( SELECT 1 FROM csras WHERE id = pedidoid LIMIT 1 )) THEN
DELETE FROM pedidos_historico WHERE pedidos_id = pedidoid ;
DELETE FROM pedidos_itens WHERE pedidos_id = pedidoid ;
DELETE FROM modulos_pagamento_dados WHERE pedidos_id = pedidoid ;
DELETE FROM pedidos WHERE id = pedidoid ;
COMMIT ;
END IF ;
END IF ;
END
EXTERNAL_NAME : NULL
EXTERNAL_LANGUAGE : NULL
PARAMETER_STYLE : SQL
IS_DETERMINISTIC : YES
SQL_DATA_ACCESS : CONTAINS SQL
SQL_PATH : NULL
SECURITY_TYPE : DEFINER
CREATED : 2010 - 03 - 18 13 : 04 : 34
LAST_ALTERED : 2010 - 03 - 18 13 : 04 : 34
SQL_MODE : NO_AUTO_VALUE_ON_ZERO
ROUTINE_COMMENT :