--匿名块
set serveroutput on;
begin
dbms_output.put_line('this is an anonymous block');
end;
/
--不考虑无数据异常
declare
v_sname varchar2(200);
begin
select name into v_sname from students t where t.student_id=10381;
dbms_output.put_line('name is'||v_sname);
end;
/
--捕获空异常
declare
v_sname varchar2(200);
begin
select name into v_sname from students t where t.student_id=10381;
dbms_output.put_line('name is'||v_sname);
exception
when no_data_found then
dbms_output.put_line('no data found');
end;
/
--注释的例子,此例子包含CASE语法,%数据类型,&表示输入
declare
v_id teachers.teacher_id%type;
v_job_title teachers.title%type;
begin
v_id:=&teacher_id;
select title into v_job_title from teachers t where t.teacher_id=v_id; --将教师标号为V——id的教师职称赋值给变量
case
when v_job_title='教授' then
update teachers set wage=1.1*wage where teacher_id=v_id;
when v_job_title='高级工程师' or v_job_title='副教授' then
update teachers set wage=1.05*wage where teacher_id=v_id;
else
update teachers set wage=wage+100 where teacher_id=v_id;
end case;
end;
/
--注释的例子,此例子包含CASE语法,%数据类型,异常处理
declare
v_id teachers.teacher_id%type;
v_job_title teachers.title%type;
begin
v_id:=&teacher_id;
select title into v_job_title from teachers t where t.teacher_id=v_id; --将教师标号为V——id的教师职称赋值给变量
exception
when no_data_found then
dbms_output.put_line('no teachear be found');
case
when v_job_title='教授' then
update teachers set wage=1.1*wage where teacher_id=v_id;
when v_job_title='高级工程师' or v_job_title='副教授' then
update teachers set wage=1.05*wage where teacher_id=v_id;
else
update teachers set wage=wage+100 where teacher_id=v_id;
end case;
end;
/
--假如只有1个值,那么可以这样写,假如比较的条件比较多,那么换上面的写法
declare
v_id teachers.teacher_id%type;
v_job_title teachers.title%type;
begin
v_id:=&teacher_id;
select title into v_job_title from teachers t where t.teacher_id=v_id; --将教师标号为V——id的教师职称赋值给变量
exception
when no_data_found then
dbms_output.put_line('no teachear be found');
case v_job_title
when '教授' then
update teachers set wage=1.1*wage where teacher_id=v_id;
when '高级工程师' then
update teachers set wage=1.05*wage where teacher_id=v_id;
else
update teachers set wage=wage+100 where teacher_id=v_id;
end case;
end;
/
--在PLSQL中执行select语句,
select * from departments;
declare
v_id departments.department_id%type;
v_name departments.department_name%type;
v_address departments.address%type;
begin
select * into v_id,v_name,v_address from departments where department_id=&department_id;
dbms_output.put_line('系部名称:'||v_name);
dbms_output.put_line('系部地址:'||v_address);
end;
/
--行类型的数据类型 ,异常处理应该写到select into 之后?还是输出之后?还是只要有异常处理,写到哪里都没有关系,oracle会自动需找异常处理的代码?
--假如有些异常不可预料,怎么写?
declare
v_student students%rowtype;
begin
select * into v_student from students where student_id =&student_id;
dbms_output.put_line('name sex birthday');
dbms_output.put_line(v_student.name || v_student.sex||v_student.dob);
exception
when no_data_found then
dbms_output.put_line('no data found');
/*在表中执行select into 这条语句,如果返回值为空,那么会抛出no_data_found异常,
如果返回的结果过多,那么则会抛出too_many_row异常*/
end;
/
--假如没有找到数据,什么都不做
declare
v_id teachers.teacher_id%type;
v_title teachers.title%type;
begin
v_id:=&teacher_id;
select title into v_title from teachers where teacher_id=v_id;
exception
when no_data_found then
null;
if v_title='' then
update teachers set wage=1.1*wage where teacher_id=v_id;
else
update teachers set wage=wage+100 where teacher_id=v_id;
end if;
end;
---多次elsif的例子
declare
v_id teachers.teacher_id%type;
v_title teachers.title%type;
begin
v_id:=&teacher_id;
select title into v_title from teachers where teacher_id=v_id;
exception
when no_data_found then
null;
if v_title='' then
update teachers set wage=1.1*wage where teacher_id=v_id;
elsif v_title='aaa' then
update teachers set wage=wage+100 where teacher_id=v_id;
elsif v_title='ccc' then
null;
else
null;
end if;
end;
--loop循环的例子
create table total(n int ,reault int);
declare
v_i int:=1;
v_sum int:=0;
begin
loop
v_sum:=v_i+1;
insert into total values (v_i,v_sum);
exit when v_sum=10;
v_i:=v_i+1;
end loop;
end;
/
select * from total;
--while 循环的例子
declare
v_i INTEGER:=1;
v_sum INTEGER:=0;
begin
while v_i<=10 loop
v_sum:=v_i+1;
insert into total values(v_i,v_Sum);
v_i:=v_i+1;
end loop;
commit;
end;
/
select * from total;
--for循环的测试例子
declare
v_i int:=1;
v_sum int :=1;
begin
for v_i in 1..100 loop
v_sum:=v_i*1;
insert into total values (v_i,v_sum);
end loop;
commit;
end;
/
select * from total;
PLSQL开发基础--结构控制
最新推荐文章于 2020-05-05 19:43:00 发布