SQL脚本-是否存在等效的#define? [英] SQL Scripts - Does the equivalent of a #define exist?
问题描述
我有一个脚本，可用来构造表和存储过程.例如，我有一个varchar类型的列. varchar需要一个size参数，该大小我也用作存储过程以及这些过程中的参数.
I have a script that I use to construct both the tables and stored procedures. For example I have a column of type varchar. varchar requires a size parameter, that size I also use as parameters in stored procedures and within those procedures.
它的大小是否有可能等于#define，因此我可以轻松地调整大小，而不必在整个脚本中进行更改?
is it possible to have thequivalentnt of a #define for its size, so I can easily adjust the size without the necessity of having to change ithroughht the whole of the script?
我正在使用MySql工作台.
I am using MySql workbench.
编辑
我尝试了SET和DECLARE
我有一个脚本-这是(删节的)
I have a script - this is (abridged)
CREATE TABLE `locations`
(
`location` VARCHAR(25) NOT NULL
);
...
CREATE PROCEDURE AddLocation (IN location VARCHAR(25)
BEGIN
...
END$$
我想要实现的是用 constant 替换脚本中的值25-与创建表和存储过程的脚本顶部的#define相似，所以我能够轻松地将25更改为另一个数字.
What I am trying to achieve is replace the values 25 in the script with a constant - similar to a #define at the top of the script that creates the table and stored procedures, so I am able to easily change the 25 to another number.
有人找到了解决这个问题的方法吗?
Anybody has found a solution to this problem?
推荐答案
C Pre Processor(cpp)历史上与C相关联(因此得名)，但实际上它是可以使用(或滥用)的通用文本处理器. ).
The C Pre Processor (cpp) is historically associated with C (hence the name), but it really is a generic text processor that can be used (or abused) for something else.
请考虑将此文件命名为location.src(稍后再介绍).
Consider this file, named location.src (more on that later).
// C++ style comments works here
/* C style works also */
-- plain old SQL comments also work,
-- but you should avoid using '#' style of comments,
-- this will confuse the C pre-processor ...
#define LOCATION_LEN 25
/* Debug helper macro */
#include "debug.src"
DROP TABLE IF EXISTS test.locations;
CREATE TABLE test.locations
(
`location` VARCHAR(LOCATION_LEN) NOT NULL
);
DROP PROCEDURE IF EXISTS test.AddLocation;
delimiter $$
CREATE PROCEDURE test.AddLocation (IN location VARCHAR(LOCATION_LEN))
BEGIN
-- example of macro
ASSERT(length(location) > 0, "lost or something ?");
-- do something
select "Hi there.";
END
$$
delimiter ;
和文件debug.src，其中包括:
and file debug.src, which is included:
#ifdef HAVE_DEBUG
#define ASSERT(C, T) \
begin \
if (not (C)) then \
begin \
declare my_msg varchar(1000); \
set my_msg = concat("Assert failed, file:", __FILE__, \
", line: ", __LINE__, \
", condition ", #C, \
", text: ", T); \
signal sqlstate "HY000" set message_text = my_msg; \
end; \
end if; \
end
#else
#define ASSERT(C, T) begin end
#endif
使用以下命令编译时:
cpp -E location.src -o location.sql
通过cpp扩展#define值，您可以获得所需的代码.
you get the code you are looking for, with cpp expanding #define values.
使用以下命令编译时:
cpp -E -DHAVE_DEBUG location.src -o location.sql
您将得到相同的结果，再加上ASSERT宏(作为奖励发布，以显示可以完成 的操作).
you get the same, plus the ASSERT macro (posted as a bonus, to show what could be done).
假设在测试环境中部署了HAVE_DEBUG的构建(在5.5或更高版本中，因为使用了SIGNAL)，结果如下:
Assuming a build with HAVE_DEBUG deployed in a testing environment (in 5.5 or later since SIGNAL is used), the result looks like this:
mysql> call AddLocation("Here");
+-----------+
| Hi there. |
+-----------+
| Hi there. |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call AddLocation("");
ERROR 1644 (HY000): Assert failed, file:location.src, line: 24, condition length(location) > 0, text: lost or something ?
请注意文件名，行号和条件如何指向源代码中location.src中引发断言的位置，再次感谢C预处理器.
Note how the file name, line number, and condition points right at the place in the source code in location.src where the assert is raised, thanks again to the C pre processor.
现在，关于".src"文件扩展名:
Now, about the ".src" file extension:
- 您可以使用任何东西.
- 具有不同的文件扩展名有助于生成文件等，并防止混淆.
最初发布为.xql，为清晰起见，更名为.src.这里与xml查询无关.
Originally posted as .xql, renamed to .src for clarity. Nothing related to xml queries here.
与任何工具一样，使用cpp可以带来很多好处，并且以可移植的方式维护LOCATION_LEN的用例看起来非常合理. 它还可能导致不良后果，因为太多的#include，嵌套的#ifdef地狱，宏等最终会使代码混淆，因此您的工作量可能会有所不同.
As with any tools, using cpp can lead to good things, and the use case for maintaining LOCATION_LEN in a portable way looks very reasonable. It can also lead to bad things, with too many #include, nested #ifdef hell, macros, etc that at the end obfuscate the code, so your mileage may vary.
有了这个答案，您就可以了解整个内容(#define，#include，#ifdef，__FILE__，__LINE__，#C，要构建的命令行选项)，所以我希望它应该涵盖全部.
With this answer, you get the whole thing (#define, #include, #ifdef, __FILE__, __LINE__, #C, command line options to build), so I hope it should cover it all.
这篇关于SQL脚本-是否存在等效的#define?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
