PL/SQL基础学习笔记

PL SQL是在oracle里面的编程语言，用来写存储过程、触发器、函数等等。
PL SQL语言是SQL的补充。SQL没有分支，没有循环，是第四代编程语言，非过程的，只要求得结果。

PL SQL写程序非常的固定：
[DECLARE]
–声明部分，可选。声明各种变量游标
BEGIN
–执行部分，必须。从这儿开始，程序开始执行
[EXCEPTION]
–异常处理部分，可选。相当于catch到exception时执行的东西
END;
–结束，end后要有分号

–最简单的语句块


begin
  dbms_output.put_line('Hello World!');
end;
/

说明：
1）必须先执行
set serveroutput on;
才能看到输出
2）“/”表示执行，相当于输入了run;

–1、简单的PL/SQL语句块


declare
  v_name varchar2(20);
begin
  v_name := 'myname';
  dbms_output.put_line(v_name);
end;
/

说明：
1）变量名用v_开头
2）声明变量，变量名在前，变量类型在后
3）赋值使用“:=”

–2、语句块的组成


declare
  v_num number := 0;
begin
  v_num := 2/v_num;
  dbms_output.put_line(v_num);
exception
  when others then
  dbms_output.put_line('error');
end;
/

说明：
1）变量在声明时可以赋初值
2）“when others then”表示任何错误都打印error

–3、变量声明


declare
  v_temp number(1);
  v_count binary_integer := 0;
  v_sal number(7, 2) := 4000.00;
  v_date date := sysdate;
  v_pi constant number(3, 2) := 3.14;
  v_valid boolean := false;
  v_name varchar2(20) not null := 'MyName';
begin
  dbms_output.put_line('v_temp value:' || v_temp);
end;
/

变量声明的规则：
1）变量名不能够使用保留字，如from、select等
2）第一个字符必须是字母
3）变量名最多包含30个字符
4）不要与数据库的表或者列同名
5）每一行只能声明一个变量

常用变量类型：
1）binary_integer：整数，主要用来计数而不是用来表示字段类型
2）number：数字类型
3）char：定长字符串
4）varchar2：变长字符串
5）date：日期
6）long：长字符串，最长2GB
7）boolean：布尔类型，可以取值为true、false和null值（建议boolean类型在声明时给初值，否则就是空值）

说明：
1）constant相当于Java里面的final，常量
2）“||”是字符串连接符
3）dbms_output.put_line不能打印布尔类型的值

–4、变量声明，使用%type属性


declare
  v_empno number(4);
  v_empno2 emp.empno%type;
  v_empno3 v_empno2%type;
begin
  dbms_output.put_line('Test');
end;
/

说明：
1）使用%type属性，可以声明一个与指定列名称相同的数据类型
2）v_empno2是emp表的empno字段的类型
3）%type可以用在变量身上，是变量的一种属性，指的是它的数据类型

复合变量table和record
table相当于Java里面的数组
record相当于Java里面的类

–5、Table变量类型


declare
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;
  v_empnos type_table_emp_empno;
begin
  v_empnos(0) := 7369;
  v_empnos(2) := 7839;
  v_empnos(-1) := 9999;
  dbms_output.put_line(v_empnos(-1));
end;
/

说明：
1）type表示定义一种新的类型
2）type_table_emp_empno是定义的数据类型，里面装的是emp.empno%type类型
3）这里先声明了一个新的类型，再用声明的类型去声明变量
4）oracle里面，table类型下标可以取负值

–6、Record变量类型
–Record变量包含了一整条记录


declare
  type type_record_dept is record
  (
    deptno dept.deptno%type,
    dname dept.dname%type,
    loc dept.loc%type
  );
  v_temp type_record_dept;
begin
  v_temp.deptno := 50;
  v_temp.dname := 'aaaa';
  v_temp.loc := 'bj';
  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/

–7、使用%rowtype声明record变量
–%rowtype属性直接声明了一行的数据，当表结构变化时，PL/SQL不用变


declare
  v_temp dept%rowtype;
begin
  v_temp.deptno := 50;
  v_temp.dname := 'aaaa';
  v_temp.loc := 'bj';
  dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/

–8、PL/SQL里的SQL语句（DML语句）
–DML语句是数据操作语言，包括：增、删、改、查
–PL/SQL里使用select语句，必须返回一条记录，并且只能返回一条记录


declare
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  select ename, sal into v_ename, v_sal from emp where empno = 7369;
  dbms_output.put_line(v_ename || ' ' || v_sal);
end;
/

说明：
1）select语句里面必须有一个into关键字

–select语句和%rowtype配合使用


declare
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = 7369;
  dbms_output.put_line(v_emp.ename);
end;
/

–insert语句


