Procedimientos

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| libreria           |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
8 rows in set (0.11 sec)
 
mysql> use libreria;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura         |
| autor              |
| editorial          |
| liautedi           |
| libro              |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> select * from autor;
+----------+----------------------+
| codautor | nombre               |
+----------+----------------------+
| A01      | Luis Joyanes         |
| A02      | Jorge Vasquez Posada |
| A03      | Jhon Soars           |
| A04      | Riaz Khadem          |
| A05      | Robert Lorber        |
| A06      | Mario Dream          |
+----------+----------------------+
6 rows in set (0.05 sec)
 
mysql> delimiter //;
mysql> delimiter //
mysql> create procedure listar_autor()
    -> begin
    -> select * from autor;
    -> end
    -> //
Query OK, 0 rows affected (0.45 sec)
 
mysql> delimiter ;
mysql> show procedure status;
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | listar_autor | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.14 sec)
 
mysql> show procedure status;
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | listar_autor | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)
 
mysql> show create procedure listar_autor;
+--------------+------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure    | sql_mode               | Create Procedure                                                                            | character_set_client | collation_connection | Database Collation |
+--------------+------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| listar_autor | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `listar_autor`()
begin
select * from autor;
end | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+--------------+------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.03 sec)
 
mysql> call listar_autor;
+----------+----------------------+
| codautor | nombre               |
+----------+----------------------+
| A01      | Luis Joyanes         |
| A02      | Jorge Vasquez Posada |
| A03      | Jhon Soars           |
| A04      | Riaz Khadem          |
| A05      | Robert Lorber        |
| A06      | Mario Dream          |
+----------+----------------------+
6 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.02 sec)
 
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01     | Calculo II          |       120 |  55000 | M01       |
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.01 sec)
 
 
mysql> show procedure status;
    -> ;
    -> show procedure status;
    -> //
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | listar_autor | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.06 sec)
 
mysql> delimiter ;
mysql> show procedure status;
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | listar_autor | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)
 
mysql> delimiter //
mysql> create procedure listar_libro()
    -> begin
    -> select * from libro;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)
 
mysql> delimiter ;
mysql> call listar_libro();
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01     | Calculo II          |       120 |  55000 | M01       |
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.03 sec)
 
Query OK, 0 rows affected (0.08 sec)
 
mysql> describe autor;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| codautor | char(25) | NO   | PRI | NULL    |       |
| nombre   | char(25) | NO   |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.05 sec)
 
mysql> insert into autor(codautor,nombre) values ('A07','Jair Conde');
Query OK, 1 row affected (0.14 sec)
 
mysql> call listar_autor();
+----------+----------------------+
| codautor | nombre               |
+----------+----------------------+
| A01      | Luis Joyanes         |
| A02      | Jorge Vasquez Posada |
| A03      | Jhon Soars           |
| A04      | Riaz Khadem          |
| A05      | Robert Lorber        |
| A06      | Mario Dream          |
| A07      | Jair Conde           |
+----------+----------------------+
7 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> describe autor;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| codautor | char(25) | NO   | PRI | NULL    |       |
| nombre   | char(25) | NO   |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)
 
mysql> delimiter //
mysql> create procedure insertar_autor(in codautor_ char(25),in nombre_ char(25))
    -> begin
    -> insert into autor(codautor,nombre) values (codautor_,nombre_);
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter ;
mysql> call insertar_autor('A08','Jorge Luis Borges');
Query OK, 1 row affected (0.05 sec)
 
mysql> call listar_autor;
+----------+----------------------+
| codautor | nombre               |
+----------+----------------------+
| A01      | Luis Joyanes         |
| A02      | Jorge Vasquez Posada |
| A03      | Jhon Soars           |
| A04      | Riaz Khadem          |
| A05      | Robert Lorber        |
| A06      | Mario Dream          |
| A07      | Jair Conde           |
| A08      | Jorge Luis Borges    |
+----------+----------------------+
8 rows in set (0.02 sec)
 
Query OK, 0 rows affected (0.02 sec)
 
mysql> show procedure status;
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name           | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | insertar_autor | PROCEDURE | root@localhost | 2015-11-07 12:35:28 | 2015-11-07 12:35:28 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| libreria | listar_autor   | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| libreria | listar_libro   | PROCEDURE | root@localhost | 2015-11-07 12:10:04 | 2015-11-07 12:10:04 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.05 sec)
 
