JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数

怼烎@ 2023-09-29 17:24 22阅读 0赞

## day03\_Oracle ##

## 一、课程目标 ##

PLSQL语法（掌握）
    存储函数（了解）
    存储过程（掌握）
    JAVA来调用oracle函数或过程(掌握)

## 二、PL/SQL基础语法 ##

### 2.1 什么是PL/SQL ###

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

### 2.2 基本语法 ###

[declare 
     	--声明变量
    ]
    begin
     	--代码逻辑 
    [exception
     		--异常处理
     ]
    end;

### 2.3 变量 ###

*  ##### 声明变量和赋值 #####
    
        变量名 类型（长度）;  
        -- 1.基本类型
        变量名:=变量值
    
        --  1+1=2的案例
        declare 
           a int:=1;
           b int:=1;
           c number;
        begin  
          c:=a+b;
           dbms_output.put_line(c);   
        end;
 *  ##### Select into 方式 赋值 #####
    
        select 列名 into 变量名 from 表名 where 条件
    
    注意：结果必须是一条记录(不是一个值) ，有多条记录和没有记录都会报错
    
        declare 
           num_sal number;
           var_name varchar2(20);
           begin
                    select ename,sal into var_name,num_sal from emp where empno=7369;
                    dbms_output.put_line(var_name||'工资是：'||num_sal);
           end;

*  **引用变量**
    
    优点：1.用户不必查看表中各列的数据类型，即可查询到所需数据；
    
    2.对表中已经有的数据类型进行修改，不必考虑已定义的数据类型，和表中的一致；
    
    2.%Type类型
    
    – 变量 表名.列名%type
    
    使用%Type关键字可以声明一个与指定名称相同的数据类型，它通常跟在指定列名的后面。

var\_job emp.job%type – 表示var\_job和emp表下的job是同一个类型；

参考案例(同上做比较）：
    
    ```plsql
    select * from emp
    declare
       var_job emp.job%type;
       var_name emp.ename%type;
    begin
      select ename,job into var_name,var_job from emp where empno=7369;
      dbms_output.put_line(var_name||'职务：'||var_job);
    end;

3.%rowtype类型

记录类型，用来存储从数据表查询到的一行的数据

语法：表变量名 表名%rowtype;

declare 
       varEmp  emp%rowtype;
       begin
         select * into varEmp from emp where empno= 7839;
         dbms_output.put_line('员工'||varEmp.empno||'姓名'||varEmp.ename);
     end;

从运行结果可以看到，变量varEmp和emp表的结构完全相同。

### 2.4 异常 ###

在运行程序时出现的错误叫做异常发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

*  ###### 预定义异常 ######
    
    > 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
 *  ###### 用户定义异常 ######
    
    > 用户可以在 PL/SQL 块的声明部分定义异常，自定义的
    > 
    > 异常通过 RAISE 语句显式引发

#### 2.4.1 预定义异常 ####

> Oracle 预定义异常 21 个

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e1H8eidn-1666020355057)(assets/image-20210326085850685.png)\]

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h24a1ePX-1666020355059)(assets/image-20210326085917379.png)\]

#### 2.4.2 语法结构 ####

exception
      when 异常类型 then
           异常处理逻辑

案例

declare
           primary_iter exception;  -- 定义一个自定义异常
           pragma exception_init(primary_iter,-00001);
           begin
             insert into emp(empno,ename)values(7369,'sss');
           exception
             when primary_iter then
                  dbms_output.put_line('主键不能重复');
           end;

### 2.5 条件判断 ###

#### 2.5.2 单分支 ####

if 条件 then
      业务逻辑
    end if;

declare
       vnum number:=&num;
       begin
         if vnum=1 then 
            dbms_output.put_line('成功登录');
         end if;
       end;

#### 2.5.2 if else ####

if 条件 then
     业务逻辑
    else
     业务逻辑
    end if;

declare
       vnum number:=&num;
       begin
            if vnum=1 then
               dbms_output.put_line('登录成功');
            else
              dbms_output.put_line('登录失败');
            end if;
         end;

#### 2.5.3 多分支 ####

if 条件 then
     	业务逻辑
    elsif 条件 then  -- 注意：这个长相：是els+if
     	业务逻辑
    else
     	业务逻辑 
    end if;

declare
       age number:= &num;
       begin
         if age<18 then 
           dbms_output.put_line('未成年人');
         elsif age>=18 and age<60 then 
           dbms_output.put_line('年轻人');
         elsif age>=60 and age<80 then
           dbms_output.put_line('中年人');
         elsif age>=80 then
           dbms_output.put_line('老人');
         end if;       
       end;

#### 2.5.4 Case语句 ####

Oracle 9i之后增加的语句，笔记二未体现。

declare
       age integer:=&num;
       vresult varchar2(100); -- 别起result关键字；
       begin
         case 
           when age<18 then
             vresult:='未成年人';
           when age>=18 and age<60 then
             vresult:='年轻人';
           when age>=60 and age<80 then
             vresult:='中年人';
           when age>=80 then
             vresult:='老年人';
         end case;
         dbms_output.put_line(vresult);
       end;

### 2.6 循环 ###

#### 2.6.1 loop when循环 ####

*  ##### 语法 #####
    
        loop   
         --循环语句   类似于之前的do while循环
           exit when 退出条件;
        end loop;
 *  ##### 演示 #####
    
    > 输出从1开始的100个数
    
        declare
        	i number:=1;
        begin 
         loop
        	 dbms_output.put_line(i);
             i:=i+1;
             exit when i>100;
         end loop; 
        end ;

#### 2.6.2 when loop循环 ####

*  ##### 语法 #####
    
        while 循环条件  
        loop
        	循环体
        end loop;
 *  ##### 演示 #####
    
    > 输出从1开始的100个数
    
        declare
        	i number:=1;
        begin
         while i<=100 loop
             dbms_output.put_line(i);
             i:=i+1; 
         end loop; 
        end ;

#### 2.6.3 for … loop循环 ####

*  ##### 语法 #####
    
        -- for(int i=1;i<结束值;i++){}
        for 变量 in 起始值..终止值 loop
            -- 循环语句
        end loop;
 *  ##### 演示 #####
    
    > 输出从1开始的100个数
    
        begin
         for i in 1..100
         loop
         	dbms_output.put_line(i); 
         end loop;
        end;

向表中插入数据；

insert into student(id,name)values(seq_test.nextval,'李四')
    
    -- 向student表中插入1000条数据；使用while loop循环？？？？
    declare
       i number :=1;
    begin
      while i<=1000 loop -- 当大于2755的时候，控制台会报错；插入没问题；
          -- dbms_output.put_line(i); 在sql控制台输出
          insert into student(id,name)values(seq_test.nextval,'李四'||i);
          commit;
          i:=i+1;
      end loop;    
    end;

## 三、存储函数 ##

### 3.1 什么是存储函数 ###

> 存储函数又称为自定义函数,一种存储在数据库中的命令程序块。可以接收零或多个参数，必须有**返回值**。

### 3.2 存储函数语法 ###

> 创建或修改存储过程的语法如下:

CREATE [ OR REPLACE ] FUNCTION 函数名称(参数名称 参数类型, 参数名称 参数类型,...n)
    	RETURN 结果变量数据类型
    IS
       变量声明部分;
    BEGIN
     -- 逻辑部分;
        RETURN  结果变量;
    [EXCEPTION 异常处理部分]
    END;

示例

> 需求：根两个数相加函数

create or replace function f_sum(a number,b number) return number
    is fresult number;  -- 这个是变量，fresult这个是需要return
    begin
           fresult:=a+b;
           return fresult;
      end;

调用存储函数

select f_sum(3,2) from dual

实用函数：

Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2 Is
      strYuan       Varchar2(150);
      strYuanFen    Varchar2(152);
      numLenYuan    Number;
      numLenYuanFen Number;
      strRstYuan    Varchar2(600);
      strRstFen     Varchar2(200);
      strRst        Varchar2(800);
      Type typeTabMapping Is Table Of Varchar2(8) Index By Binary_Integer;
      tabNumMapping  typeTabMapping;
      tabUnitMapping typeTabMapping;
      numUnitIndex   Number;
      i              Number;
      j              Number;
      charCurrentNum Char(1);
    Begin
      If Money Is Null Then
        Return Null;
      End If;
      strYuan := TO_CHAR(FLOOR(Money));
      If strYuan = '0' Then
        numLenYuan := 0;
        strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
      Else
        numLenYuan := length(strYuan);
        strYuanFen := TO_CHAR(FLOOR(Money * 100));
      End If;
      If strYuanFen = '0' Then
        numLenYuanFen := 0;
      Else
        numLenYuanFen := length(strYuanFen);
      End If;
      If numLenYuan = 0 Or numLenYuanFen = 0 Then
        strRst := '零圆整';
        Return strRst;
      End If;
      tabNumMapping(0) := '零';
      tabNumMapping(1) := '壹';
      tabNumMapping(2) := '贰';
      tabNumMapping(3) := '叁';
      tabNumMapping(4) := '肆';
      tabNumMapping(5) := '伍';
      tabNumMapping(6) := '陆';
      tabNumMapping(7) := '柒';
      tabNumMapping(8) := '捌';
      tabNumMapping(9) := '玖';
      tabUnitMapping(-2) := '分';
      tabUnitMapping(-1) := '角';
      tabUnitMapping(1) := '';
      tabUnitMapping(2) := '拾';
      tabUnitMapping(3) := '佰';
      tabUnitMapping(4) := '仟';
      tabUnitMapping(5) := '万';
      tabUnitMapping(6) := '拾';
      tabUnitMapping(7) := '佰';
      tabUnitMapping(8) := '仟';
      tabUnitMapping(9) := '亿';
      For i In 1 .. numLenYuan Loop
        j            := numLenYuan - i + 1;
        numUnitIndex := Mod(i, 8);
        If numUnitIndex = 0 Then
          numUnitIndex := 8;
        End If;
        If numUnitIndex = 1 And i > 1 Then
          strRstYuan := tabUnitMapping(9) || strRstYuan;
        End If;
        charCurrentNum := substr(strYuan, j, 1);
        If charCurrentNum <> 0 Then
          strRstYuan := tabNumMapping(charCurrentNum) ||
                        tabUnitMapping(numUnitIndex) || strRstYuan;
        Else
          If (i = 1 Or i = 5) Then
            If substr(strYuan, j - 3, 4) <> '0000' Then
              strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
            End If;
          Else
            If substr(strYuan, j + 1, 1) <> '0' Then
              strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
            End If;
          End If;
        End If;
      End Loop;
      For i In -2 .. -1 Loop
        j              := numLenYuan - i;
        charCurrentNum := substr(strYuanFen, j, 1);
        If charCurrentNum <> '0' Then
          strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
                       strRstFen;
        End If;
      End Loop;
      If strRstYuan Is Not Null Then
        strRstYuan := strRstYuan || '圆';
      End If;
      If strRstFen Is Null Then
        strRstYuan := strRstYuan || '整';
      Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then
        strRstFen := strRstFen || '整';
      End If;
      strRst := strRstYuan || strRstFen;
      --strRst := Replace(strRst, '亿零', '亿');
      --strRst := Replace(strRst, '万零', '万');
      Return strRst;
    End Money2Chinese;
    -- 调用--
    Select Money2Chinese(786.213) From dual;

## 四、存储过程\* ##

### 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 按用户导出与导入 ###

### 7.3 按表导出与导入 ###