set serveroutput ondeclare
myvar number(8);
begin
select count(*) into myvar from dual;
dbms_output.put_line(myvar);
end;
/
myvar number(8);
begin
select count(*) into myvar from dual;
dbms_output.put_line(myvar);
end;
/
begin
select count(9) into a from jbxx;
dbms_output.put_line(a);
end;
begin
select count(9) into a from b;
dbms_output.put_line(a);
end;
execute IMMEDIATE 'select avg('||rtrim(ltrim(var_ITABB))||') into '||var_Return||' from '||rtrim(ltrim(var_TABLENAME))||' where STCD='''||rtrim(ltrim(var_STCD))||''' ' ;
end if;
dbms_output.put_line(var_Return);这个有什么问题吗???
表是变量,字段名也是个变量var_Return float;execute IMMEDIATE 'select avg('||rtrim(ltrim(var_ITABB))||') into var_Return from '||rtrim(ltrim(var_TABLENAME))||' where STCD='''||rtrim(ltrim(var_STCD))||''' ' ;把这个值,赋予一个变量!!!To: ATGC(我还以为我会永远守在她身旁。。)
表示感谢!
To:ORARichard(没钱的日子好难过啊)
表示感谢!
var_ITABB: 类型:CHAR
var_TABLENAME: 类型:VARCHAR
var_STCD: 类型:varchar
STCD: 类型:CHAR表示感谢!!
-------- -------- ---------- -
aa bb 100 1
aa bb 200 2
aa bb 300 2create or replace procedure test
(var_itabb in varchar2,var_tablename in varchar2,var_stcd char)
as
type my_cursor is ref cursor;
your_cursor my_cursor;
var_return float;
sql_str varchar2(2000);
begin
if (upper(var_itabb)!='tphk' and upper(var_itabb)!='wt') then
sql_str := 'select max(trim('||var_itabb||')) from '||var_tablename||' where stcd='''||trim(var_stcd)||'''';
open your_cursor for sql_str;
fetch your_cursor into var_return;
close your_cursor;
end if;
dbms_output.put_line(var_return);
end;
/过程已创建。SQL> exec test('score','aa','1')
100PL/SQL 过程已成功完成。
upper(var_itabb)!='tphk' and upper(var_itabb)!='wt'
应该这样
upper(var_itabb)!='TPHK' and upper(var_itabb)!='WT'