你的位置:首页 > 数据库

[数据库]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
语句

 

 程序包

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

  希腊旅游多少钱跟团希腊旅游网希腊特价旅游希腊旅游时间希腊旅游跟团深圳哪里有卖旅游用品的店? 深圳有哪些服装批发市场? 深圳有哪些美术馆? 深圳有哪些游乐园?哪里最好玩? 2015中秋带小孩去深圳儿童公园有什么好玩的项目? 2015年中秋节去西冲海滩赏月好不好? 2015年国庆去西冲玩要多少钱? 2015深圳有哪些免费旅游公园景点有哪些? 春节时深圳到北京有没有打折机票? 春节去三亚旅游要带什么必备用品? 有广州到丽江的直达火车吗?价格多少? 江西庐山有哪些景点最值得一看?不可错过的景点是? 樱约而来 3月去琼海看樱花 千泷沟大瀑布怎么走?从化千泷沟大瀑布门票多少钱? 重庆主城最佳樱花观赏地:大学城 千泷沟大瀑布地址?从化千泷沟大瀑布在哪里? LME49720MAX/NOPB Datasheet LME49720MAX/NOPB Datasheet LM1771SSD/NOPB Datasheet LM1771SSD/NOPB Datasheet LME49722MA/NOPB Datasheet LME49722MA/NOPB Datasheet 中华恐龙园好玩吗 中华恐龙园好玩吗 中华恐龙园好玩吗 峨嵋金顶 峨嵋金顶 峨嵋金顶 五台山位于 五台山位于 五台山位于