declare
  v_deptno dept.deptno%type := 50;
  v_dname dept.dname%type := 'aaaa';
  v_loc dept.loc%type := 'bj';
begin
  insert into dept2 values (v_deptno, v_dname, v_loc);
  commit;
end;
/

说明：
1）insert语句、update语句、delete语句直接拿来用


declare
  v_deptno emp2.deptno%type := 10;
  v_count number;
begin
  --update emp2 set sal = sal/2 where deptno = v_deptno;
  --select deptno into v_deptno from emp2 where empno = 7369;
  select count(*) into v_count from emp2;
  dbms_output.put_line(sql%rowcount || '条记录被影响');
  commit;
end;
/

说明：
1）sql%rowcount表示执行的sql语句影响了多少行，比如update操作可能更新了很多行
2）sql是一个关键字，代表了刚刚执行完的那条sql语句
3）rowcount是sql的属性，影响了多少行
4）select语句也是1条记录被影响，是因为它最后产生了一个值，把值赋给了变量
5）影响多少条记录，一般用于insert、update、delete语句，用在select语句上没有意义

–9、PL/SQL中执行DDL语句
–DDL语句是数据定义语言，对数据库内部对象进行创建、删除、修改的操作


begin
  execute immediate 'create table T(name varchar2(20) default ''aaaa'')';
end;
/

说明：
1）不能在begin里直接写create table什么什么
2）必须加上execute immediate，后面用单引号引起来的一句话
3）aaaa要用单引号引起来，因为create table语句本身在单引号里，所以用两个单引号代表一个单引号
4）execute immediate用来执行动态SQL语句

–10、if语句
–取出7369的薪水，如果<1200，则输出’low’，如果<2000，则输出’middle’，否则’high’


declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno = 7369;
  if (v_sal < 1200) then
    dbms_output.put_line('low');
  elsif (v_sal < 2000) then
    dbms_output.put_line('middle');
  else
    dbms_output.put_line('high');
  end if;
end;
/

说明：
1）if语句的语法注意，elsif的写法，和最后有end if结束。
2）不同语言，语法背后的功能都是一样的，短时间内学习一门新语法，项目做完后忘掉。

练习：
–取7839这个人的薪水，如果小于2500就乘以2，如果大于2500就除以2，如果等于2500就输出


declare
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno = 7839;
  if (v_sal < 2500) then
    v_sal := v_sal * 2;
    dbms_output.put_line('sal < 2500 ' || v_sal);
  elsif (v_sal > 2500) then
    v_sal := v_sal / 2;
    dbms_output.put_line('sal > 2500 ' || v_sal);
  else
    dbms_output.put_line('sal = 2500 ' || v_sal);
  end if;
end;
/

–11、循环


declare
  i binary_integer := 1;
begin
  loop
    dbms_output.put_line(i);
    i := i + 1;
    exit when (i >= 11);
  end loop;
end;
/

说明：
1）在PL/SQL里面，循环一定是以loop开始，end loop结束。
2）循环结束的条件exit when。
3）这个循环相当于do while循环。


declare
  j binary_integer := 1;
begin
  while j < 11 loop
    dbms_output.put_line(j);
    j := j + 1;
  end loop;
end;
/

说明：
1）这个循环相当于while循环。


begin
  for k in 1..10 loop
    dbms_output.put_line(k);
  end loop;

  for k in reverse 1..10 loop
    dbms_output.put_line(k);
  end loop;
end;
/

说明：
1）for循环中默认计数器递增，使用reverse关键字后计数器递减。

–12、错误处理


declare
  v_temp number(4);
begin
  select empno into v_temp from emp where deptno = 10;
exception
  when too_many_rows then
    dbms_output.put_line('太多记录了');
  when others then
    dbms_output.put_line('error');
end;
/


declare
  v_temp number(4);
begin
  select empno into v_temp from emp where empno = 2222;
exception
  when no_data_found then
    dbms_output.put_line('没数据');
end;
/

说明：
1）PL/SQL也像Java一样会抛出异常。

–将数据库的错误记录在表里面


create table errorlog
(
  id number primary key,
  errcode number,
  errormsg varchar2(1024),
  errdate date
);

create sequence seq_errorlog_id start with 1 increment by 1;

declare
  v_deptno dept.deptno%type := 10;
  v_errcode number;
  v_errmsg varchar2(1024);
begin
  delete from dept where deptno = v_deptno;
  commit;
exception
  when others then
    rollback;
    v_errcode := SQLCODE;
    v_errmsg := SQLERRM;
    insert into errorlog values (seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
    commit;
end;
/

select * from errorlog;

说明：
1）SQLCODE、SQLERRM是关键字，可以在exception里面用。

一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

天使羊波波闪耀光芒

《PL/SQL基础学习笔记》上的2个想法

软件及互联网爱好者