mysql学习记录（十）--存储过程_MySQL
proses
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)Salin selepas log masuk
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Alat AI Hot
Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik
AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.
Undress AI Tool
Gambar buka pakaian secara percuma
Clothoff.io
Penyingkiran pakaian AI
AI Hentai Generator
Menjana ai hentai secara percuma.
Artikel Panas
R.E.P.O. Kristal tenaga dijelaskan dan apa yang mereka lakukan (kristal kuning)
1 bulan yang lalu
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Tetapan grafik terbaik
1 bulan yang lalu
By 尊渡假赌尊渡假赌尊渡假赌
Assassin's Creed Shadows: Penyelesaian Riddle Seashell
3 minggu yang lalu
By DDD
Apa yang Baru di Windows 11 KB5054979 & Cara Memperbaiki Masalah Kemas Kini
2 minggu yang lalu
By DDD
Akan R.E.P.O. Ada Crossplay?
1 bulan yang lalu
By 尊渡假赌尊渡假赌尊渡假赌
Alat panas
Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma
SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan
Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa
Dreamweaver CS6
Alat pembangunan web visual
SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)
Topik panas
Tutorial CakePHP
1384
52
1384
52
Terangkan keupayaan carian teks penuh InnoDB.
Apr 02, 2025 pm 06:09 PM
Keupayaan carian teks penuh InnoDB sangat kuat, yang dapat meningkatkan kecekapan pertanyaan pangkalan data dan keupayaan untuk memproses sejumlah besar data teks. 1) InnoDB melaksanakan carian teks penuh melalui pengindeksan terbalik, menyokong pertanyaan carian asas dan maju. 2) Gunakan perlawanan dan terhadap kata kunci untuk mencari, menyokong mod boolean dan carian frasa. 3) Kaedah pengoptimuman termasuk menggunakan teknologi segmentasi perkataan, membina semula indeks dan menyesuaikan saiz cache untuk meningkatkan prestasi dan ketepatan.
Bagaimana anda mengubah jadual di MySQL menggunakan pernyataan Alter Table?
Mar 19, 2025 pm 03:51 PM
Artikel ini membincangkan menggunakan pernyataan jadual Alter MySQL untuk mengubah suai jadual, termasuk menambah/menjatuhkan lajur, menamakan semula jadual/lajur, dan menukar jenis data lajur.
Bilakah imbasan jadual penuh lebih cepat daripada menggunakan indeks di MySQL?
Apr 09, 2025 am 12:05 AM
Pengimbasan jadual penuh mungkin lebih cepat dalam MySQL daripada menggunakan indeks. Kes -kes tertentu termasuk: 1) jumlah data adalah kecil; 2) apabila pertanyaan mengembalikan sejumlah besar data; 3) Apabila lajur indeks tidak selektif; 4) Apabila pertanyaan kompleks. Dengan menganalisis rancangan pertanyaan, mengoptimumkan indeks, mengelakkan lebih banyak indeks dan tetap mengekalkan jadual, anda boleh membuat pilihan terbaik dalam aplikasi praktikal.
Bolehkah saya memasang mysql pada windows 7
Apr 08, 2025 pm 03:21 PM
Ya, MySQL boleh dipasang pada Windows 7, dan walaupun Microsoft telah berhenti menyokong Windows 7, MySQL masih serasi dengannya. Walau bagaimanapun, perkara berikut harus diperhatikan semasa proses pemasangan: Muat turun pemasang MySQL untuk Windows. Pilih versi MySQL yang sesuai (komuniti atau perusahaan). Pilih direktori pemasangan yang sesuai dan set aksara semasa proses pemasangan. Tetapkan kata laluan pengguna root dan simpan dengan betul. Sambung ke pangkalan data untuk ujian. Perhatikan isu keserasian dan keselamatan pada Windows 7, dan disyorkan untuk menaik taraf ke sistem operasi yang disokong.
Bagaimana saya mengkonfigurasi penyulitan SSL/TLS untuk sambungan MySQL?
Mar 18, 2025 pm 12:01 PM
Artikel membincangkan mengkonfigurasi penyulitan SSL/TLS untuk MySQL, termasuk penjanaan sijil dan pengesahan. Isu utama menggunakan implikasi keselamatan sijil yang ditandatangani sendiri. [Kira-kira aksara: 159]
Perbezaan antara indeks kluster dan indeks bukan clustered (indeks sekunder) di InnoDB.
Apr 02, 2025 pm 06:25 PM
Perbezaan antara indeks clustered dan indeks bukan cluster adalah: 1. Klustered Index menyimpan baris data dalam struktur indeks, yang sesuai untuk pertanyaan oleh kunci dan julat utama. 2. Indeks Indeks yang tidak berkumpul indeks nilai utama dan penunjuk kepada baris data, dan sesuai untuk pertanyaan lajur utama bukan utama.
Apakah beberapa alat GUI MySQL yang popular (mis., MySQL Workbench, phpmyadmin)?
Mar 21, 2025 pm 06:28 PM
Artikel membincangkan alat MySQL GUI yang popular seperti MySQL Workbench dan PHPMyAdmin, membandingkan ciri dan kesesuaian mereka untuk pemula dan pengguna maju. [159 aksara]
Bagaimana anda mengendalikan dataset besar di MySQL?
Mar 21, 2025 pm 12:15 PM
Artikel membincangkan strategi untuk mengendalikan dataset besar di MySQL, termasuk pembahagian, sharding, pengindeksan, dan pengoptimuman pertanyaan.
