转帐事务及SQL范例编程

 转账事务     

先建立数据环境（帐户表）

 
1.转账事务（使用在存储过程中）

if exists(select name from sysobjects
             where name = 'zhuanzhang' and type='p')
drop procedure zhuanzhang
go   

--建立一个带有参数的转账存储过程
-- @zhOUT 转出帐户帐号
-- @zhIN 转入帐户帐号
--@JinE  金额
create procedure zhuanzhang
@zhOUT varchar(8),@zhIN varchar(8),@JinE int
as

--帐户A向帐户B转账事务

begin transaction

--记录点
save transaction befor_zhuanzhang

--A帐户转出一定金额
declare @xianYou int
select @xianyou=金额 from 帐户表
where 帐户编号=@zhOUT

if @xianyou<@JinE
begin
    print '金额不足！'
    return
end

update 帐户表 set 金额=金额-@JinE
where 帐户编号=@zhOUT

--B帐户转入一定金额
update 帐户表 set 金额=金额+@JinE
where 帐户编号=@zhIN
 
if @@Error<>0 or @@rowcount=0
begin
    rollback transaction befor_zhuanzhang
    print '转账出错错误。'
    return
end

commit transaction 

2.事务测试（执行存储过程zhuanzhang）

1、execute zhuanzhang 'A','B',5000
2、execute zhuanzhang 'C','D',5000
注意结果变化。

http://blog.csdn.net/cnlht/archive/2007/12/19/1953032.aspx
 查询中使用CASE结构     

使用CASE结构进行查询结果分类

例题1：
select zkzh 准考证号,xm 姓名,总成绩=
   case
     when zcj=0 then '不及格'
     when zcj=1 then '及格'
     when zcj=2 then '良好'
     when zcj=3 then '优秀'
     else '出错'
   end
 from ncre26

思考题：
1.如何将sc中grade查询结果分类（优秀-90以上、良好-80以上、及格-60以上、不及格-59及其以下）。
2.如何将性别内的1显示为男，2显示为女。
 数据库事务性操作     

SQL Server 2000 事务操作

1.事务操作实例一

--事务开始
begin transaction
    insert student(sno,sname,ssex,sage,sdept)
       values('200200005','王五','男',20,'IS')
if @@error<>0
   begin
      print '添加学生记录时出错！'
      return
   end
--存储记录点
save transaction befor_insert_score
insert sc
   values('200200003','8',90)
if @@error<>0
   begin
      --回滚操作
      rollback transaction befor_insert_score
      print '添加成绩记录出错！'
      return
   end
commit transaction 

2.事务操作实例二（与上例有什么区别？）

--事务开始
begin transaction
--存储记录点
save transaction befor_insert_student
    insert student(sno,sname,ssex,sage,sdept)
       values('200200005','王五','男',20,'IS')
if @@error<>0
   begin
      print '添加学生纪录时出错！'
      return
   end
save transaction befor_insert_score
insert sc
   values('200200005','8',90)
if @@error<>0
   begin
      --回滚操作
      rollback transaction befor_insert_student
      print '登记记录出错！'
      return
   end
commit transaction

思考题
1.存在帐户表（帐户编号，帐户名，金额）
进行如下操作： 将帐户A的金额3000转入帐户B。
为了保证安全，要求使用事务操作。

2.学生表、课程表和选课表
进行如下操作：取消一门课程。
提示：先删除对应该门课程的选课记录，然后再删除该门课程。（要求使用事务性操作方法）
 C语言中嵌入式SQL语句     

 

实验内容：

掌握SQL Server 2000的预编译程序NSQLPREP.EXE的使用（以课本例题1进行调试）；

实验步骤：

一、数据库环境配置

1、创建xuesheng数据库，建立student表等；

2、关闭sql server 2000服务管理器；

3、将devtools文件夹拷贝到：C:\Program Files\Microsoft SQL Server

4、将Binn文件夹拷贝到：C:\Program Files\Microsoft SQL Server\MSSQL

5、启动服务器；

