mysql - 在 Mysql 过程中似乎找不到错误 Error 1064 (42000)
问题描述
DELIMITER $
CREATE PROCEDURE geographical(IN appl_name1 varchar(500),
IN appl_name2 varchar(500),
IN appl_name3 varchar(500),
IN appl_name4 varchar(500),
IN granted char(1),
IN oppose tinyint(4),
IN beginDate date,
IN endDate date,
IN ipc varchar(300))
BEGIN
DROP TABLE IF EXISTS first_ctry;
DROP TABLE IF EXISTS second_ctry;
DROP TABLE IF EXISTS third_ctry;
DROP TABLE IF EXISTS fourth_ctry;
DROP TABLE IF EXISTS ref_ctry;
SET @d1 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS first_ctry AS
(SELECT a.docdb_family_id, a.appln_auth, MIN(a.appln_filing_date) as first_date
FROM patents_prod1.tls201_appln a
INNER JOIN patents_prod1.tls207_pers_appln b ON b.appln_id = a.appln_id
INNER JOIN entity.combined c ON c.person_id = b.person_id
INNER JOIN patents_prod1.tls209_appln_ipc d ON d.appln_id = a.appln_id
WHERE c.name_friendly LIKE ', CONCAT('"', appl_name1, '"'),
' AND IF(',CONCAT('"',granted,'"'),' IS NOT NULL,a.granted LIKE ',CONCAT('"',granted,'"'),',1=1)
AND IF(',CONCAT('"',oppose,'"'),' IS NOT NULL,a.isOpposed = ',CONCAT('"',oppose,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NOT NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and ',CONCAT('"',endDate,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and CURDATE(),1=1)
AND IF(',CONCAT('"',ipc,'"'),' IS NOT NULL,FIND_IN_SET(d.ipc_class_symbol, ',CONCAT('"',ipc,'"'),'),1=1)
AND a.appln_filing_year < 9999
GROUP BY a.docdb_family_id, a.appln_auth
ORDER BY a.docdb_family_id, first_date)');
PREPARE stmtd1 FROM @d1;
EXECUTE stmtd1;
DEALLOCATE PREPARE stmtd1;
SET @s1 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS ref_ctry AS
(SELECT appln_auth, COUNT(distinct docdb_family_id) as ',CONCAT('"', appl_name1, '"'),' FROM first_ctry
GROUP BY appln_auth
ORDER BY ', CONCAT('"', appl_name1, '"'), ' DESC LIMIT 15)');
PREPARE stmt1 FROM @s1;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
IF (appl_name2 IS NOT NULL OR appl_name2 <> '') THEN
SET @d2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS second_ctry AS
(SELECT a.docdb_family_id, a.appln_auth, MIN(a.appln_filing_date) as first_date
FROM patents_prod1.tls201_appln a
INNER JOIN patents_prod1.tls207_pers_appln b ON b.appln_id = a.appln_id
INNER JOIN entity.combined c ON c.person_id = b.person_id
INNER JOIN patents_prod1.tls209_appln_ipc d ON d.appln_id = a.appln_id
WHERE c.name_friendly LIKE ', CONCAT('"', appl_name2, '"'),
' AND IF(',CONCAT('"',granted,'"'),' IS NOT NULL,a.granted LIKE ',CONCAT('"',granted,'"'),',1=1)
AND IF(',CONCAT('"',oppose,'"'),' IS NOT NULL,a.isOpposed = ',CONCAT('"',oppose,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NOT NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and ',CONCAT('"',endDate,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and CURDATE(),1=1)
AND IF(',CONCAT('"',ipc,'"'),' IS NOT NULL,FIND_IN_SET(d.ipc_class_symbol, ',CONCAT('"',ipc,'"'),'),1=1)
AND a.appln_filing_year < 9999
GROUP BY a.docdb_family_id, a.appln_auth
ORDER BY a.docdb_family_id, first_date)');
PREPARE stmtd2 FROM @d2;
EXECUTE stmtd2;
DEALLOCATE PREPARE stmtd2;
SET @s2 = CONCAT('ALTER TABLE ref_ctry
ADD COLUMN ', CONCAT('"', appl_name2, '"'), ' int NOT NULL DEFAULT 0');
PREPARE stmt2 FROM @s2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
SET @s3 = CONCAT('UPDATE ref_ctry a
SET ',CONCAT('"', appl_name2, '"'),' = (SELECT COUNT(distinct b.docdb_family_id) FROM second_ctry b WHERE b.appln_auth = a.appln_auth)');
PREPARE stmt3 FROM @s3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
IF (appl_name3 IS NOT NULL OR appl_name3 <> '') THEN
-- ADD THIRD COUNTRY DATA
SET @d3 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS third_ctry AS
(SELECT a.docdb_family_id, a.appln_auth, MIN(a.appln_filing_date) as first_date
FROM patents_prod1.tls201_appln a
INNER JOIN patents_prod1.tls207_pers_appln b ON b.appln_id = a.appln_id
INNER JOIN entity.combined c ON c.person_id = b.person_id
INNER JOIN patents_prod1.tls209_appln_ipc d ON d.appln_id = a.appln_id
WHERE c.name_friendly LIKE ', CONCAT('"', appl_name3, '"'),
' AND IF(',CONCAT('"',granted,'"'),' IS NOT NULL,a.granted LIKE ',CONCAT('"',granted,'"'),',1=1)
AND IF(',CONCAT('"',oppose,'"'),' IS NOT NULL,a.isOpposed = ',CONCAT('"',oppose,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NOT NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and ',CONCAT('"',endDate,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and CURDATE(),1=1)
AND IF(',CONCAT('"',ipc,'"'),' IS NOT NULL,FIND_IN_SET(d.ipc_class_symbol, ',CONCAT('"',ipc,'"'),'),1=1)
AND a.appln_filing_year < 9999
GROUP BY a.docdb_family_id, a.appln_auth
ORDER BY a.docdb_family_id, first_date)');
PREPARE stmtd3 FROM @d3;
EXECUTE stmtd3;
DEALLOCATE PREPARE stmtd3;
SET @s4 = CONCAT('ALTER TABLE ref_ctry
ADD COLUMN ', CONCAT('"', appl_name3, '"'), ' int NOT NULL DEFAULT 0');
PREPARE stmt4 FROM @s4;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;
SET @s5 = CONCAT('UPDATE ref_ctry a
SET ',CONCAT('"', appl_name3, '"'),' = (SELECT COUNT(distinct b.docdb_family_id) FROM third_ctry b WHERE b.appln_auth = a.appln_auth)');
PREPARE stmt5 FROM @s5;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;
IF (appl_name4 IS NOT NULL OR appl_name4 <> '') THEN
-- ADD FOURTH COUNTRY DATA
SET @d4 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS fourth_ctry AS
(SELECT a.docdb_family_id, a.appln_auth, MIN(a.appln_filing_date) as first_date
FROM patents_prod1.tls201_appln a
INNER JOIN patents_prod1.tls207_pers_appln b ON b.appln_id = a.appln_id
INNER JOIN entity.combined c ON c.person_id = b.person_id
INNER JOIN patents_prod1.tls209_appln_ipc d ON d.appln_id = a.appln_id
WHERE c.name_friendly LIKE ', CONCAT('"', appl_name4, '"'),
' AND IF(',CONCAT('"',granted,'"'),' IS NOT NULL,a.granted LIKE ',CONCAT('"',granted,'"'),',1=1)
AND IF(',CONCAT('"',oppose,'"'),' IS NOT NULL,a.isOpposed = ',CONCAT('"',oppose,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NOT NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and ',CONCAT('"',endDate,'"'),',1=1)
AND IF(',CONCAT('"',beginDate,'"'),' IS NOT NULL AND ',CONCAT('"',endDate,'"'),' IS NULL,a.appln_filing_date BETWEEN ',CONCAT('"',beginDate,'"'),' and CURDATE(),1=1)
AND IF(',CONCAT('"',ipc,'"'),' IS NOT NULL,FIND_IN_SET(d.ipc_class_symbol, ',CONCAT('"',ipc,'"'),'),1=1)
AND a.appln_filing_year < 9999
GROUP BY a.docdb_family_id, a.appln_auth
ORDER BY a.docdb_family_id, first_date)');
PREPARE stmtd4 FROM @d4;
EXECUTE stmtd4;
DEALLOCATE PREPARE stmtd4;
SET @s6 = CONCAT('ALTER TABLE ref_ctry
ADD COLUMN ', CONCAT('"', appl_name4, '"'), ' int NOT NULL DEFAULT 0');
PREPARE stmt6 FROM @s6;
EXECUTE stmt6;
DEALLOCATE PREPARE stmt6;
SET @s7 = CONCAT('UPDATE ref_ctry a
SET ',CONCAT('"', appl_name4, '"'),' = (SELECT COUNT(distinct b.docdb_family_id) FROM fourth_ctry b WHERE b.appln_auth = a.appln_auth)');
PREPARE stmt7 FROM @s7;
EXECUTE stmt7;
DEALLOCATE PREPARE stmt7;
-- FINAL TABLE FOR HORIZONTAL BAR CHARTS**
SELECT * FROM ref_ctry;
ELSE
SELECT * FROM ref_ctry;
END IF;
ELSE
SELECT * FROM ref_ctry;
END IF;
ELSE
SELECT * FROM ref_ctry;
END IF;
END$
DELIMITER ;
我正在创建这个程序地理。当我运行它给我错误
call geographical("VESTAS",null,null,null,null,null,null,null,null);
ERROR 1064 (42000):您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以在第 1 行的“NULL”附近使用正确的语法
我还编写了另一个程序,其中没有引号中的语句,它给了我一个空集。
解决方案
您没有理由在存储过程中使用准备好的语句。它使程序更难编写/读取/维护,并使其更容易出错。
在过程中使用直接 SQL,这样您也可以避免在连接 NULL 值时出现问题。
推荐阅读
- java - 请求 HttpResponse 时 Android 应用程序崩溃
- python - oracle datetime 字段 spark 读取失败并出现精度错误
- python - 课堂内情节刷新
- laravel - laravel 迁移“SQL:更改表`posts`添加约束`posts_category_id_foreign`外键(`category_id`)引用`categories`(`id`)”
- c# - .NET Core 2.2 Web 应用程序。DbContext 是由依赖注入设置的,它可以在运行时更改吗?
- sql - 尝试使用生产日期创建一个井数以与 BOE 进行比较,并将其与资本支出和总 BOE 进行比较
- python - 从列中删除未出现在数组中的数字
- python - 使用 BeautfulSoup 问题提取子标签文本
- react-native - 如何在没有流错误的情况下使用 __DEV__
- typescript - Observable 不同步行动