Oracle数据库编程

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle数据库编程，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1.functions

--语法结构
create or replace function function_name(
arg1 [] datatype1,
arg2 [] datatype2
)return datattype
is
begin
end;
--调用语法
var v1 varchar2(100)
exec :v1:=function_name

--一：不带参数的定义
create or replace function get_time
return varchar2
is
time_result varchar2(50);
begin
select sysdate into time_result from dual;
return time_result;
end get_time;
--命令窗口中执行

sql> var tinfo varchar2(50);
sql> exec :tinfo:=get_time;
--执行结果
PL/sql procedure successfully completed
tinfo
---------
08-7月 -16

--二：有入参
create or replace function get_time_in(
in_info in varchar2
)return varchar2
is
time_result varchar2(50);
begin
select sysdate into time_result from dual;
return time_result||in_info;
end get_time_in;

--命令窗口中执行

sql> var v1 varchar2(50);
sql> exec :v1:=get_time_in('aaaaaaaaaaaaaaaaaaaaa');
--执行结果
PL/sql procedure successfully completed
v1
---------
08-7月 -16aaaaaaaaaaaaaaaaaaaaa

--三：带out参数的
create or replace function get_time_in2(
in_info in varchar2,
job out varchar2
)return varchar2
is
time_result varchar2(50);
begin
select sysdate into time_result from dual;
return time_result||in_info||job;
end get_time_in2;
--命令窗口中执行
sql> var job varchar2(20)
sql> var e_name varchar2(20)
sql> exec :e_name:=get_time_in2('in args',:job);

--执行结果

PL/sql procedure successfully completed
e_name
---------
09-7月 -16in args
job
---------

参考：http://blog.csdn.net/jumtre/article/details/38092067

2.procudures

3.package

4.triggers

参见：http://www.oschina.net/code/snippet_1052786_57772

5.view

6.schedules

------------------------------------------------------------------------------------------------------------

一个国家电网物资项目的一个脚本参考：

drop type htjs_business_row ;
drop type htjs_business_state_row_type;
drop PACKAGE business_state_pkg;

--定义自定类型
CREATE OR REPLACE type htjs_business_state_row_type as object(bissid VARCHAR2(20),businessState varchar2(50));
CREATE OR REPLACE type htjs_business_row as table of htjs_business_state_row_type;

--声明包
create or replace package business_state_pkg is
--预算状态函数
function fun_htjs_business_state return htjs_business_row;
--续保状态函数
function fun_htjs_bs_state_continue return htjs_business_row;
--供应商状态函数
function fun_htjs_bs_state_supp return htjs_business_row;
end business_state_pkg;

--包体
create or replace package body business_state_pkg is
--预算状态函数start
function fun_htjs_business_state
return htjs_business_row
as
rs htjs_business_row:= htjs_business_row();

businessState VARCHAR2(50);

