oracle pl/sql 编程基础

最新推荐文章于 2023-06-12 08:59:14 发布

蹒跚学步的小菜

最新推荐文章于 2023-06-12 08:59:14 发布

阅读量371

点赞数

分类专栏： Oracle PL/SQL

Oracle PL/SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

PL/SQL是oracle的专用语言,它对标准的SQL语言的扩展.SQL语句可以嵌套在PL/SQL语言中，并结合处理语句。

PL/SQL程序结构
使用了程序块的结构组织的代码.最简单的程序块是一种被称为”匿名块”的程序块,匿名块是指不会被oracle存储并且不能够重用程序块。
PL/SQL程序通常包括3部分:Declare部分、exception部分、Begin和end部分.
Begin和end是PL/SQL的必须部分,总是begin开始end结束。

Declare部分包含定义变量、常量和游标等类型的代码.

Begin与end之间可以嵌套begin与end及exception.

//打开服务器的输出  
SQL> set serveroutput on;  
SQL> begin //开始  
       dbms_output.put_line('Hello World');  
     end;  
/

1、变量与常量
变量与常量的声明：
变量名类型(字符，日期，数字，布尔)[:=缺省值];
常量名 constant 类型(字符，日期，数字，布尔) [:=缺省值];

变量与常量的区别：
变量可以在任何部分进行赋值.
而常量在declare中声明并且赋值.

正确的操作：

declare  
    pagesize constant number(2) :=10;  
begin  
    dbms_output.put_line(pagesize);  
end;  
/

作用域：
是能够引用变量名称这样的标识符的程序块。

declare  
   pagesize number(2):=10; //在整个中都有效  
begin  
   dbms_output.put_line(pagesize);  
   declare  
   pagesize number(2):=99; //只是在内容声明部分到end结束有效  
    begin  
    dbms_output.put_line(pagesize);  
    end;  
end;  
/

//声明一个整型的变量

SQL> declare  
        age number(2);  
     begin  
        age:=90;  
        dbms_output.put_line(age);  
     end;  
/

//声明后直接赋值

SQL> declare  
        age number(2) :=90;  
     begin  
        dbms_output.put_line(age);  
     end;  
/

//捕获异常处理

SQL> declare  
        age number(2);  
     begin  
        age :='qwe';  
     exception  
        when others then  
        dbms_output.put_line('赋值失败');  
     end;  
/

2、条件判断语句
有if与case语句

If语句
If语法

   If（expression1）then pl/sql_statement  
   Else  
        Pl/sql_statement;  
   End if;

   If（expression1）then pl/sql_statement  
   Else if（expression2）  
        Pl/sql_statement;  
   Else  
        Pl/sql_statement;  
   End if;

Case语句
从oracle9i以后引入

     Case <selector>  
        When <expression1> then pl/sql_statement1;  
        When <expression2> then pl/sql_statement2;  
        …….  
        [else pl/sql_statement;]  
     End;

//案例

SQL> declare score number(2) :=80;   
     begin   
          if score>70 then dbms_output.put_line('成绩合格');  
     end if;   
     end;  
/

SQL> declare score number(2) :=80;   
     begin if score>90 then dbms_output.put_line('成绩合格');  
     else dbms_output.put_line('成绩不合格');   
     end if;   
     end;  
/

SQL> declare score number(2):=8;   
     begin   
          case score   
          when 9 then dbms_output.put_line('成绩优秀');   
          when 8 then dbms_output.put_line('成绩亮');  
     end case;  
     end;  
/

3、循环语句
最基本的循环称为无条件循环,如果没有指定exit语句,循环将无条件执行，这种循环称为死循环,死循环尽量避免。

语法格式如下：

 Loop  
       ---statement---  
 Exit when condition  
 End loop;

案例：

SQL> declare   
        i number(2):=1;     
     begin   
        loop   
            dbms_output.put_line(i);  
            i:=i+1;   
        exit when i>10;   
     end loop;   
     end;  
/

While循环
语法：

While condition  
     Loop  
         Statement;  
End loop;

案例：

