sql-pl/sql

PLSQL

PL/SQL:对SQL语言的扩展，可以对SQL进行编程

单行注释以–
多行注释 /* */,可以跨多行

块结构

declare
	--声明变量、常量、光标、例外
begin
	--程序
exception 
	--例外
end;

DECLARE 声明部分。

这是为游标,数据类型定义,变量,嵌入函数和过程分配内存的位置。

BEGIN 可执行程序部分 以关键字BEGIN开头 并且必须至少有一行可执行代码,即使它是关键字NULL,这意味着没有操作。

EXCEPTION 异常处理部分 以关键字EXCEPTION开头 捕获任何数据库或PL / SQL错误的地方。

END :每个PL / SQL块以关键字END结束。

DECLARE 声明部分 和 EXCEPTION 异常处理部分 可以省略,省略之后叫 匿名块,匿名blick不保存在数据库中。适合创建测试单元。

例子1:hello world

desc dbms_output;  -- 用来打印的程序包

begin
    dbms_output.put_line('hello world');
end;

输出 : PL/SQL 过程已成功完成 , 未打印 ‘hello world’

因为 打印服务默认是关闭的,需要打开 set serveroutput on ;

输出 :
PL/SQL 过程已成功完成。
hello world

变量声明与赋值

声明变量 (变量名 类型) 并赋值 :=

psex char := '男';
pname varchar2(10);

变量名 为了 与其他 关键字重名,前面加个p (pl/sql简称p)

将表中查出来的字段赋值给已声明变量(into)

set serveroutput on ;
declare
    psex char(3) := '男'; -- char默认1个字符 ,但汉字 占3个字节(不同字符编码字节数不同)
    pname varchar2(10);
begin
    select ename into pname from emp where empno = 7499;
    dbms_output.put_line('hello world' || psex || '--------' || pname);
end;

引用表中字段的类型(引用型变量)

这里 pname 我们其实是要 对应 emp表中的 ename,所以二者类型最好保持一致 (长度),所以不能写死

pname emp.ename%type; -- 引用表中字段的类型,而且也推荐这么干

记录型变量(相当于java对象)

可以用于同时保存多个变量值

Person per new Person(1,'zs',28,11...,...);
emp_info emp%rowtype;  -- 将表映射成对象,而且 表中的所有字段全部自动映射到对象的属性中

调用 直接通过 ‘ . ‘ 调用 和java调用属性 一摸一样

declare
    emp_info emp%rowtype;
begin
    select * into emp_info from emp where empno = 7788;
    dbms_output.put_line(emp_info.empno || '----------' || emp_info.ename || '----------'  || emp_info.job);
end;

循环与判断结构

判断

①if
if 条件 then ...
end if ;
②if..else
if 条件 then ...;
else ...
end if ;

③if .. elsif..else...
if 条件 then ...;
elsif 条件 then...
else...
end if ;

示例:

declare 
  num number :=1 ;
begin
 if num=1 then  dbms_output.put_line('输入了1');
  elsif num=2 then dbms_output.put_line('输入了2');
  else dbms_output.put_line('输入了其他');
  end if ; 
end;

循环

① 相当于java的while循环
while 条件
loop
  …
 end loop;
②相当于java的do..while循环
loop 
  …
  exit when 条件;
end loop;
③相当于java的for循环
for  i  in 1 .. 3 
loop 
   …
end loop;

示例:

set serveroutput on;
declare 
begin
    for x in 1..5
    loop
      dbms_output.put_line(x);
    end loop;   
end;

示例:求1到5之和

java代码
int sum = 0;
int i=1;
do{
    sum +=i;
    i++;
}while(i<=5),

pl/sql
set serveroutput on ;
declare
    pnum number := 1;
    psum number :=0;
begin
    loop
        exit when pnum >5;
        psum := psum + pnum;  -- sum+=i;
        pnum := pnum +1;
    end loop ;
    dbms_output.put_line (psum);
end;

游标(光标 Cursor):存储查询到的集合（多行数据）

