Oracle函数与存储过程

本文主要是介绍Oracle函数与存储过程，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一. PL/SQL块

　　1. 命名的PL/SQL块：将一个完整业务逻辑的PL/SQL块起一个名字，然后进行独立的编译之后保存到数据库中，然后在后续使用时直接可以通过名称调用

　　2. 匿名的PL/SQL块：重用性不高，并且执行效率也不高

　　优点：代码重用性和效率比较高

　　常见的命名的PL/SQL块：函数，存储过程，触发器，程序包等等

二. 函数

　　自定义函数：

　　　　语法：create [or replace] function 函数名[（参数列表）]

　　　　　　　return 数据类型

　　　　　　　is

　　　　　　　　[变量声明]

　　　　　　　begin

　　　　　　　　需要执行的语句;

　　　　　　　　return 返回的值;

　　　　　　　end [函数名];

　　　　使用场景：如果业务逻辑最终需要返回一个结果则可以使用函数。

　　　　

　　　　例子：

　　　　　　create or replace function avg_sal return number

　　　　　　is

　　　　　　　emp_avg_sal number;

　　　　　　begin

　　　　　　　select avg(sal) into emp_avg_sal from emp;

　　　　　　　return emp_avg_sal;

　　　　　　end avg_sal;

 

　　　　调用方式：

　　　　　　可以在select语句中调用，也可以在PL/SQL块中使用

　　　　删除函数：

　　　　　　drop function 函数名;

三. 存储过程

　　存储过程：procedure

　　（1）创建存储过程

　　　　语法：create [or replace] procedure 存储过程名[（参数名 数据类型，参数名 数据类型，...）]

　　　　　　　is/as

　　　　　　　　[变量的定义]

　　　　　　　begin

　　　　　　　　需要执行的代码

　　　　　　　[exception

　　　　　　　　异常处理]

　　　　　　　end;

　　　　例子：

　　　　　　create or replace procedure pro_insert_dept

　　　　　　is

　　　　　　begin

　　　　　　　insert into dept values(21,'销售部','深圳');

　　　　　　end;

 

　　（2）存储过程的调用

　　　　在PL/SQL块调用

　　　　　　语法：

　　　　　　　begin

　　　　　　　　存储过程名;

　　　　　　　end;

　　　　　　例子：(接上面)

　　　　　　　begin 

　　　　　　　　pro_insert_dept;

　　　　　　　end;

　　　　在sqlplus中调用

　　　　　execute/exec 存储过程名[（参数值，参数值，...）];

 

　　（3）存储过程的参数

　　　　形参：在函数或者是存储过程创建的时候规定的参数，没有具体的值

　　　　实参：在函数或这是存储过程调用时传入的值或带有值的变量

　　　　参数传递方式：

　　　　　　1. 按照位置传参

　　　　　　　实参的顺序和形参的顺序要一一对应

　　　　　　2. 按照形参传参

　　　　　　　语法：形参名 => 实参值

　　　　　　　例子：name => '张三'

　　　　　　3. 混合传参

　　　　　　　既有按位置传参又有名称传参，但是如果第一个参数就是按照名称传参，后续一般不建议使用位置传参

 

　　（4）存储过程的参数模式

　　　　1. in模式：参数的值只能从存储过程外部传入，并且在存储过程内部不能对数据进行修改

　　　　　语法： 参数名 [in] 数据类型

　　　　　注意： 在存储过程创建时定义的参数模式默认为 in 模式 

　　　　　in模式参数的默认值设置：

　　　　　　语法：参数名 in 数据类型 default 默认值

　　　　　　如果给输入参数设置了默认值，则在调用的时候可以不用输入参数，但是要注意参数顺序

　　　　2. out模式：输出参数，相当于函数中的return，out参数的值只能在存储过程内部被赋值，不能从外部传入

　　　　　语法： 参数名 out 数据类型

　　　　　例子：

　　　　　　create or rplace procedure pro_select_emp (e_no number,e_name out varchar2,e_job out varchar2)

　　　　　　is

　　　　　　begin

　　　　　　　select ename,job into e_name,e_job from emp where empno = e_no;

　　　　　　end;

　　　　　调用：

　　　　　　declare

　　　　　　　e_name emp.ename%type;

　　　　　　　e_job emp.job%type;

　　　　　　begin

　　　　　　　pro_select_emp(7369,e_name,e_job);

　　　　　　　dbms_output.put_line('姓名：'||e_name||' 职位'||e_job);

　　　　　　end;

　　　　3. in out 模式： 输入输出参数，参数的值可以从外部传入到存储过程内部，并且在内部进行重新赋值后返回

　　　　　语法：参数名 in out 数据类型

 

　　（5）删除存储过程

　　　　drop procedure 存储过程名

 

　　（6）存储过程和函数的区别

　　　　1. 关键字不同

　　　　2. 函数必须使用 return 来返回数据，但是存储过程要使用 out 参数进行返回数据

　　　　3. 函数可以用 select 语句进行调用，但是存储过程不能使用 select 语句进行调用

　　　　4. 函数只能返回一个值，但是存储过程可以返回多个值

　　　　5. 函数只有一种模式的参数，但是存储过程有3种模式的参数

四. 序列：sequence

　　在 Oracle 数据库中，序列是可以用来生成一系列唯一的数字的一种数据库对象

　　1. 序列的创建：

　　　create sequence 序列名

　　　[start with n　　　　--序列产生的起始位置，默认为1

　　　 increment by n　　 --序列增长的步长，默认为1

　　　 minvalue n　　　　--最小值，默认为1

　　　 maxvalue n　　　   --最大值，默认为99999999999999999999

　　　 cache n|nocache　  --缓存设置，默认为20

　　　]

