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/sqlDECLARE  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 inbegin  for i in 1..100000 loop    insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));  end loop;  commit;end;-- 2. whiledeclare  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 exitdeclare  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)isbegin  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 isbegin  return input * input;end;-- 函数的调用select mypower(444) from dual;select power(444, 2) from dual;--------------------------- 定义一个函数，求明天 ---------------------------create or replace function my_next_day return date isbegin  return sysdate + 1;end;-- 函数的使用select my_next_day from dual;