博主自主知识产权《springboot深入浅出系列课程》(16章97节文档) 已经上线,请关注

PLSQL-sql操作&判断循环

oracle 字母哥 0评论

PL/SQL中SQL操作

  • sql%rowcount属性:实际在sql操作过程中开起了隐式游标,表示最近执行的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(变量占位符)


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 语句


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;

参考

http://www.cnblogs.com/hoojo/archive/2011/05/03/2035357.html

喜欢 (1)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址