SQL> declare   
     i number(2):=1;   
     begin   
         while i<10     
            loop dbms_output.put_line(i);   
                i:=i+1;   
         end loop;   
     end;  
 /

For循环
语法：

  For loop_control_variable in [reverse] lower upper loop  
         Statement;  
  End loop;

案例：

SQL> begin  
         for i in 1..10   
            loop   
            dbms_output.put_line(i);   
          end loop;  
     end;  
     /

PL/SQL语句中的SQL语句

declare  
   countnum number(2); //声明变量  
begin  
   //into的作用把从数据中检测出的数据赋值给哪个变量  
   select count(*) into countnum from emp; 
   dbms_output.put_line(countnum);  
end;  
/

注意：如果在select中没有into语句会出现bug

declare  
   empno number(4);  
   ename varchar2(10);  
   job varchar2(9);  
begin  
   select empno,ename,job into empno,ename,job from emp;  
   dbms_output.put_line(empno||'/'||ename||'/'||job);  
end;  
/

以上代码中会出现bug bug原因是因为定义的变量只可以存储一个单独的值,而你返回的是多个值.所以应该为如下：

SQL> declare  
     empno number(4);  
     ename varchar2(10);  
     job varchar2(9);  
 begin  
     select empno,ename,job into empno,ename,job from emp where empno='7369'; //在这加上where条件了  
     dbms_output.put_line(empno||'/'||ename||'/'||job);  
 end;  
 /

用%TYPE和%ROWTYPE类型的变量
在定义变量时处理可以使用oracle规定的数据类型外,还可以使用%TYPE和%ROWTYPE来定义变量.
%TYPE类型的变量是专门为存储在数据列中检索到的值而创建的.对于使用%TYPE创建的变量，其数据类型由系统根据检索的数据列的数据类型决定.
%ROWTYPE类型的变量,它可以一次存储从数据库检索的一行数据。

当你不知道表中的数据类型是什么时候?那你就考虑%TYPE来解决此问题。

SQL> declare  
         empno emp.empno%TYPE; //声明的时候一定要加上表的中列  
         ename emp.ename%TYPE;  
         job   emp.job%TYPE;  
     begin  
          select empno,ename,job into empno,ename,job from emp where empno='7369';  
          dbms_output.put_line(empno||'/'||ename||'/'||job);  
     end;  
 /

emp.empno%TYPE;解析：首先它到emp表中去查找empno列 %TYPE返回其数据的数据类型。

%TYPE的好处：
1、可移植性高(当我们对表的数据类型发生改变时,用户不必考虑定义变量类型)
2、用户不必查看数据类型就可以定义变量能够存储检索的数据。

%ROWTYPE

 SQL> declare  
      row_data emp%ROWTYPE;  
      begin  
         select * into row_data from emp where empno='7369';  
         dbms_output.put_line(row_data.empno||'/'||row_data.ename||'/'||row_data.job);  
      end;  
 /

注意：如果定义了％rowtype类型的变量,该变量的结构与定义表的结构完全相同,查询时必须使用*或者列举全部的字段信息。

4、复合变量

复合变量可以将不同的数据类型的多个值存储在一个单元中.由于复合数据类型可以有用户根据需要定义其结构,所以复合数据类型也称为自定义数据类型。
PL/SQL提供了两种类型的复合数据类型:
1、记录类型
在记录类型中可以存储多个标量值,与数据库中的行相似
2、记录表类型
允许用户在程序代码中使用”表”,以便存储多个行的数据。它只在程序运行期间有效。类似于程序代码中集合|数组。

记录类型：
必须使用TYPE语句定义记录类型的语法声明如下：

TYPE record_name is record(  
Field_name data_type[not null， :=default value]  
………  
)；

与%Rowtype不同之处是
1、%rowtype查询的是全部数据
2、记录类型必须使用type语法格式声明

//案例

SQL> declare  
        type empinfo is record( //声明一个记录类型  
          empno number(4),  
          ename varchar2(10),  
          job varchar2(9)  
        );  
        emp_data empinfo; //声明一个记录类型的变量  
     begin  
         select empno,ename,job into emp_data from emp where empno=7369; //查询的结果赋值给定义的变量  
         dbms_output.put_line(emp_data.empno||'/'||emp_data.ename||'/'||emp_data.job);  
     end;  
