ORACLE PL/SQL基础部分-事例

最新推荐文章于 2021-04-13 19:46:41 发布

x1n1010

最新推荐文章于 2021-04-13 19:46:41 发布

阅读量514

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/x1n1010/article/details/12999703

版权

Oracle 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

declare
/*定义SQL中使用的变量*/
v_cid number;
v_cname varchar2(10);
v_count number;
begin
select cid,cname into v_cid,v_cname from classes where cid=2;
if(v_cname='9999') then
select count(cname) into v_count from classes where cname='9999';
dbms_output.put_line('cname为9999的记录为：' || v_count);
end if;
end;
/

declare
/*定义SQL中使用的变量*/
v_cid number;
v_cname varchar2(10);
v_count number;
begin
v_cid:=8;
select cid,cname into v_cid,v_cname from classes where cid=v_cid;
if(v_cname='9999') then
select count(cname) into v_count from classes where cname='9999';
dbms_output.put_line('cname为9999的记录为：' || v_count);
end if;
exception
when no_data_found then
dbms_output.put_line('数据不存在');
end;
/

--定义一个只包含执行部分和结束部分的PL/SQL块
begin
Dbms_Output.put_line('Hello World');
end;
/

--定义包含子块的PL/SQL块
declare
v_cid number;
v_cname varchar2(10);
v_count number;
begin
begin
v_cid:=2;
select cname into v_cname from classes where cid=v_cid;
dbms_output.put_line('子块中查出来cid=2的cname:'||v_cname);
end;
select cname into v_cname from classes where cid=3;
dbms_output.put_line('外层查询出来cid=3的cname:'||v_cname);
end;
/

---------------------------------------------------------
--使用参考类型定义-变量类型（%type）
declare
v_all varchar(10);
v_cid number;
v_cname v_all%type; --使用参考类型定义-变量类型
v_count number;
begin
begin
v_cid:=2;
select cname into v_cname from classes where cid=v_cid;
dbms_output.put_line('子块中查出来cid=2的cname:'||v_cname);
end;
select cname into v_cname from classes where cid=3;
dbms_output.put_line('外层查询出来cid=3的cname:'||v_cname);
end;
/

declare
v_cid number;
v_cname classes.cname%type; --使用参考类型定义变量类型
v_count number;
begin
begin
v_cid:=2;
select cname into v_cname from classes where cid=v_cid;
dbms_output.put_line('子块中查出来cid=2的cname:'||v_cname);
end;
select cname into v_cname from classes where cid=3;
dbms_output.put_line('外层查询出来cid=3的cname:'||v_cname);
end;
/

--使用参考类型定义-变量类型（%rowtype）
declare
v_cid number;
v_classes classes%rowtype; -- v_classes：变量名称；classes表名
v_count number;
begin
begin
select cname into v_classes.cname from classes where cid=2;
dbms_output.put_line('子块中查出来cid=2的cname:'||v_classes.cname);
end;
select cname into v_classes.cname from classes where cid=3;
dbms_output.put_line('外层查询出来cid=3的cname:'||v_classes.cname);
end;
/

declare
c_cid constant number:=3; --声明常量，必须初始化赋值否则会报错。
v_classes classes%rowtype;
v_count number not null :=1; --声明not null变量，必须也为初始化赋值
begin
select cname into v_classes.cname from classes where cid=c_cid;
dbms_output.put_line('外层查询出来cid=3的cname:'||v_classes.cname);
end;
/

declare
cid number :=2; --定义PL/SQL变量时不要跟数据库字段列名相同，否则会很麻烦，比如
begin
delete from studentinfo where cid=cid;
end;
/

--之前studentInfo数据：
SQL> select * from studentinfo;
                                  STUID SNAME                                          CID ADDRESS
--------------------------------------- ---------- --------------------------------------- --------------------
                                      2 zhangsan                                         1 hnzz
                                      3 lisi                                             1 hncs
                                      4 wangwu                                           2 hncd
                                      5 zhaoliu                                          2 hndd
                                      6 zhudao                                           3 hnyy
                                      7 david                                            3 hnyy
