Oracle-存储过程——函数

/**
定义：存储过bai程（Stored Procedure ）是一组为了完成特定功能的SQL 语句du集，经编译后存储在数据库中zhi。用户dao通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE 中，若干个有联系的过程可以组合在一起构成程序包。
优点：
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某用户才具有对指定存储过程的使用权。

简单说，你在你的机器上写了个存储过程，这个存储过程像那些表里的数据一样被放在遥远的数据库服务器当中，但是它又是可执行的代码，其他能连到数据库服务器的用户，可以调用你写的存储过程
它的作用是隐藏细节，就是说，你写的存储过程代码可能很复杂，但是其他人调用它却很简单，不用具体知道它是如何做的，且一次能完成多个指令 
*/

----------------- 函数  ----
create or replace function my_fun  ------     表示无参
return  number
is
----- 声明部分
begin
  dbms_output.put_line('第一个无参的函数');
  return  1;
end;

-------- 函数不会自动执行   必须调用
---- 调用  
select my_fun from dual;  --- select  语句  调用函数

----------------- 函数  ----
create or replace function my_fun  ------  ()   里面必须有参数
return  boolean
is
----- 声明部分
begin
  dbms_output.put_line('第一个无参的函数');
  return  true;
end;
----------------- 函数  ----
 ------  ()   里面必须有参数
create or replace function my_fun return  date is
----- 声明部分
begin
  dbms_output.put_line('第一个无参的函数');
  return  sysdate;
end;

create or replace function my_fun  ------  ()   里面必须有参数
return  emp%rowtype
is
----- 声明部分

v_emp  emp%rowtype;

begin
  dbms_output.put_line('第一个无参的函数');
  
  select *  into v_emp  from emp where empno=7369;
  
  return  v_emp;
end;

--- 调用
declare

v_emp  emp%rowtype;

begin
 v_emp:= my_fun();
  dbms_output.put_line(v_emp.ename);
end;

create or replace function my_fun  ------  ()   里面必须有参数
return   emp.empno%type
is
----- 声明部分

v_emp  emp.empno%type;

begin
  dbms_output.put_line('第一个无参的函数');
  
  select empno  into v_emp  from emp where empno=7369;
  
  return  v_emp;
end;

select my_fun from dual;  ---      简单类型中  boolean 不能使用  只能返回 单列 单行

--------------  匿名块的调用方式
declare
v_return number;---接受函数的返回值

begin
    dbms_output.put_line('方式1-----------');
    v_return:=my_fun();
    dbms_output.put_line(v_return);
    dbms_output.put_line('方式2---------------');
    dbms_output.put_line(my_fun);
    dbms_output.put_line('方式3---------------');
    select my_fun  into v_return from dual;
    dbms_output.put_line(v_return);
end;
-----------------  有参的函数
create or replace function my_fun01(pid  number)
return number
is
  v_id number:=pid;---接受参数
begin
  dbms_output.put_line(pid);
  --pid:=12;  --  默认是输入参数   不能够修改   只能使用
  v_id:=12;
  return v_id;

end;
select my_fun01(10) from dual;
------- 根据编号  返回员工的信息
create or replace function get_emp_by_empno (p_empno number)
return emp%rowtype
is
   v_empinfo  emp%rowtype;
begin
  select * into v_empinfo  from emp where empno=p_empno;
  return v_empinfo;
exception
  when no_data_found then
     dbms_output.put_line(SQLCODE||SQLERRM);
     return v_empinfo;
end;

--执行 
declare
  v_count number:=&n;
  v_empinfo emp%rowtype;
begin
  v_empinfo:=get_emp_by_empno(v_count);
  dbms_output.put_line('--------'||v_empinfo.empno||v_empinfo.ename);
end;

---------  给某个部门的所有员工涨工资     返回值 boolean  表示是否涨工资  
create or replace function  add_sal_by_deptno (p_deptno number,p_sal number)
return boolean
is
  --------
begin
  update emp set sal=sal+p_sal where deptno=p_deptno;----隐式游标 
  
  if  sql%rowcount >0 then 
    return true;
  else
    return false;
  end if;
