包体:
CREATE OR REPLACE PACKAGE BODY SCOTT.test1 AS
/******************************************************************************
NAME: test1
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/8/2010 1. Created this package body.
******************************************************************************/ PROCEDURE procedure1(Param1 IN varchar2) IS
var_no1 zjm.no%type;
var_sql varchar2(200); BEGIN
var_sql :='select no from zjm ';
IF Param1 IS NOT NULL or Param1 <> ' ' THEN
var_sql := var_sql || Param1;
END IF;
execute immediate var_sql into var_no1;
exception when no_data_found then
dbms_output.put_line('no data');
END procedure1;END test1;
/包申明:
CREATE OR REPLACE PACKAGE SCOTT.test1 AS
/******************************************************************************
NAME: test1
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/6/2010 1. Created this package.
******************************************************************************/ PROCEDURE procedure1(Param1 IN number);
END test1;
/编译包体的时候报:PLS-00323: subprogram or cursor 'PROCEDURE1' is declared in a package specification and must be defined in the package body
CREATE OR REPLACE PACKAGE BODY SCOTT.test1 AS
/******************************************************************************
NAME: test1
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/8/2010 1. Created this package body.
******************************************************************************/ PROCEDURE procedure1(Param1 IN varchar2) IS
var_no1 zjm.no%type;
var_sql varchar2(200); BEGIN
var_sql :='select no from zjm ';
IF Param1 IS NOT NULL or Param1 <> ' ' THEN
var_sql := var_sql || Param1;
END IF;
execute immediate var_sql into var_no1;
exception when no_data_found then
dbms_output.put_line('no data');
END procedure1;END test1;
/包申明:
CREATE OR REPLACE PACKAGE SCOTT.test1 AS
/******************************************************************************
NAME: test1
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 1/6/2010 1. Created this package.
******************************************************************************/ PROCEDURE procedure1(Param1 IN number);
END test1;
/编译包体的时候报:PLS-00323: subprogram or cursor 'PROCEDURE1' is declared in a package specification and must be defined in the package body
包里的函数参数和包头定义的不一样
一个是varchar2,一个是number!
解决方法是:将后面执行部分中的number类型改成varchar2类型!
m_test1 varchar2(6);
procedure testprc(p_test1 varchar2);
end;
/
create or replace package body tp_1 as
procedure testprc(p_test1 varchar2 ) is
begin
tp_1.m_test1:=p_test1;
dbms_output.put_line(tp_1.m_test1);
end;
end;
/--调用
exec tp_1.testprc('asdf');