bissid bosp_htys_businessreserve.id%type;
isquit bosp_htys_businessreserve.isquit%type;
BUS_STATE bosp_htys_businessreserve.BUS_STATE%type;
formalcensor bosp_htys_receptioninfo.formalcensor%type;
accept_state bosp_htys_receptioninfo.accept_state%type;
substanceresult bosp_htys_backstageinfo.substanceresult%type;
iscomplete bosp_htys_backstageinfo.iscomplete%type;
gather_state bosp_htys_backstageinfo.gather_state%type;
app_state bosp_htjs_contract_report.app_state%type;
back_app_state bosp_htys_backstageinfo.app_state%type;
mortem_state bosp_htys_ysinfo.mortem_state%type;
returnman bosp_htys_businessreserve.returnman%type;
issubmit bosp_htys_backstageinfo.issubmit%type;
begin
for myrow in(
--状态sql开始
SELECT
biss. ID,
biss.isquit,
biss.BUS_STATE,
rec.formalcensor,
rec.accept_state,
back.substanceresult,
back.iscomplete,
back.gather_state,
back.app_state back_app_state,
bhcreport.app_state,
ys.mortem_state,
biss.returnman,
back.issubmit
FROM
bosp_htys_businessreserve biss
LEFT JOIN bosp_htys_receptioninfo rec ON biss. ID = rec.business_id
LEFT JOIN bosp_htjs_contractinfo con ON con. ID = biss.contractinfo_id
LEFT JOIN bosp_htjs_subengineeringinfo sub ON sub. ID = con.subengineeringinfo_id
LEFT JOIN bosp_htjs_engineeringinfo eng ON eng. ID = sub.engineering_id
LEFT JOIN bosp_global_supplier sup ON con.supplier_id = sup. ID
LEFT JOIN bosp_htys_backstageinfo back ON back.qt_business_id = rec. ID
LEFT JOIN bosp_htys_budget budget ON budget.business_id = biss. ID
LEFT JOIN bosp_htys_monthlygatherinfo bhmgather ON bhmgather.business_id = biss. ID
LEFT JOIN bosp_htjs_contract_report bhcreport ON bhmgather.report_id = bhcreport. ID
LEFT JOIN bosp_htys_ysinfo ys ON ys.business_id = biss. ID
where biss.business_type IN (1,2,3,4,5,6)
AND (
(bhcreport.id is not null and bhcreport.report_type = 2 AND bhcreport.is_del = 0)
or bhcreport.id is null
)
--状态sql结束
)
loop
--业务逻辑判断开始
bissid := myrow.ID;
isquit := myrow.isquit;
BUS_STATE := myrow.BUS_STATE;
formalcensor := myrow.formalcensor;
accept_state := myrow.accept_state;
substanceresult := myrow.substanceresult;
iscomplete := myrow.iscomplete;
gather_state := myrow.gather_state;
app_state := myrow.app_state;
back_app_state := myrow.back_app_state;
mortem_state := myrow.mortem_state;
returnman := myrow.returnman;
issubmit := myrow.issubmit;
dbms_output.put_line(BUS_STATE);


if BUS_STATE is not null THEN
if BUS_STATE=0 then
businessState := 'dsl';
elsif (BUS_STATE=1 or BUS_STATE=3) then
if formalcensor is null then
businessState := 'dxssc';
elsif formalcensor=0 then
businessState := 'xsscbtg';
elsif formalcensor=1 then
businessState := 'xssctg';
if accept_state=1 then
businessState := 'dszsc';
if mortem_state=4 then
businessState := 'ysth';
end if;
--实审开始
if substanceresult is not null then
--if issubmit is not null then
--if issubmit=1 then
if substanceresult=0 then
businessState := 'szscbtg';
elsif substanceresult=1 then
businessState := 'szsctg';
if isquit=1 then
if back_app_state is not null then
businessState := 'isquit_money_app_state' || back_app_state;
end if;
else
businessState := 'dbzys';
if iscomplete=1 then
businessState := 'ybzys';
if gather_state=1 then
if app_state is not null then
businessState := 'declare_state' || app_state;
end if;
end if;
end if;
end if;
elsif substanceresult=2 then
businessState := 'qxzz';
end if;
--end if;
--end if;
end if;
--实审结束
end if;
end if;
elsif BUS_STATE=2 then
businessState := 'thgys';
if formalcensor=0 then
businessState := 'xsscbtg';
end if;
end if;
end if;

--增加记录
rs.extend;
DBMS_OUTPUT.put_line (rs.count);
rs(rs.count) := htjs_business_state_row_type(bissid,businessState);
end loop;
return rs;
end fun_htjs_business_state;
--预算状态函数end

--续保状态函数start
function fun_htjs_bs_state_continue
return htjs_business_row
as
rs htjs_business_row:= htjs_business_row();

businessState VARCHAR2(50);

bissid bosp_htys_businessreserve.id%type;
BUS_STATE bosp_htys_businessreserve.BUS_STATE%type;
formalcensor bosp_htys_receptioninfo.formalcensor%type;
accept_state bosp_htys_receptioninfo.accept_state%type;
substanceresult bosp_htys_backstageinfo.substanceresult%type;
returnman bosp_htys_businessreserve.returnman%type;
isinvalid bosp_htys_backstageinfo.isinvalid%type;
q_receptionstate bosp_htys_receptioninfo.q_receptionstate%type;
issubmit bosp_htys_backstageinfo.issubmit%type;
begin
for myrow in(
--状态sql开始
SELECT biss. ID,
back.isinvalid,
rec.q_receptionstate,
back.issubmit
FROM bosp_htys_businessreserve biss
LEFT JOIN bosp_htys_receptioninfo rec
ON biss. ID = rec.business_id
LEFT JOIN bosp_htjs_contractinfo con
ON con. ID = biss.contractinfo_id
LEFT JOIN bosp_htjs_subengineeringinfo sub
ON sub. ID = con.subengineeringinfo_id
LEFT JOIN bosp_htjs_engineeringinfo eng
ON eng. ID = sub.engineering_id
LEFT JOIN bosp_global_supplier sup
ON con.supplier_id = sup. ID
LEFT JOIN bosp_htys_backstageinfo back
ON back.qt_business_id = rec. ID
WHERE biss.business_type = 8
--状态sql结束
)
loop
--业务逻辑判断开始
bissid := myrow.ID;
BUS_STATE := myrow.BUS_STATE;
formalcensor := myrow.formalcensor;
accept_state := myrow.accept_state;
substanceresult := myrow.substanceresult;
returnman := myrow.returnman;
isinvalid := myrow.isinvalid;
q_receptionstate := myrow.q_receptionstate;
issubmit := myrow.issubmit;

dbms_output.put_line(BUS_STATE);


if BUS_STATE is not null THEN
if BUS_STATE=0 then
businessState := 'dsl';
elsif (BUS_STATE=1 or BUS_STATE=3) then
if formalcensor is null then
businessState := 'dxssc';
elsif formalcensor=0 then
businessState := 'xsscbtg';
elsif formalcensor=1 then
businessState := 'xssctg';
if accept_state=1 then
businessState := 'dszsc';
if isinvalid=0 then
businessState := 'zf';
end if;
--实审开始
if substanceresult is not null then
--if issubmit is not null then
--if issubmit=1 then
if substanceresult=0 then
businessState := 'szscbtg';
elsif substanceresult=1 then
businessState := 'szsctg';
elsif substanceresult=2 then
businessState := 'qxzz';
end if;
--end if;
--end if;
end if;
--实审结束
end if;
end if;
elsif BUS_STATE=2 then
businessState := 'thgys';
if formalcensor=0 then
businessState := 'xsscbtg';
end if;
end if;
end if;
--增加记录
rs.extend;
DBMS_OUTPUT.put_line (rs.count);
rs(rs.count) := htjs_business_state_row_type(bissid,businessState);
end loop;
return rs;
end fun_htjs_bs_state_continue;
--续保状态函数end

--供应商状态函数start
function fun_htjs_bs_state_supp
return htjs_business_row
as
rs htjs_business_row:= htjs_business_row();

businessState VARCHAR2(50);

bissid bosp_htys_businessreserve.id%type;
BUS_STATE bosp_htys_businessreserve.BUS_STATE%type;
formalcensor bosp_htys_receptioninfo.formalcensor%type;
accept_state bosp_htys_receptioninfo.accept_state%type;
substanceresult bosp_htys_backstageinfo.substanceresult%type;
returnman bosp_htys_businessreserve.returnman%type;
isinvalid bosp_htys_backstageinfo.isinvalid%type;
q_receptionstate bosp_htys_receptioninfo.q_receptionstate%type;
app_state bosp_htys_ht_supplierinfobg.app_state%type;
issubmit bosp_htys_backstageinfo.issubmit%type;
begin
for myrow in(
--状态sql开始
SELECT
biss. ID,
biss.BUS_STATE,
rec.formalcensor,
rec.accept_state,
back.substanceresult,
biss.returnman,
back.isinvalid,
rec.q_receptionstate,
suppbg.app_state,
back.issubmit
FROM
bosp_htys_businessreserve biss
LEFT JOIN bosp_htys_receptioninfo rec ON biss. ID = rec.business_id
LEFT JOIN bosp_htjs_contractinfo con ON con. ID = biss.contractinfo_id
LEFT JOIN bosp_htjs_subengineeringinfo sub ON sub. ID = con.subengineeringinfo_id
LEFT JOIN bosp_htjs_engineeringinfo eng ON eng. ID = sub.engineering_id
LEFT JOIN bosp_global_supplier sup ON con.supplier_id = sup. ID
LEFT JOIN bosp_htys_backstageinfo back ON back.qt_business_id = rec. ID
LEFT JOIN bosp_htys_ht_supplierinfobg suppbg ON biss. ID = suppbg.business_id
WHERE BISS.BUSINESS_TYPE=7
--状态sql结束
)
loop
--业务逻辑判断开始
bissid := myrow.ID;
BUS_STATE := myrow.BUS_STATE;
formalcensor := myrow.formalcensor;
accept_state := myrow.accept_state;
substanceresult := myrow.substanceresult;
returnman := myrow.returnman;
isinvalid := myrow.isinvalid;
q_receptionstate := myrow.q_receptionstate;
app_state := myrow.app_state;
issubmit := myrow.issubmit;
dbms_output.put_line(BUS_STATE);


