Oracle PLSQL

PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.

本系列笔记可能会遇到的专业词汇有：

procedure, 存储过程
function, 函数
trigger, 触发器
package/package body, 包和包体

Oracle存储过程

Oracle中的procedure就类似于JAVA中没有返回值的方法，只是执行一些代码，不需要返回，
它和之前的PLSQL子程序的不同点在于：

匿名子程序块                                 Procedure子程序块
1.它不存在于数据库中                         1.它可以存储于数据库中
2.每次使用时都会进行重新编译                  2.可以在任何需要的地方调用
3.不能在其他块中相互调用

创建Procedure的基本语法

create [or replace] procedure 存储过程名
[(参数名 in|out|in out 参数数据类型)] is|as
变量声明部分
begin
　　存储过程执行部分
end 存储过程名
参数的三种模式说明

模式|作用
-|-
in|参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变
out|参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
in out|表示高参数可以向该过程中传递值，也可以将某个值传出去

存储过程示例：

create or replace procedure p_sample(
    param_in in number := 10,
    param_out out number,
    param_inout in out number
) is
    param_temp  number;
begin
    param_temp := param_in;
    dbms_output.put_line('The value of param_temp is ' || param_temp);
    -- param_in := 200;
    param_temp := param_out;
    dbms_output.put_line('The value of param_temp is ' || param_temp);
　
    dbms_output.put_line('The value of param_out is ' || param_out);
    param_out := 1000;
    dbms_output.put_line('After updating, The value of param_out is ' || param_out);
　
    dbms_output.put_line('The value of param_inout is ' || param_inout);
    param_inout := 789;
    dbms_output.put_line('After updating, The value of param_inout is ' || param_inout);
end p_sample;

如何调用此过程

-- 调用方式一：编辑一个匿名子程序来调用p_sample存储过程
declare
    a number := 15;
    b number := 25;
    c number := 35;
begin
    dbms_output.put_line('a before === ' || a);
    dbms_output.put_line('b before === ' || b);
    dbms_output.put_line('c before === ' || c);
    p_sample(a, b, c);
    dbms_output.put_line('a after === ' || a);
    dbms_output.put_line('b after === ' || b);
    dbms_output.put_line('c after === ' || c);
end;

-- 调用方式二：利用sqlplus终端的命令 execute 命令来执行
-- 因为三个参数中有out的模块，所以不能传值
variable a number;  //定义一个变量
print :a;           //打印一个变量的值
execute p_sample(15, :a, :a);
execute p_sample(param_in=>:a, param_out=>:a, param_inout=>:a);
出错时可以用show errors;来查看具体错误信息

查看创建的过程名:select object_name from user_procedures;

注意：只有是in模式的才可以给默认值，建议使用第一种方法进行调用
删除存储过程命令drop procedure 存储过程名;

Oracle自定义函数

创建自定义函数语法：

create [or replace] function 函数名(
　　参数一参数模型数据类型,
　　参数一参数模型数据类型,
　　….
　　参数n 参数模型数据类型
) return 数据类型
as/is
　　临时变量名1 数据类型;
　　临时变量名2 数据类型;
　　临时变量名3 数据类型;
　　….
begin
　　函数体
　　return 表达式;
end [函数名];

关于函数示例代码：

create or replace function am2num(
    num1 in number,
    num2 in number
) return number
as
    t1 number;
    t2 number;
    t3 number;
begin
    t1 := num1 + num2;
    t2 := num1 - num2;
    t3 := t1 + t2;
    return (t3 + 2);
end am2num;

删除自定义函数的命令drop function 自定义函数名;
测试代码：select am2num(4, 2) from dual;

Oracle包

包的作用:包可以将任何出现在块声明的语句(如：过程,函数,游标,类型,变量)放于包中,包相当于一个容器
用户可以从其他PL/SQL块中对其进行引用等同于包为PL/SQL提供了全程变量
包分为两部分：包头和包体， 创建包头的语法：

create or replace package 包名
　　as|is
　　　　procedure 过程名();
　　　　function 函数名() return 数据类型;
　　　　变量定义;
　　　　异常定义;
　　　　游标定义;
　　　　……
　　　　……
end 包名;
定义包头应当遵循以下原则:

包元素位置可以任意安排。然而在声明部分，对象必须在引用前进行声明
包头可以不对任何类型的元素进行说明。例如，包头可以只带过程和函数说明语句而不声明任何异常和类型
对过程和函数的任何声明都必须只对子程序和其参数进行描述，代码的实现只能在包体中出现。
它不同于块声明，在块声明中，过程和函数的代码可同时出现在声明部分

创建包体的语法：

create or replace package body 包名
　　as|is
　　　　procedure 过程定义;
　　　　function 函数定义;
　　　　……;
end 包名;

包体是与包头相互独立的，包体只能在包头完成编译后才能进行编译，包体中带有包头中
描述的子程序的具体实现的代码段。
除此之外，包体还可以包括具有包体全局属性的附加声明部分，但这些附加声明对于包头是不见的

-- 先定义一个包头
create or replace package sample_package
is 
    procedure addemp(
        emp_id number,
        emp_name varchar2,
        emp_lastname varchar2
    );
end;
　
-- 实现包体
create or replace package body sample_package
is 
    procedure addemp(
         emp_id number,
         emp_name varchar2,
         emp_lastname varchar2
     ) is
         begin
             insert into tbl_emp(id , name , lastname) values(emp_id, emp_name, emp_lastname);
             commit;
    end addemp;
end sample_package;

调用执行execute sample_package.addemp(1,'wfj','wfj');

包中的函数和过程可以重载但是以下条件不能重载:
1). 如果两个子程序的参数仅在名称和参数类型上不同，这两个程序不能重载

1
2
3

-- in, out为参数类型, number为数据类型.两个过程仅在参数类型上不同，不能重载
procedure overloadME(p_theparameter in number);
procedure overloadME(p_theparameter out number);

2). 不能根据两个函数的返回类型对其重载

1
2
3
-- 两个函数仅仅在返回类型不同，不能重载
function overloadME return date;
function overloadME return numer;

3). 重载子程序的参数的类族必须不同

1
2
3
-- char和varchar2属性同一类族，不能重载
procedure overloadME(p_theparameter in char);
procedure overloadME(p_theparameter in varchar2);

4). 打包子程序也可以重载

Oracle触发器

触发器与存储过程、函数一样在数据库中以独立对象存在，不同点在于其他两者需要用户显式调用，
触发器由一个事件来触发自动隐式地运行，它不能接受参数
Oracle事件是指对数据库的表进行的insert、update及delete或对视图进行类似的操作
用途：触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，
或用来监视对数据库的各种操作，实现审计的功能
分类：DML触发器、替代触发器、系统触发器
触发器的组成：触发时间、触发时间、触发操作、触发对象、触发条件、触发频率
注意：在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大
创建触发器的语法：

create [or replace] trigger 触发器名称
{before | after }
{insert | delete | update [of column [, column…]]}
[or {insert | delete | update [of column [, column…]]}]…
on [模式名.]表名 | [模式名.]视图名
[referencing {old [as] old | new [as] new| parent as parent}]　
[for each row]　
[when condition]　
pl/sql_block | call procedure_name;

触发器的示例：

create or replace trigger sample_trigger 
    after delete or insert or update 
    on tbl_emp
declare
    i number := 0;
begin
    select count(*) into i from tbl_emp;
    dbms_output.put_line('there are ' || i || ' recoreds in the table.');
end;

删除触发器语法：drop trigger 触发器名

如何执行动态的SQL语句

create or replace procedure test(tablename varchar2)
is
    v_sql varchar2(100);
begin
    -- delete from tbl_emp where id =1;
    v_sql := 'delete from ' || tablename || ' where id = 1';
    -- v_sql :='drop table '|| tablename;
    execute immediate v_sql;
end;

测试代码execute test('tbl_emp');