Oracle数据库自定义函数与存储过程

Oracle数据库

Oracle数据库的基本使用

一、plsql语句使用

1.判断某年是否是闰年

declare
  var_1 varchar2(40):='-----请输入:------';
  year number(32,0);
begin
  dbms_output.put_line(var_1);
  year:=&请输入年份;
  if year<0 or year>3000 then
    dbms_output.put_line('年份有误，程序退出');
  elsif (mod(year,4)=0 and mod(year,100)!=0 or(mod(year,400)=0)) then
    dbms_output.put_line(year||'是闰年');
  else
    dbms_output.put_line(year||'不是闰年');
  end if;
end;

2.给定一个百分制的分数，输出相应的等级

90分以上 A级

80~89 B级

70~79 C级

60~69 D级

60分以下 E级

declare
  mark number;
begin
  mark:=&请输入一个分数;
  if mark<0 or mark>100 then
    dbms_output.put_line('分数有误，程序退出');
  end if;
  
  if mark>=90 then
    dbms_output.put_line('这个成绩是 ''A级'' ');
  elsif mark>=80 then
     dbms_output.put_line('这个成绩是 ''B级'' ');
  elsif mark>=70 then
     dbms_output.put_line('这个成绩是 ''C级'' ');
  elsif mark>=60 then
     dbms_output.put_line('这个成绩是 ''D级'' ');
  else
     dbms_output.put_line('这个成绩是 ''E级'' ');
  end if;
end;

3.plsql编写求 1+3+5+7+……+99 的和值

declare
  v_sum number(32):=0;
begin 
  for v_num in 1..100 loop
    if mod(v_num,2)=1 then
      v_sum:=v_sum+v_num;
    end if;
  end loop;
  dbms_output.put_line('1+3+5+7+.....+99='||v_sum);
end;

二、自定义函数

1.创建自定义函数

create function <函数名> (<参数1> in <类型>，<参数2> in <类型> …) return <返回结果的类型> as <pl/sql语句>//in 可有可无

代码案例

--自定义函数fun_max()，求两个数的最大值
create or replace function fun_max(a in number,b in number) return number as
       sign_  number;
begin
  sign_:=sign(a-b);
  if sign_=1 then
    return a;
  else
    return b;
  end if;
end;

2.自定义函数的调用

2.1）用pl/sql调用

--调用自定义函数fun_max()
declare
  v_restur number;
begin
  v_restur:=fun_max(2,3);
  dbms_output.put_line('两个数比较最终结果大的数为:'||v_restur);
end;

2.2)用java代码调用

package com.rj.bd.funs;

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

/**
 * @desc  java代码调用Oracle的自定义函数
 * @author HYZ
 * @time  2021年2月3日
 */
public class TestFunction {
	public static void main01(String[] args) throws
    	ClassNotFoundException, SQLException {
		//1.加载Oracle驱动程序
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//2.指定url
		Connection connection = DriverManager.getConnection("jdbc:oracle:"+
			"thin:@ip地址:1521:PPMSDB","用户名","密码");
		if (!connection.isClosed()) {
			System.out.println("Oracle数据库连接成功！");
			CallableStatement cst = connection.prepareCall("{?=call fun_max(?,?) }");
			cst.registerOutParameter(1, Types.NUMERIC);
			cst.setInt(2, 22);
			cst.setInt(3, 33);
			cst.execute();
			System.out.println(cst.getInt(1));
			cst.close();
			connection.close();
		}
	}
}

3.存储过程(Stored Procedure）

3.1)什么是存储过程

是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

3.2）存储过程优点

• 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
• 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)， 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
• 存储过程可以重复使用,可减少数据库开发人员的工作量
• 安全性高,可设定只有某些用户才具有对指定存储过程的使用权

3.3）存储过程的缺点

• 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
• 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
• 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包、将需要重新编译（不过也可以设置成运行时刻自动编译）。
• 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

ps: 封装在数据库中的pl/sql 可以像pl/sql一样被调用;可以通过添加或者修改参数来达到动态的使用

4.存储过程代码案例与调用方式

4.1）代码案例

--创建存储过程pro_cunchu()
create or replace procedure pro_cunchu(zhuJian_ in varchar2,restr out varchar2 ) as
       zhuJian varchar2(32):=zhuJian_;
       shuLiang number:=0;
begin
  select count(*) into shuLiang from T_USER where USER_ID=zhuJian;
  shuLiang := shuLiang+1;
  restr :=shuLiang||'是加一后的结果';
end;  

4.2）调用方式

4.2.1）在pl/sql中的调用

--案例测试
declare
  v_restr varchar2(40);
begin
  pro_cunchu('8a9084883d8b6be7013d8fdc8d540189',v_restr);
  dbms_output.put_line(v_restr);
end;

4.2.2）在java代码中的调用

package com.rj.bd.pros;

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

public class TestProcedure {

	public static void main01(String[] args) throws ClassNotFoundException, SQLException {
		//1.加载Oracle驱动程序
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//2.指定url
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@IP地"
			+"址:1521:PPMSDB", "用户名", "密码");
		if (!con.isClosed()) {
			System.out.println("Oracle数据库连接成功！");
			CallableStatement cst = con.prepareCall("{call pro_cunchu( ?,? ) }");
			//1 代表第一个问号 "2"代表是给第一个问号赋值 也就是传入的主键值
			cst.setString(1, "2");
			// 2代表第二个参数 ，java.sql.Types.VARCHAR代表是参数的类型 
			cst.registerOutParameter(2, Types.VARCHAR);
			//执行此存储过程
			cst.execute();
			//将执行完的结果 也就第二个问号 的值给打印出来
			System.out.println(cst.getString(2));
			//关闭相关对象
			cst.close();
			con.close();
		}
	
	}
}

5.函数与存储过程的区别

• 封装的语法不同

函数:create function <函数名> (<参数1> in <类型>，<参数2> in <类型> …) return <返回结果的类型> as <pl/sql语句>

存储过程：create procedure <名> (<参数名> in/out <参数类型>,<参数名> in/out <参数类型>… ) as

• 存储过程多进多出或者零出，函数多进1出

• 存储过程里面可以进行增删改查 ，函数不允许

PS:

存储过程: 多进多出或者零出，使用场景为增删改查

函数：多进1出，使用场景为计算变量