mysql> describe libro;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| idlibro     | char(10) | NO   | PRI | NULL    |       |
| descripcion | char(20) | NO   |     | NULL    |       |
| nropagina   | int(3)   | NO   |     | NULL    |       |
| precio      | int(10)  | NO   |     | NULL    |       |
| codigomat   | char(3)  | NO   | MUL | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.03 sec)
 
mysql> delimiter //
mysql> create procedure insertar_libro(in idlibro_ char(10),in descripcion_ char(20),in nropagina_ int(3),precio_ int(10),codigomat_ char(3))
    -> begin
    -> insert into libro(idlibro,descripcion,nropagina,precio,codigomat) values (idlibro_,descripcion,nropagina_,precio_,codigomat_);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> show tables;
+--------------------+
| Tables_in_libreria |
+--------------------+
| asignatura         |
| autor              |
| editorial          |
| liautedi           |
| libro              |
+--------------------+
5 rows in set (0.03 sec)
 
mysql> alter table asignatura rename to materia;
Query OK, 0 rows affected (0.17 sec)
 
mysql> call insertar_libro('L09','java',500,100000,'M09');
Query OK, 1 row affected (0.02 sec)
 
mysql> call listar_libro();
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01     | Calculo II          |       120 |  55000 | M01       |
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
| L09     |                     |       500 | 100000 | M09       |
+---------+---------------------+-----------+--------+-----------+
9 rows in set (0.00 sec)
 
Query OK, 0 rows affected (0.03 sec)
 
mysql> drop procedure listar_libro; 
Query OK, 0 rows affected (0.06 sec)
 
mysql> show procedure status;
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name           | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | insertar_autor | PROCEDURE | root@localhost | 2015-11-07 12:35:28 | 2015-11-07 12:35:28 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| libreria | insertar_libro | PROCEDURE | root@localhost | 2015-11-07 12:47:16 | 2015-11-07 12:47:16 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| libreria | listar_autor   | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.08 sec)
 
mysql> drop procedure insertar_libro; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> show procedure status;
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name           | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| libreria | insertar_autor | PROCEDURE | root@localhost | 2015-11-07 12:35:28 | 2015-11-07 12:35:28 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
| libreria | listar_autor   | PROCEDURE | root@localhost | 2015-11-07 11:43:58 | 2015-11-07 11:43:58 | DEFINER       |         | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+----------+----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.03 sec)
 
mysql> delimiter //
mysql> create procedure insertar_libro(in idlibro_ char(10),in descripcion_ char(20),in nropagina_ int(3),precio_ int(10),codigomat_ char(3))
    -> begin
    -> insert into libro(idlibro,descripcion,nropagina,precio,codigomat) values (idlibro_,descripcion_,nropagina_,precio_,codigomat_);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L01     | Calculo II          |       120 |  55000 | M01       |
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
| L09     |                     |       500 | 100000 | M09       |
+---------+---------------------+-----------+--------+-----------+
9 rows in set (0.00 sec)
 
mysql> delete from libro where idlibro='L01';
Query OK, 1 row affected (0.09 sec)
 
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
| L09     |                     |       500 | 100000 | M09       |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
 
mysql> delete from libro where idlibro='L09';
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
+---------+---------------------+-----------+--------+-----------+
7 rows in set (0.00 sec)
 
mysql> call insertar_libro('L09','Java',300,100000,'M09');
Query OK, 1 row affected (0.06 sec)
 
mysql> select * from libro;
+---------+---------------------+-----------+--------+-----------+
| idlibro | descripcion         | nropagina | precio | codigomat |
+---------+---------------------+-----------+--------+-----------+
| L02     | BD II               |       150 |  65000 | M09       |
| L03     | Estructara de Datos |       180 |  85000 | M03       |
| L04     | Ingles              |       280 | 105000 | M04       |
| L05     | Admon en una Pagina |        70 |   7500 | M05       |
| L06     | Contabilidad I      |       170 |  27500 | M06       |
| L07     | Redes               |       370 |  32500 | M07       |
| L08     | Diagramacion        |        85 |  45000 | M08       |
| L09     | Java                |       300 | 100000 | M09       |
+---------+---------------------+-----------+--------+-----------+
8 rows in set (0.00 sec)
 
mysql> exit