create or replace procedure getclassname(id in varchar2) as classname1 varchar2(20); begin select classname into classname1 from jxl.class where classid = id; exception when no_data_found then raise_application_error(-20100, 'cannot find the id'); end;
楼主犯了一个微细的错误,变里与字段名不同名,这样oracle只能认作表的字段 create procedure getclassname(p_id in varchar2, p_classname out varchar2) as begin select classname into p_classname from jxl.class where classid = p_id; dbms_output.put_line(p_classname); exception when no_data_found then raise_application_error(-20100, 'cannot find the id'); end; /
to beckhambobo: 我已经把我的procedure改成和你的一样了,但是还是出现我以前的错误,说必须声明 variable p_classname varchar2; execute getclassid('1', p_classname); 出错如下: ERROR at line: 1: ORA-06550: line 1, column 23: PLS-00201: identifier 'P_CLASSNAME' must be declared ORA-06550: line 1, column7: PL/SQL: Statement ignored
说了一大堆,就是为了这个绑定变量(bind variable)。 先看看 Oracle 自带的帮助吧.SQL> help variable VARIABLE -------- Declares a bind variable that can then be referenced in PL/SQL. VARIABLE with no arguments displays a list of all variables declared in the session. VARIABLE followed by a name lists that variable. VAR[IABLE] [variable [NUMBER | CHAR | CHAR (n) | NCHAR | NCHAR (n) | VARCHAR2 (n) | NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR]] ========================================================================= 以上是定义的方式. 至于调用嘛,这样写一个简单的例子你就明白了...SQL>variable strOut varchar2(20); -- 定义 strOut 变量 SQL>CREATE OR REPLACE PROCEDURE sp_TestVar(str_id OUT VARCHAR2) AS BEGIN str_id := 'This is Test.'; end; -- 建立过程SQL>exec sp_TestVar(:strOut); -- 调用过程,内容会输出到 strOut. SQL>print strOut; -- 看看会是什么内容.(还是空的话,找我要 :-),呵呵... )
create or replace procedure getclassname(id in varchar2) as classname1 varchar2(20); begin select classname into classname1 from jxl.class where classid = id; dbms_output.put_line(classname1); 此句可在屏幕中打印出来。 exception when no_data_found then raise_application_error(-20100, 'cannot find the id'); end;
classname1 varchar2(20);
begin
select classname into classname1 from jxl.class
where classid = id;
exception
when no_data_found then
raise_application_error(-20100, 'cannot find the id');
end;
variable classname1 varchar2;
execute getclassname('1');
print classname1;
打印出来的是空值,没有取出来
create procedure getclassname(p_id in varchar2, p_classname out varchar2) as
begin
select classname into p_classname from jxl.class
where classid = p_id;
dbms_output.put_line(p_classname);
exception
when no_data_found then
raise_application_error(-20100, 'cannot find the id');
end;
/
我已经把我的procedure改成和你的一样了,但是还是出现我以前的错误,说必须声明
variable p_classname varchar2;
execute getclassid('1', p_classname);
出错如下:
ERROR at line: 1:
ORA-06550: line 1, column 23:
PLS-00201: identifier 'P_CLASSNAME' must be declared
ORA-06550: line 1, column7:
PL/SQL: Statement ignored
先看看 Oracle 自带的帮助吧.SQL> help variable VARIABLE
-------- Declares a bind variable that can then be referenced in PL/SQL.
VARIABLE with no arguments displays a list of all variables declared
in the session. VARIABLE followed by a name lists that variable. VAR[IABLE] [variable [NUMBER | CHAR | CHAR (n) | NCHAR | NCHAR (n) |
VARCHAR2 (n) | NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR]]
=========================================================================
以上是定义的方式.
至于调用嘛,这样写一个简单的例子你就明白了...SQL>variable strOut varchar2(20); -- 定义 strOut 变量
SQL>CREATE OR REPLACE PROCEDURE sp_TestVar(str_id OUT VARCHAR2)
AS
BEGIN
str_id := 'This is Test.';
end; -- 建立过程SQL>exec sp_TestVar(:strOut); -- 调用过程,内容会输出到 strOut.
SQL>print strOut; -- 看看会是什么内容.(还是空的话,找我要 :-),呵呵... )
declare
v_classname varchar2(10);
begin
getclassid('1', v_classname);
dbms_output.put_line(v_classname);
end;
/
classname1 varchar2(20);
begin
select classname into classname1 from jxl.class
where classid = id;
dbms_output.put_line(classname1); 此句可在屏幕中打印出来。
exception
when no_data_found then
raise_application_error(-20100, 'cannot find the id');
end;