你的位置:首页 > 数据库

[数据库]Oracle学习笔记十二 子程序(存储过程、自定函数)和程序包

子程序

子程序:命名的 PL/SQL 块,编译并存储在数据库中。
 
子程序的各个部分:
1.声明部分
2.可执行部分
3.异常处理部分(可选)
子程序的分类:
1.过程 - 执行某些操作
2.函数 - 执行操作并返回值

 

子程序的优点:

  模块化
    将程序分解为逻辑模块
  可重用性
    可以被任意数目的程序调用
  可维护性
    简化维护操作
  安全性
    通过设置权限,使数据更安全

 存储过程

过程是用于完成特定任务的子程序。
例如:

 

 一个购票过程可以分为很多个子过程,分别完成。

创建存储过程

创建过程的语法:

CREATE [OR REPLACE] PROCEDURE  <procedure name> [(<parameter list>)]IS|AS  <local variable declaration>BEGIN  <executable statements>[EXCEPTION  <exception handlers>]END;

 

CREATE OR REPLACE PROCEDURE find_emp (emp_no NUMBER)AS empname VARCHAR2(20);BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname);EXCEPTION WHEN NO_DATA_FOUND THEN  DBMS_OUTPUT.PUT_LINE ('雇员编号未找到');END find_emp;

 

 

我们还可以:

--查询用户所定义的存储过程select distinct name from user_source where type = 'PROCEDURE';--查看存储过程定义的源码内容(PL/SQL语句)select text from user_source where name = 'P_TEST';--如果定义有错误,查看错误原因Show error procedure 存储过程名--删除存储过程Drop procedure 存储过程名;

 

 

过程参数的三种模式:

IN
1.用于接受调用程序的值
2.默认的参数模式
OUT
1.用于向调用程序返回值
IN OUT
1.用于接受调用程序的值,并向调用程序返回更新的值
 

--统计满足指定工资数的员工的数量:带输入参数in的存储过程 

Create or replace procedure p_total_sal(var_sal in int) is   Var_count int;Begin  Select count(*) into var_count from emp where sal > var_sal;  Dbms_output.put_line(' 符合要求的员工总数为: ' || var_count);Exception  When others then    Dbms_output.put_line('未知错误');End;

 

--定义一个存储过程返回指定部门的员工总数:带返回值out的存储过程

Create or replace procedure p_get_emp(var_deptno int, var_total out int) as   Var_n int;Begin  Select count(*) into var_n from emp where deptno = var_deptno;  Var_total := var_n; --总数由参数返回End;

 

--使用Declare  Var_s int;Begin  p_get_emp(10, var_s);  Dbms_output.put_line('返回的值为' || var_s);End;

 

--定义一个存储过程,通过该存储过程能返回一个结果集(游标)。

Create or replace procedure p_get_datas(mycur out sys_refcursor) is Begin  Open mycur for select * from emp where deptno = 10;End;

 

 

--调用:Declare  Var_cur sys_refcursor; --接收参数  Row emp%rowtype;Begin  p_get_datas(var_cur);  --无需再次打开,因为在存储过程中已经打开过了  Loop    Fetch var_cur into row;    Exit when var_cur%notfound;      Dbms_output.put_line(row.ename || ' ' || row.job);  End loop;End;

--输入输出参数

--根据员工编号返回他的工资的存储过程Create or replace procedure p_get_sal(var_n in out int) isBegin  Select sal into var_n from emp where empno = var_n);End;

 

--调用:Declare  Var_s int;Begin  Var_s := &n;  p_get_sal(var_s);  Dbms_output.put_line('他的工资为:' || var_s);End;

 

 

 存储过程的使用

 

--存储过程的使用

1 命令方式:execute 存储过程名;

2 在PL/SQL中:直接使用 存储过程名 即可

 

调用存储过程时传递参数的方式。
    1、按照位置方式传递。

Swap(num1,num2);
    2、按名称方式传递。 

swap(p2=>num2,p1=>num1);  

(p1,p2是定义存储过程时参数名字)

 

将过程的执行权限授予其他用户:

GRANT EXECUTE ON find_emp TO MARTIN;GRANT EXECUTE ON swap TO PUBLIC;(所有数据库用户)

 

 

函数

函数是可以返回值的命名的 PL/SQL 子程序。
创建函数的语法:
CREATE [OR REPLACE] FUNCTION  <function name> [(param1,param2)]RETURN <datatype> IS|AS [local declarations]BEGIN  Executable Statements;  RETURN result;  EXCEPTION    Exception handlers;END;

 

定义函数的限制:
  函数只能接受 IN 参数,而不能接受 IN OUT 或 OUT 参数
  形参不能是 PL/SQL 类型
  函数的返回类型也必须是数据库类型
访问函数的两种方式:
  使用 PL/SQL 块
  使用 SQL 语句
 

创建函数

CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2ISBEGIN RETURN '朋友,您好';END;

从 SQL 语句调用函数:
SELECT fun_hello FROM DUAL;

 

 

CREATE OR REPLACE FUNCTION  item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER;BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price >= min_price AND price <= max_price  THEN  RETURN '输入的单价介于最低价与最高价之间'; ELSE  RETURN '超出范围'; END IF;END;

 

DECLARE P NUMBER := 300; MSG VARCHAR2(200);BEGIN MSG := item_price_range(300); DBMS_OUTPUT.PUT_LINE(MSG);END;

 

 

过程和函数的比较

过 程

函  数

作为 PL/SQL 语句执行

作为表达式的一部分调用

在规格说明中不包含  RETURN 子句

必须在规格说明中包含 RETURN 子句

不返回任何值

必须返回单个值

可以包含 RETURN 语句,但是与函数不同,它不能用于返回值

必须包含至少一条 RETURN
语句

 

 程序包

程序包是对相关过程、函数、变量、游标和异常等对象的封装,程序包由规范和主体两部分组成。