1 create or replace procedure pr_test1 is
2 begin
3
4 if 2 > 1 then
5
6
7 dbms_output.put_line('条件成立');
8
9 elsif 4 > 3 then
10
11 if 7 > 6 then
12
13 dbms_output.put_line('条件不成立1');
14
15 end if;
16
17 elsif 6 > 5 then
18
19 dbms_output.put_line('条件成立2');
20
21 else
22 dbms_output.put_line('条件不成立3');
23
24 end if;
25
26
27
28
29
30
31 end pr_test1;
32
33
34
35 create or replace procedure pr_text2 is
36
37 v_case number(3) := 100;
38
39 begin
40
41 case v_case
42
43 when 1 then
44
45 dbms_output.put_line('条件匹配=1');
46
47 when 100 then
48
49 dbms_output.put_line('条件成匹配=100');
50
51 else
52
53 dbms_output.put_line('条件不匹配');
54
55 end case;
56
57 case
58
59 when 8 > 7 then
60
61 dbms_output.put_line('8>7成立');
62
63 when 9 > 8 then
64
65 dbms_output.put_line('9>8成立');
66
67 else
68
69 dbms_output.put_line('都不成立');
70
71 end case;
72
73 --loop循环
74
75 <<loop1>>
76 loop
77
78 v_case:=v_case-1;
79
80 dbms_output.put_line('v_case='||v_case);
81
82 --if(v_case = 90) then
83
84 --dbms_output.put_line('退出循环');
85
86 exit loop1 when v_case =90;
87
88 --end if;
89
90 end loop;
91
92
93 while v_case >80
94
95 loop
96
97 v_case:=v_case-1;
98
99 dbms_output.put_line('v_case='||v_case);
100
101 --exit when v_case =90;
102
103 end loop;
104
105
106 dbms_output.put_line('v_case='||'--------');
107 for inx in reverse 1..10 loop --1..10(逐渐加1) reverse(从大向小加)
108
109
110 v_case:=v_case+inx;
111
112 dbms_output.put_line('v_case='||v_case);
113
114
115
116 end loop;
117
118
119
120 end pr_text2;
121
122
123
124 create or replace procedure pr_test2(v_nl in varchar2 default '22' ) is
125 begin
126
127 update t_hq_ryxx set ruzrq = sysdate where nianl = v_nl;
128
129 commit;
130
131 end pr_test2;
132
133
134 create or replace procedure pr_test3(v_nl in varchar2,v_xx in out varchar2) is
135 begin
136
137 select xingm into v_xx from t_hq_ryxx where nianl = v_nl and bum = v_xx;
138
139 if sql%found then
140
141
142 dbms_output.put_line('查找到数据了');
143
144
145 else
146
147 dbms_output.put_line('未找到数据');
148
149 end if;
150
151
152
153 exception --异常
154
155
156
157
158 when no_data_found then
159
160 --dbms_output.put_line('未查找到数据');
161 dbms_output.put_line('sqlcode='|| sqlcode);
162 dbms_output.put_line('sqlerrm='|| sqlerrm);
163
164 when others then
165 dbms_output.put_line('查找出错');
166 dbms_output.put_line('sqlcode='|| sqlcode);
167 dbms_output.put_line('sqlerrm='|| sqlerrm);
168
169
170 end pr_test3;
171
172
173
174 create or replace procedure pr_test4(v_nl in varchar2) is
175
176 v_xm t_hq_ryxx.xingm%type;
177
178 begin
179
180 v_xm := '102';
181 pr_test3(v_nl,v_xm);
182
183 dbms_output.put_line('v_xm='|| v_xm);
184
185
186
187 end pr_test4;
188
189
190
191 create or replace procedure pr_test5 is
192 begin
193
194 update t_hq_ryxx set bum= '101' where bum is null;
195
196 commit;
197
198 if sql%rowcount >0 then
199
200 dbms_output.put_line('更新了'|| sql%rowcount || '记录');
201
202 else
203
204 dbms_output.put_line('更新了0条记录');
205
206
207 end if;
208
209 end pr_test5;
