教学大纲:

PLSQL编程：Hello World、程序结构、变量、流程控制、游标.存储过程：概念、无参存储、有参存储（输入、输出）.J***A调用存储存储过程.

1。PLSQL编程

1.1。概念和目的

什么是PL/SQL？

PL/SQL（Procedure Language/SQL）PLSQL是Oracle对sql语言的过程化扩展 (类似于Basic)指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。

1.2。程序结构

通过Plsql Developer工具的测试窗口创建一个程序模板，或者通过语句在sql窗口中编写。

提示:PLSQL语言不区分大小写。

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

-- Created on 2018/3/21 by ADMINISTRATOR DECLARE -- 声明变量、游标。 I INTEGER;BEGIN -- 执行语句 --[异常处理]?END;

声明部分用于声明变量或游标(结果集类型变量)，如果程序中没有变量声明，可以省略。

1.3。你好世界

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

其中DBMS_OUTPUT是oracle内置包，相当于Java中的System.out，PUT_LINE()是被调用的方法，相当于println()方法。

您还可以在sqlplus中编写和运行PLSQL程序:

SQL> BEGIN 2 3 --打印hello world 4 5 DBMS_OUTPUT.PUT_LINE('hello world'); 6 7 END; 8 /PL/SQL 过程已成功完成。

执行后，不显示输出结果。默认情况下，输出选项是关闭的。我们需要打开set serveroutput on

在SQLPLUS中执行PLSQL程序需要在程序末尾加一个/来标记程序的结束。

1.4。变量

PLSQL编程中的常见变量分为两类:

普通数据类型（char,varchar2, date, number, boolean, long）特殊变量类型（引用型变量、记录型变量）

该变量声明如下

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

1.4.1。公共变量

有两种方法可以给变量赋值:

直接赋值语句 := 比如: v_name := ‘zhangsan’语句赋值，使用select …into … 赋值：（语法 select 值 into 变量）

[示例]打印员工的个人信息，包括姓名、工资和地址。

-- 打印人员个人信息，包括： 姓名、薪水、地址DECLARE -- 姓名 V_NAME VARCHAR2(20) := '张三'; -- 声明变量直接赋值 --薪水 V_SAL NUMBER; --地址 V_ADDR VARCHAR2(200);?BEGIN? --在程序中直接赋值 V_SAL := 1580;? --语句赋值 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表中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

[示例]确定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

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。什么是光标

用于临时存储查询返回的多行数据(ResultSet，类似于Java Jdbc连接返回的结果集)。通过遍历光标，可以逐行访问处理结果集的数据。

游标的用法:declare & # 8212& gt打开—& gt阅读—& gt关闭

2.2。语法

光标声明:

CURSOR cursor name [(参数列表)]是一个查询语句；

光标打开:

打开光标名称；

光标的值:

将光标名称提取到变量列表中；

光标关闭:

关闭光标名称；

2.3。游标的属性

游标属性的返回值类型的说明。

如果在游标中找不到任何元素，则%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。带参数的游标

[示例]使用光标查询并打印某部门员工的姓名和工资。部门编号在运行时手动输入。

--使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。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(10);? --遍历游标中的值 LOOP? ?? --通过%NOTFOUND判断是否有值,有值打印,没有则退出循环 EXIT WHEN C_EMP%NOTFOUND; --通过FETCH语句获取游标中的值并赋值给变量 FETCH C_EMP INTO V_ENAME, V_SAL;? DBMS_OUTPUT.PUT_LINE('姓名:' || V_ENAME || ',薪水:' || V_SAL);?? END LOOP;? --关闭游标 CLOSE C_EMP;?END;

注意:%NOTFOUND属性的默认值是FLASE，所以要注意判断条件在循环中的位置。如果在FETCH first中判断，最后一条记录的值会打印两次(默认值是多一个循环)；

3。存储过程

3.1。概念函数

我们之前写过的PLSQL程序，可以进行表运算，判断，循环逻辑处理，但是不能重复调用。

可以理解为前面的代码都是用***in方法写的，是匿名程序。J***A可以通过封装对象和方法来解决重用问题。

PLSQL是为了存储PLSQL的业务流程以供重用。这些存储的PLSQL程序称为存储过程。

存储过程的功能:

1.在开发过程中，对于一个具体的业务功能，会多次连接和关闭数据库(连接和关闭都是非常消耗资源的)，需要多次读写数据库，所以性能比较低。如果把这些服务放到PLSQL中，我们在应用中调用PLSQL就可以一次连接关闭数据库，可以大大提高效率。

2.甲骨文官方建议:不要在程序里放任何能让数据库运行的东西。基本上在数据库中实现不会有错误，但是在程序中操作可能会有错误。(如果在数据库中操作数据，可以有日志恢复等一些功能。)

3.2。语法

CREATE OR REPLACE PROCEDURE 过程名称[(参数列表)] ISBEGIN?END [过程名称];

根据参数的类型，我们将分三类来解释它们:

l无参数

带输入参数的l

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

3.3。没有参数存储

3.3.1。创建存储

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

[示例]通过调用存储过程打印hello world

创建存储过程:

--通过调用存储过程打印hello worldCREATE OR REPLACE PROCEDURE P_HELLO ISBEGIN? DBMS_OUTPUT.PUT_LINE('hello world');?END P_HELLO;

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

3.3.2。调用存储过程

1.通过PLSQL程序调用:

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

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

提示:在SQLPLUS中显示结果的前提是需要设置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 P_QUERYNAMEANDSAL;

命令调用:

SQL> exec p_querynameandsal(7839);姓名:KING,薪水:5000PL/SQL 过程已成功完成。

PLSQL程序调用:

BEGIN? P_QUERYNAMEANDSAL(7839);?END;

执行结果:

3.5。带有输出参数的存储过程

[示例]输入雇员号以查询雇员的信息(雇员号7839)，并请求将薪水作为调用程序的返回值输出。

--输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(I_EMPNO IN EMP.EMPNO%TYPE,O_SAL OUT EMP.SAL%TYPE) IS?BEGIN? SELECT SAL INTO O_SAL FROM EMP WHERE EMPNO = I_EMPNO;?END P_QUERYSAL_OUT;

PLSQL程序调用:

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

注意:调用时，参数应与定义的参数的顺序和类型一致。

3.7。J***A程序调用存储过程

要求:如果一个语句不能实现结果集，比如多表查询或者复杂逻辑查询，我们可以选择调用store query来查找你的结果。

3.7.1。jdk API分析

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

得出连接对象调用prepareCall方法传递一个转义的sql语句来调用存储过程，输入参数直接由set方法传递。在需要注册输出参数之后，执行存储过程并通过get方法获取。参数列表的下标从1开始。

。实现代码

准备环境:

l 导入Oracle的jar包【示例】通过员工号查询员工的姓名和薪资package cn.itcast.oracle.jdbc;?import oracle.jdbc.OracleTypes;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;?public class ProcedureTest { public static void ***in(String[] args) throws Exception { //1.加载驱动 Class.forName("oracle.jdbc.driver.OracleDriver");? //2.获得连接对象? //2.1 设置连接字符串 String url ="jdbc:oracle:thin:@localhost:1521:xe" ;? String name = "scott";? String password = "tiger";? Connection conn = DriverManager.getConnection(url, name, password);?? //3.获取语句对象 String sql = "{call p_querysal_out(?,?)}";//转义语法,{call 存储过程(参数列表)} 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();? }}