6 rows selected

SQL> declare
2 cid number :=2;
3 begin
4 delete from studentinfo where cid=cid;
5 end;
6 /
PL/SQL procedure successfully completed

SQL> select * from studentinfo;
STUID SNAME CID ADDRESS
--------------------------------------- ---------- --------------------------------------- --------------------

SQL>
--以上调用删除的不是单行数据，而是整个数据表。

--IF THEN 单分支语句；
declare
v_cname varchar2(10);
begin
select cname into v_cname from classes where cid=2;
if(v_cname='9999') then
dbms_output.put_line('cid=2的记录cname为：'||v_cname);
end if;
end;
/

--双分支语句；
declare
v_cname varchar2(10);
begin
    select cname into v_cname from classes where cid=3;
    if(v_cname='9999') then
    dbms_output.put_line('cid=2的记录cname为：'||v_cname);
    else
      dbms_output.put_line('执行的是else里面程序'||v_cname);
      end if;
      end;
/

--多条分支语句 if ...then ..elsif...then...else
declare
   v_cname varchar2(10);
   begin
     select cname into v_cname from classes where cid=1;
     if(v_cname='9999') then
     dbms_output.put_line('v_cname=9999打印了。值为:'||v_cname);
     elsif(v_cname='0901') then
     dbms_output.put_line('v_cname=0901打印了。值为：'||v_cname);
     elsif(v_cname='0903') then
     dbms_output.put_line('v_cname=0903打印了。值为：'||v_cname);
     else
       dbms_output.put_line('没有任何值可打...'||v_cname);
     end if;
     end;

--CASE语句
declare
   v_test varchar(1):='f';
   begin
     case v_test
     when 'A' then dbms_output.put_line('输出了A');
     when 'B' then dbms_output.put_line('输出了B');
     when 'C' then dbms_output.put_line('输出了C');
     else dbms_output.put_line('输出了X');
     end case;
     end;

declare
   v_cname classes.cname%type;
   begin
     select cname into v_cname from classes where cid=2;
     case
       when v_cname='9999' then
         dbms_output.put_line('执行了1');
       when v_cname='0901' then
         dbms_output.put_line('执行了2');
       when v_cname='0903' then
         dbms_output.put_line('执行了3');
         else
           dbms_output.put_line('执行了0');
           end case;
           end;
/

--循环结构
declare
   i number:=1;
   begin
     loop
       dbms_output.put_line(i || '的平方数为:' || i*i);
       i:=i+1;
       exit when i>5;
       end loop;
       end;
/

--使用loop循环向表中插入30条记录
declare
i number :=1;
begin
    loop
      insert into classes values(seq_stuid.nextval,'A');
      i:=i+1;
      exit when i>=30;
      end loop;
      end;
/

--使用loop循环&if条件判断语句向表中插入30条记录
declare
i number:=1;
begin
    loop
      insert into classes values(seq_stuid.nextval,'B');
      i:=i+1;
      if i>=30 then
      exit;
      end if;
      end loop;
      end;
/

--while循环
declare
i number :=1;
begin
    while(i<=5) loop
    dbms_output.put_line(i||'的平方和为：'||i*i);
    i:=i+1;
    end loop;
    end;
/
--使用while循环向表中插入30条记录
declare
i number :=1;
begin
    while i<=30 loop
      insert into classes values(seq_stuid.nextval,'C');
      i:=i+1;
      end loop;
      end;
/

--使用for循环
declare
i number :=1;
begin
    for i in 1..30 loop --in 从小到大顺序遍历
      insert into classes values(seq_stuid.nextval,'D');
      end loop;
      end;
/

--终止for循环
declare
i number :=1;
begin
    for v_content in reverse 1..5 loop
      insert into classes values(seq_stuid.nextval,'G');
      if(v_content=3) then exit;
      end if;
      end loop;
      end;
/

--GOTO语句
begin
<<outer>>
for i in reverse 1..3 loop
    <<inner>>
    for j in 1..3 loop
      dbms_output.put_line('i='||i||','||'j='||j);
      end loop;
      end loop;
      end;


