Oracle-PLSQL编程

码仆的逆袭

已于 2023-11-04 17:54:19 修改

阅读量624

点赞数

分类专栏：数据库文章标签： oracle 数据库 sql

于 2022-08-26 14:47:02 首次发布

本文链接：https://blog.csdn.net/qq_34191426/article/details/126543227

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

🍓PLSQL编程

声明变量

	--格式：
	declare
		变量名1 变量类型;
		变量名2 变量类型 := 初始值;
		变量名3 T_TABLE_NAME.COLUMN%TYPE;
	--demo
	DECLARE
	 	a varchar2(10);
		i VARCHAR2(10) :='张三';
		name T_MY_USERS.NAME%TYPE;

输出

	--格式
	dbms_output.put_line(变量名);
	
	--demo1 业务逻辑一般都是写在begin ... end中的
	BEGIN
		dbms_output.put_line(i);
	END;
	
	--demo2 
	DECLARE
		--将表格中某个列的类型声明给变量
		vsal T_TABLE_NAME.TOTAL%TYPE;
	BEGIN
		--将查询的结果赋值给变量
		SELECT total into vsal from T_TABLE_NAME WHERE kkbh = 1;
		dbms_output.put_line(vsal);
	END;
	
	--demo3
	DECLARE
		--将表格中的一条记录类型声明给变量
		vsal T_TABLE_NAME%rowtype;
	BEGIN
		--将查询的结果赋值给变量
		SELECT * into vsal from T_TABLE_NAME where kkbh = 1;
		--使用 || 拼接数据
		dbms_output.put_line('姓名：' ||vsal.NAME ||'日期：' || vsal.day||'总量：'||vsal.TOTAL);
	END;

条件判断：

	--格式
	if 条件 then
	
	elsif 条件 then

	else

	end if;

	--demo
	DECLARE
		age number := 20;
	BEGIN
		if age < 18 THEN
			dbms_output.put_line('小孩');
		elsif age > 18 and age <= 24 then
			dbms_output.put_line('年轻人');
		elsif age > 24 and age <= 40 then
			dbms_output.put_line('老司机');
		else
			dbms_output.put_line('老年人');
		end if;
	END;

循环：

--while循环
DECLARE
	i NUMBER := 1;
BEGIN
	while i <= 10 loop
		dbms_output.put_line(i);
		i := i + 1;
	end loop;
END;

--for循环
--顺序输出
BEGIN
	for i in 1..10 loop
		dbms_output.put_line(i);
	end loop;
END;
--倒序输出
BEGIN
	for i in reverse 1..10 loop
		dbms_output.put_line(i);
	end loop;
END;
--查询列表遍历
BEGIN
	for cur in (select * from T_TABLE_NAME where 1=1) loop
		dbms_output.put_line(cur.name);
		dbms_output.put_line(cur.age);
	end loop;
END;


--loop循环
DECLARE
	i number :=1;
BEGIN
	loop
		--当i > 10的时候退出
		exit WHEN i > 10;
		dbms_output.put_line(i);
		i := i + 1;
	end loop;
end;

🍓游标

游标主要是用来操作结果集的，相当于JDBC中的ResultSet
开发步骤：

声明游标
打开游标
从游标中取数据
关闭游标

	--demo无参数的游标
	DECLARE
		--1.声明游标
		cursor vrows is select * from T_TABLE_NAME;
		--声明变量的类型为表中的一条记录
		vrow T_TABLE_NAME%rowtype;
	BEGIN
		--2.打开游标
		open vrows;
		loop
			--3.从游标中取数据
			fetch vrows into vrow;
			--判断是否能从游标中取出数据
			exit when vrows%notfound;
			dbms_output.put_line('姓名：'||vrow.NAME||'日期：'||vrow.DAY||'总量：'||vrow.TOTAL);
		end loop;
		--4.关闭游标
		close vrows;
	END;
	
	--demo有参数的游标
	DECLARE
		--1.声明游标
		cursor vrows(bh number) is select * from T_TABLE_NAME where kkbh = bh;
		--声明变量的类型为表中的一条记录
		vrow T_TABLE_NAME%rowtype;
	BEGIN
		--2.打开游标
		open vrows(1);
		loop
			--3.从游标中取数据
			fetch vrows into vrow;
			--判断是否能从游标中取出数据
			exit when vrows%notfound;
			dbms_output.put_line('姓名：'||vrow.NAME||'日期：'||vrow.DAY||'总量：'||vrow.TOTAL);
		end loop;
		--4.关闭游标
		close vrows;
	END;

