Translate

Thursday, 14 October 2021

MySQL Variables and User Defined Variable sql videos in telugu 90

 https://youtu.be/60Mb4oUjMuI

-------------------------------------------------------------------

MySQL Variables;
The main purpose of the variable is to store data in memory and can be used throughout the program.

MySQL can use variables in three different ways, which are given below:

User-Defined Variable
Local Variable
System Variable


MySQL Variables- User-Defined Variable
Sometimes, we want to pass values from one statement to another statement. The user-defined variable enables us to store a value in one statement and later can refer it to another statement.

By using the SET statement

SET @var_name = value;  
NOTE: We can use either '=' or ':=' assignment operator with the SET statement.
2. By using the SELECT statement

SELECT @var_name := value;


SELECT @maxage:= MAX(age) FROM customers;  


---------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
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> set @age=88;
Query OK, 0 rows affected (0.00 sec)

mysql> select @age;
+------+
| @age |
+------+
|   88 |
+------+
1 row in set (0.00 sec)

mysql> set @age1 := 66;
Query OK, 0 rows affected (0.00 sec)

mysql> select @age1;
+-------+
| @age1 |
+-------+
|    66 |
+-------+
1 row in set (0.00 sec)

mysql> select @age2=55;
+----------+
| @age2=55 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> select @age2;
+--------------+
| @age2        |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

mysql> select @sal := 30000;
+---------------+
| @sal := 30000 |
+---------------+
|         30000 |
+---------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @sal;
+-------+
| @sal  |
+-------+
| 30000 |
+-------+
1 row in set (0.00 sec)

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> select max(creditlimit) from customers;
+------------------+
| max(creditlimit) |
+------------------+
|        227600.00 |
+------------------+
1 row in set (0.03 sec)

mysql> select @mc := max(creditlimit) from customers;
+-------------------------+
| @mc := max(creditlimit) |
+-------------------------+
|               227600.00 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @mc;
+-----------+
| @mc       |
+-----------+
| 227600.00 |
+-----------+
1 row in set (0.00 sec)

mysql> select * from customers where creditlimit= @mc;
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName           | contactLastName | contactFirstName | phone          | addressLine1       | addressLine2 | city   | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+------------------------+-------------+
|            141 | Euro+ Shopping Channel | Freyre          | Diego            | (91) 555 94 44 | C/ Moralzarzal, 86 | NULL         | Madrid | NULL  | 28034      | Spain   |                   1370 |   227600.00 |
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+------------------------+-------------+
1 row in set (0.00 sec)

mysql> select * from customers where creditlimit= @mc \G;
*************************** 1. row ***************************
        customerNumber: 141
          customerName: Euro+ Shopping Channel
       contactLastName: Freyre
      contactFirstName: Diego
                 phone: (91) 555 94 44
          addressLine1: C/ Moralzarzal, 86
          addressLine2: NULL
                  city: Madrid
                 state: NULL
            postalCode: 28034
               country: Spain
salesRepEmployeeNumber: 1370
           creditLimit: 227600.00
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

--------------------------
Local Variable
It is a type of variable that is not prefixed by @ symbol. The local variable is a strongly typed variable. The scope of the local variable is in a stored program block in which it is declared.


Syntax
We can use the DECLARE statement with the following syntax:

DECLARE variable_name datatype(size) [DEFAULT default_value];  

DECLARE a,b,c INT DEFAULT 0; 



Create Procedure Test()  
    BEGIN  
        DECLARE A INT DEFAULT 100;  
        DECLARE B INT;  
        DECLARE C INT;  
        DECLARE D INT;  
        SET B = 90;  
        SET C = 45;  
        SET D = A + B - C;  
        SELECT A, B, C, D;  
    END //  
DELIMITER ;  

------------- 
System variables are a special class to all program units, which contains predefined variables.

 SHOW VARIABLES;  

SHOW VARIABLES LIKE '%version%';  

SELECT @@key_buffer_size;



https://www.javatpoint.com/mysql-variables
https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/ch04s02.html

Inout parameter in stored procedure sql videos in telugu 89

 https://youtu.be/za5iKeK0OcU

