Oracle数据库技术之PL/SQL语法和存储过程_欢迎来视频课学习的技术博客_

一、课程目标

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 个

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:=#
   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:=#
   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:= #
   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:=#
   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 while 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 in varchar2, -- 部门名称
       p_loc in 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;
或者在命令行窗口执行
exec  pro_dept_addinfo('公关部','郑州');
区别： 1.call一般用来在外部程序调用使用，例如JAVAEE;
      2.begin end;一般用在在sql窗口执行；
      3.exec 在命令行窗口使用；

4.3.2 创建带传出参数的存储过程

输入参数是in，输出参数是out

格式：

参数名 out 类型

一般用来创建比较灵活输出的存储过程实用场景；

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包

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);
    }
}

六、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'

整库导入命令

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

一、课程目标

二、PL/SQL基础语法

2.1 什么是PL/SQL

2.2 基本语法

2.3 变量

声明变量和赋值

Select into 方式 赋值

2.4 异常

预定义异常

用户定义异常

2.4.1 预定义异常

2.4.2 语法结构

2.5 条件判断

2.5.2 单分支

2.5.2 if else

2.5.3 多分支

2.5.4 Case语句

2.6 循环

2.6.1 loop when循环

语法

演示

2.6.2 while loop循环

语法

演示

2.6.3 for .. loop循环

语法

演示

三、存储函数

3.1 什么是存储函数

3.2 存储函数语法

四、存储过程*

4.1 什么是存储过程

4.2 存储过程语法结构

4.3 案例

4.3.1 创建带输入参数的存储过程

4.3.2 创建带传出参数的存储过程

五、JDBC连接Oracle

5.1 创建工程，导入jar包

5.2 编写JDBC工具类

properties配置文件

JDBCUtil

5.3 JDBC工具类测试

六、Oracle增删改查

6.1 编写实体类

6.2 DeptDao

6.3 DeptTest

七、数据导出与导入

7.1 整库导出与导入

整库导出命令

添加参数 full=y 就是整库导出

整库导入命令

7.2 按用户导出与导入

按用户导出

按用户导入

7.3 按表导出与导入

按表导出

用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可

按表导入

Select into 方式赋值