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;