--goto跳轉(zhuǎn)語句

--在goto 后，跳轉(zhuǎn)到相應(yīng)的語句，然后執(zhí)行該語句和后面所有語句

begin

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

     goto c;

          --不被執(zhí)行

          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代表最近執(zhí)行的sql語句，rowcount代表最近執(zhí)行的sql語句影響的行數(shù)

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執(zhí)行sql語句

begin

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

end;

--動(dòng)態(tài)sql，把sql語句放到變量中并傳遞參數(shù)

declare

     sqlstr varchar2(100);

begin

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

     --變量是按照占位符順序插入的

     execute immediate sqlstr using '教務(wù)部', '武漢';

end;

select * from scott.dept;

--動(dòng)態(tài)sql,把查詢結(jié)果賦值給變量

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循環(huán)

declare

  i binary_integer := 10;

begin

  loop

    dbms_output.put_line(i);

    i := i - 1;

    exit when(i < 1);

  end loop;

end;

--while循環(huán)

declare

  i binary_integer := 1;

begin

  while i < 11 loop

     dbms_output.put_line(i);

     i := i   1;

  end loop;

end;

--for 循環(huán)

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;