＜SQL＞《SQL命令（含例句）精心整理版（4）》

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,   -------指定默认值
	)
1
2
3
4
5
6
7

2、更新表

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

3、删除表

DROP TABLE CustCopy
1

4、重命名表

DB2/MariaDB/MySQL/Oracle/PostgreSQL

RENAME TABLE DB2INST1.TBL_BANKCODE20170822 TO TBL_BANKCODE

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

SQL Server

sp_rename存储过程
1

SQLite

ALTER TABLE语句
1

14.2 视图

1、创建视图

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.order_num;
1
2
3
4
5

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

14.3 存储过程

14.3.1 概念

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;
1
2
3
4
5
6
7
8
9
10

14.3.2 调用存储过程

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

14.3.3 DbVisualizer工具中调用方法

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

--/ 
 存储过程
/
1
2
3

注意：

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

注意：

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
1
2
3
4

报错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
1
2
3
4
5

15 事务处理

15.1 RANSACTION

定义：

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

示例：

15.2 ROLLBACK

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

DELETE FROM Orders；
ROLLBACK;
1
2

15.3 COMMIT

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

15.4 保留点SAVEPOINT

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

1、SQL Server
创建

SAVE TRANSACTION delete1
1

回滚

ROLLBACK TRANSACTION delete1
1

完整

BEGIN TRANSACTION
			INSERT 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
1
2
3
4
5
6
7
8
9
10

2、MariaDB、MySQL和Oracle
创建

SACEPOINT delete1
1

回滚

ROLLBACK TO delete1
1