Oracle 数据库存储过程

1、银行转账

use master
go
if exists (select * from sysdatabases where name ='BankDB' )
 drop database BankDB
 go
create database BankDB
go
use BankDB
go
create table bank
(
    name nvarchar(50) primary key,
    money int not null
)
go 

insert into bank values ('张三',1000)
insert into bank values ('李四',0)
insert into bank values ('王五',15000)
insert into bank values ('赵六',1000)
insert into bank values ('孙七',8000)

select * from bank
go


if exists (select * from sysobjects where name ='proc_bank' )
drop proc proc_bank
go
create proc proc_bank
  @from nvarchar(50),
  @to nvarchar(50),
  @money int
as
    if(@money<0)
        begin
            raiserror('不可以小于0',16,1) 
        end
    declare @err int 
    set @err = 0
    begin transaction
    update bank set money = money-@money where name = @from  
    set @err += @@ERROR
    update bank set money = money+@money where name = @to
    set @err += @@ERROR
    if(@err >0) 
        rollback transaction
    else
       commit transaction
       go
exec proc_bank '张三','李四',1000
go

2、学员信息

use master 
go 
if exists(select * from sysdatabases where name = 'MySchool')
  drop database MySchool
  go
  create database MySchool
go

use MySchool
go

create table Stu
(
    id int primary key identity(1,1),
    gradeId nvarchar(50) not null,
    name nvarchar(50) not null unique,
    sex bit not null,
    age int not null check (age>=0 and age <=100),
    hobby nvarchar(50) not null
)

insert into Stu values(1,'张三',0,10,'篮球')
insert into Stu values(2,'李四',1,20,'篮球')
insert into Stu values(3,'王五',0,55,'篮球')
insert into Stu values(2,'赵六',1,44,'篮球')
insert into Stu values(1,'孙七',0,33,'篮球')
 go
 select * from Stu
  go

--要求全部使用存储过程
--1.查询全部学员信息（不带任何参数）
if exists(select * from sysobjects where name = 'Proc_Stu')
  drop proc Proc_Stu
go
create proc Proc_Stu
as
    select * from Stu
go
exec Proc_Stu

--2.根据学号查询学员信息（带输入参数）
if exists(select * from sysobjects where name = 'Proc_Stu')
  drop proc Proc_Stu
go
create proc Proc_Stu
@id int 
as
    select * from Stu  where id = @id
go
exec Proc_Stu  1


--3.根据学号查询学员年龄（使用带输出参数 print @age）
if exists(select * from sysobjects where name = 'Proc_Stu')
  drop proc Proc_Stu
go
create proc Proc_Stu
@id int,                    
@age int output
as
    select @age =  age  from Stu  where id = @id
go
declare @age int  
exec Proc_Stu  1,@age output
print @age