数据管理基础-ch45-47

ch 45 面向驱动的数据库编程

• JAVA世界： JDBC
  • w3cschool
• 微软：ODBC, OLE DB等
• Python： Python DB-API +各数据库模块

JDBC为例

以JDBC为例 1

• 加载数据库驱动

  Class.forName("com.mysql.jdbc.Driver");
  

• 建立连接

  conn = DriverManager.getConnection(
  "jdbc:mysql://localhost:3306/test", "root", "");
  

• 创建 Statement 对象，用于向数据库发送 SQL 语句；

  String sql = "SELECT * FROM `user`";
  Statement statement = connection.createStatement();
  ResultSet resultSet = statement.executeQuery(sql);
  

• 获取 ResultSet 对象，取出数据，此对象代表结果集；

  while (resultSet.next()) { 
  		int id = resultSet.getInt(“id”); 
  		……
  		}
  

• 释放资源，断开与数据库的连接。

  resultSet.close();	
  statement.close();
  connection.close();
  

ch 46 过程化SQL

过程化SQL

• 过程化SQL
  • SQL的扩展
  • 增加了过程化语句功能
  • 基本结构是块
    • 块之间可以互相嵌套
    • 每个块完成一个逻辑操作

过程化SQL的块结构

• 定义部分
  DECLARE 变量、常量、游标、异常等
  • 定义的变量、常量等只能在该基本块中使用
  • 当基本块执行结束时，定义就不再存在
• 执行部分

  BEGIN
	SQL语句、过程化SQL的流程控制语句
     EXCEPTION
	异常处理部分        		
     END；

变量和常量的定义

• 变量定义
  • 变量名 数据类型 [[NOT NULL]:=初值表达式]或
  • 变量名 数据类型 [[NOT NULL] 初值表达式]
• 常量定义
  • 常量名 数据类型 CONSTANT :=常量表达式
  • 常量必须要给一个值，并且该值在存在期间或常量的作用域内不能改变。如果试图修改它，过程化SQL将返回一个异常
• 赋值语句
  • 变量名称 :=表达式

流程控制-条件控制语句

流程控制-循环控制语句

流程控制-错误处理

• 如果过程化SQL在执行时出现异常，则应该让程序在产生异常的语句处停下来，根据异常的类型去执行异常处理语句

• SQL标准对数据库服务器提供什么样的异常处理做出了建议，要求过程化SQL管理器提供完善的异常处理机制

ch 47 存储过程和函数

存储过程

存储过程 1

• 过程化SQL块类型
• 命名块
  • 编译后保存在数据库中，可以被反复调用，运行速度较快，过程和函数是命名块
• 匿名块
  • 每次执行时都要进行编译，它不能被存储到数据库中，也不能在其他过程化SQL块中调用

存储过程 2

• 存储过程：由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。
• 存储过程的优点
  • 运行效率高
  • 降低了客户机和服务器之间的通信量
  • 方便实施企业规则

存储过程的用户接口-创建存储过程

存储过程的用户接口-创建存储过程 1

存储过程的用户接口-创建存储过程 2

• [例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

• CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount  INT,amount FLOAT) 
         /*定义存储过程TRANSFER，其参数为转入账户、转出账户、转账额度*/
  	AS DECLARE		/*定义变量*/
  		totalDepositOut Float;
       	totalDepositIn Float;
  		inAccountnum INT;
  

  存储过程的用户接口-创建存储过程 3

  BEGIN                         	    /*检查转出账户的余额 */	                       
  	       SELECT Total INTO totalDepositOut FROM Accout 
  	       WHERE accountnum=outAccount; 
  	       IF totalDepositOut IS NULL THEN   
                                     		 /*如果转出账户不存在或账户中没有存款*/
  	                ROLLBACK; 	   /*回滚事务*/
  	                RETURN;
  	       END IF; 
   	IF totalDeposit Out< amount THEN    	/*如果账户存款不足*/
  		ROLLBACK; 				/*回滚事务*/
  		RETURN;
  	END IF;
  

  存储过程的用户接口-创建存储过程 4

  SELECT Accountnum INTO inAccountnum FROM Account
  	WHERE accountnum=inAccount;
  	IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        
  		ROLLBACK; 	         	 		/*回滚事务*/
  		RETURN;
  	ENDIF;
  	UPDATE Account SET total=total-amount 	WHERE accountnum=outAccount;
   				/* 修改转出账户余额，减去转出额 */
  	UPDATE Account SET total=total + amount 
  	WHERE   accountnum=inAccount; 
  				/* 修改转入账户余额，增加转入额 */
  	COMMIT;                       	/* 提交转账事务 */
  END;
  

存储过程的用户接口-执行存储过程

• 执行存储过程
  CALL/PERFORM PROCEDURE 过程名([参数1,参数2,…])；
  • 使用CALL或者PERFORM等方式激活存储过程的执行
  • 在过程化SQL中，数据库服务器支持在过程体中调用其他存储过程
• [例8.9] 从账户01003815868转10000元到01003813828账户中。

  CALL PROCEDURE TRANSFER(01003813828,01003815868,10000);

储过程的用户接口-其他

• 改存储过程
  • ALTER PROCEDURE 过程名1 RENAME TO 过程名2;
• 删除存储过程
  • ·DROP PROCEDURE 过程名()；

函数

• 函数的定义语句格式
  • CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型> AS <过程化SQL块>;
• 函数的执行语句格式
  • CALL/SELECT 函数名 ([参数1,参数2,…]);
• 修改函数
  • 重命名
    • ALTER FUNCTION 过程名1 RENAME TO 过程名2;
  • 重新编译
    • ALTER FUNCTION 过程名 COMPILE;