SQL/MySQL/Procedure Function/Function
Версия от 13:46, 26 мая 2010; (обсуждение)
Содержание
- 1 A function that takes a parameter, performs an operation using an SQL function, and returns the result
- 2 Create a procedure for "READS SQL DATA"
- 3 Creating Functions
- 4 Nested function call
- 5 Single statement function
- 6 To remove a stored function, use the DROP command
- 7 Using buildin function in user-defined function
A function that takes a parameter, performs an operation using an SQL function, and returns the result
mysql>
mysql>
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-> RETURN CONCAT("Hello, ",s,"!");
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> SELECT hello("world");
+----------------+
| hello("world") |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop function hello;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
Create a procedure for "READS SQL DATA"
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(in_id int)
-> READS SQL DATA
-> BEGIN
-> SELECT first_name,last_name
-> FROM employee
-> WHERE id=in_id;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc(1);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Jason | Martin |
+------------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
Creating Functions
mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> CREATE FUNCTION myProc (cost DECIMAL(10,2), sal DECIMAL(10,2))
-> RETURNS DECIMAL(10,2)
-> RETURN cost * 1.05 + sal;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT id, first_name, myProc(salary, salary) AS total
-> FROM employee;
+------+------------+----------+
| id | first_name | total |
+------+------------+----------+
| 1 | Jason | 2530.85 |
| 2 | Alison | 13656.65 |
| 3 | James | 13416.80 |
| 4 | Celia | 4806.80 |
| 5 | Robert | 4786.30 |
| 6 | Linda | 8861.70 |
| 7 | David | 16190.45 |
| 8 | James | 2527.20 |
+------+------------+----------+
8 rows in set, 24 warnings (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop function myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.02 sec)
Nested function call
mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION myFunction (rush_ship INT(10)) RETURNS DECIMAL(10,2)
-> BEGIN
->
-> DECLARE rush_shipping_cost DECIMAL(10,2);
->
-> CASE rush_ship
-> WHEN 1 THEN
-> SET rush_shipping_cost = 20.00;
-> WHEN 2 THEN
-> SET rush_shipping_cost = 15.00;
-> WHEN 3 THEN
-> SET rush_shipping_cost = 10.00;
-> ELSE
-> SET rush_shipping_cost = 0.00;
-> END CASE;
->
-> RETURN rush_shipping_cost;
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE FUNCTION myFunction1 (item_sum DECIMAL(10,2))
-> RETURNS DECIMAL(10,1)
-> BEGIN
->
-> RETURN myFunction(1);
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> select myFunction1(1);
+----------------+
| myFunction1(1) |
+----------------+
| 20.0 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop function myFunction1;
Query OK, 0 rows affected (0.00 sec)
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
Single statement function
mysql>
mysql> DELIMITER //
mysql> CREATE FUNCTION calc_tax (cost DECIMAL(10,2)) RETURNS DECIMAL(10,2)
-> RETURN cost * .05
-> //
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> select calc_tax(123.123);
+-------------------+
| calc_tax(123.123) |
+-------------------+
| 6.16 |
+-------------------+
1 row in set, 2 warnings (0.00 sec)
mysql>
mysql> drop function calc_tax;
Query OK, 0 rows affected (0.00 sec)
To remove a stored function, use the DROP command
mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION myFunction (item_sum DECIMAL(10,2))
-> RETURNS DECIMAL(10,1)
-> BEGIN
->
->
-> RETURN ROUND(item_sum,1);
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(123.12);
+--------------------+
| myFunction(123.12) |
+--------------------+
| 123.1 |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> DROP FUNCTION test.myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
Using buildin function in user-defined function
mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION myFunction (item_sum DECIMAL(10,2))
-> RETURNS DECIMAL(10,1)
-> BEGIN
->
->
-> RETURN ROUND(item_sum,1);
->
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(123.12);
+--------------------+
| myFunction(123.12) |
+--------------------+
| 123.1 |
+--------------------+
1 row in set (0.02 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)