SQL> select id,sal,status
2 from test;
ID SAL S
---------- ---------- -
1 3000
2 4000
3 3500
4 4300
SQL> create or replace package pkg_test is
2
3 procedure p1 (p_id in test.id%TYPE);
4 procedure p2 (p_id in test.id%TYPE);
5
6 end pkg_test;
7 /
Package created.
SQL> create or replace package body pkg_test is
2
3 cs_status char(1) := 'N';
4
5 procedure p1 (p_id in test.id%TYPE)
6 is
7 lv_sal test.sal%TYPE;
8 begin
9
10 select sal
11 into lv_sal
12 from test
13 where id = p_id;
14
15 if lv_sal>3800 then
16 cs_status := 'Y';
17 end if;
18
19 pkg_test.p2(p_id);
20
21 exception
22 when others then
23 return;
24 end p1;
25
26 procedure p2 (p_id in test.id%TYPE)
27 is
28 begin
29
30 update test
31 set status = cs_status
32 where id = p_id;
33
34 commit;
35
36 exception
37 when others then
38 rollback;
39 end p2;
40 end pkg_test;
41 /
Package body created.
SQL> select id,sal,status
2 from test;
ID SAL S
---------- ---------- -
1 3000
2 4000
3 3500
4 4300
SQL> exec pkg_test.p1(1);
PL/SQL procedure successfully completed.
SQL> select id,sal,status
2 from test;
ID SAL S
---------- ---------- -
1 3000 N
2 4000
3 3500
4 4300
SQL> exec pkg_test.p1(2);
PL/SQL procedure successfully completed.
SQL> select id,sal,status
2 from test;
ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500
4 4300
SQL> exec pkg_test.p1(3);
PL/SQL procedure successfully completed.
SQL> select id,sal,status
2 from test;
ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500 Y
4 4300
SQL>
**********************************************
SQL> create or replace package body pkg_test is
2
3 cs_status char(1) := 'N';
4
5 procedure p1 (p_id in test.id%TYPE)
6 is
7 lv_sal test.sal%TYPE;
8 begin
9
10 select sal
11 into lv_sal
12 from test
13 where id = p_id;
14
15 if lv_sal>3800 then
16 cs_status := 'Y';
17 end if;
18
19 pkg_test.p2(p_id);
20
21 exception
22 when others then
23 return;
24 end p1;
25
26 procedure p2 (p_id in test.id%TYPE)
27 is
28 begin
29
30 update test
31 set status = cs_status
32 where id = p_id;
33
34 commit;
35 cs_status := 'N'; --reset variable
36
37 exception
38 when others then
39 rollback;
40 end p2;
41 end pkg_test;
42 /
Package body created.
SQL> update test set status=null;
4 rows updated.
SQL> commit;
Commit complete.
SQL> select * from test;
ID SAL S
---------- ---------- -
1 3000
2 4000
3 3500
4 4300
SQL> exec pkg_test.p1(1);
PL/SQL procedure successfully completed.
SQL> select * from test;
ID SAL S
---------- ---------- -
1 3000 N
2 4000
3 3500
4 4300
SQL> exec pkg_test.p1(2);
PL/SQL procedure successfully completed.
SQL> select * from test;
ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500
4 4300
SQL> exec pkg_test.p1(3);
PL/SQL procedure successfully completed.
SQL> select * from test;
ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500 N
4 4300
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/45259/viewspace-134953/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/45259/viewspace-134953/