前面都是 存储一行数据 或 存储多行数据,那存储多行数据怎么办

pename varchar2(20) ;
select  ename into pename from emp ; --错误

存储多行数据–>光标

语法:

cursor 光标名 (参数列表)
is
select …

示例:
用光标存储集合
cursor mycursor is select ename from emp ;
使用:
打开光标 open mycurosr 光标使用前必须打开
获取一行光标的值 fetch mycursor into pename ; — (其中pename类型是emp.ename%type)
关闭光标 close mycursor ;

光标属性
%isopen — 判断光标是否打开
%rowcount — 已从光标中读取的记录数
%found — 判断这行是否有数据
%notfound — 判断这行是否没有数据

示例:查询并打印员工姓名、薪水

set serveroutput on;
declare 
  --定义光标
  cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal emp.sal%type;
begin
  open cemp;
  loop
    fetch cemp into pename,psal; -- 获取当前指向的记录,并将指针下移
    dbms_output.put_line(pename || '的薪水是' || psal);
    --退出条件
    --exit when 没有数据;
    exit when cemp%notfound;
  end loop;
  close cemp;
end;

示例:涨工资 job 为PRESIDENT涨1000 job为MANAGER涨800 其他400

set serveroutput on
declare
  cursor cemp is select empno, job from emp;
  pempno emp.empno%type;
  pjob emp.job %type;
begin
  open cemp;
  loop  
    --取一条记录
    fetch cemp into pempno,pjob;

    --判断职位
    if pjob = 'PRESIDENT'
        then update emp set sal=sal+1000 where empno=pempno;
    elsif pjob = 'MANAGER'
        then update emp set sal=sal+800 where empno=pempno;
    else
        update emp set sal=sal+400 where empno=pempno;
    end if;

    exit when cemp%notfound;

  end loop;
  close cemp;
  commit; -- oracle需要手动提交 --> ACID（Oracle默认级别是 Read Commit,一边不提交、另一边读不到）
  dbms_output.put_line('完成');
end;

示例:带参数的光标 查询某个部门的员工姓名

set serveroutput on
declare
  --定义光标保存某个部门的员工姓名
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin
  open cemp(20);
  loop
    fetch cemp into pename;
    dbms_output.put_line(pename);
    exit when cemp%notfound;
  end loop;
  close cemp;
end;

例外(Java中的异常)

a.系统例外:(空指针、数学异常、越界异常)

No_data_found:没有找到数据
Too_many_rows:数据太多了,保存不下 select..into 匹配多个行,如 select ename into pename from emp;
Zero_Divide:被零除
Value_error:算术或转换错误
Timeout_on_resource:等待资源超时 （分布式数据库）

示例:被0除

set serveroutput on

declare
  pnum number;
begin
  pnum := 1/0;
exception
  when zero_divide then dbms_output.put_line('1:0不能做被除数');
                        dbms_output.put_line('2:0不能做被除数');
  when value_error then dbms_output.put_line('算术或者转换错误');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外');
end;

b.自定义例外

declare
    例外名 exception ;   -- 定义异常
begin
    if … then raise 例外名 ;  -- 抛出异常
    end if;
exception 
    when 例外名 then DBMS_OUTPUT.PUT_LINE('…'); -- 捕获异常
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外');
end;

declare
    myexc exception;  -- 定义异常
    pnum number := 1;
begin
    if pnum = 1 then raise myexc;  -- 抛出异常
    end if;
exception
    when myexc then dbms_output.put_line('自定义例外...'); -- 捕获异常
end;

示例:是否存在50号部门的员工?如果不存在,抛出一个例外。

set serveroutput on;
declare
  cursor cemp is select ename from emp where deptno=50;
  pename emp.ename%type;
  no_emp_found exception;  --自定义例外
begin
  open cemp;
  fetch cemp into pename;  --从光标取一条记录
  if cemp%notfound then
    raise no_emp_found;    --抛出例外
  end if;
  close cemp;
exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('其他例外');
end;