数据库基础概论__嵌入式SQL之动态SQL

最新推荐文章于 2024-05-22 15:19:03 发布

吴珝君

最新推荐文章于 2024-05-22 15:19:03 发布

阅读量763

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_39804483/article/details/84976127

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

(1)动态SQL的引入

--静态SQL：
SpecName = ‘张三’;
exec sql select Sno, Sname, Sclass into :vSno, :vSname, :vSclass from
Student where Sname= :SpecName ;
或
exec sql declare cur_student cursor for
select Sno, Sname, Sclass from Student where Sclass= :vClass
order by Sno for read only ; /*定义*/
exec sql open cur_student ; /*执行*/
静态SQL特点：SQL语句在程序中已经按要求写好，只需要把一些参数通过
变量(高级语言程序语句中不带冒号) 传送给嵌入式SQL语句即可(嵌入式SQL
语句中带冒号)

#include <stdio.h>
exec sql include sqlca;
exec sql begin declare section;
char user_name[ ] = “Scott”; char user_pwd[ ] =“tiger”;
char sqltext[ ] = “ delete from customers where cid = \‘c006\’ ” ;
exec sql end declare section;
int main()
{
exec sql whenever sqlerror goto report_error;
exec sql connect :user_name identified by :user_pwd;
exec sql execute immediate :sqltext;//动态SQL
exec sql commit release; return 0;
report_error: print_dberror(); exec sql rollback release; return 1;
}

/*动态SQL构造 ：SQL语句可以在程序中动态构造，形成一个字符串，如上例sqltext，然后再交给DBMS执行，
交给DBMS执行时仍旧可以传递变量。
*/

问：

静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。

动态 SQL 语句是在应用程序运行时被编译和执行的，例如，使用 DB2 的交互式工具 CLP 访问数据库时，用户输入的 SQL 语句是不确定的，因此 SQL 语句只能被动态地编译。动态 SQL 的应用较多，常见的 CLI 和 JDBC 应用程序都使用动态 SQL。

动态SQL的两种执行方式;

如果SQL语句已经被构造在host-variable字符串变量中，则：立即执行语句：运行时编译并执行
EXEC SQL EXECUTE IMMEDIATE :host-variable;
Prepare-Execute-Using语句:PREPARE语句先编译，编译后的SQL语句允许动态参数，EXECUTE语句
执行，用USING语句将动态参数值传送给编译好的SQL语句
EXEC SQL PREPARE sql_temp FROM :host-variable;
……
EXEC SQL EXECUTE sql_temp USING :cond-variable

#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;
exec sql begin declare section;
char cust_id[5], sqltext[256], user_name[20], user_pwd[10];
exec sql end declare section;
char cid_prompt[ ] = "Name customer cid to be deleted: ";
int main()
{
strcpy(sqltext, "delete from customers where cid = :dcid");
……
while (prompt(cid_prompt, 1, cust_id, 4) >= 0) {
exec sql whenever not found goto no_such_cid;
exec sql prepare delcust from :sqltext; /* prepare statement */
exec sql execute delcust using :cust_id; /* using clause ... replaces ":n" above */
exec sql commit work; continue;
no_such_cid: printf("No cust %s in table\n",cust_id);
continue;
}
…
}

（2）数据字典（又称为系统目录）

A）定义

是数据字典中的一些表或视图的集合，这些表或者属兔存储了数据库中各类对象的定义信息，这些对象包括Create语句定义的表、列、索引、视图、权限、约束等等，这些信息又称为数据库的元数据—关于数据的数据。

注：

不同的DBMS中术语不一样，数据字典、目录表、系统目录、系统视图

不同DBMS中系统目录存储方式可能不一样，但是一些信息会对DBA公开，这些公开信息，可以通过SQL命令检索出来。

B）数据字典的内容构成

数据字典通常存储的事数据库和表的元数据，即模式本身的信息。

1与关系相关的信息

2.关系名字每个关系的属性名及其类型视图的名字及其定义完整性约束

3.用户与账户信息与密码

4.统计和描述数据如每个关系中元组的数目

5.物理文件组织形式(关系是如何存储的、关系的物理位置)

6.索引相关信息

注：数据字典的结构：也是存储在硬盘上的关系专为内存高效访问设计而特定的数据结构

C)可能的数据字典的表结构或视图

Relation_metadata = (relation_name, number_of_attributes,

storage_organization, location)//关系元数据

Attribute_metadata = (attribute_name, relation_name, domain_type,

position, length)//属性元数据

User_metadata = (user_name, encrypted_password, group)//用户元数据

Index_metadata = (index_name, relation_name, index_type,

index_attributes)//索引元数据

View_metadata = (view_name, definition)//视图元数据

示例：

X/Open标准中有一个目录表Info_Schem.Tables, 该表中的一行是一个已经定义的表的有关信息。

可以使用SQL语句来访问这个表中的信息，比如了解已经定义了哪些表，可如下进行：

Select Table_Name From Tables;

模式的含义是指某一用户所设计和使用的表、索引及其他与数据库有关的对象的集合，因此表的完整名应是：模式名.表名。这样做可允许不同用户使用相同的表名，而不混淆。

一般而言，一个用户有一个模式。可以使用Create Schema语句来创建模式(用法略，参见相关文献)，

在Create Table等语句可以使用所定义的模式名称。

示例：ORACLE的数据字典

Oracle数据字典由视图组成，分为三种不同形式，由不同的前缀标识

USER_ :用户视图，用户所拥有的对象，在用户模式中

ALL_ :扩展的用户视图，用户可访问的对象

DBA_ :DBA视图(所有用户都可访问的DBA对象的子集)

Oracle数据字典中定义了三个视图USER_Tables,ALL_Tables,和DBA_Tables供DBA和用户使用数据字典中关于表的信息同样,Oracle数据字典中也定义了三个视图USER_TAB_Columns.ALL_TAB_Columns(Accessible_Columns),DBA_TAB_Columns供DBA和用户使用数据字典中关于表的列的信息

可以使用SQL语句来访问这些表中的信息：

Select Column_Name From ALL_TAB_Columns

Where Table_Name = ‘STUDENT’ ;

Oracle数据字典中还定义了其他视图

TABLE_PRIVILEDGE(或ALL_TAB_GRANTS)

COLUMN_PRIVILEDGE(或ALL_COL_GRANTS)

可访问表的权限，列的权限

CONSTRAINT_DEFS(或ALL_CONSTRAINTS)

可访问表的各种约束、还有其他视图……

可以使用下述命令获取Oracle定义的所有视图信息

Select view_name from all_views where owner = ‘SYS’ and

view_name like ‘ALL_%’ or view_name like ‘USER_%’;

注：如果用户使用Oracle,可使用其提供的SQL*PLUS进行交互式访问。

（3）数据字典与SQLDA

针对一组表，编写由用户确定检索表和检索条件的查询程序。只知道表的集合，但具体检索哪个Table，以及检索该Table中的哪些列都是可构造的，检索条件也是用户临时设置的，可构造的（换句话说，动态SQL的检索条件、表列都需要构造）。

要构造这样复杂的动态SQL需要了解数据字典和SQLDA 以获得关系模式信息。

SQLDA:SQL descriptor Area;

SQLDA是同一个内存数据结构、内部装在关系模式的定义信息，如列的数目、每一个列的名字和类型等等。

（4）ODBC（Open DataBase Connection）

是一种标准—不同语言的应用程序与不同数据服务器之间通讯的标准。提供一组API,支持应用程序与数据库服务器的交互。

提供一组API,支持应用程序与数据库服务器的交互

应用程序通过调用ODBCAPI 实现与数据库服务器的连接，向SQL数据库服务器发送SQL指令、一条一条提取数据库检索见过中的元组传递给应用程序。

具体的DBMS提供一套驱动程序，即DRIVER函数，提供ODBC条用，以便实现数据库与应用程序的连接。

应用程序如何通过ODBC连接一个数据库服务器

(1)ODBC应用前，需要确认具体DBMS Driver被安装到ODBC环境中

(2)当应用程序调用ODBC API的时候，ODBC API 会调用具体的DBMS DIRVER库函数，DBMS库函数和数据库服务器通讯，执行相应的请求并返回检索结果。

(3)ODBC应用程序首先需要分配一个SQL环境，再产生一个数据库连接句柄。

(4)应用程序使用SQLconnect() 打开一个数据库连接。

(5)应用程序通过SQLexecDirect()向数据库发送SQL命令

(6)使用SQLfetch()获取产生的结果元组。

(7)使用SQLBindCol()绑定C语言变量与结果中的属性

(8)当获取一个元组时，属性值会自动地传送到相应的C语言变量中.

