Oracle-PL/SQL编程

在线练习oracle的sql语句

https://livesql.oracle.com
livesql各个功能的介绍

数据准备

-- 准备数据
create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);
create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept  
values(20, 'RESEARCH', 'DALLAS');

insert into dept  
values(30, 'SALES', 'CHICAGO');

insert into dept  
values(40, 'OPERATIONS', 'BOSTON');

insert into emp  
values(  
 7839, 'KING', 'PRESIDENT', null,  
 to_date('17-11-1981','dd-mm-yyyy'),  
 5000, null, 10  
);

insert into emp  
values(  
 7698, 'BLAKE', 'MANAGER', 7839,  
 to_date('1-5-1981','dd-mm-yyyy'),  
 2850, null, 30  
);

insert into emp  
values(  
 7782, 'CLARK', 'MANAGER', 7839,  
 to_date('9-6-1981','dd-mm-yyyy'),  
 2450, null, 10  
);

insert into emp  
values(  
 7566, 'JONES', 'MANAGER', 7839,  
 to_date('2-4-1981','dd-mm-yyyy'),  
 2975, null, 20  
);

insert into emp  
values(  
 7788, 'SCOTT', 'ANALYST', 7566,  
 to_date('13-JUL-87','dd-mm-rr') - 85,  
 3000, null, 20  
);

insert into emp  
values(  
 7902, 'FORD', 'ANALYST', 7566,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 3000, null, 20  
);

insert into emp  
values(  
 7369, 'SMITH', 'CLERK', 7902,  
 to_date('17-12-1980','dd-mm-yyyy'),  
 800, null, 20  
);

insert into emp  
values(  
 7499, 'ALLEN', 'SALESMAN', 7698,  
 to_date('20-2-1981','dd-mm-yyyy'),  
 1600, 300, 30  
);

insert into emp  
values(  
 7521, 'WARD', 'SALESMAN', 7698,  
 to_date('22-2-1981','dd-mm-yyyy'),  
 1250, 500, 30  
);

insert into emp  
values(  
 7654, 'MARTIN', 'SALESMAN', 7698,  
 to_date('28-9-1981','dd-mm-yyyy'),  
 1250, 1400, 30  
);

insert into emp  
values(  
 7844, 'TURNER', 'SALESMAN', 7698,  
 to_date('8-9-1981','dd-mm-yyyy'),  
 1500, 0, 30  
);

insert into emp  
values(  
 7876, 'ADAMS', 'CLERK', 7788,  
 to_date('13-JUL-87', 'dd-mm-rr') - 51,  
 1100, null, 20  
);

insert into emp  
values(  
 7900, 'JAMES', 'CLERK', 7698,  
 to_date('3-12-1981','dd-mm-yyyy'),  
 950, null, 30  
);

insert into emp  
values(  
 7934, 'MILLER', 'CLERK', 7782,  
 to_date('23-1-1982','dd-mm-yyyy'),  
 1300, null, 10  
);

pl/sql的介绍

什么是PL/SQL?

1. PL/SQL（Procedure Language/SQL）
2. PLSQL是Oracle对sql语言的过程化扩展 (类似于Basic)
3. 指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。过程、函数可以在java程序中调用
4. 过程、函数、触发器是pl/sql编写
5. 过程、函数、触发器是在oracle中

优点

1. 通过存储过程，减少程序的编译，提供应用程序的运行性能
2. 模块化的设计思想[分页的过程，订单的过程，转账的过程…]
3. 减少网络传输量
4. 提高安全性

缺点

移植性不好

分类

• 存储过程
• 函数
• 触发器
• 包

pl/sql开发工具

• sqlplus开发工具
  sqlplus是oracle公司提供的一个工具，
• pl/sql developer开发工具
  pl/sql developer是用于开发pl/sql块的集成开发环境(ide),它是一个独立的产品，而不是oracle的一个附带品

编写规范

-- 注释
-- 单行注释
select * from emp where empno=7788;
/*多行注释*/
-- 标识符号的命名规范
-- 当定义变量时，建议使用v_作为前缀 v_sal
-- 当定义常量时，建议使用c_作为前缀c_rate
-- 当定义游标时，建议使用_cursor作为后缀emp_cursor;
-- 当定义异常时，建议使用e_作为前缀e_error

