3,491
社区成员
发帖
与我相关
我的任务
分享
-- 我给你写了个例子,用到了游标、with 、order by 这些关键字 ,你参考一下,有问题再追问
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as oracle@ORALAB
SQL>
SQL> create table test(id int, name varchar(10));
Table created
SQL> begin
2 insert into test values(1,'zhang') ;
3 insert into test values(2,'wang');
4 end ;
5 /
PL/SQL procedure successfully completed
SQL> create table test_bak(id int, name varchar(10));
Table created
SQL> create procedure sp_a
2 is
3 v_id int ;
4 v_name varchar(10);
5
6 cursor ss is select * from (
7 with m as
8 (select * from test)
9 select * from m
10 union all
11 select * from m
12 )
13 order by id;
14 begin
15 open ss ;
16 fetch ss into v_id, v_name ;
17 loop
18 exit when not ss%found ;
19 insert into test_bak values(v_id, v_name) ;
20 fetch ss into v_id, v_name;
21 end loop ;
22
23 end ;
24 /
Procedure created
SQL> call sp_a() ;
Method called
SQL> select * from test ;
ID NAME
--------------------------------------- ----------
1 zhang
2 wang
SQL> select * from test_bak ;
ID NAME
--------------------------------------- ----------
1 zhang
1 zhang
2 wang
2 wang
SQL> drop table test purge ;
Table dropped
SQL> drop table test_bak purge ;
Table dropped
SQL> drop procedure sp_a ;
Procedure dropped
SQL>