　　2. 序列的使用

　　　nextval：生成下一条数字

　　　currval：获取当前的数字

　　　注意：在创建好一个序列之后，在使用currval之前必须先执行一次nextval来生成数字

　　　语法：select 序列名.nextval/currval from dual;

　　　使用：create table  user_t(

　　　　　　　　u_id number,

　　　　　　　　u_name varchar2(20)

　　　　　　)

　　　　　　insert into uesr_t values (序列名.nextval,'小明');

五. 程序包

　　将数据库中的对象封装到一起，方便后期的管理和维护，并且可以提高程序的效率

　　程序包的组成

　　　1.程序包规范：package

　　　　规范中主要保存一些声明操作，比如：游标、类型、变量、存储过程的声明，函数的声明等

　　　　语法：create [or replace] package 包名

　　　　　　　is

　　　　　　　[游标声明]

　　　　　　　[变量声明]

　　　　　　　[类型声明]

　　　　　　　[存储过程声明]

　　　　　　　[函数声明]

　　　　　　　end [包名];

　　　　例子：

　　　　　create or replace package pack_age

　　　　　is

　　　　　　procedure pro_emp(e_no number,e_name out varchar2,e_sal out number);

　　　　　　function fun_sal(e_deptno number) return number;

　　　　　end pack_age;

　　　2.程序包主体：package body

　　　　主要就是对规范中的声明的子程序进行一个具体的实现

　　　　语法：create or replace package body 包名

　　　　　　　is

　　　　　　　[存储过程的实现]

　　　　　　　[函数的实现]

　　　　　　　...

　　　　　　　end 包名;

　　　　例子：

　　　　　create or replace package body pack_age

　　　　　　procedure pro_emp(e_no number,e_name out varchar2,e_sal out number)

　　　　　　is

　　　　　　begin

　　　　　　　select ename,sal into e_name,e_sal from emp where empno = e_no;

　　　　　　end pack_emp;

 

　　　　　　function fun_sal(e_deptno number) return number

　　　　　　is

　　　　　　　v_sal number;

　　　　　　begin

　　　　　　　select max(sal) into v_sal from emp where deptno = e_deptno;

　　　　　　　return v_sal;

　　　　　　end fun_sal;

　　　　　end pack_age;

　　3. 程序包中的子程序的引用

　　　　包名.子程序名

　　4. 引用游标的使用（ref游标）：可以使用引用游标来返回一个查询的结果集

　　　使用步骤：

　　　（1）定义一个引用游标类型

　　　　　type 类型名 is ref cursor;

　　　（2）在存储过程中使用out参数将游标类型返回

　　　　　参数名 out 包名.游标类型名;

　　　（3）在存储过程实现的时候，将查询结果放入到引用游标的输出参数中

　　　　　open 参数名 for select 查询语句;

　　　（4）在调用存储过程的时候，也需要定义引用类型的游标对输出参数进行接受

 

　　　编写规范：

　　　　create or replace package pack_emp

　　　　is

　　　　　type ref_cur is ref cursor;

　　　　　procedure pro_emp(d_no number,row_emp out pack_emp.ref_cur);

　　　　end pack_emp;

　　　　create or replace package body pack_emp

　　　　is

　　　　　procedure pro_emp(d_no number,row_emp out pack_emp.ref_cur);

　　　　　is

　　　　　begin

　　　　　　open row_emp for select * from emp where deptno = d_no;

　　　　　end pro_emp;

　　　　end pack_emp;

　　　调用：

　　　　declare

　　　　　r_emp pack_emp.ref_cur;

　　　　　row_e emp%rowtype;

　　　　begin

　　　　　pack_emp.pro_emp(30,r_emp);

　　　　　loop

　　　　　　fetch r_emp into row_e;

　　　　　　exit when r_emp %notfound;

　　　　　　　dbms_output.put_line(row_e.ename||','||row_e.sal);

　　　　　end loop;

　　　　end;

六. 定时任务调度

　　Oracle中可以在系统中设置一些定时任务，并且规定好执行的时间，然后系统会自动去执行该操作

　　Oracle数据库中定时任务的操作需要使用一个程序包：dbms_job：

　　1. 设置定时任务

　　　dbms_job.submit(

　　　　job out number,　　　--定时任务生成的编号

　　　　what in varchar2,　　 --设置定时任务要执行的存储过程

　　　　next_date in date,　　--设置任务下一次执行的时间

　　　　interval in varchar2　  --设置任务执行的时间间隔

　　　)

　　　例子：每隔1分钟向数据库添加一条数据

　　　　create sequence test_pk nocache;

　　　　create or replace procedure pro_insert_user

　　　　is

　　　　begin

　　　　　insert into user_t values(test_pk.nextnvl,'小明');

　　　　　commit;

　　　　end pro_insert_user;

　　　　

　　　　declare

　　　　　jobid number;

　　　　begin

　　　　　dbms_job.submit( job=>jobid,what=>'pro_insert_user;',next_date=>sysdate,interval=>'sysdate+(1/24/60)');

　　　　　dbms_output.put_line(jobid);

　　　　end;

　　2.查询系统中当前的所有定时任务

　　　数据字典：dba_jobs

　　　select * from dba_jobs;

　　　注意：只能在管理员模式下进行查询

　　3.禁用/启用定时任务

　　　dbms_job.broken(任务编号，true/false);

　　4.删除定时任务

　　　dbms_job.remove(任务编号)；

　　　注意删除定时任务之前先将定时任务禁用

这篇关于Oracle函数与存储过程的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！