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);
数据库截图: