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

复习内容：

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

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

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

为了要有输出的结果，在写PL/SQL程序前都在先运行这一句：
setserveroutputon
结构：
declare
--声明变量、类型、游标
begin
--程序的执行部分（类似于java里的main()方法）
exception
--针对begin块中出现的异常，提供处理的机制
--when...then...
--when...then...
end;
举例1：

declare
v_salnumber(10);（注意每句话后面别忘记了分号，跟java中的一样）
begin
selectsalaryintov_salfromemployeeswhereemployee_id=100;
dbms_output.put_line(v_sal);
end;

举例2：

declare
v_salnumber(10);（注意，这里声明的空间大小不能比原表中的小）
v_emailvarchar2(20);
v_hire_datedate;
begin
selectsalary,email,hire_dateintov_sal,v_email,v_hire_datefromemployeeswhereemployee_id=
100;
dbms_output.put_line(v_sal||","||v_email||","||v_hire_date);
end;
或者：
declare
v_salemployees.salary%type;
v_emailemployees.email%type;
v_hire_dateemployees.hire_date%type;
begin
selectsalary,email,hire_dateintov_sal,v_email,v_hire_datefromemployeeswhereemployee_id=
100;
dbms_output.put_line(v_sal||","||v_email||","||v_hire_date);
end;

记录：

declare
typeemp_recordisrecord(
v_salemployees.salary%type,
v_emailemployees.email%type,
v_hire_dateemployees.hire_date%type
);
v_emp_recordemp_record;
begin
selectsalary,email,hire_dateintov_emp_recordfromemployeeswhereemployee_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...thenelseifthen...else...endif;
方式二：case...when...then...end;
3.2循环结构（三种）
方式一：loop...exitwhen...endloop;
方式二：while...loop...endloop;
方式三：foriin...loop...endloop;
3.3goto、exit
4.游标的使用（类似于java中的Iterator）
5.异常的处理

6.会写一个存储函数（有返回值）、存储过程（没有返回值）
7.会写一个触发器

复习记录类型：

declare
typeemp_recordisrecord(
--v_emp_idemployees.employee_id%type,
--v_salemployees.salary%type
v_emp_idnumber(10):=120,
v_salnumber(10,2):=12000
);
v_emp_recordemp_record;
begin
--selectemployee_id,salaryintov_emp_recordfromemployeeswhereemployee_id=123;
dbms_output.put_line("employee_id:"||v_emp_record.v_emp_id||""||"salary:"||
v_emp_record.v_sal);
end;

也可以升级一下，要是想对表的所有列都输出，则：（须注意输出的列名要跟表中的列名要一样）

declare
v_emp_recordemployees%rowtype;
begin
select*intov_emp_recordfromemployeeswhereemployee_id=123;
dbms_output.put_line("employee_id:"||v_emp_record.employee_id||""||"salary:"||
v_emp_record.salary);
end;
使用记录来执行update操作：
declare
v_emp_idnumber(10);
begin
v_emp_id:=123;
updateemployees
setsalary=salary+100
whereemployee_id=v_emp_id;
dbms_output.put_line("执行成功！~~");
end;

流程控制：
查询150号员工的工资，若其工资大于或等于10000则打印‘salary>=10000";
若在5000到10000之间，则打印‘5000<=salary<10000";否则打印‘salary<5000"

declare
v_salemployees.salary%type;
begin
selectsalaryintov_salfromemployeeswhereemployee_id=150;
ifv_sal>=10000thendbms_output.put_line("salary>=10000");
elsifv_sal>5000thendbms_output.put_line("10000>salary>=5000");
elsedbms_output.put_line("salary<5000");
endif;
dbms_output.put_line("salary:"||v_sal);
end;
利用case...when...then...when...then...else...end实现上题;
declare
v_salemployees.salary%type;
v_tempvarchar2(20);
begin
selectsalaryintov_salfromemployeeswhereemployee_id=150;
v_temp:=
casetrunc(v_sal/5000)when0then"salary<5000"
when1then"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_idemployees.job_id%type;
v_tempvarchar2(20);
begin
selectjob_idintov_job_idfromemployeeswhereemployee_id=122;
v_temp:=
casev_job_idwhen"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_inumber(5):=1;

begin
loop
dbms_output.put_line(v_i);
exitwhenv_i>=100;
v_i:=v_i+1;
endloop;
end;
使用while实现：
declare
v_inumber(5):=1;
begin
whilev_i<=100loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
endloop;
end;
使用for...in...loop...endloop;实现：
begin
forcin1..100loop
dbms_output.put_line(c);
endloop;
end;

输出2-100之间的质数

