PL/SQL函数与过程

一、函数

  函数参数包括三种类型：输入参数、输出参数、输入输出参数；

  说明：函数必须包含return语句。

                 当定义参数函数时，只指定数据类型，不能指定长度。

1、无参函数

创建：

SQL> create or replace function cur_datetime

  2  return varchar2

  3  is

  4  begin

  5    return to_char(sysdate,'yyyy-mm-dd');

  6  end;

  7  /

调用：

SQL> begin

  2    dbms_output.put_line(cur_datetime);

  3  end;

  4  /

2012-03-14

 

PL/SQL 过程已成功完成。

2、带有输入参数的函数（缺省是in）

创建：

SQL> create or replace function get_sal(name varchar2)

  2  return number

  3  as

  4    v_sal empl.sal%type;

  5  begin

  6    select sal into v_sal from empl where upper(ename)=upper(name);

  7    return v_sal;

  8  end;

  9  /

调用：

SQL> begin

  2    dbms_output.put_line('工资'||get_sal('&name'));

  3  end;

  4  /

输入 name 的值:  jack

原值    2:   dbms_output.put_line('工资'||get_sal('&name'));

新值    2:   dbms_output.put_line('工资'||get_sal('jack'));

工资0

 

PL/SQL 过程已成功完成。

或者：

SQL> select get_sal('&name') 工资 from dual;

输入 name 的值:  jack

原值    1: select get_sal('&name') 工资 from dual

新值    1: select get_sal('jack') 工资 from dual

 

      工资

----------

       400

 

3、带有输出参数的函数

创建：

SQL> create or replace function get_info(no number,title out varchar2)

  2  return varchar2

  3  as

  4    name empl.ename%type;

  5  begin

  6    select ename,dept into name,title from empl

  7      where eno=no;

  8    return name;

  9  end;

 10  /

 

函数已创建。

调用：

SQL> declare

  2    v_name empl.ename%type;

  3    v_dept empl.dept%type;

  4  begin

  5    v_name:=get_info(&eno,v_dept);

  6    dbms_output.put_line('姓名：'||v_name||',部门'||v_dept);

  7  end;

  8  /

输入 eno 的值:  1001

原值    5:   v_name:=get_info(&eno,v_dept);

新值    5:   v_name:=get_info(1001,v_dept);

姓名：jack,部门sa

 

PL/SQL 过程已成功完成。

4、带有输入输出参数的函数

创建：

SQL> create or replace function get_upd_info

  2  (no number,sal_chg in out number)

  3  return varchar2

  4  as

  5    name empl.ename%type;

  6  begin

  7    update empl set sal=sal+sal_chg where eno=no

  8  returning ename,sal into name,sal_chg;

  9  return name;

 10  end;

 11  /

 

函数已创建。

调用：

SQL> declare

  2    v_eno empl.eno%type;

  3    v_name empl.ename%type;

  4    v_salchg empl.sal%type;

  5  begin

  6    v_eno:=&eno;

  7    v_salchg:=&incre;

  8    v_name:=get_upd_info(v_eno,v_salchg);

  9    dbms_output.put_line('姓名:'||v_name||'新工资'||v_salchg);

 10  end;

 11  /

输入 eno 的值:  1001

原值    6:   v_eno:=&eno;

新值    6:   v_eno:=1001;

输入 incre 的值:  400

原值    7:   v_salchg:=&incre;

新值    7:   v_salchg:=400;

姓名:jack新工资400

 

PL/SQL 过程已成功完成。

二、过程

 过程参数包括三种类型：输入参数、输出参数、输入输出参数。

1、建立无参的过程

创建：

SQL> create or replace procedure out_time

  2  is

  3    begin

  4      dbms_output.put_line(sysdate);

  5    end;

  6  /

 

过程已创建。

调用：

SQL> exec out_time

14-3月 -12

 

PL/SQL 过程已成功完成。

2、带有输入参数的过程

创建：

SQL> create or replace procedure add_emp(

  2    v_eno empl.eno%type,

  3    v_ename empl.ename%type,

  4    v_dept empl.dept%type,

  5    v_sal empl.sal%type)

  6  is

  7  begin

  8    insert into empl values(v_eno,v_ename,v_dept,v_sal);

  9  end;

 10  /

 

过程已创建。

调用：

SQL> exec add_emp('1011','ella','sa',6000);

 

PL/SQL 过程已成功完成。

3、带有输出参数的过程

创建：

SQL> create or replace procedure update_sal(

  2    no number,

  3    salary number,

  4    name out varchar2)

  5  is

  6  begin

  7    update empl set sal=salary where eno=no returning ename into name;

  8  end;

  9  /

 

过程已创建。

调用：

SQL> declare

  2    v_ename empl.ename%type;

  3  begin

  4    update_sal(&eno,&salary,v_ename);

  5    dbms_output.put_line('姓名：'||v_ename);

  6  end;

  7  /

输入 eno 的值:  1001

输入 salary 的值:  3000

原值    4:   update_sal(&eno,&salary,v_ename);

新值    4:   update_sal(1001,3000,v_ename);

姓名：jack

 

PL/SQL 过程已成功完成。

4、带有输入输出参数的过程

创建：

SQL> create or replace procedure divide(num1 in out number,num2 in out number)

  2  is

  3    v1 number;

  4    v2 number;

  5  begin

  6    v1:=trunc(num1/num2);

  7    v2:=mod(num1,num2);

  8    num1:=v1;

  9    num2:=v2;

 10  end;

 11  /

 

过程已创建。

调用：

SQL> declare

  2    n1 number:=&n1;

  3    n2 number:=&n2;

  4  begin

  5    divide(n1,n2);

  6    dbms_output.put_line('商：'||n1||'余数'||n2);

  7  end;

  8  /

输入 n1 的值:  3

原值    2:   n1 number:=&n1;

新值    2:   n1 number:=3;

输入 n2 的值:  4

原值    3:   n2 number:=&n2;

新值    3:   n2 number:=4;

商：0余数3

 

PL/SQL 过程已成功完成。