Oracle之plsql及游标

1--1、赋值
2    --:= 赋值
3        declare 
4          var_name varchar2(10) :='&请输入名字';--&是一个提示输入的特殊符号，会打开一个输入框
5          var_age number(3) :='&请输入年龄';
6        begin
7          dbms_output.put_line(var_name||'---'||var_age);--输入 ||是连接符号和java中的+一样
8        end;
9
10    --into 赋值
11        declare 
12          var_name varchar2(10);
13          var_age number(3);
14        begin
15          select stuname,age into var_name,var_age from t_student where id = 2;
16          dbms_output.put_line(var_name||'---'||var_age);
17        end;
18
19--2、 特殊类型：
20    --%type 绑定某个表中的特定字段的类型
21      declare
22        v_name emp.ename%type;
23        v_job emp.job%type;
24      begin
25        select ename,job into v_name,v_job from emp where emp.empno=3;
26        dbms_output.put_line(v_name||'---'||v_job); 
27      end;
28
29
30   --%rowtype:行类型和表中的一行对应
31      declare
32        v_emp emp%rowtype; -- v_emp的类型是一个行类型 和emp的一条记录对应
33      begin
34        select * into v_emp from emp where empno=1;
35        dbms_output.put_line(v_emp.ename||' '||v_emp.sal||' '||v_emp.job);
36      end;
37      
38--3、if条件判断
39      if语句
40       语法格式:   if  条件 then
41                   [ elsif 条件 then ]
42                   [ elsif  条件 then]
43                   [else ]
44                    end if;
45                    
46                    
47                    --实例
48                    declare 
49                        v_age number(3) :=&请输入年龄;
50                    begin
51                         if v_age = 18 then 
52                                 dbms_output.put_line('18');
53                         elsif v_age > 18 then 
54                                 dbms_output.put_line('大于18');
55                         else  dbms_output.put_line('小于18');
56                         end if;
57                    end;
58               
59--4、case
60        case when  条件 then
61             when  条件 then
62               else
63             end case;
64             
65             
66             --实例
67             declare 
68                  v_age number(3) :=&请输入年龄;
69             begin
70                  case 
71                        when  v_age = 18  then  dbms_output.put_line('18');--条件可以是一个定值也可以是>或者<
72                        when  v_age = 19  then  dbms_output.put_line('19');
73                        when  v_age = 20  then  dbms_output.put_line('20');
74                        when  v_age = 21  then  dbms_output.put_line('21');
75                        when  v_age = 22  then  dbms_output.put_line('22');
76                        when  v_age > 23 then  dbms_output.put_line('大于23');     
77                   else dbms_output.put_line('不知道');
78                   end case;
79             end;
80
81
82--5、循环
83       --5、1无限循环
84              loop
85                -- 循环体
86                exit when 退出条件;
87              end loop;
88              
89              --实例
90                  declare 
91                     v_num number(3) := 1;
92                  begin
93                    loop
94                        dbms_output.put_line(v_num);
95                        v_num := v_num+1;
96                        exit when v_num > 10;
97                    end loop;
98                  end;
99                    
100         --5、2 while带条件的循环
101             while 循环条件 loop
102                   --循环体
103               end loop;
104               
105              --实例
106               declare
107                 v_i number(5) := 1;
108               begin
109                 while v_i<10 loop
110                   dbms_output.put_line(v_i);
111                   v_i := v_i+1;
112                   end loop;
113               end; 
114               
115            --5/3 for循环:
116                  --1.不用专门去声明循环变量
117                  --2.每次只能自增一，
118                  --3.要想实现循环降序需要在 in 后加 reverse
119
120                  --实例
121                     declare 
122                     begin
123                        for v_i in reverse 1..9 loop
124                          dbms_output.put_line(v_i); 
125                        end loop;
126                     end;
127
128                    
129--6、goto关键字：跳转到指定的位置
130          
131       --实例：
132            declare 
133                  v_num number(5) := &请输入;
134                 
135            begin
136                  if v_num = 18 then
137                     dbms_output.put_line(18);
138                     goto a1;
139                   elsif v_num = 20 then 
140                     dbms_output.put_line(20);
141                     goto a2;
142                   else  dbms_output.put_line('----------');
143                         goto a3;
144                   end if;
145                   
146                   <<a1>>
147                        dbms_output.put_line('a1======');
148                   <<a2>>
149                        dbms_output.put_line('a2======');
150                   <<a3>>
151                        dbms_output.put_line('a3======');
152            end;
153
154--7、动态SQL语句：解决的是字符串格式的sql语句执行的问题
155      EXECUTE IMMEDIATE dynamic_sql_string
156      [INTO  define_variable_list]
157      [USING bind_argument_list];
158      
159             declare
160                 v_stuname t_student.stuname%type;
161                 v_sql varchar2(100) := 'select stuname from t_student where id = :id';
162                 v_id number(3) :=  &请输入查询的id; 
163             begin 
164                execute immediate v_sql into v_stuname using v_id;
165                dbms_output.put_line(v_stuname);
166             end;
167
168--8、异常处理
169             --8、1 系统异常
170             no_data_found;没有找到数据的异常
171             too_many_rows: 多行数据
172             others 其他异常
173             
174             declare 
175                    v_stuname t_student.stuname%type;
176                    v_id t_student.id%type;
177             begin 
178                    -- 业务逻辑代码
179                    v_id:=100;
180                    select stuname into v_stuname from t_student where id = v_id;
181                    dbms_output.put_line(v_stuname);
182                    -- 异常处理代码 
183                    
184                    exception
185                       when no_data_found then
186                            dbms_output.put_line('找不到数据');
187                       when too_many_rows then
188                            dbms_output.put_line('数据过多');
189                       when others then 
190                            dbms_output.put_line('其他异常');
191             end;
192             
193             --8、2 自定义异常
194                   declare
195                            v_i number(3) :=&请输入;
196                            myexception exception; 
197                   begin
198                            if v_i = 2 then 
199                               raise myexception;
200                            end if;
201                            dbms_output.put_line('==================');
202                            
203                            exception
204                              when myexception then 
205                                dbms_output.put_line('自定义异常触发了');
206                              when others then 
207                                dbms_output.put_line('不知道的异常');
208                   end;
209                     
210
211游标：
212    一.隐式游标：系统自动维护的，在我们做DML操作的时候系统会自动的维护这样一个游标
213     名称叫:sql
214           隐式游标提供的常用的属性：
215            sql%found: boolean 找到数据 true
216            sql%notfound: boolean 没有找到数据 true
217            sql%rowcount: 数值型 影响的行数
218            sql%isopen: 是否打开 dml中都是 false
219              插入：select * from student;
220           
221           begin 
222            update t_student set stuname='鲁睿骁' where id = 10;
223            
224            if sql%found then
225              dbms_output.put_line('影响了'|| sql%rowcount ||'行记录');
226            end if; 
227            if sql%isopen then
228               dbms_output.put_line('--------1--------');
229            end if;
230            if sql%notfound then
231            dbms_output.put_line('-----------2-----------'); 
232            end if;
233            
234           end;
235           
236    二.显示游标:处理多行数据，隐式游标配合显示游标使用
237           2.1无参游标
238           查询出学生表中的所有的记录：
239         使用的步骤：
240             1.声明游标
241             2.打开游标
242             3.循环提取数据
243             4.关闭游标
244             
245             declare 
246              v_student t_student%rowtype;
247              --1.声明游标
248              cursor mycursor is select * from t_student;
249              v_count number(3):=0;
250              begin 
251              --2.打开游标
252              open mycursor;
253              --3.循环提取数据
254              
255              loop
256                 --提取数据
257                 -- 每循环一次从游标中取一条记录保存到v_student变量中
258                 fetch mycursor into v_student;
259                 --指定退出条件
260                 exit when mycursor%notfound;
261                 v_count := v_count+1;
262                 dbms_output.put_line(v_student.id||v_student.stuname||v_student.sex);
263              end loop;
264              dbms_output.put_line('有'||v_count||'记录');
265              --4.关闭游标
266             close mycursor;
267              end;  
268              
269              
270             declare
271             v_student t_student%rowtype;
272             cursor mycursor is select * from student for update; -- 1. for update
273          begin
274            open mycursor;
275               loop
276             fetch mycursor into v_student;
277             
278             exit when mycursor%notfound;
279             dbms_output.put_line(v_student.id||v_student.name||v_student.sex||v_student.birth);
280             if v_student.birth is null then
281               -- 2.在更新语句后加 current of 游标名称
282                update t_student set stuname='鲁睿骁' where current of mycursor;
283             end if;  
284             end loop;
285             commit;
286            close mycursor;
287          end; 
288     
289              
290              
291              
292        2.2 有参游标
293          根据姓名查询学生表中的所有的学生信息
294          declare
295           v_student t_student%rowtype;
296           v_name t_student.stuname%type:='&请输入姓名';
297           cursor mycursor(c_name varchar2) 
298              is select * from t_student where stuname like '%'||c_name||'%';
299          begin
300            open mycursor(v_name);
301            loop
302               fetch mycursor into v_student;
303               if mycursor%found then 
304              dbms_output.put_line(v_student.id||'--'||v_student.stuname||'---'||v_student.age);
305               else 
306             exit;
307               end if;
308             end loop; 
309             close mycursor;
310          end;
311          
312          
313          
314           declare
315            v_student t_student%rowtype;
316            v_name t_student.stuname%type := '&请输入要查询的姓名';
317            cursor mycursor -- 带有参数 
318            is select * from t_student where stuname like '%'||v_name||'%'; 
319          begin
320            open mycursor; --打开的时候需要指定参数
321             loop 
322               fetch mycursor into v_student;
323               if mycursor%found then
324                  -- 有数据
325                  dbms_output.put_line(v_student.id||v_student.stuname||v_student.sex);
326               else 
327                  -- 退出
328                  exit;
329               end if;
330             end loop;
331            close mycursor;
332          end;
333
334         2.3 游标循环时使用for循环提取
335
336         declare
337           v_name t_student.stuname%type := '&请输入';
338           cursor mycursor is select * from t_student where stuname like '%'||v_name||'%';
339         begin
340           for v_student in mycursor loop
341               update t_student set age=23;
342              dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex);
343           end loop;
344            commit;
345         end;
346         
347         
348    3.REF游标【动态游标】：是解决游标动态执行sql
349          显示游标在声明的时候就必须制定sql语句
350          动态游标：在打开的时候确定要执行的sql语句比显示游标更加的灵活
351          缺点：不能使用for循环和更新行
352          
353         3.1 自定义ref游标
354             通过REF游标查询出学生表中的所有的学生记录
355            declare 
356              type myreftype is ref cursor;-- 1.定义一个ref 类型
357              myrefcursor myreftype;-- 2.声明一个myreftype类型的变量
358              v_student t_student%rowtype;
359              v_sql varchar2(100);
360            begin
361              v_sql:='select * from t_student';
362              
363               -- for 后及可以带'' 也可以直接是sql语句
364            --open myrefcursor for select * from student; -- 打开游标的同时指定要执行的sql语句
365              open myrefcursor for v_sql;
366             loop
367                fetch myrefcursor into v_student;
368                exit when myrefcursor%notfound;
369                 dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex);
370                
371             end loop;
372              close myrefcursor;
373            end;
374          
375         
376           3.2 sys_refcursor:系统提供的一个 ref cursor 类型
377           
378           declare 
379              myrefcursor sys_refcursor; -- 声明一个变量类型是 refcursor 类型
380              v_student t_student%rowtype;
381              v_sql varchar2(100);
382           begin 
383              v_sql :='select * from t_student';
384              open myrefcursor for v_sql;
385              loop
386             fetch myrefcursor into v_student;
387                exit when myrefcursor%notfound;
388                 dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex);
389                
390              end loop;
391               close myrefcursor;
392           end;