SQL学习笔记6 视图存储过程事务处理

待我称王封你为后i 2022-05-30 05:52 249阅读 0赞

## 视图 VIEW ##

视图是**虚拟的表**，其只包含**使用时动态检索数据的查询**。视图**本身不包含数据**，返回的数据都是从**其他表**中检索出来的，一旦**这些表的数据发生变化**，对应的**视图也将返回变化的数据**。  
**（Access不支持视图，SQLite只支持视图只读）**

**其包括以下用途：**

*  简化复杂的联结
 *  重新格式化检索出的数据
 *  过滤不想要的数据
 *  创建包含计算字段的视图

正常检索数据，当需要检索订购了某种产品的顾客时，需要理解这些表的结果，并修改`WHERE`子句，显得很繁琐

SELECT cust_name，cust_contact，prod_id
    FROM customers，orders，orderitems
    WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.cust_id.order_num;
    AND prod_id = '001';

**创建视图**

CREATE VIEW productcustomers AS SELECT cust_name，cust_contact，prod_id FROM customers，orders，orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.cust_id.order_num;

**检索视图**

SELECT cust_name，cust_contact FROM productcustomers WHERE prod_id = '001';

*  创建视图以后，以后有类似的查询，都可以**直接检索视图**，简化了查询步骤
 *  与表一样，视图必须**唯一命名**
 *  视图**可以嵌套**，即查询时利用其它视图来构造视图，但严重消耗性能
 *  许多DBMS禁止在视图中使用`ORDER BY`子句
 *  有些DBMS要求对返回的所有列命名
 *  **删除视图**，可以使用`DROP VIEW viewname`

## 存储过程 ##

存储过程就是**为以后使用而保存的一条或多条SQL语句**，经编译后存储在数据库，用户通过**指定存储过程的名字并给出参数**（如果该存储过程带有参数）来执行它。可将其视为批文件。但其他们的作用不仅限于批处理。**个人理解有点像自定义的函数**  
**Access和SQLite不支持存储过程**

**执行存储过程**

EXECUTE addnewproduct('001','zjw','6.49');

*  `EXECUTE`接受**存储过程名**和需要传递给它的**任何参数**

**创建存储过程**  
自动创建订单（SQL server）

-- 接受一个参数，顾客id
    CREATE PROCEDURE NewOrder @cust_id CHAR(10) AS -- 定义变量 DECLARE @order_num INTEGER -- 获取最大订单号 SELECT @order_num = MAX(order_num) FROM orders -- 计算下一订单号 SELECT @order_num = @order_num + 1 --插入新订单 INSERT INTO orders（order_num,order_date,cust_id） VALUES(@order_num,GETDATE(),@cust_id) --返回新订单号 RETURN @order_num;

*  只适用于**SQL server**
 *  `DECLARE`用于声明变量，SQL server中所有局部变量都以`@`开头
 *  **不同的DBMS，实现存储过程的语法实现不一**

## 事务处理 TRANSACTION ##

使用**事务处理（Transaction processing）**，通过确保成批的SQL操作，**要么完全执行、要么不执行**，保证不包含不完整的操作结果。  
事务管理用来管理`INSERT`， `UPDATE` ，`DELETE`，**不能回退**`SELECT`， `CREATE` ，`DROP`

*  事务：一组SQL语句
 *  回退（rollback）：撤销指定SQL语句的过程
 *  提交（commit）：将未存储的SQL语句结果写入数据表
 *  保留点（savepoint）：指事务处理中设置的临时占位符，可以对它发布回退

**SQLserver**

BEGIN TRANSACTION .... COMMIT TRANSACTION

**MariaDB 和 MySQL**

START TRANSACTION ...

**Oracle**

SET TRANSACTION ...

**PostgreSQL**

BEGIN ...

*  多数实现没有明确标识事务处理在何处结束，通常`COMMIT`用于保存更改，`ROLLBACK`用于撤销

**使用ROLLBACK**

DELETE FROM orders； ROLLBACK；