oracle pl/sql之函数(function)

上帝大脸

　　一.PL/SQL语言介绍
　　虽然SQL是用于从数据库中检索数据和添加、修改或删除数据的主要语言，但是它确实缺乏某些重要的编程构造。例如，SQL不能控制执行流，也不能为了以后的重用而将数据存储为变量，甚至在出现错误的时候不能执行特定的动作。Oracle采用PL/SQL解决了上述问题。PL/SQL表示对SQL语言的过程语言扩展(Procedural Language Extensions to SQL)。
　　在Oracle中集成PL/SQL之前，应用程序检索和操纵数据库信息的方式会受到限制。我们既可以通过诸如SQL*Plus之类的交互式工具所生成的脚本文件来向服务器发送SQL语句，也可以将这些SQL语句嵌入在一个名为Pro*C的语言预编译器内。后一种方法提供了我们想得到的处理能力，但是却难以实现。这种方法需要使用若干行代码来解释连接数据库的方式、运行的语句以及如何使用语句执行的结果。SQL与预编译语言可使用的数据类型也存在差异。PL/SQL则解决了上述两种方法的局限性。
　　PL/SQL代码必须被编写为若干名为代码块(block)的部分。因为PL/SQL是一种编译型语言，所以这些代码块在执行之前必须经过编译器的处理。编译是一个检查过程，这个过程能够确保代码中引用的对象存在以及语句具有正确的语法。代码在编译过程完成后可以运行，但是必须在PL/SQL引擎内运行。PL/SQL引擎不是一个与Oracle服务器分离的产品，而是Oracle数据库的一个集成部分，这个集成部分能够获取并执行PL/SQL代码块。
　　二.PL/SQL代码块
　　PL/SQL代码块具有两种形式：匿名块与命名块。匿名(anonymous)PL/SQL代码块是头部不具有名称的PL/SQL代码。此时，我们可以通过诸如SQL*Plus之类的交互式工具将匿名块发送至PL/SQL引擎，这些代码块随后会立即运行。须要记住的是，PL/SQL是一种编译型语言，因此匿名块会被编译并运行，随后则会消失。如果希望再次进行运行，则必须将完整的代码块再次发送至PL/SQL引擎，这些代码在PL/SQL引擎内会再次被编译并运行，随后又会消失。为了更易于再次运行，匿名块可以被存储至操作系统的脚本文件中。
　　使用命名(named)PL/SQL代码块的名称就可以多次"调用"命名块。因此，命名块经常被用于实现某个程序内的模块化。这个程序可以被分为若干能够被多次调用的模块或子程序。Oracle中存在下列4种命名子程序：过程、函数、程序包和触发器。过程与函数是在其拥有者的模式中可以被创建为数据库对象的子程序。如果进行了上述创建，那么这些过程与函数就被称为存储子程序(stored subprogram)。使用存储子程序的优点是它们在创建阶段经过编译，随后在不需要重编译开销的情况下能够多次运行。程序包是若干过程与函数的集合，并且无法通过其名称被调用，但是对于程序包内的不同子程序(也就是过程与函数)来说，只要前面添加了程序包名，就可以使用其名称分别调用这些子程序。触发器是在发生触发动作(如在某个表中插入一条记录、用户登入数据库或出现系统错误)时被自动调用的代码块。
　　所有PL/SQL代码块都具有相同的结构，包括一个用于声明变量和其他标识符的声明部分(这一部分以DECLARE关键字开始)、一个用于运行代码的执行部分(这一部分以BEGIN关键字开始)，一个用于捕获错误的异常部分(这一部分以EXCEPTION关键字开始)以及一个使用END关键字指示的代码块结束符。对于匿名PL/SQL代码块，必需的元素只有BEGN和END关键字(以及这两个关键字之间的代码)。
　　有效的PL/SQL代码块只需要BEGIN和END关键字，并且这两个关键字之间至少要存在一行有效的代码。
　　PL/SQL代码块的每行代码都使用分号来结束，同时Oracle将整个代码块视为一个执行单元，这意味着先运行完该代码块，然后再将运行结果发送至调用程序或客户工具。下面示例给出了一个匿名PL/SQL代码块：
　　DECLARE
　　Val1 NUMBER ：= 5；
　　Val2 NUMBER ：= 2；
　　TheAnswer NUMBER；
　　BEGIN
　　TheAnswer：=Val1 + Val2；
　　DBMS OUTPUTPUT_LINE(：′The answer is′ | | TheAnswer)；
　　EXCEPTION
　　WHEN ZERO_DIVIDE THEN
　　DBMS_OUTPUT.PUT_ LINE(′Cannot divide by zero！′)；
　　END；
　　命名PL/SQL代码块具有相似的结构，但是允许将某些参数传入代码块，并且可以选择向调用程序返回某些值。函数必须总是返回一个值并指定返回类型，而过程与触发器则不需要实现某些功能。下面示例演示了如何将前面给出的一个匿名PL/SQL代码块作为一个过程(注意在过程名与参数后面不需要使用DECLARE关键字定义的声明部分)：
　　CREATE OR REPLACE PROCEDURE Add_Nums (Val1 IN NUMBER)
　　AS
　　TheAnswer NUMBER；
　　BEGIN
　　TheAnswer ：= Val1 + Val2
　　DBMS_OUTPUT.PUT LINE('The answer is' 丨丨 TheAnswer)；
　　EXCEPTION
　　WHEN ZERO_DIVIDE THEN
　　DBMS OUTPUT.PUT LINE('Cannot divide by zero！')；
　　END；
　　为了调用上面这个过程并向其传递要相加的数字，需要在SQL*Plus、SQL Worksheet或其他支持的客户查询工具中执行如下所示的命令：
　　Execute Add_nums(10，2)
　　三.存储过程和函数的区别
　　过程与函数（另外还有包与触发器）是命名的PL/SQL块（也是用户的方案对象），被编译后存储在数据库中，以备执行。因此，其它PL/SQL块可以按名称来使用他们。所以，可以将商业逻辑、企业规则写成函数或过程保存到数据库中，以便共享。
　　过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：
　　函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如 修改、插入数据库表或执行某些DDL语句等等
　　函数语法：
　　create or  replace function function_name(argu1 datatype,argu2 datatype...)
　　return datatype
　　is|as
　　PL/SQL Block;
　　在建立函数时，在函数头部必须要带有RETURN语句，在函数体内至少要包含一条RETURN语句。
　　1.不带任何参数
　　---code
　　create or replace function fun_user
　　return varchar2
　　is
　　v_user varchar2(50);
　　begin
　　select username  into v_user from user_users;
　　return v_user;
　　end;
　　-------
　　//调试
　　SQL> select fun_user from dual;    ---method
　　FUN_USER
　　--------------------------------------------------------------------------------
　　SCOTT
　　SQL>
　　---mothod
　　SQL> var v_res varchar2(100);
　　SQL> exec :v_res :=fun_user;
　　PL/SQL procedure successfully completed.
　　SQL> print v_res;
　　V_RES
　　--------------------------------------------------------------------------------
　　SCOTT
　　2.带IN参数
　　//通过雇员名获取员工薪水
　　create or replace function fun_get_sal(v_ename varchar2) return number is
　　v_error_code    number;
　　v_error_message varchar2(100);
　　v_sal           number;
　　begin
　　select sal into v_sal from emp where upper(ename) = upper(v_ename);
　　return v_sal;
　　exception
　　when others then
　　v_error_code    := sqlcode;
　　v_error_message := substr(sqlerrm, 1, 100);
　　insert into errors
　　(error_id, program_name, error_code, error_message)
　　values
　　(seq_errors.nextval, 'fun_get_sal', v_error_code, v_error_message);
　　commit;
　　end;
　　3.带OUT参数
　　如果要同时返回多个数据，需要使用输出参数
　　//返回雇员名所在的部门名和岗位
　　---code
　　create or replace function fun_ename_info(v_ename varchar2,v_job out varchar2)
　　return varchar2
　　is
　　v_deptname dept.dname%type;
　　begin
　　select b.dname,a.job into  v_deptname,v_job from emp a
　　join dept b on a.deptno=b.deptno
　　where upper(a.ename) = upper(v_ename);
　　return v_deptname;
　　end;
　　------------------
　　//调试
　　SQL> var job varchar2(50);
　　SQL> var dname varchar2(50);
　　SQL>
　　SQL> exec :dname :=fun_ename_info('scott',:job);
　　PL/SQL procedure successfully completed.
　　SQL> print dname job;
　　5.练习
　　//function
　　1.建立函数fun_valid_customer，根据输入的客户号，检查客户是否存在，如果客户存在，则返回TRUE，否则返回FALSE。
　　---------------------------------------
　　create or replace function fun_valid_customer(v_customer_id number) return boolean
　　is
　　v_tmp number;
　　begin
　　select 1 into v_tmp from customers where customer_id=v_customer_id;
　　return true;
　　exception
　　when no_data_found then
　　return false;
　　end;
　　---------------------------------------
　　2.建立函数fun_get_total，根据输入的订单号返回订单总价，然后调用该函数。当建立函数fun_get_total时，实现规则：
　　如果订单不存在，则显示自定义错误消息“ORA-20001:Please check correct order no.”
　　---------------------------------------
　　create or replace function fun_get_total(v_order_id number)
　　return number
　　is
　　v_total number;
　　begin
　　select total into v_total from orders where order_id = v_order_id;
　　return v_total;
　　exception
　　when others then
　　raise_application_error(-20001,'Please check correct order no.');
　　end;
　　#调用1
　　select fun_get_total(2) from dual;
　　#调用2
　　select fun_get_total(2440) from dual;
　　----------------------------------------
　　3.建立过程pro_add_order，根据输入的订单号，预定日期，客户号，交付日期和订单总价，为ORDERS表插入数据，然后调用过程。当建立过程pro_add_order，实现规则：
　　使用fun_valid_customer检查客户号是否正确；如果正确，则插入数据，否则显示自定义错误消息“ORA-20001,Please check correct customer no.”
　　如果交付日期小于预定日期，则显示错误信息“ORA-20002:交付日期必须在预定日期之后。”
　　如果输入了已经存在的订单号，则显示自定义错误信息“ORA-20003：该订单已经存在。”
　　---------------------------------------------
　　create or replace procedure pro_add_order(v_order_id number,v_order_date timestamp,
　　v_customer_id number,v_ship_date timestamp,v_total number)
　　is
　　test varchar2(20);
　　begin
　　if fun_valid_customer(v_customer_id) then
　　if v_order_date > v_ship_date then
　　raise_application_error(-20002,'ship_date must after the order_date');
　　else
　　insert into orders values(v_order_id,v_order_date,v_customer_id,v_ship_date,v_total);
　　end if;
　　else
　　raise_application_error(-20001,'Please check correct customer no.');
　　end if;
　　exception
　　when dup_val_on_index then
　　raise_application_error(-20003,'this order is already exist');
　　end;
　　#调用1
　　set serveroutput on;
　　call pro_add_order(2458,sysdate,138,sysdate+1,455);
　　#调用2
　　set serveroutput on;
　　call pro_add_order(24,sysdate,10,sysdate+1,455);
　　--------------------------------------------
　　4.建立过程pro_delete_order，根据输入的订单号取消特定订单，然后调用该过程。实现规则：
　　(1).如果订单不存在，则显示错误信息“ORA-20001，请检查并输入正确的订单号。”
　　--------------------------------------------
　　create or replace procedure pro_delete_order(v_order_id number)
　　is
　　begin
　　delete from orders where order_id=v_order_id;
　　if sql%notfound then
　　raise_application_error(-20001,'please input the correct order_id');
　　end if;
　　end;
　　set serveroutput on;
　　call pro_delete_order(2438);
　　-------------------------------------------