🍒系统引用游标

	--demo
	DECLARE
		--区别
		vrows sys_refcursor;
		vrow T_TABLE_NAME%rowtype;
	BEGIN
		--区别
		open vrows for SELECT * from T_TABLE_NAME;
		loop
			fetch vrows into vrow;
			exit when vrows%notfound;
			dbms_output.put_line('姓名：'||vrow.NAME||'日期：'||vrow.DAY||'总量：'||vrow.TOTAL);
		end loop;
		--4.关闭游标
		close vrows;
	end;

🍒使用for循环遍历游标

使用for循环不需要声明记录型变量，不需要打开游标，不需要关闭游标

	DECLARE
		cursor vrows is select * from T_TABLE_NAME;
	begin
		for vrow in vrows loop
			dbms_output.put_line('姓名：'||vrow.NAME||'日期：'||vrow.DAY||'总量：'||vrow.TOTAL);
		end loop;
	END;

🍓异常

🍒Oracle中常见的异常

zero_divide：除零异常
value_error：类型转换异常
too_many_rows：查询出多行记录，但是赋值给了rowtype记录一行数据的变量
no_data_found：没有找到数据

🍒异常处理的方式

	--格式：
	declare
		声明变量
	begin
		业务逻辑
	exception
		处理异常
		when 异常1 then
			...
		when 异常2 then
			...
		when others then
			...处理其他异常
	end;	

	--demo：
	declare
		vi number;
	begin
		vi := 'aaa';
	exception
		when zero_divide then
			dbms_output.put_line('发生了除零异常');
		when others then
			dbms_output.put_line('发生了其他异常'||sqlerrm);
	end;

🍒自定义异常

declare 
	cursor vrows is select * from T_TABLE_NAME where KKBH = 999;
	vrow T_TABLE_NAME%rowtype;
	--自定义异常
	no_info exception;
begin
	open vrows;
	fetch vrows into vrow;
	if vrows%notfound then
		--抛出自定义异常
		raise no_info;
	end if;
	close vrows;
exception
	--输出自定义异常
	when no_info then
		dbms_output.put_line('输出自定义异常');
end;

🍓存储过程

存储过程实际上是封装在服务器上的一段PLSQL代码片段，已经编译好的代码。客户端调用存储过程，执行的效率就会非常高效。

	create or replace procedure 存储过程的名称(参数名 in|out 参数类型,参数名 in|out 参数类型...)is 
	声明变量
	begin
	业务逻辑
	end;

demo:

--创建存储过程
create or replace procedure proc_updatesal(kh in number,addTotal in number) 
is
tota number;
begin
	select total into tota from T_TABLE_NAME where kkbh = kh;
	dbms_output.put_line('增加前的总量'||tota);
	update T_TABLE_NAME set total = tota + addTotal where KKBH = kh;
	--提交
	commit;
	select total into tota from T_TABLE_NAME where KKBH = kh;
	dbms_output.put_line('增加后的总量'||tota);
end;

--调用存储过程方式1
call proc_updatesal(1,100);

--调用存储过程方式2
begin
	proc_updatesal(1,100);
end;

🍓存储函数

存储函数实际上是封装在服务器上的一段PLSQL代码片段，已经编译好的代码。

--格式
create or replace function 存储函数的名称(参数名 参数类型 数据类型,参数名 参数类型 数据类型...) return 返回值类型 is
声明变量
begin
业务逻辑
return 返回值;
end;

存储过程和函数的区别：

通常情况下，函数存在的意义是给存储过程调用的，允许存储函数调用存储过程的
函数可以在sql语句里面直接调用
函数有返回值，存储过程没有返回值，存储过程的返回值在参数里，存储过程的入参有输入和输出两种类型

create or replace function func_getsal(kb number) return number is
 tota number;
begin
	select total into tota from T_TABLE_NAME where KKBH = kb;
	return tota;
end;
--函数调用方式1
declare
	tota number;
begin
	tota := func_getsal(1);
	dbms_output.put_line(tota);
end;
--函数调用方式2
select t.*,func_getsal(t.KKBH) as total from T_TABLE_NAME t;

