create or replace procedure update_jilh as a1 number(10) := 0; --第1项
an number(10) := 0; --第n项
d number(10):=4 ; --公差
n number(10) := 0; --项数
cursor cur_jilh is
select jilh from a order by jilh;
begin
savepoint ticketcount;
open cur_jilh;
loop
fetch cur_jilh into n;
exit when cur_jilh%notfound; an := a1 + (n - 1)*d;
update a set JILH = jilh + an,biaoz='1' where JILH = n and biaoz='0';
end loop;
close cur_jilh;
commit;EXCEPTION
WHEN OTHERS THEN
rollback to ticketcount;
end update_jilh;
上面的过程只能对表a进行操作。也就是他是一个死过程,如果要对其他表操作的话要更改表名。我想要实现的是这样的过程:(现在这个过程不能执行)
create or replace procedure update_jilh(table_name varchar2)
as
a1 number(10) := 0; --第1项
an number(10) := 0; --第n项
d number(10):=4 ; --公差
n number(10) := 0; --项数
cursor cur_jilh is
select jilh from table_name order by jilh;
begin
savepoint ticketcount;
open cur_jilh;
loop
fetch cur_jilh into n;
exit when cur_jilh%notfound; an := a1 + (n - 1)*d;
update table_name set JILH = jilh + an,biaoz='1' where JILH = n and biaoz='0';
end loop;
close cur_jilh;
commit;EXCEPTION
WHEN OTHERS THEN
rollback to ticketcount;
end update_jilh;
问,我如何用动态游标更改?在网上查了很多资料,用Transact-SQL很容易,但PL/SQL不知道怎么搞啊。多谢各位兄弟了。
an number(10) := 0; --第n项
d number(10):=4 ; --公差
n number(10) := 0; --项数
cursor cur_jilh is
select jilh from a order by jilh;
begin
savepoint ticketcount;
open cur_jilh;
loop
fetch cur_jilh into n;
exit when cur_jilh%notfound; an := a1 + (n - 1)*d;
update a set JILH = jilh + an,biaoz='1' where JILH = n and biaoz='0';
end loop;
close cur_jilh;
commit;EXCEPTION
WHEN OTHERS THEN
rollback to ticketcount;
end update_jilh;
上面的过程只能对表a进行操作。也就是他是一个死过程,如果要对其他表操作的话要更改表名。我想要实现的是这样的过程:(现在这个过程不能执行)
create or replace procedure update_jilh(table_name varchar2)
as
a1 number(10) := 0; --第1项
an number(10) := 0; --第n项
d number(10):=4 ; --公差
n number(10) := 0; --项数
cursor cur_jilh is
select jilh from table_name order by jilh;
begin
savepoint ticketcount;
open cur_jilh;
loop
fetch cur_jilh into n;
exit when cur_jilh%notfound; an := a1 + (n - 1)*d;
update table_name set JILH = jilh + an,biaoz='1' where JILH = n and biaoz='0';
end loop;
close cur_jilh;
commit;EXCEPTION
WHEN OTHERS THEN
rollback to ticketcount;
end update_jilh;
问,我如何用动态游标更改?在网上查了很多资料,用Transact-SQL很容易,但PL/SQL不知道怎么搞啊。多谢各位兄弟了。
STRSQL VARCHAR2(1000);
TYPE MYRCTYPE IS REF CURSOR;
rc myrctype;
begin
STRSQL:= 'select * from ' || strTableName; DBMS_OUTPUT.PUT_LINE(STRSQL);
OPEN RC FOR STRSQL;
DBMS_OUTPUT.PUT_LINE(STRSQL);
CLOSE rc;
end mytest;
create or replace procedure update_jilh(tn varchar2) as a1 number(10) := 0; --第1项
an number(10) := 0; --第n项
d number(10):=4 ; --公差
n number(10) := 0; --项数
sql_statement varchar2(1000);
type curtype is ref cursor;
cur_jilh curtype;
begin
savepoint ticketcount;
sql_statement:='select jilh from '|| tn ||' order by jilh';
open cur_jilh for sql_statement;
loop
fetch cur_jilh into n;
exit when cur_jilh%notfound; an := a1 + (n - 1)*d;
execute immediate 'update '||tn||' set JILH = jilh + :1,biaoz=1 where JILH = :2 and biaoz=0 '
using an,n;
end loop;
close cur_jilh;
commit;EXCEPTION
WHEN OTHERS THEN
rollback to ticketcount;
end update_jilh;