--------------------------------------------------------
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.

DELIMITER $$
CREATE PROCEDURE totalrec( )

BEGIN
select count(*) from customers;
END$$

DELIMITER ;


-----------------
drop procedure totalrec;

DELIMITER $$
CREATE PROCEDURE totalrec( in total int )

BEGIN
        select total;
select count(*) into total from customers;
END$$

DELIMITER ;

set @value=500;
call totalrec( @value );
select @value;
---------------------
drop procedure totalrec;

DELIMITER $$
CREATE PROCEDURE totalrec( out total int )

BEGIN
        select total;
select count(*) into total from customers;
END$$

DELIMITER ;

set @value=500;
call totalrec( @value );
select @value;
------------------------
drop procedure totalrec;

DELIMITER $$
CREATE PROCEDURE totalrec( inout total int )

BEGIN
        select total;
select count(*) into total from customers;
END$$

DELIMITER ;

set @value=500;
call totalrec( @value );
select @value;


---------------------

DELIMITER $$

CREATE PROCEDURE SetCounter(INOUT counter INT, IN inc INT )
BEGIN
SET counter = counter + inc;
END$$

DELIMITER ;
----------------
SET @counter = 1;
CALL SetCounter(@counter,1); -- 2
CALL SetCounter(@counter,1); -- 3
CALL SetCounter(@counter,5); -- 8
SELECT @counter; -- 8

------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
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 count(*) from customers;
+----------+
| count(*) |
+----------+
|      122 |
+----------+
1 row in set (0.00 sec)

mysql> drop procedure totalrec;
Query OK, 0 rows affected (0.10 sec)

mysql>
mysql> DELIMITER $$
mysql> CREATE PROCEDURE totalrec( )
    ->
    -> BEGIN
    -> select count(*) from customers;
    -> END$$
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> DELIMITER ;
mysql> call totalrec();
+----------+
| count(*) |
+----------+
|      122 |
+----------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> drop procedure totalrec;
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE totalrec( in total int )
    ->
    -> BEGIN
    ->         select total;
    -> select count(*) into total from customers;
    -> END$$
Query OK, 0 rows affected (0.14 sec)

mysql>
mysql> DELIMITER ;
mysql> set @value=600;
Query OK, 0 rows affected (0.00 sec)

mysql> call totalrec(@value);
+-------+
| total |
+-------+
|   600 |
+-------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select @value;
+--------+
| @value |
+--------+
|    600 |
+--------+
1 row in set (0.00 sec)

mysql> drop procedure totalrec;
Query OK, 0 rows affected (0.14 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE totalrec( out total int )
    ->
    -> BEGIN
    ->         select total;
    -> select count(*) into total from customers;
    -> END$$
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> DELIMITER ;
mysql> set @value=800;
Query OK, 0 rows affected (0.00 sec)

mysql> call totalrec(@value)
    -> ;
+-------+
| total |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select @value;
+--------+
| @value |
+--------+
|    122 |
+--------+
1 row in set (0.00 sec)

mysql> drop procedure totalrec;
Query OK, 0 rows affected (0.12 sec)

mysql> DELIMITER $$
mysql> CREATE PROCEDURE totalrec( inout total int )
    ->
    -> BEGIN
    ->         select total;
    -> select count(*) into total from customers;
    -> END$$
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> DELIMITER ;
mysql> set @value=999;
Query OK, 0 rows affected (0.00 sec)

mysql> call totalrec(@value);
+-------+
| total |
+-------+
|   999 |
+-------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select @value;
+--------+
| @value |
+--------+
|    122 |
+--------+
1 row in set (0.00 sec)

mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE SetCounter(INOUT counter INT, IN inc INT )
    -> BEGIN
    -> SET counter = counter + inc;
    -> END$$
Query OK, 0 rows affected (0.18 sec)

mysql>
mysql> DELIMITER ;
mysql> set @counter=1
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> setcounter(@counter,0);
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 'setcounter(@counter,0)' at line 1
mysql> call setcounter(@counter,0);
Query OK, 0 rows affected (0.00 sec)

mysql> call setcounter(@counter,1);
Query OK, 0 rows affected (0.00 sec)

mysql> select @counter;
+----------+
| @counter |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> call setcounter(@counter,2);
Query OK, 0 rows affected (0.00 sec)

mysql> select @counter;
+----------+
| @counter |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql>

Out parameter in stored procedure sql videos in telugu 88

 https://youtu.be/pSUXFDfQDbw

-----------------------------------------
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.



delimiter $$
create procedure total_Credit_limit(in cname VARCHAR(255),out totalcr int)
begin
  select sum(creditLimit) into totalcr from customers where country=cname;
end $$
delimiter ;




call total_Credit_limit("USA",@total);
select @total;
-------------------------------


delimiter $$
create procedure total_Credit_limit3(out totalcr int)
begin
  select @total1;
  select sum(creditLimit) into totalcr from customers;
end $$
delimiter ;



call total_Credit_limit2(@total1);

select @total1;


select sum(creditLimit) from customers;
-------------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
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>
mysql> delimiter $$
mysql> create procedure total_Credit_limit2(out totalcr int)
    -> begin
    ->   select sum(creditLimit) into totalcr from customers;
    -> end $$
Query OK, 0 rows affected (0.40 sec)

mysql> delimiter ;
mysql> call total_Credit_limit2(@summ);
Query OK, 1 row affected (0.03 sec)

mysql> select @summ;
+---------+
| @summ   |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)

mysql> set @total=50000;
Query OK, 0 rows affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
|  50000 |
+--------+
1 row in set (0.00 sec)

mysql> call total_Credit_limit2(@total);
Query OK, 1 row affected (0.04 sec)

mysql> select @total;
+---------+
| @total  |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)

mysql> select @total;
+---------+
| @total  |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure total_Credit_limit3(out totalcr int)
    -> begin
    ->   select @total1;
    ->   select sum(creditLimit) into totalcr from customers;
    -> end $$
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter ;
mysql> all total_Credit_limit3(@total1);
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 'all total_Credit_limit3(@total1)' at line 1
mysql> call total_Credit_limit3(@total1);
+------------------+
| @total1          |
+------------------+
| NULL             |
+------------------+
1 row in set (0.03 sec)

Query OK, 1 row affected (0.05 sec)

mysql> select @total1;
+---------+
| @total1 |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)

mysql> call total_Credit_limit3(@total1);
+---------+
| @total1 |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> set @total1=700;
Query OK, 0 rows affected (0.00 sec)

mysql> call total_Credit_limit3(@total1);
+---------+
| @total1 |
+---------+
| 700     |
+---------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select @total1;
+---------+
| @total1 |
+---------+
| 8254400 |
+---------+
1 row in set (0.00 sec)

mysql> drop procedure total_Credit_limit;
Query OK, 0 rows affected (0.21 sec)

mysql> delimiter $$
mysql> create procedure total_Credit_limit(in cname VARCHAR(255),out totalcr int)
    -> begin
    ->   select sum(creditLimit) into totalcr from customers where country=cname;
    -> end $$
Query OK, 0 rows affected (0.19 sec)

mysql> delimiter ;
mysql> call total_Credit_limit("usa",@tc);
Query OK, 1 row affected (0.04 sec)

mysql> select * tc;
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 'tc' at line 1
mysql> select @tc;
+---------+
| @tc     |
+---------+
| 2811700 |
+---------+
1 row in set (0.00 sec)

mysql>

SELECT INTO variable to store query result in variables sql videos in telugu 87

 https://youtu.be/eMIzYq20jPw

----------------------------------------------
SELECT INTO variable to store query result in variables.


SELECT 
    c1, c2, c3, ...
INTO 
    @v1, @v2, @v3,...
FROM 
    table_name
WHERE 
    condition;
