新手学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，根据任务的多少，可以配置不同的数值。

1.在存储过程中使用游标返回结果集：

(1)包：

create or replace package PKG_ReturnSet

as

type MyCurType is ref cursor;

end;

 

(2)存储过程：

create or replace procedure SP_ReturnSet(p_Result out PKG_ReturnSet.MyCurType)

as

begin

open p_Result for 'select * from alarminfo';

end; 

2.游标参数：

declare

cursor Cur_1(p_StationID varchar2) is

select * from MyTable where ID= p_StationID;

begin

for v_Row in Cur_1('dmz1') loop

       DBMS_OUTPUT.put_line(v_Row.name);

end loop;

end;

((1)在游标for循环中，游标的打开、获取、关闭都是隐含地进行，不需要你书写显式的代码来执行。(2)变量v_Row无须显式地声明。)

3.另一些PL/SQL简单概念：

(1)PL/SQL代码组织而成“块”，如果你创建存储过程或包，那么它是一个“命名块”，否则，称为“匿名块”。

PL/SQL块包含三个部分：(1)声明：定义及初始化变量及游标，(2)可执行命令：使用流程控制关键字来执行命令并将值赋于已声明的变量，(3)异常处理。如下：

declare

<declarations section>

begin

<executable commands>

exception

<exception handling>

end;

(2)流程控制(If)：

If……then

……;

Elsif……then

……;

Else

……;

End If;

(3)流程控制(循环)：

简单循环：循环直到遇到Exit或Exit When

For循环：循环执行指定次数

While循环：在符合条件下循环

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

Loop

       ……;

Exit When ……;

End Loop;

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

For radius in 1..7 Loop

area:=pi*power(radius,2);

insert into areas values(radius,area)

End Loop;

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

While radius<=7 Loop

……;

End Loop;

 (4)流程控制(Case)：

Case When …… Then

……;

When …… Then

       ……;

Else ……;

End Case;