Question

Briefly discuss dynamic and embedded SQL. Cite an example of how either enhance database design and performance

Answer 1

Dynamic SQL

Dynamic SQL is a programming techniques that enables you to built SQL statements dynamically at runtime.
dynamic SQL allows you to write SQL that will then write and execute more SQL for you. This can be a great time saver because you can:

Automate repetitive tasks.
Write code that will work in any database or server.
Write code that dynamically adjusts itself to changing conditions
Most dynamic SQL is driven off the system tables, and all the examples I use here will use system tables, but if you have suitable data in user tables, you can write dynamic SQL from the contents of these tables, too.

Embedded SQL

Embedded SQL is a method of combining the computing power of a programing language and the database manipulation capabilities of SQL.

Executable SQL statements let you query, manipulate, and control Oracle data and create, define, and maintain Oracle objects such as tables, views, and indexes. This chapter focuses on the statements that query and manipulate data.

Static SQL provides performance advantages over dynamic SQL because static SQL is preprocessed, which means the statements are parsed, validated, and optimized only once.

If you are using a standards-based API, such as ODBC, to develop your application, static SQL is probably not an option for you. However, you can achieve a similar level of performance by using either statement pooling or stored procedures.
A stored procedure is a set of SQL statements (a subroutine) available to applications accessing a relational database system. Stored procedures are physically stored in the database. The SQL statements you define in a stored procedure are parsed, validated, and optimized only once, as with static SQL.

Stored procedures are database dependent because each relational database system implements stored procedures in a proprietary way. Therefore, if you want your application to be database independent, think twice before using stored procedures.