Oracle函数 使用数据库/oralce函数(PLSQL软件)与ideal进行存储过程
- 测试入参/出参/出入参的存储过程 ( 使用ideal与数据库进行存储过程)
1.在数据库中
如:使用emp这个表
-- 测试存储过程
CREATE PROCEDURE pro_myTest2(in pempno int,out pename VARCHAR(20),inout psal DECIMAL(7,2))
BEGIN
DECLARE v_sal DECIMAL(7,2);
SELECT ename,sal into pename,v_sal from emp
where empno=pempno;
set psal=psal+v_sal;
end;
- 在ideal中
- 导入依赖
<parent>
<artifactId>spring-boot-starter-parent</artifactId>
<groupId>org.springframework.boot</groupId>
<version>2.7.17</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
</dependency>
</dependencies>
- 在application.yml (记得改账户/密码/数据库 !!!)
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://localhost:3306/t311
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
type-aliases-package: com.entity
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
auto-mapping-behavior: partial
mapper-locations: classpath:mapper/*Mapper.xml
- 在App
@SpringBootApplication
@MapperScan("com.dao")
public class App
{
public static void main( String[] args )
{
SpringApplication.run(App.class,args);
}
}
- 在Dao层里面
public interface EmpDao {
/**
* 测试调用存储
* @param map
*/
void prodTest01(Map<String,Object> map);
}
- 在mapper的xml文件中
pro_myTest2 ->数据库中存储过程名称
NUMERIC ->相当于number,数值类型
VARCHAR ->字符串类型
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "mybatis_mapper" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dao.EmpDao">
<select id="prodTest01" statementType="CALLABLE">
{ call pro_myTest2(#{empNo,mode=IN,jdbcType=NUMERIC},
#{ename,mode=OUT,jdbcType=VARCHAR},
#{sal,mode=INOUT,jdbcType=NUMERIC})}
</select>
</mapper>
- 在service/impl层
public interface EmpService {
String getEname(Integer empNo,Double sal);
}
@Service
public class EmpServiceimpl implements EmpService {
@Resource
private EmpDao empDao;
@Override
public String getEname(Integer empNo, Double sal) {
Map<String, Object> map = new HashMap<>();
map.put("empNo", empNo);
map.put("ename", ""); // 初始化ename为空字符串
map.put("sal", sal);
System.out.println(map);
empDao.prodTest01(map);
System.out.println(map);
// 检查"ename"是否为null,避免空指针异常
Object enameObj = map.get("ename");
if (enameObj != null) {
return enameObj.toString();
} else {
return null; // 或者返回适当的默认值
}
}
}
- 在controller层
@RestController
@RequestMapping("emp")
public class EmpController {
@Resource
private EmpService empService;
@GetMapping
public Object test(Integer empNo, Double sal) {
System.out.println("1");
return empService.getEname(empNo != null ? empNo : 0, sal != null ? sal : 0.0);
}
@PostMapping
public Object test2(Integer empNo, Double sal) {
System.out.println("2");
return empService.getEname(empNo != null ? empNo : 0, sal != null ? sal : 0.0);
}
}
- 点击运行后在打开APiPost7软件
在第4个的时候看数据是否出现,出现表表成功
- 函数的存储过程 ( 使用ideal与数据库进行存储过程)
- 在数据库中
CREATE DEFINER=`root`@`localhost` FUNCTION `fun_add`(f1 int(3),f2 int(3)) RETURNS int(11)
BEGIN
RETURN (f1+f2)*2;
END
- 在ideal中的dao层
/**
* 测试调用函数
* @param map
*/
void funTest01(Map<String,Object> map);
3.在mapper的xml文件中
<select id="funTest01" statementType="CALLABLE">
{ #{f3,mode=OUT,jdbcType=NUMERIC} = call fun_add(#{f1,mode=IN,jdbcType=NUMERIC},
#{f2,mode=IN,jdbcType=NUMERIC})}
</select>
4.在service/impl中
int getSum(Integer f1,Integer f2);
@Override
public int getSum(Integer f1, Integer f2) {
Map<String, Object> map = new HashMap<>();
map.put("f1", f1);
map.put("f2", f2);
map.put("f3", 0);
System.out.println(map);
empDao.funTest01(map);
System.out.println(map);
return new Integer(map.get("f3").toString());
}
5.在controller中
@RestController
@RequestMapping("fun")
public class FunContrller {
@Resource
private EmpService empService;
@GetMapping
public Object test(Integer f1, Integer f2) {
System.out.println("1");
return empService.getSum(f1, f2);
}
@PostMapping
public Object test2(Integer f1, Integer f2) {
System.out.println("2");
return empService.getSum(f1, f2);
}
}
6.在ApiPost7软件中
- 入参/出参/出入参的存储过程 (使用ideal与Oralce函数进行存储过程)
- 在oralce函数中 软件 ->PLSQL Developer
--测试
create or replace procedure pro_myTest2(pempno in number,
pename out varchar2,psal in out number)
is
v_sal number(7,2);
begin
select ename,sal into pename,v_sal from emp where pempno=empno;
psal :=psal+v_sal;
end;
--调用
DECLARE
v_pename VARCHAR2(50);
v_psal NUMBER(7,2);
BEGIN
v_psal := 1000; -- 设置初始值
pro_myTest2(7369, v_pename, v_psal); -- 调用存储过程
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_pename);
DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_psal);
END;
- 在application.yml (记得改账户/密码/数据库 !!!)
1521 ->PLSQL软件的端口号
B332 ->SID(数据库的名称)
Type ->里面一定要改成oralce函数的type
server:
port: 8080
spring:
datasource:
url: jdbc:oracle:thin:@127.0.0.1:1521:B332
username: scott
password: 123
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: oracle.jdbc.driver.OracleDriver
mybatis:
type-aliases-package: com.entity
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
auto-mapping-behavior: partial
mapper-locations: classpath:mapper/*Mapper.xml
- 后面的操作跟前面一模一样 (dao/service/mapper/controller/运行)
- 函数的存储过程 (使用ideal与Oralce函数进行存储过程)
1.在oralce函数中 软件 ->PLSQL Developer
--测试函数
create or replace function fun_add(f1 in number,f2 in number)
return number
is begin
return (f1+f2)*2;
end;
-- 调用函数
SELECT fun_add(3, 4) FROM dual;
2.在application.yml (记得改账户/密码/数据库 !!!)
1521 ->PLSQL软件的端口号
B332 ->SID(数据库的名称)
Type ->里面一定要改成oralce函数的type
server:
port: 8080
spring:
datasource:
url: jdbc:oracle:thin:@127.0.0.1:1521:B332
username: scott
password: 123
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: oracle.jdbc.driver.OracleDriver
mybatis:
type-aliases-package: com.entity
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
auto-mapping-behavior: partial
mapper-locations: classpath:mapper/*Mapper.xml
3.后面的操作跟前面一模一样 (dao/service/mapper/controller/运行)