有关正确及错误的"PL/SQL"对照使用示例

来源:岁月联盟 编辑:zhuzhu 时间:2008-01-28

正确及错误的"PL/SQL"对照使用示例:

/**关于正确及错误PL/SQL对照使用的示例*/create table test(tid int  primary key not null,tname varchar2(50));create sequence test_seq start with 1 increment by 1;insert into test values(test_seq.nextval,'FLB');insert into test values(test_seq.nextval,'FLB1');-----------------------------------错误函数1create or replace function freturn intbegin       return 5*5;end;--错误函数2create or replace function f()return numberisbegin       return 5*5;end;--错误函数3create or replace function freturn numberbegin       return 5*5;end;--该函数的正确函数1create or replace function freturn numberisbegin       return 5*5;end;--该函数的正确函数2create or replace function freturn intisbegin       return 5*5;end;--------------------------------------正确函数create or replace function f5(pid number)return varchar2is     rname varchar2(50);     cursor cname     is     select tname from test where tid=pid;begin     open cname;     fetch cname into rname;          return rname;end;--调用函数用call出错call f5(2);--调用出错,函数必须有值的接收者begin     f5(2);end;--这种方法调用不正确declarer varchar2;begin     r:=f5(2);     dbms_output.put_line(r);end;--这种方法调用正确begin     dbms_output.put_line(f5(2));end;--这种方法调用正确declarer varchar2(50);begin     r:=f5(2);     dbms_output.put_line(r);end;--正确函数create or replace function f4(ttid in integer)return varchar2is       rname varchar(50);       cursor cname       is        select tname from test where tid=ttid;begin       open cname;       fetch cname into rname;       return rname;end;--正确函数create or replace function f7(n in number)return numberisbegin       return n*n;end f7;--------------------------------------------过程create or replace procedure p1isbegin      dbms_output.put_line('First Procuder');end p1;--调用过程用call不会出错call p1();--这种方法调用过程不正确begin     dbms_output.put_line(p1());end;----------注重函数与过程的调用的不同处------------------------------------------------------------错误过程create or replace procedure p3(ttid in Integer)is       rname varchar;       cursor cname       is        select tname from test where tid=ttid;begin       open cname;       fetch cname into rname;       dbms_output.put_line(rname);end;--正确过程create or replace procedure p3(ttid in Integer)is       rname varchar(50);       cursor cname       is        select tname from test where tid=ttid;begin       open cname;       fetch cname into rname;       dbms_output.put_line(rname);end;