Home > Database > Mysql Tutorial > body text

ORACLE PL/SQL基础部分

WBOY
Release: 2016-06-07 15:20:46
Original
928 people have browsed it

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

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     dbms_output.put_line(i||'的平方和为:'||i*i);
    i:=i+1;
    end loop;
    end;
/
--使用while循环向表中插入30条记录  
declare
  i number :=1;
  begin
    while i       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
  >
  for i in reverse 1..3 loop
    >
    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;

 

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template