dbms和sql_DBMS中的动态SQL-CSDN博客

dbms和sql

Here you will learn about dynamic sql in dbms.

在这里，您将了解dbms中的动态sql。

什么是动态SQL？ (What is Dynamic SQL?)

When the pattern of database access is known in advance then static SQL is very adequate to serve us. Sometimes, in many applications we may not know the pattern of database access in advance. For example, a report writer must be able to decide at run time that which SQL statements will be needed to access the database. Such a need can’t be fulfilled with static SQL and requires an advanced form of static SQL known as dynamic SQL.

如果事先知道数据库访问模式，那么静态SQL就足以为我们服务。有时，在许多应用程序中，我们可能事先不知道数据库访问的模式。例如，报表编写者必须能够在运行时决定访问数据库需要哪些SQL语句。静态SQL无法满足这种需求，需要静态SQL的高级形式称为动态SQL。

Also Read: Embedded SQL (Static SQL) in DBMS

另请参阅： DBMS中的嵌入式SQL(静态SQL)

There are several limitations in static SQL. Although using the host variables (host variables allows us to input values for search condition at run time), we can achieve a little bit dynamicness, for e.g.,

静态SQL有一些限制。尽管使用主机变量(主机变量允许我们在运行时输入搜索条件的值)，但我们可以实现一点点动态性，例如，

exec sql select tname, sex from teacher where salary > :sal;

exec sql选择tname，老师的性别，其中薪金>：sal;

Here the salary will be asked on run time. But getting column name or table asked at run time not possible with embedded SQL. For having such a feature we need dynamic SQL.

在这里，工资将在运行时询问。但是使用嵌入式SQL不可能在运行时获取列名或表要求。为了具有这样的功能，我们需要动态SQL。

动态SQL概念 (Dynamic SQL Concepts)

In dynamic SQL, the SQL statements are not hard coded in the programming language. The text of the SQL statement is asked at the run time to the user.
在动态SQL中，SQL语句不是用编程语言硬编码的。在运行时向用户询问SQL语句的文本。
In dynamic SQL, the SQL statements that are to be executed are not known until runtime, so DBMS can’t get prepared for executing the statements in advanced.
在动态SQL中，直到运行时才知道要执行SQL语句，因此DBMS无法为高级执行语句做好准备。
When the program is executed, the DBMS takes the text of SQL statements to execute the statements that are executed in such a manner called statement string. Once DBMS receives the text, it goes through a five steps execution as illustrated below.
执行程序时，DBMS将使用SQL语句的文本来执行以称为语句字符串的方式执行的语句。 DBMS收到文本后，将执行五个步骤，如下所示。

动态语句执行(立即执行) (Dynamic Statement Execution (Execute Immediate))

The Execute Immediate statement provides the simplest form of dynamic SQL. This statement passes the text of SQL statements to DBMS and asks the DBMS to execute the SQL statements immediately.

Execute Instant语句提供动态SQL的最简单形式。该语句将SQL语句的文本传递给DBMS，并要求DBMS立即执行SQL语句。

For using the statement our program goes through the following steps.

为了使用该语句，我们的程序执行以下步骤。

The program constructs a SQL statement as a string of text in one of its data areas (called a buffer).
该程序在其数据区之一(称为缓冲区)中将SQL语句构造为文本字符串。
The program passes the SQL statements to the DBMS with the EXECUTE IMMEDIATE statement.
程序使用EXECUTE IMMEDIATE语句将SQL语句传递给DBMS。
The DBMS executes the statement and sets the SQL CODE/SQL STATE values to flag the finishing status same like if the statement had been hard coded using static SQL.
DBMS执行该语句并设置SQL CODE / SQL STATE值以标记完成状态，就像该语句已使用静态SQL进行硬编码一样。