PLSQL编程

程序结构

通过Plsql Developer工具的Test Window 创建 程序模版或者通过语句在SQL Window编写

提示：PLSQL语言的大小写是不区分的

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

declare
-- 声明变量、游标
i integer;
begin
 -- 执行语句
 
-- 异常处理
end;

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

declare
-- 定义部分：定义常量、变量、游标、异常、复杂数据类型
begin
-- 执行部分【该部分是可选的】
exception 
-- 异常处理部门，处理运行的各种错误【该部分是可选的】
end;

Hello World

-- 打印Hello World
begin
/*
dbms_output 为oracle内置程序包，
dbms_output相当于java中的System.out
put_line相当于java中的System.out下的println()方法
*/
dbms_output.put_line('Hello World');
end;

在PL/SQL Developer中输出

在sqlplus中也可以编写运行PLSQL程序：
在oracle命令行中，sql语句结尾加上/表示立即执行该语句。
执行结束后并未显示输出的结果，默认情况下，输出选项是关闭状态的 我们需要开启一下 set serveroutput on

变量

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

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

/*
变量
声明变量的方式为：
变量名  变量类型（变量长度）  例如： v_name  varchar2(20);

*/
declare 
-- 定义变量 v_ename
v_ename varchar2(5);
begin
-- 执行部分
-- &empno接收从控制台输入的变量empno
select ename into v_ename from emp where empno=&empno;
-- dbms_output..put_line:控制台输出
-- 字符串连接符 ||
dbms_output.put_line('用户名是' || v_ename);
end;

执行sql语句后

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 :=1500;
-- 语句赋值
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号员工的个人信息，打印姓名和薪水
*/
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;

/*
记录型变量
语法： 变量名称   表名%ROWTYPE，
例如： v_emp emp%rowtype;
*/
-- 查询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;

执行sql语句输出

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

错误的使用：
1． 记录型变量只能存储一个完整的行数据

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

流程控制

条件分支

/*
条件分支
语法：
*/
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;   

循环

在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;

while循环

-- 创建表
create table users(
username varchar2(50),
pwd varchar2(50)
);

/*
请编写一个过程，可输入用户名，
并循环添加10个用户到users表中，用户编号从11开始增加
*/

create or replace procedure sp_pro6(spName varchar2) is 
-- 定义变量v_num,类型为number，等于11
v_num number :=11;
-- 执行
begin
    while v_num <=20 loop
    -- 循环语句
    insert into users values(v_num,spName);
    -- 自增  
    v_num := v_num+1;
    end loop; 
end;

-- 调用存储过程
call sp_pro6('子正');

或者这样调用存储过程

-- 调用存储过程
exec sp_pro6('子正');

或者这样调用存储过程

begin
sp_pro6('子正');
end;

select * from users;

查询调用存储过程结果插入的数据

for循环

/*
循环语句
for循环
基本for循环的结构如下
begin
     for i in reverse 1.. 10 loop
     insert into users values(i,'紫狐');
     end loop;
end;     
*/

begin
-- 控制变量i,在隐含中就不停的增加
  for i in reverse 1 .. 10 loop
  insert into users values (i,'紫狐');
  end loop;
end;

执行结果如下：

不建议使用，
顺序控制语句goto,null

/*
顺序控制语句 
1. goto语句
goto语句用于跳转到特定标号去执行语句，
注意由于使用goto语句会增加程序的复杂性，
并使得应用程序可读性变差，所以在做开发时，
不建议使用goto语句
基本语法如下 goto label,其中label是已经定义好的标号名
*/
declare
i int :=1;
begin
     loop
     dbms_output.put_line('输出i='||i);
     if i =10 then
     goto end_loop;
     end if;
     i :=i+1;
     end loop;
     <<end_loop>>
     dbms_output.put_line('循环结束');
     end;

控制台输出

/*
顺序控制语句 
1. null语句
null语句不会执行任何操作，并且会直接将控制传递到下一条语句
使用null语句的主要好处是可以提高pl/sql的可读性
*/
declare
       v_sal emp.sal%type;
       v_ename emp.ename%type;
