MySQL Stored Procedure Parameters
IN parameters
IN is the default mode. When you define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure.
In addition, the value of an IN parameter is protected. It means that even you change the value of the IN parameter inside the stored procedure, its original value is unchanged after the stored procedure ends. In other words, the stored procedure only works on the copy of the IN parameter.
OUT parameters
The value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program.
Notice that the stored procedure cannot access the initial value of the OUT parameter when it starts.
INOUT parameters
An INOUT parameter is a combination of IN and OUT parameters. It means that the calling program may pass the argument, and the stored procedure can modify the INOUT parameter, and pass the new value back to the calling program.
https://www.mysqltutorial.org/stored-procedures-parameters.aspx
--------------------
DELIMITER //
CREATE PROCEDURE ByCountry(IN countryName VARCHAR(255))
BEGIN
SELECT * FROM customers WHERE country = countryName;
END //
DELIMITER ;
call ByCountry("india");
-----------
DELIMITER //
CREATE PROCEDURE bylimith(IN cl int(8))
BEGIN
SELECT phone,state,country FROM customers WHERE creditLimit > cl;
END //
DELIMITER ;
call bylimit(10000)
----------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodel;
ERROR 1049 (42000): Unknown database 'classicmodel'
mysql> use classicmodels;
Database changed
mysql> select * from customers limit 3 \G;
*************************** 1. row ***************************
customerNumber: 103
customerName: Atelier graphique
contactLastName: Schmitt
contactFirstName: Carine
phone: 40.32.2555
addressLine1: 54, rue Royale
addressLine2: NULL
city: Nantes
state: NULL
postalCode: 44000
country: France
salesRepEmployeeNumber: 1370
creditLimit: 21000.00
*************************** 2. row ***************************
customerNumber: 112
customerName: Signal Gift Stores
contactLastName: King
contactFirstName: Jean
phone: 7025551838
addressLine1: 8489 Strong St.
addressLine2: NULL
city: Las Vegas
state: NV
postalCode: 83030
country: USA
salesRepEmployeeNumber: 1166
creditLimit: 71800.00
*************************** 3. row ***************************
customerNumber: 114
customerName: Australian Collectors, Co.
contactLastName: Ferguson
contactFirstName: Peter
phone: 03 9520 4555
addressLine1: 636 St Kilda Road
addressLine2: Level 3
city: Melbourne
state: Victoria
postalCode: 3004
country: Australia
salesRepEmployeeNumber: 1611
creditLimit: 117300.00
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql> BEGIN
-> SELECT * FROM customers WHERE country = countryName;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM customers WHERE country = countryName' at line 2
mysql> END //
->
-> DELIMITER ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END //
DELIMITER' at line 1
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE ByCountry(IN countryName VARCHAR(255))
-> BEGIN
-> SELECT * FROM customers WHERE country = countryName;
-> END //
Query OK, 0 rows affected (0.41 sec)
mysql>
mysql> DELIMITER ;
mysql> call bycountry("india");
Empty set (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> call bycountry("Usa");
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
| 124 | Mini Gifts Distributors Ltd. | Nelson | Susan | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 1165 | 210500.00 |
| 129 | Mini Wheels Co. | Murphy | Julie | 6505555787 | 5557 North Pendale Street | NULL | San Francisco | CA | 94217 | USA | 1165 | 64600.00 |
| 131 | Land of Toys Inc. | Lee | Kwai | 2125557818 | 897 Long Airport Avenue | NULL | NYC | NY | 10022 | USA | 1323 | 114900.00 |
| 151 | Muscle Machine Inc | Young | Jeff | 2125557413 | 4092 Furth Circle | Suite 400 | NYC | NY | 10022 | USA | 1286 | 138500.00 |
| 157 | Diecast Classics Inc. | Leong | Kelvin | 2155551555 | 7586 Pompton St. | NULL | Allentown | PA | 70267 | USA | 1216 | 100600.00 |
| 161 | Technics Stores Inc. | Hashimoto | Juri | 6505556809 | 9408 Furth Circle | NULL | Burlingame | CA | 94217 | USA | 1165 | 84600.00 |
| 168 | American Souvenirs Inc | Franco | Keith | 2035557845 | 149 Spinnaker Dr. | Suite 101 | New Haven | CT | 97823 | USA | 1286 | 0.00 |
| 173 | Cambridge Collectables Co. | Tseng | Jerry | 6175555555 | 4658 Baden Av. | NULL | Cambridge | MA | 51247 | USA | 1188 | 43400.00 |
| 175 | Gift Depot Inc. | King | Julie | 2035552570 | 25593 South Bay Ln. | NULL | Bridgewater | CT | 97562 | USA | 1323 | 84300.00 |
| 181 | Vitachrome Inc. | Frick | Michael | 2125551500 | 2678 Kingston Rd. | Suite 101 | NYC | NY | 10022 | USA | 1286 | 76400.00 |
| 198 | Auto-Moto Classics Inc. | Taylor | Leslie | 6175558428 | 16780 Pompton St. | NULL | Brickhaven | MA | 58339 | USA | 1216 | 23000.00 |
| 204 | Online Mini Collectables | Barajas | Miguel | 6175557555 | 7635 Spinnaker Dr. | NULL | Brickhaven | MA | 58339 | USA | 1188 | 68700.00 |
| 205 | Toys4GrownUps.com | Young | Julie | 6265557265 | 78934 Hillside Dr. | NULL | Pasadena | CA | 90003 | USA | 1166 | 90700.00 |
| 219 | Boards & Toys Co. | Young | Mary | 3105552373 | 4097 Douglas Av. | NULL | Glendale | CA | 92561 | USA | 1166 | 11000.00 |
| 239 | Collectable Mini Designs Co. | Thompson | Valarie | 7605558146 | 361 Furth Circle | NULL | San Diego | CA | 91217 | USA | 1166 | 105000.00 |
| 286 | Marta's Replicas Co. | Hernandez | Marta | 6175558555 | 39323 Spinnaker Dr. | NULL | Cambridge | MA | 51247 | USA | 1216 | 123700.00 |
| 319 | Mini Classics | Frick | Steve | 9145554562 | 3758 North Pendale Street | NULL | White Plains | NY | 24067 | USA | 1323 | 102700.00 |
| 320 | Mini Creations Ltd. | Huang | Wing | 5085559555 | 4575 Hillside Dr. | NULL | New Bedford | MA | 50553 | USA | 1188 | 94500.00 |
| 321 | Corporate Gift Ideas Co. | Brown | Julie | 6505551386 | 7734 Strong St. | NULL | San Francisco | CA | 94217 | USA | 1165 | 105000.00 |
| 328 | Tekni Collectables Inc. | Brown | William | 2015559350 | 7476 Moss Rd. | NULL | Newark | NJ | 94019 | USA | 1323 | 43000.00 |
| 339 | Classic Gift Ideas, Inc | Cervantes | Francisca | 2155554695 | 782 First Street | NULL | Philadelphia | PA | 71270 | USA | 1188 | 81100.00 |
| 347 | Men 'R' US Retailers, Ltd. | Chandler | Brian | 2155554369 | 6047 Douglas Av. | NULL | Los Angeles | CA | 91003 | USA | 1166 | 57700.00 |
| 362 | Gifts4AllAges.com | Yoshido | Juri | 6175559555 | 8616 Spinnaker Dr. | NULL | Boston | MA | 51003 | USA | 1216 | 41900.00 |
| 363 | Online Diecast Creations Co. | Young | Dorothy | 6035558647 | 2304 Long Airport Avenue | NULL | Nashua | NH | 62005 | USA | 1216 | 114200.00 |
| 379 | Collectables For Less Inc. | Nelson | Allen | 6175558555 | 7825 Douglas Av. | NULL | Brickhaven | MA | 58339 | USA | 1188 | 70700.00 |
| 424 | Classic Legends Inc. | Hernandez | Maria | 2125558493 | 5905 Pompton St. | Suite 750 | NYC | NY | 10022 | USA | 1286 | 67500.00 |
| 447 | Gift Ideas Corp. | Lewis | Dan | 2035554407 | 2440 Pompton St. | NULL | Glendale | CT | 97561 | USA | 1323 | 49700.00 |
| 450 | The Sharp Gifts Warehouse | Frick | Sue | 4085553659 | 3086 Ingle Ln. | NULL | San Jose | CA | 94217 | USA | 1165 | 77600.00 |
| 455 | Super Scale Inc. | Murphy | Leslie | 2035559545 | 567 North Pendale Street | NULL | New Haven | CT | 97823 | USA | 1286 | 95400.00 |
| 456 | Microscale Inc. | Choi | Yu | 2125551957 | 5290 North Pendale Street | Suite 200 | NYC | NY | 10022 | USA | 1286 | 39800.00 |
| 462 | FunGiftIdeas.com | Benitez | Violeta | 5085552555 | 1785 First Street | NULL | New Bedford | MA | 50553 | USA | 1216 | 85800.00 |
| 475 | West Coast Collectables Co. | Thompson | Steve | 3105553722 | 3675 Furth Circle | NULL | Burbank | CA | 94019 | USA | 1166 | 55400.00 |
| 486 | Motor Mint Distributors Inc. | Salazar | Rosa | 2155559857 | 11328 Douglas Av. | NULL | Philadelphia | PA | 71270 | USA | 1323 | 72600.00 |
| 487 | Signal Collectibles Ltd. | Taylor | Sue | 4155554312 | 2793 Furth Circle | NULL | Brisbane | CA | 94217 | USA | 1165 | 60300.00 |
| 495 | Diecast Collectables | Franco | Valarie | 6175552555 | 6251 Ingle Ln. | NULL | Boston | MA | 51003 | USA | 1188 | 85100.00 |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
36 rows in set (0.00 sec)
Query OK, 0 rows affected (0.54 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE bylimit(IN cl int(8))
-> BEGIN
-> SELECT phone,state,country FROM customers WHERE creditLimit < cl;
-> END //
Query OK, 0 rows affected, 1 warning (0.19 sec)
mysql>
mysql> DELIMITER ;
mysql> call bylimit(50000);
+------------------+----------+--------------+
| phone | state | country |
+------------------+----------+--------------+
| 40.32.2555 | NULL | France |
| (26) 642-7555 | NULL | Poland |
| 2035557845 | CT | USA |
| (1) 356-5555 | NULL | Portugal |
| 6175555555 | MA | USA |
| 6175558428 | MA | USA |
| +612 9411 1555 | NULL | Singapore |
| 3105552373 | CA | USA |
| 0372-555188 | NULL | Germany |
| (514) 555-8054 | Québec | Canada |
| (91) 745 6555 | NULL | Spain |
| 069-0555984 | NULL | Germany |
| 089-0877555 | NULL | Germany |
| +41 26 425 50 01 | NULL | Switzerland |
| +31 20 491 9555 | NULL | Netherlands |
| 030-0074555 | NULL | Germany |
| 2015559350 | NJ | USA |
| 0555-09555 | NULL | Germany |
| 2967 555 | Co. Cork | Ireland |
| +27 21 550 3555 | Pretoria | South Africa |
| 0251-555259 | NULL | Germany |
| 6175559555 | MA | USA |
| (1) 354-2555 | NULL | Portugal |
| 0452-076555 | NULL | Switzerland |
| (071) 23 67 2555 | NULL | Belgium |
| 0711-555361 | NULL | Germany |
| 0342-555176 | NULL | Germany |
| 2035554407 | CT | USA |
| 7675-3555 | NULL | Austria |
| 2125551957 | NY | USA |
| 0241-039123 | NULL | Germany |
| +34 913 728555 | NULL | Spain |
| +39 022515555 | NULL | Italy |
| 0621-08555 | NULL | Germany |
| +7 812 293 0521 | NULL | Russia |
| + 972 9 959 8555 | NULL | Israel |
| (171) 555-7555 | NULL | UK |
+------------------+----------+--------------+
37 rows in set (0.03 sec)
Query OK, 0 rows affected (0.15 sec)
mysql> call bylimit(10000);
+------------------+----------+--------------+
| phone | state | country |
+------------------+----------+--------------+
| (26) 642-7555 | NULL | Poland |
| 2035557845 | CT | USA |
| (1) 356-5555 | NULL | Portugal |
| +612 9411 1555 | NULL | Singapore |
| 0372-555188 | NULL | Germany |
| (91) 745 6555 | NULL | Spain |
| 069-0555984 | NULL | Germany |
| 089-0877555 | NULL | Germany |
| +41 26 425 50 01 | NULL | Switzerland |
| +31 20 491 9555 | NULL | Netherlands |
| 030-0074555 | NULL | Germany |
| 0555-09555 | NULL | Germany |
| 2967 555 | Co. Cork | Ireland |
| +27 21 550 3555 | Pretoria | South Africa |
| 0251-555259 | NULL | Germany |
| (1) 354-2555 | NULL | Portugal |
| 0452-076555 | NULL | Switzerland |
| 0711-555361 | NULL | Germany |
| 0342-555176 | NULL | Germany |
| 0241-039123 | NULL | Germany |
| +34 913 728555 | NULL | Spain |
| 0621-08555 | NULL | Germany |
| +7 812 293 0521 | NULL | Russia |
| + 972 9 959 8555 | NULL | Israel |
+------------------+----------+--------------+
24 rows in set (0.02 sec)
Query OK, 0 rows affected (0.12 sec)
mysql> call bylimit(1000);
+------------------+----------+--------------+
| phone | state | country |
+------------------+----------+--------------+
| (26) 642-7555 | NULL | Poland |
| 2035557845 | CT | USA |
| (1) 356-5555 | NULL | Portugal |
| +612 9411 1555 | NULL | Singapore |
| 0372-555188 | NULL | Germany |
| (91) 745 6555 | NULL | Spain |
| 069-0555984 | NULL | Germany |
| 089-0877555 | NULL | Germany |
| +41 26 425 50 01 | NULL | Switzerland |
| +31 20 491 9555 | NULL | Netherlands |
| 030-0074555 | NULL | Germany |
| 0555-09555 | NULL | Germany |
| 2967 555 | Co. Cork | Ireland |
| +27 21 550 3555 | Pretoria | South Africa |
| 0251-555259 | NULL | Germany |
| (1) 354-2555 | NULL | Portugal |
| 0452-076555 | NULL | Switzerland |
| 0711-555361 | NULL | Germany |
| 0342-555176 | NULL | Germany |
| 0241-039123 | NULL | Germany |
| +34 913 728555 | NULL | Spain |
| 0621-08555 | NULL | Germany |
| +7 812 293 0521 | NULL | Russia |
| + 972 9 959 8555 | NULL | Israel |
+------------------+----------+--------------+
24 rows in set (0.00 sec)
Query OK, 0 rows affected (0.09 sec)
mysql> call bylimit(100);
+------------------+----------+--------------+
| phone | state | country |
+------------------+----------+--------------+
| (26) 642-7555 | NULL | Poland |
| 2035557845 | CT | USA |
| (1) 356-5555 | NULL | Portugal |
| +612 9411 1555 | NULL | Singapore |
| 0372-555188 | NULL | Germany |
| (91) 745 6555 | NULL | Spain |
| 069-0555984 | NULL | Germany |
| 089-0877555 | NULL | Germany |
| +41 26 425 50 01 | NULL | Switzerland |
| +31 20 491 9555 | NULL | Netherlands |
| 030-0074555 | NULL | Germany |
| 0555-09555 | NULL | Germany |
| 2967 555 | Co. Cork | Ireland |
| +27 21 550 3555 | Pretoria | South Africa |
| 0251-555259 | NULL | Germany |
| (1) 354-2555 | NULL | Portugal |
| 0452-076555 | NULL | Switzerland |
| 0711-555361 | NULL | Germany |
| 0342-555176 | NULL | Germany |
| 0241-039123 | NULL | Germany |
| +34 913 728555 | NULL | Spain |
| 0621-08555 | NULL | Germany |
| +7 812 293 0521 | NULL | Russia |
| + 972 9 959 8555 | NULL | Israel |
+------------------+----------+--------------+
24 rows in set (0.00 sec)
Query OK, 0 rows affected (0.09 sec)
mysql> call bylimit(10);
+------------------+----------+--------------+
| phone | state | country |
+------------------+----------+--------------+
| (26) 642-7555 | NULL | Poland |
| 2035557845 | CT | USA |
| (1) 356-5555 | NULL | Portugal |
| +612 9411 1555 | NULL | Singapore |
| 0372-555188 | NULL | Germany |
| (91) 745 6555 | NULL | Spain |
| 069-0555984 | NULL | Germany |
| 089-0877555 | NULL | Germany |
| +41 26 425 50 01 | NULL | Switzerland |
| +31 20 491 9555 | NULL | Netherlands |
| 030-0074555 | NULL | Germany |
| 0555-09555 | NULL | Germany |
| 2967 555 | Co. Cork | Ireland |
| +27 21 550 3555 | Pretoria | South Africa |
| 0251-555259 | NULL | Germany |
| (1) 354-2555 | NULL | Portugal |
| 0452-076555 | NULL | Switzerland |
| 0711-555361 | NULL | Germany |
| 0342-555176 | NULL | Germany |
| 0241-039123 | NULL | Germany |
| +34 913 728555 | NULL | Spain |
| 0621-08555 | NULL | Germany |
| +7 812 293 0521 | NULL | Russia |
| + 972 9 959 8555 | NULL | Israel |
+------------------+----------+--------------+
24 rows in set (0.00 sec)
Query OK, 0 rows affected (0.10 sec)
mysql> select creditlimit from customers;
+-------------+
| creditlimit |
+-------------+
| 21000.00 |
| 71800.00 |
| 117300.00 |
| 118200.00 |
| 81700.00 |
| 210500.00 |
| 0.00 |
| 59700.00 |
| 64600.00 |
| 114900.00 |
| 227600.00 |
| 53100.00 |
| 83400.00 |
| 123900.00 |
| 103800.00 |
| 138500.00 |
| 100600.00 |
| 84600.00 |
| 97900.00 |
| 96800.00 |
| 0.00 |
| 0.00 |
| 82900.00 |
| 84300.00 |
| 43400.00 |
| 84300.00 |
| 81200.00 |
| 76400.00 |
| 96500.00 |
| 136800.00 |
| 69400.00 |
| 23000.00 |
| 92700.00 |
| 90300.00 |
| 68700.00 |
| 90700.00 |
| 0.00 |
| 53800.00 |
| 58600.00 |
| 60300.00 |
| 11000.00 |
| 0.00 |
| 120800.00 |
| 48700.00 |
| 0.00 |
| 105000.00 |
| 93900.00 |
| 61100.00 |
| 0.00 |
| 113000.00 |
| 68100.00 |
| 77900.00 |
| 120400.00 |
| 89600.00 |
| 0.00 |
| 107800.00 |
| 119600.00 |
| 93300.00 |
| 123700.00 |
| 0.00 |
| 141300.00 |
| 95100.00 |
| 0.00 |
| 0.00 |
| 90500.00 |
| 79900.00 |
| 102700.00 |
| 94500.00 |
| 105000.00 |
| 88000.00 |
| 77000.00 |
| 43000.00 |
| 51600.00 |
| 98800.00 |
| 0.00 |
| 81100.00 |
| 59600.00 |
| 57700.00 |
| 0.00 |
| 65000.00 |
| 81100.00 |
| 0.00 |
| 77700.00 |
| 0.00 |
| 41900.00 |
| 114200.00 |
| 0.00 |
| 0.00 |
| 70700.00 |
| 23500.00 |
| 71700.00 |
| 81500.00 |
| 121400.00 |
| 94400.00 |
| 95000.00 |
| 0.00 |
| 86800.00 |
| 77000.00 |
| 67500.00 |
| 0.00 |
| 49700.00 |
| 116400.00 |
| 77600.00 |
| 45300.00 |
| 95400.00 |
| 39800.00 |
| 104600.00 |
| 0.00 |
| 85800.00 |
| 0.00 |
| 60300.00 |
| 34800.00 |
| 55400.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 65700.00 |
| 72600.00 |
| 60300.00 |
| 43300.00 |
| 85100.00 |
| 110000.00 |
+-------------+
122 rows in set (0.00 sec)
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE bylimith(IN cl int(8))
-> BEGIN
-> SELECT phone,state,country FROM customers WHERE creditLimit > cl;
-> END //
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql>
mysql> DELIMITER ;
mysql> call bilimith(100000);
ERROR 1305 (42000): PROCEDURE classicmodels.bilimith does not exist
mysql> call bylimith(100000);
+----------------+----------+-------------+
| phone | state | country |
+----------------+----------+-------------+
| 03 9520 4555 | Victoria | Australia |
| 40.67.8555 | NULL | France |
| 4155551450 | CA | USA |
| 2125557818 | NY | USA |
| (91) 555 94 44 | NULL | Spain |
| 78.32.5555 | NULL | France |
| +65 221 7555 | NULL | Singapore |
| 2125557413 | NY | USA |
| 2155551555 | PA | USA |
| (171) 555-1555 | NULL | UK |
| 86 21 3555 | NULL | Denmark |
| 7605558146 | CA | USA |
| 011-4988555 | NULL | Italy |
| 0221-5554327 | NULL | Germany |
| 02 9936 8555 | NSW | Australia |
| 035-640555 | NULL | Italy |
| 6175558555 | MA | USA |
| 0897-034555 | NULL | Switzerland |
| 9145554562 | NY | USA |
| 6505551386 | CA | USA |
| 6035558647 | NH | USA |
| 0522-556555 | NULL | Italy |
| 0695-34 6555 | NULL | Sweden |
| (91) 555 22 82 | NULL | Spain |
| +64 9 5555500 | NULL | New Zealand |
+----------------+----------+-------------+
25 rows in set (0.00 sec)
Query OK, 0 rows affected (0.10 sec)
mysql> call bylimith(300000);
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call bylimith(200000);
+----------------+-------+---------+
| phone | state | country |
+----------------+-------+---------+
| 4155551450 | CA | USA |
| (91) 555 94 44 | NULL | Spain |
+----------------+-------+---------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.