数据库基础系列之四：PL/SQL入门

最近不时需要写一些PL/SQL语句，对这东西不熟悉，有时候搞得挺晕，摘一些小知识点，以备查用，错误的地方，各位多多批评指正：

 

//日期格式化：

to_date('2006-11-20 16:35:00','yyyy-mm-dd hh24:mi:ss')

 

//取子串，第二个参数表示从原串的何处起取，第三个参数指子串长度：

substr('abcdef',1,4)

substr('ab',1,4)= 'ab'

 

//日期截取，即截取时间，保留时间部分：

trunc(sysdate)

 

//日期的加减：

trunc(sysdate)-1  前一天

trunc(sysdate)-1/(24*60*60)  前一秒

to_number(trunc(sysdate)-begintime)*1440  计算时间差，单位：分钟

 

//四舍五入：

round(2.555)=2

round(2.555,2)=2.56

 

//case语句：

case when sysdate>MyTable.cleartime

then ……

else

……

end

 

case MyTable.objid when 'hmy'

then ……

else

……

end

 

//decode函数，类似于三元运算符：

decode(MyTable.price,null,0, MyTable.price)  如果price为null，返回0

具体的语法格式如下：

DECODE(input_value,value,result[,value,result…][,default_result]);

 

//常见的比率计算示例：

Select to_char(round(sum(decode(isactive,0,1,0))/count(isactive)*100,2))||'%' from ……

 

//在字符串类型数据判等时，如果是char 或nchar，注意多余的空格

 

//判断交集：

如何判断两个时间段有交集，starttime_1= = =endtime_1， starttime_2= = =endtime_2

where least(endtime_1, endtime_2) >greatest(starttime_1, starttime_2)

 

//打印输出

dbms_output.put_line('……');

 

//存储过程：

(1)从最简单的存储过程例子开始：

create or replace procedure MyProc

as

begin

  delete from MyTable;

end;

// oracle中可以使用replace，方便多了。as亦可用is。

 

(2)带上参数：

create or replace procedure MyProc(p_date in date)

as

begin

  delete from MyTable where starttime= p_date;

end;

//in可省去，如有输出参数，应用out.

 

(3)声明变量：

create or replace procedure MyProc

as

v_count number:=0;

begin

select count(*) into v_count from MyTable;

if v_count>0 then

……;

end if;

end;

//变量声明不用declare。变量可以指定默认值。

(“PL/SQL程序”又称“PL/SQL块”，“PL/SQL块”分为过程，函数，触发器等，“PL/SQL块”又分为“带名块”和“匿名块”。“带名块”就是有名字的程序，如上面存储过程“MyProc”，“匿名块”就是没有名字的程序，“带名块”里面以is/as开始定义变量，“匿名块”里面以declare开始来定义变量)

 

(4)声明游标：

create or replace procedure MyProc

as

cursor cursor_1 is

select objid,starttime from MyTable;

c1_row cursor_1%ROWTYPE;

c1_objid MyTable.objid%TYPE;

c1_starttime MyTable.starttime%TYPE;

 

begin

  open cursor_1;

fetch cursor_1 into c1_objid,c1_starttime;

loop

fetch cursor_1 into c1_row;

exit when cursor_ 1%notfound;

  if c1_row.starttime< c1_starttime then

    ……

  end if;

end loop;

close cursor_1

end;

//使用%TYPE可以省去查看列的数据类型的麻烦

//可以使用%ROWTYPE来在游标中表示一行记录。

//游标中常用的属性：%notfound、%found

 

(5)动态游标：

create or replace procedure MyProc

as

v_id varchar2(20);

 

type v_cursor is ref cursor;

cursor_1 v_cursor;

c1_objid MyTable.objid%TYPE;

c1_starttime MyTable.starttime%TYPE;

 

begin

  v_id:=……;

  open cursor_1 for 'select objid,starttime from MyTable where objid=''' || v_id || '''';

……

close cursor_1;

end;

//游标的语句可以动态指定

 

//调试存储过程

不同数据库中对存储过程的调试都是个令人头痛的问题，oracle中如欲调试存储过程，最好有辅助软件，如pl/sql developer。在pl/sql developer可按以下步骤对存储过程进行调试：

一、定位你的procedure

1在屏幕左边的对象浏览器中展开procedure

2找到你的procedure

二、打开测试窗口

1在你的procedure上点击右键

2在弹出的菜单中选择test

3PL/SQL Devoloper就会打开一个测试窗口并自动生成一个调用块

4在测试窗口的下方输入你的procedure的入口参数

三、打开编辑窗口

1在你的procedure上点击右键

2在弹出的菜单中选择edit

3PL/SQL Devoloper就会打开编辑窗口

4在需要的地方设置断点

四、开始调试

1回到调试窗口

2打开debug菜单，你就会看见start项已经可以用了，点击它

3接下来就可以用调试窗口上的按钮进行单步跟踪

 

如果在调试过程中提示“no debug information”，则右键相应的存储过程，选中“add debug information”。

 

//设置定时任务

variable n number;

begin

dbms_job.submit(:n,'MyProc;',sysdate,'sysdate+1/1440');

commit;

end;

//定时执行MyProc存储过程，第隔一分钟执行一次。变量n用于返回该任务在系统中的编号(唯一标识)，你可以用语句将它打印出来。

//如果存储过程有参数，则第二个参数改作类似：'MyProc(10);'即可。

(使用Submit()过程，工作被正常地计划好。
这个过程有五个参数：job、what、next_date、interval与no_parse。

PROCEDURE Submit ( job OUT binary_ineger,
What IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN booean:=FALSE)

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。
what参数是将被执行的PL/SQL代码块。
next_date参数指识何时将运行这个工作。
interval参数何时这个工作将被重执行。
no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE
指示此PL/SQL代码在它第一次执行时应进行语法分析，
而FALSE指示本PL/SQL代码应立即进行语法分析)

 

begin

dbms_job.remove(21);

end;

//移除指定编号的任务

 

begin

dbms_job.run(21);

end;

//手动执行一次指定编号的任务

 

select job,next_date,next_sec,failures,broken from user_jobs;

//获取系统中所有的任务信息

 

//如果在使用submit添加任务之后，任务并未自动执行，请检查并将：企业管理器=》数据库==》例程==》配置==》所有初始化参数==》已配置==》job_queue_processes设置为非0，或直接修改文件\oracle\ora90\database\SPFILExxx.ORA(其中xxx是oracle的sid)中的job_queue_processes的值，随后重启数据库。

该参数定义SNP进程的启动个数为n。系统缺省值为0，正常定义范围为0～36，根据任务的多少，可以配置不同的数值。