pl/sql编程笔记

-- 完整语句
[DECLARE]
  -- 变量声明区
BEGIN
  -- 代码区
[EXCEPTION]
  -- 异常处理区
END;


-- 最基本
begin
  dbms_output.put_line('hello, world');
end;

-- 顺序执行
begin
  dbms_output.put('hello, ');
  dbms_output.put_line('world');
end;

-- 用 declare 声明变量
-- 变量必须要跟着类型
declare
  name varchar2(20);
  age number;
begin
  -- 变量赋值
  name := 'Tom';
  age := 12;

  dbms_output.put_line('您好，' || name);
  dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
end;


-- 可以带异常处理
declare
  name varchar2(20);
  age number;
  result number;
begin
  name := 'Tom';
  age := 12;

  result := 10/0;

  dbms_output.put_line('您好，' || name);
  dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
exception
  when others then
    dbms_output.put_line(name || ', 你算错了，除数不能为 0');
end;


-- 将异常信息保存到表中
create table mylog (msg varchar2(100), createdate date default sysdate);
select * from mylog;
declare
  name varchar2(20);
  age number;
  result number;
  errmsg varchar2(40);
begin
  name := 'Tom';
  age := 12;

  result := 10/0;

  dbms_output.put_line('您好，' || name);
  dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
exception
  when zero_divide then
    errmsg := sqlerrm;
    dbms_output.put_line(name || ', 你算错了，除数不能为 0');
    insert into mylog (msg) values ('TOM:' || errmsg);
    commit;
end;


-- 可以给变量赋予默认值，:= 或 default
-- 可以通过 constant 设定常量，即不可更改的量
-- 异常处理区，可以处理多个异常
declare
  name CONSTANT varchar2(20) := 'CAT';
  age number default 15;
begin
  dbms_output.put_line('您好，' || name);
  dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
exception
  when zero_divide then
    dbms_output.put_line('aaaaa');
  when others then
    dbms_output.put_line('bbbbbbb');
end;



-- 单独为某一段代码处理异常
DECLARE
  vemp emp%rowtype;
BEGIN
  BEGIN
    select * into vemp from emp where ename = 'KINGs';
  EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('ccc');
  END;

  dbms_output.put_line('bbb');
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('aaa');
END;

9.2 类型与赋值

DECLARE
  -- Scalar 类型
  a number;
  b varchar2(20); -- 4000
  c date;
  d clob;

  -- 属性类型
  vempno emp.empno%type;
  vsal emp.sal%type default 111;

BEGIN
  dbms_output.put_line('1: ' || vsal);
  -- 第一种赋值方式
  vsal := 222;
  dbms_output.put_line('2: ' || vsal);
  -- 第二种赋值方式
  select sal into vsal from emp where ename = 'KING';
  dbms_output.put_line('3: ' || vsal);
  -- 第三种赋值方式
  update emp set sal = 444 where ename = 'KING' returning sal + nvl(comm, 0), empno into vsal, vempno;
  dbms_output.put_line('4: ' || vsal);
  dbms_output.put_line('5: ' || vempno);
  rollback;
  -- 第四种赋值方式(fetch into, 游标)
END;



-- rowtype
-- 可以进一步简化赋值
DECLARE
  vemp emp%rowtype;
BEGIN
  select * into vemp from emp where ename = 'KING';
  dbms_output.put_line(vemp.empno || ':' || vemp.ename || ':' || vemp.sal || '/' || vemp.deptno);
END;

9.3 控制流程

if ... then ... else ... end if;
case ... when ... then ... when ... then ... else ... end case;
loop ... end loop;
for ... in x..y loop ... end loop;
while ... loop ... end loop;

-- 向数据库中插入批量数据示例
-- 用到了 for in 循环 和 if else 判断
create table haha (id int primary key, name varchar2(20));
create sequence seq_haha;
-- pl/sql
DECLARE
  begin_time timestamp;
BEGIN 
  begin_time := systimestamp;

  FOR i IN 1..100003 LOOP
    insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
    if mod(i, 1000) = 0 then
      commit;
    elsif i = 100003 then
      dbms_output.put_line('总共花费了' || (systimestamp - begin_time) || '时间');
      commit;
    end if;
  END LOOP;
END;



-- 以下三种方式是等效的
-- 1. for in
begin
  for i in 1..100000 loop
    insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
  end loop;
  commit;
end;
-- 2. while
declare
  n int := 100001;
begin
  while n > 1 loop
    insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
    n := n - 1;
  end loop;
  commit;
end;
-- 3. loop exit
declare
  n int := 1;
begin
  loop
    insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
    n := n + 1;
    exit when n > 100000;
  end loop;
  commit;
end;

9.4 游标

