в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:06Z

1 версия

Новая страница

==CASE Statement with Condition Checks==



<source lang="sql">

mysql>
mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction(delivery_day INT(1),preferred INT(1))
-> RETURNS INT(2)
-> BEGIN
->
-> DECLARE shipping_cost INT(2) DEFAULT 0;
->
-> CASE
-> WHEN preferred = 1 THEN
-> SET shipping_cost = 2;
-> WHEN delivery_day = 1 THEN
-> SET shipping_cost = 20;
-> WHEN delivery_day = 2 THEN
-> SET shipping_cost = 15;
-> WHEN delivery_day = 3 THEN
-> SET shipping_cost = 10;
-> ELSE
-> SET shipping_cost = 5;
-> END CASE;
-> RETURN shipping_cost;
->
-> END
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(1,1);
+-----------------+
| myFunction(1,1) |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> select myFunction(2,2);
+-----------------+
| myFunction(2,2) |
+-----------------+
| 15 |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> select myFunction(3,3);
+-----------------+
| myFunction(3,3) |
+-----------------+
| 10 |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
</source>

==CASE WHEN with ELSE==



<source lang="sql">

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(id int)
-> BEGIN
-> CASE
-> WHEN id < 2 THEN
-> select "less than 2";
-> WHEN id > 2 and id < 5 THEN
-> select "greater than 2 and less than 5";
-> ELSE
-> select "ELSE";
-> END CASE;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call myProc(1);
+-------------+
| less than 2 |
+-------------+
| less than 2 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> call myProc(3);
+--------------------------------+
| greater than 2 and less than 5 |
+--------------------------------+
| greater than 2 and less than 5 |
+--------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
</source>

==For checking a uniform condition you may use the CASE construct==



<source lang="sql">

mysql>
mysql>
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction (delivery_day INT(1)) RETURNS INT(2)
-> BEGIN
->
-> DECLARE shipping_cost INT(2) DEFAULT 0;
->
-> CASE delivery_day
-> WHEN 1 THEN
-> SET shipping_cost = 20;
-> WHEN 2 THEN
-> SET shipping_cost = 15;
-> WHEN 3 THEN
-> SET shipping_cost = 10;
-> ELSE
-> SET shipping_cost = 5;
-> END CASE;
-> RETURN shipping_cost;
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(1);
+---------------+
| myFunction(1) |
+---------------+
| 20 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> select myFunction(2);
+---------------+
| myFunction(2) |
+---------------+
| 15 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> select myFunction(3);
+---------------+
| myFunction(3) |
+---------------+
| 10 |
+---------------+
1 row in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
</source>

==Using CASE WHEN condition statement in a procedure==



<source lang="sql">

mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc(id int)
-> BEGIN
->
-> CASE
-> WHEN id < 2 THEN
-> select "less than 2";
->
-> WHEN id > 2 and id < 5 THEN
-> select "greater than 2 and less than 5";
->
-> WHEN id > 5 THEN
-> select "greater than 5";
->
-> END CASE;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc(2);
ERROR 1339 (20000): Case not found for CASE statement
mysql> call myProc(20);
+----------------+
| greater than 5 |
+----------------+
| greater than 5 |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
</source>

==Using CASE WHEN statement in a procedure==



<source lang="sql">

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(customer_status VARCHAR(10))
-> BEGIN
->
-> CASE customer_status
-> WHEN "PLATINUM" THEN
-> select "PLATINUM";
->
-> WHEN "GOLD" THEN
-> select "GOLD";
->
-> WHEN "SILVER" THEN
-> select "SILVER";
->
-> WHEN "BRONZE" THEN
-> select "BRONZE";
-> END CASE;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc("BRONZE");
+--------+
| BRONZE |
+--------+
| BRONZE |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call myProc("GOLD");
+------+
| GOLD |
+------+
| GOLD |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Procedure Function/CASE - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия