目录

1.概念和目的

1.1. 什么是PL/SQL?

1.2. 程序结构

1.3. Hello World

sqlplus编译运行

 开启输出：

 set serveroutput on

1.4. 变量

1.4.1. 普通变量

1.4.2. 引用型变量

引用型变量的好处：

1.4.3. 记录型变量

1.5. 流程控制

1.5.1. 条件分支

根据输入分数给定等级

if条件分支比较大小

1.5.2. 循环

2. 游标

2.1. 什么是游标

2.2. 语法

游标声明：

游标的打开：

游标的取值：

游标的关闭：

2.3. 游标的属性

2.4. 创建和使用

2.5. 带参数的游标

3. 存储过程

3.1. 概念作用

3.2. 语法

3.3. 无参存储

3.3.1. 创建存储

新建→程序窗口→Procedure→命名

3.3.2. 调用存储过程

通过PLSQL程序调用：

 结论：比调方法要简单，调方法要创建对象、实例化对象，通过对象去.方法名；调存储过程只要写过程名就行了

2.在SQLPLUS中通过EXEC命令调用:

 开启输出：

 set serveroutput on

注意：

3.4. 带输入参数的存储过程

在sql窗口命令调用：

sqlplus命令调用：

3.5. 带输出参数的存储过程

执行结果

 注意:调用的时候,参数要与定义的参数的顺序和类型一致.

3.7. JAVA程序调用存储过程

3.7.1. 分析jdk API

得出结论:

3.7.2. 实现代码

准备环境：

导入Oracle的jar包

实现代码

---

---

1.概念和目的

1.1. 什么是PL/SQL?

PL/SQL（Procedure Language/SQL） 是Oracle对sql语言的过程化扩展 (类似于Basic)；

指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

1.2. 程序结构

PL/SQL可以分为三个部分：声明部分、可执行部分、异常处理部分。

且不区分大小写。

DECLARE
  -- 声明变量、游标。
  I INTEGER;
BEGIN
  -- 执行语句

  --[异常处理]

END;

其中 DECLARE部分用来声明变量或游标（结果集类型变量），如果程序中无变量声明可以省略掉。

1.3. Hello World

BEGIN
  --打印hello world
  DBMS_OUTPUT.PUT_LINE('hello world');
END;

其中DBMS_OUTPUT 为oracle内置程序包，相当于Java中的System.out,而PUT_LINE（）是调用的方法,相当于println（）方法。

运行结果

sqlplus编译运行

还可以通过sqlplus窗口进行编译运行

win+R→cmd→sqlplus→输入用户名：SCOTT，密码TIGER（登录区分大小写！！！）

 begin
    dbms_output.put_line('hello world');
 end;
    /

SQLPLUS中执行PLSQL程序 需要在程序最后添加一个 / 标识程序的结束

执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的 我们需要开启一下

 开启输出：

 set serveroutput on

运行结果

 Oracle客户端自带的命令窗口也可以实现

1.4. 变量

PLSQL编程中常见的变量分两大类：

• 普通数据类型（char,varchar2, date, number, boolean, long）

• 特殊变量类型（引用型变量、记录型变量）

声明变量的方式为

变量名  变量类型（变量长度）  例如： v_name  varchar2(20);

1.4.1. 普通变量

变量赋值的方式有两种：

1. 直接赋值语句 := 比如: v_name := 'zhangsan'

2. 语句赋值，使用select …into … 赋值：（语法 select 值 into 变量）

【示例】打印人员个人信息，包括： 姓名、薪水、地址

-- 打印人员个人信息，包括： 姓名、薪水、地址
DECLARE
  -- 姓名
  V_NAME VARCHAR2(20) := '张三'; -- 声明变量直接赋值
  --薪水
  V_SAL NUMBER;
  --地址
  V_ADDR VARCHAR2(200);

BEGIN

  --在程序中直接赋值
  V_SAL := 5580;

  --语句赋值
  SELECT '云南昆明' INTO V_ADDR FROM DUAL;

  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL || ',地址：' ||V_ADDR);

END;

 展示结果

1.4.2. 引用型变量

变量的类型和长度取决于表中字段的类型和长度

通过表名.列名%TYPE指定变量的类型和长度，例如： v_name emp.ename%TYPE;

【示例】查询emp表中7839号员工的个人信息，打印姓名和薪水

加入引用变量

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
DECLARE
  -- 姓名
  V_NAME EMP.ENAME%TYPE; -- 声明变量直接赋值
  --薪水
  V_SAL EMP.SAL%TYPE;

BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型要一致
  SELECT ENAME, SAL INTO V_NAME, V_SAL FROM EMP WHERE EMPNO = 7839;

  --打印变量
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || ',薪水：' || V_SAL);

END;

结果展示

引用型变量的好处：

使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不需要考虑列的类型，使用%TYPE是非常好的编程风格，因为它使得PL/SQL更加灵活，更加适应于对数据库定义的更新。

1.4.3. 记录型变量

接受表中的一整行记录，相当于Java中的一个对象

语法： 变量名称 表名%ROWTYPE， 例如： v_emp emp%rowtype;

【示例】

查询并打印7839号员工的姓名和薪水

 将emp作为对象，enamel和sal作为属性取值

-- 查询emp表中7839号员工的个人信息，打印姓名和薪水
DECLARE
  -- 记录型变量接受一行
  V_EMP EMP%ROWTYPE;

BEGIN
  --记录型变量默认接受表中的一行数据，不能指定字段。
  SELECT * INTO V_EMP FROM EMP WHERE EMPNO = 7839;

  --打印变量，通过变量名.属性的方式获取变量中的值
  DBMS_OUTPUT.PUT_LINE('姓名：' || V_EMP.ENAME || ',薪水：' || V_EMP.SAL);

END;

如果有一个表，有100个字段，那么你程序如果要使用这100字段话，如果你使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便的解决这个问题

错误的使用：

1． 记录型变量只能存储一个完整的行数据

 2．返回的行太多了，记录型变量也接收不了

1.5. 流程控制

1.5.1. 条件分支

语法：

BEGIN
  IF 条件1 THEN 执行1 
   ELSIF 条件2 THEN 执行 2
   ELSE 执行3 
  END IF; 
END;

注意关键字：ELSIF

根据输入分数给定等级

DECLARE
--定义变量：
V_1 NUMBER:='&输入你的分数';
BEGIN
  --判断数据大小
  IF V_1>=90 THEN
    DBMS_OUTPUT.PUT_LINE('A');
  ELSIF V_1>=80 THEN
    DBMS_OUTPUT.PUT_LINE('B');
  ELSIF V_1>=60 THEN
    DBMS_OUTPUT.PUT_LINE('C');
  ELSE
    DBMS_OUTPUT.PUT_LINE('D');
  END IF;
END;

解释：

当第一个表达式为true则执行第一个语句，若第一个表达式为false则判断第二个表达式，

如果第二个表达式为true执行第二个语句，若第二个表达式为false则判断第三个表达式……

直到最后一个表达式判断仍为false，则执行ELSE中的语句。

if条件分支比较大小

declare
  v_data1 number := '56';
  v_data2 number := '32';
begin
  if v_data1>v_data2 then
    dbms_output.put_line(v_data1 || '大');
    else
      dbms_output.put_line(v_data2 || '大');
      end if;
end;

 

【示例】判断emp表中记录是否超过20条，10-20之间，或者10条以下

--判断emp表中记录是否超过20条，10-20之间，或者10条以下
DECLARE
  --声明变量接受emp表中的记录数
  V_COUNT NUMBER;
BEGIN
  --查询emp表中的记录数赋值给变量
  SELECT COUNT(1) INTO V_COUNT FROM EMP;
  --判断打印
  IF V_COUNT > 20 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');
  ELSIF V_COUNT >= 10 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');
  ELSE
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');
  END IF;
END;

1.5.2. 循环

在ORACLE中有三种循环方式，这里我们不展开,只介绍其中一种：loop循环

语法：

BEGIN
  LOOP
  	EXIT WHEN 退出循环条件  
  END LOOP;
END;

【示例】打印数字1-10

--打印数字1-10
DECLARE
  --声明循环变量并赋初值
  V_NUM NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN V_NUM > 10;
    DBMS_OUTPUT.PUT_LINE(V_NUM);  
    --循环变量自增
    V_NUM := V_NUM + 1;  
  END LOOP;
END;

2. 游标

2.1. 什么是游标

用于临时存储一个查询返回的多行数据（结果集,类似于Java的Jdbc连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：声明--->打开--->读取--->关闭

2.2. 语法

游标声明：

CURSOR 游标名[(参数列表)] IS 查询语句;

游标的打开：

OPEN 游标名;

游标的取值：

FETCH 游标名 INTO 变量列表;

游标的关闭：

CLOSE 游标名;

2.3. 游标的属性

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开时值为真，否则为假

其中 %NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环

2.4. 创建和使用

【示例】使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
DECLARE
  --声明游标
  CURSOR C_EMP IS
    SELECT ENAME, SAL FROM EMP;
  --声明变量用来接受游标中的元素
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP;
  --遍历游标中的值
  LOOP 
    --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
      INTO V_ENAME, V_SAL;  
    --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
    EXIT WHEN C_EMP%NOTFOUND; 
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);  
  END LOOP;
  --关闭游标
  CLOSE C_EMP;
END;

2.5. 带参数的游标

 带参数和不带参数的游标对比一下：

【示例】使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。

--输出所有部门员工姓名和薪水
 SELECT EMPNO，ENAME, SAL FROM EMP;

 --使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
DECLARE
  --声明游标传递参数
  CURSOR C_EMP(V_EMPNO EMP.EMPNO%TYPE) IS
    SELECT ENAME, SAL FROM EMP WHERE EMPNO = V_EMPNO;
  --声明变量用来接受游标中的元素
  V_ENAME EMP.ENAME%TYPE;
  V_SAL EMP.SAL%TYPE;
BEGIN
  --打开游标并传递参数
  OPEN C_EMP(7902);
  --遍历游标中的值
  LOOP
      --通过FETCH语句获取游标中的值并赋值给变量
    FETCH C_EMP
      INTO V_ENAME, V_SAL;
     --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环
       EXIT WHEN C_EMP%NOTFOUND;       
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  END LOOP;
  --关闭游标
  CLOSE C_EMP;
END;

注意:%NOTFOUND属性默认值为FLASE,所以在循环中要注意判断条件的位置.如果先判断在FETCH会导致最后一条记录的值被打印两次(多循环一次默认);

FETCH在后输出结果：（两条记录被重复打印）

 FETCH在前：

 自我小结：

之所以会多打一次是因为打印之前没有做判断无法终止打印，重复打印后才进入上面的判断，此时打印终止，多输出了一条记录。

3. 存储过程

3.1. 概念作用

之前我们编写的PLSQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用.

可以理解之前的代码全都编写在了main方法中,是匿名程序. JAVA可以通过封装对象和方法来解决复用问题

PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程

存储过程作用：

1， 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源), 需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.

2， ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

3.2. 语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] IS
BEGIN

END [过程名称];

根据参数的类型，我们将其分为3类讲解：

l 不带参数的

l 带输入参数的

l 带输入输出参数(返回值)的。

3.3. 无参存储

3.3.1. 创建存储

通过Plsql Developer或者语句创建存储过程

新建→程序窗口→Procedure→命名

【示例】 通过调用存储过程打印hello world

创建存储过程:

create or replace procedure p_hello is
--通过调用存储过程打印hello world
begin
  dbms_output.put_line('hello world');
end p_hello;

  通过工具查看创建好的存储过程:

3.3.2. 调用存储过程

1. 通过PLSQL程序调用：

BEGIN
 --直接输入调用存储过程的名称
  P_HELLO;

END P_HELLO;

 

 输出结果

 结论：比调方法要简单，调方法要创建对象、实例化对象，通过对象去.方法名；调存储过程只要写过程名就行了

2.在SQLPLUS中通过EXEC命令调用:

还可以通过sqlplus窗口进行编译运行

win+R→cmd→sqlplus→输入用户名：SCOTT，密码TIGER（登录区分大小写！！！）

SQLPLUS中执行PLSQL程序 需要在程序最后添加一个 / 标识程序的结束

执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的 我们需要开启一下

 开启输出：

 set serveroutput on

运行结果

注意：

第一个问题：is和as是可以互用的，用哪个都没关系的

第二个问题：过程中没有declare关键字，declare用在语句块中

3.4. 带输入参数的存储过程

【示例】查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入员工编号，自动控制台打印。

在程序窗口：

