oracle复习笔记之PL/SQL程序所要了解的知识点

复习内容：

PL/SQL的基本语法、记录类型、流程控制、游标的使用、

异常处理机制、存储函数/存储过程、触发器。

为方便大家跟着我的笔记练习，为此提供数据库表文件给大家下载：点我下载

为了要有输出的结果，在写PL/SQL程序前都在先运行这一句：

set serveroutput on

结构：

declare

--声明变量、类型、游标

begin

--程序的执行部分(类似于java里的main()方法)

exception

--针对begin块中出现的异常，提供处理的机制

--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

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

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

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

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

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)

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)

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

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

select employee_id,salary from employees ;

2、创建my_emp_bak表：

create table my_emp_bak

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程序所要了解的知识点

相关文章