开发PLSQL子程序

1. 子程序:

子程序是已命名的PL/SQL块,可带参数并可从调用环境中进行调用.与未命名或匿名PL/SQL块一样,

子程序具有声明部分,可执行部分和可选的异常处理部分;

子程序的优点:

l        模块化: 通过子程序,可以将程序分解为可管理的,明确的逻辑模块.

l        可重用性: 子程序在创建并执行后,就可以在任意数目的应用程序中使用;

l        可维护性: 子程序可以简化维护操作,因为如果一个子程序受到影响,则只需修改该子程序的定义

1.1开发存储过程

1)建立带参或者不带参的存储过程

例如:

create or replace procedure find(e_sal number)    --建立过程
is
    cursor cur_emp is select ename,sal fromscott.emp where sal<=e_sal;   --定义一个游标
    row_emp cur_emp%rowtype;           --基于游标定义一个记录
begin
  open cur_emp;
  fetch cur_emp into row_emp;
  while cur_emp%found        --循环检测游标属性,看是否到最后一行
  loop
    dbms_output.put_line(row_emp.ename||'--'||row_emp.sal);
  fetch cur_emp into row_emp;         --从游标提取一行到记录中
  end loop;
  close cur_emp;
end;
 
call find(1500);   --调用find过程

2)建立带输出参数的存储过程

在过程中输出数据用OUT或IN OUT参数来完成的.

 

--建立带OUT输出参数的过程

create OR REPLACEPROCEDURE query_employee
(eno number,NAME OUTVARCHAR2,salary OUT NUMBER)
IS
BEGIN
    SELECT ename , sal INTO NAME,salary FROMscott.emp WHERE empno = eno;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20000,'该雇员不存在');
END;

以下是在SQL*PLUS中调用该过程的示例:

 

SQL> var namevarchar2(10)
SQL> var salarynumber
SQL> execquery_employee(7799,:name,:salary)
 
beginquery_employee(7799,:name,:salary); end;
 
ORA-20000: 该雇员不存在
ORA-06512: 在"SYS.QUERY_EMPLOYEE", line 8
ORA-06512: 在line 1
name
---------
 
salary
---------

1.2开发函数

函数是一段独立的PL/SQL程序代码,它执行某个特定的,明确的任务.通常,函数将处理从程序的调用部分传递给它的信息,然后返回单个值.信息通过称为参数的特殊标识符传递给函数,然后通过return语句返回.建立函数语法如下:

 

Create[or replace]function function_name
(argument1[model]datatype1,
Argument2[model]datatype2,
…
)
Return datetype
is|as
       PL/SQL Block;

建立函数时参数可带可不带,当定义参数是,如果不指定参数模式,则默认输入参数,所以IN关键字既可以指定也可以不指定

示例如下:

--建立带参的函数

create or replacefunction get_user(e_no in number)
return varchar2
is
  v_user scott.emp.ename%type;
begin
   select ename into v_user from scott.empwhere empno=e_no;
   return v_user;
end;
--调用函数
declare
 e_name scott.emp.ename%type;
begin
  e_name := GET_USER(7369);
  dbms_output.put_line(e_name);
end;

一般情况下,函数只需要返回单个数据.如果希望使用函数返回多个数据,就需要使用输出参数了,以下为建立带OUT参数函数模式

--建立带输出参数的函数
create or replacefunction get_info
(NAME IN VARCHAR2,titleOUT VARCHAR2)
return varchar2
as
  deptname scott.dept.dname%type;
begin
    select a.job,b.dname into title,deptnamefrom scott.emp a, scott.dept b
     where a.deptno = b.deptno
     and upper(ename)=upper(name);
    return deptname;
EXCEPTION 
      WHEN NO_DATA_FOUND THEN
           raise_application_error(-20000,'该雇员不存在');
end;
 
SQL> var jobvarchar2(20)
SQL> var dnamevarchar2(20)
SQL> exec :dname:=get_info('scott',:job)
 
PL/SQL 过程成功完成
dname
---------
RESEARCH
job
---------
ANALYST