oracle总结之十———PL/SQL编程

PL/SQL编程

   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 //开始

  2  dbms_output.put_line('Hello World');

  3  end;

  4  /

变量与常量的声明：

     变量名  类型(字符，日期，数字，布尔)[:=缺省值];

     常量名 constant 类型(字符，日期，数字，布尔) [:=缺省值]; 

变量与常量的区别： 

     变量可以在任何部分进行赋值.

     而常量在declare中声明并且赋值.

注意：都是在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;

 输出结果为：

10

10

//声明一个整型的变量

SQL> declare

  2   age number(2);

  3  begin

  4  age:=90;

  5  dbms_output.put_line(age);

  6  end;

  7  /

//声明后直接赋值 

SQL> declare

  2   age number(2) :=90;

  3  begin

  4  dbms_output.put_line(age);

  5  end;

  6  /

//捕获异常处理

SQL> declare

  2  age number(2);

  3  begin

  4  age :='qwe'

  5  ;

  6  exception

  7  when others then

  8  dbms_output.put_line('赋值失败');

  9  end;

 10  /

条件判断语句

  有if与case语句

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;

 /

输出结果为：

成绩合格

循环语句

  最基本的循环称为无条件循环,如果没有指定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

   select count(*) into countnum from emp; //into的作用把从数据中检测出的数据赋值给哪个变量 

   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

  2     empno number(4);

  3     ename varchar2(10);

  4     job varchar2(9);

  5  begin

  6     select empno,ename,job into empno,ename,job from emp where empno='7369'; //在这加上where条件了

  7     dbms_output.put_line(empno||'/'||ename||'/'||job);

  8  end;

  9  /

 

7369/SMITH/CLERK

是用%TYPE和%ROWTYPE类型的变量

      在定义变量时处理可以使用oracle规定的数据类型外,还可以使用%TYPE和%ROWTYPE来定义变量.

     %TYPE类型的变量是专门为存储在数据列中检索到的值而创建的.对于使用%TYPE创建的变量，其数据类型由系统根据检索的数据列的数据类型决定.

     %ROWTYPE类型的变量,它可以一次存储从数据库检索的一行数据。

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

SQL> declare

  2     empno emp.empno%TYPE; //声明的时候一定要加上表的中列

  3     ename emp.ename%TYPE;

  4     job   emp.job%TYPE;

  5  begin

  6     select empno,ename,job into empno,ename,job from emp where empno='7369';

  7     dbms_output.put_line(empno||'/'||ename||'/'||job);

  8  end;

  9  /

7369/SMITH/CLERK

emp.empno%TYPE;

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

%TYPE的好处：

1、 可移植性高(当我们对表的数据类型发生改变时,用户不必考虑定义变量类型)

2、 用户不必查看数据类型就可以定义变量能够存储检索的数据。

%ROWTYPE

   SQL> declare

  2      row_data emp%ROWTYPE;

  3  begin

  4     select * into row_data from emp where empno='7369';

  5     dbms_output.put_line(row_data.empno||'/'||row_data.ename||'/'||row_data.job);

  6  end;

  7  /

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

复合变量

   复合变量可以将不同的数据类型的多个值存储在一个单元中.由于复合数据类型可以有用户根据需要定义其结构,所以复合数据类型也称为自定义数据类型。

  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

  2     type empinfo is record(  //声明一个记录类型

  3       empno number(4),

  4       ename varchar2(10),

  5       job  varchar2(9)

  6     );

  7  

  8   emp_data  empinfo; //声明一个记录类型的变量

  9  begin

 10    select empno,ename,job into emp_data from emp where empno=7369; //查询的结果赋值给定义的变量

 11    dbms_output.put_line(emp_data.empno||'/'||emp_data.ename||'/'||emp_data.job);

 12  end;

 13  /

记录表类型

   它可以处理多个记录或多个行记录。

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

  2     type table_emp is table of emp%rowtype

  3     index by binary_integer;

  4     empt table_emp;

  5  begin

  6     empt(1).ename:='wangyi';

  7     dbms_output.put_line(empt(1).ename);

  8  end;

  9  /

//返回总记录

SQL> declare

  2     type table_emp is table of emp%rowtype

  3     index by binary_integer;

  4     empt table_emp;

  5  begin

  6     dbms_output.put_line(empt.count);

  7  end;

  8  /

0  //没有记录

//删除的操作

  表名.Delete(记录数);

//检索记录变量

First：获取第一个的索引

Next：下一个的索引 但是必须有参数

Last：最后一个的索引

SQL> declare

  2     type table_emp is table of emp%rowtype

  3     index by binary_integer;

  4     empt table_emp;

  5     i number(2):=1;

  6  begin

  7      while i<10

  8      loop

  9        empt(i).ename:='wangyi';

 10        i:=i+1;

 11      end loop;

 12  

 13     dbms_output.put_line(empt.count);

 14  

 15     empt.delete(2);

 16  

 17     dbms_output.put_line(empt.count);

 18  

 19     dbms_output.put_line(empt.first);

 20     dbms_output.put_line(empt.next(2));

 21     dbms_output.put_line(empt.last);

 22  end;

 23  /

 

9

8

1

3

9