· 9 years ago · Jun 29, 2017, 05:12 AM
1DELIMITER //
2DROP DATABASE IF EXISTS mysql_bookdb//
3CREATE DATABASE mysql_bookdb//
4
5USE mysql_bookdb//
6
7GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE ON mysql_bookdb.* TO george@localhost IDENTIFIED BY 'password'//
8GRANT FILE ON *.* TO george@localhost IDENTIFIED BY 'password'////
9
10
11CREATE TABLE IF NOT EXISTS AUTHORS
12(
13 ID INT(11) NOT NULL AUTO_INCREMENT,
14 PRIMARY KEY(ID)
15) ENGINE MyISAM DEFAULT CHARSET=utf8//
16
17
18CREATE TABLE IF NOT EXISTS NAMES
19(
20 ID INT(11) NOT NULL AUTO_INCREMENT,
21 NAME VARCHAR(100) NOT NULL,
22 PRIMARY KEY(ID),
23 UNIQUE (NAME)
24) ENGINE MyISAM DEFAULT CHARSET=utf8//
25
26
27CREATE TABLE IF NOT EXISTS FULLNAMES
28(
29 AUTHOR_ID INT(11) NOT NULL,
30 NAME_ID INT(11) NOT NULL,
31 ORDINAL_POSITION INT(11),
32 FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS (ID),
33 FOREIGN KEY (NAME_ID) REFERENCES NAMES (ID)
34) ENGINE MyISAM DEFAULT CHARSET=utf8//
35
36
37
38CREATE TABLE IF NOT EXISTS AUTHORSHIP
39(
40 ID INT(11) NOT NULL AUTO_INCREMENT,
41 AUTHOR_ID INT(11) NOT NULL,
42 BOOK_ID INT(11) NOT NULL,
43 PRIMARY KEY(ID),
44 FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHORS (ID),
45 FOREIGN KEY (BOOK_ID) REFERENCES BOOKS (ID)
46) ENGINE MyISAM DEFAULT CHARSET=utf8//
47
48
49
50CREATE TABLE IF NOT EXISTS BOOKS
51(
52 ID INT(11) NOT NULL AUTO_INCREMENT,
53 TITLE1 VARCHAR(100) DEFAULT NULL,
54 PRIMARY KEY(ID)
55) ENGINE MyISAM DEFAULT CHARSET=utf8//
56
57CREATE FUNCTION AddAuthor2(_name1 VARCHAR(100), _name2 VARCHAR(100))
58RETURNS INT
59DETERMINISTIC
60BEGIN
61 DECLARE name_id1 INT DEFAULT NULL;
62 DECLARE name_id2 INT DEFAULT NULL;
63
64 DECLARE author_id1 INT DEFAULT NULL;
65 DECLARE author_id2 INT DEFAULT NULL;
66
67 SELECT N.ID INTO name_id1 FROM NAMES AS N WHERE N.NAME LIKE UPPER(_name1);
68 IF name_id1 IS NULL THEN
69 INSERT INTO NAMES (NAME) VALUES (UPPER(_name1));
70 SELECT LAST_INSERT_ID() INTO name_id1;
71 END IF;
72
73 SELECT N.ID INTO name_id2 FROM NAMES AS N WHERE N.NAME LIKE UPPER(_name2);
74 IF name_id2 IS NULL THEN
75 INSERT INTO NAMES (NAME) VALUES (UPPER(_name2));
76 SELECT LAST_INSERT_ID() INTO name_id2;
77 END IF;
78
79 SELECT FN.AUTHOR_ID INTO author_id1
80 FROM FULLNAMES AS FN
81 WHERE FN.NAME_ID = name_id1 AND FN.ORDINAL_POSITION = 0;
82
83 SELECT FN.AUTHOR_ID INTO author_id2
84 FROM FULLNAMES AS FN
85 WHERE FN.NAME_ID = name_id2 AND FN.ORDINAL_POSITION = 1;
86
87 IF author_id1 IS NULL OR author_id2 IS NULL OR (author_id1 != author_id2) THEN
88 INSERT INTO AUTHORS () VALUES ();
89 SELECT LAST_INSERT_ID() INTO author_id1;
90 INSERT INTO FULLNAMES (AUTHOR_ID, NAME_ID, ORDINAL_POSITION) VALUES (author_id1, name_id1, 0);
91 INSERT INTO FULLNAMES (AUTHOR_ID, NAME_ID, ORDINAL_POSITION) VALUES (author_id1, name_id2, 1);
92 END IF;
93
94
95 RETURN author_id1;
96END//
97
98SELECT AddAuthor2('Hans', 'Hansen');
99SELECT AddAuthor2('Hans', 'Jensen');
100SELECT AddAuthor2('Hans', 'Nielsen');
101
102----------------------------
103gauss:/home/george/Desktop# mysql --user=root --password=password < books4_db.sql
104AddAuthor2('Hans', 'Hansen')
1051
106AddAuthor2('Hans', 'Jensen')
1072
108ERROR 1172 (42000) at line 98: Result consisted of more than one row
109gauss:/home/george/Desktop#