写了一个过程,然后调用自定义包中的一些自定义过程和函数。
源代码如下:
自定义包为my_pkg
包说明部分如下:
create or replace package my_pkg is
Cursor c1 Is Select empno,ename,sal,comm,deptno From emp;--公有游标
Function retDeptNo(p_empno emp.empno%Type) Return emp.deptno%Type;
Function retAvgSal(p_deptno emp.deptno%Type) Return emp.sal%Type;
Procedure printInfo(p_sumsal emp.sal%Type,p_avgSal emp.sal%Type,p_empno emp.empno%TYPE);
end my_pkg;包体部分如下:
create or replace package body my_pkg is
v_avgsal emp.sal%Type;
v_deptno emp.deptno%Type;
Function retDeptNo(p_empno emp.empno%Type)
Return emp.deptno%Type
Is
begin
Select deptno Into v_deptno From emp Where empno = p_empno;
Return v_deptno;
END retDeptNo;
Function retAvgSal(p_deptno emp.deptno%Type)
Return emp.sal%Type
Is
BEGIN
Select avg(emp.sal+emp.comm) INTO v_avgsal FROM emp GROUP BY deptno HAVING deptno=p_deptno;
RETURN v_avgsal;
END retAvgSal;
Procedure printInfo(p_sumsal emp.sal%Type,p_avgSal emp.sal%Type,p_empno emp.empno%TYPE)
IS
--lv_sal emp.sal%TYPE;
lv_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO lv_comm FROM emp WHERE empno=p_empno;
IF p_sumsal>p_avgSal THEN
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is over deptavgsal,no need to add');
ELSIF (p_sumsal+500.00) < p_avgSal THEN
UPDATE emp SET sal=(sal+500) WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is:'||(p_sumsal+500)||'. after add.');
ELSE
UPDATE emp SET sal=(p_avgSal-comm) WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is:'||p_avgSal||'. after add.');
END IF;
END printInfo;
begin
DBMS_OUTPUT.PUT_LINE('TEST');
end my_pkg;调用包中函数的过程pro_test_mypkg如下:
create or replace procedure pro_test_mypkg is
TYPE emp_infor_type IS RECORD(
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_comm emp.comm%TYPE,
v_deptno emp.deptno%TYPE);
emp_info emp_infor_type;
v_deptno emp.deptno%TYPE;
v_avgsal emp.sal%TYPE;
v_sumsal emp.sal%TYPE;
begin
FOR emp_info in my_pkg.c1 LOOP
v_avgsal := my_pkg.retAvgSal(emp_info.v_deptno);
v_sumsal := (emp_info.v_sal+emp_info.v_comm);
my_pkg.printInfo(v_sumsal,v_avgsal,emp_info.v_empno);
DBMS_OUTPUT.PUT_LINE('TEST');
END LOOP;
end pro_test_mypkg;
编译过程中,包编译能够通过,
但是在编译调用包的过程的时候,报错,错误信息如下:说上面红色部分没有declared.大家帮忙看看是什么原因啊
上面的代码有哪些错误和不足吗
源代码如下:
自定义包为my_pkg
包说明部分如下:
create or replace package my_pkg is
Cursor c1 Is Select empno,ename,sal,comm,deptno From emp;--公有游标
Function retDeptNo(p_empno emp.empno%Type) Return emp.deptno%Type;
Function retAvgSal(p_deptno emp.deptno%Type) Return emp.sal%Type;
Procedure printInfo(p_sumsal emp.sal%Type,p_avgSal emp.sal%Type,p_empno emp.empno%TYPE);
end my_pkg;包体部分如下:
create or replace package body my_pkg is
v_avgsal emp.sal%Type;
v_deptno emp.deptno%Type;
Function retDeptNo(p_empno emp.empno%Type)
Return emp.deptno%Type
Is
begin
Select deptno Into v_deptno From emp Where empno = p_empno;
Return v_deptno;
END retDeptNo;
Function retAvgSal(p_deptno emp.deptno%Type)
Return emp.sal%Type
Is
BEGIN
Select avg(emp.sal+emp.comm) INTO v_avgsal FROM emp GROUP BY deptno HAVING deptno=p_deptno;
RETURN v_avgsal;
END retAvgSal;
Procedure printInfo(p_sumsal emp.sal%Type,p_avgSal emp.sal%Type,p_empno emp.empno%TYPE)
IS
--lv_sal emp.sal%TYPE;
lv_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO lv_comm FROM emp WHERE empno=p_empno;
IF p_sumsal>p_avgSal THEN
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is over deptavgsal,no need to add');
ELSIF (p_sumsal+500.00) < p_avgSal THEN
UPDATE emp SET sal=(sal+500) WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is:'||(p_sumsal+500)||'. after add.');
ELSE
UPDATE emp SET sal=(p_avgSal-comm) WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is:'||p_avgSal||'. after add.');
END IF;
END printInfo;
begin
DBMS_OUTPUT.PUT_LINE('TEST');
end my_pkg;调用包中函数的过程pro_test_mypkg如下:
create or replace procedure pro_test_mypkg is
TYPE emp_infor_type IS RECORD(
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_comm emp.comm%TYPE,
v_deptno emp.deptno%TYPE);
emp_info emp_infor_type;
v_deptno emp.deptno%TYPE;
v_avgsal emp.sal%TYPE;
v_sumsal emp.sal%TYPE;
begin
FOR emp_info in my_pkg.c1 LOOP
v_avgsal := my_pkg.retAvgSal(emp_info.v_deptno);
v_sumsal := (emp_info.v_sal+emp_info.v_comm);
my_pkg.printInfo(v_sumsal,v_avgsal,emp_info.v_empno);
DBMS_OUTPUT.PUT_LINE('TEST');
END LOOP;
end pro_test_mypkg;
编译过程中,包编译能够通过,
但是在编译调用包的过程的时候,报错,错误信息如下:说上面红色部分没有declared.大家帮忙看看是什么原因啊
上面的代码有哪些错误和不足吗
解决方案 »
- oracle imp 导入数据出错问题.
- 两台服务器两个表之间更新
- 建ORACLE时存储空间该如何规划!
- 删除用户没反应,强行结束后,再删除也不行.加 cascade也不行.其他用户也是一样.环境:AIX5.2,ORACLE 9i ,ERP TIPTOP
- VC 数据库 显示表的几个问题
- 这个时间转换又不对了,晕
- sql语句问题?爆急!给分
- 求救:oracle 中建立快照(实例华视图)的问题
- ...where name = 'xx' or name = 'yy' or name = 'zz' 是否可以写成类似于 where name in( 'xx' ,'yy', 'zz')这样的结构?
- oracle11 r2 提示TNS-12541: TNS: 无监听程序
- 字符串轉時間類型的問題
- over()的问题
而是弹出包中定义的一些东西。
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_comm emp.comm%TYPE,
v_deptno emp.deptno%TYPE);
把这个定义放在包头试试看.
现在我都是用的plsql自带的一些东西
create or replace procedure pro_test_mypkg is
type emp_infor_type is record(
v_empno emp.empno%type,
v_ename emp.ename%type,
v_sal emp.sal%type,
v_comm emp.comm%type,
v_deptno emp.deptno%type);
emp_info emp_infor_type;
v_avgsal emp.sal%type;
v_sumsal emp.sal%type;
begin
open my_pkg.c1;
loop
fetch my_pkg.c1 into emp_info; v_avgsal := my_pkg.retavgsal(emp_info.v_deptno);
v_sumsal := (emp_info.v_sal+emp_info.v_comm);
my_pkg.printinfo(v_sumsal,v_avgsal,emp_info.v_empno);
dbms_output.put_line('test');
end loop;
end pro_test_mypkg;
v_avgsal := my_pkg.retAvgSal(emp_info.v_deptno);
这里的emp_info 是my_pkg.c1 这个游标。所以这里,应该用emp_info.deptno 才是这个游标的对应字段值。
估计是这里的问题,其他没有细看。
v_sumsal := (emp_info.v_sal+emp_info.v_comm);
my_pkg.printInfo(v_sumsal,v_avgsal,emp_info.v_empno);
这3个估计都得对应的做修改。
可能包没编译成功
游标定义的是 Cursor c1 Is Select empno,ename,sal,comm,deptno From emp;--
诸如emp_info.v_deptno等,游标中并没有该字段,所以出现错误
将v_deptno改成deptno,其他的作类似修改
create or replace package my_pkg is
TYPE emp_infor_type IS RECORD(
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_comm emp.comm%TYPE,
v_deptno emp.deptno%TYPE);
emp_info emp_infor_type;
Cursor c1 Is Select empno,ename,sal,comm,deptno From emp;--公有游标
Function retDeptNo(p_empno emp.empno%Type) Return emp.deptno%Type;
Function retAvgSal(p_deptno emp.deptno%Type) Return emp.sal%Type;
Procedure printInfo(p_sumsal emp.sal%Type,p_avgSal emp.sal%Type,p_empno emp.empno%TYPE); end my_pkg;create or replace package body my_pkg is v_avgsal emp.sal%Type;
v_deptno emp.deptno%Type;
Function retDeptNo(p_empno emp.empno%Type)
Return emp.deptno%Type
Is
begin
Select deptno Into v_deptno From emp Where empno = p_empno;
Return v_deptno;
END retDeptNo;
Function retAvgSal(p_deptno emp.deptno%Type)
Return emp.sal%Type
Is
BEGIN
Select avg(emp.sal+emp.comm) INTO v_avgsal FROM emp GROUP BY deptno HAVING deptno=p_deptno;
RETURN v_avgsal;
END retAvgSal;
Procedure printInfo(p_sumsal emp.sal%Type,p_avgSal emp.sal%Type,p_empno emp.empno%TYPE)
IS
--lv_sal emp.sal%TYPE;
lv_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO lv_comm FROM emp WHERE empno=p_empno;
IF p_sumsal>p_avgSal THEN
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is over deptavgsal,no need to add');
ELSIF (p_sumsal+500.00) < p_avgSal THEN
UPDATE emp SET sal=(sal+500) WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is:'||(p_sumsal+500)||'. after add.');
ELSE
UPDATE emp SET sal=(p_avgSal-comm) WHERE empno=p_empno;
DBMS_OUTPUT.PUT_LINE('employee:'||p_empno||'. the sal is:'||p_avgSal||'. after add.');
END IF;
END printInfo;
begin
DBMS_OUTPUT.PUT_LINE('TEST');
end my_pkg;