oracle笔记5
来源:互联网 发布:2017软件企业认定 编辑:程序博客网 时间:2024/06/05 11:08
--1、任意执行一个update操作,用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
begin
update emp set ename = 'alerk' where empno = 7369;
if sql%isopen then
dbms_output.put_line('opening');
else
dbms_output.put_line('closing');
end if;
if sql%found then
dbms_output.put_line('found');
else
dbms_output.put_line('not found');
end if;
dbms_output.put_line(sql%rowcount);
exception
when no_data_found then
dbms_output.put_line('nodata ');
when too_many_rows then
dbms_output.put_line('too many rows ');
end;
--2使用游标和loop循环来显示所有部门的名称
declare
cursor cur_name is select dname from dept;
v_name cur_name%rowtype;
begin
for v_name in cur_name loop
dbms_output.put_line(v_name.dname);
exit when cur_name%notfound;
end loop;
end;
--使用游标和while循环来显示所有部门的的地理位置(用%found属性)
declare
cursor cur_dept is
select loc from dept;
v_loc cur_dept%rowtype;
begin
open cur_dept;
fetch cur_dept into v_loc;
while cur_dept%found loop
dbms_output.put_line(v_loc.loc);
fetch cur_dept into v_loc;
end loop;
end;
--接收用户输入的部门编号,用for循环和游标,打印出此部门的所有雇员的所有信息(使用循环游标)
declare
cursor cur_emp is
select * from emp where empno = &v_deptno;
r_emp emp%rowtype;
begin
for r_emp in cur_emp loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
declare
cursor cur_emp(v_deptno varchar2) is
select * from emp where empno = v_deptno;
r_emp emp%rowtype;
begin
for r_emp in cur_emp(&v_deptno) loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
--向游标传递一个工种,显示此工种的所有雇员的所有信息(使用参数游标)
declare
cursor cur_emp(jobname varchar2) is
select * from emp where job = jobname;
r_emp emp%rowtype;
begin
--传递字符要加'' 例如'CLERK'
for r_emp in cur_emp(&jobname) loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
declare
cursor cur_emp(jobname varchar2) is
select * from emp where job = jobname;
r_emp emp%rowtype;
begin
--传递字符要加'' 例如'CLERK'
for r_emp in cur_emp('CLERK') loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
begin
update emp set ename = 'alerk' where empno = 7369;
if sql%isopen then
dbms_output.put_line('opening');
else
dbms_output.put_line('closing');
end if;
if sql%found then
dbms_output.put_line('found');
else
dbms_output.put_line('not found');
end if;
dbms_output.put_line(sql%rowcount);
exception
when no_data_found then
dbms_output.put_line('nodata ');
when too_many_rows then
dbms_output.put_line('too many rows ');
end;
--2使用游标和loop循环来显示所有部门的名称
declare
cursor cur_name is select dname from dept;
v_name cur_name%rowtype;
begin
for v_name in cur_name loop
dbms_output.put_line(v_name.dname);
exit when cur_name%notfound;
end loop;
end;
--使用游标和while循环来显示所有部门的的地理位置(用%found属性)
declare
cursor cur_dept is
select loc from dept;
v_loc cur_dept%rowtype;
begin
open cur_dept;
fetch cur_dept into v_loc;
while cur_dept%found loop
dbms_output.put_line(v_loc.loc);
fetch cur_dept into v_loc;
end loop;
end;
--接收用户输入的部门编号,用for循环和游标,打印出此部门的所有雇员的所有信息(使用循环游标)
declare
cursor cur_emp is
select * from emp where empno = &v_deptno;
r_emp emp%rowtype;
begin
for r_emp in cur_emp loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
declare
cursor cur_emp(v_deptno varchar2) is
select * from emp where empno = v_deptno;
r_emp emp%rowtype;
begin
for r_emp in cur_emp(&v_deptno) loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
--向游标传递一个工种,显示此工种的所有雇员的所有信息(使用参数游标)
declare
cursor cur_emp(jobname varchar2) is
select * from emp where job = jobname;
r_emp emp%rowtype;
begin
--传递字符要加'' 例如'CLERK'
for r_emp in cur_emp(&jobname) loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
declare
cursor cur_emp(jobname varchar2) is
select * from emp where job = jobname;
r_emp emp%rowtype;
begin
--传递字符要加'' 例如'CLERK'
for r_emp in cur_emp('CLERK') loop
dbms_output.put_line('员工号:'||r_emp.EMPNO||'员工名:'||r_emp.ENAME||'工资:'||r_emp.SAL);
end loop;
end;
- oracle 笔记5 包
- Oracle笔记.5
- oracle笔记5-练习
- oracle笔记5-练习
- Oracle学习笔记 5
- oracle笔记5
- Oracle笔记(5)
- ORACLE 笔记5
- Oracle笔记5
- Oracle笔记
- Oracle笔记
- oracle笔记
- Oracle 笔记!
- oracle笔记
- oracle 笔记
- ORACLE笔记
- oracle笔记
- ORACLE笔记
- 实现企业VPN
- Eclipse下配置主题颜色
- redhat 6 下安装telnet
- linux网络参数设置CentOS6.2
- 比较两个文件,同一url的出现次数 17道海量数据处理
- oracle笔记5
- OnDraw与OnPaint的区别
- Linux重新启动网络的命令
- linux下FTP安装与配置
- php传时间倒计时功能
- shell中对小数进行比较的方法总结
- Linux中shell基本运算
- IT招聘及面试经验之谈(来自网络)
- CentOS6.3更改yum源换成163