【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;--系统表
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;--系统表