第6章数据库应用编程

掌握ODBC数据库连接的工作原理及配置方式。
掌握JDBC数据库连接的工作原理及程序结构。
掌握数据库存储过程、触发器、游标的程序结构。
掌握数据库数据Web呈现技术及MyBatis数据访问技术。

6.1 数据库连接技术

ODBC的技术背景

一般情况下，通常选用特定的DBMS管理系统；
网络环境下，开发应用时通常需要对多种数据库的实现互连和资源共享；
如果从一种DBMS改变到另一种DBMS，就意味着要重写应用程序；
而开发对应的多种应用，在不同的DBMS上运行的应用程序，显然不可取
Microsoft推出了开放式数据库互连技术（Open DataBase Connectivity，简写为 ODBC）。
——ODBC实现了应用程序对多种不同DBMS的数据库的访问
——ODBC定义了一套基于SQL的、公共的、与数据库无关的API（应用程序设计接口）；
——使每个应用程序利用相同的源代码就可访问不同的数据库系统，存取多个数据库中的数据；

ODBC的层次结构

客户端数据库应用程序：
- 请求与数据源的连接和会话
- 向数据源发送SQL请求
ODBC应用程序接口：是一种使用ODBC技术实现应用程序与数据库互连的标准接口
ODBC驱动程序管理器：用于管理系统中的各种驱动程序。
驱动程序
- 是一个用于支持 ODBC函数调用的模块，通常是一个动态链接库DLL。
SQL Server DBMS：用户管理数据库的管理软件
SQL Server DB：① 用户创建的数据库； ② 用于存储用户数据、对象。

应用程序使用ODBC访问数据库的步骤

首先必须用ODBC管理器注册一个数据源；
管理器根据数据源提供的数据库位置、数据库类型及ODBC驱动程序等信息，建立起ODBC与数据连接
应用程序只需将数据源名提供给ODBC，ODBC就能建立起与相应数据库的连接；
这样，应用程序就可以通过驱动程序管理器与数据库交换信息；
驱动程序管理器负责将应用程序对ODBC API的调用传递给正确的驱动程序；
驱动程序在执行完相应的SQL操作后，将结果通过驱动程序管理器返回给应用程序

使用ODBC管理器配置数据源

首先必须在数据库厂商的官网下载与你的数据库对应的驱动程序；

例如：名字为psqlodbc_x64.msi的驱动程序支持PostgreSQL 64位数据库，安装它。

JDBC编程

JDBC（Java DataBase Connectivity，Java数据库连接）技术的简称，是一种用于执行SQL语句的Java API。

这个API由java.sql.*包中的一些类和接口组成，
注意：使用JDBC访问数据库需要相应数据库的JDBC驱动程序。

JDBC工作原理

JDBC程序访问数据库步骤

应用程序：Eclipse

（1）加载驱动

PostgreSQL数据库驱动程序加载语句

Class.forName("org.postgresql.Driver")；

（2）建立连接

与PostgreSQL数据库建立连接：

String URL = "jdbc:postgresql://localhost:5432/testdb";
String userName = "myuser";
String passWord = "sa";
connection conn = DriverManager.getConnection(URL,userName,passWord);//调用方法，建立连接

（3）创建Statement对象

Statement stmt = conn.createStatement();

（4）执行SQL语句

主要掌握两种执行SQL语句的方法

executeQuery()：可执行指定的静态select语句

返回结果集
例如向staff表中插入一条数据的代码即向数据库发送SQL命令，代码示例如下。

Statement stmt = conn.createStatement() ;
String sql = "INSERT INTO public.Student (sid, sname, sex, classid)" + " VALUES ('2017001003', ’张山’, ’男’, 'C2017001')";
stmt.executeUpdate(sql);

executeUpdate() 可用来更新数据，包括插入和删除等操作；

返回值是一个整数，即不同步更新记录的条数。（update、insert、delete语句）。

Statement stmt = conn.createStatement();
String sql = "INSERT INTO public.student (sid, sname, gender, birthday, major, phone)"  +  " VALUES ('2017001', '张山', '男', '1998-10-10','软件工程','13602810001')";
stmt.executeUpdate(sql);

（5）ResultSet保存结果集

调用Statement对象的executeQuery()方法，会得到ResultSet 对象。

ResultSet 对象具有可以指向当前数据行的指针。