end;
select *from emp;
----调用
declare
v_flag boolean;------ 默认值是 false   
v_deptno number:=&n;
begin 
  select    deptno into v_deptno from dept where deptno=v_deptno;
   
  v_flag:=add_sal_by_deptno(v_deptno,1);
    if  v_flag  then 
       dbms_output.put_line('修改成功');
    else   
      dbms_output.put_line('修改失败');
    end  if;
exception
  when others then 
      dbms_output.put_line('部门不存在');
end;

--------------- 参数调用     --定位传参     前提得知道 参数的名字 
--调用  add_sal_by_deptno (p_deptno number,p_sal number)

declare
v_flag boolean;------ 默认值是 false   
v_deptno number:=&n;
begin 
  
   
  -- v_flag:=add_sal_by_deptno(v_deptno,1);--- 参数传递1
     v_flag:=add_sal_by_deptno(p_sal=>1,p_deptno=>v_deptno);--- 参数传递2 定位传参
  ----混合使用
     
    if  v_flag  then 
       dbms_output.put_line('修改成功');
    else   
      dbms_output.put_line('修改失败');
    end  if;
end;
---  ----混合使用
create or replace function  test_p (p_01 varchar2,p_02 number,p_03 date) 

return date 
is
begin
   dbms_output.put_line(p_01||p_02||p_03);
   return sysdate;
end;

declare

v_date  date;
begin
  --v_date:=test_p(p_03=>sysdate,p_01=>'字符串',p_02=>110);
  --v_date:=test_p(p_03=>sysdate,110,p_01=>'字符串');
   v_date:=test_p('字符串',110,p_03=>sysdate);------ 如果开始使用定位传参  后面的所有参数必须使用 定位传参  
   v_date:=test_p('字符串',p_03=>sysdate,p_02=>110);
 end;
--- 通过一个员工 名的 部分信息  返回含有该信息的 员工 个数
create or replace function empcount(empname varchar2)
return number
is
  e_count number;
begin
  --select count(*) into e_count from emp where ename like '%'||empname||'%';
  select count(*) into e_count from emp where instr(ename,empname)>0;
  return e_count;
end;
--调用
declare
  emp_count number;
  e_name varchar2(10):=&n;
begin
  emp_count:=empcount(e_name);
  dbms_output.put_line('名字包含'||e_name||'的人数为:'||emp_count);
end;
---- 显示员工的信息（给我一个员工  显示信息）   返回 date
create  or replace  function show_emp(p_emp emp%rowtype)
return  date
is
begin
   dbms_output.put_line('员工姓名'||p_emp.ename||'编号'||p_emp.empno);
   return  sysdate;
end;

declare  
v_emprow  emp%rowtype;
v_date    date;
begin
  select * into  v_emprow  from emp where empno=8888;
  v_date:= show_emp(v_emprow);-- 函数 调用  必须接受返回值

end;
 
------输出所有的员工信息   表
declare  
type emp_table_type is table of emp%rowtype  ;
v_emp_table  emp_table_type;
v_date    date;
begin
  select *  bulk  collect into  v_emp_table  from emp ;
  for  i in 1..v_emp_table.count loop
       v_date:= show_emp(v_emp_table(i));-- 函数 调用  必须接受返回值
  end loop;
end;

----输出所有的员工信息   游标
declare  
v_date    date;
v_emp  emp%rowtype;
cursor  cur_emp is select *from emp;
begin
   for  e in cur_emp  loop
      v_date:=show_emp(e);
   end loop; 
    
    open  cur_emp;
    loop 
      fetch  cur_emp  into v_emp  ;
      exit when cur_emp%notfound;
       v_date:=show_emp(v_emp);
    end loop;
end;
--------------------------- 参数   in 输入  out 输出   in out 输入输出     ------------------

create  or replace  function  test_fun01(p01   number ,p02  out  varchar2)
return number
is        --------------------  in 输入  out 输出     区别
-------- in  带入参数值   in 只能用于传值   不能被赋值 （final）
--------out  不能带入值 默认是 空   目的就是从函数中带出去值  需要在函数中赋值 （然后带出）        
begin
  --p01:=110;
  dbms_output.put_line('p01  in number'||p01);
  dbms_output.put_line('p02  in number'||p02);
  p02:='苏大强';
  return 1;
