Oracle存储过程

最新推荐文章于 2024-04-02 12:41:14 发布
最新推荐文章于 2024-04-02 12:41:14 发布
阅读量2.4k 收藏 54
点赞数 10
分类专栏： Oracle 文章标签： 存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_26565861/article/details/90292228
版权

什么是存储过程

存储过程是一组为了完成特定功能的SQL语句，经编译后存在数据库，存储过程是数据库中的一个重要对象。

ps:有人说：任何一个设计良好的数据库应用程序都应该用到存储过程，我觉得这个不一定，其实很多互联网公司的WEB应用程序都没有用到存储过程，难道就能说他们的应用程序设计的不好吗？现实中，银行、金融相关的公司使用存储过程多一些。

存储过程的优缺点

优点

  1. 效率高：创建的时候就会编译，调用时不需要重新编译，而直接写SQL每次执行都先编译再执行。
  2. 安全：与直接写SQL语句相比，存储过程不存在SQL注入的问题。可以向用户授予存储过程的访问权限，而不是直接授予表的访问权限，控制对特定数据的访问。
  3. 降低网络流量：存储过程直接存储在数据库中，所以不会产生大量的SQL代码流量。

缺点

  1. 可移植性很差：应用程序更换其他数据库，需要将原有的存储过程重新写一遍，并且如果你的存储过程中包含大量的业务逻辑，这样就会更加麻烦。
  2. 代码可复用性很差：面向对象的思维在这里完全没有用，两个很相近的功能，需要写两个存储过程，虽然可以相互调用，但是不能利用继承等面向对象的功能。

语法

create or replace procedure <存储过程名>
[(
参数列表：p_name1 in <输入参数数据类型>,p_name2 out <输出参数数据类型>
,p_name3 in out <参数数据类型>
)]
as或is
[定义变量：v_name1 <变量数据类型>]
begin
  <执行特定的操作或者任务，可以进行增删改查>
  end;

注意

如果没有参数（）要去掉，存储过程主体不能为空（begin和end之间不能什么都不做）

说明

  1. in表示输入参数，out表示输出参数，可以同时使用in和out表示既是输入参数又是输出参数。
  2. 声明变量时可以使用 列名+%type 来声明类型
  3. 存储过程主体做select查询时，必须使用into将结果集保存到变量中
  4. :=表示赋值操作

DEMO

登录Oracle数据库scott用户，初始密码tigger

1.定义一个存储过程，用来查询指定部门的员工的人数和平均工资，如果部门不存在，就引发一个自定义异常，显示“部门编号不存在！”(部门编号作为存储过程的输入参数，在调用存储过程前由用户输入，该部门员工的人数和平均工资需要返回)

创建

create or replace procedure proc1(
p_deptno in emp.deptno%type,
p_count_emp out number ,
p_avg_sal out emp.sal%type
)
is
v_row_num number:=0;
error1 exception;
begin
  select count(*) into p_count_emp from emp e where e.deptno=p_deptno;
  if p_count_emp!=0  then
  select avg(e.sal) into p_avg_sal from emp e where e.deptno=p_deptno;
  else raise error1;
  end if;
  exception when error1 then
    dbms_output.put_line('部门编号不存在');
end;

语句块调用

declare
a emp.deptno%type;
b number:=0;
c emp.sal%type:=0;
begin
 a:=&部门编号;
 proc1(a,b,c);
 dbms_output.put_line('部门编号为'||a||
  '的员工人数为' ||b||'，平均工资为：'||
 c);
 end;

2.存储过程相互调用

创建

--根据员工编号删除员工
create or replace procedure proc3
(p_empno in emp.empno%type
)
as
begin
  delete from emp e where e.empno=p_empno;
  commit;
  end;

--首先调用上面的那个存储过程，然后用输出参数返回剩下的员工人数
create or replace procedure proc4(
p_empno in emp.empno%type,
p_nums_afterdelete out number)
as
begin
  proc3(p_empno);
  select count(*) into p_nums_afterdelete from emp;
  end;

语句块调用

declare
p_empno emp.empno%type:=&请输入员工编号;
p_nums_afterdelete number:=0;
begin
  proc4(p_empno,p_nums_afterdelete);
  dbms_output.put_line('已成功删除员工编号为'||p_empno||'的员工'
  ||',当前还剩的员工总数为'||p_nums_afterdelete);
  end;

3.使用call关键字直接调用存储过程

create or replace procedure proc2(a in number,b in number)
as 
c number;
begin
  c :=a+b;
  dbms_output.put_line(c);
  end;
  
call proc2(1,1);

JAVA调用存储过程

首先需要下载classes12.jar、ojdbc14.jar，并导入到自己的工程里。

插入数据

package java_procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//只需ojdbcjar包就行
/*

--java调用存储过程
create table tb_student(
stu_id varchar2(20),
stu_name varchar2(20)
);
--①没有返回值的存储过程
create or replace procedure TESTA(
p_stu_id   in tb_student.stu_id%type,
p_stu_name in tb_student.stu_name%type) 
as
begin
  insert into tb_student
    (stu_id, stu_name)
  values
    (p_stu_id, p_stu_name);
end TESTA;
/

 */
