Oracle -PLSQL 详解

PLSQL 详解

PL/SQL 是Oracle 对 sql 语言的过程化扩展，

指在sql 命令语言中增加了过程处理语句（如分支、循环等），使sql语言具有过程处理能力

PL/SQL 的程序结构

declare   说明部分（变量说明，光标申明，例外说明）begin    语句序列（DML语句）exception    例外处理语句end;

说明部分

定义变量

基本变量

• 类型：char,varchar2,date,number,boolean,long
• 举例：varl char(15); married boolean:=true; (变量名在前，类型在后)

引用类型变量

• 举例：my_name emp.ename%tyoe;

--引用型变量set serveroutput ondeclare--定义变量--pename varchar2(20);pid a.id%type;pnum a.num%type;beginselect id,num into pid,pnum from a where id='a';--属性DBMS_OUTPUT.PUT_LINE(pid||'id'||pnum);end;/

记录型变量

• 举例：emp_rec emp%rowtype;
• 记录型变量分量的引用： emp_rec.ename:=’ADAMS’;

--打开显示面板set SERVEROUTPUT ONDECLARE--定义记录型变量：注意代表一行emp_rec A%rowtype;--A 表Abegin--得到a id的信息select * into emp_rec from A where id='a';DBMS_OUTPUT.PUT_LINE(emp_rec.id);end;/

IF 语句

IF 条件 THEN 语句1;语句2；END IF;

IF 条件 THEN 语句序列1;ESLE 语句序列2；END IF;

IF 条件 THEN 语句;ELSLF 语句 THEN 语句;ELSE 语句;END IF;

使用实例

/* 判断用户从键盘输入的数字 1.如何使用if语句 2.接收一个键盘输入（字符串）*/set serveroutput on--接受一个键盘输入--num:地址值，含义在该地址上保存了输入的值accept num prompt '请输入一个数字'declare --定义变量保存用户从键盘输入的数字pnum number:=#begin--执行if语句进行条件判断if pnum=0 then SYS.DBMS_OUTPUT.PUT_LINE('你输入数字是0');elsif pnum=1 then SYS.DBMS_OUTPUT.PUT_LINE('你输入数字是1');else SYS.DBMS_OUTPUT.PUT_LINE('你输入的是什么？');end if;end;

循环语句

第一种 while

WHILE total<=2500 LOOP ...END LOOP;

实例代码

--使用while 循环打印数字的1-10set serveroutput nodeclare --定义循环变量pnum number:=1;beginwhile pnum<=10 loop--循环体dbms_output.put_line(pnum);pnum:=pnum+1;end loop;end;

第二种 loop

LoopEXIT [WHEN 条件];...END LOOP;

实例代码

--使用loop 循环打印1-10set serveroutput nodeclare --定义变量pnum number:=1;beginloop-- 退出条件：循环变量大于10exit when pnum>10;--打印该变量的值DBMS_OUTPUT.PUT_LINE(pnum);--循环变量+1pnum:=pnum+1;end loop;end;

第三种 FOR

FOR I IN 1..3 loop语句序列;sdsdfsdafasdfend loop;

实例代码

--使用for循环打印1-10 set serveroutput ondeclare--定义循环变量pnum number:=1;beginfor pnum in 1..10 loop DBMS_OUTPUT.PUT_LINE(pnum);end loop;end;

光标

光标的语法

CURSOR 光标名[(参数名 数据类型，[参数名 数据类型])]IS SELECT 语句；-- 打开光标open c1;-- 关闭光标close c1;-- 取一行光标的值fetch c1 into pename;(取一行到变量中)

fetch 的作用，1、把当前指针指向的记录返回。2、将指针指向下一条记录
实例

--查询并打印员工给的姓名和薪水/*光标的属性，光标的属性共有4个%found   光标取到值为true%notfound 光标取不到值true*/set SERVEROUTPUT ONdeclare--定义一个光标cursor ca is select id,num from a;--为光标定义对应的变量pid a.id%type;pnum a.num%type;begin--打开光标open ca;  loop    fetch ca into pid,pnum;    --    exit when ca%notfound;    DBMS_OUTPUT.PUT_LINE(pid||'的值'||pnum);    end loop;--关闭光标close ca;end;

光标的属性

• %found 光标取到值为true
• %notfound 光标取不到值true
• %isopen 判断光标是否打开
• %rowcount 影响的行数

光标数的限制

默认情况下，oracle 数据库只允许打开300个光标

show parameter cursor –查看光标数

alter system set open_cursors=’400’ scope=’both’
scope 的取值：both memory spfile(数据库需要重启生效)

带参数的光标

CURSOR 光标名[(参数名 数据类型，[参数名 数据类型])]IS SELECT 语句；

实例

--查询某个部门中员工的姓名set serveroutput ondeclare--定义嗲参数的光标cursor ca(d char) is select id from a where id=d;pid a.id%type;begin  open ca('a');  loop    fetch ca into pid;    exit when ca%notfound;    SYS.DBMS_OUTPUT.PUT_LINE(pid);  end loop;  close ca;end;

例外（错误）

例外是程序设计语言提供给的一种功能，用来增强程序的健壮性和容错性。

系统列外

No_data_found (没有找到数据)
实例

--系统例外：no_data_foundset SERVEROUTPUT ONdeclare  pename a.id%type;begin  select id into pename from a where num=5;exception when no_data_found then DBMS_OUTPUT.PUT_LINE('没有这个员工'); when others then SYS.DBMS_OUTPUT.PUT_LINE('其他例外');end;

Too_many_rows (select … into 语句匹配多个行)

Zero_Divide (被零除)

Value_error (算术或转换错误)

Timeout_on_resource (在等待资源时发生超时)

自定义列外

• 定义变量，类型是 exception
• 使用raise抛出自定义列外

--自定义例外：set serveroutput ondeclarecursor ca is select id from a where num=123;pid a.id%type;--自定义例外no_num_found exception;begin  open ca;  fetch ca into pid;  if ca%notfound then   --跑出例外  raise no_num_found;  end if;  close ca;exception  when no_num_found then SYS.DBMS_OUTPUT.PUT_LINE('没有num');  when others then SYS.DBMS_OUTPUT.PUT_LINE('其他例外');end;