简单来讲3个优点:简单、安全、高性能
EXECUTE AddNewProduct('JTS01','Stuffed Eiffel Tower',6.49,'Plush stuffed toy with the text La Tour Eiffel in red white and blue');
履行1个名为AddNewProduct的存储进程,将1个新产品添加到Products表中。AddNewProduct有4个参数,分别是:供应商ID、产品名、价格和描写。这4个参数匹配存储进程中的4个预期变量。此存储进程将新行添加到Products表,并将传入的属性赋给相应的列。我们注意到,在Products表中还有另外1个需要值的列Prod_id,它是表的主键。为何不把这个值作为属性传递给存储进程?要保证恰当地生成此ID,最好是使生成此ID的进程自动化。
以下是存储进程完成的工作:
1个例子,对邮件发送清单中具有邮件地址的顾客进行计数。
CREATE PROCEDURE MailingListCount(
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount :=v_rows;
END;
事务处理
事务处理用来保护数据库的完全性,它保证成批的SQL操作要末完全履行,要末完全不履行
事务处理是1种机制,用来管理必须成批履行的SQL操作,以保证数据库不包括不完全的操作结果(好比运行进程中突然断电了,致使批处理意外终止)
事务:指1组SQL语句
回退:rollback,撤消指定SQL语句的进程
提交:commit,将未存储的SQL语句结果写入数据库表
保存点:临时占位符
可以回退INSERT、UPDATE、DELETE语句
不能回退CREATE、DROP、SELECT(回退SELECT也没有必要)
通常COMMIT用于保存更改,ROLLBACK用于撤消
DELETE FROM Orders;
ROLLBACK;
在事务处理块中,提交不会隐式进行。(但是也是与不同的DBMS有关,有的就是隐式提交处理事务端)
进行明确的提交,使用COMMIT语句。下面是1个SQL Server的例子:
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num =12345
COMMIT TRANSACTION
在这个例子中,从系统中完全删除定单12345.由于设计删除两个数据库表,用事务处理块来保证定单不被部份删除。最后COMMIT语句仅在不出错时写出更改。如果第1叫DELETE起作用,但第2条DELETE失败,则DELETE不会提交。
设置占位符,每一个点有唯1的名字。
完全的SQL Server例子
BEGIN TRANSACTION
INSERT INTO Customers(cust_id,cust_name)
VALUES('100000010','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,1,'BR03',100,10.99)
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION
这里的事务处理块中包括4个INSERT语句,只要判断变量不等于0,那末就有毛病产生,就能够进行回退。
返回结果集,简单的SELECT没法得到第1行、下1行或前10行。
常见的1些选项和特性:
- 能够标记游标为只读,数据不能更新和删除
- 能控制可以履行的定向操作(向前、向后、第1、最后、绝对位置、相对位置等)
- 能标记某些列为可编辑的,某些列为不可编辑的
- 规定范围,使游标对创建它的特定要求(如存储进程)或对所有要求可访问
- 唆使DBMS对检索出的数据(而不是指出表中活动数据)做复制,使在游标打开和访问期间数据不变化
步骤:
使用DECLARE语句创建游标。
例子,找出空缺的电子邮件地址
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;
OPEN CURSOR CustCursor
现在可以用FETCH语句访问游标数据了,FETCH指出要检索哪些行,从何处检索它们和将它们放于何处。
例子:从游标中检索第1行
DECLARE TYPE CustCursor IS REF CURSOR
RETURN Custmors%ROWTYPE;
DECLARE CustRecord Customers%POWTYPE
BEGIN
OPEN CustCursor
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
END;
例子2:从第1行到最后1行,对检索出来的数据进行循环
DECLARE TYPE CustCursor IS REF CURSOR
RETURN Custmors%ROWTYPE;
DECLARE CustRecord Customers%POWTYPE
BEGIN
OPEN CustCursor
LOOP
FETCH CustCursor INTO CustRecord;
EXIT WHEN CustCursor%NOTFOUND;
...
END LOOP;
CLOSE CustCursor;
END;
CLOSE CustCursor
主键是1种特殊的束缚,用来保证1列(或1组列)中的值是唯1的,而且永不改动。
那末主键需要甚么条件呢?
在定义表的时候可以创建主键
CREATE TABLE Vendors
(
vend_id CHAR(10) NOT NULL PRIMARY KEY,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) NULL,
更新表的时候
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);
外键是表中的1列,其值必须在另外一表的主键中。外键是保证援用完全性的及其重要的部份。
如何理解外键?
Orders表将录入系统的每一个定单作为1行包括其中。顾客信息存储在Customers表中。Orders表中的定单通过顾客ID与Customers表中的特定的行进行关联。顾客ID为Customers表的主键,每一个顾客都有唯1的ID。定单号为Orders的主键,每一个定单都有唯1的定单号。
Orders表中的顾客ID列的值其实不1定是唯1的。如果某个顾客有多个定单,则有多个行有相同的顾客ID。同时,Orders表中的顾客ID列的合法值为Customers表中的顾客ID。
在这个例子中,在Orders的顾客ID列上定义1个外键,因此只能接受CUstomers表的主键值
下面是定义这个外键的方法
CTEATE TABLE Orders
(.......
REFERENCES Customers(cust_id)
);
其中表定义使用了REFERENCES关键字,它表示cust_id中的任何值都必须是Customers表中的cust_id的值。
相同的工作也能在ALTER TABLE与剧中使用CONSTRAINT语法来完成:
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)
唯1束缚用来保证1列中的数据是唯1的。它们类似主键,但是又有1些区分。
检查束缚用来保证1列(或1组列)中的数据满足1组指定的条件。检查束缚的常见用处有以下几点
下面的例子就是束缚检查
CREATE TABLE OrderItems
(
..........
quantity INTEGER NOT NULL CHECK (quantity>0),
)
这样就可以保证任何插入或更新的行都会被检查,保证大于0。
检查gender只包括M或F可以用
ADD CONSTRAINT CHECK (gender LIKE '[MF]')
为何要索引?
用来排序数据,以加快搜索和排序操作的速度。
在Products表的产品名列上创建1个简单的索引
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
触发器的常见用处
触发器是特殊对存储进程,它在特定的数据库活动产生时自动履行。
1个简单的例子,它对所有INSERT和UPDATE操作,将Customers表中的cust_state列转换为大写
CREATE TRIGGER customer_state
ON Customers
FOR INSERT,UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;
1般束缚比触发器块,因此可能的时候,应当尽量使用束缚。