multiple in parameters mysql stored procedure
mysql> use classicmodels;
mysql> select * from customers limit 2 \G;
DELIMITER //
CREATE PROCEDURE ByCountrycl( IN countryName VARCHAR(255) , in climit int)
BEGIN
SELECT * FROM customers WHERE country = countryName and creditLimit > climit;
END //
DELIMITER ;
call ByCountrycl(
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
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 classicmodels;
Database changed
mysql> select * from customers limit 2 \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
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE ByCountrycl( IN countryName VARCHAR(255) , in climit int)
-> BEGIN
-> SELECT * FROM customers WHERE country = countryName and creditLimit > climit;
-> END //
Query OK, 0 rows affected (0.28 sec)
mysql>
mysql> DELIMITER ;
mysql> call bycountrycl("usa",100000);
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
| 124 | Mini Gifts Distributors Ltd. | Nelson | Susan | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 1165 | 210500.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 |
| 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 |
| 321 | Corporate Gift Ideas Co. | Brown | Julie | 6505551386 | 7734 Strong St. | NULL | San Francisco | CA | 94217 | USA | 1165 | 105000.00 |
| 363 | Online Diecast Creations Co. | Young | Dorothy | 6035558647 | 2304 Long Airport Avenue | NULL | Nashua | NH | 62005 | USA | 1216 | 114200.00 |
+----------------+------------------------------+-----------------+------------------+------------+---------------------------+--------------+---------------+-------+------------+---------+------------------------+-------------+
9 rows in set (0.04 sec)
Query OK, 0 rows affected (0.19 sec)
mysql> call bycountrycl("usa",200000);
+----------------+------------------------------+-----------------+------------------+------------+-----------------+--------------+------------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------------+-----------------+------------------+------------+-----------------+--------------+------------+-------+------------+---------+------------------------+-------------+
| 124 | Mini Gifts Distributors Ltd. | Nelson | Susan | 4155551450 | 5677 Strong St. | NULL | San Rafael | CA | 97562 | USA | 1165 | 210500.00 |
+----------------+------------------------------+-----------------+------------------+------------+-----------------+--------------+------------+-------+------------+---------+------------------------+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> call bycountrycl(200000,"usa");
ERROR 1366 (HY000): Incorrect integer value: 'usa' for column 'climit' at row 1
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.