mysql学习记录（十）--存储过程_MySQL
过程
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database change
mysql> select * from emp;
+------------+----------+------+--------+
| ename | hiredate | sal | deptno |
+------------+----------+------+--------+
| aaaaa | NULL | NULL | 1 |
| cccccccccc | NULL | NULL | 2 |
| ddddddddd | NULL | NULL | 3 |
| ffffff | NULL | NULL | 4 |
| ggg | NULL | NULL | 5 |
| a1 | NULL | NULL | 5 |
+------------+----------+------+--------+
6 rows in set (0.00 sec)
mysql> show create table emp \G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> DELIMITER &&
mysql> CREATE PROCEDURE num_from_employee (IN input_deptno int, OUT count_num INT )
-> READS SQL DATA
-> BEGIN
-> SELECT COUNT(*) FROM emp WHERE deptno=input_deptno ;
-> END &&
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call num_from_employee(5,@a);
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> call num_from_employee(1,@a);
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> create table inventory(
-> film_id int(11),
-> store_id int(11),
-> inventory_in_stock varchar(50)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into inventory(film_id,store_id,inventory_in_stock) values (1,2,'aaaaaaaa'), (3,4,'bbbb'), (5,6,'cccccccccc'), (7,8,'dddddd'), (9,10,'fff');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from inventory;
+---------+----------+--------------------+
| film_id | store_id | inventory_in_stock |
+---------+----------+--------------------+
| 1 | 2 | aaaaaaaa |
| 3 | 4 | bbbb |
| 5 | 6 | cccccccccc |
| 7 | 8 | dddddd |
| 9 | 10 | fff |
+---------+----------+--------------------+
5 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure film_in_stock(in p_film_id int,in p_store_id int,out p_film_count int)
-> reads sql data
-> begin
-> select film_id
-> from inventory
-> where film_id = p_film_id
-> and store_id = p_store_id;
-> select found_rows() into p_film_count;
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call film_in_stock(5,6,@a);
+---------+
| film_id |
+---------+
| 5 |
+---------+
1 row in set (0.01 sec)
Query OK, 1 row affected (0.01 sec)
mysql> show create procedure film_in_stock \G;
*************************** 1. row ***************************
Procedure: film_in_stock
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(in p_film_id int,in p_store_id int,out p_film_count int)
READS SQL DATA
begin
select film_id
from inventory
where film_id = p_film_id
and store_id = p_store_id;
select found_rows() into p_film_count;
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> create table actor(
-> actor_id int(11) NOT NULL AUTO_INCREMENT ,
-> first_name varchar(30),
-> last_name varchar(30),
-> PRIMARY KEY (actor_id)
-> ) engine = innodb charset = utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter $$
mysql> create procedure actor_insert()
-> begin
-> set @x = 1;
-> insert into actor(actor_id,first_name,last_name) values (201,'Test',201);
-> set @x = 2;
-> insert into actor(actor_id,first_name,last_name) values(1,'Test','1');
-> set @x = 3;
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> call actor_insert();
Query OK, 0 rows affected (0.02 sec)
mysql> call actor_insert();
ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'
mysql> select @x;
+------+
| @x |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure actor_insert_new()
-> begin
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
-> set @x = 1;
-> insert into actor(actor_id,first_name,last_name) values (201,'Test',201);
-> set @x = 2;
-> insert into actor(actor_id,first_name,last_name) values(1,'Test','1');
-> set @x = 3;
-> end $$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call actor_insert_new();
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> call actor_insert_new();
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @x,@x2;
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> show create table payment \G;
*************************** 1. row ***************************
Table: payment
Create Table: CREATE TABLE `payment` (
`staff_id` int(11) DEFAULT NULL,
`amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> select * from payment;
+----------+--------+
| staff_id | amount |
+----------+--------+
| 1 | 10000 |
| 2 | 20000 |
| 3 | 30000 |
| 4 | 400000 |
| 5 | 500000 |
+----------+--------+
5 rows in set (0.01 sec)
mysql> delimiter $$
mysql> create procedure payment_stat()
-> begin
-> DECLARE i_staff_id int;
-> DECLARE d_amount int;
-> declare tmp_name varchar(30) default "";
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0 ;
-> set @x2 = 0 ;
->
-> open cur_payment;
-> fetch cur_payment into i_staff_id,d_amount;
-> while(i_staff_id <=3 )
-> do
-> if i_staff_id < 3 then
-> select i_staff_id,d_amount;
-> end if;
-> fetch cur_payment into i_staff_id,d_amount;
-> end while;
-> close cur_payment;
->
-> select @x1,@x2;
-> end;
-> $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call payment_stat();
+------------+----------+
| i_staff_id | d_amount |
+------------+----------+
| 1 | 10000 |
+------------+----------+
1 row in set (0.01 sec)
+------------+----------+
| i_staff_id | d_amount |
+------------+----------+
| 2 | 20000 |
+------------+----------+
1 row in set (0.01 sec)
+------+------+
| @x1 | @x2 |
+------+------+
| 0 | 0 |
+------+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure payment_stat;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure payment_stat()
-> begin
-> DECLARE i_staff_id int;
-> DECLARE d_amount int;
-> declare tmp_name varchar(30) default "";
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0 ;
-> set @x2 = 0 ;
->
-> open cur_payment;
-> fetch cur_payment into i_staff_id,d_amount;
-> while(i_staff_id <=3 )
-> do
-> if i_staff_id < 3 then
-> set @x1 = @x1+ i_staff_id;
-> else
-> set @x2 = @x2+ d_amount;
-> end if;
-> fetch cur_payment into i_staff_id,d_amount;
-> end while;
-> close cur_payment;
->
-> select @x1,@x2;
-> end;
-> $$
Query OK, 0 rows affected (0.01 sec)
mysql> call payment_stat();
-> $$
+------+-------+
| @x1 | @x2 |
+------+-------+
| 3 | 30000 |
+------+-------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter $$
mysql> create procedure payment_stat()
-> begin
-> DECLARE i_staff_id int;
-> DECLARE d_amount int;
->
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0 ;
-> set @x2 = 0 ;
->
-> open cur_payment;
-> fetch cur_payment into i_staff_id,d_amount;
-> while(i_staff_id <=3 )
-> do
-> if i_staff_id < 3 then
-> set @x1 = @x1+ i_staff_id + 1;
-> else
-> set @x2 = @x2+ d_amount ;
-> end if;
-> fetch cur_payment into i_staff_id,d_amount;
-> end while;
-> close cur_payment;
->
-> select @x1,@x2;
-> end;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call payment_stat();
+------+-------+
| @x1 | @x2 |
+------+-------+
| 3 | 30000 |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure payment_stat;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure payment_stat()
-> begin
-> DECLARE i_staff_id int;
-> DECLARE d_amount int;
->
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0 ;
-> set @x2 = 0 ;
->
-> open cur_payment;
-> fetch cur_payment into i_staff_id,d_amount;
-> while(i_staff_id <=3 )
-> do
-> if i_staff_id < 3 then
-> set @x1 = @x1+ i_staff_id;
-> else
-> set @x2 = @x2+ d_amount;
-> end if;
-> fetch cur_payment into i_staff_id,d_amount;
-> end while;
-> close cur_payment;
->
-> select @x1,@x2;
-> end;
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call payment_stat();
+------+-------+
| @x1 | @x2 |
+------+-------+
| 3 | 30000 |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure payment_stat()
-> begin
-> DECLARE i_staff_id int;
-> DECLARE d_amount int;
->
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0 ;
-> set @x2 = 0 ;
->
-> open cur_payment;
-> fetch cur_payment into i_staff_id,d_amount;
-> while(i_staff_id <=3 )
-> do
-> if i_staff_id < 3 then
-> set @x1 = @x1+ i_staff_id + 1;
-> else
-> set @x2 = @x2+ d_amount;
-> end if;
-> fetch cur_payment into i_staff_id,d_amount;
-> end while;
-> close cur_payment;
->
-> select @x1,@x2;
-> end;
-> $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call payment_stat();
+------+-------+
| @x1 | @x2 |
+------+-------+
| 5 | 30000 |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> drop procedure payment_stat;
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter $$
mysql> create procedure payment_stat()
-> begin
-> DECLARE i_staff_id int;
-> DECLARE d_amount int;
->
-> DECLARE cur_payment cursor for select staff_id,amount from payment;
-> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
->
-> set @x1 = 0 ;
-> set @x2 = 0 ;
->
-> open cur_payment;
-> fetch cur_payment into i_staff_id,d_amount;
-> while(i_staff_id <=3 )
-> do
-> if i_staff_id < 3 then
-> set @x1 = @x1+ i_staff_id + 6;
-> else
-> set @x2 = @x2+ d_amount + 5;
-> end if;
-> fetch cur_payment into i_staff_id,d_amount;
-> end while;
-> close cur_payment;
->
-> select @x1,@x2;
-> end;
-> $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> call payment_stat();
+------+-------+
| @x1 | @x2 |
+------+-------+
| 15 | 30005 |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE addNum()
-> BEGIN
-> DECLARE x INT;
-> SET x = 0;
-> for_loop : LOOP
-> SET x = x + 1;
-> IF x > 30 THEN
-> LEAVE for_loop;
-> END IF;
-> IF mod(x,2) = 0 then
-> select "num:",x;
-> ITERATE for_loop;
-> END IF;
-> END LOOP;
-> select "count:",x;
-> END $$
Query OK, 0 rows affected (0.01 sec)
mysql> call addNum();
-> $$
+------+------+
| num: | x |
+------+------+
| num: | 2 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 4 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 6 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 8 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 10 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 12 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 14 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 16 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 18 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 20 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 22 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 24 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 26 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 28 |
+------+------+
1 row in set (0.00 sec)
+------+------+
| num: | x |
+------+------+
| num: | 30 |
+------+------+
1 row in set (0.00 sec)
+--------+------+
| count: | x |
+--------+------+
| count: | 31 |
+--------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure repeatPractise()
-> begin
-> set @v = 0 ;
-> REPEAT
-> set @v = @v+ 1;
-> UNTIL @v >=5
-> END REPEAT;
-> END
-> $$
Query OK, 0 rows affected (0.01 sec)
mysql> call repeatPractise();
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> select @v;
-> $$
+------+
| @v |
+------+
| 5 |
+------+
1 row in set (0.00 sec)Nach dem Login kopieren
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Heiße KI -Werkzeuge
Undresser.AI Undress
KI-gestützte App zum Erstellen realistischer Aktfotos
AI Clothes Remover
Online-KI-Tool zum Entfernen von Kleidung aus Fotos.
Undress AI Tool
Ausziehbilder kostenlos
Clothoff.io
KI-Kleiderentferner
AI Hentai Generator
Erstellen Sie kostenlos Ai Hentai.
Heißer Artikel
R.E.P.O. Energiekristalle erklärten und was sie tun (gelber Kristall)
3 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Beste grafische Einstellungen
3 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
Assassin's Creed Shadows: Seashell Riddle -Lösung
1 Wochen vor
By DDD
R.E.P.O. So reparieren Sie Audio, wenn Sie niemanden hören können
3 Wochen vor
By 尊渡假赌尊渡假赌尊渡假赌
Wo kann man die Kransteuerungsschlüsselkarten in Atomfall finden
1 Wochen vor
By DDD
Heiße Werkzeuge
Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor
SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen
Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung
Dreamweaver CS6
Visuelle Webentwicklungstools
SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)
Heiße Themen
CakePHP-Tutorial
1359
52
1359
52
Wie verändern Sie eine Tabelle in MySQL mit der Änderungstabelleanweisung?
Mar 19, 2025 pm 03:51 PM
In dem Artikel werden mithilfe der Änderungstabelle von MySQL Tabellen, einschließlich Hinzufügen/Löschen von Spalten, Umbenennung von Tabellen/Spalten und Ändern der Spaltendatentypen, erläutert.
Wie konfiguriere ich die SSL/TLS -Verschlüsselung für MySQL -Verbindungen?
Mar 18, 2025 pm 12:01 PM
In Artikel werden die Konfiguration der SSL/TLS -Verschlüsselung für MySQL, einschließlich der Erzeugung und Überprüfung von Zertifikaten, erläutert. Das Hauptproblem ist die Verwendung der Sicherheitsauswirkungen von selbstsignierten Zertifikaten. [Charakterzahl: 159]
Wie behandeln Sie große Datensätze in MySQL?
Mar 21, 2025 pm 12:15 PM
In Artikel werden Strategien zum Umgang mit großen Datensätzen in MySQL erörtert, einschließlich Partitionierung, Sharding, Indexierung und Abfrageoptimierung.
Was sind einige beliebte MySQL -GUI -Tools (z. B. MySQL Workbench, PhpMyAdmin)?
Mar 21, 2025 pm 06:28 PM
In Artikel werden beliebte MySQL -GUI -Tools wie MySQL Workbench und PhpMyAdmin beschrieben, die ihre Funktionen und ihre Eignung für Anfänger und fortgeschrittene Benutzer vergleichen. [159 Charaktere]
Wie lassen Sie eine Tabelle in MySQL mit der Drop -Tabelle -Anweisung fallen?
Mar 19, 2025 pm 03:52 PM
In dem Artikel werden in MySQL die Ablagerung von Tabellen mithilfe der Drop -Tabellenerklärung erörtert, wobei Vorsichtsmaßnahmen und Risiken betont werden. Es wird hervorgehoben, dass die Aktion ohne Backups, die Detaillierung von Wiederherstellungsmethoden und potenzielle Produktionsumfeldgefahren irreversibel ist.
Wie erstellen Sie Indizes für JSON -Spalten?
Mar 21, 2025 pm 12:13 PM
In dem Artikel werden in verschiedenen Datenbanken wie PostgreSQL, MySQL und MongoDB Indizes für JSON -Spalten in verschiedenen Datenbanken erstellt, um die Abfrageleistung zu verbessern. Es erläutert die Syntax und die Vorteile der Indizierung spezifischer JSON -Pfade und listet unterstützte Datenbanksysteme auf.
Wie repräsentieren Sie Beziehungen mit fremden Schlüsseln?
Mar 19, 2025 pm 03:48 PM
In Artikeln werden ausländische Schlüssel zur Darstellung von Beziehungen in Datenbanken erörtert, die sich auf Best Practices, Datenintegrität und gemeinsame Fallstricke konzentrieren.
Wie sichere ich mich MySQL gegen gemeinsame Schwachstellen (SQL-Injektion, Brute-Force-Angriffe)?
Mar 18, 2025 pm 12:00 PM
Artikel erläutert die Sicherung von MySQL gegen SQL-Injektions- und Brute-Force-Angriffe unter Verwendung vorbereiteter Aussagen, Eingabevalidierung und starken Kennwortrichtlinien (159 Zeichen).
