PL/SQL编程

SQL与PL/SQL
SQL语言只是访问、操作数据库的语言，并不是一种具有流程控制的程序设计语言，而只有程序设计语言才能用于应用软件的开发。PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。
PL/SQL是 Procedure Language & Structured Query Language 的缩写。
PL/SQL的优点或特征
(1) 有利于客户/服务器环境应用的运行
对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。
(2) 适合于客户环境
PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
(3) 过程化
PL/SQL是Oracle在标准SQL上的过程性扩展，不仅允许在PL/SQL程序内嵌入SQL语句，而且允许使用各种类型的条件分支语句和循环语句，可以多个应用程序之间共享其解决方案。
(4) 模块化
PL/SQL程序结构是一种描述性很强、界限分明的块结构、嵌套块结构，被分成单独的过程、函数、触发器，且可以把它们组合为程序包，提高程序的模块化能力。
(5) 运行错误的可处理性
使用PL/SQL提供的异常处理（EXCEPTION），开发人员可集中处理各种ORACLE错误和PL/SQL错误，或处理系统错误与自定义错误，以增强应用程序的健壮性。
(6) 提供大量内置程序包
ORACLE提供了大量的内置程序包。通过这些程序包能够实现DBS的一些低层操作、高级功能，不论对DBA还是应用开发人员都具有重要作用。

PL/SQL块

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。
PL/SQL块的结构如下：

DECLARE
–声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数
BEGIN
– 执行部分: 过程及SQL 语句 , 即程序的主要部分
EXCEPTION
– 异常处理部分
END;

标识符
标识符名不能超过30字符；

1.不能用保留字
2.第一个字符必须是字母
3.变量名最多包含30个字符
4.不要与数据库的表或者列同名
5.每一行只能声明一个变量

PL/SQL 变量类型

基本类型 7种

1.binary_integer 整数，主要用来计数(数组下标、循环i)而不是用来表示字段类型 4个字节

2.number 数字类型 //number(7,2) 一共7位，小数点后两位，其余五位

3.char 定长字符串 sql 最长2000 pl/sql 最长32767

4.varchar2 变长字符串 sql 最长4000 pl/sql 最长32767

5.long 长字符串，最长2GB

6.date 日期

7.boolean 布尔类型，可以取值true、false和 null值。

举例：

DECLARE 
      v_binary_integer BINARY_INTEGER :=0;
      v_number NUMBER(7,2):=255.567;
      v_date DATE :=SYSDATE;
      v_pi CONSTANT NUMBER(3,2) :=3.14;

      v_valid BOOLEAN ;

      v_name VARCHAR(20) NOT NULL :='MyName';
BEGIN
      --v_binary_integer
      dbms_output.put_line('v_binary_integer:'||(v_binary_integer+1));
      --v_number
      dbms_output.put_line('v_number:'||(v_number));
      --v_date
       dbms_output.put_line('v_date:'||(v_date));
      --v_pi
      ---
      --v_pi:=3.24;
       dbms_output.put_line('v_pi:'||(v_pi));

      --v_valid 布尔类型有三个值true false null不能直接打印
      --dbms_output.put_line('v_valid:'||(v_valid));
        IF(v_valid=TRUE)THEN
                dbms_output.put_line('v_valid:true');
        ELSIF(v_valid=FALSE)THEN
                dbms_output.put_line('v_valid:false');
        ELSE
                dbms_output.put_line('v_valid:null');
        END IF;

      --v_name
      dbms_output.put_line('v_name:'||(v_name||':Waleee'));

END;

复合类型有四种
(1) 字段类型 %type,好处:便于维护

declare
    --和emp表的empno字段的类型是一样的
    v_empno2 emp.empno%type;
    v_empno3 v_empno2%type;
begin
    dbms_output.put_line('Test');
end;

(2).table 相当于java里的数组 自定义类型

DECLARE 
 --int[]
 TYPE type_table_emp_empno IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;--定义一个数组类型
 --arr[] 
 v_arr type_table_emp_empno;

 BEGIN
     v_arr(1):=4789;
     v_arr(0):=1789;
     v_arr(-1):=3789; 
     dbms_output.put_line(v_arr(-1));
 END;

(3)record 相当于java里的类 自定义类型

 DECLARE
  TYPE type_record_dept IS RECORD(
       m_deptno dept.deptno%TYPE,
       m_dname dept.dname%TYPE,
       m_loc dept.loc%TYPE

  );--声明了一个新的类型

  trd type_record_dept; 
  BEGIN
      trd.m_deptno:=66;
      trd.m_dname:='waleee';
      trd.m_loc:='beijing';

       dbms_output.put_line(trd.m_deptno||  trd.m_dname || trd.m_loc);
  END;

(4)rowtype 记录类型 行类型

DECLARE
 v_emptype emp%ROWTYPE;
BEGIN
 v_emptype.empno:=34;
 dbms_output.put_line(v_emptype.empno||v_emptype.ename);
END;

pl/sql中6类SQL语句都可以放

(1) pl/sql中 select语句的要求:

1.返回必须有且只有一条记录，因为不返回记录没有用处，返回很多时变量装不了。
2.select 语句必须带into

举例

declare
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    select ename,sal into v_ename,v_sal from emp where empno=7369;
    dbms_output.put_line(v_ename||'  '||v_sal);
end;

(2) 放DML、TPL语句

declare
    v_deptno dept.deptno%type:=50;
    v_dname dept.dname%type:='aaaa';
    v_loc dept.loc%type:='bj';
begin
   insert into dept2 values(v_deptno,v_dname,v_loc);
   commit;
end

(3) 放DCL和DDL语句，必须要加execute immediate，如：

begin
    execute immediate 'grant dba to huanghuai';
end;


begin
    execute immediate 'revoke dba from huanghuai';
end;

控制语句
(1) if elsif else

需求：取出7369的薪水，如果<1200,则输出low,如果<2000则输出middle,否则high

declare
    v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno=7369;
    if(v_sal<1200) then
    dbms_output.put_line('low');
    elsif(v_sal<2000) then
    dbms_output.put_line('middle');
    else
    dbms_output.put_line('high');
    end if;
end;

(2) switch

DECLARE
        v_val NUMBER(1):=3;
 BEGIN    
       CASE(v_val)
               WHEN 1 THEN    DBMS_OUTPUT.put_line('one');
               WHEN 2 THEN    DBMS_OUTPUT.put_line('two');
               ELSE
                    DBMS_OUTPUT.put_line('滚');
       END CASE;
 END;

循环
需求:打印1..10
(1).for()

BEGIN

  FOR i IN  1..10 LOOP
      dbms_output.put_line(i);
  END LOOP;
  dbms_output.put_line('----------------------------');
  --REVERSE 表示倒序输出，从10输出到1
  FOR k IN REVERSE 1..10 LOOP
      dbms_output.put_line(k);
  END LOOP;
END;

(2).while()

DECLARE
  i BINARY_INTEGER:=1;
BEGIN
  WHILE(i<=10) LOOP
      dbms_output.put_line(i); 
      i:=i+1;       
  END LOOP;
END;

(3).do while()

DECLARE
    i BINARY_INTEGER:=1;
BEGIN
    LOOP
       dbms_output.put_line(i);
       i:=i+1;
      EXIT WHEN(i>10);
    END LOOP;
END;

预定义的异常处理

预定义说明的部分 ORACLE 异常错误

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反了唯一性限制
ORA-1403	No_data_found	SELECT INTO没有找到数据
ORA-1422	Too_many_rows	SELECT INTO 返回多行
ORA-1476	Zero-divide	试图被零除

错误记录

-------------记录错误的方法

 CREATE TABLE errorlog(
        ID NUMBER PRIMARY KEY,
        username    VARCHAR2(20),
        errorcode   NUMBER,
        errmsg      VARCHAR2(1024),
        errdate     DATE      

 );


 CREATE SEQUENCE seq_errorlog MINVALUE 0 START WITH 0 INCREMENT BY 1;

 DECLARE
        v_errcode NUMBER;
        v_errmsg  VARCHAR2(1024);

 BEGIN
        dbms_output.put_line(2/0);
 EXCEPTION
        WHEN OTHERS THEN
        ROLLBACK;
        v_errcode:=SQLCODE;--出错代码
        v_errmsg:=SQLERRM;--出错信息
        INSERT INTO errorlog VALUES(seq_errorlog.nextval,USER,v_errcode,v_errmsg,SYSDATE);
        COMMIT;
 END;

 SELECT * FROM errorlog

 --问题 11：接受 2 个数相除，并显示结果，如果除数为 0，则显示错误提示;
 DECLARE
       a NUMBER;
       b NUMBER;
 BEGIN
       a:=&a;
       b:=&b;

       dbms_output.put_line(a||'/'||b||'='||a/b);

 EXCEPTION
       WHEN zero_divide THEN
             dbms_output.put_line('伙计，除数不能为0');
 END;