begin
-- &no 接口从控制台输入的变量
     select ename,sal into v_ename,v_sal from emp where empno=&no;       
     if v_sal<3000 then
        update emp set comm=sal*0.1 where ename=v_ename;
     else
         null;
     end if; 
end; 

执行sql语句后，弹出以下界面

游标

什么是游标

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

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

游标声明：

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

游标的打开：

OPEN 游标名;

游标的取值：

FETCH 游标名 INTO 变量列表;

游标的关闭：

CLOSE 游标名;

游标的属性

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

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

创建和使用

-- 使用游标查询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;     

执行结果:

带参数的游标

-- 使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
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(7982);
      --遍历游标中的值
      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会导致最后一条记录的值被打印两次(多循环一次默认);

存储过程

概念作用

之前我们编写的PLSQL程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用.
可以理解之前的代码全都编写在了main方法中,是匿名程序. JAVA可以通过封装对象和方法来解决复用问题
PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程
存储过程作用：
1， 在开发程序中，为了一个特定的业务功能，会向数据库进行多次连接关闭(连接和关闭是很耗费资源), 需要对数据库进行多次I/O读写，性能比较低。如果把这些业务放到PLSQL中，在应用程序中只需要调用PLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率.
2， ORACLE官方给的建议：能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误，在程序中操作可能会存在错误.(如果在数据库中操作数据,可以有一定的日志恢复等功能.)

语法

create or replace procedure 过程名称[(参数列表)] is
变量名 变量类型
begin
end [过程名称];

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

• 不带参数的

• 带输入参数的

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

无参存储

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

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

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

调用存储过程

1. 通过PLSQL程序调用：

begin
--- 直接输入调用存储过程的名称
pro6;
end;

PLSQL调用存储过程，执行结果如下：

在SQLPLUS中通过EXEC命令调用:

提示:SQLPLUS中显示结果的前提是需要 set serveroutput on
注意：
第一个问题：is和as是可以互用的，用哪个都没关系的
第二个问题：过程中没有declare关键字，declare用在语句块中

带输入参数的存储过程

-- 查询并打印某个员工（如7839号员工）的姓名和薪水--存储过程：要求，调用的时候传入员工编号，自动控制台打印。
-- 要求，调用的时候传入员工编号，自动控制台打印。
-- in:表示输入参数,存储过程中，
-- 存储过程名称后的阔后里面的 参数后面有个默认的in,表示是输入参数
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,薪水:5000

PL/SQL 过程已成功完成。

PLSQL程序调用：

begin

 P_QUERYNAMEANDSAL(7839);

end;

执行结果:

带输出参数的存储过程

-- 输入员工号查询某个员工（7839号员工）信息，要求，将薪水作为返回值输出，给调用的程序使用。
-- out:表示是输出参数
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. JAVA程序调用存储过程

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

3.7.1. 分析jdk API

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

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

实现代码

准备环境：

• 导入Oracle的jar包

 <!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc10 -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.14.0.0</version>
        </dependency>

• 创建存储过程

create or replace procedure p_querysal(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;

java程序调用oracle存储过程

import oracle.jdbc.OracleTypes;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

/**
 * @author 16837
 */
@SuppressWarnings("{all}")
public class TestCaseDemo {
    private static String driver = "oracle.jdbc.OracleDriver";

    private static String url = "jdbc:oracle:thin:@localhost:1521:XE";

    private static String user = "sys as sysdba";

    private static String password = "sys";

    public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection(url, user, password);
        /*获取语句对象*/
        /*call 存储过程(参数列表)*/
        String sql = "{ call p_querysal(?,?) }";
        CallableStatement callableStatement = connection.prepareCall(sql);
        /*设置输入参数*/
        callableStatement.setInt(1, 7839);
        /*注册输出参数*/
        callableStatement.registerOutParameter(2, OracleTypes.DOUBLE);
        /*执行存储过程*/
        callableStatement.execute();
        /*获取输出参数*/
        double sal = callableStatement.getDouble(2);
        System.out.println("薪水：" + sal);
        /*释放资源*/
        callableStatement.close();
        connection.close();
    }
}

控制台输出：

