原创：oracle 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;
/



//声明一个整型的变量
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语法
   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


//查询数据库的数据赋值给你创建的记录表类型
分析一：
SQL> declare
 2     type table_emp is table of emp%rowtype
 3     index by binary_integer;
 4     empt table_emp;
 5     i number(10):=0;
 6 begin
 7 
 8     for rec in (select * from emp) loop
 9       i:=i+1;
 10       empt(i).ename:=rec.ename;
 11      end loop;
 12 
 13     dbms_output.put_line(empt.count);
 14 end;
 15 /

15
具体实现：

//把查询的的数据全部赋值给记录表类型
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
 2     type table_emp is table of emp%rowtype
 3     index by binary_integer;
 4     empt table_emp;
 5     i number(10):=0;
 6     j number(10):=1;
 7 begin
 8 
 9     for rec in (select * from emp) loop
 10       i:=i+1;
 11       empt(i).empno:=rec.empno;
 12       empt(i).ename:=rec.ename;
 13       empt(i).job:=rec.job;
 14       empt(i).mgr:=rec.mgr;
 15       empt(i).hiredate:=rec.hiredate;
 16       empt(i).sal:=rec.sal;
 17       empt(i).comm:=rec.comm;
 18       empt(i).deptno:=rec.deptno;
 19      end loop;
 20 
 21     dbms_output.put_line(empt.count);
 22 
 23     while j<=empt.count loop
 24     dbms_output.put_line(empt(j).empno||'/'||empt(j).ename||'/'||empt
 25 
 26 (j).job||'/'||empt(j).mgr||'/'||empt(j).hiredate||'/'||empt(j).sal||'/'||empt
 27 
 28 (j).comm||'/'||empt(j).deptno);
 29     j:=j+1;
 30    end loop;
 31 end;
 32 /


以上内容归redarmy_chen所有,如需转载请附带出处,如有疑问请发送至redarmy_chen@qq.com