declare
v_inumber(3):=2;
v_jnumber(3):=2;
v_flagnumber(1):=1;
begin
whilev_i<=100loop
whilev_j<=sqrt(v_i)loop
ifmod(v_i,v_j)=0thenv_flag:=0;
endif;
v_j:=v_j+1;
endloop;
ifv_flag=1thendbms_output.put_line(v_i);
endif;
v_j:=2;
v_i:=v_i+1;
v_flag:=1;
endloop;
end;

利用for循环实现输出2-100之间的质数：

declare
v_flagnumber(1):=1;
begin
forv_iin2..100loop
forv_jin2..sqrt(v_i)loop
ifmod(v_i,v_j)=0thenv_flag:=0;
endif;
endloop;
ifv_flag=1thendbms_output.put_line(v_i);
endif;
v_flag:=1;
endloop;
end;

可以用goto改进一下：

declare
v_flagnumber(1):=1;
begin
forv_iin2..100loop
forv_jin2..sqrt(v_i)loop
ifmod(v_i,v_j)=0thenv_flag:=0;
gotolabel;
endif;
endloop;
<<label>>
ifv_flag=1thendbms_output.put_line(v_i);
endif;
v_flag:=1;
endloop;
end;

打印1-100的自然数，当打印到50时，跳出循环，输出‘打印结束"：

begin
foriin1..100loop
ifi=50thengotolabel;
endif;
dbms_output.put_line(i);
endloop;
<<label>>
dbms_output.put_line("打印结束");
end;
或者：
begin
foriin1..100loop
ifi=50thendbms_output.put_line("打印结束");
exit;
endif;
dbms_output.put_line(i);
endloop;
end;

游标：
打印出80部门的所有的员工的工资：salary:XXX
declare
v_salemployees.salary%type;
--定义游标
cursoremp_sal_cursorisselectsalaryfromemployeeswheredepartment_id=80;
begin
--打开游标
openemp_sal_cursor;
--提取游标
fetchemp_sal_cursorintov_sal;
whileemp_sal_cursor%foundloop
dbms_output.put_line("salary:"||v_sal);
fetchemp_sal_cursorintov_sal;
endloop;
--关闭游标
closeemp_sal_cursor;
end;
可以进行优化如下：

