本人刚开始使用ORACLE,现在在Schema Manager中建立一个很简单的存储过程,结果始终提示有错误,请各位高人指点:存储过程LoadUserByName(PARM_NAME IN VARCHAR2)
AS BEGIN SELECT LINE FROM SYSTEMMANAGE
WHERE NAME=PARM_NAME;
END LOADUSERBYNAME;存储过程LOADALLUSER
BEGIN SELECT * FROM SYSTEMMANAGE;
END LOADALLUSERS;SYSTEMMANAGE为表名
AS BEGIN SELECT LINE FROM SYSTEMMANAGE
WHERE NAME=PARM_NAME;
END LOADUSERBYNAME;存储过程LOADALLUSER
BEGIN SELECT * FROM SYSTEMMANAGE;
END LOADALLUSERS;SYSTEMMANAGE为表名
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create or replace procedure LoadUserByName(PARM_NAME IN VARCHAR2,p_rc out pkg_test.myrctype)
AS
str varchar2(50);
BEGIN
str:='SELECT LINE FROM SYSTEMMANAGE
WHERE NAME='''||PARM_NAME||'''';
open p_rc for str;
END LOADUSERBYNAME;
/
declare
v_rc pkg_test.myrctype;
v_line systemmanage.line%type;
begin
LOADUSERBYNAME('1',v_rc);
fetch v_rc into v_line;
loop
exit when v_rc%notfound;
dbms_output.put_line(v_rc.line);
end loop;
end;
/
第一步:定义一个包头,包含一个游标类型。
每二步:创建一个过程
第三步:在SQL*PULS执行过程。具体每一步发生什么错误贴出来吧。
注:LOADUSERBYNAME('1',v_rc);--'1'是你的数据,找适当数据写入吧.
那LoadUserByName做例子。
create or replace procedure LoadUserByName
(PARM_NAME IN VARCHAR2)
is
cursor mycur is SELECT LINE FROM SYSTEMMANAGE;
begin
open mycur for SELECT LINE FROM SYSTEMMANAGE WHERE NAME=PARM_NAME;
end;
不过尽量不要用cursor,因为它对性能影响很大,可以考虑建立临时表。