public class TestProcedureOne {

	public static void main(String[] args) {
		CallableStatement cs=null;
		Connection conn=null;
		String driver="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		String userName="a3";
		String pwd="a123";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,userName,pwd);
			cs=conn.prepareCall("{call testa(?,?)}");
			cs.setString(1,"001");
			cs.setString(2, "zhangsan");
			cs.execute();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
				try {
					if (cs!=null) {
					cs.close();
			}
					if (conn!=null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}

条件查询

package java_procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


//只需ojdbcjar包就行
/*
--②有返回值的存储过程
CREATE OR REPLACE PROCEDURE TESTB(
p_stu_id   in tb_student.stu_id%type,
p_stu_name out tb_student.stu_name%type) 
 AS
BEGIN
  SELECT stu_name INTO p_stu_name FROM tb_student 
  WHERE stu_id = p_stu_id;
END TESTB;
/
 */
public class TestProcedureTwo {

	public static void main(String[] args) {
		CallableStatement cs=null;
		Connection conn=null;
		String driver="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		String userName="a3";
		String pwd="a123";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,userName,pwd);
			cs=conn.prepareCall("{call testb(?,?)}");
			cs.setString(1,"001");
			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
			//registerOutParameter()方法的第二个参数：如果是MySQL的驱动，使用Types.VARCHAR
			cs.execute();
			String stuName=cs.getString(2);
			System.out.println(stuName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
				try {
					if (cs!=null) {
					cs.close();
			}
					if (conn!=null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}

查询所有

package java_procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;



//要用classes12.jar，ojdbcjar包有bug
/*
--返回一个列表的存储过程
--建一个程序包
CREATE OR REPLACE PACKAGE TESTPACKAGE  AS
 TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
--建立存储过程
CREATE OR REPLACE PROCEDURE TESTC(
p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
    OPEN p_CURSOR FOR SELECT * FROM tb_student;
END TESTC;

 */
public class TestProcedureThree {

	public static void main(String[] args) {
		CallableStatement cs=null;
		Connection conn=null;
		ResultSet rs=null;
		String driver="oracle.jdbc.driver.OracleDriver";
		String url="jdbc:oracle:thin:@localhost:1521:orcl";
		String userName="a3";
		String pwd="a123";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,userName,pwd);
			cs=conn.prepareCall("{call testc(?)}");
			cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
			cs.execute();
			rs=(ResultSet)cs.getObject(1);
			//上面一行代码可以这样写: rs=((OracleCallableStatement)cs).getCursor(1);
			while(rs.next()){
				System.out.println("ID:"+rs.getString(1)+"\t"+"姓名："+rs.getString(2));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
				try {
					if (rs!=null) {
					rs.close();
			        }
					if (cs!=null) {
						cs.close();
				        }
					if (conn!=null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}

存储过程返回游标类型

  • 创建新表，使用bulk collect into
create or replace procedure getHighSalPerson(p_emp out sys_refcursor)
is
v_avg_sal number;
begin
  select avg(sal) into v_avg_sal from emp ;
  open p_emp for select empno,ename,job,sal from emp where sal>=v_avg_sal;
end;

create table t_emp_high_sal(empno varchar2(100 byte),ename varchar2(100 byte),job varchar2(100 byte),sal number);

declare
type row_type is table of  t_emp_high_sal%rowtype;
emp_cursor sys_refcursor;
emp_row row_type;
begin
  getHighSalPerson(emp_cursor);
  fetch emp_cursor bulk collect into emp_row;
  for i in 1..emp_row.count loop
    dbms_output.put_line('员工编号:'||emp_row(i).empno||',员工姓名：'||emp_row(i).ename||',职位：'||emp_row(i).job||',工资：'||emp_row(i).sal);
  end loop;
  close emp_cursor;
end;
  • 定义包类型
create or replace procedure getHighSalPerson(p_emp out getHighsalperson_package.type_cursor)
is
v_avg_sal number;
begin
  select avg(sal) into v_avg_sal from emp ;
  open p_emp for select empno,ename,job,sal from emp where sal>=v_avg_sal;
end;

create or replace package getHighsalperson_package
is
       type type_cursor is ref cursor;
       type type_record is record(empno varchar2(100 byte),ename varchar2(100 byte),job varchar2(100 byte),sal number);
end;

declare
emp_cursor getHighsalperson_package.type_cursor;
emp_row getHighsalperson_package.type_record;
begin
  getHighSalPerson(emp_cursor);
  loop fetch emp_cursor into emp_row;
  exit when emp_cursor%notfound;
  dbms_output.put_line('员工编号:'||emp_row.empno||',员工姓名：'||emp_row.ename||',职位：'||emp_row.job||',工资：'||emp_row.sal);
  end loop;
  close emp_cursor;
end;
  • 直接用空结果集的游标
create or replace procedure getHighSalPerson(p_emp out sys_refcursor)
is
v_avg_sal number;
begin
  select avg(sal) into v_avg_sal from emp ;
  open p_emp for select empno,ename,job,sal from emp where sal>=v_avg_sal;
end;

declare
cursor emp_cur is select empno,ename,job,sal from emp where 1=2;
emp_cursor sys_refcursor;
emp_row emp_cur%rowtype;
begin
  getHighSalPerson(emp_cursor);
  loop fetch emp_cursor into emp_row;
  exit when emp_cursor%notfound;
  dbms_output.put_line('员工编号:'||emp_row.empno||',员工姓名：'||emp_row.ename||',职位：'||emp_row.job||',工资：'||emp_row.sal);
  end loop;
  close emp_cursor;
end;

 

public static void m
关注
  • 10
    点赞
  • 踩
  • 54
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle 存储过程
11-20
oracle 存储过程，关于存储过程的，函数，包等语法介绍
oracle 存储过程加密的方法
09-11
以下将详细介绍Oracle存储过程的加密方法。 首先，确保你的环境满足加密所需的条件。在这个例子中，我们看到配置了一个Oracle 8i R2 (8.1.7) for NT 企业版的数据库，安装在C:\ORACLE路径下。加密过程通常需要对...
Oracle存储过程案例详解
jackleeonlyone的博客
08-09 1万+
Oracle存储过程案例详解
Oracle存储过程常用技巧，入职阿里啦
最新发布
2401_84007015的博客
04-02 560
Java架构进阶面试及知识点文档笔记这份文档共498页，其中包括Java集合，并发编程，JVM，Dubbo,Redis，Spring全家桶，MySQL，Kafka等面试解析及知识点整理Java分布式高级面试问题解析文档其中都是包括分布式的面试问题解析，内容有分布式消息队列，Redis缓存，分库分表，微服务架构，分布式高可用，读写分离等等！互联网Java程序员面试必备问题解析及文档学习笔记Java架构进阶视频解析合集链图片转存中…(img-lS153j6B-1712032862411)]
Oracle 数据库（十四）—— Oracle 存储过程、函数
Jsundoku的博客
04-15 2453
存储过程是一种命名的PL/SQL程序块，它既可以没有参数，也可以有若干个输入、输出参数，甚至可以有多个既作输入又作输出的参数，但它通常没有返回值。存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL程序块内部被调用。 函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。函数的调用是表达式的一部分，而过程的调用是一条PL/SQL语句。
十、Oracle 存储过程
骑士梦的博客
03-01 1922
一、Oracle 存储过程
Oracle数据库创建存储过程
csdn_db96的博客
06-28 1087
【代码】Oracle数据库创建存储过程。
帆软报表Oracle存储过程解决storeParameter1参数试用插件
12-12
帆软报表调用Oracle存储过程如果存储过程定义中没有参数，但是设计器中缺弹出一个storeParameter1参数的解决插件
oracle存储过程.ppt
07-08
oracle存储过程详细解说
润乾报表调用oracle存储过程案例说明
03-15
在实际的业务场景中，有时我们需要调用数据库中的存储过程来获取或处理数据，本篇将详细介绍如何在润乾报表中调用Oracle存储过程。 首先，我们要在Oracle数据库中创建存储过程。以下是一个简单的存储过程示例，名为...
oracle数据库 存储过程
01-12
oracle数据库 存储过程oracle数据库 存储过程
oracle 存储过程的基本语法
东方咖啡屋
06-23 2100
oracle 存储过程的基本语法  1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字(    参数1 IN NUMBER,    参数2 IN NUMBER) IS变量1 INTEGER :=0;变量2 DATE;BEGINEND 存储过程名字2.SELECT INTO STATEMENT  将select查询的结果存入到变量中，可
oracle如何创建存储过程和调用
weixin_30344131的博客
04-16 996
oracle存储过程的创建语法 create or replace procedure 存储过程名称 ( --定义输入、输出参数-- 参数名1 in 参数类型, 参数名2 in 参数类型, 参数名3 in 参数类型, 参数名4 out 参数类型 ) as --定义变量-- --变量名 变量数据类型；如： -- numCount integer; begin ...
Oracle的存储过程
热门推荐
weixin_41697106的博客
05-19 1万+
一、什么是存储过程 存储过程: 百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。 简单的说就是专门干一件事一段sql语句。 可以由数据库自己去调用，也可以由java程序去调用。 在oracle数据库中存储过程是procedure。 二、为什么要写存储过程 1.效率高 存储过程编译一次后，就会存到
SQL Server 存储过程
weixin_34384915的博客
07-19 1547
Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念     存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。     存储过程中可以包含逻辑控...
oracle函数与存储过程中的类型问题---char 与 varchar2
CodePasser的博客
03-27 2263
 select count(*) into v_count from testTable where id0000 = v_id0000;上面这条语句是某一条函数中的语句，其中v_id0000是函数的一个变量，类型为varchar2(20)，而id0000是testTable的一个字段，类型为char(19)，这种情况即使testTable表中有与v_id0000对应的记录，v_count也会是0...
oracle存储过程out类型,oracle存储过程定义结构及注意事项
weixin_39983993的博客
04-09 764
一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常处理。存储过程基本结构：CREATE [ OR REPLACE ] PROCEDURE 存储过程名称(参数名 in 类型, 参...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值