14 数据库对象

14.1 表

1、创建表

	CREATE TABLE OrderItems(order_num  INTEGER  NOT NULL,Order_date DATETIME NOT NULL,Cust_id    CHAR(10) NOT NULL,Quantity   INTEGER  NOT NULL  DAFAULT  1,   -------指定默认值)

2、更新表

ALTER TABLE Vendors ADD vend_phone CHAR（20）      -------给Vendors表增加一个名为vend_phone的列，数据类型为CHAR

3、删除表

DROP TABLE CustCopy

4、重命名表

DB2/MariaDB/MySQL/Oracle/PostgreSQL

RENAME TABLE DB2INST1.TBL_BANKCODE20170822 TO TBL_BANKCODE<注意1>重命名表时，源表不能在任何现有定义（视图或具体化查询表）、触发器、SQL函数或约束中引用。它也不能具有任何生成列（标识列除外），或者不能是父表或从属表。目录条目更新以反映新表名。
<注意2>后面的表不能加schame。

SQL Server

sp_rename存储过程

SQLite

ALTER TABLE语句

14.2 视图

1、创建视图

CREATE VIEW ProductCustomers ASSELECT cust_name,cust_contact,prod_idFROM Customers,Orders,OrderItemsWHERE Customers.cust_id=Orders.cust_idAND OrderItems.order_num=Orders.order_num;

2、视图和表一样，必须唯一名。

14.3 存储过程

14.3.1 概念

定义	说明
定义	存储过程就是为以后使用而保存的一条或多条SQL语句。
说明	可将存储过程视为批文件，但作用不仅限于批处理。
说明	存储过程的优缺点 优点 1-将处理封装在简易单元可以简化复杂的操作。 2-不用要求反复建立一系列的处理步骤，保证了数据的一致性。开发人员使用同一存储过程，则能保证代码的一致性，防止错误。 3-简化对变动的管理。即如果表名、列名等有变化，只需要更改存储过程的代码。 4-存储过程以编译过的形式存储，可以是DBMS 处理命令所需的工作量少，提高性能。 缺点 1-不同DBMS存储过程语法有所不同，编写真正可移至的存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。如果需要移植到别的DBMS，至少客户端应用代码不需要移动。 2-编写存储过程比编写SQL语句复杂，需要更高的技能和更丰富的经验。

14.3.2 创建存储过程

CREATE OR REPLACE  PROCEDURE "WHJH"."PR_UPDATE_LAST_STATE" ( )BEGIN --6、开始CALL WHJH.PRO_LOG('PR_HANDLE_CHECK_RESULT',1,9,'更新错误/疑问正式表中的“最终数据状态”');--单位基本情况信息表CALL WHJH.PRO_LOG('PR_HANDLE_CHECK_RESULT',1,91,'更新单位表错误/疑问正式表中的“最终数据状态”');UPDATE WHJH.TBL_BIZCHECK_CUSTINFO_DOU_OFF T SET (LAST_STATE,DEALDATE,DEALTYPE) = (SELECT LAST_STATE,CURRENT DATE,'A' FROM WHJH.TBL_LAST_STATE_TMP A WHERE T.CUSTCODE = A.DATANO fetch first 1 rows only)where exists(SELECT 1 FROM WHJH.TBL_LAST_STATE_TMP A WHERE T.CUSTCODE = A.DATANO);commit;END;

14.3.2 调用存储过程

CALL WHJH.PRO_LOG('PR_HANDLE_CHECK_RESULT',1,9,'更新错误/疑问正式表中的“最终数据状态”');

14.3.3 DbVisualizer工具中调用方法

说明：在工具中直接执行存储过程。
格式：

--/ 存储过程
/

注意：

1 END后面不能加；符号
2 CREATE OR REPLACE PROCEDURE “WHJH”.“PR_PER_TIMELESS” ( ) 要去掉OR REPLACE
3 备份旧的存储过程
4 删除旧的存储过程后再执行建立新的存储过程
5 存储过程：stored Procedures
6 存储过程分段只能用脚本执行

14.3.3 DB2命令行脚本调用方法

	db2 -td@ -vf 1.sql

注意：

1 sql的执行，需要连接上数据库，每个存储过程上放@隔开。
2 分段的存储过程可以执行。例：P2:BEGIN

14.3.4 DB2中两个存储过程报错.

报错1：缺少权限。缺少方法的权限。

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0440N  No authorized routine named "FN_USDREATE" of type "FUNCTION" having 
compatible arguments was found.  LINE NUMBER=109.  SQLSTATE=42884

报错2：要创建的存储过程已存在，再次执行时报错。

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0454N  The signature provided in the definition for routine 
"TYCJ.SP_DIS_CFA" matches the signature of some other routine that already 
exists in the schema or module.  LINE NUMBER=12.  SQLSTATE=42723

15 事务处理

15.1 RANSACTION

定义：

事务（transaction）指一组SQL语句；
事务处理（transaction processing）用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。

示例：

数据库	格式
SQL Server	BEGIN TRANSACTION … COMMIT TRANSACTION
MariaDB 和 MySQL	START TRANSACTION …
Oracle	SET TRANSACTION …
PostgreSQL	BEGIN …

15.2 ROLLBACK

定义：回退（rollback）指撤销指定SQL语句的过程；
说明：事务处理用来管理INSERT/UPDATE/DELETE语句，不能回退SELECT语句，也不能回退CREATE或DROP操作。
示例：

DELETE FROM Orders；
ROLLBACK;

15.3 COMMIT

定义：提交（commit）指未将存储的SQL语句结果写入数据库表；
说明：一般SQL语句都是针对数据库表直接执行和编写的，是隐式提交（implicit commit），即提交操作时自动进行的。
说明：事务处理块中，提交不会隐式进行必须使用COMMIT进行明确的提交。
示例：

数据库	格式
SQL Server	BEGIN TRANSACTION DELETE OrderItems WHERE order_num=12345 DELETE Order WHERE order_num=12345 COMMIT TRANSACTION
Oracle	SET TRANSACTION DELETE OrderItems WHERE order_num=12345 DELETE Order WHERE order_num=12345 COMMIT

15.4 保留点SAVEPOINT

定义：保留点（SAVEPOINT）指事务处理中设置的临时占位符（placeholder），可以对他发布回退（与回退整个事务处理不同）。
说明：又称为占位符。
示例：创建保留点（占位符）delete1

1、SQL Server
创建

SAVE TRANSACTION delete1

回滚

ROLLBACK TRANSACTION delete1

完整

BEGIN TRANSACTIONINSERT INTO Customers(cust_id,cust_name) VALUES('1000000010','Toys Emporium');SAVE TRANSACTION StartOrder;INSERT INTO Orders(order_num,order_date,cust_id) VALUES(20100,'2001/12/1','1000000010');IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;INSERT INTO OrderItems(order_num,order_item,prod_id,quantity,item_price) VALUES(20100,1,'BR01',100,5.49);IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;INSERT INTO OrderItems(order_num,order_item,prod_id,quantity,item_price) VALUES(20100,2,'BR03',100,10.99);IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;COMMIT TRANSACTION

2、MariaDB、MySQL和Oracle
创建

SACEPOINT delete1

回滚

ROLLBACK TO delete1