创建存储过程:
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)
方法二.从 SQL Server 数据库导入数据到 ORACLE 数据库可以选择用 Windows 下 ORACLE9i 企业或者个人版数据库做中转。 ( 注意:ORACLE 通过访问 SQL Server 的数据...
SQLserver、Oracle、Mysql语法与用法对比_计算机软件及应用_IT/计算机_专业资料。操作 查看表结 构 SQ:LServer exec sp_help 表名 Oracle desc 表名 Mysql desc...
mysql sqlserver oracle数据库比较_计算机软件及应用_IT/计算机_专业资料。数据库比较表数据库 MySql 当前, MySQL 采用双重授权 (Dual Licensed),他们是 GPL 和 ...
MySql Oracle SqlServer三大数据库的数据类型列表_IT/计算机_专业资料。MySql Oracle SqlServer三大数据库的数据类型列表,对比JAVA中的具体数据类型。...
在oracle , sqlserver , mysql 建立3个类似的简单表来测试 mysql create table test_user ( NAME VARCHAR(50) ) oracle create table test_user ( NAME ...
android通过http访问mysql或者sqlserver,oracle数据库_计算机软件及应用_IT/计算机_专业资料。今日推荐 78份文档 不小心又胖了 胖女人必看...
SQL Server,Oracle 和 MySQL 区别联系_计算机软件及应用_IT/计算机_专业资料。1. 如果要说明三者的区别,首先就要从历史入手。 1. Oracle:中文译作甲骨文,这是...
MySql Oracle SqlServer数据类型的详...
浅析MySQL、SQL Server、Oracle的异同点 计112 肖洪岳 从以下几方面进行简要分析:开放性 可伸缩性,并行性 安全性 性能 客户端支持及应用模式使用风险 一、开放性...
Oracle, SQL Server 和MySQL的分页SQL语句_计算机软件及应用_IT/计算机_专业...Oracle: :方法一: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT ...
热门文档