CN116340339A - Method and system for automatically generating SQL Server user storage process - Google Patents

Method and system for automatically generating SQL Server user storage process Download PDF

Info

Publication number
CN116340339A
CN116340339A CN202310230624.1A CN202310230624A CN116340339A CN 116340339 A CN116340339 A CN 116340339A CN 202310230624 A CN202310230624 A CN 202310230624A CN 116340339 A CN116340339 A CN 116340339A
Authority
CN
China
Prior art keywords
storage process
sql
database
user
stored
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202310230624.1A
Other languages
Chinese (zh)
Inventor
张军健
张德浩
陈志勇
丁翔龙
乔明昊
袁鹏
刘滨
丁占平
李学勤
李雪
赵晨汐
张靓
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Shandong Metalist Heavy Machinery Co ltd
Original Assignee
Shandong Metalist Heavy Machinery Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Shandong Metalist Heavy Machinery Co ltd filed Critical Shandong Metalist Heavy Machinery Co ltd
Priority to CN202310230624.1A priority Critical patent/CN116340339A/en
Publication of CN116340339A publication Critical patent/CN116340339A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/242—Query formulation
    • G06F16/2433—Query languages
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2455—Query execution
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/248—Presentation of query results
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Computational Linguistics (AREA)
  • Data Mining & Analysis (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Mathematical Physics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The invention discloses a method and a system for automatically generating SQL Server user storage processes, and mainly relates to the technical field of database storage. The method comprises the following steps: selecting a database for implementing an automatic generation and storage process; taking the database in the steps as a query condition, acquiring all table objects of the database, formatting and displaying the results; selecting a table for generating a stored procedure according to the output result in the step, and simultaneously selecting the type of the stored procedure to be established by the table; inputting the selected result in the steps, and automatically generating an SQL sentence for establishing a user storage process; executing the SQL sentence in the step, and automatically generating a storage process in the database selected in the step S1. The invention has the beneficial effects that: the storage process is automatically generated according to the requirement, and the efficiency and the accuracy are achieved.

Description

Method and system for automatically generating SQL Server user storage process
Technical Field
The invention relates to the technical field of database storage, in particular to a method and a system for automatically generating SQL Server user storage processes.
Background
When the SQL Server is used as a database to develop an application system, particularly when a large and medium application system is developed, a storage process is usually selected to process data in order to ensure data security and improve system performance. Under the default condition, only the system stores the process in the newly created database, and the user stores the process needed by the application system and needs the user to develop the process by himself.
When the application system is developed, the storage process of 4 operations such as adding, modifying, deleting and paging inquiry on the data in the table is mainly developed. The development of the storage process is a difficult work, and can be mastered only by system learning, so that the use threshold is high. Developing an application system requires creating hundreds to thousands of storage processes, each storage process has tens to hundreds of lines of codes, and if the work is completed completely by manpower, the time and the labor are wasted, the efficiency is low, and the quality of the storage processes is uneven.
There is a need for a method and system for automatically generating user stored procedures that addresses the above-described problems.
Disclosure of Invention
The invention aims to provide a method and a system for automatically generating a storage process of an SQL Server user, which realize the on-demand automatic generation of the storage process and have the efficiency and the accuracy.
The invention aims to achieve the aim, and the aim is achieved by the following technical scheme:
a method for automatically generating a stored procedure of an SQL Server user, comprising the steps of: selecting a database for implementing an automatic generation and storage process; taking the database in the step as a query condition, acquiring all table objects of the database, formatting and displaying the results; selecting a table for generating a stored procedure according to the output result in the step, and simultaneously selecting the type of the stored procedure to be established by the table; inputting the selected result in the steps, and automatically generating an SQL sentence for establishing a user storage process; executing the SQL sentence in the step, and automatically generating a storage process in the database selected in the first step.
Preferably, the step of obtaining all table objects of the database and formatting the results is shown specifically as follows: querying SQL Server system table sysobjects, obtaining all table objects of the database, and displaying the result in the system after formatting.
Preferably, the automatically generating the SQL sentence for establishing the user storage process specifically comprises the following steps:
determining column information of each table and determining a storage process category generated for the table by using the table selected by the user in the cyclic traversal step S3, firstly reading the table selected by the user in single cycle, then querying an SQL Server system table COLUMNS under the condition of the table name, acquiring all column information of the table selected by the user, storing the result to a first temporary table, simultaneously recording the result selected by the user, determining the storage process category generated for the table selected by the user, and storing the result to a second temporary table;
traversing a second temporary table to determine a storage process type generated for the table selected by the user; and the stored process type is used as a judging condition, and the branch statement is utilized to guide the flow to different S43 logics so as to generate different SQL statements;
and generating SQL sentences for establishing the stored procedures, and generating 4 SQL sentences through 4 logics, wherein the 4 SQL sentences are respectively used for establishing the stored procedures of 4 operations of adding, modifying, deleting and paging inquiry on the table.
Preferably, the generating the SQL sentence for creating the stored procedure specifically includes: when an SQL statement for establishing an 'increased storage process' is generated, a table name and a keyword form are used as new storage process names, when the storage process contents are organized, a first temporary table is traversed, column names and data types of the table are sequentially read, column names are added at the front after being read out and used as a new variable, the data types corresponding to the column are read and formatted and used as the data types of the variable, and then the column names, the variables and the like are spliced according to the format and the requirement of the 'increased storage process', so that the SQL statement for establishing the 'increased storage process' is formed; the SQL statement for establishing the modified storage process is also generated by establishing the SQL statement for increasing the storage process; when generating the SQL sentence for establishing the "delete storage process", the SQL sentence for establishing the "delete storage process" is formed by splicing character strings according to the format of the delete storage process when the stored process content is organized by taking the table name and the keyword of the table as the new storage process name; the SQL statement that creates the "paged query store procedure" is also created by creating the SQL statement that "delete store procedure".
A system for automatically generating SQL Server user storage process comprises an input module, a core module and an output module, wherein the input module, the core module and the output module are connected by data.
Preferably, the input module is configured to determine a database of automatically generated stored procedures, determine a table of automatically generated stored procedures, and determine a type of stored procedure generated.
Preferably, the core module is used for automatically generating SQL sentences required in the establishment of the storage process and establishing the storage process in the appointed database.
Preferably, the output module is used for providing downloading and uploading functions, checking and optimizing SQL sentences for establishing a stored procedure; and automatically calculating the change of the stored process data in the database before and after the stored process is generated, and outputting the name and the content of the newly added stored process.
Compared with the prior art, the invention has the beneficial effects that:
the invention provides a technical scheme for automatically generating a storage process of an SQL Server user by utilizing a database technology; the scheme has the characteristics of simplicity in operation, high accuracy, strong portability and the like, is beneficial to improving the development efficiency of the storage process and shortening the development period of an application system.
Drawings
Fig. 1 is a flow chart of the present invention.
FIG. 2 is a flow chart of the present invention for generating a build user stored procedure SQL statement.
Detailed Description
The invention will be further illustrated with reference to specific examples. It is to be understood that these examples are illustrative of the present invention and are not intended to limit the scope of the present invention. Further, it will be understood that various changes or modifications may be made by those skilled in the art after reading the teachings of the invention, and such equivalents are intended to fall within the scope of the invention as defined herein.
Example 1: method for automatically generating SQL Server user storage process
A method for automatically generating SQL Server user stored procedure as shown in FIG. 1 comprises the following steps:
s1, starting a system in the invention, and determining which database is automatically generated with a storage process in a checking mode;
s2, continuing to operate the system, inquiring SQL Server system table sysobjects by taking the database selected in the step S1 as an inquiry condition, acquiring all table objects of the database, formatting the result and displaying the result in the system;
s3, determining which tables are generated with storage processes in a checking mode according to the output result of the step S2 and combining the service requirement of the application system to be developed, and determining which types of storage processes are generated for each selected table in the checking mode;
s4, taking the user selection result in the step S3 as input, and generating an SQL sentence for establishing a user storage process through three steps;
s41 uses the loop to traverse the table selected by the user in step S3, determine the column information of each table and determine which of several stored procedures to generate for this table. During single circulation, firstly reading a table selected by a user (called table x for short), then querying an SQL Server system table COLUMNS by taking the table name as a condition, acquiring all column information of the table x, and storing the result into a first temporary table; simultaneously recording a user selection result, determining which storage processes are to be generated for the table x, and storing the result into a second temporary table;
s42, determining which storage processes are generated for the table x by traversing the second temporary table; the stored process type is used as a judging condition, and the branch sentences are utilized to guide the flow to different third-step logics to generate different SQL sentences;
the S43 is used for generating SQL sentences for establishing storage processes, and the method generates 4 SQL sentences through 4 logics and is respectively used for establishing the storage processes for 4 operations such as adding, modifying, deleting, paging inquiring and the like on a table.
When an SQL statement for establishing an 'added storage process' is generated, a table name + keyword form of a table x is used as a new storage process name; when the content of the storage process is organized, traversing the first temporary table, and sequentially reading the column names and the data types of the table x; reading the column name, adding @ before reading the column name to serve as a new variable, and simultaneously reading the data type corresponding to the column and formatting the data type to serve as the data type of the variable; to this end, the variables required to define the stored procedure and the Insert inito statement are all determined. Then, according to the format and the requirement of the 'adding storage process', the SQL statement for building the 'adding storage process' is formed after the contents such as column names, variables and the like are spliced. The SQL statement that builds the "modify stored procedure" is also generated in a similar manner.
When generating the SQL sentence for establishing the "delete stored procedure", the name of the new stored procedure is also used as the form of the table name and the key word of the table x; when the content of the storage process is organized, an SQL sentence for establishing a 'deletion storage process' is formed by splicing character strings according to the format of the deletion storage process. SQL statements that build a "paged query store" are also generated in a similar manner.
In order to improve accuracy and flexibility, the user can independently select and download the SQL statement generated in the step, and upload the SQL statement to the system after checking or optimizing.
S5, continuing to operate the system in the invention, executing the SQL sentence in the step S4, and automatically generating a storage process in the database selected in the step S1.
Thus, the user storage process required by the application system is automatically created.
Example 2:
a system for automatically generating SQL Server user stored procedures, comprising the following modules:
an input module: a determination is made as to which database to automatically generate the stored procedures and which tables to automatically generate which stored procedures.
The core module comprises: and automatically generating SQL sentences required by establishing the user storage process through three steps, and establishing the storage process in a specified database.
And an output module: the method has the advantages that the downloading and uploading functions are provided, so that a user can check and optimize SQL sentences for establishing a storage process conveniently; and automatically calculating the change of the stored process data in the database before and after the stored process is generated, and outputting the name and the content of the newly added stored process.

Claims (8)

1. A method for automatically generating a stored procedure of an SQL Server user, comprising the steps of:
s1, selecting a database for implementing an automatic generation and storage process;
s2, taking the database in the step S1 as a query condition, acquiring all table objects of the database, formatting and displaying the results;
s3, selecting a table for generating a storage process according to the output result in the step S2, and simultaneously selecting the type of the storage process to be established by the table;
s4, inputting the selected result in the step S3, and automatically generating an SQL sentence for establishing a user storage process;
s5, executing the SQL sentence in the step S4, and automatically generating a storage process in the database selected in the step S1.
2. The method for automatically generating the stored procedures of the SQL Server user according to claim 1, wherein the steps of obtaining all table objects of the database and formatting the results are shown as follows: querying SQL Server system table sysobjects, obtaining all table objects of the database, and displaying the result in the system after formatting.
3. The method for automatically generating the SQL Server user stored procedure according to claim 1, wherein the automatically generating the SQL sentence for establishing the user stored procedure specifically comprises the following steps:
s41, traversing the list selected by the user in the step S3 by using a loop, determining the column information of each list and determining the storage process category generated for the list, firstly reading the list selected by the user in single loop, then querying the SQL Server system list by taking the list name as a condition, acquiring all the column information of the list selected by the user, saving the result to the first temporary list, simultaneously recording the result selected by the user, determining the storage process category generated for the list selected by the user, and saving the result to the second temporary list;
s42, traversing a second temporary table, and determining a storage process type generated for the table selected by the user; and the stored process type is used as a judging condition, and the branch statement is utilized to guide the flow to different S43 logics so as to generate different SQL statements;
s43, generating SQL sentences for establishing the stored procedures, and generating 4 SQL sentences through 4 kinds of logic, wherein the SQL sentences are respectively used for establishing the stored procedures for 4 operations of adding, modifying, deleting and paging inquiry on a table.
4. The method for automatically generating SQL Server user stored procedures according to claim 3, wherein the generating SQL statement for creating stored procedures is specifically: when an SQL statement for establishing an 'increased storage process' is generated, a table name and a keyword form are used as new storage process names, when the storage process contents are organized, a first temporary table is traversed, column names and data types of the table are sequentially read, column names are added at the front after being read out and used as a new variable, the data types corresponding to the column are read and formatted and used as the data types of the variable, and then the column names, the variables and the like are spliced according to the format and the requirement of the 'increased storage process', so that the SQL statement for establishing the 'increased storage process' is formed; the SQL statement for establishing the modified storage process is also generated by establishing the SQL statement for increasing the storage process; when generating the SQL sentence for establishing the "delete storage process", the SQL sentence for establishing the "delete storage process" is formed by splicing character strings according to the format of the delete storage process when the stored process content is organized by taking the table name and the keyword of the table as the new storage process name; the SQL statement that creates the "paged query store procedure" is also created by creating the SQL statement that "delete store procedure".
5. The system for automatically generating the SQL Server user storage process is characterized by comprising an input module, a core module and an output module, wherein the input module, the core module and the output module are connected by data.
6. The system for automatically generating SQL Server user stored procedures according to claim 7, wherein the input module is configured to determine a database of automatically generated stored procedures, determine a table of automatically generated stored procedures, and determine a type of stored procedure generated.
7. The system for automatically generating SQL Server user stored procedures according to claim 7, wherein the core module is configured to automatically generate SQL statements required to build the stored procedures and build the stored procedures in a specified database.
8. The system for automatically generating SQL Server user stored procedures according to claim 7, wherein the output module is used for providing download and upload functions, checking SQL statements optimized for creating stored procedures; and automatically calculating the change of the stored process data in the database before and after the stored process is generated, and outputting the name and the content of the newly added stored process.
CN202310230624.1A 2023-03-07 2023-03-07 Method and system for automatically generating SQL Server user storage process Pending CN116340339A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202310230624.1A CN116340339A (en) 2023-03-07 2023-03-07 Method and system for automatically generating SQL Server user storage process

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202310230624.1A CN116340339A (en) 2023-03-07 2023-03-07 Method and system for automatically generating SQL Server user storage process

Publications (1)

Publication Number Publication Date
CN116340339A true CN116340339A (en) 2023-06-27

Family

ID=86887041

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202310230624.1A Pending CN116340339A (en) 2023-03-07 2023-03-07 Method and system for automatically generating SQL Server user storage process

Country Status (1)

Country Link
CN (1) CN116340339A (en)

Similar Documents

Publication Publication Date Title
CN107798123B (en) Knowledge base and establishing, modifying and intelligent question and answer methods, devices and equipment thereof
CN116028653B (en) Method and system for constructing map by visually configuring multi-source heterogeneous data
CN113407514A (en) Method, device and equipment for migrating database and readable medium
CN113467785A (en) SQL translation method and system for mimicry database
CN110309214A (en) A kind of instruction executing method and its equipment, storage medium, server
CN116340339A (en) Method and system for automatically generating SQL Server user storage process
CN108090034B (en) Cluster-based uniform document code coding generation method and system
CN108388589B (en) Device for automatically generating sql query statement of database
CN112783927B (en) Database query method and system
CN114186554A (en) Script data processing method and device, computer equipment and storage medium
CN114676155A (en) Code prompt information determining method, data set determining method and electronic equipment
CN113220800B (en) ANTLR-based data field blood-edge analysis method and device
CN112131432A (en) Method and device for acquiring command line multi-way tree of data network equipment
CN117290377A (en) Method and device for converting SQL sentences among relational databases
US20240160647A1 (en) Automatic error recovery for parser
CN117725190B (en) Multi-round question-answering method, system, terminal and storage medium based on large language model
CN113448969B (en) Data processing method, device and storage medium
CN111414330B (en) Data editing method and system, data processing device and storage medium
CN111144136B (en) Data conversion method, system, computer device and readable storage medium
CN118210809A (en) Object definition method, system, equipment and medium based on ER information
CN117668024A (en) SQL statement analysis-based data processing and synchronizing method and system
CN117992054A (en) File processing method and device
CN118152427A (en) SQL analysis method, system, terminal and medium based on large model
CN113688613A (en) Method, device and storage medium for generating field annotation and understanding character string
CN118092897A (en) Low code generation method and device for database DML tool

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination