我有一个过程,接受传进来的表名和列名,查询出这个列的最大值.但以下写的SQL语句执行时出错.我用的是oracle9i.
谁能告诉我该怎么做?谢谢!
PROCEDURE SELECT_MAXID (col_name IN VARCHAR2,table_name IN VARCHAR2,seq_max OUT NUMBER)
IS
select_stmt VARCHAR2(200);
BEGIN
select_stmt := 'select max('||col_name||') into seq_max from '||table_name;
EXECUTE IMMEDIATE select_stmt;
EXCEPTION
WHEN NO_DATA_FOUND THEN
seq_max := 1;
WHEN VALUE_ERROR THEN
seq_max := 1;
WHEN OTHERS THEN
seq_max := 1;
END SELECT_MAXID;
谁能告诉我该怎么做?谢谢!
PROCEDURE SELECT_MAXID (col_name IN VARCHAR2,table_name IN VARCHAR2,seq_max OUT NUMBER)
IS
select_stmt VARCHAR2(200);
BEGIN
select_stmt := 'select max('||col_name||') into seq_max from '||table_name;
EXECUTE IMMEDIATE select_stmt;
EXCEPTION
WHEN NO_DATA_FOUND THEN
seq_max := 1;
WHEN VALUE_ERROR THEN
seq_max := 1;
WHEN OTHERS THEN
seq_max := 1;
END SELECT_MAXID;
select_stmt := 'select max('||col_name||') from '||table_name;
EXECUTE IMMEDIATE select_stmt into seq_max ;
select_stmt := 'select max('||col_name||') from '||table_name;
EXECUTE IMMEDIATE select_stmt into seq_max ;
IS
select_stmt VARCHAR2(200);
BEGIN
select_stmt := 'select max('||col_name||') into :seq_max from '||table_name;
EXECUTE IMMEDIATE select_stmt using out seq_max;
EXCEPTION
WHEN NO_DATA_FOUND THEN
seq_max := 1;
WHEN VALUE_ERROR THEN
seq_max := 1;
WHEN OTHERS THEN
seq_max := 1;
END SELECT_MAXID;
is
select_stmt varchar2(200);
begin
select_stmt:='select max('||col_name||') from '||table_name;
EXECUTE IMMEDIATE select_stmt into seq_max;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('no data');
seq_max := 1;
WHEN VALUE_ERROR THEN
seq_max := 1;
dbms_output.put_line('error');
WHEN OTHERS THEN
dbms_output.put_line('others');
seq_max := 1;
END SELECT_MAXID;
select_stmt :='SELECT max('||v_column||') from '||v_table;
OPEN cv FOR select_stmt;
FETCH cv INTO max_id;
CLOSE cv;