Cum extragem informatii despre rutine in MySQL

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:

Copyright 2020. All Rights Reserved