Oracle存储过程学习(附带SpringBoot整合Mybatis连接Oracle调用存储过程)

最新推荐文章于 2024-05-10 11:25:49 发布

立志成为架构师

最新推荐文章于 2024-05-10 11:25:49 发布

阅读量523

点赞数 1

文章标签： oracle java

本文链接：https://blog.csdn.net/qq_43527397/article/details/119222218

版权

Oracle的存储过程即对应JAVA语言函数的概念。可以将服务器的逻辑代码转换到SQL语句中，减少服务器与数据库的数据传输。

--java:System.out.println("");
	BEGIN
		DBMS_OUTPUT.PUT_LINE('');
	END;   --END结束符的分号不可省略！
	/            --结束符,使用SQLPLUS命令行时需要添加/结束符，在PL/SQL中省略。
	SET SERVEROUTPUT ON  --由于DBMS_OUTPUT.PUT_LINE('')是PL/SQL语句，不加这句，打印结果不回返回控制台，只有加上，才有打印结果。

变量声明

	DECLARE
		-- 定义变量
		V_NAME VARCHAR2(20)  := '张三' ;   --直接赋值
	BEGIN
		V_NAME := '李四';      --直接赋值
		SELECT '胡图图' INTO V_NAME FROM DUAL; --语句赋值，DUAL为ORACLE虚拟表
		DBMS_OUTPUT.PUT_LINE('姓名' || V_NAME); --使用||拼接
	END;

引用型变量

	DECLARE
    	V_NAME EMP.ENAME%TYPE; --v_name的类型跟emp表中的ENAME字段的类型一致
    	V_SAL EMP.SAL%TYPE;
	BEGIN
    	SELECT ENAME ,SAL INTO V_NAME,V_SAL FROM  EMP WHERE ENO = 1;--语句赋值，赋多个值时一一对应。
    	DBMS_OUTPUT.put_line(v_name||V_SAL);
    END;

记录型变量

	DECLARE
    	V_EMP EMP%ROWTYPE; --接收EMP表的一行数据
 	BEGIN
    	SELECT * INTO V_EMP FROM EMP WHERE ENO = 1;
    	DBMS_OUTPUT.put_line(V_EMP.ENAME || V_EMP.SAL);
    --错误示例:
    --SELECT ENAME INTO V_EMP FROM EMP; ORACLE会报异常，原因是一对多。
	END;

流程控制

条件分支

	DECLARE
	V_COUNT NUMBER;
	BEGIN
		SELECT COUNT(*) INTO V_COUNT FROM EMP;
			IF V_COUNT > 20 THEN
				DBMS_OUTPUT.PUT_LINE('EMP记录数超过20条');
			ELSIF V_COUNT >10 THEN  --不是ELSEIF
				DBMS_OUTPUT.PUT_LINE('EMP记录数超过10条但未超过20条');
			ELSE  --与java的ELSE保持一致
				DBMS_OUTPUT.PUT_LINE('EMP记录数不超过10条');
			END IF; --IF结束语,;分号不可省 
	END;

循环

循环打印1-10

	--LOOP循环
	DECLARE
		V_NUM NUMBER :=1;--声明循环变量
	BEGIN
		--循环入口
		LOOP
		EXIT WHEN V_NUM > 10 ; --当EXIT WHEN返回TRUE时，退出循环体。EXIT WHEN可以放在后面;注意：;分号不可省
		DBMS_OUTPUT.PUT_LINE(V_NUM);
		V_NUM :=V_NUM+1;
		--循环出口
		END LOOP;
	END;	

	--WHILE循环
	DECLARE
	BEGIN
		V_NUM NUMBER :=1;
		WHILE V_NUM <=10 LOOP --WHILE语句为真，执行循环语句
			DBMS_OUTPUT.PUT_LINE(V_NUM);
			V_NUM :=V_NUM+1;
		END LOOP;--注意：;分号不可省
	END;	
	--FOR循环
	DECLARE
		V_NUM NUMBER :=1;
	BEGIN
		FOR V_NUM IN REVERSE 1..10 --IN REVERSE MIN..MAX 在 MIN<=X<=MAX之间循环
		LOOP
			DBMS_OUTPUT.PUT_LINE(V_NUM);
			--FOR循环中不需要自己进行循环数的++操作。
		END LOOP;
	END;

游标

用于临时存储多行数据以及多个字段的数据

游标的属性	返回值类型	说明
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%ISOPEN	布尔型	游标已经打开时值为真，否则为假

	DECLARE
		--定义游标
		CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP; --存储EMP表中ENAME,SAL字段的多行数据
		V_NAME EMP.ENAME%TYPE;
		V_SAL EMP.SAL%TYPE;
	BEGIN
		OPEN C_EMP;--打开游标
		--使用游标
		--遍历游标
		LOOP --游标的使用FETCH方法需要放在一个循环中，这里使用LOOP循环
			FETCH C_EMP INTO V_NAME,V_SAL;--获取游标的数据并给V_NAME,V_SAL赋值，注意顺序。
				--退出循环的条件
			EXIT WHEN C_EMP%NOTFOUND; --如果游标为空，退出循环。注意顺序，先判断再打印，否则最后条数据会打印两次。
			DBMS_OUTPUT.PUT_LINE(V_NAME||' : '||V_SAL);
		END LOOP;
		CLOSE C_EMP;--关闭游标
	END;

	DECLARE
    	--定义游标
    	CURSOR C_EMP IS SELECT ENAME,SAL FROM EMP; --存储EMP表中ENAME,SAL字段的多行数据
    	V_NAME EMP.ENAME%TYPE;
    	V_SAL EMP.SAL%TYPE;
  	BEGIN
  		OPEN C_EMP;--打开游标
    	--使用游标
    	--遍历游标
      	WHILE true loop --这里使用WHILE循环使用游标，由于游标必须FETCH之后才能判断是否为空，所以这里循环条件直接为TURE。
      		FETCH C_EMP INTO V_NAME,V_SAL;--获取游标的数据并给V_NAME,V_SAL赋值，注意顺序。
       		EXIT WHEN C_EMP%NOTFOUND;
      		DBMS_OUTPUT.PUT_LINE(V_NAME||' : '||V_SAL);
     
    	END LOOP;
    	CLOSE C_EMP;--关闭游标
  END;

带参游标

	DECLARE
		CURSOR C_EMP(V_ENO EMP.ENO%TYPE) IS --入参为V_DEPNO,类型与EMP.DEPNO相同
		SELECT ENAME,SAL FROM EMP WHERE EMP.ENO = V_ENO; 
		V_NAME EMP.ENAME%TYPE;
		V_SAL EMP.SAL%TYPE;
		BEGIN
		OPEN C_EMP(1);--入参1，查询1号员工信息
		--使用游标
		--遍历游标
		LOOP
			FETCH C_EMP INTO V_NAME,V_SAL;--获取游标的数据并给V_NAME,V_SAL赋值，注意顺序。
		--退出循环的条件
		EXIT WHEN C_EMP%NOTFOUND; --如果游标为空，退出循环
			DBMS_OUTPUT.PUT_LINE(V_NAME||' : '||V_SAL);
		END LOOP;
		CLOSE C_EMP;--关闭游标
	END;

存储过程

将PLSQL的业务处理过程封装起来进行复用，这些被封装存储起来的PLSQL程序称之为存储过程。
作用：能够减少程序与数据库的IO操作，提高效率，实现一次IO操作即可完成数据传输。

无参的存储过程

	--定义名为GETALLEMPT的存储过程名
	CREATE OR REPLACE PROCEDURE GETALLEMP AS --也可以使用IS，GETALLEMPT为存储过程名
	--在存储过程中，可以省略DECLARE。默认在BEGIN 与CREATE存储过程之间定义变量
	CURSOR C_EMP IS 
		SELECT ENAME,SAL FROM EMP;
	V_NAME EMP.ENAME%TYPE;
	V_SAL EMP.SAL%TYPE;
	BEGIN
		OPEN C_EMP;
			LOOP
				FETCH C_EMP INTO V_NAME,V_SAL;
			EXIT WHEN C_EMP%NOTFOUND;
				DBMS_OUTPUT.PUT_LINE(V_NAME||' : '||V_SAL);	
			END LOOP;
		CLOSE C_EMP;
	END;
	--存储过程定义结束，加上过程名，不加默认CREATE中的过程名。


--使用PLSQL调用无参存储过程	

	BEGIN
		GETALLEMP; --存储过程名
	END;


--使用SQLPLUS调用存储过程
	SQL>set serveroutput on
	SQL>EXEC GETALLEMP;

带输入参数的存储过程

	--定义名为GETALLEMPT的存储过程名
	CREATE OR REPLACE PROCEDURE GETEMPBYENO(I_ENO IN EMP.ENO%TYPE) AS --带参数的存储过程，入参I开头，出参O开头。
	--在存储过程中，可以省略DECLARE。默认在BEGIN 与CREATE存储过程之间定义变量
	CURSOR C_EMP IS 
		SELECT ENAME,SAL FROM EMP WHERE EMP.ENO = I_ENO;
	V_NAME EMP.ENAME%TYPE;
	V_SAL EMP.SAL%TYPE;
	BEGIN
		OPEN C_EMP;
			LOOP
				FETCH C_EMP INTO V_NAME,V_SAL;
			EXIT WHEN C_EMP%NOTFOUND;
				DBMS_OUTPUT.PUT_LINE(V_NAME||' : '||V_SAL);	
			END LOOP;
		CLOSE C_EMP;
	END;
	--存储过程定义结束，加上过程名，不加默认CREATE中的过程名。