用ResultSet.next()方法，可以使指针指向下一行。如果next()方法返回null，则表示下一行中没有数据存在。

并且它通过一套get()方法提供了对查询结果的数据访问。（这些 get()方法可以访问当前行中的不同列）

Statement stmt = conn.createStatement();
String sql = "SELECT id, name, age FROM company";
ResultSet rs = stmt.executeQuery(sql);

（6）关闭连接

作为一种好的编程风格，不需要对象时，应该显式地关闭它们。

rs.close(); //关闭结果集对象
stmt.close(); //关闭执行对象
conn.close(); //关闭连接对象

在Eclipse下使用JDBC连接数据库

在Eclipse环境中创建应用项目，然后在项目中配置JDBC驱动程序包

在项目中创建Java类，并按照下列步骤在程序中访问数据库。

官网下载JDBC驱动程序包

在Eclipse中创建工程

在PSQL工程中创建jdbclib目录，并且将这个下载的程序包拷贝到这个目录

eclipse如何导入MySQL的JDBC驱动jar包？

eclipse如何导入MySQL的JDBC驱动jar包-百度经验 (baidu.com)

Java使用JDBC连接数据库的例子

package test1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Class1 {
	public static void main( String args[] ){
        Connection conn = null;
        Statement stmt = null;
        String URL = "jdbc:postgresql://localhost:5432/CAR";  //这里的CAR是首先自己要创建的数据库名，必须与数据库里已有的那个大小写一致
        String userName = "postgres";	//必须用posrgres
        String password = "root";
        
    try {
    	Class.forName( "org.postgresql.Driver" );
    	conn = DriverManager. getConnection(URL , userName, password );
    	System.out.println( "opened database successfully");  	
  	  	stmt = (Statement) conn.createStatement();
/****************************更改这个car为对应的数据库名************************/
   		String sql = "select * from car" ;    //查询数据的sql语句
   		stmt = (Statement) conn.createStatement();
    	ResultSet rs = stmt.executeQuery(sql); 
    	System.out.println("最后的查询结果为: ");
    	while (rs.next()) {	 //判断是否还有下一个数据
/****************************更改要打印的字符，为对应的数据库的列************************/  
   			//输出查到的记录的各个字段的值
    		System.out.println(rs.getString("caid") +" " +rs.getString( "caplate") +" "
    + rs.getString( "cacolor") +" " + rs.getString( "castate") );
		}
		stmt.close();
		conn.close();
		}catch ( Exception e ) {
		System.err.println( e.getClass().getName()+": "+ e.getMessage() );
		System.exit(0);}
		}
}

当出现bug：用户？？？密码？？？　说明数据库打不开，可能是因为大小写不一致

使用JDBC连接PostgreSQL数据库并完成一些操作

完成Student表的创建和删除，插入数据、查询数据和删除数据等操作。

package test1;
import java.sql.*; //加载Java包java.sql.*

