PLSQL开发基础--结构控制

--匿名块
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;