二、VC++6.0编辑器配置（初始化Vc++环境）

1.工具—>选择—>目录—>Include Files

添加： C:\Program Files\Microsoft SQL Server\devtools\include

并设为第一项

2.选择Library Files

  添加：C:\Program Files\Microsoft SQL Server\devtools\x86lib

并设为第一项

 

三、写程序，预编译，最后在VC++中编译、执行

1、编辑EXEC.sqc文件，保存到：C:\Program Files\Microsoft SQL Server\MSSQL\Binn目录

EXEC.sqc文件如下：

// EXEC.cpp : Defines the entry point for the console application.
//

#include <stdio.h>
#include <stdlib.h>

EXEC SQL BEGIN DECLARE SECTION;  /*主变量说明开始*/
 char deptname[64];
 char HSno[64];
 char HSname[64];
 char HSsex[64];
 int  HSage;
 int NEWAGE;
        long SQLCODE;
EXEC SQL END DECLARE SECTION;     /*主变量说明结束*/

EXEC SQL INCLUDE sqlca;               /*定义SQL通信区*/

/*************************************************************************/

int main(void)                           /*C语言主程序开始*/
{
 int  count = 0;
 char  yn;                              /*变量yn代表yes或no*/
 printf("Please choose the department name(CS/MA/IS): ");
 scanf("%s", deptname);                 /*为主变量deptname赋值*/
 EXEC SQL CONNECT TO demin USER
      sa;         /*连接数据库TEST*/
 EXEC SQL DECLARE SX CURSOR FOR   /*定义游标*/
  SELECT Sno, Sname, Ssex, Sage   /*SX对应语句的执行结果*/
  FROM Student
  WHERE SDept = :deptname;
 EXEC SQL OPEN SX;        /*打开游标SX便指向查询结果的第一行*/

for ( ; ; )                       /*用循环结构逐条处理结果集中的记录*/
{
 EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex,:HSage;
      /*推进游标，将当前数据放入主变量*/
if (sqlca->sqlcode != 0)     /* sqlcode != 0,表示操作不成功*/
  break;                /*利用SQLCA中的状态信息决定何时退出循环*/
 if(count++ == 0)             /*如果是第一行的话，先打出行头*/
  printf("\n%-10s %-20s %-10s %-10s\n", "Sno", "Sname", "Ssex", "Sage");
 printf("%-10s %-20s %-10s %-10d\n", HSno, HSname, HSsex, HSage); 
     /*打印查询结果*/
 printf("UPDATE AGE(y/n)?"); /*询问用户是否要更新该学生的年龄*/
 do{                    
    scanf("%c",&yn);
 }
 while(yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
if (yn == 'y' || yn == 'Y')                 /*如果选择更新操作*/
  {
   printf("INPUT NEW AGE:");
   scanf("%d",&NEWAGE);          /*用户输入新年龄到主变量中*/
   EXEC SQL UPDATE Student            /*嵌入式SQL*/
   SET Sage = :NEWAGE
   WHERE CURRENT OF SX ;
  }                 /*对当前游标指向的学生年龄进行更新*/
}

     EXEC SQL CLOSE SX;           /*关闭游标SX不再和查询结果对应*/
 EXEC SQL COMMIT WORK;                           /*提交更新*/
 EXEC SQL DISCONNECT TEST;                  /*断开数据库连接*/
}

2、使用MS-DOS进行预编译：

   1）进到Binn文件下：cd  C:\Program Files\Microsoft SQL Server\MSSQL\Binn

   2）调用预编译程序nsqlprep.exe程序：nsqlprep EXEC

成功编译完成结果：

见附件1

 

3、使用Vc++进行编译，见附件2

4、添加库文件：工程—>设置—>Link—>对象/库模块 

输入SQLakw32.lib Caw32.lib两个文件名。附件3

注意，两个文件之间用空格分开。

5、将动态链接库SQLakw32.dll，sqlaiw32.dll文件拷贝到操作系统目录下的子目录C:\WINDOWS\system32中

6、连接：附件4

7、执行：附件5

附件1：

附件2（VC编译EXEC.C）

附件3（配置添加库文件）

附件4（连接）

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1930960

 游标的应用     

--use sms
--goto
--定义游标
/*
declare 游标名称 cursor
 [local | clobal]
 [forward_only|scroll]
 [static|keyset|dynamic|fast_forward]
 [read_only|scroll_locks|optimistic]
 [type_warning]
 for select 语句
 [for update[for 字段名][,...n]]]
根据帮助理解各项含义
 */
declare cj_cursor cursor
scroll
for
select zkzh 身份证号,cj33 笔试,cj44 上机,zcj 总成绩 from ncre26

--打开游标

open cj_cursor

--从游标中提取一行记录
--由于未指定scroll选项，fetch next是唯一可用的可提取选项
fetch next from cj_cursor
fetch next from cj_cursor
fetch next from cj_cursor
fetch next from cj_cursor
fetch next from cj_cursor
fetch prior from cj_cursor
fetch first from cj_cursor
fetch next from cj_cursor
fetch absolute 3 from cj_cursor
fetch relative -1 from cj_cursor
--察看有几行
--if @@cursor_rows>0
  print '共有'+convert(varchar,@@cursor_rows)
--关闭游标
close cj_cursor
--删除游标
deallocate cj_cursor
 

 触发器的简单应用     

一、定义数据环境：teacher表记录教师情况，sal_log记录teacher表工资修改，添加情况。

create database T
go

use T
go

create table teacher
(
Eno numeric(4) primary key,
EName varchar(8) unique,
pJob varchar(8),
Sal numeric(7,2)
);
go

create table Sal_log
(
eno numeric(4) references teacher(eno),
Sal Numeric(7,2),
uSERNAME CHAR(10),
Date datetime);
go 

二、定义触发器

1.insert_sal触发器，当向teacher表插入新元组时，向sal_log添加一条记录

if exists (select * from sysobjects
            where name='insert_sal' and type='TR')
drop trigger insert_sal

go

--新触发器
Create trigger insert_sal
 on teacher
 for insert as
 set Nocount off
 declare @new_eno numeric(4),
  @new_sal numeric(7,2)
 --if @@rowcount=1
 begin
  select @new_eno=eno,@new_sal=sal from inserted
  insert into sal_log
  values(@new_eno,@new_sal,current_user,current_timestamp)
 end

2.update_sal触发器，当teacher表修改工资时，向sal_log添加修改前和修改后，两条元组。

 Create trigger update_sal
  on teacher
 for update as
        declare @new_sal numeric(7,2),
  @old_sal numeric(7,2),
  @eno numeric(4)
 if @@rowcount=1
 begin
  select @eno=eno,@new_sal=sal from inserted
  select @old_sal=sal from deleted
  if (@new_sal<>@old_sal)
   insert into
   sal_log values(@eno,@old_sal,
   current_user,current_timestamp);
   insert into
   sal_log values(@eno,@new_sal,
   current_user,current_timestamp);
 end

3.定义触发器insert_sal_little，当向teacher添加记录时，检测若是教授，应该把工资最低为4000

if exists (select * from sysobjects
            where name='insert_sal_little' and type='TR')
drop trigger insert_sal_little

go

--新触发器
Create trigger insert_sal_little
 on teacher
 for insert,update as
 set Nocount off
 declare @new_eno numeric(4),
  @new_sal numeric(7,2)
 --if @@rowcount=1
 begin
  select @new_eno=eno,@new_sal=sal from inserted
  if (@new_sal-4000.00<0)
  begin 
   update teacher set sal=4000.00
    where eno=@new_eno
  end
 end

三、检测

--检测inser_salt触发器

insert into teacher values(2112,'Li','教授',6000)
go

select * from teacher
select * from sal_log

--检测update_sal触发器--

update teacher set sal=7300
where eno=2112

select * from teacher
select * from sal_log

--检测inser_sal_littlet触发器

insert into teacher values(2113,'wang','教授',3000)
go

select * from teacher
select * from sal_log