Oracle系列:(29)存储过程和存储函数

1、存储过程【procedure】

公司主营业务:成都网站设计、成都网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传,提高企业的竞争能力。创新互联建站是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化,感谢他们对我们的高要求,感谢他们从不同领域给我们带来的挑战,让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联建站推出鲅鱼圈免费做网站回馈大家。

什么是存储过程?

事先运用oracle语法写好的一段具有业务功能的程序片段,长期保存在oracle服务器中,供oracle客户端(例如,sqlplus)和程序语言远程访问,类似于Java中的函数。

为什么要用存储过程?

    (1)PLSQL每次执行都要整体运行一遍,才有结果

    (2)PLSQL不能将其封装起来,长期保存在oracle服务器中

    (3)PLSQL不能被其它应用程序调用,例如:Java

存储过程与PLSQL是什么关系?

存储过程是PLSQL的一个方面的应用,而PLSQL是存储过程的基础。

即存储过程需要用到PLSQL。

--------------------------------------------------------存储过程

语法:

create [or replace] procedure 过程名[(参数列表)]  
as
PLSQL程序体;

注意:存储过程中有【begin…end;/】,无declare

创建无参存储过程hello,无返回值,语法:create or replace procedure 过程名 as PLSQL程序

create or replace procedure hello
as
begin
       dbms_output.put_line('这是我的第一个存储过程'); 
end;
/

删除存储过程hello,语法:drop procedure 过程名

drop procedure hello;

调用存储过程方式一,exec 存储过程名

exec hello;

调用存储过程方式二,PLSQL程序

begin
  hello;  
end;
/

Oracle系列:(29)存储过程和存储函数

调用存储过程方式三,Java程序

JDBC中讲过一个对象:CallableStatement

创建有参存储过程raiseSalary(编号),为7369号员工涨10%的工资,演示in的用法,默认in,大小写不敏感

-- 定义过程
create or replace procedure raiseSalary(pempno number)
as
begin
  update emp set sal=sal*1.2 where empno=pempno;
end;
/

-- 调用过程
exec raiseSalary(7369);

Oracle系列:(29)存储过程和存储函数

创建有参存储过程findEmpNameAndSalAndJob(编号),查询7788号员工的的姓名,职位,月薪,返回多个值,演示out的用法

-- 定义过程
create or replace procedure findEmpNameAndSalAndJob(pempno in number,pename out varchar2,pjob out varchar2,psal out number)
as
begin
  select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
end;
/
-- 调用过程
declare
   pename emp.ename%type;
   pjob emp.job%type;
   psal emp.sal%type;
begin
  findEmpNameAndSalAndJob(7369,pename,pjob,psal);
  dbms_output.put_line('7369号员工的姓名是' || pename ||',职位是' || pjob || ',月薪是' || psal);
end;
/

Oracle系列:(29)存储过程和存储函数

什么情况下用exec调用,什么情况下用PLSQL调用存储过程?

exec适合于调用存储过程无返回值

plsql适合于调用存储过程有返回值,不管多少个

用存储过程,写一个计算个人所得税的功能

-- 定义存储过程
create or replace procedure get_rax(sal in number,rax out number)
as
  -- sal表示收入
  -- bal 表示需要交税的收收入
  bal number;
begin
  bal := sal - 3500;
  if bal <= 1500 then
    rax := bal * 0.03 - 0;
  elsif bal <= 4500 then
    rax := bal * 0.1 - 105;
  elsif bal <=9000 then
    rax := bal * 0.2 - 555;
  elsif bal <=35000 then
    rax := bal * 0.25 - 1005;
  elsif bal <= 55000 then
    rax := bal * 0.3 - 2755;
  elsif bal <=80000 then
    rax := bal * 0.35 - 5505;
  else 
    rax := bal * 0.45 - 13505;
  end if;
end;
/


-- 调用存储过程
declare
   -- 需要交的税
   rax number;
begin
   get_rax(&sal,rax);
   dbms_output.put_line('你需要交税' || rax);     
end;
/

Oracle系列:(29)存储过程和存储函数

2、存储函数

创建无参存储函数getName,有返回值,语法:create or replace function 函数名 return 返回类型 as PLSQL程序段

create or replace function get_name return varchar2
as 
begin
       return 'hello 你好';  
end;
/

删除存储函数getName,语法:drop function 函数名

drop function get_name;

调用存储函数方式一,PLSQL程序

declare
  name varchar2(20);
begin
  name := get_name(); 
  dbms_output.put_line(name); 
end;
/

调用存储函数方式二,Java程序

创建有参存储函数findEmpIncome(编号),查询7369号员工的年收入,演示in的用法,默认in

-- 定义存储函数
create or replace function findEmpIncome(pempno in number) return number
as
       income number; 
begin
  select sal*12+NVL(comm,0) into income from emp where empno=pempno;
  return income;
end;
/
-- 调用存储函数
declare
   income number;
begin
   income := findEmpIncome(&income);
   dbms_output.put_line('该员工的年收入为' || income);
end;
/

Oracle系列:(29)存储过程和存储函数

创建有参存储函数findEmpNameAndJobAndSal(编号),查询7788号员工的的姓名(return),职位(out),月薪(out),返回多个值

-- 定义存储函数
create or replace function findEmpNameAndJobAndSal(pempno in number,pjob out varchar2, psal out number) return varchar2
as
       pename emp.ename%type;
begin
       select ename,job,sal into pename,pjob,psal from emp where empno=pempno;
       return pename;       
end;
/

-- 调用存储函数
declare
   pename emp.ename%type;
   pjob emp.job%type;
   psal emp.sal%type;
begin
   pename := findEmpNameAndJobAndSal(&empno,pjob,psal);
   dbms_output.put_line('7369号员工的姓名是'|| pename ||',职位是'|| pjob || ',月薪是' || psal);
end;
/

3、存储过程和存储函数的适合场景

注意:适合不是强行要使用,只是优先考虑

什么情况下【适合使用】存储过程?什么情况下【适合使用】存储函数?

    【适合使用】存储过程:无返回值 或 有多个返回值时,适合用过程 

    【适合使用】存储函数:有且只有一个返回值时,适合用函数

   

什么情况【适合使用】过程函数,什么情况【适合使用】SQL?

    【适合使用】过程函数:

    》需要长期保存在数据库中

          》需要被多个用户重复调用

          》业务逻辑相同,只是参数不一样

    》批操作大量数据,例如:批量插入很多数据

    【适合使用】SQL:

    》凡是上述反面,都可使用SQL

    》对表,视图,序列,索引,等这些还是要用SQL 

批量添加操作示例:

-- 定义过程
create or replace procedure batchInsert
as
       i number(4) := 1;
begin
       for i in 1..999
           loop
             insert into emp(empno,ename) values(i,'员工'||i);
           end loop; 
end;
/

-- 调用过程
exec batchInsert;


标题名称:Oracle系列:(29)存储过程和存储函数
标题链接:http://ybzwz.com/article/ppcsdh.html