Oracle课程文档三.docx
《Oracle课程文档三.docx》由会员分享，可在线阅读，更多相关《Oracle课程文档三.docx（16页珍藏版）》请在冰点文库上搜索。
Oracle课程文档三
1.PL/SQL程序设计
1.1.什么是PL/SQL
♦PL/SQL（ProcedureLanguage/SQL）
♦PLSQL是Oracle对sql语言的过程化扩展
♦指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。
●把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。
●Plsql（oracle）,Transact-sql（SQLserver）
1.2.HelloWorld
♦写一段PL/SQL程序，在屏幕上打印“HelloWorld!
”
●注意：
如果要在屏幕上输出信息，需要将serveroutput开关打开：
setserveroutputon
1.3.PL/SQL程序结构
♦如果没有变量，就可以不写declare段
♦PL/SQL对大小写不敏感。
♦赋值是使用冒号等号“:
=”（中间不能有空格）
♦注释使用“--”或是“/*...*/”（就是SQL注释）
♦注意最后的end后面有个分号。
1.4.变量与赋值
1.4.1.声明变量
●说明变量（char,varchar2,date,number,boolean,long）
●记录变量分量的引用：
emp_rec.ename:
='ADAMS';
*属性类型有两种：
*%TYPE-引用变量和数据库列的数据类型
*%ROWTYPE-提供表示表中一行的记录类型
*使用属性类型的优点：
*不需要知道被引用的表列的具体类型
*如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变
1.4.2.赋值语句
●var1:
='thisisaargument';
●emp_rec.sal:
=sal*2+nvl（comm,0）;
●sum_sal:
=sum_sal+v_sal;
●FETCHc1INTOe_eno,e_sal;
●Selectsalintopsalfromempwhere....
1.5.IF语句
1.5.1.语法
♦只有IF的情况：
♦带ELSE的情况：
♦IF...ELSEIF...ELSE的情况：
注意：
是ELSIF，不是ELSEIF。
1.5.2.示例
●要求：
判断用户输入的数字。
●提示：
•从键盘输入：
acceptnumprompt'请输入一个数字';
•得到键盘输入的值：
pnumnumber:
=#
1.6.循环语句
1.6.1.语法
♦Loop
♦For
♦While
1.6.2.示例
1.7.光标（Cursor）
1.7.1.使用光标
●说明光标语法：
CURSOR光标名[（参数名数据类型[,参数名数据类型]...）]
ISSELECT语句;
●用于存储一个查询返回的多行数据
例如：
cursorc1isselectenamefromemp;
●打开光标：
openc1;（打开光标执行查询）
●取一行光标的值：
fetchc1intopjob;（取一行到变量中）
●关闭光标：
closec1;（关闭游标释放资源）
●注意：
上面的pjob必须与emp表中的job列类型一致：
●定义：
pjobemp.empjob%type;
1.7.2.带参数的光标
执行语句:
Openc2（‘clerk’）;
1.7.3.带参数的光标示例
♦写一段PL/SQL程序，为部门号为10的员工涨工资。
1.7.4.示例：
按员工的工种长工资,总裁1000元，经理长800元其，他人员长400元。
要真正的修改数据
游标代码
用来在PL语句中使用表结构的一个集合，。
可以再PL语句中灵活使用表的值。
语法：
--游标：
setserveroutputon;
--定义
declare
v_nameemp.ename%type;
v_salemp.sal%type;
cursorcursor_empis
selectename,salfromemp;
begin
--打开游标
opencursor_emp;
loop
--查找游标值。
fetchcursor_empintov_name,v_sal;
--退出条件找不到游标值。
exitwhencursor_emp%notfound;
dbms_output.put_line（v_name||''||v_sal）;
endloop;--退出循环
closecursor_emp;--关闭游标。
end;
游标属性：
%found：
只有在DML语句影响一行或者多行时，才会返回true。
%notfound：
与上条相反。
%roucount：
返回DML语句影响的行数。
如果没影响行，则返回0
%isopen：
返回游标是否已经打开。
For简化游标
setserveroutputon;
declare
v_nameemp.ename%type;
v_salemp.sal%type;
cursorcursor_empis
selectename,salfromemp;
begin
forempssincursor_emploop
dbms_output.put_line（'第'||cursor_emp%rowcount||'为雇员'||empss.ename||''||empss.sal）;
endloop;
end;
1.8.例外（异常）
♦例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。
1.8.1.Oracle的异常处理
●系统定义例外
•No_data_found（没有找到数据）
•Too_many_rows（select…into语句匹配多个行）
•Zero_Divide（被零除）
•Value_error（算术或转换错误）
•Timeout_on_resource（在等待资源时发生超时）
●用户定义的例外
手动抛出运行异常
语法：
raise_application_error（异常编号,’异常处理语句’）
PS：
手动抛出的异常必须低于0
●错误号：
错误号就是异常编号，用于处理异常的。
由于系统占用了编号，所以用户只能使用-20000到-20999之间的编号。
●演示：
系统定义例外（被0除）
1.8.2.演示：
用户定义例外及处理例外
●在declare节中定义例外
•out_ofexception;
●在可行语句中引起例外
•raiseout_of；
●在Exception节处理例外
•whenOut_ofthen…
Plsql异常
--异常处理语句。
begin
dbms_output.put_line（"asd"）;
--代码异常。
exception
whenaccess_into_nullthen
sequence_of_statements;
whenothersthen
rollback;
raise;
End
自定义处理异常
--异常处理语句。
--设置output的输出显示。
setserveroutputon;
--设置一个自定义的异常
declareu_nullexception;
begin
--抛出异常
raiseu_null;
--处理异常
exception
--捕获异常
whenu_nullthen
dbms_output.put_line（'ccc1'）;
--其他异常捕获
whenothersthen
dbms_output.put_line（'ccc2'）;
end;
1.9.PLsql命名细节：
标识符
命名规则
例子
程序变量
V_name
V_student_name
程序常量
C_name
C_company_name
异常标志
E_name
E_too_many
游标变量
Cursor_name
Cursor_emp
表类型
Name_table_type
Emp_record_type
表
Name_table
Emp_table
记录类型
Name_record
Emp_record
绑定变量
G_name
G_year_sal
在11G中的新特性：
是赋值语句中访问序列：
语法：
V_name:
=emp_sqe.nextVal;
1.10.实例
1.10.1.实例1：
按员工的工种长工资,总裁1000元，经理长800元，其他人员长400元
1.10.2.实例2：
统计每年入职的员工个数
1.10.3.实例3：
为员工涨工资，从最低工资调起，工资总额不能超过5万元
1.10.4.案例4：
实现按部门分段（6000以上、（6000，3000）、3000元以下）统计各工资段的职工人数、以及各部门的工资总额
题目：
用PL/SQL语言编写一程序，实现按部门分段（6000以上、（6000，3000）、3000元以下）统计各工资段的职工人数、以及各部门的工资总额（工资总额中不包括奖金），参考如下格式：
●提示：
可以创建一张新表用于保存数据
createtablemsg1
（deptnonumber,
emp_num1number,
emp_num2number,
emp_num3number,
sum_salnumber）;
升级会员 