开发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)ISBEGIN    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 salarynumberSQL> execquery_employee(7799,:name,:salary) beginquery_employee(7799,:name,:salary); end; ORA-20000: 该雇员不存在ORA-06512: 在"SYS.QUERY_EMPLOYEE", line 8ORA-06512: 在line 1name--------- salary---------

1.2开发函数

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

 

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

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

示例如下:

--建立带参的函数

create or replacefunction get_user(e_no in number)return varchar2is  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 varchar2as  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---------RESEARCHjob---------ANALYST