🍓JDBC调用

	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static String username = "user_name";
    private static String password = "123";
	private static List<Connection> conns = new ArrayList<Connection>();
	private static int poolSize = 5;
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			for(int i = 0; i < poolSize; i++){
				Connection conn = DriverManager.getConnection(url, username, password);
				conns.add(conn);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List<Map<String, Object>> select(String sql){
		Connection conn = getConnection();
		List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Map<String,Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for(int i = 0; i < rsmd.getColumnCount();i++) {
					String name = rsmd.getColumnName(i+1).toLowerCase();
					map.put(name, rs.getObject(name));
				}
				maps.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps != null) {
					ps.close();
				}
				if(rs != null) {
					rs.close();
				}
				conns.add(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return maps;
	}
	
	public static List<Map<String, Object>> selectByPages(String sql,String startNum,String endNum){
		Connection conn = getConnection();
		sql = "select * from (select t.*,ROWNUM as rowno from (" + sql +") t where ROWNUM <= " + endNum + ") s where s.rowno >= " + startNum;
		List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Map<String,Object> map = new HashMap<String, Object>();
				ResultSetMetaData rsmd = rs.getMetaData();
				for(int i = 0; i < rsmd.getColumnCount();i++) {
					String name = rsmd.getColumnName(i+1).toLowerCase();
					map.put(name, rs.getObject(name));
				}
				maps.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps != null) {
					ps.close();
				}
				if(rs != null) {
					rs.close();
				}
				conns.add(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return maps;
	}

	public static Object selectOne(String sql) {
		Object obj = null;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if( rs.next() ){
				obj = rs.getObject(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps != null) {
					ps.close();
				}
				if(rs != null) {
					rs.close();
				}
				conns.add(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return obj;
	}
	
	public int update(String sql){
		Connection conn = getConnection();
		int status = 0;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			status = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps != null) {
					ps.close();
				}
				conns.add(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return status;
	}
	
	//批处理
	public int[] updatePCL(String sql,List<List<Object>> list){
		Connection conn = getConnection();
		int[] status = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < list.size();i++){
				List<Object> list2 = list.get(i);
				for(int j = 0 ; j < list2.size();j++){
					ps.setObject(j+1, list2.get(j));
				}
				ps.addBatch();
			}
			status = ps.executeBatch();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if(ps != null) {
					ps.close();
				}
				conns.add(conn);
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return status;
	}

	//获取连接
	private static Connection getConnection(){
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			if( conns.size() > 0){
				conn = conns.remove(0);
			}
			String sql = "select 1 from dual";
			ps = conn.prepareStatement(sql);
		} catch(Exception e) {
			try{
				conn = DriverManager.getConnection(url, username, password);
			} catch(Exception e2){
				e2.printStackTrace();
			}
		}finally{
			try{
				if(ps != null) {
					ps.close();
				}
			}catch(Exception e2){
				e2.printStackTrace();
			}
		}
		return conn;
	}

	//inObj传入输入参数，outObj传入输出参数的类型，把输出参数写在前面，因为存储函数的输出参数在前面
    public List runSP(String sql,int[] outObj,Object[] inObj) {
    	//收集返回值
        List list = new ArrayList<>();
      	//调用连接
        oracle();
        try{
        	//准备sql，prepareCall，不是prepareStatement
            CallableStatement statement = con.prepareCall(sql);
            //传入输出参数类型
            for(int i = 0;i< outObj.length;i++){
                statement.registerOutParameter(i +1,outObj[i]);
            }
            //传入输入参数
            for(int i = 0;i < inObj.length;i++){
                statement.setObject(outObj.length + i+1,inObj[i]);
            }
            //执行sql
            statement.execute();
            //收集结果
            for(int i = 0;i< outObj.length;i++){
                list.add(statement.getObject(i +1));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    //调用存储过程
	
	//调用存储函数

🍒JDBC调用存储过程

先在数据库中执行下面语句，创建存储过程

	create or replace procedure proc_updatesal(tota out number,kh in number,addTotal in number) 
	is
	begin
		select total into tota from T_TABLE_NAME where kkbh = kh;
		update T_TABLE_NAME set total = tota + addTotal where KKBH = kh;
		commit;
	end;

调用

	public static void main(String[] args) {
		//调用存储过程的sql，注意加上{}
        String sql = "{call proc_updatesal(?,?,?)}";
        //输出参数类型
        int[] outObj = {OracleTypes.NUMBER};
        //输入参数
        Object[] inObj = {1,100};
        DBUtil util = new DBUtil();
        //调用
        List list = util.runSP(sql, outObj,inObj);
        System.out.println(list.toString());
    }

🍒JDBC调用存储函数

先在数据库中执行下面语句，创建存储函数

create or replace function func_getsal(kb number) return number is
	tota number;
begin
	select total into tota from T_TABLE_NAME where KKBH = kb;
	return tota;
end;

调用

	public static void main(String[] args) {
		//注意：这边没有 :=
        String sql = "{? = call func_getsal(?)}";
        Object[] inObj = {1};
        int[] outObj = {OracleTypes.NUMBER};
        DBUtil util = new DBUtil();
        List list = util.runSP(sql, outObj,inObj);
        System.out.println(list.toString());
    }

🍓触发器（不推荐使用）

🍒语句级触发器

当用户执行了 insert | update | delete 这些操作之前或者之后，可以触发一系列的业务逻辑
作用：

数据校验

	--格式
	create or replace triger 触发器的名称
	before | after
	insert | update | delete
	on 表名
	declare
	begin
	end;

demo1：
显示插入记录

create or replace trigger tri_test1
after
insert
on T_TABLE_NAME
declare
	dayTime VARCHAR2(20);
begin
	select to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')	into dayTime from dual;
	dbms_output.put_line(dayTime||'插入一条数据');
end;

demo2：
设置插入条件

create or replace trigger tri_test2
before
insert
on T_TABLE_NAME
declare
	vday VARCHAR2(10);
begin
	SELECT trim(to_char(sysdate,'day')) into vday from dual;
	if vday = 'sunday' then
		dbms_output.put_line('不能插入');
		--抛出系统异常，使系统崩溃，不能插入
		raise_application_error(-20001,'不能插入');
	end if;
end;

🍒行级触发器

	--格式
	create or replace triger 触发器的名称
	before | after
	insert | update | delete
	on 表名
	--行级触发器的标志
	for each row
	declare
	begin
	end;

demo1

create or replace trigger tri_test3
before
update
on T_TABLE_NAME
for each row
declare
begin
	--:old和:new分别为行级触发器的隐藏对象，代表着旧记录和新记录
	if :old.total > :new.total then
		raise_application_error(-20002,'旧总数不能大于新总数');
	end if;
end;

demo2
级联插入

create or replace trigger tri_test4
after
insert
on T_TABLE_NAME
for each row
declare
begin
	--级联插入相关联的表
	insert into T_TRUE_TJRESULT values(:new.kkbh,to_char(sysdate,'yyyy-mm-dd'),TO_CHAR(sysdate,'HH24'),0);
end;

demo3
模拟 mysql 的ID自增长的触发器

create sequence seq_pid;

create or replace trigger tri_add_pid
before
insert
on T_TABLE_NAME
for each row
declare
begin
	select  seq_pid into :new.pid from dual;
end;

🍒行级触发器与语句级触发器的区别

行级触发器在语句级触发器的基础上加了一个 for each row
行级触发器在 insert update delete的操作影响多少行就会触发多少次，而语句级触发器不管你影响多少行，只会执行一次
行级触发器有两个隐藏对象
:old 代表旧记录，更新前的记录
:new代表新纪录

🍓Oracle定时任务

--创建存储过程
create or replace procedure clearStudents as 
begin
 delete from T_TABLE_NAME where round(to_number(sysdate - rksj)) > 30;
 commit;
end;

--创建定时任务执行存储过程
--自动生成任务号，类型为number
declare jobno number;
begin
 dmbs_job.submit(
 	--将任务号赋值给任务job
	job => jobno,
	-- 将需要执行的存储过程名称，名称后面需要加“;”赋值给what
	what => 'clearStudents;',
	--需要执行的时间,sysdate表示立即执行
	next_date => sysdate,
	--每次需要执行的时间
	interval => 'sysdate + 1'
 );
end;

码仆的逆袭

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Oracle-PLSQL编程

-自定义异常 no_info exception;if vrows % notfound then --抛出自定义异常 raise no_info;end if;exception --输出自定义异常 when no_info then dbms_output . put_line('输出自定义异常');end;
复制链接

扫一扫