SQL> create table cardacct (cardno varchar2(19),expiredate varchar2(8));
SQL> insert into cardacct values ('0919088800001797244','20130404');
SQL> insert into cardacct values ('0919088800001797253','20130505');
SQL> insert into cardacct values ('0919088800001797262','20130606');
SQL> select * from cardacct; -----------------将下面的 2013 年改成 2014 年即可。
CARDNO EXPIREDA
------------------- --------
0919088800001797244 20130404
0919088800001797253 20130505
0919088800001797262 20130606
(3)代码:
declare
v_cardno cardacct.cardno%type;
v_expiredate cardacct.expiredate%type;
n_cardno cardacct.cardno%type;
n_expiredate cardacct.expiredate%type;
cursor c1 is select cardno,expiredate from cardacct where cardno between '0919088800001797244' and '0919088800001797262';
BEGIN
OPEN c1 ;
LOOP
FETCH c1 INTO v_cardno, v_expiredate;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('old cardno is ' || v_cardno || ' old expiredate is '|| v_expiredate);
n_cardno:=v_cardno;
update cardacct set expiredate=concat(substr(expiredate,1,4)+1,substr(expiredate,5,8)) where cardno=n_cardno;
dbms_output.put_line('new cardno is ' || n_cardno || ' new expiredate is '|| n_expiredate);
END LOOP;
CLOSE c1;
END;
/
(4)打印结果:(第二个 dbms 没有打印更新后的 过期日期。但是数据库中的数据已经修改)
old cardno is 0919088800001797244 old expiredate is 20130404
new cardno is 0919088800001797244 new expiredate is
old cardno is 0919088800001797253 old expiredate is 20130505
new cardno is 0919088800001797253 new expiredate is
old cardno is 0919088800001797262 old expiredate is 20130606
new cardno is 0919088800001797262 new expiredate is------------------------------------ 请问如何才能将更新后的结果也给打印出来呢---------------------------
SQL> insert into cardacct values ('0919088800001797244','20130404');
SQL> insert into cardacct values ('0919088800001797253','20130505');
SQL> insert into cardacct values ('0919088800001797262','20130606');
SQL> select * from cardacct; -----------------将下面的 2013 年改成 2014 年即可。
CARDNO EXPIREDA
------------------- --------
0919088800001797244 20130404
0919088800001797253 20130505
0919088800001797262 20130606
(3)代码:
declare
v_cardno cardacct.cardno%type;
v_expiredate cardacct.expiredate%type;
n_cardno cardacct.cardno%type;
n_expiredate cardacct.expiredate%type;
cursor c1 is select cardno,expiredate from cardacct where cardno between '0919088800001797244' and '0919088800001797262';
BEGIN
OPEN c1 ;
LOOP
FETCH c1 INTO v_cardno, v_expiredate;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('old cardno is ' || v_cardno || ' old expiredate is '|| v_expiredate);
n_cardno:=v_cardno;
update cardacct set expiredate=concat(substr(expiredate,1,4)+1,substr(expiredate,5,8)) where cardno=n_cardno;
dbms_output.put_line('new cardno is ' || n_cardno || ' new expiredate is '|| n_expiredate);
END LOOP;
CLOSE c1;
END;
/
(4)打印结果:(第二个 dbms 没有打印更新后的 过期日期。但是数据库中的数据已经修改)
old cardno is 0919088800001797244 old expiredate is 20130404
new cardno is 0919088800001797244 new expiredate is
old cardno is 0919088800001797253 old expiredate is 20130505
new cardno is 0919088800001797253 new expiredate is
old cardno is 0919088800001797262 old expiredate is 20130606
new cardno is 0919088800001797262 new expiredate is------------------------------------ 请问如何才能将更新后的结果也给打印出来呢---------------------------
解决方案 »
- 统计类sql语句求助
- 对象库(object libraries)不是属于某个form的吗?它和对象组(object groups)有什么区别
- 请问Oracle如何创建表名为User的数据表
- 很郁闷的错!
- EXECUTE IMMEDIATE 'RENAME 旧表名 TO 新表名' 这个语句可以进行事务处理吗?
- 求修改SQL句,提高搜索速度
- Oracle9i无法安装 在线急等
- ora-00248是什么错?
- 请问在oracle中怎么设定周一是1,周日是7?
- 请问那有比较好的,关于ORACEL的资料的网址 !
- ORACLE存储过程中如何调用自己写的存储过程
- 去掉重复行SQL oracle 效率很低,请问高人
v_cardno cardacct.cardno%TYPE;
v_expiredate cardacct.expiredate%TYPE;
n_cardno cardacct.cardno%TYPE;
n_expiredate cardacct.expiredate%TYPE;
CURSOR c1 IS
SELECT cardno, expiredate
FROM cardacct
WHERE cardno BETWEEN '0919088800001797244' AND '0919088800001797262';
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO v_cardno, v_expiredate;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('old cardno is ' || v_cardno ||
' old expiredate is ' || v_expiredate);
n_cardno := v_cardno;
UPDATE cardacct
SET expiredate = concat(substr(expiredate, 1, 4) + 1,
substr(expiredate, 5, 8))
WHERE cardno = n_cardno
RETURNING expiredate INTO n_expiredate;
dbms_output.put_line('new cardno is ' || n_cardno ||
' new expiredate is ' || n_expiredate);
END LOOP;
CLOSE c1;
END;
/
*
ERROR at line 22:
ORA-06550: line 22, column 11:
PLS-00103: Encountered the symbol "EXPIREDATE" when expecting one of the following:
:= . ( @ % ;