-- 隐式游标
begin
  delete from emp where sal > 9500;
  -- sql%isopen
  -- sql%found
  -- sql%notfound
  -- sql%rowcount
  if sql%found then
    dbms_output.put_line('you');
  else
    dbms_output.put_line('wu');
  end if;
  rollback;
end;


-- 如果结果集有多条数据，隐式游标会报错
-- 需要使用显式游标
declare
  vemp emp%rowtype;
begin
  select * into vemp from emp where sal > 3000;
  dbms_output.put_line(vemp.ename);
end;

-- 游标的使用，分下面几步
-- 1. 定义
-- 2. 打开
-- 3. 获取数据（循环）
-- 4. 关闭
declare
  vemp emp%rowtype;
  -- 定义游标
  cursor c_emp
    is select * from emp where sal > 2000;
begin
  -- 打开游标
  open c_emp;
  -- 提取游标当前数据

  fetch c_emp into vemp;
  loop
    dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
    fetch c_emp into vemp;
    exit when c_emp%notfound;
  end loop;

  -- 关闭游标
  close c_emp;
end;


-- for in 循环会自动维护游标的打开与关闭
declare
  cursor c_emp
    is select * from emp where sal > 3000;
begin
  for vemp in c_emp loop
    dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
  end loop;
end;

-- 甚至可以更简
begin
  for vemp in (select * from emp where mgr = 7698) loop
    dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
  end loop;
end;


-- 游标可以带参数
declare
  cursor c_emp(s number)
    is select * from emp where sal > s;
begin
  for vemp in c_emp(&sal) loop
    dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
  end loop;
end;


-- 动态游标
-- 略

9.5 动态语句

两种方式：

1. execute immediate
2. dbms_sql package

DECLARE
  name varchar2(50);
  sal number;
BEGIN
  -- 字符串
  name := upper('&ename');
  -- 动态执行
  execute immediate 'select sal+nvl(comm,0) from emp where ename =''' || name || '''' into sal;
  dbms_output.put_line(name || '的工资是' || sal);
END;

9.6 异常处理

异常分两部分：

1. 异常编号(sqlcode)
2. 异常描述(sqlerrm)，可选，有的异常只有编号但没有描述

分两种：

1. 系统异常，比如 data_not_found, zero_devide 等异常。
2. 用户自定义异常

   DECLARE
     myinput number;
     -- 声明异常
     myexception exception;
   BEGIN
     myinput := &mynum;
     if myinput > 10 then
       dbms_output.put_line('OK');
     elsif myinput >= 0 then
       -- 主动抛出异常
       raise myexception;
     else
       -- 主动抛出异常, 是 raise 语句的封装
       raise_application_error(-20002, '数字必须要大于0');
     end if;
   EXCEPTION
     -- 捕获异常
     when myexception then
       dbms_output.put_line('数字太小');
   END;
   

9.7 Procedure

有了名字的 pl/sql 块，可以反复使用。

CREATE OR REPLACE PROCEDURE ptest1 (myinput number)
AS
  -- 声明异常
  myexception exception;
BEGIN
  if myinput > 10 then
    dbms_output.put_line('OK');
  elsif myinput >= 0 then
    -- 主动抛出异常
    raise myexception;
  else
    -- 主动抛出异常, 是 raise 语句的封装
    raise_application_error(-20002, '数字必须要大于0');
  end if;
EXCEPTION
  -- 捕获异常
  when myexception then
    dbms_output.put_line('数字太小');
END;


-- 存储过程的参数类型
create or replace procedure pxxx  (name in varchar2,  -- 不能指定长度
                                   s out number, c out number)
is
begin
  select sal, nvl(comm, 0) into s, c from emp where ename = name;
end;

-- 调用过程
declare
  s number;
  c number;
begin 
  pxxx('KING', s, c);
  dbms_output.put_line(s || '----' || c);
end;

9.8 Function

函数跟过程的区别在于，它有一个明显的返回值，可以在 sql 语句中直接调用。

-----------------------------------------
-- 定义一个函数，从 emp 中查询某人的工资 --
-----------------------------------------
create or replace function pyyy(name in varchar2) return number is
  r number;
begin
  select sal + nvl(comm, 0) into r from emp where ename = name;
  return r;
end;

-- 函数的调用
select pyyy('CLARK') from dual;
select * from emp where sal + nvl(comm, 0) >= pyyy('KING');



-------------------------
-- 定义一个函数，求平方 --
-------------------------
create or replace function mypower(input number) return number is
begin
  return input * input;
end;

-- 函数的调用
select mypower(444) from dual;
select power(444, 2) from dual;



-------------------------
-- 定义一个函数，求明天 --
-------------------------
create or replace function my_next_day return date is
begin
  return sysdate + 1;
end;

-- 函数的使用
select my_next_day from dual;