if BUS_STATE is not null THEN
if BUS_STATE=0 then
businessState := 'dsl';
elsif (BUS_STATE=1 or BUS_STATE=3) then
if formalcensor is null then
businessState := 'dxssc';
elsif formalcensor=0 then
businessState := 'xsscbtg';
elsif formalcensor=1 then
businessState := 'xssctg';
if accept_state=1 then
businessState := 'dszsc';
if isinvalid=0 then
businessState := 'zf';
end if;
--实审开始
if substanceresult is not null then
--if issubmit is not null then
--if issubmit=1 then
if substanceresult=0 then
businessState := 'szscbtg';
elsif substanceresult=1 then
businessState := 'szsctg';
if app_state is not null then
businessState := 'suppbg_app_state' || app_state;
if app_state=2 then
if isinvalid=0 then
businessState := 'zf';
end if;
end if;
end if;
elsif substanceresult=2 then
businessState := 'qxzz';
end if;
--end if;
--end if;
end if;
--实审结束
end if;
end if;
elsif BUS_STATE=2 then
businessState := 'thgys';
if formalcensor=0 then
businessState := 'xsscbtg';
end if;
end if;
end if;
--增加记录
rs.extend;
DBMS_OUTPUT.put_line (rs.count);
rs(rs.count) := htjs_business_state_row_type(bissid,businessState);
end loop;
return rs;
end fun_htjs_bs_state_supp;
--供应商状态函数end

end business_state_pkg;

--创建视图
create materialized view mv_htjs_business_state refresh force on demand as select htjs_business_state.* from table(business_state_pkg.fun_htjs_business_state) htjs_business_state;
create materialized view mv_htjs_bs_state_continue refresh force on demand as select htjs_bs_state_continue.* from table(business_state_pkg.fun_htjs_bs_state_continue) htjs_bs_state_continue;
create materialized view mv_htjs_bs_state_supp refresh force on demand as select htjs_bs_state_supp.* from table(business_state_pkg.fun_htjs_bs_state_supp) htjs_bs_state_supp;
--删除视图
drop materialized view mv_htjs_business_state;
drop materialized view mv_htjs_bs_state_continue;
drop materialized view mv_htjs_bs_state_supp;
--查询
select htjs_business_state.* from table(business_state_pkg.fun_htjs_bs_state_supp) htjs_business_state;
select * from mv_htjs_business_state;
select htjs_bs_state_continue.* from table(business_state_pkg.fun_htjs_bs_state_continue) htjs_bs_state_continue;
select * from mv_htjs_bs_state_continue;
select htjs_bs_state_supp.* from table(business_state_pkg.fun_htjs_bs_state_supp) htjs_bs_state_supp;
select * from mv_htjs_bs_state_supp;
--刷新视图（命令）
exec dbms_mview.refresh('mv_htjs_business_state');
exec dbms_mview.refresh('mv_htjs_bs_state_continue');
exec dbms_mview.refresh('mv_htjs_bs_state_supp');

--java调用：call dbms_mview.refresh('mv_htjs_business_state')";

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问