--查询并打印某个员工（如7839号员工）的姓名和薪水
--要求，调用的时候传入员工编号，自动控制台打印。
CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(I_EMPNO IN EMP.EMPNO%TYPE) IS
  --声明变量接受查询结果
  V_ENAME EMP.ENAME%TYPE;
  V_SAL   EMP.SAL%TYPE;

BEGIN

  --根据用户传递的员工号查询姓名和薪水
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = I_EMPNO;

  --打印结果
  DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);
  
--结尾两种写法，一种是直接 end； 一种是和开头一样 END P_QUERYNAMEANDSAL;
END;

在sql窗口命令调用：

--输出所有部门员工姓名和薪水
 SELECT EMPNO，ENAME, SAL FROM EMP;
 
declare
  i integer;
begin
  p_querynameandsal(7839);
end;

 结果

sqlplus命令调用：

exec p_querynameandsal(7902);

执行结果：

3.5. 带输出参数的存储过程

【示例】输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。

  分析：有输入（工号），有输出（薪水）

在程序窗口创建存储

--通过调用存储过程打印hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN

  DBMS_OUTPUT.PUT_LINE('hello world');

END P_HELLO;

PLSQL程序调用：

DECLARE
  --声明一个变量接受存储过程的输出参数
  V_SAL EMP.SAL%TYPE;
BEGIN
  P_QUERYSAL_OUT(7839, V_SAL); --注意参数的顺序
  DBMS_OUTPUT.PUT_LINE(V_SAL);
END; 

执行结果

 注意:调用的时候,参数要与定义的参数的顺序和类型一致.

3.7. JAVA程序调用存储过程

需求:如果一条语句无法实现结果集, 比如需要多表查询,或者需要复杂逻辑查询,我们可以选择调用存储查询出你的结果.

3.7.1. 分析jdk API

 通过Connection对象的prepareCall方法可以调用存储过程

用于执行SOL存储过程的界面。JDBC API提供了存储过程SQL转义语法，允许以标准方式为所有RDBMS调用存储过程。此转义语法包含一个结果参数和不包含结果参数的表单。

如果使用，结果参数必须注册为OUT警数。其他参数可用于输入，输出或两者。 参数按顺序依次引用，第一个参数为1。
{?= call sprocedure-name>[(<arg1>,<arg2>，...)]}    存储函数必须有返回值（不灵活）

{call <procedure-name>[(<arg1>,<arg2>，...)]}         存储过程没有返回值（推荐）
IN参数值使用从Preparedstatement继承的set方法设置。所有OUT参数的类型必须在执行存储过程之前进行注册;它们的值通过这里提供的get方法在执行get。
A CallableStatement可以返回一个ResultSet对象或多ResultSet对象。多个ResultSet对象使用从Statement ResultSet操作来处理。
为获得最大的便携性，应在处理输出参数值之前处理一个电话ResultSet对象和更新计数。

得出结论:

通过Connection对象调用prepareCall方法传递一个转义sql语句调用存储过程, 输入参数直接调用set方法传递.输出参数需要注册后,执行存储过程,通过get方法获取.参数列表的下标是从1开始的

3.7.2. 实现代码

准备环境：

导入Oracle的jar包

【示例】通过员工号查询员工的姓名和薪资

在idea进行操作

新建项目

 新建模块

新建java模块jdbctest 在jdbctest文件下新建一个lib目录

 将对应ojdbc6的jar包复制到lib下

 

 添加为库

 

 

 在src下创建一个类cn.wxx.oracle.test.JdbcTest

实现代码

package cn.itcast.oraclle.test;
import oracle.jdbc.OracleTypes;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

/*存储过程 通过员工号查询员工的姓名和薪资*/
public class JdbcTest {
    public static void main(String[] args) throws  Exception {
        //1.加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");

        //2.获取连接对象
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user ="SCOTT";
        String password = "TIGER";
        Connection conn = DriverManager.getConnection(url, user, password);
        
        //3.获得语句对象
        String sql = "{call p_querysal_out(?,?)}";
        CallableStatement call = conn.prepareCall(sql);

        //4.设置输入参数
        call.setInt(1,7839);

        //5.注册输出参数
        call.registerOutParameter(2, OracleTypes.DOUBLE);

        //6.执行储存过程
        call.execute();

        //7.获取输出参数
        double sal = call.getDouble(2);
        System.out.println("该工号员工薪资为："+sal);

        //8.释放资源
        call.close();
        conn.close();
    }
}

输出展示：