有返回值的存储过程

 /*
    有返回值的存储过程(非列表)
    编写一个存储过程，可以输入雇员的编号，返回该雇员的姓名
  */
  -- 有输入和输出的存储过程
  create or replace procedure sp_pro8(spno in number, spName out varchar) is
begin
  select ename into spNae from emp where empno = &no;
end;

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package了，所以要分两部分：

• 建立一个包，如下

-- 返回结果集的过程
-- 创建一个包,在该包中，我定义类型test_cursor,是个游标
create or replace package testpackage as
-- ref cursor 游标
type test_cursor is ref cursor;
end testpackage;

• 建立存储过程，如下：

-- 创建存储过程
create or replace procedure sp_pro9(spNo in number,
           p_cursor out testpackage.test_cursor) 
 is
begin
  open p_cursor for
    select * from emp where deptno = spNo;
end;

• 在Java程序中调用

package com.study;

import oracle.jdbc.OracleTypes;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

@SuppressWarnings({"all"})
public class TestCase {
    private static String driver = "oracle.jdbc.OracleDriver";

    private static String url = "jdbc:oracle:thin:@localhost:1521:XE";

    private static String user = "sys as sysdba";

    private static String password = "sys";

    public static void main(String[] args) {
        try {
            /*加载驱动*/
            Class.forName(driver);
            /*得到连接*/
            Connection connection = DriverManager.getConnection(url, user, password);
            CallableStatement cs = connection.prepareCall("{call sp_pro9(?,?)}");
            /*给?赋值*/
            cs.setInt(1, 10);
            /*注册输出参数类型为oracle的游标*/
            /*oracle.jdbc.OracleTypes.CURSOR*/
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            /*调用执行存储过程*/
            cs.execute();
            /*得到结果集*/
            ResultSet rs = (ResultSet) cs.getObject(2);
            while (rs.next()) {
                System.out.println(rs.getInt(1) + " " + rs.getString(2));
            }
            /*关闭连接*/
            cs.close();
            rs.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

java程序调用之后，控制台输出如下：

函数

函数返回的是一个值

/*
函数：
函数用于返回特定的数据，
当建立函数时，在函数头部必须包含return子句，
而在函数体内必须包含return 语句返回的数据，
可以使用create function 来建立函数，
实际案例如下：
*/
create or replace function annual_income(v_name varchar2)
return number is 
annual_salary number(7,2);
begin
     select sal*12+nvl(comm,0) into annual_salary from emp where ename=v_name;
     return annual_salary;
end;

-- 调用函数
declare 
v_income number;
begin
v_income :=sys.annual_income('SMITH');
dbms_output.put_line('SMITH的收入：'||v_income);
end;

执行sql,输出结果如下：

-- 删除函数
drop function sys.annual_income;

包

包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
我们可以使用create package命令来创建包
实例：

-- 创建包
-- 创建一个包sp_package
-- 声明该包有一个存储过程update_sal
-- 声明该包有一个函数annual_income
create or replace package sp_package is 
procedure update_sal(newname varchar2,newsal number);
function annual_income(ename varchar2) return number;
end sp_package;
-- 删除包
drop package sp_package;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包用于实现包规范中的过程和函数
建立包体可以使用create package body命令

-- 建立包体可以使用create package body命令
create or replace package body sp_package is
  procedure update_sal(newname varchar2, newsal number) is
  begin
    update emp set sal = newsal where ename = newname;
  end;
  function annual_income(ename varchar2) return number is
    annual_salary number;
  begin
    select sal * 12 + nvl(comm, 0)
      into annual_salary
      from emp
     where ename = newname;
    return annual_salary;
  end;

end;

调用包的过程或是函数

当调用包的过程或是函数时，在过程和函数前需要带有包名

-- 例如：
call sp_package.update_sal('SMITH');

触发器

/*
  触发器
  触发器时指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，
  常用的触发事件包括insert、update、delete语句，而触发操作实际就是一个pl/sql块。可以
  使用create trigger来建立触发器
  触发器可以维护数据库的安全性和一致性
*/

变量和常量

在编写pl/sq程序时，可以定义变量和常量；在pl/sql程序中包括有：

• 标量类型(scalar)
• 复合类型(composite)
• 参照类型(reference)
• lob (large object)

标量类型

declare
  c_tax_rate number(3, 2) := 0.03;

  -- 用户名
  v_ename   varchar2(5);
  v_sal     number(7, 2);
  v_tax_sal number(7, 2);
begin
  --执行
  select ename, sal into v_ename, v_sal from emp where empno = &no;
  -- 计算所得税
  v_tax_sal := v_sal * c_tax_rate;
  -- 输出
  dbms_output.put_line('' || v_ename || '工资：' || v_sal || ' 交税：' ||
                       v_tax_sal);
end;

选中sql语句块，点击执行之后，弹出弹窗输入员工工号

执行结果如下:

declare
  c_tax_rate number(3, 2) := 0.03;

  -- 用户名
  v_ename   emp.ename%type;
  v_sal     emp.sal%type;
  v_tax_sal number(7, 2);
begin
  --执行
  select ename, sal into v_ename, v_sal from emp where empno = &no;
  -- 计算所得税
  v_tax_sal := v_sal * c_tax_rate;
  -- 输出
  dbms_output.put_line('' || v_ename || '工资：' || v_sal || ' 交税：' ||
                       v_tax_sal);
end;

复合类型

复合变量(composite) - 介绍
用于存放多个值的变量。主要包括这几种

• pl/sql记录
• pl/sql表
• 嵌套表
• varray

复合类型- pl/sql记录

类似与高级语言中的结构体,需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

-- pl/sql记录类似java中的类
-- pl/sql记录实例
declare
  -- 定义一个pl/sql记录类型 emp_record_type ,类型包含三个数据ename,salary,title
  type emp_record_type is record(
    ename  emp.ename%type,
    salary emp.sal%type,
    title  emp.job%type);
    sp_record emp_record_type;
  begin  
  -- 定义了一个变量sp_record，这个变量的类型是emp_recrd_type
  select ename,sal,job into sp_record
  from emp where empno=7698;
  dbms_output.put_line('员工姓名:' || sp_record.ename);
end;

执行后控制台输出结果如下：

复合类型-pl/sql表

相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制，示例如下：

-- pl/sql表实例
declare
-- 定义了一个pl/sql表类型sp_table_type,该类型是用于存放emp.ename%type
-- index by binary_integer 表示下标是整数 
type sp_table_type is table of emp.ename%type index by binary_integer;
-- 定义了一个sp_table变量，这个变量的类型是sp_table_type
sp_table sp_table_type;
/*
说明：
      sp_table_type 是pl/sql表类型
      emp.ename%type 指定了表的元素的类型和长度
      sp_table 为pl/sql表变量
      sp_table(0)则表示下标为0的元素
*/
-- pl/sql中，总是变量名在前，变量类型在后
begin
select ename into sp_table(0) from emp where empno=7902;
dbms_output.put_line('员工姓名：'||sp_table(0));
end;

执行sql语句，输出结果：

参照变量

参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型

参照变量 - ref cursor游标变量

使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时(open时)需要指定select语句，这样一个游标就与一个select语句结合了。实例如下：

-- 使用pl/sql编写一个块，可以输入部门号，并显示该部门所有信息
declare 
-- 定义游标类型 sp_emp_cursor
type sp_emp_cursor is ref cursor;
-- 定义一个游标变量test_cursor是sp_emp_cursor类型
test_cursor sp_emp_cursor;
-- 定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
-- 把test_cursor和一个select结合
-- 打开游标变量test_cursor for[指向] select ename,sal from emp where deptno=30
open test_cursor for select ename,sal from emp where deptno=30;
-- 循环取出
loop
    -- fetch 取出
    fetch test_cursor into v_ename,v_sal;
    --判断是否test_cursor为空,为空则退出
    exit when test_cursor%notfound;
    dbms_output.put_line('名字:'||v_ename||'工资:'||v_sal);
end loop;
-- 关闭游标
close test_cursor;
end;

执行sql,输出结果

异常处理

oracle中将异常分为预定义异常、非预定义异常和 自定义异常三种
预定义异常用于处理常见的oracle错误
非预定义异常用于处理预定义异常外不能处理的异常
自定义异常用于处理与oracle异常无关的其他情况

-- 异常
-- 定义变量
declare v_ename emp.ename%type
begin
-- 把emp表中的ename赋值给v_ename
select ename into v_ename from emp where empno=7780;
-- 控制台打印输出
dbms_output.put_line('名字':||v_ename);
-- 异常
exception
-- oracle预定义异常no_data_found，数据没找到
    when no_data_found then
    dbms_output.put_line('编号不存在');
end;
/

预定义异常

case_not_found

在开发pl/sql编程中，编写case语句时，如果在when子句中没有包含必须的条件分支，就会触发case_not_found

create or replace procedure sp_pro6(spno number) is 
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=spno;
case
    when v_sal<1000 then
    update emp set sal=sal+100 where empno=spno;
    when v_sal<2000 then
    update emp set sal=sal+200 where empno=spno;
    end case;
exception
    when case_not_found then
    dbms_output.put_line('case语句没有与v-sal相匹配的条件');
end;
/

cursor_already_open

重新打开已经打开的游标时，会隐含的触发异常cursor_already_open

-- cursor_already_open
declare 
    cursor emp_cursor is select ename,sal from emp;
begin
    open emp_cursor;
    -- 再次打开游标emp_cursor 
    for emp_record in emp_cursor loop
    dbms_output.put_line(emp_record.ename);
    end loop;
exception 
    when cursor_already_open then
    dbms_output.put_line('游标已经打开');
end;
/

dup_val_on_index

在唯一索引所对应的列上插入重复的值，会隐含的触发异常
dup_val_on_index

-- dup_val_on_index
begin
insert into dept values (10,'公关部','北京');
exception
-- 发生dup_val_on_index异常
when dup_val_on_index then
dbms_output.put_line('在deptno列上不能出现重复值');
end;
/

invalid_cursor

当试图在不合法的游标上执行操作时，会触发该异常。
例如：试图从没有打开的游标提取数据，或是关闭没有打开的游标，就会触发invalid_cursor异常

--- invalid_cursor
declare 
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
-- open emp_cursor打开游标
fetch emp_cursor into emp_record;
dbms_output.put_line(emp_record.ename);
exception
    when invalid_cursor then
dbms_output.put_line('请检测游标是否打开');
end;
/

invalid_number

当输入的数据有误时，会触发该异常
比如：数字100写成1oo就会触发该异常

-- invalid_number
begin
update emp set=sal+'1oo';
exception
    when invalid_number then
dbms_output.put_line('输入的数字不正确');
end;
/

too_many_rows

当执行select into 语句时，如果返回超过了一行，则会触发该异常

-- too_many_rows
declare
v_ename emp.ename%type;
begin
    select ename into v_ename from emp;
exception
    when too_many_rows then
dbms_output.put_line('返回了多行');
end;
/

value_error

当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该异常

-- value_error
declare 
v_ename varchar2(5);
begin
    select ename into v_ename from emp where empno=1;
    dbms_output.put_line(ename);
exception
    when value_error then
     dbms_output.put_line('变量尺寸不足');
end;
/

login_denide

当用户非法登录时，会触发该异常

not_logged_on

如果用户没有登录就执行dml操作，就会触发该异常

storage_error

如果超出了内存空间或是内存被损坏，就触发该异常

timeout_on_resource

如果oracle在等待资源时，出现了超时就触发该异常。

自定义异常

-- 自定义异常
create or replace procedure ex_test(spNo number)
is
-- 定义一个异常
myex exception
begin
-- 更新用户sal
update emp set sal=sal+1000 where empno=1;
-- sql %notfound这是表示没有update
if sql %notfound then
-- raise 扔出异常 myex
raise myex;
end if;
when myex then 
 dbms_output.put_line('没有更新任何用户');
end;
/

视图

• 表需要占用磁盘空间，视图不需要
• 视图不能添加所以
• 使用视图可以简化复杂查询
• 视图利于提高安全性，比如：不同用户查看不同视图

-- 创建视图
create view 视图名 as select 语句[with read only]
-- 创建或修改视图
create or replace view 视图名 as select 语句[with read only]
-- 删除视图
drop view 视图名;