小弟刚刚开始学习orcale
写存储过程遇到个问题.
先写了个
函数create or replace function check_compare(pid varchar2,birthday varchar2) return boolean is
Result boolean;
tmp varchar2(20);
begin
tmp:=substrB(pid,6,11);
if(tmp=birthday) then
Result:=true;
else
Result:=false;
end if;
return(Result);
end check_compare;---用来把传进来的两个字符窜进行切割并做比较,返回boolean还有一个存储过程要调用这个函数.我不知道怎么在存储过程中调用函数。我是这样写的:
create or replace procedure zc.check_pid as
v_pid char(18);
v_birthday char(8);isequal boolean;cursor mycursor is
select t.pid,t.birthday from zc.tojswdata t where rownum=1;
cursorrecord mycursor%rowtype;
begin
open mycursor;
fetch mycursor into cursorrecord;--从数据库中取得一条记录进行测试,将pid和birthday字段取出
v_pid:=cursorrecord.pid;
v_birthday:=cursorrecord.birthday;isequal:=check_compare(v_pid,v_birthday);--这里是 调用,不知道这样 行不行
....
end;
我这样写他报错sqlplus说Error: PLS-00201: 必须说明标识符 'CHECK_COMPARE'
Line: 16
Text: isequal:=check_compare(v_pid,v_birthday);
有什么 解决办法嘛?谢谢,在线等
写存储过程遇到个问题.
先写了个
函数create or replace function check_compare(pid varchar2,birthday varchar2) return boolean is
Result boolean;
tmp varchar2(20);
begin
tmp:=substrB(pid,6,11);
if(tmp=birthday) then
Result:=true;
else
Result:=false;
end if;
return(Result);
end check_compare;---用来把传进来的两个字符窜进行切割并做比较,返回boolean还有一个存储过程要调用这个函数.我不知道怎么在存储过程中调用函数。我是这样写的:
create or replace procedure zc.check_pid as
v_pid char(18);
v_birthday char(8);isequal boolean;cursor mycursor is
select t.pid,t.birthday from zc.tojswdata t where rownum=1;
cursorrecord mycursor%rowtype;
begin
open mycursor;
fetch mycursor into cursorrecord;--从数据库中取得一条记录进行测试,将pid和birthday字段取出
v_pid:=cursorrecord.pid;
v_birthday:=cursorrecord.birthday;isequal:=check_compare(v_pid,v_birthday);--这里是 调用,不知道这样 行不行
....
end;
我这样写他报错sqlplus说Error: PLS-00201: 必须说明标识符 'CHECK_COMPARE'
Line: 16
Text: isequal:=check_compare(v_pid,v_birthday);
有什么 解决办法嘛?谢谢,在线等
Result boolean;
tmp varchar2(20);
begin
tmp:=substrB(pid,6,11);
if(tmp=birthday) then
Result:=true;
else
Result:=false;
end if;
return(Result);
end check_compare;
/
比如function check_compare在用户b下
则
conn b/psw
grant execute on check_compare to zc;
...
conn zc/111111@myoracle
grant execute on check_compare to zc;
begin
zc.check_pid_test;
commit;
end;
conn system/manager
grant execute any procudure to zc;
我那个必须说明标识符 'CHECK_COMPARE'是函数,不是过程啊.
:=.(@%;notnullrangedefault
character
Line: 4
Text: v_pid varchar2(30);Error: Hint: Variable 'conn' is declared but never used in 'zc.check_pid_test1'
Line: 4
Text: v_pid varchar2(30);Error: Hint: Variable 'tmp' is declared but never used in 'zc.check_pid_test1'
Line: 8
Text: type curs is ref cursor;
抱错了
isequal:=zc.check_compare(v_pid,v_birthday);
...
Line: 23
Text: execute immediate 'Error: PL/SQL: Statement ignored
Line: 23
Text: execute immediate 'Error: Hint: Variable 'tmp' is declared but never used in 'zc.check_pid_test1'
Line: 7
Text: type curs is ref cursor;