表中数据如下:EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7499 SMITH SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7839 1981-4-2 2975.00 30
7566 JONES MANAGER 7698 1981-2-20 1600.00 300.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 0 1981-11-17 5000.00 0
7844 TURNE SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
题目如下:
1.在包pack中定义一个函数,根据员工编号参数,将该员工的奖金(comm)修改为参数指定的值
create or replace package emp_pack
is
emp_record emp%rowtype;
function updateEmpComm(
v_empno in emp.empno%type,
v_comm in emp.comm%type)
return number;
procedure queryEmp(v_empno in emp.empno%type);
end emp_pack;2.在包pack中定义一个过程,根据员工编号参数,查询出该员工的姓名和工资级别,其中0---2000为C级(含2000,下同),2000---4000为B级,4000以上为A级.
提示:使用case语句
create or replace package body emp_pack
is
function updateEmpComm(
v_empno in emp.empno%type,
v_comm in emp.comm%type)
return number
is
e_deptno_exist exception;
pragma exception_init(e_deptno_exist,-1);
begin
update emp set comm=v_comm where empno=v_empno;
if sql%found then
return 1;
else return 0;
end if;
exception
when e_deptno_exist then
dbms_output.put_line('empno' || v_empno ||'has exist!!');
return -1;
when others then
dbms_output.put_line(sqlcode || ',' ||sqlerrm);
return -1;
end updateEmpComm;
procedure queryEmp(v_empno in emp.empno%type)
is
begin
select ename,
case when emp_record.comm>4000 then 'A'
when emp_record.comm>2000 then 'B'
else 'C' end sal
into emp_record
from emp
where empno=v_empno;
dbms_output.put_line(emp_record.ename || ',' || emp_record.sal);
exception
when no_data_found then
dbms_output.put_line('no data found!');
when too_many_rows then
dbms_output.put_line('too many rows!');
when others then
dbms_output.put_line(sqlcode || ',' || sqlerrm);
end queryEmp;
end emp_pack;
3.给出调用程序.DECLARE
v_result NUMBER;
v_empno NUMBER := &eno;
v_comm NUMBER := &ecomm;
BEGIN
v_result := emp_pack. updateEmpComm (v_empno, v_comm);
IF v_result = -1 THEN
dbms_output.put_line ('some exception occurs! ');
ELSIF v_result = 0 THEN
DBMS_OUTPUT.put_line ('insert error');
ELSIF v_result = 1 THEN
DBMS_OUTPUT.put_line ('the dept ' || v_deptno || ' has been inserted!');
demo_pack.querydept (v_deptno);
v_result := demo_pack.removedept (v_deptno);
IF v_result = -1
THEN
DBMS_OUTPUT.put_line ('some exception occurs! ');
ELSIF v_result = 1
THEN
DBMS_OUTPUT.put_line ('the dept ' || v_deptno || ' has been deleted!');
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ',' || SQLERRM);
END;
主要出错在CASE 那边,提示错误如下:
33 pl/sql :ORA-00913:值过多
28 PL/SQL:SQL Statement ignored麻烦大家帮忙看看啦........
7499 SMITH SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7839 1981-4-2 2975.00 30
7566 JONES MANAGER 7698 1981-2-20 1600.00 300.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 0 1981-11-17 5000.00 0
7844 TURNE SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
题目如下:
1.在包pack中定义一个函数,根据员工编号参数,将该员工的奖金(comm)修改为参数指定的值
create or replace package emp_pack
is
emp_record emp%rowtype;
function updateEmpComm(
v_empno in emp.empno%type,
v_comm in emp.comm%type)
return number;
procedure queryEmp(v_empno in emp.empno%type);
end emp_pack;2.在包pack中定义一个过程,根据员工编号参数,查询出该员工的姓名和工资级别,其中0---2000为C级(含2000,下同),2000---4000为B级,4000以上为A级.
提示:使用case语句
create or replace package body emp_pack
is
function updateEmpComm(
v_empno in emp.empno%type,
v_comm in emp.comm%type)
return number
is
e_deptno_exist exception;
pragma exception_init(e_deptno_exist,-1);
begin
update emp set comm=v_comm where empno=v_empno;
if sql%found then
return 1;
else return 0;
end if;
exception
when e_deptno_exist then
dbms_output.put_line('empno' || v_empno ||'has exist!!');
return -1;
when others then
dbms_output.put_line(sqlcode || ',' ||sqlerrm);
return -1;
end updateEmpComm;
procedure queryEmp(v_empno in emp.empno%type)
is
begin
select ename,
case when emp_record.comm>4000 then 'A'
when emp_record.comm>2000 then 'B'
else 'C' end sal
into emp_record
from emp
where empno=v_empno;
dbms_output.put_line(emp_record.ename || ',' || emp_record.sal);
exception
when no_data_found then
dbms_output.put_line('no data found!');
when too_many_rows then
dbms_output.put_line('too many rows!');
when others then
dbms_output.put_line(sqlcode || ',' || sqlerrm);
end queryEmp;
end emp_pack;
3.给出调用程序.DECLARE
v_result NUMBER;
v_empno NUMBER := &eno;
v_comm NUMBER := &ecomm;
BEGIN
v_result := emp_pack. updateEmpComm (v_empno, v_comm);
IF v_result = -1 THEN
dbms_output.put_line ('some exception occurs! ');
ELSIF v_result = 0 THEN
DBMS_OUTPUT.put_line ('insert error');
ELSIF v_result = 1 THEN
DBMS_OUTPUT.put_line ('the dept ' || v_deptno || ' has been inserted!');
demo_pack.querydept (v_deptno);
v_result := demo_pack.removedept (v_deptno);
IF v_result = -1
THEN
DBMS_OUTPUT.put_line ('some exception occurs! ');
ELSIF v_result = 1
THEN
DBMS_OUTPUT.put_line ('the dept ' || v_deptno || ' has been deleted!');
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ',' || SQLERRM);
END;
主要出错在CASE 那边,提示错误如下:
33 pl/sql :ORA-00913:值过多
28 PL/SQL:SQL Statement ignored麻烦大家帮忙看看啦........
解决方案 »
- ORACLE SQL语句求助 oracle中table中默认存在emp,dept表,在这基础上询问
- 有关Oracle更新语句中空值处理的问题
- 两个ORACLE查询的一个基础问题,希望大家帮我看一下,谢谢。
- ORACLE响应速度的问题
- SQL> startup mountORA-12514: TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
- 高手解难题,高分送高手(关于数据导出和导入....)
- 纵横表转换问题--请帮忙
- 急救!为何登录控制台失败?初学者请教!!!
- 问一个PL/SQL块结束符的问题
- 求大神·关于oracle查找字符串问题
- 相互学习1
- 存储过程的问题,高手帮我解决下
case when emp_record.comm>4000 then 'A'
when emp_record.comm>2000 then 'B'
else 'C' end sal
into emp_record
from emp
where empno=v_empno;
//值过多 是不是这块儿 select后面应该是两个字段吧(ename,sal),而into 后面只有一个?这个存储过程很复杂啊 基本上看不懂 呵呵 如果说错了 别见笑
下面帖子 可以看看:
http://blog.csdn.net/web_gus/archive/2004/10/13/134907.aspx
1. 个人感觉, select 那一段,写成cursor应该也可以吧;
2. emp_record中的sal 和 case when 中的最后的sal应该不是一个类型吧?
3. 不知道楼主的用意,我给改了改,编译通过了,不知道对不,望讨论进步:create or replace package emp_pack
is
/*emp_record emp%rowtype;*/
Type emp_record IS RECORD(
ename emp.ename%TYPE
,salClass VARCHAR2(1)
);
function updateEmpComm(
v_empno in emp.empno%type,
v_comm in emp.comm%type)
return number;
procedure queryEmp(v_empno in emp.empno%type);
end emp_pack;
/
create or replace package body emp_pack
is
function updateEmpComm(
v_empno in emp.empno%type,
v_comm in emp.comm%type)
return number
is
e_deptno_exist exception;
pragma exception_init(e_deptno_exist,-1);
begin
update emp set comm=v_comm where empno=v_empno;
if sql%found then
return 1;
else return 0;
end if;
exception
when e_deptno_exist then
dbms_output.put_line('empno' || v_empno ||'has exist!!');
return -1;
when others then
dbms_output.put_line(sqlcode || ',' ||sqlerrm);
return -1;
end updateEmpComm; procedure queryEmp(v_empno in emp.empno%type)
IS
empRec emp_record;
begin
select ename,
case when emp.comm>4000 then 'A'
when emp.comm>2000 then 'B'
else 'C' end salClass
into empRec
from emp
where empno=v_empno;
dbms_output.put_line(empRec.ename || ',' || empRec.salClass);
exception
when no_data_found then
dbms_output.put_line('no data found!');
when too_many_rows then
dbms_output.put_line('too many rows!');
when others then
dbms_output.put_line(sqlcode || ',' || sqlerrm);
end queryEmp;
end emp_pack;
/
希望共同进步!