用于创建过程的 MySQL Shell 脚本
我正在编写 Mac OSX 的安装脚本，并使用 shell 脚本来创建数据库以及程序使用所需的所有表。我面临的问题是创建我们需要的程序。我尝试了多种实现方式，想知道是否有人知道一个例子或者是否可能。以下是我所掌握的程序进展情况。我将不胜感激任何帮助。
过程：
DELIMITER //
CREATE PROCEDURE Hours_Procedure()
BEGIN
DECLARE avg_usage FLOAT;
DECLARE max_time TIMESTAMP;
DECLARE counter INTEGER;
DECLARE NumOfNodes INTEGER;
DECLARE Num_Hour_Records INTEGER;
SET counter = 0;
SELECT MAX(Node_Num) INTO NumOfNodes FROM Minutes;
loop1 : LOOP SET avg_usage = 0.0;
SET counter = counter + 1;
IF counter = (NumOfNodes + 1) THEN LEAVE loop1;
ELSE SELECT AVG(x.Power_Usage), MAX(x.Record_Time) INTO avg_usage, max_time FROM(
SELECT M.Power_Usage, M.Record_Time FROM Minutes M
WHERE M.Node_Num = counter ORDER BY RID DESC LIMIT 60) x;
INSERT INTO Hours(Node_Num, Record_Time, Power_Usage) VALUES (counter, max_time, avg_usage);
DELETE FROM Minutes WHERE Node_Num = counter ORDER BY RID ASC LIMIT 60;
END IF;
END LOOP loop1;
DROP INDEX Hours_Index ON Hours;
CREATE INDEX Hours_Index ON Hours(RID, Node_Num);
END// DELIMITER ;
我尝试过的：
$mysql -u $_adminuser -h $_host -Bse "USE $_hostdb; $createProc;"
其中 $createProc 是创建过程的代码。
I'm working on an install script for Mac OSX and am using a shell script to create a database and all the tables I need for the program to use. The problem that I am facing is the creating of the procedure we need. I have tried multiple way of implementing and wondered if anyone knows of an example or if it is possible. Below is what I have as far as the procedure goes. I would appreciate any help.
The Procedure:
DELIMITER //
CREATE PROCEDURE Hours_Procedure()
BEGIN
DECLARE avg_usage FLOAT;
DECLARE max_time TIMESTAMP;
DECLARE counter INTEGER;
DECLARE NumOfNodes INTEGER;
DECLARE Num_Hour_Records INTEGER;
SET counter = 0;
SELECT MAX(Node_Num) INTO NumOfNodes FROM Minutes;
loop1 : LOOP SET avg_usage = 0.0;
SET counter = counter + 1;
IF counter = (NumOfNodes + 1) THEN LEAVE loop1;
ELSE SELECT AVG(x.Power_Usage), MAX(x.Record_Time) INTO avg_usage, max_time FROM(
SELECT M.Power_Usage, M.Record_Time FROM Minutes M
WHERE M.Node_Num = counter ORDER BY RID DESC LIMIT 60) x;
INSERT INTO Hours(Node_Num, Record_Time, Power_Usage) VALUES (counter, max_time, avg_usage);
DELETE FROM Minutes WHERE Node_Num = counter ORDER BY RID ASC LIMIT 60;
END IF;
END LOOP loop1;
DROP INDEX Hours_Index ON Hours;
CREATE INDEX Hours_Index ON Hours(RID, Node_Num);
END// DELIMITER ;
What I tried:
$mysql -u $_adminuser -h $_host -Bse "USE $_hostdb; $createProc;"
Where $createProc is the code to create the procedure.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
将您的过程存储在文件
procedure.sql中，尝试以下操作。它通过在命令行上指定数据库来消除 USE 语句：With your procedure stored in the file
procedure.sql, try the following. It eliminates theUSEstatement by specifying the database on the command line: