Mysqlストレージ・プロシージャが配列タイプパラメータを転送、変換できないソリューション
6878 ワード
mysqlストアド・プロシージャの機能が弱い問題はずっと注目されている問題で、今日はMysqlストアド・プロシージャが配列タイプのパラメータを伝達できない解決策についてお話しします.
多くの場合、ストレージの作成中に配列が使用されることが多いが、mysqlにプロシージャの転送パラメータを格納するには、直接配列に転送する方法はない.この場合、私たちは後退して求めるか、あるいは別の方法で文字列の形式でパラメータを入力し、プロセスボディで文字列を配列に変換するしかありませんか?しかし、残念ながらmysqlは文字列を配列に変換する関数を直接提供していません.今あなたは人を殴りたい感じがしますか?しかし、慌てないでください.この道は通じません.私たちは別の道を歩いて、いつも解決方法があります.入力した文字列を複数の文字に切り取ってテンポラリ・テーブルに転送し、カーソルまたは直接関連テーブルを使用してデータをフィルタできます.これで後から予想される効果が得られます.
次に、例を挙げて具体的に実践します.
1.インスタンス用のデータベースの作成:
2、需要:学生番号によって学生情報を一括削除する
説明:ストレージ・プロシージャを作成するときに、2つのパラメータが入力されます.1つ目は、入力する配列文字列の形式を表し、2つ目のパラメータは、文字列をどのように分割するかを表します.初期化変数 を宣言受信パラメータ配列長 を取得する.テンポラリ・テーブル の作成配列文字列を切り取り、後続のトラフィックが を使用するために一時テーブルに順次格納する.
注意:ストアド・プロシージャの終了時にテンポラリ・テーブルを削除することを忘れないでください.
非常に複雑なビジネスではなく、ストレージ・プロシージャを使用する必要はありません.本稿では、ストレージ・プロシージャを必ず使用するように導くのではなく、このようなことを知ってもらうだけです.
多くの場合、ストレージの作成中に配列が使用されることが多いが、mysqlにプロシージャの転送パラメータを格納するには、直接配列に転送する方法はない.この場合、私たちは後退して求めるか、あるいは別の方法で文字列の形式でパラメータを入力し、プロセスボディで文字列を配列に変換するしかありませんか?しかし、残念ながらmysqlは文字列を配列に変換する関数を直接提供していません.今あなたは人を殴りたい感じがしますか?しかし、慌てないでください.この道は通じません.私たちは別の道を歩いて、いつも解決方法があります.入力した文字列を複数の文字に切り取ってテンポラリ・テーブルに転送し、カーソルまたは直接関連テーブルを使用してデータをフィルタできます.これで後から予想される効果が得られます.
次に、例を挙げて具体的に実践します.
1.インスタンス用のデータベースの作成:
CREATE DATABASE huafeng_db;
use huafeng_db;
DROP TABLE IF EXISTS `huafeng_db`.`t_scores`;
DROP TABLE IF EXISTS `huafeng_db`.`t_students`;
DROP TABLE IF EXISTS `huafeng_db`.`t_class`;
CREATE TABLE `huafeng_db`.`t_class` (
`class_id` int(11) NOT NULL,
`class_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('1', ' ');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('2', ' ');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('3', ' ');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('4', ' ');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('5', ' ');
INSERT INTO `huafeng_db`.`t_class` (`class_id`, `class_name`) VALUES ('6', ' ');
CREATE TABLE `t_students` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` varchar(32) NOT NULL,
`sex` int(1) DEFAULT NULL,
`seq_no` int(11) DEFAULT NULL,
`class_id` int(11) NOT NULL,
PRIMARY KEY (`student_id`),
KEY `class_id` (`class_id`),
CONSTRAINT `t_students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(' ',0,1,'1');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(' ',0,2,'2');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(' ',1,3,'3');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(' ',0,4,'4');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(' ',1,5,'5');
INSERT INTO `huafeng_db`.`t_students`(`student_name`,`sex`,`seq_no`,`class_id`) VALUES(' ',1,6,'6');
CREATE TABLE `huafeng_db`.`t_scores` (
`score_id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(64) DEFAULT NULL,
`score` double(3,2) DEFAULT NULL,
`student_id` int(11) DEFAULT NULL,
PRIMARY KEY (`score_id`),
KEY `student_id` (`student_id`),
CONSTRAINT `t_scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `t_students` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('1', ' ', '90', '1');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('2', ' ', '97', '1');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('3', ' ', '95', '1');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('4', ' ', '92', '2');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('5', ' ', '100', '2');
INSERT INTO `t_scores` (`score_id`, `course_name`, `score`, `student_id`) VALUES ('6', ' ', '98', '2');
2、需要:学生番号によって学生情報を一括削除する
DROP PROCEDURE IF EXISTS `p_del_studentInfo_bySeqNo`;
DELIMITER $$
CREATE PROCEDURE p_del_studentInfo_bySeqNo(IN arrayStr VARCHAR(1000),IN sSplit VARCHAR(10))
SQL SECURITY INVOKER #
BEGIN
DECLARE e_code INT DEFAULT 0;# 0
DECLARE result VARCHAR(256) CHARACTER set utf8;# ,
DECLARE arrLength INT DEFAULT 0;/* */
DECLARE arrString VARCHAR(1000);/* */
DECLARE sStr VARCHAR(1000);/* */
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_code=1;# ;( )
START TRANSACTION;#
SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/* */
SET arrString = arrayStr;
DROP TEMPORARY TABLE IF EXISTS list_tmp;
create temporary table list_tmp(id VARCHAR(32));/* */
WHILE arrLength > 0 DO
set sStr = substr(arrString,1,instr(arrString,sSplit)-1); --
set arrString = substr(arrString,length(sStr)+length(sSplit)+1); --
set arrLength = arrLength -1;
set @str = trim(sStr);
insert into list_tmp(id) values(@str);
END WHILE;
IF row_count()=0 THEN
SET e_code = 1;
SET result = ' ';
END IF;
set @count = (SELECT count(1) FROM t_students s,list_tmp t WHERE s.seq_no = t.id);
IF @count >0 THEN
DELETE FROM t_scores WHERE student_id in (SELECT s.student_id FROM t_students s,list_tmp t WHERE s.seq_no = t.id);
DELETE FROM t_students WHERE student_id in (SELECT t.id FROM list_tmp t);
ELSE
SET e_code = 1;
SET result = ' !';
END IF;
IF e_code=1 THEN
ROLLBACK; #
ELSE
COMMIT;
SET result = ' ';
END IF;
SELECT result;
DROP TEMPORARY TABLE IF EXISTS list_tmp;
END $$
DELIMITER ;
説明:ストレージ・プロシージャを作成するときに、2つのパラメータが入力されます.1つ目は、入力する配列文字列の形式を表し、2つ目のパラメータは、文字列をどのように分割するかを表します.
DECLARE arrLength INT DEFAULT 0;/* */
DECLARE arrString VARCHAR(1000);/* */
DECLARE sStr VARCHAR(1000);/* */
SET arrLength = LENGTH(arrayStr) - LENGTH(REPLACE(arrayStr,sSplit,''));/* */
SET arrString = arrayStr;/* */
DROP TEMPORARY TABLE IF EXISTS list_tmp;
create temporary table list_tmp(id VARCHAR(32));/* */
WHILE arrLength > 0 DO
set sStr = substr(arrString,1,instr(arrString,sSplit)-1); --
set arrString = substr(arrString,length(sStr)+length(sSplit)+1); --
set arrLength = arrLength -1;
set @str = trim(sStr);
insert into list_tmp(id) values(@str);
END WHILE;
注意:ストアド・プロシージャの終了時にテンポラリ・テーブルを削除することを忘れないでください.
非常に複雑なビジネスではなく、ストレージ・プロシージャを使用する必要はありません.本稿では、ストレージ・プロシージャを必ず使用するように導くのではなく、このようなことを知ってもらうだけです.