public class Class1 {
    // 创建静态全局变量
    static Connection conn;
    static Statement st;
    public static void main(String[] args) {
    createtable(); //创建数据表
    insert(); //插入记录
    query(); //查询记录并显示
    update(); //更新记录数据
    query(); //查询记录并显示
    delete(); //删除记录
    droptable(); //删除数据表
}

/* 创建学生表Student*/
    public static void createtable() {
        conn = getConnection(); // 首先要获取连接，即连接到数据库
        try {
            String sql = "CREATE TABLE Student" +
            "( sid character(13) NOT NULL, " +
            "sname character varying(30) NOT NULL, " +
            "sex character（2）NOT NULL, " +
            "classid character(11), "+
            "CONSTRAINT sid_pkey PRIMARY KEY (sid), "+
            "CONSTRAINT gCheck CHECK (sex = ANY (ARRAY[’男’, ’女’])))";
            st = (Statement) conn.createStatement();// 创建Statement对象
            st.executeUpdate(sql); 					// 执行创建表的操作
            System.out.println("成功创建Student表 ");
            conn.close(); //关闭数据库连接
        } catch (SQLException e) {
        System.out.println("创建表操作失败" + e.getMessage());
        }
    }
    
/* 删除学生表Student*/
    public static void droptable() {
        conn = getConnection(); // 首先要获取连接，即连接到数据库
        try {
        String sql = "DROP TABLE Student";
        st = (Statement) conn.createStatement(); // 创建Statement对象
        st.executeUpdate(sql); // 执行删除表的操作
        System.out.println("成功删除Student表 ");
        conn.close(); //关闭数据库连接
        } catch (SQLException e) {
        System.out.println("删除操作失败" + e.getMessage());
        }
	}
	
/* 插入数据记录，并输出插入的数据记录数*/
    public static void insert() {
        conn = getConnection(); // 首先要获取连接，即连接到数据库
        try {
        String sql="INSERT INTO public.Student(sid,sname,sex,classid)"+ " VALUES ('2017001003',’张山’, ’男’, C2017001')";
        st = (Statement) conn.createStatement();
        //创建执行SQL的Statement对象
        int count = st.executeUpdate(sql); // 执行插入的SQL语句并返回插入条数
        System.out.println("向Student表中插入 " + count + " 条数据");
        conn.close(); //关闭数据库连接
        } catch (SQLException e) {
        System.out.println("插入数据失败" + e.getMessage());
        }
    }
    
/* 更新符合要求的记录，并返回更新的记录数目*/
    public static void update() {
        conn = getConnection(); //同样要获取连接，即连接到数据库
        try {
        String sql = "update Student set sid='2017001012 where sname = ’张山’";
        st = (Statement) conn.createStatement();
        int count = st.executeUpdate(sql);// 执行更新的SQL语句，返回更新记录数
        System.out.println("Student表中更新 " + count + " 条数据");
        conn.close(); //关闭数据库连接
        } catch (SQLException e) {
        System.out.println("更新数据失败");
        }
    }
    
/* 查询数据库，输出符合要求的记录的情况*/
    public static void query() {
        conn = getConnection(); //同样要获取连接，即连接到数据库
        try {
            String sql = "select * from Student"; // 查询数据的SQL语句
            st = (Statement) conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            System.out.println("最后的查询结果：");
            while (rs.next()) 
            	{ // 判断是否还有下一个数据
            // 根据字段名获取相应的值
            String vsid = rs.getString("sid");
            String vname = rs.getString("sname");
            String vsex = rs.getString("sex");
            String vclassid = rs.getString("classid");
            //输出查到的记录的各个字段的值
            System.out.println(vsid+" "+vname + " " +vsex + " " +vclassid);
           		}
            conn.close(); //关闭数据库连接
       		} catch (SQLException e) {
        System.out.println("查询数据失败");
        }
    }
/* 删除符合要求的记录，输出情况*/
    public static void delete() {
        conn = getConnection(); //同样要获取连接，即连接到数据库
        try {
        String sql = "delete from Student where sname = ’张山’";
        st = (Statement) conn.createStatement();
        int count = st.executeUpdate(sql);
        // 执行SQL删除语句，返回删除数据的数量
        System.out.println("Student表中删除 " + count + " 条数据\n");
        conn.close(); //关闭数据库连接
        } catch (SQLException e) {
        System.out.println("删除数据失败");
        }
    }
/* 获取数据库连接的函数*/
    public static Connection getConnection() {
        Connection con = null; //创建用于连接数据库的Connection对象
        String URL = "jdbc:postgresql://localhost:5432/testdb";
        String userName = "root";
        String passWord = "root";
        try {
        Class.forName("org.postgresql.Driver");// 加载PostgreSQL数据驱动
        con = DriverManager.getConnection(URL, userName, passWord);
        // 创建与数据库连接
        } catch (Exception e) {
        System.out.println("数据库连接失败" + e.getMessage());
        }
        return con; //返回所建立的数据库连接
    }
}

6.2 Java Web数据库编程

Java web简介
• Java web开发运行环境
• Servlet技术
• JSP
• JavaBean
• MyBatis

一、 WEB数据库系统技术

以浏览器/服务器（B/S）模式为平台，其运行模式：

浏览器 —— Web服务器 —— 数据库

Java web

Java 的 Web 框架虽然各不相同，但遵循特定的 J2EE 规范：使用 Servlet 或者 Filter 拦截请求，使用 MVC 的思想设计架构，使用XML 或 Annotation 实现配置，运用 Java 面向对象的特点，面向对象实现请求和响应的流程

二、Java web开发运行环境

Web 服务器主要用来接收客户端发送的请求和响应客户端请求。

JavaWeb 应用程序需要Servlet 容器，容器的主要作用就是调用 Java 程序处理用户发送的请求，并响应指定的资源。

