oracle中的function,procedure,package.

create sequence seq_loginfo;
create table loginfo
(
id number primary key,
logName varchar(20) not null,
logPass varchar(20) not null
);
--drop TABLE loginfo;
insert into loginfo(id, logname, logpass) values (seq_loginfo.nextval,'admin','123');
select * from loginfo;
create or replace function validLogin(loginName_ varchar2,
loginPwd_ varchar2) return number is
cursor login_ is
select * from loginfo t where t.logname = loginName_;
r login_%Rowtype;
begin
open login_;
fetch login_
into r;
close login_;
if (r.logPass is not null) then
if (r.logPass = loginPwd_) then
return 1;
else
return 0;
end if;
else
return 0;
end if;
end;
declare
returnNumber_ number;
begin
returnNumber_ := validLogin('admin', '123');
dbms_output.put_line('returnNumber_ :=' || returnNumber_);
end;
create or replace procedure login_proc(
loginname_ in loginfo.logname%type,
loginpwd_ in loginfo.logpass%type,
returnMsg_ in out varchar2) is
cursor login_ is select * from loginfo t where t.logname=loginname_;
r login_%Rowtype;
begin
open login_;
fetch login_ into r;
close login_;
if(r.logPass is not null) then
if(r.logPass = loginpwd_ )then
returnMsg_:='登录成功';
else
returnMsg_:='密码错误';
end if;
else
returnMsg_:='用户名有误';
end if;
end;
declare
returnMsg_ varchar2(200);
begin
login_proc('admin', '123', returnMsg_);
dbms_output.put_line('returnMsg_ :=' || returnMsg_);
end;

create or replace package Login__ is
function validLogin(loginName_ varchar2,
loginPwd_ varchar2) return number;
procedure login_proc(loginname_ in
loginfo.logname%type,
loginpwd_ in loginfo.logpass%type,
returnMsg_ in out varchar2);
end;
create or replace package body Login__ is
function validLogin(loginName_ varchar2, loginPwd_ varchar2) return number is cursor login_ is
select * from loginfo t where t.logname = loginName_;


r login_%Rowtype;
begin
open login_;
fetch login_
into r;
close login_;
if (r.logPass is not null) then
if (r.logPass = loginPwd_) then
return 1;
else
return 0;
end if;
else
return 0;
end if;
end;
procedure login_proc(loginname_ in loginfo.logname%type,
loginpwd_ in loginfo.logpass%type,
returnMsg_ in out varchar2) is
cursor login_ is
select * from loginfo t where t.logname = loginname_;
r login_%Rowtype;
begin
open login_;
fetch login_
into r;
close login_;
if (r.logPass is not null) then
if (r.logPass = loginpwd_) then
returnMsg_ := '登录成功';
else
returnMsg_ := '密码错误';
end if;
else
returnMsg_ := '用户名有误';
end if;
end;
end;
declare
returnNumber_ number;
begin
returnNumber_ := login__.validLogin('admin', '123');
dbms_output.put_line('returnNumber_ :=' || returnNumber_);
end;
declare
returnMsg_ varchar2(200);
begin
login__.login_proc('admin', '123', returnMsg_);
dbms_output.put_line('returnMsg_ :=' || returnMsg_);
end;
declare
isnumber number;
begin
isnumber := login__.ISNUMBER('12213');
dbms_output.put_line('isnumber :=' || isnumber);
end;