JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数（一）https://developer.aliyun.com/article/1432877
四、存储过程*
4.1 什么是存储过程
存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。 2、存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。 3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码
4.2 存储过程语法结构
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称 （参数名 类型, 参数名 类型, 参数名 类型） IS|AS 变量声明部分;-- 注意这里结束 BEGIN 逻辑部分 [EXCEPTION 异常处理部分] END;
参数只指定类型，不指定长度
过程参数的三种模式：
IN传入参数（默认）OUT传出参数 ，主要用于返回程序运行结果IN OUT传入传出参数
4.3 案例
4.3.1 创建不带传出参数的存储过程
添加部门信息
-- 创建序列 create sequence seq_dept_id start with 60 increment by 10; -- 创建存储过程 create or replace procedure pro_dept_addinfo ( p_dname varchar2, -- 部门名称 p_loc varchar2 -- 部门位置 ) is begin insert into dept values(seq_dept_id.nextval,p_dname,p_loc); commit; end;
PL/SQL 中调用不带传出参数的存储过程
call pro_dept_addinfo('软件测试','武汉'); or begin pro_dept_addinfo('软件质量','郑州'); end;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p6lMT4R8-1666020355061)(assets/image-20210326111257015.png)]
4.3.2 创建带传出参数的存储过程
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LtqUi7x0-1666020355062)(asseits/image-20221015234841374.png)]
一般用来创建比较灵活输出的存储过程实用场景；
Create or replace procedure getRax(xsal in number,fee out number) as r number:=xsal-5000; begin case when r<0 then fee:=0; when r<=3000 then fee:=r*0.03; when r<=12000 then fee:=r*0.1-210; when r<=25000 then fee:=r*0.2-1410; when r<=35000 then fee:=r*0.25-2660; when r<=55000 then fee:=r*0.3-4410; when r<=80000 then fee:=r*0.35-7160; else fee:=r*0.45-15160; end case; end;
PL/SQL 中调用传出参数的存储过程
对于有数据返回的，在PL/SQL中只能使用begin end，不能使用call方式
-- 调用传出参数存储过程 declare fee number; begin getRax(&xsal,fee); dbms_output.put_line('应交税额:'||fee); end;
如果是命令窗口就用exec 存储过程名，举个栗子：
1.如果是命令窗口就用exec 存储过程名，举个栗子：
EXEC` `procedure ``;``--procedure是存储过程名
2.如果是PL/SQL窗口就用 begin 存储过程名 end; 举个栗子：
begin ``procedure``;``--procedure是存储过程名 end``;
3.如果是程序中调用就用 call 存储过程名 ，举个栗子：
hibernateDao.excuteSqlUpdate("{Call proc_stuInfo()}");//存储过程proc_stuInfo
五、JDBC连接Oracle
5.1 创建工程，导入jar包
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vghGW1HS-1666020355063)(assets/image-20210326120911482.png)]
5.2 编写JDBC工具类
- properties配置文件
driver=oracle.jdbc.driver.OracleDriver url=jdbc:oracle:thin:@127.0.0.1:1521:orcl username=oracletest password=root
- JDBCUtil
public class JDBCUtil { private static String driver; private static String url; private static String username; private static String password; private static Connection conn; static { try { // 读取加载配置文件 InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); Properties pro = new Properties(); pro.load(is); driver = pro.getProperty("driver"); url = pro.getProperty("url"); username = pro.getProperty("username"); password = pro.getProperty("password"); // 加载驱动 Class.forName(driver); // 连接oracle } catch (Exception e) { e.printStackTrace(); } } // 获取数据库连接 public static Connection getConnection() { conn = DriverManager.getConnection(url, username, password); return conn; } /** * 关闭资源 * * @param rs * @param stmt * @param conn */ public static void close(ResultSet rs, Statement stmt, Connection conn) { //关闭结果集 if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } //关闭执行对象 if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } //关闭执行对象 if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
5.3 JDBC工具类测试
public class JDBCTest { public static void main(String[] args) { Connection conn = JDBCUtil.getConnection(); System.out.println(conn); } }
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EC0JN8kG-1666020355064)(assets/image-20210326121455494.png)]
六、Oracle增删改查
以dept表为例，演示增删改查
6.1 编写实体类
public class Dept { private Integer id; private String dname; private String loc; // 省略有参和无参 getter和setter toString }
6.2 DeptDao
public class DeptDao { public void addDept(Dept dept) throws SQLException { Connection conn = JDBCUtil.getConnection(); String sql = "insert into dept (id,dname,loc) values (?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, dept.getId()); pstmt.setString(2,dept.getDname()); pstmt.setString(3,dept.getLoc()); pstmt.execute(); JDBCUtil.close(null,pstmt,conn); } }
6.3 DeptTest
public class DeptTest { public static void main(String[] args) throws SQLException { DeptDao dao = new DeptDao(); Dept dept = new Dept(80,"总裁委员会","北京"); dao.addDept(dept); } }
七、数据导出与导入
当我们使用一个数据库时，总希望数据库的内容是可靠的、正确的，但由于计算机系统的故障（硬件故障、软件故障、网络故障、进程故障和系统故障）影响数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库中全部或部分数据丢失。因此当发生上述故障后，希望能重构这个完整的数据库该处理称为数据库恢复，而要进行数据库的恢复必须要有数据库的备份工作。
7.1 整库导出与导入
- 整库导出命令
exp 用户名/密码@实例名 file=导出的dmp文件存放路径 log=导出日志存放路径
- 添加参数 full=y 就是整库导出
执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。
如果想指定备份文件的名称，则添加 file 参数即可，命令如下
exp 用户名/密码@实例名 file=文件名 full=y
- 案例：将u1用户下导出到d盘中，文件叫做u1.dmp
exp u1/123456 file='u1.dmp'
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vYtXOqRq-1666020355065)(\assets\image-20211202114900529.png)]
- 整库导入命令
imp 用户名/密码 file='文件'
- 案例：
imp u1/123456 file='u1.dmp'
7.2 按用户导出与导入
- 按用户导出
exp system/oracletest owner=oracletest file=oracletest.dmp
- 按用户导入
imp system/oracletest file=oracletest.dmp fromuser=oracletest
7.3 按表导出与导入
- 按表导出
exp oracletest/root file=a.dmp tables=dept
- 用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可
- 按表导入
imp oracletest/root file=a.dmp tables=dept
出日志存放路径
###### ==添加参数 full=y 就是整库导出== 执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。 如果想指定备份文件的名称，则添加 file 参数即可，命令如下 ```sql exp 用户名/密码@实例名 file=文件名 full=y
案例：将u1用户下导出到d盘中，文件叫做u1.dmp
exp u1/123456 file='u1.dmp'
[外链图片转存中…(img-vYtXOqRq-1666020355065)]
- 整库导入命令
imp 用户名/密码 file='文件'
- 案例：
imp u1/123456 file='u1.dmp'
7.2 按用户导出与导入
- 按用户导出
exp system/oracletest owner=oracletest file=oracletest.dmp
- 按用户导入
imp system/oracletest file=oracletest.dmp fromuser=oracletest
7.3 按表导出与导入
- 按表导出
exp oracletest/root file=a.dmp tables=dept
- 用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可
- 按表导入
imp oracletest/root file=a.dmp tables=dept