-----------------
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
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
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName       | contactLastName | contactFirstName | phone      | addressLine1    | addressLine2 | city      | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
|            103 | Atelier graphique  | Schmitt         | Carine           | 40.32.2555 | 54, rue Royale  | NULL         | Nantes    | NULL  | 44000      | France  |                   1370 |    21000.00 |
|            112 | Signal Gift Stores | King            | Jean             | 7025551838 | 8489 Strong St. | NULL         | Las Vegas | NV    | 83030      | USA     |                   1166 |    71800.00 |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from customers limit 2 \g;
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName       | contactLastName | contactFirstName | phone      | addressLine1    | addressLine2 | city      | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
|            103 | Atelier graphique  | Schmitt         | Carine           | 40.32.2555 | 54, rue Royale  | NULL         | Nantes    | NULL  | 44000      | France  |                   1370 |    21000.00 |
|            112 | Signal Gift Stores | King            | Jean             | 7025551838 | 8489 Strong St. | NULL         | Las Vegas | NV    | 83030      | USA     |                   1166 |    71800.00 |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from customers limit 2 /g;
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 '/g' at line 1
mysql> select * from customers limit 1 /G;
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 '/G' at line 1
mysql> select * from customers limit 1 \g;
+----------------+-------------------+-----------------+------------------+------------+----------------+--------------+--------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName      | contactLastName | contactFirstName | phone      | addressLine1   | addressLine2 | city   | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+-------------------+-----------------+------------------+------------+----------------+--------------+--------+-------+------------+---------+------------------------+-------------+
|            103 | Atelier graphique | Schmitt         | Carine           | 40.32.2555 | 54, rue Royale | NULL         | Nantes | NULL  | 44000      | France  |                   1370 |    21000.00 |
+----------------+-------------------+-----------------+------------------+------------+----------------+--------------+--------+-------+------------+---------+------------------------+-------------+
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from customers limit 1 \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
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select phone from customers limit 10;
+-------------------+
| phone             |
+-------------------+
| 40.32.2555        |
| 7025551838        |
| 03 9520 4555      |
| 40.67.8555        |
| 07-98 9555        |
| 4155551450        |
| (26) 642-7555     |
| +49 69 66 90 2555 |
| 6505555787        |
| 2125557818        |
+-------------------+
10 rows in set (0.00 sec)

mysql> select customername,phone from customers limit 10;
+------------------------------+-------------------+
| customername                 | phone             |
+------------------------------+-------------------+
| Atelier graphique            | 40.32.2555        |
| Signal Gift Stores           | 7025551838        |
| Australian Collectors, Co.   | 03 9520 4555      |
| La Rochelle Gifts            | 40.67.8555        |
| Baane Mini Imports           | 07-98 9555        |
| Mini Gifts Distributors Ltd. | 4155551450        |
| Havel & Zbyszek Co           | (26) 642-7555     |
| Blauer See Auto, Co.         | +49 69 66 90 2555 |
| Mini Wheels Co.              | 6505555787        |
| Land of Toys Inc.            | 2125557818        |
+------------------------------+-------------------+
10 rows in set (0.00 sec)

mysql> select customername,phone into @cname,@cohone from customers where SELECT ;
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' at line 1
mysql>     c1, c2, c3, ...
    -> INTO
    ->     @v1, @v2, @v3,...
    -> FROM
    ->     table_name
    -> WHERE
    ->     condition;
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 'c1, c2, c3, ...
INTO
    @v1, @v2, @v3,...
FROM
    table_name
WHERE
    cond' at line 1
mysql> select customername,phone into @cname,@cohone from customers where customernumber=103;
Query OK, 1 row affected (0.04 sec)

mysql> select @name;
+--------------+
| @name        |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

mysql> select @cname;
+-------------------+
| @cname            |
+-------------------+
| Atelier graphique |
+-------------------+
1 row in set (0.00 sec)

mysql> select @cname,@cohone;
+-------------------+------------+
| @cname            | @cohone    |
+-------------------+------------+
| Atelier graphique | 40.32.2555 |
+-------------------+------------+
1 row in set (0.00 sec)

mysql> select customername,phone into @cname,@cohone from customers;
ERROR 1172 (42000): Result consisted of more than one row
mysql>

Multiple input Parameters in stored procedure sql videos in telugu 86

 https://youtu.be/fd8C19CV_O8

-----------------------------------------
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>





How to passing in Parameter in stored procedure sql videos in telugu 85

https://youtu.be/9bYx-5FLzos 

--------------------------------------------------------
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>