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出,使用场景为计算变量