--使用PLSQL调用无参存储过程	

	BEGIN
		GETEMPBYENO(1); --存储过程名
	END;


--使用SQLPLUS调用存储过程
	--启动执行存储过程
	SQL>set serveroutput on
	SQL>EXEC GETEMPBYENO(1);

带输入和输出参数的存储过程

	--定义名为GETALLEMPT的存储过程名
	CREATE OR REPLACE PROCEDURE GETEMPBYENO(I_ENO IN EMP.ENO%TYPE,O_NAME OUT EMP.ENAME%TYPE,O_SAL OUT EMP.SAL%TYPE) AS --带参数的存储过程，入参I开头，出参O开头。
	--在存储过程中，可以省略DECLARE。默认在BEGIN 与CREATE存储过程之间定义变量
	CURSOR C_EMP IS 
		SELECT ENAME,SAL FROM EMP WHERE EMP.ENO = I_ENO;
	BEGIN
		OPEN C_EMP;
			LOOP
				FETCH C_EMP INTO O_NAME,O_SAL;
			EXIT WHEN C_EMP%NOTFOUND;
			END LOOP;
		CLOSE C_EMP;
	END;
	--存储过程定义结束，加上过程名，不加默认CREATE中的过程名。


--使用PLSQL调用无参存储过程	

DECLARE
 V_NAME EMP.ENAME%TYPE;
 V_SAL EMP.SAL%TYPE;
BEGIN
   GETEMPBYENO(1,V_NAME,V_SAL);
   DBMS_OUTPUT.PUT_LINE(V_NAME ||' : '|| V_SAL);
END;


--使用SQLPLUS调用存储过程
	--启动执行存储过程
	SQL>set serveroutput on
	SQL>DECLARE 
			V_NAME EMP.ENAME%TYPE;
			V_SAL EMP.SAL%TYPE;
		BEGIN
			GETEMPBYENO(1,V_NAME,V_SAL);
			DBMS_OUTPUT.PUT_LINE(V_NAME||' : '||V_SAL);
		END;
		/

JAVA springboot 整合 mybatis 连接Oracle调用存储过程

pom.xml

<dependencies>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>
        <!--springboot-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>
        <!--Oracle-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
    </dependencies>

application.yml

spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521:orcl #orcl为数据库实例名，若你的实例名为xe或者其他的，替换掉。
    driver-class-name: oracle.jdbc.driver.OracleDriver
    username: scott #换成你oracle账号
    password: scott #换成你oracle密码
server:
  port: 1500 #你想使用的端口

Emp

@Data //使用该注解要引入lombok依赖，详见我的pom.xml,可省去get，set等方法
public class Emp {
    public int no;
    public String name;
    public int sal;


}
**OracleMapper**

```java
@Repository
public interface OracleMapper {
    @Select("{call GETEMPBYENO(#{no,jdbcType=INTEGER,mode=IN},#{name,jdbcType=VARCHAR,mode=OUT},#{sal,jdbcType=INTEGER,mode=OUT})}")
    @Options(statementType = StatementType.CALLABLE)
    public void getEmpByNo(Emp emp);
}

**OracleController**

```java
@RestController
public class OracleController {
    @Autowired
    OracleMapper oracleMapper; //省事，省去了Sevice层。
    @GetMapping("/getEmp/{id}")
    public Emp getEmpByNo(@PathVariable("id")int id){
        Emp emp = new Emp();
        emp.setNo(id);
        oracleMapper.getEmpByNo(emp);
        return  emp;
    }
}

结果:
在这里插入图片描述

附注

本篇中数据库数据

	--建表
	CREATE TABLE EMP(
	DEPNO INT PRIMARY KEY,
	ENAME VARCHAR2(20),
	SAL INT
	);
	--插入数据
	insert into emp values(1,'张三',20000);
	insert into emp values(2,'李四',10000);

数据库截图:
在这里插入图片描述

立志成为架构师

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
Oracle存储过程学习(附带SpringBoot整合Mybatis连接Oracle调用存储过程)

Oracle的存储过程即对应JAVA语言函数的概念。可以将服务器的逻辑代码转换到SQL语句中，减少服务器与数据库的数据传输。--java:System.out.println(""); BEGIN DBMS_OUTPUT.PUT_LINE(''); END / --结束符 SET SERVEROUTPUT ON --打开存储过程的执行结果。变量声明 DECLARE V_NAME VARCHAR2(20) := '张三' ; --直接赋值 BEGIN
复制链接

扫一扫