oracle之pl/sql编程

                                                                                  oracle之pl/sql编程

1.pl/sql编程的优点：1）提高程序运行性能

                                      （相对于java程序一条条调用sql语句，pl/sql编程则较少了sql语句在数据库中编译的

                                          时间开销以及程序和数据库之间传输的时间开销）

                                 2）模块化的设计思想

                                 3）减少网络传输消耗(程序和数据库服务器之间的交互需要网络资源消耗)             

                                 4）提高了安全性

                        缺点是可移植性很差。不便于在不同数据库之间进行移植。

2.pl/sql编程分类，最基本的编程单位是块，在此基础上引出存储过程，触发器，包，函数四种编程。

   1）块编程：块是pl/sql的基本程序单元，pl/sql编程实际上就是块的编程。

                      pl/sql块编程由三个部分组成：定义部分，执行部分，例常处理部分。

                      declare 定义部分定义常量，变量及复杂的数据类型定义  这部分是可选的。

                      begin执行部分，要执行的sql语句。                                  这部分是必须的。

                      exection 例常处理部分。

                     块的编程实例：

                     

<span style="font-size:24px;">create trigger xw_table_trigger before insert on xw_table for each row when(new.xh is null)
  begin
    select xw_sequence.nextval into:new.xh from dual;
  end;</span>

    2）存储过程：存储过程用于执行特定的操作，当监理存储过程时既可以指定输入参数（in），

                            也可以指定输出参数。

                            带返回值的存储过程：

                           

<span style="color:#ff0000;font-size: 18px;"> </span><span style="font-size:24px;color:#333333;">create or replace procedure emp_procedure(emp_empno in emp.empno%type,emp_ename outemp.ename%type)is
begin
  select ename into emp_ename from emp where empno=emp_empno;
end;</span>

    3）函数：函数用于返回特定的数据，当监理函数时，在函数头部必须声明return子句，

                    在函数体内必须包含惹return语句返回的数据。

                   函数编程实例：

                  

<span style="font-size: 18px;"> </span><span style="font-size:24px;color:#333333;">create or replace function xw_fun(emp_nameemp.ename%type)
                   return number is yearsal emp.sal%type;
                   begin
          select sal*12+nvl(comm,0) into yearsal from emp where ename=emp_name;
               return yearsal;
                   end;     </span>

   4）包：包是用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。

                包规范包括对函数以及存储过程的声明，但是不具体实现。

                包体包括对具体声明的函数以及存储过程具体实现。

                包的声明：

               

<span style="font-size: 18px;"> </span><span style="font-size:24px;color:#333333;">create package xw_package is
procedure xw_procedure(emp_name emp.ename%type,emp_sal emp.sal%type);
function xw_function(emp_name emp.ename%type)return number;
end;   </span><span style="font-size: 18px;">  </span>

                包体的实现：

               

<span style="font-size: 18px;"> </span><span style="font-size:24px;">create or replace package body xw_package is
procedure xw_procedure(emp_name emp.ename%type,emp_sal emp.sal%type) is
  begin
    update emp set sal=emp_sal where ename=emp_name;
  end;
function xw_function(emp_name emp.ename%type)return number is yearsal emp.sal%type;
begin
    select sal*12+nvl(comm,0) into yearsal from emp where ename=emp_name;
    dbms_output.put_line(yearsal);
    return yearsal;
  end;
  end;      </span><span style="font-size: 18px;">   </span>

5.触发器：触发器是隐含的执行的存储过程，当定义触发器时必须要指定处罚的时间和触发的操作

                   常用的触发事件包括insert，update，delete语句。

6. pl/sql编程中定义并使用变量：

        1）标量类型：就是在声明部分定义一些变量，

                                如定义一个小数并初始化：v_sal number(5,2):=123.34;

                                如定义一个日期类型：       v_date date;

                                如定义一个bool类型：       v_bool boolean not null default false;

        2）复合类型：用于存放多个值得变量。包括pl/sql记录和pl/sql表两个较常用的。

                             a：pl/sql记录，可以理解为高级语言中的结构体。

                                  

<span style="font-size:24px;">declare
type emp_record_type is record(ename emp.ename%type,esal emp.sal%type,ejobemp.job%type);
        result_type emp_record_type;
  begin
  select ename,sal,job into result_type from emp where empno=7369;
  dbms_output.put_line(result_type.ename);
 end;</span>

                            b：pl/sql表，相当于高级语言中的数组。声明格式：

                                  type type_table is a table of emp.ename%type index by binary_integer.

        3）参照类型：分为游标变量（ref_cursor）和对象类型变量(ref_obj_type)

                                游标变量的使用，编写一个块，输入一个部门编号是输出所有员工姓名以及工资。

                                

<span style="font-size:24px;">declare 
type ref_cursor is ref cursor;
xw_cur_test ref_cursor;
v_name emp.ename%type;
v_sal emp.sal%type;
begin  
  open xw_cur_test for select ename,sal from emp where deptno=30;
  Loop
    fetch xw_cur_test into v_name,v_sal;
    dbms_output.put_line(v_name||v_sal);
    exit when xw_cur_test%notfound;
  end loop;
         end;</span><span style="font-size: 18px;">
</span>