PL/SQL编程

概述

是专用于Oracle服务器，在SQL基础之上，添加了一些过程化控制语句，叫PLSQL 过程化包括有：类型定义，判断，循环，游标，异常或例外处理。。。

[DECLARE]-声明部分，可选BEGIN-执行部分，必须[EXCEPTION]-异常处理部分，可选END

例子

SQL> set serveroutput onSQL> declare    a int:100;    b int:200;    c number;    begin        c:=(a+b)/(a-b)；        dbms_output.put_line(c);    exception        when zero_divide then        dbms_output.put_line('除数不许为零！')    end;

数据类型与定义变量和常量

基本数据类型

• 数据类型
  • NUMBER(存储整数或浮点数)
  • PLS_INTEGER,BINARY_INTEGER(只存储整数)

语法: NUMBER(P,S),P表示精度(有效数据个数),S表示刻度范围(小数点右边小数位的个数)

• 字符类型
  • VARCHAR2:可变长字符串
  • CHAR:指定长度字符串
  • LONG：可变字符串,数据库类型的LONG最大长度可达2GB
  • NCHAR和NVARCHAR2:长度根据各国的字符集类确定
• 日期类型
  • DATE
• 布尔类型
  • BOOLEAN

特殊数据类型

• %TYPE

声明一个与指定列名称相同的数据类型

declare    var_job emp.job%type

• RECORD

由多个值组成的一行数据

set serveroutput ondeclare    type emp_type is record   //声明record类型emp_type    (        var_ename varchar2(20),    //定义字段/成员变量        var_job varchar2(20),        var_sal number    );    empinfo emp_type;    //定义 变量begin    select ename,job,sal    into empinfo    from emp    where empno=7369    dbms_output.put_line('雇员'||enmpinfo.var_ename||'的职务是')

• %ROWTYPE类型

存储从数据表中检索到的一行数据

set serveroutput ondeclare    rowVar_emp emp%rowtype;//定义能够存储emp表中一行数据的变量rowVar_empbegin    select *     into rowVar_emp    from emp    where empno=7369;    dbms_output.put_line();end;

定义变量和常量

• 定义变量

var_countryname varchar2(50):='中国';

• 定义常量

con_day constant integer:=365;

流程控制语句

选择语句

• if...then语句

set serveroutput ondeclare    var_name1 varchar2(50);    var_name2 varchar2(50);begin    var_name1:='East';    var_name2:='xiaoke';    if length(var_name1)<length(var_name2) then        dbms_output.put_line(....);    end if;end

• if...then...else语句

set serveroutput ondelclare    age int:55;begin    if age>=56 then        dbms_output.put_line('您可以申请退休');    else        dbms_output.put_line('您年龄小于56，不可以申请退休');    end if;end;

• if...then...elsif语句

set servetoutput ondeclare    month int:=10;begin    if month>=0 and month<=3 then        dbms_output.put_line('这是春季');    elsif month>=4 and month<=6 then        dbms_output.put_line('这是夏季');    else        dbms_output.put_line('月份不合法');    end if;end;

• case语句

set serveroutput ondeclare    season int:=3;    abountInfo varchar2(50);begin    case season    when 1 then        abountInfo:=season||'季度包括1,2,3月份';    when 2 then        abountInfo:=season||'季度包括4,5,6月份';    else        abountInfo:=season||'季节不合法';    end case;    dbms_output.put_line(aboutInfo);end;

循环语句

• loop语句

ser serveroutput ondeclare    sum_i int:=0;    i int:=0;begin    loop        i:=i+1;        sum_i:=sum_i+i;        exit when i=100;    end loop;    dbms_output.put_line('前100个自然数的和是:'||sum_i);end;

• while语句

set  serveroutput ondeclare    sum_i int:=0;    i int:=0;begin    while i<=99 loop        i:=i+1;        sum_i:=sum_i+i;    end loop;    dbms_output.put_line('前100个自然数的和是:'||sum_i);end;

• for语句

set serveroutput ondeclare    sum_i int:=0;begin    for i in reverse 1..100 loop        if mod(i,2)=0 then            sum_i:=sum_i+i;        end if;    end loop;    dbms_output.put_line('前100个自然数的偶数和是:'||sum_i);end;

PL/SQL游标

使用游标过程

1. 声明游标
2. 打开游标
3. 读取游标
4. 关闭游标

实例

declare—定义游标cursor cemp is select ename,sal from emp;—定义变量vename emp.ename%type;vsal emp.sal%type;begin—打开游标，这时游标位于第一条记录之前open cemp;—循环loop—向下移动游标一次fetch cemp into vename,vsal;—退出循环,当游标下移一次后，找不到记录时，则退出循环exit when cemp%notfound;—输出结果dbms_output.put_line(vename||’————‘||vsal);end loop;—关闭游标close cemp;end;

-使用无参光标cursor，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，其它涨400，要求显示编号，姓名，职位，薪水declarecursor cemp is select empno,ename,job,sal from emp;pempno emp.empno%type;pename emp.ename%type;pjob emp.job%type;psal emp.sal%type;beginopen cemp;loopfetch cemp into pempno,pename,pjob,psal;—循环退出条件一定要写exit when cemp%notfound;if pjob=’ANALYST’ thenupdate emp set sal = sal + 1000 where empno = pempno;elsif pjob=’MANAGER’ thenupdate emp set sal = sal + 800 where empno = pempno;elseupdate emp set sal = sal + 400 where empno = pempno;end if;end loop;commit;close cemp;end;

游标属性

• %found:布尔属性，如果SQL语句至少影响到一行数据，则该属性为true
• %notfound:布尔属性，与%found属性相反
• %rowcount:数字型属性，返回受SQL语句影响的行数
• %isopen:布尔属性，游标是否打开

隐式游标

Oracle自动创建的游标(默认名称为SQL)

set serveroutput onbegin    update emp    set sal=sal*(1+0.2)    where job='SALESMAN'    if sql%notfound then        dbms_output.put_line('没有雇员上调工资');    else        dbms_output.put_line('有'||sql%rowcount||'个雇员上调工资');    end if;end;