Dynamic SQL and Embedded SQL



SQL queries can be of two types namely Embedded or Static SQL and Dynamic SQL. So, in this blog, we will be learning about these two types of SQL statements.

Embedded / Static SQL

In an embedded or fixed SQL statement, the SQL statement has been modified and cannot be changed during operation. Statement is compiled during consolidation only. Because you have SQL statements with you, when you use this statement you know how to make statements, so you can improve your SQL query to do it as quickly and effectively as possible. This type of static SQL statement is often used on a similarly distributed website, where the data access method is defined in advance.
Encryption of these statements in the application is not an option, so if you want to build an application that requires flexible SQL statements, you should use Dynamic SQL. In System C, the embedded SQL statement is separated from the programming language statements by previewing it with EXEC SQL keywords so that the preprocessor (or precompiler) can distinguish embedded SQL statements from the source code of the host language.

 Dynamic SQL

 So if you want to build an application that requires flexible SQL statements where needed, use Dynamic SQL. SQL statements that are generously generated or used during operation are called flexible SQL statements. In some applications, users are able to create their own queries. These statements are compiled during operation. SQL statements of this type are used when the data on the site is different. Static SQL is robust and cannot be used for other applications that require flexibility.
In addition, since integration occurs during operation, the database will only be available during operation. Therefore, development and planning can not be done in advance. This will reduce system performance. Additionally, if a user is asked to enter a question during operation, then there is a chance that they will enter the wrong question, which is very dangerous as here you are dealing with a large amount of data.

 


When to Use Dynamic SQL

In those cases where static SQL can not provide the functionality you want, or if you are not sure what SQL statements need to be executed by PL / SQL processes, you should use flexible SQL. These SQL statements may be based on user input or may be based on system processing functionality.
It is possible to use flexible SQL to create applications that generate flexible queries, also known as queries whose full text may not be known until the time of operation. There are many types of applications that require flexible queries, including:

●Apps where users can specify search query terms during operation or include them in a query search
● Apps that allow users to install or select upgrade strategies during operation
● Any application that asks for a website with descriptive table descriptions that are constantly changing
● Asking frequent questions to create new tables on the website

 When to Use Static SQL

Some questions have no definite answers. "Should I use a static SQL or a dynamic SQL?" you are one of those questions. You can come to a decision quickly if you follow the sixth rule: If it can be done in static SQL, do it in static SQL.
Consider flexible SQL only if it cannot be done in static SQL.
For example
Static SQL provides integration time integration. Dynamic SQL does not.
Suppose we are writing a PL / SQL database containing buggy SQL (eg invalid column name in SELECT)

 With static SQL:

With static SQL, you read well during compilation that something is wrong with it. If the code refers to non-website items or access rights are missing, the moderator will provide that information immediately.

With dynamic SQL:


It is a dynamic SQL version of the same query, no error reported; process successfully integrated. Dynamic SQL has delayed error detection, which means you will get an unpleasant surprise if you run the process.

 

The examples above show that, at compile time, static SQL gives you a far stronger guarantee of the correctness of your code than dynamic SQL.

Key Differences

  1. Compared to flexible SQL statements, static SQL statements are faster and more efficient.
  2. Dynamic SQL statements are compiled during operation, and standard SQL statements are   compiled during compilation.
  3. Static SQL is used in a uniformly distributed format and Dynamic SQL is used in a uniformly distributed format.
  4. Flexible Situation The fixed SQL is less flexible on the other hand Dynamic SQL is more flexible.

·   

   

Conclusion

If you want to create a flexible application you can use flexible SQL, but make sure your users are professional and trained. If not, you should go to static or embedded SQL. This works very well compared to the powerful SQL.


    References

Do share this blog with your friends to spread the knowledge

Keep Learning :)

Group 50 TY-C - Vishwakarma Institute of technology

Aakanksha Najardhane

Swaminath Nalwar

Navneet Sharma

Samruddhi Parate

Thank you :)




Comments

Post a Comment