end;

declare
v_return number;
v_p02  varchar2(10) :='李强';
begin
  v_return :=test_fun01(11,v_p02);
  --- 如果参数 是 out  类型  通过  变量传递参数 （不能给定常量）
  
  dbms_output.put_line('v_p02  in number'||v_p02);
end;

----------- 默认 类型是 in 输入参数p03 
create  or replace  function  test_fun02(p01  in number ,p02  out  varchar2,p03 number)
return number
is         
begin
 -- p01:=110;
 -- p03:=110;
  dbms_output.put_line('p01  in number'||p01);
  dbms_output.put_line('p02  in number'||p02);
  p02:='苏大强';
  return 1;
end;

---- in  out  可以传入   也可以带出    （重新可以被赋值）  调用时 必须使用变量
create  or replace  function  test_fun03(p01  in number ,p02  out  varchar2,p03   in  out   number)
return number
is         
begin
 -- p01:=110;
 -- p03:=110;
  dbms_output.put_line('p01  in number'||p01);
  dbms_output.put_line('p02  in number'||p02);
  dbms_output.put_line('p03  in number'||p03);
  p02:='苏大强';
  p03:=11;
  return 1;
end;

declare
v_return number;
v_p02  varchar2(10) :='李强';
v_p03 number :=12;
begin
  v_return :=test_fun03(11,v_p02,v_p03);
  --- 如果参数 是 out  类型  通过  变量传递参数 （不能给定常量）
  
  dbms_output.put_line('v_p02  in number'||v_p02);
  dbms_output.put_line('v_p03  in number'||v_p03);
end;

------- 参数的默认值   
create  or replace  function  test_default( p01 number ,p02  number default 20 ,p03 number     )
 --p02  参数 加了 默认值 之后 可以不用传参  
 --- 一般 放到 最后一个参数   oracle  在中间 也可以 必须使用 定位传参
return  number
is
begin
     dbms_output.put_line('p01  in number'||p01);
     dbms_output.put_line('p02  in number'||p02);
     return 1;
end;
select  test_default(1 ,p03=>3)  from dual;
   
---- 过程        --- （没有返回值）
create  or replace  procedure   my_pro   --- 无参过程

is
begin
    dbms_output.put_line('第一个过程');
    
end;

---调用  过程  
begin
  my_pro;
end;

---- 传入部门编号   部门名称   部门所在地  添加 部门     告诉我 添加成功 ？
create  or replace  procedure  add_dept(pdept  dept%rowtype,pfalg  out boolean)   
is
begin
  insert into dept values(pdept.deptno,pdept.dname,pdept.loc);
  pfalg:=true;
exception
   when dup_val_on_index then
      dbms_output.put_line('违反唯一约束');
       pfalg:=false;
end;         

declare
 v_dept dept%rowtype;
 v_flag boolean;
begin
  v_dept.deptno:=12;
  v_dept.dname:='许愿墙';
  v_dept.loc:='中国西安';
  add_dept(v_dept,v_flag);
  
  if  v_flag then
     dbms_output.put_line('部门添加成功');
  else
      dbms_output.put_line('部门添加失败。。。。。。。。。。');
  end if;
  
end;

---------业务   一个业务  需要操作多次数据库 (分页 总页数  查询当前的数据 )
--   删除  部门信息     删除 部门下有 员工   得先处理员工  (  set  null )    
create or replace procedure delete_dept_by_id (pdeptno number)
is
father_find_child exception ;

pragma  exception_init(father_find_child,-2292);

begin
  delete from dept where deptno= pdeptno;
exception
  when  father_find_child  then
    ---- 表示 不能删除  关联的员工  设置  set  null
    update emp  set deptno=null where deptno=pdeptno;
    --delete from dept where deptno= pdeptno;
    delete_dept_by_id(pdeptno);
end;

begin
  delete_dept_by_id(40);
end;
select  *from emp;
select *from dept;