oracle语句块判断结果,Oracle笔记 八、PL/SQL跳转/判断/循环语句块

--goto跳转语句

--在goto 后，跳转到相应的语句，然后执行该语句和后面所有语句

begin

dbms_output.put_line('goto开始了');

goto c;

--不被执行

dbms_output.put_line('goto');

dbms_output.put_line('goto');

dbms_output.put_line('goto');

dbms_output.put_line('goto');

<>

dbms_output.put_line('goto aaa');

<>

dbms_output.put_line('goto bbb');

<>

dbms_output.put_line('goto ccc');

end;

--PL/SQL中的sql和rowcount属性：sql代表最近执行的sql语句，rowcount代表最近执行的sql语句影响的行数

declare

vCount number;

vDeptno dept2.deptno%type := 10;

begin

--select count(*) into vCount from dept2;

--update dept2 set dname = dname || '~.~' where deptno = vDeptno;

--delete from dept2 where deptno = 40;

insert into dept2 select * from dept;

dbms_output.put_line('count:' || vCount);

dbms_output.put_line(sql%rowcount || '行受影响');

end;

--PL/SQL执行sql语句

begin

execute immediate 'create table temp as select * from dept';

end;

--动态sql，把sql语句放到变量中并传递参数

declare

sqlstr varchar2(100);

begin

sqlstr := 'insert into scott.dept values(12, :1, :abc)';

--变量是按照占位符顺序插入的

execute immediate sqlstr using '教务部', '武汉';

end;

select * from scott.dept;

--动态sql,把查询结果赋值给变量

declare

dept_row_data scott.dept%rowtype;

sqlstr varchar2(100);

begin

sqlstr := 'select * from scott.dept where deptno = 11';

execute immediate sqlstr into dept_row_data;

dbms_output.put_line(dept_row_data.deptno);

end;

--if 语句

--select * from emp;

declare

vSal emp.sal%type;

begin

select sal into vSal from emp where empno = 7566;

if (vSal < 1200) then

dbms_output.put_line('小于1200');

elsif (vSal < 2000) then

dbms_output.put_line('小于2000');

else

dbms_output.put_line('大于2000');

end if;

end;

--loop循环

declare

i binary_integer := 10;

begin

loop

dbms_output.put_line(i);

i := i - 1;

exit when(i < 1);

end loop;

end;

--while循环

declare

i binary_integer := 1;

begin

while i < 11 loop

dbms_output.put_line(i);

i := i + 1;

end loop;

end;

--for 循环

begin

for i in 1..10 loop

dbms_output.put_line(i);

end loop;

for i in reverse 1..10 loop

dbms_output.put_line(i);

end loop;

end;