存储过程 sql server案例 sql存储过程实例详解

转载

网络小墨 2023-12-01 15:10:31

文章标签 存储过程 sql server案例 java 数据库存储过程数据 文章分类 SQL Server 数据库

例1：

create proc proc_stu
@sname varchar(20),
@pwd varchar(20)
as
select * from ren where sname=@sname and pwd=@pwd
go

查看结果：proc_stu 'admin','admin'

例2：

下面的存储过程实现用户验证的功能，如果不成功，返回0，成功则返回1.

CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUT
AS

IF EXISTS(SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD)
SELECT @LEGAL = 1
ELSE
SELECT @LEGAL = 0

在程序中调用该存储过程，并根据@LEGAL参数的值判断用户是否合法。

例3：一个高效的数据分页的存储过程可以轻松应付百万数据

CREATE PROCEDURE pageTest --用于翻页的测试
--需要把排序字段放在第一列

(
 @FirstID nvarchar(20)=null, --当前页面里的第一条记录的排序字段的值
 @LastID nvarchar(20)=null, --当前页面里的最后一条记录的排序字段的值
 @isNext bit=null, --true 1 :下一页；false 0:上一页
 @allCount int output, --返回总记录数
 @pageSize int output, --返回一页的记录数
 @CurPage int --页号（第几页）0：第一页；-1最后一页。
 ) AS
 if @CurPage=0--表示第一页
 begin
 --统计总记录数
 select @allCount=count(ProductId) from Product_test 

 set @pageSize=10
 --返回第一页的数据
 select top 10 
 ProductId,
 ProductName,
 Introduction 
 from Product_test order by ProductId 
 end else if @CurPage=-1--表示最后一页
 select * from 
 (select top 10 ProductId,
 ProductName,
 Introduction from Product_test order by ProductId desc ) as aa 
 order by ProductId
 else begin 
 if @isNext=1
 --翻到下一页
 select top 10 ProductId,
 ProductName,
 Introduction
 from Product_test where ProductId > @LastID order by ProductId 
 else
 --翻到上一页
 select * from
 (select top 10 ProductId,
 ProductName,
 Introduction
 from Product_test where ProductId < @FirstID order by ProductId desc) as bb order by ProductId
 end

上文中讲到的这三个例子都是sql存储过程比较典型的例子，希望大家好好学习，都能够学到大家各自需要的东西。

原文出处：http://www.codesky.net/article/201103/144215.html

自己练习的：

create table tb_student
(
    id integer,
    name varchar(20),
    chinese number,
    math number
);
insert into tb_student values(1,'小明',90,80);
insert into tb_student values(2,'小王',60,70);
insert into tb_student values(3,'小张',90,70);

select * from tb_student;

--无返回值的存储过程
create or replace procedure xs_proc_no is
begin 
  insert into tb_student values(4,'小李',70,80);
  commit;
end xs_proc_no;
--无参存储过程的调用
declare 
begin
   xs_proc_no;
end;
---带参数的存储过程
create or replace procedure xs_proc(temp_name in varchar2,
                                    temp_num  out number) is
  num_1 number;
  num_2 number;
begin
  select chinese , math
    into num_1, num_2
    from tb_student
   where name = temp_name;
  --dbms_output.put_line(num_1 + num_2);
  temp_num := num_1 + num_2;
end; 
---带参数的存储过程调用
declare 
temp_name varchar2(20);--参数1
temp_num number;--参数2
begin
  temp_name:='小李';--初始化参数值（第二个参数是返回的，可以不用初始化）
  xs_proc(temp_name=>temp_name,temp_num=>temp_num);--执行存储过程
  --或者xs_proc(temp_name,temp_num);
  Dbms_Output.put_line(temp_num);
end;
create table tb_student
(
    id integer,
    name varchar(20),
    chinese number,
    math number
);
insert into tb_student values(1,'小明',90,80);
insert into tb_student values(2,'小王',60,70);
insert into tb_student values(3,'小张',90,70);

select * from tb_student;

