复习内容：
PL/SQL的基本语法、记录类型、流程控制、游标的使用、
异常处理机制、存储函数/存储过程、触发器。
为方便大家跟着我的笔记练习，为此提供数据库表文件给大家下载：点我下载
为了要有输出的结果，在写PL/SQL程序前都在先运行这一句：
set serveroutput on
结构：
declare
--声明变量、类型、游标
begin
--程序的执行部分（类似于java里的main()方法）
exception
--针对begin块中出现的异常，提供处理的机制
--when...then...
--when...then...
end;
举例1：
declare v_sal number(10); （注意每句话后面别忘记了分号，跟java中的一样） begin select salary into v_sal from employees where employee_id = 100; dbms_output.put_line(v_sal); end;
举例2：
declare v_sal number(10); （注意，这里声明的空间大小不能比原表中的小） v_email varchar2(20); v_hire_date date; begin select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100; dbms_output.put_line(v_sal||','||v_email||','||v_hire_date); end; 或者： declare v_sal employees.salary%type; v_email employees.email%type; v_hire_date employees.hire_date%type; begin select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100; dbms_output.put_line(v_sal||','||v_email||','||v_hire_date); end;
记录：
declare type emp_record is record( v_sal employees.salary%type, v_email employees.email%type, v_hire_date employees.hire_date%type ); v_emp_record emp_record; begin select salary,email,hire_date into v_emp_record from employees where employee_id = 100; dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','|| v_emp_record.v_hire_date); end;
1、pl/sql基本的语法格式
2、记录类型 type ... is ...record(,,,);
3、流程控制：
3.1 条件判断（两种）
方式一： if ... then elseif then ... else ... end if;
方式二： case ... when ... then ...end;
3.2 循环结构（三种）
方式一：loop ... exit when ... end loop;
方式二：while ... loop ... end loop;
方式三：for i in ... loop ... end loop;
3.3 goto、exit
4.游标的使用（类似于java中的Iterator）
5.异常的处理
6.会写一个存储函数（有返回值）、存储过程（没有返回值）
7.会写一个触发器
复习记录类型：
declare type emp_record is record( -- v_emp_id employees.employee_id%type, -- v_sal employees.salary%type v_emp_id number(10) := 120, v_sal number(10,2) :=12000 ); v_emp_record emp_record; begin -- select employee_id,salary into v_emp_record from employees where employee_id = 123; dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' '||'salary:'|| v_emp_record.v_sal); end;
也可以升级一下，要是想对表的所有列都输出，则：（须注意输出的列名要跟表中的列名要一样）
declare v_emp_record employees%rowtype; begin select * into v_emp_record from employees where employee_id = 123; dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'|| v_emp_record.salary); end; 使用记录来执行update操作： declare v_emp_id number(10); begin v_emp_id :=123; update employees set salary = salary + 100 where employee_id = v_emp_id; dbms_output.put_line('执行成功！~~'); end;
流程控制：
查询150号员工的工资，若其工资大于或等于10000 则打印‘salary >= 10000';
若在5000到10000之间，则打印‘5000 <= salary <10000';否则打印‘salary < 5000'
declare v_sal employees.salary%type; begin select salary into v_sal from employees where employee_id =150; if v_sal >= 10000 then dbms_output.put_line('salary >= 10000'); elsif v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000'); else dbms_output.put_line('salary < 5000'); end if; dbms_output.put_line('salary:'||v_sal); end; 利用case ... when ... then ... when ...then ... else ... end实现上题; declare v_sal employees.salary%type; v_temp varchar2(20); begin select salary into v_sal from employees where employee_id =150; v_temp := case trunc(v_sal/5000) when 0 then 'salary < 5000' when 1 then '5000 <= salary < 10000' else 'salary >= 10000' end; dbms_output.put_line('salary:'||v_sal||' '||v_temp); end;
查询出122号员工的job_id,若其值为 ‘IT_PROG', 则打印‘GRADE:A'
‘AC_MGT', 则打印‘GRADE:B'
‘AC_ACCOUNT', 则打印‘GRADE:B'
否则打印‘GRADE:D'
declare v_job_id employees.job_id%type; v_temp varchar2(20); begin select job_id into v_job_id from employees where employee_id =122; v_temp := case v_job_id when 'IT_PROG' then 'A' when 'AC_MGT' then 'B' when 'AC_ACCOUNT' then 'C' else 'D' end; dbms_output.put_line('job_id:'||v_job_id||' '||v_temp); end;
使用循环语句打印：1-100
declare v_i number(5) :=1; begin loop dbms_output.put_line(v_i); exit when v_i >=100; v_i := v_i + 1; end loop; end; 使用while实现： declare v_i number(5) :=1; begin while v_i <= 100 loop dbms_output.put_line(v_i); v_i := v_i + 1; end loop; end; 使用for...in...loop...end loop;实现： begin for c in 1..100 loop dbms_output.put_line(c); end loop; end;
输出2-100之间的质数
declare v_i number(3):= 2; v_j number(3):= 2; v_flag number(1):= 1; begin while v_i<=100 loop while v_j<=sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; end if; v_j:= v_j+1; end loop; if v_flag = 1 then dbms_output.put_line(v_i); end if; v_j :=2; v_i := v_i + 1; v_flag := 1; end loop; end;
利用for循环实现输出2-100之间的质数：
declare v_flag number(1):= 1; begin for v_i in 2..100 loop for v_j in 2..sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; end if; end loop; if v_flag=1 then dbms_output.put_line(v_i); end if; v_flag := 1; end loop; end;
可以用goto改进一下：
declare v_flag number(1):= 1; begin for v_i in 2..100 loop for v_j in 2..sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; goto label; end if; end loop; <<label>> if v_flag=1 then dbms_output.put_line(v_i); end if; v_flag := 1; end loop; end;
打印1-100的自然数，当打印到50时，跳出循环 ，输出‘打印结束'：
begin for i in 1..100 loop if i=50 then goto label; end if; dbms_output.put_line(i); end loop; <<label>> dbms_output.put_line('打印结束'); end; 或者： begin for i in 1..100 loop if i=50 then dbms_output.put_line('打印结束'); exit; end if; dbms_output.put_line(i); end loop; end;
游标：
打印出80部门的所有的员工的工资：salary:XXX
declare
v_sal employees.salary%type;
--定义游标
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('salary:'||v_sal);
fetch emp_sal_cursor into v_sal;
end loop;
--关闭游标
close emp_sal_cursor;
end;
可以进行优化如下：
declare v_empid employees.employee_id%type; v_lastName employees.last_name%type; v_sal employees.salary%type; cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80; begin open emp_sal_cursor; fetch emp_sal_cursor into v_empid,v_lastName,v_sal; while emp_sal_cursor%found loop dbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||', '||'salary:'||v_sal); fetch emp_sal_cursor into v_empid,v_lastName,v_sal; end loop; close emp_sal_cursor; end;
或者使用记录再优化一下：
declare type emp_record is record( v_empid employees.employee_id%type, v_lastName employees.last_name%type, v_sal employees.salary%type ); v_emp_record emp_record; cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80; begin open emp_sal_cursor; fetch emp_sal_cursor into v_emp_record; while emp_sal_cursor%found loop dbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'|| v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal); fetch emp_sal_cursor into v_emp_record; end loop; close emp_sal_cursor; end;
可以使用for循环最优化：（注意：在for循环中它会自动的打开游标、提取游标，当提取完里面的数据后也会自动
的关闭游标）
declare cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80; begin for c in emp_sal_cursor loop dbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||', '||'salary:'||c.salary); end loop; end;
利用游标，调整公司中员工的工资：
工资范围 调整基数
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%
实现：
declare cursor emp_cursor is select employee_id,salary from employees; v_empid employees.employee_id%type; v_sal employees.salary%type; v_temp number(4,2); begin open emp_cursor; fetch emp_cursor into v_empid,v_sal; while emp_cursor%found loop if v_sal < 5000 then v_temp:=0.05; elsif v_sal < 10000 then v_temp:=0.03; elsif v_sal < 15000 then v_temp:=0.02; else v_temp:=0.01; end if; dbms_output.put_line(v_empid||','||v_sal); update employees set salary = salary * (1+v_temp) where employee_id = v_empid; fetch emp_cursor into v_empid,v_sal; end loop; close emp_cursor; end;
用for循环实现
declare cursor emp_cursor is select employee_id,salary from employees; v_temp number(4,2); begin for c in emp_cursor loop if c.salary <5000 then v_temp:=0.05; elsif c.salary <10000 then v_temp:=0.03; elsif c.salary <15000 then v_temp:=0.02; else v_temp:=0.01; end if; update employees set salary = salary * (1+v_temp) where employee_id = c.employee_id; end loop; end;
隐式游标：更新员工salary（涨工资10），如果该员工没有找到，则打印“查无此人”信息：
begin update employees set salary = salary + 10 where employee_id = 1001; if sql%notfound then dbms_output.put_line('查无此人'); end if; end;
异常：
预定义异常：（有24个预定义异常，可查表）
declare v_sal employees.salary%type; begin select salary into v_sal from employees where employee_id > 100; dbms_output.put_line(v_sal); exception when too_many_rows then dbms_output.put_line('输出的行数过多'); when others then dbms_output.put_line('出现其它的异常了'); end;
非预定义异常：
declare e_deleteid_exception exception; pragma exception_init(e_deleteid_exception,-2292); begin delete from employees where employee_id = 100; exception when e_deleteid_exception then dbms_output.put_line('违反了完整性约束，故不能删除此用户'); when others then dbms_output.put_line('出现其它的异常了'); end;
用户自定义异常：
declare e_sal_hight exception; v_sal employees.salary%type; begin select salary into v_sal from employees where employee_id = 100; if v_sal > 10000 then raise e_sal_hight; end if; exception when e_sal_hight then dbms_output.put_line('工资太高了'); when others then dbms_output.put_line('出现其它的异常了'); end;
通过select...into...查询某人的工资，若没找到则打印出“未找到此数据”：
declare v_sal employees.salary%type; begin select salary into v_sal from employees where employee_id = 1001; exception when no_data_found then dbms_output.put_line('未找到此数据'); when others then dbms_output.put_line('出现其它的异常了'); end; 更新指定员工工资，如工资小于300，则加100，对NO_DATA_FOUND异常，TOO_MANY_ROWS进行处理。 declare v_sal employees.salary%type; begin select salary into v_sal from employees where employee_id = 1001; if v_sal < 300 then update employees set salary = salary + 100 where employee_id =101; end if; exception when no_data_found then dbms_output.put_line('未找到此数据'); when too_many_rows then dbms_output.put_line('输出的行数太多了'); when others then dbms_output.put_line('出现其它的异常了'); end;
自定义异常：
更新指定员工工资，增加100；若指定员工不在，则抛出异常：NO_RESULT;
declare no_result exception; begin update employees set salary = salary + 100 where employee_id = 1001; if sql%notfound then raise no_result; end if; exception when no_result then dbms_output.put_line('查无此数据,更新失败'); when others then dbms_output.put_line('出现其它异常'); end;
存储过程：
写个简单的hello_world存储函数
create or replace function hello_world return varchar2 is （相当于declare，可以在其后面定义变量、记录、游标） begin return 'helloworld'; end; 存储函数的调用： begin dbms_output.put_line(hello_world); end; 或者： select hello_world from dual;
带参数的存储函数：
create or replace function hello_world1(v_logo varchar2) return varchar2 is begin return 'helloworld'||v_logo; end; 调用： select hello_world1('shellway') from dual 或者： begin dbms_output.put_line(hello_world1('shellway')); end;
定义一个获取系统时间的函数：
create or replace function get_sysdate return varchar2 is begin return to_char(sysdate,'yyyy-MM-dd HH24:mi:ss'); end;
定义带参数的函数，两个数相加
create or replace function add_param(v_num1 number,v_num2 number) return number is v_num3 number(10); begin v_num3 := v_num1 + v_num2; return v_num3; end; 调用： select add_param(2,5) from dual; 或者： begin dbms_output.put_line(add_param(5,4)); end;
定义一个函数：获取给定部门的工资总和，要求：部门号定义为参数，工资总额为返回值：
create or replace function get_sal(dept_id number) return number is v_sumsal number(10) := 0; cursor salary_cursor is select salary from employees where department_id = dept_id; begin for c in salary_cursor loop v_sumsal := v_sumsal + c.salary; end loop; return v_sumsal; end; 调用： select get_sal(80) from dual;
定义一个函数：获取给定部门的工资总和 和 该部门的员工总数（定义为OUT类型的参数）。
要求：部门号定义为参数，工资总额定义为返回值。
create or replace function get_sal(dept_id number,total_count out number) return number is v_sumsal number(10) := 0; cursor salary_cursor is select salary from employees where department_id = dept_id; begin total_count := 0; for c in salary_cursor loop v_sumsal := v_sumsal + c.salary; total_count := total_count + 1; end loop; return v_sumsal; end; 调用： declare v_count number(4); begin dbms_output.put_line(get_sal(80,v_count)); dbms_output.put_line(v_count); end;
定义一个存储过程：获取给定部门的工资总和（通过out参数），要求部门号和工资总额定义为参数。
（注意：存储过程和存储函数是不一样的，存储函数有返回值而存储过程没有，调用时候存储过程直接调用）
create or replace procedure get_sal1(dept_id number,sumsal out number) is cursor salary_cursor is select salary from employees where department_id = dept_id; begin sumsal := 0; for c in salary_cursor loop sumsal := sumsal + c.salary; end loop; dbms_output.put_line(sumsal); end; 调用： declare v_sal number(10):=0; begin get_sal1(80,v_sal); end;
对给定部门（作为输入参数）的员工进行加薪操作，若其到公司的时间在（？，95）期间，为其加薪5%
（95，98） 3%
（98，？） 1%
得到以下返回结果：为此次加薪公司每月额外付出多少成三（定义一个OUT型的输出参数）
create or replace procedure add_sal(dept_id number,temp out number) is cursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id; v_temp number(4,2):=0; begin temp := 0; for c in sal_cursor loop if to_char(c.hire_date,'yyyy') < '1995' then v_temp:=0.05; elsif to_char(c.hire_date,'yyyy') < '1998' then v_temp:=0.03; else v_temp:=0.01; end if; update employees set salary = salary * (1+v_temp) where employee_id = c.employee_id; temp := temp + c.salary*v_temp; end loop; dbms_output.put_line(temp); end; 调用： declare v_i number(10):=0; begin add_sal(80,v_i); end;
触发器：
触发事件：在INSERT,UPDATE,DELETE情况下会触发TRIGGER
触发时间：该TRIGGER是在触发事件发生之前(BEFORE)还是之后（AFTER）
触发器本身：该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情，如PL/SQL块
触发频率：有语句级(STATEMENT)触发器和行级(ROW)触发器
写一个简单的触发器：
create or replace trigger update_emp_trigger after update on employees for each row (行级触发器，即每更新一条记录就会输出一次'helloworld'，若没有这语句则是语句级触发器) begin dbms_output.put_line('helloworld'); end;
使用:new,:old修饰符：
1、 create table emp1 as select employee_id,salary,email from employees where department_id = 80; 2、 create or replace trigger update_emp_trigger2 after update on emp1 for each row begin dbms_output.put_line('old salary:'||:old.salary||'new salary:'||:new.salary); end; 3、 update emp1 set salary = salary + 100 ；
编写一个触发器，在对my_emp记录进行删除的时候，在my_emp_bak表中备份对应的记录
1、创建my_emp表： create table my_emp as select employee_id,salary from employees ; 2、创建my_emp_bak表： create table my_emp_bak as select employee_id,salary from employees where 1=2; 3、检查创建的表中的记录： select * from my_emp select * from my_emp_bak 4、创建一个触发器： create or replace trigger delete_emp_trigger before delete on my_emp for each row begin insert into my_emp_bak values(:old.employee_id,:old.salary); end; 5、执行含有触发器时间的语句： delete from my_emp 6、检查触发器执行后的结果： select * from my_emp select * from my_emp_bak
【oracle复习笔记之PL/SQL程序所要了解的知识点】相关文章：
★ oracle存储过程中return和exit区别概述及测试