Oracle-PLSQL学习

PLSQL

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

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

PL/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 on
declare
--定义变量
--pename varchar2(20);
pid a.id%type;
pnum a.num%type;
begin
select 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 ON
DECLARE
--定义记录型变量：注意代表一行
emp_rec A%rowtype;--A 表A
begin
--得到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-10
set serveroutput no
declare 
--定义循环变量
pnum number:=1;
begin
while pnum<=10 loop
--循环体
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;

第二种 loop

Loop
EXIT [WHEN 条件];
...
END LOOP;

实例代码

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

第三种 FOR

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

实例代码

--使用for循环打印1-10 
set serveroutput on
declare
--定义循环变量
pnum number:=1;
begin
for 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 ON

declare
--定义一个光标
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 on
declare
--定义嗲参数的光标
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_found
set SERVEROUTPUT ON
declare 
 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 on

declare
cursor 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;