lare i number(3):=1;
begin
dbms_output.put_line(i);
end;
end;
execute test_pro;--在sqlplus里执行
--在sql里执行存储过程
begin
test_pro;
end;
--带参数的存储过程
create or replace procedure get_par(i innumber)
as
begin
dbms_output.put_line(i);
end;
begin
get_par(2012);
end;
--根据学生id查询某学生名
create or replace procedure get_stu(stu_id innumber)
is
stuname varchar2(28);
begin
select s_name into stuname from student where s_id =stu_id;
dbms_output.put_line(stuname);
exception
when no_data_found then
dbms_output.put_line('查无此人');
end;
begin
get_stu(1);
end;
--带有输入输出参数的存储过程
--根据学生id来查询学生姓名
create or replace procedure get_stu(stu_id in number,stuname in out varchar2)
as
begin
select username into stuname from user_tb where userid =stu_id;
dbms_output.put_line(stuname);
exception
when no_data_found then
dbms_output.put_line('查无此人');
end;
declare
stuname varchar2(30);
begin
get_stu(1,stuname);
end;
*******************************************************
--模拟登录与注册
create table usertb(
userid number primary key,
username varchar2(30),
userpwd varchar2(30)
)
create or replace procedure login_pros(uname in out varchar2,pwd in out varchar2,islogin in out boolean)
as
begin
select username,userpwd into uname,pwd from usertb where username=uname and userpwd=pwd;
islogin:=true;
exception
when no_data_found then
dbms_output.put_line('用户没有注册');
islogin:=false;
end;
--执行登录的存储过程
declare
uname varchar2(30):='李冰冰';
pwd varchar2(30):='abc';
islogin boolean;
begin
login_pros(uname,pwd,islogin);
if(islogin) then
dbms_output.put_line('登录成功,'||'登录的用户是'||uname);
else
dbms_output.put_line('登录失败'||'请重新注册');
end if;
end;
--注册
create or replace procedure regist_pros(uname in out varchar2,pwd in out varchar2,userid in number,isregist out boolean)
as
begin
insert into usertb values(userid,uname,pwd);
isregist:=true;
dbms_output.put_line('注册成功'||'注册用户是:'||uname);
exception
when no_data_found then
dbms_output.put_line('您输入的用户信息是否正确');
isregist:=false;
end;
--注册的存储过程的调用
declare
uname varchar2(30):='李冰冰';
pwd varchar2(30):='abc';
userid number(10):=3;
isregist boolean;
begin
regist_pros(uname,pwd,userid,isregist);
if(isregist) then
dbms_output.put_line('注册的用户是：'||uname);
else
dbms_output.put_line('是否重新注册');
end if;
end;