开发 JavaWeb 项目，Web 服务器和 Servlet 容器是必需的，两者通常是合在一起的

数据库应用程序结构三层架构（3-tier Architecture）

所谓三层体系架构是在客户端与数据库之间加入了一个中间件层，也叫组件层。三层是指逻辑上的三层。三层体系的应用程序将业务规则、数据访问、合法性校验等工作放到了中间层进行处理。通常来说，客户端不与数据库直接进行交互，而是与中间层建立连接，再经由中间层与数据库进行交互。

表现层（UI）：通俗讲就是展现给用户的界面，即用户在使用一个系统的时候他的所见所得。
业务逻辑层（BLL）：针对具体问题的操作，也可以说是对数据层的操作，对数据业务逻辑处理。
数据访问层（DAL）：该层所做事务直接操作数据库，针对数据的增加、删除、修改、查找等

数据库应用程序结构 MVC

三、 Servlet技术

Servlet 是运行在服务器端的程序

• Servlet 被 Web 服务器加载和执行

• Servlet 从客户端（通过 Web 服务器）接收请求，执行某种操作，然后返回结果。

Servlet的工作流程

四、JSP技术

JSP（JavaServer Pages）是在普通HTML中嵌入了Java程序片段

JSP网页Java程序片段可以操纵数据库、重新定向网页以及发送E-mail等，实现建立动态网站所需要的功能。

所有程序操作都在服务器端执行，网络上传送给客户端的仅是得到的结果，这样大大降低了对客户浏览器的要求，

即使客户浏览器端不支持Java，也可以访问JSP网页

五、JavaBean技术

HTML 代码与Java 代码相分离，而使用Java代码封装的类，就是一个JavaBean组件

·在JavaWeb 开发中，可以使用JavaBean组件来完成

六、MyBatis访问数据库

6.3 存储过程编程

什么是存储过程

就是C语言的函数！

创建的语法

CREATE [ OR REPLACE ] FUNCTION name() 
RETURNS XXX
AS $XXX$ 	//$$声明实际的开始
DECLARE
-- 声明段
BEGIN
--函数体语句
END;
$XXX$ LANGUAGE lang_name; //$$ 表明代码的结束, LANGUAGE后面指明所用的编程语言

参数解释：

OR REPLACE ：覆盖同名的存储过程；
RETURNS：返回值；
RETURNS TABLE：返回二维表

创建示例

创建一个名为countRecords()的存储过程，用于统计STUDENT表的记录数。

CREATE OR REPLACE FUNCTION countRecords() 
RETURNS integer 
AS $count$ 
declare 
	count integer; 
BEGIN
	SELECT count(*) into count FROM STUDENT; 
	RETURN count; 
END;
$count$ LANGUAGE plpgsql;

编写存储过程统计数据库原理（ CID='1288264844'）及应用不及格人数，代码如下：

create or replace function count_1()
returns integer as $count$
declare
    count integer;
begin
	select count(*) into count from GRADE
	where Score<60 and CID='1288264844';
	return count;
end
$count$ language plpgsql;

执行存储过程

语法形式如下：

select 存储过程名（参数）；
例如： select countRecords ();

或者： select * from 存储过程名（参数）；
又例如： select * from countRecords ( );

用一个存储过程调用其它存储过程，语法形式如下：

select into 自定义变量 from 存储过程名（参数）；
CREATE OR REPLACE FUNCTION testExec() 
returns integer AS $$ 
declare
    rec integer; 
BEGIN
    select into rec countRecords(); 
    //如果不关心countRecords()的返回值，则可用 PERFORM countRecords() 代替; 
    return rec; 
END;
$$ LANGUAGE plpgsql;

删除存储过程

参数：

CASCADE：级联删除依赖于存储过程的对象(如触发器)。
RESTRICT：如果有任何依赖对象存在，则拒绝删除该函数；这个是缺省值。

例子：假如需要删除前面定义的存储过程 testExec()

DROP FUNCTION IF EXISTS testExec()

PL/SQL基本语法

1、声明局部变量。

变量声明的语法如下：

declare
	变量名 变量类型；

例如：
declare
    count intger；
    rec RECORD ；//注：声明变量为记录类型,RECORD不是真正的数据类型，只是一个占位符。

2、条件语句

在PL/pgSQL中有以下三种形式的条件语句，与其他高级语言的条件语句意义相同。

1). IF-THEN 
IF boolean-expression THEN
	statements
END IF;

2). IF-THEN-ELSE
IF boolean-expression THEN
	statements
ELSE

3). IF-THEN-ELSIF-ELSE
IF boolean-expression THEN
	statements
ELSIF boolean-expression THEN
	statements
ELSIF boolean-expression THEN
	statements
ELSE
	statements
END IF;

3、循环语句

**LOOP 语句（EXIT CONTINUE） ** 例如：

LOOP
    count=count+1;
    EXIT WHEN count >100;
END LOOP;

WHILE例

WHILE amount_owed > 0 AND balance > 0 LOOP
    do something
END LOOP;

FOR 例

FOR i IN 1...10 LOOP
	RAISE NOTICE 'i IS %', i;
END LOOP;
	FOR i IN REVERSE 10...1 LOOP
    do something
END LOOP;

4、遍历命令结果

FOR record_or_row IN query LOOP
	statements
END LOOP ;

FOR循环可以遍历命令的结果并操作相应的数据，例如：
declare
	rec RECORD ；
FOR rec IN SELECT sid , sname FROM student LOOP
	raise notice ‘%-,%-’,rec.sid, rec.sname;
END LOOP;

存储过程的优缺点

1、使用存储过程的优点

(1) 减少网络通信量
(2) 执行速度更快
(3) 更强的适应性
(4) 降低了业务实现与应用程序的耦合
(5) 降低了开发的复杂性
(6) 保护数据库元信息
(7) 增强了数据库的安全性

2、使用存储过程的缺点

(1) SQL本身是一种结构化查询语言，而存储过程本质上是过程化的程序；
- 面对复杂的业务逻辑，过程化处理逻辑相对比较复杂；
- 而SQL语言的优势是面向数据查询而非业务逻辑的处理。
(2) 更新存储过程的参数代码麻烦
(3) 开发调试复杂
(4) 可移植性差

6.4 触发器编程

掌握postgreSQL触发器创建、修改、删除的方法

一、触发器的基本概念

触发器是一种存储过程（函数），可由某操作触发自动执行。

触发器本身是一个特殊的事务单位。

触发器的特点

与表相关联：必须定义在表或视图上。
自动触发：由执行INSERT、DELETE、UPDATE操作时触发
不能直接调用，也不能传递或接受参数
是事务的一部分：触发器和触发语句作为可在触发器内回滚的单个事务。

触发器的分类

按触发的语句分为：

INSERT触发器
DELETE触发器
UPDATE触发器

即用户一旦在应用中执行了这三种动作，数据库的触发器会有相应的反应。

触发器执行的次数可分：

语句级触发器：
- 由关键字FOR EACH STATEMENT声明
- 在触发器作用的表上执行一条SQL语句时，该触发器只执行一次
行级触发器：
- 由关键字FOR EACH ROW标记的触发器
- 当触发器作用的表的数据发生变化时，每变化一行就会执行一次触发器。
- 例如，在学生成绩表表执行DELETE语句删除记录时，如果删除了20条记录，将导致 DELETE触发器被执行20 次。

按触发的时间分为三类：

BEFORE触发器：在触发事件之前执行触发器。
AFTER触发器：在触发事件之后执行触发器。
INSTEAD OF触发器：当触发事件发生后，执行触发器中指定的函数，而不是执行产生触发事件的SQL 语句
注：在表或视图上，对于INSERT、UPDATE 或 DELETE 三种触发事件，每种最多可以定义一个INSTEAD OF 触发器

触发器相关的特殊变量

NEW
- 数据类型是RECORD。
- 对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。
- 对于语句级触发器，它的值是NULL。
OLD
- 数据类型是RECORD。
- 对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。
- 对于语句级触发器，它的值是NULL。
TG_OP
- 数据类型是text；
- 是值为INSERT、UPDATE、DELETE 的一个字符串，它说明触发器是为哪个操作引发。

二、安装触发器的语法

CREATE TRIGGER 触发器名()
{ BEFORE | AFTER | INSTEAD OF }
ON 表名
[ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE 存储过程名 ( 参数列表 )
（1）指明所定义的触发器名
（2） BEFORE | AFTER | INSTEAD OF 指明触发器被触发的时间
（3） ON 表名 指明触发器所依附的表
（4） FOR EACH { ROW | STATEMENT } 指明触发器被触发的次数
（5） EXECUTE PROCEDURE 存储过程名 ( 参数列表 ) 指明触发时所执行的存储过程

三、创建触发器的基本步骤：

（1）创建触发器所要依附的表
（2）创建触发器被触发时所要执行的触发器函数，该函数的类型必须是TRINGER型，是触发器的执行函数。
（3）创建触发器

四、创建触发器的示例

假设有stu_score表存储学生的课程成绩，其表结构如下

CREATE TABLE stu_score
( 
sid character(10) NOT NULL, 
cid character(10) NOT NULL, 
score numeric(5,1),
CONSTRAINT stu_score_pkey PRIMARY KEY (sid, cid)
)

对表中插入如下数据：

INSERT INTO stu_score VALUES('14101','1201',85);
INSERT INTO stu_score VALUES('14101','1301',85);

为了防止非法修改stu_score表的课程成绩，创建audit_score表记录stu_score表的成绩变化，其表结构如下：

CREATE TABLE audit_score
( 
username character(20) ,
sid character(10) ,
cid character(10) ,
updatetime text , 
oldscore numeric(5,1), 
newscore numeric(5,1) 
)

创建触发器函数score_audit()

CREATE OR REPLACE FUNCTION score_audit() RETURNS TRIGGER AS $score_audit$
	BEGIN
		IF (TG_oP = 'DELETE' )THEN
			INSERT INTO audit_score SELECT user，old.sid，old.cid，now(),OLD.score ;
			RETURN OLD;			
		ELSIF (TG_oP = 'UPDATE' ) THEN
            INSERT INTO audit_score SELECT user,old.sid,old.cid,now(),old.score,new.score
            where old.sid=new.sid and old.cid=new.cid;
            RETURN NEW;            
		ELSIF (TG_OP = 'INSERT')THEN
			INSERT INTO audit_score SELECT user，new.sid，new.cid，now(),null,new.score;
			RETURN NEw;
		END IF;
		RETURN NULL;END;
$score_audit$ LANGUAGE plpgsql;

接下来在stu_score表上安装触发器score_audit_triger

CREATE TRIGGER score_audit_triger
AFTER INSERT OR UPDATE OR DELETE ON stu_score
FOR EACH ROW EXECUTE PROCEDURE score_audit();

五、验证触发器的执行

将课程号为1201的课程成绩增加1分

update stu_score set score=score+1 where cid='1201';

依次查看两个表即可。

六、触发器的修改

ALTER TRIGGER old_name ON table_name RENAME TO new_name

例如：将上述定义的触发器改名为score_audit_trig

ALTER TRIGGER score_audit_trigger ON stu_score RENAME TO score_audit_trig;

七、触发器的删除

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
主要参数说明：
（1）IF EXISTS：如果指定的触发器不存在，那么发出提示而不是抛出错误。
（2）name：要删除的触发器名。
（3）table_name：触发器定义所依附的表的名称。
（5）CASCADE：级联删除依赖此触发器的对象。
（6）RESTRICT：如果有依赖对象存在，那么拒绝删除。该参数缺省是拒绝删除。
例如：将上述触发器score_audit_trig删除，同时级联删除依赖触发器的对象。
DROP TRIGGER IF EXISTS score_audit_trig ON stu_score CASCADE;

6.5 游标编程

一、游标的基本概念

游标（Cursor）是一种临时的数据库对象；
用来存放从数据库表中查询返回的数据记录；
提供了从结果集中提取并分别处理每一条记录的机制；
游标总是与一条SQL查询语句相关联；
游标包括：SQL语言的查询结果，指向特定记录的指针。

二、声明游标

（1）在存储过程中游标类型的变量。例如：游标变量 refcursor;此时，游标变量还没有绑定查询语句，因此不能访问游标变量。

（2）游标声明语法：

游标名 REFCURSOR [ ( arguments ) ] FOR SELECT;

其中arguments为由逗号分隔的参数列表，用于打开游标时向游标传递参数，类似于函数的形式参数；

返回的值存储在游标变量中。

游标声明的示例

（1）curStudent REFCURSOR FOR SELECT * FROM student;
（2）curStudentOne REFCURSOR (key integer) IS SELECT * FROM student WHERE SID = key;

三、打开游标

（1）OPEN FOR 其声明形式，例如：

OPEN curVars1 FOR SELECT * FROM student WHERE SID = mykey;

（2）OPEN FOR EXECUTE 其声明形式为：

OPEN unbound_cursor FOR EXECUTE query-string;

打开未绑定的游标变量。EXECUTE将动态执行查询字符串。

例如：

OPEN curVars1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

注意：$1是指由存储过程传递的第1个参数。

（3）打开一个绑定的游标，其声明形式为：

OPEN bound_cursor [ ( argument_values ) ];

仅适用于绑定的游标变量，只有当该变量在声明时包含接收参数，才能以传递参数的形式打开该游标，参数将传入到游标声明的查询语句中，例如：

OPEN curStudent;
OPEN curStudentOne (‘20160230302001’);

四、使用游标

FETCH命令从游标中读取下一行记录的数据到目标中，读取成功与否，可通过PL/SQL内置系统变量FOUND来判断。

例如：

FETCH curVars1 INTO rowvar;     --rowvar为行变量
FETCH curStudent INTO SID, Sname, sex;

请注意：游标的属性列必须与目标列的数量一致，并且类型兼容

五、关闭游标

CLOSE curVars1;

当游标数据不再需要时，需要关闭游标，以释放其占有的系统资源，主要是释放游标数据所占用的内存资源，

六、在存储过程中使用游标的示例1

1、下面例子使用不带参数的游标，查询student表的学号、学生姓名和性别

CREATE OR REPLACE FUNCTION cursorDemo ( )
returns boolean as $BODY$
Declare --定义变量及游标
    unbound_refcursor refcursor; --声明游标变量
    vsid varchar; --学号变量
    vsname varchar; --姓名变量
    vsgender varchar;  --性别变量
begin --函数开始
	open unbound_refcursor for execute 'select sid,sname ,sex from student'; --打开
	loop --开始循环
		fetch unbound_refcursor into vsid,vsname ,vsgender;--从游标中取值给变量
		if found then	--检查从游标中是取到数据
			raise notice '% ,% ,% ' , vsid ,vsname , vsgender;
		else
			exit;
		end if;
	end loop;  --结束循环
	close unbound_refcursor;	--关闭游标
	raise notice'取数据循环结束...';	--打印消息
	return true;	--为函数返回布尔值
	exception when others then raise exception 'error(%) ' , sqlerrm;-- sqlerrm错误代码变量,抛出异常end; --结束
$BODY$  LANGUAGE plpgsql;--规定语言

2、下面示例使用带参数的游标，从成绩表中查询分数大于某给定值的学号和课程号

create or replace function cusorDemo2 (myscore int)
returns void as
$$
	declare
		vstuscore stu_score%ROWTYPE; --定义与表stu_score结构相同的行变量
		--定义带有一个输入参数的游标
		vstucursor cursor( invalue int)
		for select sid,cid,score from stu_score where score>=invalue order by sid;
	begin
		open vstucursor (myscore) ;--从外部传入参数给游标
		loop fetch vstucursor into vstuscore;
			exit when not found; --假如没有检索到数据，结束循环处理
			raise notice '%,%,% ' , vstuscore.sid,vstuscore.cid,vstuscore.score;
		end loop;
		close vstucursor;--关闭游标
	end;
$$ language plpgsql;

select cusorDemo2 (85)

6.6 嵌入式SQL编程

掌握嵌入SQL的使用方式

一、嵌入式SQL与宿主语言

SQL语言具有很强的查询处理能力，而逻辑表达的能力很弱，界面编程能力也很弱。
如JAVA，C/C++等高级语言具有很强逻辑表达能力，能实现复杂的处理逻辑，同时具有较强的用户界面实现功能。
为了使程序语言同时具有它的优点，在JAVA、C/C++等高级语言中嵌入SQL语句，称高级语言为宿主语言。
由DBMS的预编译器扫描识别处理SQL语句，把SQL语句转换成主语言调用语句，以使主语言编译程序能识别它，最后由主语言的编译程序将整个源程序编译成目标码，然后连接（Link）处理生成装载模块。

二、嵌入式SQL的处理过程

三、嵌入式SQL与主语言之间的通信

将SQL嵌入到高级语言中混合编程，SQL语句负责操纵数据库，高级语言语句负责控制流程。

这时程序中会含有两种不同的计算模型的语句，从而需要二者之间建立通信。

数据库工作单元与源程序工作单元之间的通信主要包括：

（1）向主语言传递SQL语句的执行状态信息，使主语言能够根据此信息控制程序流程，主要用SQL通信区实现

（2）主语言向SQL语句提供参数，主要用主变量实现

（3）将SQL语句查询数据库的结果传回主语言处理，主要用主变量和游标实现

四、嵌入SQL的使用规定

（1）在程序中要区分SQL语句和宿主语言语句

嵌入式SQL语句的格式如：

EXEC SQL <SQL语句> END_EXEC

结束标志在不同的宿主语言中可能是不同的，在C和PASCAL语言程序中规定结束标志为“；”，而非“END_EXEC”。

（2）SQL的集合处理方式与宿主语言单记录处理方式之间的协调
由于SQL语句处理的是记录集合，而宿主语言语句一次只能处理一条记录，因此需要使用游标（Cursor）机制，把集合操作转换为单记录处理方式。

游标是与某一查询结果相联系的符号名，与游标有关的SQL语句如下：

1）游标定义语句（DECLARE）。其格式如下：

EXEC SQL DECLARE <游标名> CURSOR FOR
< SELECT 语句>
END EXEC

2）游标打开语句（OPEN）。

该语句在执行游标定义中的SELECT语句，同时游标处于活动状态。游标是一个指针，此时指向查询结果的第一行之前。
OPEN语句的格式如下：

EXEC SQL OPEN <游标名>
END_EXEC

3）游标读取数据语句（FETCH）。

此时游标推进一行，并把游标指向的行（即当前行）中的值取出并送到共享变量，其格式如下：

EXEC SQL FETCH FROM <游标名> INTO <变量表>
END_EXEC

变量表由逗号分开的共享变量组成。

FETCH语句通常置于宿主语言程序的循环结构中，以逐一处理查询结果中的每一个元组。

posted @ 2022-05-05 14:58 Dinesaw 阅读(246) 评论(0) 收藏举报

刷新页面返回顶部

Dinesaw

第6章 数据库应用编程

第6章 数据库应用编程

6.1 数据库连接技术

ODBC的技术背景

ODBC的层次结构

应用程序使用ODBC访问数据库的步骤

使用ODBC管理器配置数据源

JDBC编程

JDBC工作原理

JDBC程序访问数据库步骤

（1）加载驱动

（2）建立连接

（3）创建Statement对象

（4）执行SQL语句

（5）ResultSet保存结果集

（6）关闭连接

在Eclipse下使用JDBC连接数据库

eclipse如何导入MySQL的JDBC驱动jar包？

Java使用JDBC连接数据库的例子

使用JDBC连接PostgreSQL数据库并完成一些操作

6.2 Java Web数据库编程

一、 WEB数据库系统技术

Java web

二、Java web开发运行环境

数据库应用程序结构 三层架构（3-tier Architecture）

数据库应用程序结构 MVC

三、 Servlet技术

Servlet的工作流程

四、JSP技术

五、JavaBean技术

六、MyBatis访问数据库

6.3 存储过程编程

什么是存储过程

创建的语法

创建 示例

执行存储过程

删除存储过程

PL/SQL基本语法

1、声明局部变量。

2、条件语句

3、循环语句

存储过程的优缺点

6.4 触发器编程

一、触发器的基本概念

触发器的分类

按触发的语句分为：

触发器执行的次数可分：

按触发的时间分为三类：

触发器相关的特殊变量

二、安装触发器的语法

三、创建触发器的基本步骤：

四、创建触发器的示例

五、验证触发器的执行

六、触发器的修改

七、触发器的删除

6.5 游标编程

一、游标的基本概念

二、声明游标

游标声明的示例

三、打开游标

四、使用游标

五、关闭游标

六、在存储过程中使用游标的示例1

1、下面例子使用不带参数的游标，查询student表的学号、学生姓名和性别

2、下面示例使用带参数的游标，从成绩表中查询分数大于某给定值的学号和课程号

6.6 嵌入式SQL编程

一、嵌入式SQL与宿主语言

二、嵌入式SQL的处理过程

三、嵌入式SQL与主语言之间的通信

四、嵌入SQL的使用规定

公告

第6章数据库应用编程

第6章数据库应用编程

数据库应用程序结构三层架构（3-tier Architecture）

创建示例