实验内容
1，oracle中，使用存储过程的方式，实现登录日志。
2, SQLserver中，使用存储过程的方式，实现登录日志。
3，oracle中，在用户表中添加最后登录时间字段，使用触发器的方式，实现登录日志。
4, SQLserver中，在用户表中添加最后登录时间字段，使用触发器的方式，实现登录日志。
Oracle_建表.sql--建立用户表CREATE TABLE "SCOTT"."ACCOUNT" ("NAME" VARCHAR2(10) NOT NULL, "PASSWORD" NUMBER(10) NOT NULL, "LASTLANDED" DATE NOT NULL, PRIMARY KEY("NAME")) TABLESPACE "USERS" --建立日志表CREATE TABLE "SCOTT"."LOG" ("LOGINLOG" VARCHAR2(10) NOT NULL, "NAME" VARCHAR2(10) NOT NULL, PRIMARY KEY("NAME"), FOREIGN KEY("NAME") REFERENCES "SCOTT"."ACCOUNT"("NAME")) TABLESPACE "USERS"Oracle_存储过程_登录日志.sql--Oracle中，使用存储过程的方式，实现登录日志CREATE OR REPLACE PROCEDURE "SCOTT"."LOGIN"(name_param in VARCHAR2, password_param in NUMBER)isvar_name VARCHAR2(10);var_password NUMBER(10); var_message VARCHAR2(10);beginselect password INTO var_password from "SCOTT"."ACCOUNT" where name = name_param; if var_password = password_param then var_message := '登陆成功'; insert into "SCOTT"."LOG"(LoginLog, name) values (var_message, name_param); else var_message := '登陆失败'; insert into "SCOTT"."LOG"(LoginLog, name) values (var_message, name_param); end if; commit;end;Oracle_触发器_登录日志.sqlCreate or replace Trigger login2after insert or update On "SCOTT"."LOG" declare var_LoginLog VARCHAR2(10); var_name VARCHAR2(10);begin select loginlog into var_LoginLog from "SCOTT"."LOG"; if var_LoginLog = '登陆成功' then Update "SCOTT"."ACCOUNT" Set lastLanded = sysdate where name = var_name; end if;end;SQLserver_建表.sqlUSE [tempdb]GO/****** 对象: Table [dbo].[account] 脚本日期: 06/24/2010 20:52:48 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--建立用户表CREATE TABLE [dbo].[account]( [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, [password] [int] NULL, [lastLanded] [datetime] NULL, CONSTRAINT [PK_account] PRIMARY KEY CLUSTERED ( [name] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]--建立日志表CREATE TABLE [dbo].[log]( [LoginLog] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [name] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL, CONSTRAINT [PK_log] PRIMARY KEY CLUSTERED ( [name] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[log] WITH CHECK ADD CONSTRAINT [FK_log_account] FOREIGN KEY([name])REFERENCES [dbo].[account] ([name])GOALTER TABLE [dbo].[log] CHECK CONSTRAINT [FK_log_account]SQLserver_存储过程_登录日志.sql--SQLserver中，使用存储过程的方式，实现登录日志。CREATE PROCEDURE login @name_param nchar(10), @password_param intASDeclare@var_name nchar(10), @var_password int, @var_message nchar(10);BEGIN SET NOCOUNT ON; -- Insert statements for procedure here select @var_password = password from [dbo].[account] where name = @name_param; if @var_password = @password_param set @var_message = '登陆成功'; insert into [dbo].[log](LoginLog, name) values (@var_message, @var_name); if @var_password <> @password_param set @var_message = '登陆失败'; insert into [dbo].[log](LoginLog, name) values (@var_message, @var_name); commit;ENDGO--Drop Procedure login--GoSQLserver_触发器_登录日志.sqlCreate Trigger login2 On [dbo].[log] --在log表中创建触发器FOR insert ,replace --为什么事件触发As declare @var_LoginLog nvarchar(50)declare @var_name nchar(10)select @var_LoginLog =[dbo].[log].LoginLog from [dbo].[log]select @var_name =[dbo].[log].name from [dbo].[log]begin --事件触发后所要做的事情 if(@var_LoginLog = '登陆成功') Update [dbo].[account] Set lastLanded = GETDATE() where name = @var_nameend go实验结论
1.SQL Server与Oracle在定义变量方式与变量类型上存在差别
2.给自定义的临时变量赋值语句也存在差别
select password INTO var_password from "SCOTT"."ACCOUNT" where name = name_param;(Oracleb版)
select @var_password = password from [dbo].[account] where name = @name_param;( SQL Server版)
3.if-else语句格式和创建触发器的格式都有微小差别
4.存储过程可以通过程序或其他过程的调用进行执行；触发器虽然也算一种存储过程，但一般要靠insert,update,delete等操作的发生作为触发事件才被调用