--do while循环
declare
cursor c is select * from t_t_student order by id;
v_record c%rowtype;
begin
open c;
loop
fetch c into v_record;
exit when(c%notfound);
dbms_output.put_line('编号:'||v_record.id||', 姓名:'||v_record.name||', 年龄'||v_record.age||',电话:'
||v_record.phone||',地址:'||v_record.address);
end loop;
close c;
end;
--while循环查找数据始终不对,不建议使用
declare
cursor c is select * from t_t_student order by id;
v_record c%rowtype;
begin
open c;
fetch c into v_record;
while(c%found) loop
fetch c into v_record;
dbms_output.put_line('编号:'||v_record.id||', 姓名:'||v_record.name||', 年龄'||v_record.age||',电话:'
||v_record.phone||',地址:'||v_record.address);
end loop;
close c;
end;
--for循环,基本上普遍使用
declare
cursor c is select * from t_t_student order by id;
v_record c%rowtype;
begin
for v_record in c loop
dbms_output.put_line('编号:'||v_record.id||', 姓名:'||v_record.name||', 年龄'||v_record.age||',电话:'
||v_record.phone||',地址:'||v_record.address);
end loop;
end;
--带参游标
declare
cursor c(v_age t_t_student.age%type) is select * from t_t_student where age = v_age order by id;
v_record c%rowtype;
begin
for v_record in c(24) loop
dbms_output.put_line('编号:'||v_record.id||', 姓名:'||v_record.name||', 年龄'||v_record.age||',电话:'
||v_record.phone||',地址:'||v_record.address);
end loop;
end;
--可变游标,为了改变当前游标所指数据而使用
declare
cursor c is select * from t_t_student order by id for update;
v_record c%rowtype;
begin
for v_record in c loop
if(v_record.name = '李信') then
update t_t_student set age = 24 where current of c;
end if;
end loop;
commit;
end;
--存储过程
create or replace procedure p is
cursor c is select * from t_t_student order by id;
v_record c%rowtype;
begin
for v_record in c loop
if(v_record.age=24) then
dbms_output.put_line('编号:'||v_record.id||', 姓名:'||v_record.name||', 年龄'||v_record.age||',电话:'
||v_record.phone||',地址:'||v_record.address);
end if;
end loop;
end;
--调用存储过程
begin
p;
end;
--带参存储过程
create or replace procedure p1(v_a in number,v_b number,v_c out number,v_d in out number) is
begin
if(v_a>v_b ) then
v_c := v_a;
else
v_c := v_b;
end if;
v_d := v_d + 10;
end;
--调用带参存储过程
declare
v_a number := 11;
v_b number := 21;
v_c number;
v_d number := 4;
begin
p1(v_a,v_b,v_c,v_d);
dbms_output.put_line(v_d);
dbms_output.put_line(v_c);
end;
--触发器
create or replace trigger v_tri
after insert or delete or update on t_t_student for each row
begin
if inserting then
dbms_output.put_line('已执行添加操作');
elsif updating then
dbms_output.put_line('已执行编辑操作');
elsif deleting then
dbms_output.put_line('已执行删除操作');
end if;
end;
update t_t_student set address = '邯郸' where id = 39
--修改表搜索条件的值可以使用触发器,不建议使用
drop trigger v_tri;
create or replace trigger v_tri
after update on t_t_student for each row
begin
update t_t_student set name = :NEW.name where name = :OLD.name;
end;
update t_t_student set name = '姬只' where name = '姬喜';
--函数
create or replace function v_fun
(age t_t_student.age%type)
return t_t_student.name%type
is
begin
if(age<30) then
return '青年';
elsif(30<age and age<55) then
return '中年';
else
return '老年';
end if;
end;
select name ,v_fun(age) from t_t_student order by id