declare cursor emp_cursor is select empno,ename,job from emp; v_empno emp.empno%type; v_name emp.ename%type; v_job emp.job%type; begin open emp_cursor; loop fetch emp_cursor into v_empno,v_name,v_job; dbms_output.put_line('员工号为:'||v_empno||'姓名是'||v_name||'职位:'||v_job); exit when emp_cursor%notfound; end loop; close emp_cursor; end;
declare cursor emp_cursor is select empno,ename,job from emp; v_empno emp.empno%type; v_name emp.ename%type; v_job emp.job%type; begin open emp_cursor; loop fetch emp_cursor into v_empno,v_name,v_job; exit when emp_cursor%notfound; end loop; if emp_cursor%isopen then dbms_output.put_line('游标已打开'); dbms_output.put_line('读取了'||emp_cursor%rowcount||'行'); else dbms_output.put_line('游标没有打开'); end if; close emp_cursor; end;
create table emp_new as select * from emp;
begin update emp_new set sal = sal+500 where empno=&empno; if sql%found then dbms_output.put_line('成功修改'); commit; else dbms_output.put_line('修改失败'); rollback; end if; end;
declare cursor emp_cursor is select empno,ename,job from emp; begin for emp_record in emp_cursor loop dbms_output.put_line('员工号:'||emp_record.empno||'员工姓名'||emp_record.ename||'员工职位'||emp_record.job); end loop; end;
begin for emp_record in (select empno,ename,job from emp) loop dbms_output.put_line('员工号:'||emp_record.empno||'员工姓名'||emp_record.ename||'员工职位'||emp_record.job); end loop; end;
declare cursor emp_cursor(dno number)is select empno,ename,job from emp where deptno=dno; begin for emp_record in emp_cursor(&dno) loop dbms_output.put_line('员工号'||emp_record.empno||'姓名'||emp_record.ename||'职位'||emp_record.job); end loop; end;
create table emp_new as select * from emp;
declare cursor empnew_cursor is select ename,job from emp_new for update; begin for empnew_record in empnew_cursor loop dbms_output.put_line('姓名'||empnew_record.ename||'职位'||empnew_record.job); if empnew_record.job='president' then update emp_new set sal=sal+1000 where current of empnew_cursor; elsif empnew_record.job='manager' then update emp_new set sal=sal+500 where current of empnew_cursor; end if; end loop; commit; end;
select * from emp where job in('president','manager'); select * from emp_new where job in('president','manager');
如对本文有疑问, 点击进行留言回复!!
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句(时间格式、去重、去特殊符号、查询表空间等)
Navicate 如何导出数据库中的存储过程、事件、视图等?
每日一记:Oracle升级2020年4月份的数据库补丁Database Patch Set Update : 11.2.0.4.200414 (30670774)
网友评论