oracle语言块

一、作用：

如果不使用PL/SQL语言块，oracle一次只能处理一条SQL语句。每条SQL语句都导致客户（client）向服务器(server)调用，从而在性能上产生很大的开销，尤其是在网络操作中。如果使用 PL/SQL,一个块中的语句作为一个组，导致客户向服务器的一次调用，减少网络转输。

二、分类：

1、无名块：

没有命名的pl/sql块，不存储到数据库中，可以调用其他程序，却不能被其他程序调用。

2、存储过程/函数。

可以接收参数，可以被重复的调用。

3、包，

命名了的pl/sql块，由一组相关的过程，函数和标识符组成。

4、数据库触发器：

是一个与具体数据库表相关联的存储pl/sql程序。每当一个sql操作影响到该数据库表时，系统就自动执行相应的数据库触发器。每个表最多可以有12个触发器。当要删除比较重要的表信息时，执行删除操作后，可以把该数据备份，该功能就可以使用触发器来实现。

三、语法：

三部分组成:定义部分、可执行部分以及例外处理部分，

1、定义部分：

        定义将在可执行部分中调用的所有变量、常量、游标和用户自定义的例外处理。可以没有。

 2、可执行部分：

        包括对数据库中进行的sql语句，以及对快中进行组织、控制的pl/sql语句。必须存在。

3、例外处理部分：

        在执行过程中出错或出现非正常现象时所做的相应处理，可以没有，相当于try/catch.

四、匿名块实例

        接收用户输入的员工编号，查询并输出该员工的姓名和雇佣日期，处理用户输入的员工编号不存在的异常。

先创建一个班级表和学生表备用：

create table class(
 id number(3) primary key,
 name varchar2(20) not null,
 cnum number(3) 
);
create table student(
 id number(3) primary key,
 name varchar2(30)not null,
 sex varchar(1)not null,
 age number(2) not null,
 birthday date 
);
alter table student modify sex varchar2(2);
create sequence seq_class;
create sequence seq_student;
insert into class(id,name,cnum)
select seq_class.nextval,name,cnum from(
select '111'as name, 23 as cnum from dual union all
select '112'as name, 34 as cnum from dual union all
select '113'as name, 43 as cnum from dual union all
select '114'as name, 23 as cnum from dual union all
select '115'as name, 23 as cnum from dual 
);
insert into student(id,name,sex,age,birthday)
select seq_student.nextval,name,sex,age,birthday from(
select '张三'as name,'f'as sex, 23 as age,to_date('1993-02-03','yyyy-mm-dd')as birthday from dual union all
select '李四'as name,'m'as sex, 34 as age,to_date('1993-02-03','yyyy-mm-dd')as birthday  from dual union all
select '王五'as name,'f'as sex, 43 as age,to_date('1993-02-03','yyyy-mm-dd')as birthday  from dual union all
select '赵柳'as name,'m'as sex, 23 as cnum,to_date('1993-02-03','yyyy-mm-dd')as birthday  from dual union all
select '你大爷'as name,'f'as sex, 23 as cnum,to_date('1993-02-03','yyyy-mm-dd')as birthday  from dual 
);

1、无名块：

根据输入的学生id输出该学生的姓名和性别，

--先查一下表内数据，方便查看。
select * from student;
declare --定义，相当于声明属性。
 t_name varchar2(20);--声明自定义属性
 t_sex char(2);
 t_id number(3);
begin
  t_id:=&项目编号;
  select name,sex into t_name,t_sex from student where id =t_id;--查询出来姓名和性别，并填充进去自定义属性。
  dbms_output.put_line('该id的姓名：'||t_name||'性别：'||t_sex);--打印出来
  exception
  when no_data_found then
    dbms_output.put_line('该id不存在');--打印异常。
end;

当输入id存在时输出：

不存在时输出打印异常语句：

 2、常量的使用：

--常量的使用：
declare
 pi constant number(8,7):=3.1415926;--通过constant关键字声明常量，无论什么时候都不会改变。小数点后7位小数。
 r number(4,2);--小数点后两位小数
 area number(8,2);
 begin
   r:=&半径;
   area:=pi*r*r;
   dbms_output.put_line('面积为:'||area);
end;

半径输入2：

3、%type的用法： 

在第一步中，我们需要给变量设置类型，但是我们使用%type后，他会默认同步使用表中的类型，就不用担心类型错误的问题了。

declare
 t_empno emp.empno%type; --定义一个变量并且使用emp表中empno的字符类型。
 t_hiredate emp.hiredate%type;
 t_ename emp.ename%type;
begin
  t_empno:=&员工编号; --手动输入员工编号，并传值给t_empno;
  select ename,hiredate into t_ename,t_hiredate from emp where empno =t_empno;

    --日期类型要用to_char进行转换，不然会报错。打印信息要使用||进行连接。
  dbms_output.put_line('该员工姓名:'||t_ename||',生日:'||to_char(t_hiredate,'yyyy-mm-dd'));
  exception
    when no_data_found then --当输入的数据无法在表中找到时，进行打印
    dbms_output.put_line('该员工不存在');
end;

4、 rowtype

如果是要查询表的整列信息，可以使用rowtype直接将表的字段赋予给自定义变量。

declare
t_dept_row dept%rowtype; --定义一个变量，并将dept所有的列赋予给他。
begin
  select * into t_dept_row from dept where deptno=&deptNo; --&后的即为输入框提示符。
  dbms_output.put_line('部门名称：'||t_dept_row.dname||'位置：'||t_dept_row.loc);
  exception
  when no_data_found then
    dbms_output.put_line('该部门不存在');
end;

5、record 

定义一个record，在内部将数据表的值通过%type赋予给他，然后定义一个变量进行使用。

--record
declare
--定义一个record，相当于一个类
 type record_emp is record(
  rempno emp.empno%type,
  rename emp.ename%type,
  rjob emp.job%type,
  rsalary emp.sal%type
 );
 --定义一个变量，属于record类。
 t_record_emp record_emp;
begin
  select empno,ename,job,sal into t_record_emp from emp where empno=&empno;
  dbms_output.put_line('员工姓名为：'||t_record_emp.rename||'职位：'||t_record_emp.rjob||'工资为：'||
  t_record_emp.rsalary);
 end;

 

6、table 

 定义一个table类，并声明一个属于他的变量，将查询到的数据按顺序赋值，并进行打印。

-- table  用法
declare
  -- 定义部门
   --定义table类
   -- binary_integer 二进制整型，只可以作为集合或者数组的下标使用，不能用于表结构定义
   type table_emp is table of emp%rowtype  index by binary_integer;
   -- 定义变量属于table类
   t_table_emp table_emp;
begin
   
   -- 查询emp的一行数据并赋值给t_table_emp的第一行
   select * into t_table_emp(0) from emp where empno=7369;
   -- 查询emp的一行数据并赋值给t_table_emp的第一行
   select * into t_table_emp(1) from emp where empno=7788;
   -- 查询emp的一行数据并赋值给t_table_emp的第一行
   select * into t_table_emp(2) from emp where empno=7654;
   -- 打印
   dbms_output.put_line('7369的姓名为：'||t_table_emp(0).ename||',工资'||t_table_emp(0).sal);
   dbms_output.put_line('7788的姓名为：'||t_table_emp(1).ename||',工资'||t_table_emp(1).sal);
   dbms_output.put_line('7654的姓名为：'||t_table_emp(2).ename||',工资'||t_table_emp(2).sal);
end;

 7、预定义异常：

-- 预定义异常 例子
begin
    -- 执行部分
    -- select * from emp where empno=&empno;
    -- 插入违反主键约束一条sql
    insert into dept values(10,'开发1部','5楼');
    
    exception --异常处理
       when no_data_found then 
         dbms_output.put_line('数据查询不到。。。');
       when dup_val_on_index then 
         dbms_output.put_line('您违反了主键唯一约束。。。');
end;

8、自定义异常：

-- 自定义异常 例子


declare 
  -- 定义部分  
   -- 自定义异常
   no_data_del exception;
begin
   -- 执行删除
   delete from dept where deptno =&deptNo;
   -- 判断上面语句是否有数据被找到  sql%found  sql%rowcount...  隐式游标
   if sql%notfound then
     --  用于触发自定义异常 相当于java中throw ,如果被触发，直接跳至exception，不再执行下面命令。
      raise  no_data_del;
   end if;
    dbms_output.put_line('数据正常删除！');
   commit;-- 正常提交事务
   -- 异常处理
   exception 
      when no_data_del then 
         dbms_output.put_line('要删除的数据不存在');
         rollback;-- 回滚事务
   
end;

9、事物回滚：

begin
  insert into dept values(1,'开发部','1楼');
  insert into dept values(2,'调研部','2楼');
  savepoint pointA;
  insert into dept values(3,'运维部','4楼');
  insert into dept values(4,'前端','5楼');
  rollback to pointA;--跳转至回滚点，回滚点后的命令不执行，直接执行后面的语句。
  insert into dept values(5,'ui','6楼');
  commit;
end;