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;
视图
| --视图 create view emp_view as select * from usertb; --删除视图中的字段，会影响到住表中的数据 delete from emp_view where userid=2; --对视图进行更新 update emp_view set username='李斯' where userid=2; |
触发器
| --触发器 --触发器对select不起作用 create or replace trigger delete_tir after delete on scott.emp begin dbms_output.put_line('删除一条语句'); end; alter trigger delete_tir disable; delete from scott.emp where empno=110; create or replace trigger update_tri before update on scott.emp begin dbms_output.put_line('更新一条语句'); end; update scott.emp set sal=9000 where empno=7499; --对scott.emp表进行插入数据的时候，也同时将此数据插入到 create table emptest( eno number(20) primary key, enames varchar2(30), jobs varchar2(30), mgrs number(10), hiretime date, sals number(10), comms number(10), dept_no number(10) ) --在删除之前执行，在删除之前打印即将删除学生的信息 --删除之前执行用":old"，更新之前执行用":new"; create or replace trigger stu_delete_prinStu before deleteon tb_stu for each row begin dbms_output.put_line('即将删除的学生学号是：'||:old.stu_no); dbms_output.put_line('即将删除的学生姓名是：'||:old.stu_name); end; delete from tb_stu where stu_no=6; create or replace trigger new_tri before insert on scott.emp for each row begin insert int |