动态 SQL 示例
我最近了解了什么是动态 sql，它对我来说最有趣的功能之一是我们可以使用动态列名和表。但我无法思考现实生活中有用的例子。我唯一想到的是统计表。
假设我们有一个包含名称、类型和created_data 的表。然后我们想要一个表，其列中是从created_data列开始的年份，行类型是年份中创建的名称数量。 （抱歉我的英语）
使用以列和表作为参数的动态 SQL 的其他有用的现实生活示例还有哪些？你如何使用它？
感谢您的任何建议和帮助:) 问候 加布
/编辑 感谢您的回复，我对不包含管理内容或数据库转换或类似内容的示例特别感兴趣，我正在寻找示例 java 中的代码比在存储过程中使用动态 sql 更复杂的示例。
I have lately learned what is dynamic sql and one of the most interesting features of it to me is that we can use dynamic columns names and tables. But I cannot think about useful real life examples. The only one that came into my mind is statistical table.
Let`s say that we have table with name, type and created_data. Then we want to have a table that in columns are years from created_data column and in row type and number of names created in years. (sorry for my English)
What can be other useful real life examples of using dynamic sql with column and table as parameters? How do you use it?
Thanks for any suggestions and help :)
regards
Gabe
/edit
Thx for replies, I am particulary interested in examples that do not contain administrative things or database convertion or something like that, I am looking for examples where the code in example java is more complicated than using a dynamic sql in for example stored procedure.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
动态 SQL 的一个示例是修复损坏的架构并使其更可用。
例如，如果您有数百个用户，而某人最初决定为每个用户创建一个新表，您可能需要重新设计数据库以仅包含一个表。然后您需要将所有现有数据迁移到这个新系统。
您可以查询信息模式以获取具有特定命名模式或包含特定列的表名称，然后使用动态 SQL 从每个表中选择所有数据，然后将其放入单个表中。
希望在执行此操作后您将永远不需要再次接触动态 SQL。
An example of dynamic SQL is to fix a broken schema and make it more usable.
For example if you have hundreds of users and someone originally decided to create a new table for each user, you might want to redesign the database to have only one table. Then you'd need to migrate all the existing data to this new system.
You can query the information schema for table names with a certain naming pattern or containing certain columns then use dynamic SQL to select all the data from each of those tables then put it into a single table.
Then hopefully after doing this once you will never need to touch dynamic SQL again.
很久以前，我曾使用过用户在公共数据库中使用自己的表的应用程序。
想象一下，每个用户都可以通过 UI 在数据库中创建自己的表。为了访问这些表中的数据，开发人员需要使用动态 SQL。
Long-long ago I have worked with appliaction where users uses their own tables in common database.
Imagine, each user can create their own table in database from UI. To get the access to data from these tables, developer needs to use the dynamic SQL.
我曾经不得不编写一个 Excel 导入，其中 Excel 工作表不像 csv 文件，而是像矩阵一样布局。所以我必须处理 3 个临时表的未知数量的列（列、行、“内场”）。这些行也是树的一种简短形式。听起来很奇怪，但做起来很有趣。
在 SQL Server 中，如果没有动态 SQL，就没有机会处理这个问题。
I once had to write an Excel import where the excel sheet was not like a csv file but layed out like a matrix. So I had to deal with a unknown number of columns for 3 temporary tables (columns, rows, "infield"). The rows were also a short form of tree. Sounds weird, but was a fun to do.
In SQL Server there was no chance to handle this without dynamic SQL.
我最近遇到的另一个例子。一个包含大约 250 个表的 MySQL 数据库，全部位于 MyISAM 引擎中，根本没有数据库设计模式、图表或其他解释 - 好吧，除了不太有用的表和列名称。
为了规划到 InnoDB 的转换并找到可能的外键，我们必须手动检查从 Web 前端创建的所有查询（以及
JOIN和WHERE子句中使用的条件）编写或制作一个脚本，使用动态 SQL 并检查具有兼容数据类型的所有列组合，并比较存储在这些列组合中的数据（然后手动接受或拒绝这些可能性）。Another example from a situation I recently came up against. A MySQL database of about 250 tables, all in MyISAM engine and no database design schema, chart or other explanation at all - well, except the not so helpful table and column names.
To plan for conversion to InnoDB and find possible foreign keys, we either had to manually check all queries (and the conditions used in
JOINandWHEREclauses) created from the web frontend code or make a script that uses dynamic SQL and checks all combinations of columns with compatible datatype and compares the data stored in those columns combinations (and then manually accept or reject these possibilities).