Oracle之plsql及游标

一、赋值
–:= 赋值
declare
var_name varchar2(10) :=’&请输入名字’;–&是一个提示输入的特殊符号，会打开一个输入框
var_age number(3) :=’&请输入年龄’;
begin
dbms_output.put_line(var_name||’—’||var_age);–输入 ||是连接符号和java中的+一样
end;

--into 赋值
    declare 
      var_name varchar2(10);
      var_age number(3);
    begin
      select stuname,age into var_name,var_age from t_student where id = 2;
      dbms_output.put_line(var_name||'---'||var_age);
    end;

二、 特殊类型：
–%type 绑定某个表中的特定字段的类型
declare
v_name emp.ename%type;
v_job emp.job%type;
begin
select ename,job into v_name,v_job from emp where emp.empno=3;
dbms_output.put_line(v_name||’—’||v_job);
end;

–%rowtype:行类型和表中的一行对应
declare
v_emp emp%rowtype; – v_emp的类型是一个行类型 和emp的一条记录对应
begin
select * into v_emp from emp where empno=1;
dbms_output.put_line(v_emp.ename||’ ‘||v_emp.sal||’ ‘||v_emp.job);
end;

三、if条件判断
if语句
语法格式: if 条件 then
[ elsif 条件 then ]
[ elsif 条件 then]
[else ]
end if;

                --实例
                declare 
                    v_age number(3) :=&请输入年龄;
                begin
                     if v_age = 18 then 
                             dbms_output.put_line('18');
                     elsif v_age > 18 then 
                             dbms_output.put_line('大于18');
                     else  dbms_output.put_line('小于18');
                     end if;
                end;

四、case
case when 条件 then
when 条件 then
else
end case;

         --实例
         declare 
              v_age number(3) :=&请输入年龄;
         begin
              case 
                    when  v_age = 18  then  dbms_output.put_line('18');--条件可以是一个定值也可以是>或者<
                    when  v_age = 19  then  dbms_output.put_line('19');
                    when  v_age = 20  then  dbms_output.put_line('20');
                    when  v_age = 21  then  dbms_output.put_line('21');
                    when  v_age = 22  then  dbms_output.put_line('22');
                    when  v_age > 23 then  dbms_output.put_line('大于23');     
               else dbms_output.put_line('不知道');
               end case;
         end;

五、循环
–5、1无限循环
loop
– 循环体
exit when 退出条件;
end loop;

          --实例
              declare 
                 v_num number(3) := 1;
              begin
                loop
                    dbms_output.put_line(v_num);
                    v_num := v_num+1;
                    exit when v_num > 10;
                end loop;
              end;

     --5、2 while带条件的循环
         while 循环条件 loop
               --循环体
           end loop;

          --实例
           declare
             v_i number(5) := 1;
           begin
             while v_i<10 loop
               dbms_output.put_line(v_i);
               v_i := v_i+1;
               end loop;
           end; 

        --5/3 for循环:
              --1.不用专门去声明循环变量
              --2.每次只能自增一，
              --3.要想实现循环降序需要在 in 后加 reverse

              --实例
                 declare 
                 begin
                    for v_i in reverse 1..9 loop
                      dbms_output.put_line(v_i); 
                    end loop;
                 end;

六、goto关键字：跳转到指定的位置

   --实例：
        declare 
              v_num number(5) := &请输入;

        begin
              if v_num = 18 then
                 dbms_output.put_line(18);
                 goto a1;
               elsif v_num = 20 then 
                 dbms_output.put_line(20);
                 goto a2;
               else  dbms_output.put_line('----------');
                     goto a3;
               end if;

               <<a1>>
                    dbms_output.put_line('a1======');
               <<a2>>
                    dbms_output.put_line('a2======');
               <<a3>>
                    dbms_output.put_line('a3======');
        end;

七、动态SQL语句：解决的是字符串格式的sql语句执行的问题
EXECUTE IMMEDIATE dynamic_sql_string
[INTO define_variable_list]
[USING bind_argument_list];

         declare
             v_stuname t_student.stuname%type;
             v_sql varchar2(100) := 'select stuname from t_student where id = :id';
             v_id number(3) :=  &请输入查询的id; 
         begin 
            execute immediate v_sql into v_stuname using v_id;
            dbms_output.put_line(v_stuname);
         end;

八、异常处理
–8、1 系统异常
no_data_found;没有找到数据的异常
too_many_rows: 多行数据
others 其他异常

         declare 
                v_stuname t_student.stuname%type;
                v_id t_student.id%type;
         begin 
                -- 业务逻辑代码
                v_id:=100;
                select stuname into v_stuname from t_student where id = v_id;
                dbms_output.put_line(v_stuname);
                -- 异常处理代码 

                exception
                   when no_data_found then
                        dbms_output.put_line('找不到数据');
                   when too_many_rows then
                        dbms_output.put_line('数据过多');
                   when others then 
                        dbms_output.put_line('其他异常');
         end;

         --8、2 自定义异常
               declare
                        v_i number(3) :=&请输入;
                        myexception exception; 
               begin
                        if v_i = 2 then 
                           raise myexception;
                        end if;
                        dbms_output.put_line('==================');

                        exception
                          when myexception then 
                            dbms_output.put_line('自定义异常触发了');
                          when others then 
                            dbms_output.put_line('不知道的异常');
               end;

