文档下载 文档下载导航
设为首页 | 加入收藏
搜索 请输入内容:  
 导航当前位置: 文档下载 > 所有分类 > IT/计算机 > 计算机软件及应用 > Oracle mysql sqlserver
侵权投诉

Oracle mysql sqlserver

创建存储过程:
1、Oracle版
CREATE PROCEDURE MailingListCount (ListCount OUT NUMBER)
IS
BEGIN
SELECT * FROM basdept;
ListCount := SQL%ROWCOUNT;
END;
2、SQL Server版
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM basdept;
RETURN @cnt;


事务:
1、SQL Server版
BEGIN TRANSACTION
...
COMMIT TRANSACTION
2、MySQL版
START TRANSACTION
...
回退:
DELETE FROM orders;
ROLLBACK;
提交:
1、Oracle版
DELETE ORDERITEMS WHERE ORDER_NUM = 12345;
DELETE ORDERS WHERE ORDER_NUM = 12345;
COMMIT;
2、SQL Server版
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT transaction
保留点:
1、Oracle和MySQL版
SAVEPOINT delete1;
回退:ROLLBACK TO delete1;
2、SQL Server版
SAVE TRANSACTION delete1;
回退:ROLLBACK TRANSACTION delete1;
实例:
BEGIN TRANSACTION
INSERT INTO Customers(...) VALUES(...);
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(...) VALUES (...);
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(...) VALUES (...);
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION


游标:
创建游标:
1、DB2和SQL Server版
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL
2、Oracle版
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL

使用游标:
OPEN CURSOR CustCursor

游标的使用举例:
1、Oracle版1
DECLARE TYPE CustCursor IS REF CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
FETCH CustCursor INTO CustRecord;
CLOSE CustCursor;
END;
2、Oracle版2
DECLARE TYPE CustCursor CURSOR
RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
OPEN CustCursor;
LOOP
FETCH CustCursor INTO CustRecord;
EXIT WHEN CustCursor%NOTFOUND;
...
END LOOP;
END;
3、SQL Server版
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers WHERE cust_email IS NULL
DECLARE @cust_id CHAR(10),
@cust_name CHAR(50)
OPEN CustCursor
FETCH NEXT FROM CustCursor
INTO @cust_id,@cust_name
WHILE @@FETCH_STATUS = 0
BEGIN
...
FETCH NEXT FROM CustCursor
INTO @cust_id,@cust_name
END;
CLOSE CustCursor
4、MySQL版
DECLARE Employee_Cursor CURSOR FOR
  SELECT LastName, FirstName FROM Northwind.dbo.Employees
  OPEN Employee_Cursor
  FETCH NEXT FROM Employee_Cursor
  WHILE @@FETCH_STATUS = 0
  BEGIN
  FETCH NEXT FROM Employee_Cursor
  END
  CLOSE Employee_Cursor
  DEALLOCATE Employee_Cursor

关闭游标:
1、DB2和Oracle版
CLOSE CustCursor
2、SQL Server版
CLOSE CustCursor
DEALLOCATE CURSOR CustCursor



触发器:
1、SQL Server版
CREATE TRIGGER customer_state
ON Customers
FOR INSERT,UPDATE
AS
UPDATE Customers
SET cust_state = UPPER(cust_state)

第1页

你可能喜欢

热门文档

相关文档

站点地图 | 文档上传 | 侵权投诉 | 手机版
本站所有资源均来自互联网,本站只负责收集和整理,均不承担任何法律责任,如有侵权等其它行为请联系我们.
文档下载 Copyright 2013 doc.guandang.net All Rights Reserved.  -  email