存储过程--数据库优化
什么是存储过程
简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;
ps:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用;
存储过程有哪些特性
有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;
函数的普遍特性:模块化,封装,代码复用;
速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;
创建一个存储过程
create procedure user_porced ()
begin
select name from users;
end;
调用存储过程
call porcedureName ();
传参存储过程
create PROCEDURE user_porcedPa(
in a int(10)
)
BEGIN
select * from users where age>a;
END;
call user_porcedPa(10);
–call方式调用存储过程
call pro_process.p_time_test();
–创建并执行定时计划
Declare
i Integer;
Begin
dbms_job.submit(i,'pro_process.p_time_test;',Sysdate,'sysdate+30/(24*60*60)');
end;
–创建调用带参数的存储过程的定时任务
declare
i integer;
begin
dbms_job.submit(i,'declare arg varchar2(128); begin my_demo.demo_loop(arg); end;',sysdate,'sysdate+30/(24*60*60)');
commit;
end;
–查看任务队列情况(包括任务号码)
select job as jobno,next_date,next_sec,failures,broken from user_jobs where what='pro_process.p_time_test;' ;
--broken:Y:没启动 N:启动
–job如果由于某种原因未能成功执行,oracle将重试16次后,还未能成功执行,将被标记为broken,重新启动状态为broken的job
–通过任务号码重启定时任务
Declare
jobno Integer := 23;
Begin
dbms_job.run(jobno); -- 运行制定的执行计划
end;
–停止执行定时任务
–方式一
Declare
jobno Integer := 23;
Begin
dbms_job.broken(jobno,True); -- 停止计划,不再继续执行
dbms_job.broken(jobno,True,Sysdate+(2/24/60)); -- 停止计划,并在两分钟后继续执行
end;
–方式二
begin
dbms_job.broken(23, true, sysdate);
commit;
end;
–修改执行计划方式一
Declare
jobno Integer;
Begin
dbms_job.interval(jobno, 'sysdate+1/(24*60)'); -- 修改为:每分钟执行一次
end;
–修改执行计划方式二
declare
jobno Integer;
begin
dbms_job.next_date(jobno, next_date); --指定任务号的时间
end
–删除执行计划
Declare
jobno Integer:=23;
Begin
dbms_job.remove(jobno);
commit;
end;
act_defid number;--活动定义Id
act_defname varchar2(128);--活动实例名称
act_desc varchar2(512);--活动实例描述
act_type varchar2(64);--活动类型
act_currentstate number;--活动当前环节
act_state number;--活动当前状态
act_nextdefid number;--下个活动定义Id
act_nextdefname varchar2(128);--下个活动定义名称
act_nextstate number;--下个活动环节
act_grade number;--参与当前活动(环节)执行机构等级
act_gradetype number;--机构等级与机构的类型(0为机构等级,1为机构,2为流程执行者)
act_lastgrade number;--当前环节执行机构上级机构等级(属于等级的上级,如部门,营业厅,班组的上级就是网、省、市等)
act_role clob;--角色标识
act_rolename clob;--角色名称
act_person clob;--参与人员
act_org clob;--组织机构
存储过程优缺点
优点
1.在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。但这一点便利被许多人滥用了。有人直接就在正式服务器上修改存储过程,而没有经过完整的测试,后果非常严重。
2.执行速度快。存储过程经过编译之后会比单独一条一条执行要快。但这个效率真是没太大影响。如果是要做大数据量的导入、同步,我们可以用其它手段。
3.减少网络传输。存储过程直接就在数据库服务器上跑,所有的数据访问都在服务器内部进行,不需要传输数据到其它终端。但我们的应付服务器通常与数据库是在同一内网,大数据的访问的瓶颈会是硬盘的速度,而不是网速。
4.能够解决presentation与数据之间的差异,说得文艺青年点就是解决OO模型与二维数据持久化之间的阻抗。领域模型和数据模型的设计可能不是同一个人(一个是SA,另一个是DBA),两者的分歧可能会很大——这不奇怪,一个是以OO的思想来设计,一个是结构化的数据来设计,大家互不妥协——你说为了软件的弹性必须这么设计,他说为了效率必须那样设计,为了抹平鸿沟,就用存储过程来做数据存储的逻辑映射(把属性映射到字段)。好吧,台下已经有同学在叨咕ORM了。
5.方便DBA优化。所有的SQL集中在一个地方,DBA会很高兴。这一点算是ORM的软肋。不过按照CQRS框架的思想,查询是用存储过程还是ORM,还真不是问题——DBA对数据库的优化,ORM一样会受益。况且放在ORM中还能用二级缓存,有些时候效率还会更高。
缺点
1.SQL本身是一种结构化查询语言,加上了一些控制(赋值、循环和异常处理等),但不是OO的,本质上还是过程化的,面对复杂的业务逻辑,过程化的处理会很吃力。这一点算致命伤。
2.不便于调试。基本上没有较好的调试器,很多时候是用print来调试,但用这种方法调试长达数百行的存储过程简直是噩梦。好吧,这一点不算啥,C#/java一样能写出噩梦般的代码。
3.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。
4.无法适应数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。
5.精通SQL的新手越来越少——不要笑,这是真的,我面试过N多新人,都不知道如何创建全局临时表、不知道having、不知道聚集索引和非聚集索引,更别提游标和提交叉表查询了。好吧,这个缺点算是凑数用的,作为屌丝程序员,我们的口号是:没有不会的,只有不用的。除了少数有语言洁癖的人,我相信精通SQL只是时间问题。
上一篇: 大话系统架构优化项目之数据库优化
下一篇: Mysql中视图的作用