Oracle创建和调用存储过程,函数,视图

【1】Oracle创建和调用存储过程
1:写一个简单的例子删除三级级联
1)create or replace procedure ww_procedure_servicecheck
(V_ID IN NUMBER) -- 定义输入变量
IS
begin
   DELETE FROM ww_mt_index_dt WHERE index_mainid IN (SELECT id FROM ww_mt_project_dt WHERE project_mainid=V_ID);--第三级
   DELETE FROM ww_mt_serviceRaters where Ratersid in (select id from ww_mt_servicecheck where id=V_ID);--第三级
   DELETE FROM ww_mt_project_dt WHERE project_mainid = V_ID; --第二级
   DELETE FROM ww_mt_servicecheck WHERE id = V_ID;第一级
   commit;--提交
end;
2)plsql执行存储过程
BEGIN
ww_procedure_servicecheck(id);
END;
3)sql语句调用存储过程
CALL ww_procedure_servicecheck(id);
2:有返回值的存储过程,就写一个简单的返回empno=7935的sal值
1)create or replace procedure emp_out_sal
 (
 v_empno in varchar2,
 v_sal out number
 ) is
 vsal number(7,2);
 begin
 select sal into vsal from emp where empno=v_empno;
 v_sal:=vsal;
 end;
2)plsql调用有返回值的过程
SQL> var vsal number
SQL> exec emp_out_sal('7935',:vsal);
3)sql语句调用存储过程
SQL> var vsal number
SQL> call emp_out_sal('7935',:vsal);
【2】函数创建
1实例
create or replace function getRoutineExaminationNew(suppliermdm in varchar2,khyd in varchar2)
  --参数有IN、OUT、IN OUT三种类型；IN代表需要输入的参数，OUT代表要返回的参数，IN OUT代表即是输入参数也是返回参数。
return number is--返回类型
  rckhdf number(15,2):=0;
begin
     select round(avg(decode(select_zlsp,0,100,decode(select_zlsp,1,80,decode(select_zlsp,2,60,40))))*0.3+
                  avg(decode(select_jhnl,0,100,decode(select_jhnl,1,80,decode(select_jhnl,2,60,40))))*0.2+
                  avg(decode(select_shfwcxjy,0,100,decode(select_shfwcxjy,1,80,decode(select_shfwcxjy,2,60,40))))*0.2+
                  avg(decode(select_hsepjys,0,100,1,80,2,40,0))*0.1+
                  avg(decode(select_hsepjxz,0,100,1,80,2,40,0))*0.1+
                  avg(decode(select_hsepjaz,0,100,1,80,2,40,0))*0.1
                  ,2 ) into rckhdf
        from formtable_main_126 m  where 1=1
        and substr(m.data_ysrq,1,4)= khyd  and m.txt_gysid= suppliermdm; --执行的SQL
    return rckhdf;
end getRoutineExaminationNew;
2实例
create or replace function avg_pric(v_total in number(10.2),
v_num In OUT number(10,2)) return  number(10,2)
as
v_pric number(10,2);
begin
  v_pric:=v_total/v_num;
  return v_pric;
  end;
1)sql调用此函数：
SQL> var vsla number
SQL> call getRoutineExaminationNew('7935',vsla) into :vsal;
2)plsql调用此函数
BEGIN  
dbms_output.put_line(getRoutineExaminationNew(2,4));  
END;
【3】视图创建
create or replace view view_ww_mt_gysrckh as
select distinct substr(data_ysrq,1,4) as year, y.id as id,txt_gysid,txt_gysmc,getRoutineExaminationNew(txt_gysid,substr(data_ysrq,1,4)) as rckhdf
 from  formtable_main_126 y left join workflow_requestbase r on r.requestid=y.requestid
where r.currentnodetype='3' order by year desc,rckhdf desc;
1)视图查询
select * from view_ww_mt_gysrckh
【4】查看函数
select * from  User_Procedures;--用户拥有的触发器、存储过程、函数查询表
select * from User_Source;--查询用户拥有触发器、存储过程、函数的源码。
select * from dba_Procedures;--系统表
select * from dba_Source;--系统表