/

记录表类型
它可以处理多个记录或多个行记录。
1、为什么使用记录表类型呢?
因为我们查询的数据的往往需要返回多行记录,所以需要记录表类型。

2、定义记录表类型

TYPE table_name is table of data_type[not null]  
     Index by binary_integer;//主键的索引

declare  
   type table_emp is table of emp%rowtype //创建一个表 此表的类型与emp表的类型一致  
   index by binary_integer;  

   type table_text is table of varchar2(20) //创建一个表 此表具有一个varchar2列的简单表  
   index by binary_integer;  

   empt table_emp; //声明记录表类型的变量  
   tabtext table_text;  
begin

案例：

SQL> declare  
       type table_emp is table of emp%rowtype  
       index by binary_integer;  
       empt table_emp;  
     begin  
       empt(1).ename:='wangyi';  
       dbms_output.put_line(empt(1).ename);  
     end;  
    /

//返回总记录

SQL> declare  
       type table_emp is table of emp%rowtype  
       index by binary_integer;  
       empt table_emp;  
     begin  
       dbms_output.put_line(empt.count);  
     end;  
     /

0//没有记录

//删除的操作
表名.Delete(记录数);

//检索记录变量
First：获取第一个的索引
Next：下一个的索引但是必须有参数
Last：最后一个的索引

SQL> declare  
        type table_emp is table of emp%rowtype  
        index by binary_integer;  
        empt table_emp;  
        i number(2):=1;  
     begin  
        while i<10  
        loop  
           empt(i).ename:='wangyi';  
           i:=i+1;  
        end loop;  
        dbms_output.put_line(empt.count);  
        empt.delete(2);  
        dbms_output.put_line(empt.count);  
        dbms_output.put_line(empt.first);  
        dbms_output.put_line(empt.next(2));  
        dbms_output.put_line(empt.last);  
     end;  
    /

9
8
1
3
9

//查询数据库的数据赋值给你创建的记录表类型
分析一：

SQL> declare  
        type table_emp is table of emp%rowtype  
        index by binary_integer;  
        empt table_emp;  
        i number(10):=0;  
     begin  
        for rec in (select * from emp) loop  
           i:=i+1;  
           empt(i).ename:=rec.ename;  
        end loop;  
        dbms_output.put_line(empt.count);  
     end;  
     /

具体实现：
//把查询的的数据全部赋值给记录表类型

declare  
   type table_emp is table of emp%rowtype  
   index by binary_integer;  
   empt table_emp;  
   i number(10):=0;  
   j number(10):=1;  
begin  

   for rec in (select * from emp) loop  
     i:=i+1;  
     empt(i).empno:=rec.empno;  
     empt(i).ename:=rec.ename;  
     empt(i).job:=rec.job;  
     empt(i).mgr:=rec.mgr;  
     empt(i).hiredate:=rec.hiredate;  
     empt(i).sal:=rec.sal;  
     empt(i).comm:=rec.comm;  
     empt(i).deptno:=rec.deptno;  
    end loop;  

   while j<=empt.count loop  
   dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt  
(j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt  
(j).comm||'/'||empt(j).deptno);  
   j:=j+1;  
 end loop;  
end;  
/

SQL> declare  
       type table_emp is table of emp%rowtype  
       index by binary_integer;  
       empt table_emp;  
       i number(10):=0;  
       j number(10):=1;  
   begin  

       for rec in (select * from emp) loop  
          i:=i+1;  
          empt(i).empno:=rec.empno;  
          empt(i).ename:=rec.ename;  
          empt(i).job:=rec.job;  
          empt(i).mgr:=rec.mgr;  
          empt(i).hiredate:=rec.hiredate;  
          empt(i).sal:=rec.sal;  
          empt(i).comm:=rec.comm;  
          empt(i).deptno:=rec.deptno;  
         end loop;  

        dbms_output.put_line(empt.count);  

        while j<=empt.count loop  
        dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt(j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt  (j).comm||'/'||empt(j).deptno);  
        j:=j+1;  
       end loop;  
    end;  
   /