【データベースMySQL】練習---ストアド・プロシージャと関数

一、データ準備
schテーブル構造:
フィールド名
データ型
プライマリキー
外部キー
空でない
ユニーク
じこぞうか
id
INT
はい
いいえ
はい
はい
いいえ
name
VARCHAR(50)
いいえ
いいえ
はい
いいえ
いいえ
glass
VARCHAR(50)
いいえ
いいえ
はい
いいえ
いいえ
schテーブルの内容:
id
name
glass
1
xiaoming
glass 1
2
xiaojun
glass 2
1、schテーブルとempテーブルの作成

#sch 
Create table sch(
Id int primary key unique,
Name varchar(50) not null,
Glass varchar(50) not null
);

#emp 
CREATE TABLE `emp`  (
  `empno` int(4) NOT NULL,
  `ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mgr` int(4) NULL DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sai` int(255) NOT NULL,
  `comm` int(255) NULL DEFAULT NULL,
  `deptno` int(2) NOT NULL,
  PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2、データの挿入

#sch 
insert into sch values(1,'xiaoming','glass 1');
insert into sch values(2,'xiaojun','glass 2');

#emp 
INSERT INTO `emp` VALUES (1001, '  ', '  ', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '   ', '   ', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '   ', '   ', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '  ', '  ', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '  ', '   ', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '  ', '  ', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '  ', '  ', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '   ', '   ', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '   ', '   ', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '   ', '   ', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '  ', '  ', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '  ', '  ', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '  ', '   ', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '  ', '  ', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '  ', '   ', 1001, '2013-05-01', 80000, 50000, 50);

3、表データ記録の表示

mysql> select * from sch;
+----+----------+---------+
| Id | Name     | Glass   |
+----+----------+---------+
|  1 | xiaoming | glass 1 |
|  2 | xiaojun  | glass 2 |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from emp;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename     | job       | mgr  | hiredate   | sai   | comm  | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
|  1001 |         |         | 1013 | 2000-12-17 |  8000 |  NULL |     20 |
|  1002 |        |        | 1006 | 2001-02-20 | 16000 |  3000 |     30 |
|  1003 |        |        | 1006 | 2001-02-22 | 12500 |  5000 |     30 |
|  1004 |         |         | 1009 | 2001-04-02 | 29750 |  NULL |     20 |
|  1005 |         |        | 1006 | 2001-09-28 | 12500 | 14000 |     30 |
|  1006 |         |         | 1009 | 2001-05-01 | 28500 |  NULL |     30 |
|  1007 |         |         | 1009 | 2001-09-01 | 24500 |  NULL |     10 |
|  1008 |        |        | 1004 | 2007-04-19 | 30000 |  NULL |     20 |
|  1009 |        |        | NULL | 2001-11-17 | 50000 |  NULL |     10 |
|  1010 |        |        | 1006 | 2001-09-08 | 15000 |     0 |     30 |
|  1011 |         |         | 1006 | 2007-05-23 | 11000 |  NULL |     20 |
|  1012 |         |         | 1006 | 2001-12-03 |  9500 |  NULL |     30 |
|  1013 |         |        | 1004 | 2001-12-03 | 30000 |  NULL |     20 |
|  1014 |         |         | 1007 | 2002-01-23 | 13000 |  NULL |     10 |
|  1015 |         |        | 1001 | 2013-05-01 | 80000 | 50000 |     50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.00 sec)

二、練習問題
1、テーブル内のレコード数を統計できる記憶関数を作成する.関数名はcount_sch().

\d $$
Create function count_sch()
Returns int
Begin
Declare n int default 0;
Select count(*) into n from sch;
Return n;
End $$
\d;

mysql> Select count_sch();
+-------------+
| count_sch() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

2、ストレージプロセスavg_を作成するsaiには,deptno,jobの3つのパラメータがあり,平均賃金を受信し,機能クエリempテーブルdeptは30,jobは販売員の平均賃金である.

\d $$
Create procedure avg_sai(in p1 int(2),in p2 varchar(255),out p3 int)
Begin
  Select avg(sai) into p3 from emp
    Where deptno=p1 and job=p2;
End $$
\d;

mysql> call avg_sai(30,'   ',@a);
Query OK, 1 row affected (0.00 sec)

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