--游标定义使用查询一组数据
declare
cursor classes_cursor is select * from classes;
v_cid classes.cid%type;
v_cname classes.cname%type;
begin
    if not classes_cursor%isopen then
      open classes_cursor;
      end if;
      loop
        fetch classes_cursor into v_cid,v_cname;
        exit when classes_cursor%notfound;
        dbms_output.put_line('第'||classes_cursor%rowcount ||'条记录'||'班级id：'||v_cid || '班级编号'||v_cname);
        end loop;
        close classes_cursor;
        end;
/

--对于select定义的游标的每一列，fetch变量列表都应该有一个变量与之对应，而且变量的类型要相同，变量顺序也要一直。

declare
   cursor classes_cursor is select * from classes;
   v_classes classes%rowtype;
   begin
     if not classes_cursor%isopen then
       open classes_cursor;
       end if;
       loop
         fetch classes_cursor into v_classes;
         exit when classes_cursor%notfound;
         dbms_output.put_line('第'||classes_cursor%rowcount ||'条记录'||'班级id：'||v_classes.cid || '班级编号'||v_classes.cname);
         end loop;
         close classes_cursor;
         end;
/
--以上使用%rowtype指定查询结果变量。

--以下使用有表名也可以定义%rowtype类型
declare
   cursor classes_cursor is select * from classes;
   v_classes classes_cursor%rowtype;
   begin
     if not classes_cursor%isopen then
       open classes_cursor;
       end if;
       loop
         fetch classes_cursor into v_classes;
         exit when classes_cursor%notfound;
         dbms_output.put_line('第'||classes_cursor%rowcount ||'条记录'||'班级id：'||v_classes.cid || '班级编号'||v_classes.cname);
         end loop;
         close classes_cursor;
         end;
/

--使用while循环迭代游标处理结果集
declare
   cursor class_cursor is select * from classes;    --class_cursor:游标名，存放游标查询出来所有结果集数
   v_classes class_cursor%rowtype;                  --v_classes：行变量，用于下面取值“.”列明
   begin
     if not class_cursor%isopen then
       open class_cursor;
       end if;
       fetch class_cursor into v_classes;   --和下面相同，因为while循环判断之前先拿到值进行判断是否有值才进行循环
       while class_cursor%found loop
       dbms_output.put_line('第'||class_cursor%rowcount ||'条记录'||'班级id：'||v_classes.cid || '班级编号'||v_classes.cname);
       fetch class_cursor into v_classes;   --和上面相同，这句意思逐一获取到值。
       end loop;
       close class_cursor;
       end;
/
--使用for循环迭代，因为oracle数据库对使用for循环变量检索游标进行了简化，并隐式的定义了一个游标名 %rowtype类型的记录变量，
--把游标所有指向当前数据放入到该变量当中去。游标的打开、数据读取、游标关闭都有oracle数据库自动完成。也就是使用for循环不需要
--也不能使用open语句、fetch、close语句。
declare
cursor class_cursor is select * from classes;
begin
    for v_classes in class_cursor loop
    dbms_output.put_line('第'||class_cursor%rowcount ||'条记录'||'班级id：'||v_classes.cid || '班级编号'||v_classes.cname);
    end loop;
    end;

--使用子查询的for循环实现上面功能；
begin
for v_classes in (select * from classes) loop
dbms_output.put_line('班级id：'||v_classes.cid || '班级编号'||v_classes.cname);
end loop;
end;
/

--隐式游标，ORACLE数据库自动管理，又被成SQL游标
begin
update classes set cname='5555' where cid=100;
if SQL%NOTFOUND THEN
    dbms_output.put_line('没有找到更新记录');
    insert into classes values(100,'5555');
    commit;
    end if;
    end;

begin
update classes set cname='6666' where cid=100;
if SQL%ROWCOUNT = 0 THEN
    dbms_output.put_line('没有找到更新记录');
    insert into classes values(100,'5555');
    commit;
    end if;
    end;