游标：
一.隐式游标：系统自动维护的，在我们做DML操作的时候系统会自动的维护这样一个游标
名称叫:sql
隐式游标提供的常用的属性：
sql%found: boolean 找到数据 true
sql%notfound: boolean 没有找到数据 true
sql%rowcount: 数值型 影响的行数
sql%isopen: 是否打开 dml中都是 false
插入：select * from student;

       begin 
        update t_student set stuname='张三' where id = 10;

        if sql%found then
          dbms_output.put_line('影响了'|| sql%rowcount ||'行记录');
        end if; 
        if sql%isopen then
           dbms_output.put_line('--------1--------');
        end if;
        if sql%notfound then
        dbms_output.put_line('-----------2-----------'); 
        end if;

       end;

**二.显示游标:处理多行数据，隐式游标配合显示游标使用**
       2.1无参游标
       查询出学生表中的所有的记录：
     使用的步骤：
         1.声明游标
         2.打开游标
         3.循环提取数据
         4.关闭游标

         declare 
          v_student t_student%rowtype;
          --1.声明游标
          cursor mycursor is select * from t_student;
          v_count number(3):=0;
          begin 
          --2.打开游标
          open mycursor;
          --3.循环提取数据

          loop
             --提取数据
             -- 每循环一次从游标中取一条记录保存到v_student变量中
             fetch mycursor into v_student;
             --指定退出条件
             exit when mycursor%notfound;
             v_count := v_count+1;
             dbms_output.put_line(v_student.id||v_student.stuname||v_student.sex);
          end loop;
          dbms_output.put_line('有'||v_count||'记录');
          --4.关闭游标
         close mycursor;
          end;  


         declare
         v_student t_student%rowtype;
         cursor mycursor is select * from student for update; -- 1. for update
      begin
        open mycursor;
           loop
         fetch mycursor into v_student;

         exit when mycursor%notfound;
         dbms_output.put_line(v_student.id||v_student.name||v_student.sex||v_student.birth);
         if v_student.birth is null then
           -- 2.在更新语句后加 current of 游标名称
            update t_student set stuname='张三' where current of mycursor;
         end if;  
         end loop;
         commit;
        close mycursor;
      end; 




    2.2 有参游标
      根据姓名查询学生表中的所有的学生信息
      declare
       v_student t_student%rowtype;
       v_name t_student.stuname%type:='&请输入姓名';
       cursor mycursor(c_name varchar2) 
          is select * from t_student where stuname like '%'||c_name||'%';
      begin
        open mycursor(v_name);
        loop
           fetch mycursor into v_student;
           if mycursor%found then 
          dbms_output.put_line(v_student.id||'--'||v_student.stuname||'---'||v_student.age);
           else 
         exit;
           end if;
         end loop; 
         close mycursor;
      end;



       declare
        v_student t_student%rowtype;
        v_name t_student.stuname%type := '&请输入要查询的姓名';
        cursor mycursor -- 带有参数 
        is select * from t_student where stuname like '%'||v_name||'%'; 
      begin
        open mycursor; --打开的时候需要指定参数
         loop 
           fetch mycursor into v_student;
           if mycursor%found then
              -- 有数据
              dbms_output.put_line(v_student.id||v_student.stuname||v_student.sex);
           else 
              -- 退出
              exit;
           end if;
         end loop;
        close mycursor;
      end;

     2.3 游标循环时使用for循环提取

     declare
       v_name t_student.stuname%type := '&请输入';
       cursor mycursor is select * from t_student where stuname like '%'||v_name||'%';
     begin
       for v_student in mycursor loop
           update t_student set age=23;
          dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex);
       end loop;
        commit;
     end;


**3.REF游标【动态游标】：是解决游标动态执行sql**
      显示游标在声明的时候就必须制定sql语句
      动态游标：在打开的时候确定要执行的sql语句比显示游标更加的灵活
      缺点：不能使用for循环和更新行

     3.1 自定义ref游标
         通过REF游标查询出学生表中的所有的学生记录
        declare 
          type myreftype is ref cursor;-- 1.定义一个ref 类型
          myrefcursor myreftype;-- 2.声明一个myreftype类型的变量
          v_student t_student%rowtype;
          v_sql varchar2(100);
        begin
          v_sql:='select * from t_student';

           -- for 后及可以带'' 也可以直接是sql语句
        --open myrefcursor for select * from student; -- 打开游标的同时指定要执行的sql语句
          open myrefcursor for v_sql;
         loop
            fetch myrefcursor into v_student;
            exit when myrefcursor%notfound;
             dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex);

         end loop;
          close myrefcursor;
        end;


       3.2 sys_refcursor:系统提供的一个 ref cursor 类型

       declare 
          myrefcursor sys_refcursor; -- 声明一个变量类型是 refcursor 类型
          v_student t_student%rowtype;
          v_sql varchar2(100);
       begin 
          v_sql :='select * from t_student';
          open myrefcursor for v_sql;
          loop
         fetch myrefcursor into v_student;
            exit when myrefcursor%notfound;
             dbms_output.put_line(v_student.age||v_student.stuname||v_student.sex);

          end loop;
           close myrefcursor;
       end;

“