Oracle学习笔记十二 子程序（存储过程、自定函数）和程序包

时间：2021-01-16 05:15:18

子程序

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

 

子程序的优点：

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

 存储过程

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

 Oracle学习笔记十二 子程序（存储过程、自定函数）和程序包

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

创建存储过程

创建过程的语法：

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) is
Begin
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  VARCHAR2
IS
BEGIN
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
语句

 

 程序包

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

 Oracle学习笔记十二 子程序（存储过程、自定函数）和程序包

 

程序包规范

CREATE [OR REPLACE]
  PACKAGE 
  package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];

程序包主体

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];

 

 

例

CREATE OR REPLACE PACKAGE pack_me
IS
PROCEDURE order_proc (orno VARCHAR2);
FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2;
END pack_me;

 

 

CREATE OR REPLACE PACKAGE BODY pack_me AS
PROCEDURE order_proc (orno VARCHAR2) IS
    stat CHAR(1);
BEGIN
SELECT ostatus INTO stat FROM order_master
WHERE orderno = orno;
    ……
END order_proc;
FUNCTION order_fun(ornos VARCHAR2)
RETURN VARCHAR2
IS
    icode   VARCHAR2(5);
    ocode   VARCHAR2(5);
BEGIN
  ……
END order_fun;
END pack_me;

 

程序包的优点

  • 模块化
  • 更轻松的应用程序设计
  • 信息隐藏
  • 新增功能(过程可以重载，可以定义公用变量或游标)
  • 性能更佳

程序包中的游标

游标的定义分为游标规范和游标主体两部分。在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型。   RETURN子句指定的数据类型可以是： 1.用 %ROWTYPE 属性引用表定义的记录类型 2.程序员定义的记录类型   
CREATE OR REPLACE PACKAGE cur_pack IS
CURSOR ord_cur(vcode VARCHAR2)
RETURN order_master%ROWTYPE;
PROCEDURE ord_pro(vcode VARCHAR2);
END cur_pack;

 

 

CREATE OR REPLACE PACKAGE BODY cur_pack AS
CURSOR ord_cur(vcode VARCHAR2)
RETURN order_master%ROWTYPE IS 
SELECT * FROM order_master WHERE VENCODE=vcode;
PROCEDURE ord_pro(vcode VARCHAR2) IS
   or_rec order_master%ROWTYPE;
BEGIN
OPEN ord_cur(vcode); 
  LOOP
FETCH ord_cur INTO or_rec;
EXIT WHEN ord_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LIne('返回的值为' || or_rec.orderno);
END LOOP;
END ord_pro;
END cur_pack;

 

 

USER_OBJECTS 视图包含用户创建的子程序和程序包的信息 
SELECT object_name, object_type
　　FROM USER_OBJECTS
　　WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

 

 

USER_SOURCE 视图存储子程序和程序包的源代码 
SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';

 

标签：

相关文章