char branchname[80]; float balance;
int lenOut1, lenOut2;
HSTMT stmt;
SQLAllocStmt(conn, &stmt);
//分配一个与指定数据库连接的新的语句句柄
char * sqlquery = "select branch_name, sum (balance)
from account
group by branch_name";
error = SQLExecDirect(stmt, sqlquery, SQL_NTS);
//执行查询，stmt句柄指向结果集合
if (error == SQL_SUCCESS) {
SQLBindCol(stmt, 1, SQL_C_CHAR, branchname , 80, &lenOut1);
SQLBindCol(stmt, 2, SQL_C_FLOAT, &balance, 0 , &lenOut2);
//绑定高级语言变量与stmt句柄中的属性
while (SQLFetch(stmt) >= SQL_SUCCESS) {
//提取一条记录，结果数据被存入高级语言变量中
printf (" %s %g\n", branchname, balance);
}
}
}
SQLFreeStmt(stmt, SQL_DROP);

(5)JDBC

JDBC：Java DataBase Connection

JDBC是一组Java版的应用程序接口API，提供了Java应用程序与数据库服务器的连接和通讯能力。

JDBC API 分成两个程序包：

Java.sql 核心API --J2SE(Java2标准版)的一部分。使用java.sql.DriverManager

类、java.sql.Driver和java.sql.Connection接口连接到数据库

Javax.sql 可选扩展API--J2EE(Java2企业版)的一部分。包含了基于JNDI(Java

Naming and Directory Interface, Java命名和目录接口)的资源，以及管理连接池、分布式事务等，使用DataSource接口连接到数据库。

java.sql.Statement——对特定的数据库执行SQL语句

java.sql.PreparedStatement —— 用于执行预编译的SQL语句

java.sql.CallableStatement ——用于执行对数据库内嵌过程的调用。

java.sql.ResultSet——从当前执行的SQL语句中返回结果数据。

JDBC工作过程

打开一个连接；创建“Statement”对象，并设置查询语句；使用Statement对象执行

查询，发送查询给数据库服务器和返回结果给应用程序；处理错误的例外机制

具体实施过程

传递一个Driver给DriverManager，加载数据库驱动。

Class.forName()通过URL得到一个Connection对象, 建立数据库连接

DriverManager.getConnection(sDBUrl)

DriverManager.getConnection(sDBUrl,sDBUserID,sDBPassword)接着创建一个Statement对象(PreparedStatement或CallableStatement)，用来查询或者修改数据库。

Statement stmt=con.createStatement()查询返回一个ResultSet。

ResultSet rs=stmt.executeQuery(sSQL)

public static void JDBCexample(String dbid, String userid, String passwd)
{ 
try { //错误捕获
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@db.yale.edu:1521:univdb", userid, passwd);
//加载数据库驱动，建立数据库连接
Statement stmt = conn.createStatement();
//创建一个语句对象
…Do Actual Work ….
//进行SQL语句的执行与处理工作
stmt.close();
conn.close();
//关闭语句对象，关闭连接
} catch (SQLException sqle) {
System.out.println("SQLException : " + sqle); }
}
Update to database
try { stmt.executeUpdate( "insert into instructor values
(‘77987', ‘Kim', ‘Physics’,98000)");
//插入一条记录
} catch (SQLException sqle) {
System.out.println("Could not insert tuple. " + sqle); }
Execute query and fetch and print results
ResultSet rset = stmt.executeQuery( "select dept_name, avg(salary)”+
“ from instructor group by dept_name“ );
while ( rset.next() ) {
System.out.println(rset.getString(“dept_name") + " "
+ rset.getFloat(2)); }
}

public static void JDBCexample(String dbid, String userid, String passwd)
{
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:
@db.yale.edu:1521:univdb", userid, passwd);
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate( "insert into instructor values
(‘77987', ‘Kim', ‘Physics’,98000)");
} catch (SQLException sqle) {
System.out.println("Could not insert tuple. " + sqle);
}
ResultSet rset = stmt.executeQuery( "select dept_name, avg(salary)”+
“ from instructor group by dept_name");
while ( rset.next() ) {
System.out.println(rset.getString(“dept_name") + " " + rset.getFloat(2)); }
stmt.close();
conn.close();
} catch (SQLException sqle) {
System.out.println("SQLException : " + sqle);
}

（6）嵌入式SQL 与ODBC与JDBC的工作过程

SQL

ODBC

JDBC

基于ODBC/JDBC的数据库访问与直接访问数据库的比较

吴珝君

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库基础概论__嵌入式SQL之动态SQL

(1)动态SQL的引入--静态SQL：SpecName = ‘张三’;exec sql select Sno, Sname, Sclass into :vSno, :vSname, :vSclass fromStudent where Sname= :SpecName ;或exec sql declare cur_student cursor forselect Sno,...
复制链接

扫一扫

专栏目录