有关正确及错误的"PL/SQL"对照使用示例
来源:岁月联盟
时间: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; |