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');
    <<a>>
           dbms_output.put_line('goto aaa');           
    <<b>>
           dbms_output.put_line('goto bbb');
    <<c>>
           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;

• 作者：hoojo