--无返回值的存储过程
create or replace procedure xs_proc_no is
begin 
  insert into tb_student values(4,'小李',70,80);
  commit;
end xs_proc_no;
--无参存储过程的调用
declare 
begin
   xs_proc_no;
end;
---带参数的存储过程
create or replace procedure xs_proc(temp_name in varchar2,
                                    temp_num  out number) is
  num_1 number;
  num_2 number;
begin
  select chinese , math
    into num_1, num_2
    from tb_student
   where name = temp_name;
  --dbms_output.put_line(num_1 + num_2);
  temp_num := num_1 + num_2;
end; 
---带参数的存储过程调用
declare 
temp_name varchar2(20);--参数1
temp_num number;--参数2
begin
  temp_name:='小李';--初始化参数值（第二个参数是返回的，可以不用初始化）
  xs_proc(temp_name=>temp_name,temp_num=>temp_num);--执行存储过程
  --或者xs_proc(temp_name,temp_num);
  Dbms_Output.put_line(temp_num);
end;

java中调用存储过程

Ⅰ、不带输出参数情况 过程名称为pro1，参数个数1个，数据类型为整形数据

import  java.sql. * ; 
 public   class  ProcedureNoArgs  
  { 
 public   static   void  main(String args[])  throws  Exception 
 { 
   // 加载Oracle驱动  
   DriverManager.registerDriver( new  oracle.jdbc.driver.OracleDriver()); 
   // 获得Oracle数据库连接  
   Connection conn = DriverManager.getConnection( " jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd " ); 

     // 创建存储过程的对象  
     CallableStatement c = conn.divpareCall( " {call pro1(?)} " ); 
     
     // 给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188  
     c.setInt( 1 , 188 ); 
     
     // 执行Oracle存储过程  
     c.execute(); 
    conn.close(); 
}  
}
import  java.sql. * ; 
 public   class  ProcedureNoArgs  
  { 
 public   static   void  main(String args[])  throws  Exception 
 { 
   // 加载Oracle驱动  
   DriverManager.registerDriver( new  oracle.jdbc.driver.OracleDriver()); 
   // 获得Oracle数据库连接  
   Connection conn = DriverManager.getConnection( " jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd " ); 

     // 创建存储过程的对象  
     CallableStatement c = conn.divpareCall( " {call pro1(?)} " ); 
     
     // 给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188  
     c.setInt( 1 , 188 ); 
     
     // 执行Oracle存储过程  
     c.execute(); 
    conn.close(); 
}  
}

Ⅱ、带输出参数的情况
过程名称为pro2，参数个数2个，数据类型为整形数据，返回值为整形类型

import java.sql.*; 
public class ProcedureWithArgs  
  { 
public static void main(String args[]) throws Exception 
{ 
   //加载Oracle驱动 
   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 
   //获得Oracle数据库连接 
   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd "); 

    //创建Oracle存储过程的对象，调用存储过程 
    CallableStatement c=conn.divpareCall("{call pro2(?,?)}"); 
     
    //给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188 
    c.setInt(1,188); 
    //注册存储过程的第二个参数 
      c.registerOutParameter(2,java.sql.Types.INTEGER);    //执行Oracle存储过程 
    c.execute(); 
      //得到存储过程的输出参数值并打印出来
      System.out.println (c.getInt(2)); 
    conn.close(); 
} 
}
import java.sql.*; 
public class ProcedureWithArgs  
  { 
public static void main(String args[]) throws Exception 
{ 
   //加载Oracle驱动 
   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 
   //获得Oracle数据库连接 
   Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd "); 

    //创建Oracle存储过程的对象，调用存储过程 
    CallableStatement c=conn.divpareCall("{call pro2(?,?)}"); 
     
    //给Oracle存储过程的参数设置值 ，将第一个参数的值设置成188 
    c.setInt(1,188); 
    //注册存储过程的第二个参数 
      c.registerOutParameter(2,java.sql.Types.INTEGER);    //执行Oracle存储过程 
    c.execute(); 
      //得到存储过程的输出参数值并打印出来
      System.out.println (c.getInt(2)); 
    conn.close(); 
} 
}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。