SQL/MySQL/Procedure Function/Procedure Data Dictionary

Материал из SQL эксперт
Перейти к: навигация, поиск

Show the procedure"s database, name, language, security type, parameter list, body, definer, comment, and other information

   <source lang="sql">

mysql> mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE myProc (IN in_count INT) 

   -> BEGIN
   ->     DECLARE count INT default 0;
   ->
   ->     increment: LOOP
   ->     SET count = count + 1;
   ->     IF count < 20 THEN ITERATE increment; END IF;
   ->     IF count > in_count THEN LEAVE increment;
   ->     END IF;
   ->     END LOOP increment;
   ->
   ->     SELECT count;
   -> END
   -> //

Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> call myProc(5); +-------+ | count | +-------+ | 20 | +-------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> mysql> SELECT * FROM mysql.proc WHERE name = "myProc"\G

                                                      • 1. row ***************************
             db: test
           name: myProc
           type: PROCEDURE
  specific_name: myProc
       language: SQL
sql_data_access: CONTAINS_SQL

is_deterministic: NO 

  security_type: DEFINER
     param_list: IN in_count INT
        returns:
           body: BEGIN
   DECLARE count INT default 0;
   increment: LOOP
   SET count = count + 1;
   IF count < 20 THEN ITERATE increment; END IF;
   IF count > in_count THEN LEAVE increment;
   END IF;
   END LOOP increment;
   SELECT count;

END 

        definer: root@localhost
        created: 2007-07-23 18:58:35
       modified: 2007-07-23 18:58:35
       sql_mode:
        comment:

1 row in set (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> 

       </source>


To get more details on a stored procedure, use the SHOW CREATE PROCEDURE statement

   <source lang="sql">

mysql> mysql> mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE myProc (IN in_count INT) 

   -> BEGIN
   ->     DECLARE count INT default 0;
   ->
   ->     increment: LOOP
   ->     SET count = count + 1;
   ->     IF count < 20 THEN ITERATE increment; END IF;
   ->     IF count > in_count THEN LEAVE increment;
   ->     END IF;
   ->     END LOOP increment;
   ->
   ->     SELECT count;
   -> END
   -> //

Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> call myProc(5); +-------+ | count | +-------+ | 20 | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> SHOW CREATE PROCEDURE test.myProc\G

                                                      • 1. row ***************************
      Procedure: myProc
       sql_mode:

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `myProc`(IN in_count INT) BEGIN 

   DECLARE count INT default 0;
   increment: LOOP
   SET count = count + 1;
   IF count < 20 THEN ITERATE increment; END IF;
   IF count > in_count THEN LEAVE increment;
   END IF;
   END LOOP increment;
   SELECT count;

END 1 row in set (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> 

       </source>


Use SELECT statement to access on the proc table in the mysql database

   <source lang="sql">

mysql> mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE myProc (IN in_count INT) 

   -> BEGIN
   ->     DECLARE count INT default 0;
   ->
   ->     increment: LOOP
   ->     SET count = count + 1;
   ->     IF count < 20 THEN ITERATE increment; END IF;
   ->     IF count > in_count THEN LEAVE increment;
   ->     END IF;
   ->     END LOOP increment;
   ->
   ->     SELECT count;
   -> END
   -> //

Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ; mysql> mysql> call myProc(5); +-------+ | count | +-------+ | 20 | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> SELECT * FROM mysql.proc WHERE name = "myProc"\G

                                                      • 1. row ***************************
             db: test
           name: myProc
           type: PROCEDURE
  specific_name: myProc
       language: SQL
sql_data_access: CONTAINS_SQL

is_deterministic: NO 

  security_type: DEFINER
     param_list: IN in_count INT
        returns:
           body: BEGIN
   DECLARE count INT default 0;
   increment: LOOP
   SET count = count + 1;
   IF count < 20 THEN ITERATE increment; END IF;
   IF count > in_count THEN LEAVE increment;
   END IF;
   END LOOP increment;
   SELECT count;

END 

        definer: root@localhost
        created: 2007-07-23 18:58:34
       modified: 2007-07-23 18:58:34
       sql_mode:
        comment:

1 row in set (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> 

       </source>


Viewing Stored Procedures

   <source lang="sql">

mysql> mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE myProc (IN in_count INT) 

   -> BEGIN
   ->     DECLARE count INT default 0;
   ->
   ->     increment: LOOP
   ->     SET count = count + 1;
   ->     IF count < 20 THEN ITERATE increment; END IF;
   ->     IF count > in_count THEN LEAVE increment;
   ->     END IF;
   ->     END LOOP increment;
   ->
   ->     SELECT count;
   -> END
   -> //

Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> call myProc(5); +-------+ | count | +-------+ | 20 | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> SHOW PROCEDURE STATUS\G

                                                      • 1. row ***************************
          Db: test
        Name: myProc
        Type: PROCEDURE
     Definer: root@localhost
    Modified: 2007-07-23 18:58:34
     Created: 2007-07-23 18:58:34

Security_type: DEFINER 

     Comment:
                                                      • 2. row ***************************
          Db: test
        Name: simpleproc
        Type: PROCEDURE
     Definer: root@localhost
    Modified: 2007-07-17 18:50:06
     Created: 2007-07-17 18:50:06

Security_type: DEFINER 

     Comment:

2 rows in set (0.02 sec) mysql> mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> 

       </source>


Viewing Stored Procedures with a LIKE clause

   <source lang="sql">

mysql> mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE myProc (IN in_count INT) 

   -> BEGIN
   ->     DECLARE count INT default 0;
   ->
   ->     increment: LOOP
   ->     SET count = count + 1;
   ->     IF count < 20 THEN ITERATE increment; END IF;
   ->     IF count > in_count THEN LEAVE increment;
   ->     END IF;
   ->     END LOOP increment;
   ->
   ->     SELECT count;
   -> END
   -> //

Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> call myProc(5); +-------+ | count | +-------+ | 20 | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> mysql> SHOW PROCEDURE STATUS LIKE "my%"\G

                                                      • 1. row ***************************
          Db: test
        Name: myProc
        Type: PROCEDURE
     Definer: root@localhost
    Modified: 2007-07-23 18:58:34
     Created: 2007-07-23 18:58:34

Security_type: DEFINER 

     Comment:

1 row in set (0.02 sec) mysql> mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> 

       </source>
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Procedure_Function/Procedure_Data_Dictionary&oldid=5431»