declare
v_empidemployees.employee_id%type;
v_lastNameemployees.last_name%type;
v_salemployees.salary%type;
cursoremp_sal_cursorisselectemployee_id,last_name,salaryfromemployeeswhere
department_id=80;
begin
openemp_sal_cursor;
fetchemp_sal_cursorintov_empid,v_lastName,v_sal;
whileemp_sal_cursor%foundloop
dbms_output.put_line("employee_id:"||v_empid||","||"last_name:"||v_lastName||",
"||"salary:"||v_sal);
fetchemp_sal_cursorintov_empid,v_lastName,v_sal;
endloop;
closeemp_sal_cursor;
end;

或者使用记录再优化一下：

declare
typeemp_recordisrecord(
v_empidemployees.employee_id%type,
v_lastNameemployees.last_name%type,
v_salemployees.salary%type
);
v_emp_recordemp_record;
cursoremp_sal_cursorisselectemployee_id,last_name,salaryfromemployeeswhere
department_id=80;
begin
openemp_sal_cursor;
fetchemp_sal_cursorintov_emp_record;
whileemp_sal_cursor%foundloop
dbms_output.put_line("employee_id:"||v_emp_record.v_empid||","||"last_name:"||
v_emp_record.v_lastName||","||"salary:"||v_emp_record.v_sal);
fetchemp_sal_cursorintov_emp_record;
endloop;
closeemp_sal_cursor;
end;

可以使用for循环最优化：（注意：在for循环中它会自动的打开游标、提取游标，当提取完里面的数据后也会自动
的关闭游标）

declare
cursoremp_sal_cursorisselectemployee_id,last_name,salaryfromemployeeswhere
department_id=80;
begin
forcinemp_sal_cursorloop
dbms_output.put_line("employee_id:"||c.employee_id||","||"last_name:"||c.last_name||",
"||"salary:"||c.salary);
endloop;
end;

利用游标，调整公司中员工的工资：
工资范围      调整基数
0-5000       5%
5000-10000   3%
10000-15000  2%
15000-       1%
实现：

declare
cursoremp_cursorisselectemployee_id,salaryfromemployees;
v_empidemployees.employee_id%type;
v_salemployees.salary%type;
v_tempnumber(4,2);
begin
openemp_cursor;
fetchemp_cursorintov_empid,v_sal;
whileemp_cursor%foundloop
ifv_sal<5000thenv_temp:=0.05;
elsifv_sal<10000thenv_temp:=0.03;
elsifv_sal<15000thenv_temp:=0.02;
elsev_temp:=0.01;
endif;
dbms_output.put_line(v_empid||","||v_sal);
updateemployees
setsalary=salary*(1+v_temp)
whereemployee_id=v_empid;
fetchemp_cursorintov_empid,v_sal;
endloop;
closeemp_cursor;
end;

用for循环实现

declare
cursoremp_cursorisselectemployee_id,salaryfromemployees;
v_tempnumber(4,2);
begin
forcinemp_cursorloop
ifc.salary<5000thenv_temp:=0.05;
elsifc.salary<10000thenv_temp:=0.03;
elsifc.salary<15000thenv_temp:=0.02;
elsev_temp:=0.01;
endif;
updateemployees
setsalary=salary*(1+v_temp)
whereemployee_id=c.employee_id;
endloop;
end;

隐式游标：更新员工salary（涨工资10），如果该员工没有找到，则打印“查无此人”信息：

begin
updateemployees
setsalary=salary+10
whereemployee_id=1001;
ifsql%notfoundthendbms_output.put_line("查无此人");
endif;
end;

异常：
预定义异常：（有24个预定义异常，可查表）

declare
v_salemployees.salary%type;
begin
selectsalaryintov_salfromemployees
whereemployee_id>100;
dbms_output.put_line(v_sal);
exception
whentoo_many_rowsthendbms_output.put_line("输出的行数过多");
whenothersthendbms_output.put_line("出现其它的异常了");
end;

非预定义异常：

declare
e_deleteid_exceptionexception;
pragmaexception_init(e_deleteid_exception,-2292);
begin
deletefromemployees
whereemployee_id=100;
exception
whene_deleteid_exceptionthendbms_output.put_line("违反了完整性约束，故不能删除此用户");
whenothersthendbms_output.put_line("出现其它的异常了");
end;

用户自定义异常：

declare
e_sal_hightexception;
v_salemployees.salary%type;
begin
selectsalaryintov_salfromemployeeswhereemployee_id=100;
ifv_sal>10000thenraisee_sal_hight;
endif;
exception
whene_sal_hightthendbms_output.put_line("工资太高了");
whenothersthendbms_output.put_line("出现其它的异常了");
end;

通过select...into...查询某人的工资，若没找到则打印出“未找到此数据”：

declare
v_salemployees.salary%type;
begin
selectsalaryintov_salfromemployeeswhereemployee_id=1001;
exception
whenno_data_foundthendbms_output.put_line("未找到此数据");
whenothersthendbms_output.put_line("出现其它的异常了");
end;
更新指定员工工资，如工资小于300，则加100，对NO_DATA_FOUND异常，TOO_MANY_ROWS进行处理。
declare
v_salemployees.salary%type;
begin
selectsalaryintov_salfromemployeeswhereemployee_id=1001;
ifv_sal<300thenupdateemployeessetsalary=salary+100whereemployee_id=101;
endif;
exception
whenno_data_foundthendbms_output.put_line("未找到此数据");
whentoo_many_rowsthendbms_output.put_line("输出的行数太多了");
whenothersthendbms_output.put_line("出现其它的异常了");
end;

自定义异常：
更新指定员工工资，增加100；若指定员工不在，则抛出异常：NO_RESULT;

declare
no_resultexception;
begin
updateemployeessetsalary=salary+100whereemployee_id=1001;
ifsql%notfoundthenraiseno_result;
endif;
exception
whenno_resultthendbms_output.put_line("查无此数据,更新失败");
whenothersthendbms_output.put_line("出现其它异常");
end;

存储过程：
写个简单的hello_world存储函数

createorreplacefunctionhello_world
returnvarchar2
is（相当于declare，可以在其后面定义变量、记录、游标）
begin
return"helloworld";
end;
存储函数的调用：
begin
dbms_output.put_line(hello_world);
end;
或者：
selecthello_worldfromdual;

带参数的存储函数：

createorreplacefunctionhello_world1(v_logovarchar2)
returnvarchar2
is
begin
return"helloworld"||v_logo;
end;
调用：
selecthello_world1("shellway")fromdual
或者：
begin
dbms_output.put_line(hello_world1("shellway"));
end;

定义一个获取系统时间的函数：

createorreplacefunctionget_sysdate
returnvarchar2
is
begin
returnto_char(sysdate,"yyyy-MM-ddHH24:mi:ss");
end;

定义带参数的函数，两个数相加

createorreplacefunctionadd_param(v_num1number,v_num2number)
returnnumber
is
v_num3number(10);
begin
v_num3:=v_num1+v_num2;
returnv_num3;
end;
调用：
selectadd_param(2,5)fromdual;
或者：
begin
dbms_output.put_line(add_param(5,4));
end;

定义一个函数：获取给定部门的工资总和，要求：部门号定义为参数，工资总额为返回值：

createorreplacefunctionget_sal(dept_idnumber)
returnnumber
is
v_sumsalnumber(10):=0;
cursorsalary_cursorisselectsalaryfromemployeeswheredepartment_id=dept_id;
begin
forcinsalary_cursorloop
v_sumsal:=v_sumsal+c.salary;
endloop;
returnv_sumsal;
end;
调用：
selectget_sal(80)fromdual;

定义一个函数：获取给定部门的工资总和和该部门的员工总数（定义为OUT类型的参数）。
要求：部门号定义为参数，工资总额定义为返回值。

createorreplacefunctionget_sal(dept_idnumber,total_countoutnumber)
returnnumber
is
v_sumsalnumber(10):=0;
cursorsalary_cursorisselectsalaryfromemployeeswheredepartment_id=dept_id;
begin
total_count:=0;
forcinsalary_cursorloop
v_sumsal:=v_sumsal+c.salary;
total_count:=total_count+1;
endloop;
returnv_sumsal;
end;
调用：
declare
v_countnumber(4);
begin
dbms_output.put_line(get_sal(80,v_count));
dbms_output.put_line(v_count);
end;

定义一个存储过程：获取给定部门的工资总和（通过out参数），要求部门号和工资总额定义为参数。
（注意：存储过程和存储函数是不一样的，存储函数有返回值而存储过程没有，调用时候存储过程直接调用）

createorreplaceprocedureget_sal1(dept_idnumber,sumsaloutnumber)
is
cursorsalary_cursorisselectsalaryfromemployeeswheredepartment_id=dept_id;
begin
sumsal:=0;
forcinsalary_cursorloop
sumsal:=sumsal+c.salary;
endloop;
dbms_output.put_line(sumsal);
end;
调用：
declare
v_salnumber(10):=0;
begin
get_sal1(80,v_sal);
end;

对给定部门（作为输入参数）的员工进行加薪操作，若其到公司的时间在（？，95）期间，为其加薪5%

                                             （95，98）         3%

                                             （98，？）         1%
得到以下返回结果：为此次加薪公司每月额外付出多少成三（定义一个OUT型的输出参数）

createorreplaceprocedureadd_sal(dept_idnumber,tempoutnumber)
is
cursorsal_cursorisselectemployee_id,salary,hire_date
fromemployeeswheredepartment_id=dept_id;
v_tempnumber(4,2):=0;
begin
temp:=0;
forcinsal_cursorloop
ifto_char(c.hire_date,"yyyy")<"1995"thenv_temp:=0.05;
elsifto_char(c.hire_date,"yyyy")<"1998"thenv_temp:=0.03;
elsev_temp:=0.01;
endif;

updateemployees
setsalary=salary*(1+v_temp)
whereemployee_id=c.employee_id;

temp:=temp+c.salary*v_temp;
endloop;
dbms_output.put_line(temp);
end;
调用：
declare
v_inumber(10):=0;
begin
add_sal(80,v_i);
end;

触发器：
触发事件：在INSERT,UPDATE,DELETE情况下会触发TRIGGER
触发时间：该TRIGGER是在触发事件发生之前(BEFORE)还是之后（AFTER）
触发器本身：该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情，如PL/SQL块
触发频率：有语句级(STATEMENT)触发器和行级(ROW)触发器
写一个简单的触发器：

createorreplacetriggerupdate_emp_trigger
after
updateonemployees
foreachrow(行级触发器，即每更新一条记录就会输出一次"helloworld"，若没有这语句则是语句级触发器)
begin
dbms_output.put_line("helloworld");
end;

使用:new,:old修饰符：

1、
createtableemp1
as
selectemployee_id,salary,emailfromemployeeswheredepartment_id=80;
2、
createorreplacetriggerupdate_emp_trigger2
after
updateonemp1
foreachrow
begin
dbms_output.put_line("oldsalary:"||:old.salary||"newsalary:"||:new.salary);
end;
3、
updateemp1setsalary=salary+100；

编写一个触发器，在对my_emp记录进行删除的时候，在my_emp_bak表中备份对应的记录

1、创建my_emp表：
createtablemy_emp
as
selectemployee_id,salaryfromemployees;
2、创建my_emp_bak表：
createtablemy_emp_bak
as
selectemployee_id,salaryfromemployeeswhere1=2;
3、检查创建的表中的记录：
select*frommy_emp
select*frommy_emp_bak
4、创建一个触发器：
createorreplacetriggerdelete_emp_trigger
before
deleteonmy_emp
foreachrow
begin
insertintomy_emp_bak
values(:old.employee_id,:old.salary);
end;
5、执行含有触发器时间的语句：
deletefrommy_emp
6、检查触发器执行后的结果：
select*frommy_emp
select*frommy_emp_bak