Oracle 数据库存储过程

- 日理万妓 2022-07-29 11:46 193阅读 0赞

**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