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>