PLSQL 概念：
oracle公司在SQL基础上进行扩展的一种过程语言，具有高级语言的特性，如封装、例外处理机制、信息隐藏、面向对象等，SQL语言可以直接写到PLSQL的‘块’中或者是PLSQL的过程、函数中。PLSQL消除了存取数据库的便利性与过程语言之间的障碍。
使用SQL来处理数据，如增删改查；使用PLSQL来形成控制结构，处理业务逻辑，如循环、条件判断。
PLSQL的优点：
1.PLSQL消除了存取数据库的便利性与过程语言之间的障碍。
2PLSQL包含复杂的业务逻辑处理能力，可以减少B/S 架构中客户端与服务端来回进行数据交互，以此减轻网络流量压力、节省时间。
oracle数据库中有两个引擎：oracle数据库引擎，专门负责sql；PLSQL 引擎，专门负责PLSQL逻辑业务处理。PLSQL引擎可以放到不同的地方，在数据库里面或者前端开发工具集。
PLSQL的代码结构：
PLSQL中引擎的处理：
--SQL引擎执行，然后发现有 PLSQL业务逻辑处理语句，进行上下文切换，转移到PLSQL引擎，
--PLSQL引擎执行结束后，将结果发送给SQL引擎，SQL引擎接着执行
--当PLSQL中有频繁的SQL块与PLSQL块交替时，会产生大量的上下文切换，引擎交替，会使得程序运行效率下降。
PLSQL的块的类型：
1匿名块
2存储过程
3函数
PLSQL的变量类型：
1 系统内置的常规简单变量类型：boolean、date、blob、long、number、bfile、varchar2、binary_integer
2用户自定义的复杂变量类型，类似于面向对象语言的结构体 structure
3引用类型：保存了一个指针值
4大对象类型LOB：保存了一个指向大对象的地址
PLSQL的变量作用域：
PLSQL复杂自定义数据类型：
PLSQL常用自定义数据类型：
1记录类型
2PLSQL内存表类型
构成部分：1、使用BINARY_INTEGER 类型构成的索引主键；
2、另外一个简单类型或者用户自定义类型的字段作为具体的数组元素。
备注：在PLSQL中%ROWTYPE 表示某张表的记录类型 或者是用户指定以的记录类型，
自定义复杂类型的使用实例：
游标是一个私有的SQLPLSQL中的游标概念：游工作区域
oracle中的游标类型：
1隐式游标：隐式游标不易被用户和程序员察觉和意识到，实际上Oracle服务器使用隐式游标来解析和执行我们提交的SQL语句。
2显示游标：显式游标是程序员在程序中显式声明的
游标的常用属性：
SQL%ROWCOUNT 受最近的SQL语句影响的行数
SQL%FOUND 最近的SQL语句是否影响了一行以上的
SQL%NOTFOUND 最近的SQL语句是否未影响任何数
SQL%ISOPEN 对于隐式游标而言永远为FALSE
游标是对内存区域进行操作，并发执行会带来一致性错误，最好的方法是加锁
显示游标的使用过程：
游标在不同的循环体中的差异：
游标在do..while 或while结构中的使用过程：
游标在for循环中的使用过程：
PLSQL 处理内存的常见错误：
PLSQL中的异常处理：
oracle中的例外类型：1 预定义例外 （有错误号+常量定义）
2非预定义例外（仅有错误号，无常量定义）
例外处理的情形：
oracle已经定义好的例外：
–NO_DATA_FOUND
–TOO_MANY_ROWS
–INVALID_CURSOR
–ZERO_DIVIDE
–DUP_VAL_ON_INDEX
oracle 提供了两个内置函数SQLCODE 和SQLERRM帮助了解异常的号码和错误信息
使用自定义异常：
PLSQL 的控制结构：
PLSQL的循环结构：
for循环
while 结构
do... while 结构
PLSQL中的SQL块：
select 语句
merge 语句
Insert语句
delete语句
PLSQL的匿名块
PLSQL的存储过程
--过程定义
--过程参数说明，in表明输入，out表明输出，in out表明既有输入又有输出
CREATEORREPLACEPROCEDURE add_jobs( p_job_id INVARCHAR2
,p_job_title INVARCHAR2
,p_min_salary INNUMBER
,x_max_salary OUTNUMBER
,x_return_code OUTNUMBER
,x_return_messageOUTVARCHAR2)
IS
--变量申明
e_job_id_is_null EXCEPTION;
e_job_id_is_not_unique EXCEPTION;
e_job_title_is_null EXCEPTION;
e_job_title_is_not_unique EXCEPTION;
e_min_salary_is_invalid EXCEPTION;
v_counter NUMBER;
BEGIN
--变量初始化
x_return_code :=1 ;
x_return_message :=NULL;
--输入参数进行校验
--输入参数的校验，输入参数若有为主键的的，则应考虑主键的约束条件：1非空 2唯一 3类型合法
--输入参数若与具体的业务相关，则应结合业务考虑具体的约束条件，如数据类型合法，如输入参数若为人数，则不可能为浮点数
--非空校验
IF(p_job_idISNULL)THEN
RAISE e_job_id_is_null;
ENDIF;
--唯一性校验
SELECTCOUNT(job_id)
INTO v_counter
FROM my_jobs
WHERE job_id=p_job_id;
IF(v_counter>0)THEN
RAISE e_job_id_is_not_unique;
ENDIF;
IF(p_job_titleISNULL)THEN
RAISE e_job_title_is_null;
ENDIF;
SELECTCOUNT(job_title)
INTO v_counter
FROM my_jobs
WHERE job_title=p_job_title;
IF(v_counter>0)THEN
RAISE e_job_title_is_not_unique;
ENDIF;
IF(p_min_salary IS NULL)THEN
RAISE e_min_salary_is_invalid;
ENDIF;
SELECT job_idFROM my_jobsFORUPDATEOF job_id;
INSERTINTO my_jobs(
job_id
,job_title
,min_salary
,max_salary
)
VALUES
(
p_job_id
,p_job_title
,p_min_salary
,p_min_salary*2
);
--commit保存事务
COMMIT;
--返回值赋值
x_max_salary :=p_min_salary*2;
x_return_code :=0;
x_return_message:=NULL;
--捕获异常，在对异常进行处理时，如果发生异常前已经进行了DML（增删改），则需要进行回滚操作
--异常类型：
--1针对参数的校验：1非空 2唯一 3业务逻辑合理
--2针对select into的异常 1 no_data_found 2 too_many_values
--3程序自身的语法错误 1类型转换错误
EXCEPTION
WHEN e_job_id_is_nullTHEN
x_return_message:='Job ID can''t benull!';
WHEN e_job_id_is_not_uniqueTHEN
x_return_message:='JOB ID('||p_job_id||')exists！';
WHEN e_job_title_is_nullTHEN
x_return_message:='Job title can''t benull!';
WHEN e_job_title_is_not_uniqueTHEN
x_return_message:='JOB title('||p_job_title||')exists！';
WHEN e_min_salary_is_invalidTHEN
x_return_message:='MIN salary isinvalid!';
WHENOTHERSTHEN
ROLLBACK;
x_return_message:='Found Error!'||SQLERRM;
END add_jobs;
--过程调用
DECLARE
v_job_id VARCHAR2(200);
v_job_title VARCHAR2(200);
v_min_salary NUMBER;
v_max_salary NUMBER;
v_return_code NUMBER;
v_return_message VARCHAR2(1000);
BEGIN
v_job_id := 'SY_ANAL';
v_job_title := 'System Analyst';
v_min_salary := 6000;
add_jobs( p_job_id =>v_job_id
,p_job_title =>v_job_title
,p_min_salary =>v_min_salary
,x_max_salary =>v_max_salary
,x_return_code =>v_return_code
,x_return_message=>v_return_message);
IF (v_return_code =0)THEN
dbms_output.put_line(to_char(v_max_salary));
--IF (x_return_code =1)THEN
ELSE
dbms_output.put_line(to_char(v_return_message));
ENDIF;
END;
--删除过程
DROPPROCEDUREadd_jobs;
PLSQL 自定义函数：
--函数定义
--函数参数一般只有in类型，在函数参数列表中不建议有out类型
CREATEorreplaceFUNCTION GET_SERVICE_YRS( p_employee_id IN NUMBER)
returnnumber
--变量声明
IS
e_employee_id_is_null EXCEPTION;
e_employee_id_is_not_valid EXCEPTION;
v_counter NUMBER;
v_emp_years NUMBER:=0;
v_job_years NUMBER:=0;
BEGIN
---参数校验
IF(p_employee_idISNULL)THEN
RAISE e_employee_id_is_null;
ENDIF;
SELECTCOUNT(employee_id)
INTO v_counter
FROM employees
WHERE employee_id=p_employee_id;
IF(v_counter>1)THEN
RAISE e_employee_id_is_not_valid;
ENDIF;
SELECTSUM(SYSDATE-hire_date)/365
INTO v_emp_years
FROM employees
WHERE employee_id=p_employee_id;
SELECTCOUNT(employee_id)
INTO v_counter
FROM job_history
WHERE employee_id=p_employee_id;
IF(v_counter>0)THEN
SELECTSUM(end_date-start_date)/365
INTO v_job_years
FROM job_history
WHERE employee_id=p_employee_id;
ENDIF;
--不存在DML，则不需要使用commit进行保存
RETURN v_job_years+v_emp_years;
--异常处理
EXCEPTION
WHEN e_employee_id_is_nullTHEN
dbms_output.put_line('Employee ID can''tbe null!');
RETURNNULL;
WHEN e_employee_id_is_not_validTHEN
dbms_output.put_line('Employee ID('||p_employee_id||')exists！');
RETURNNULL;
WHENOTHERSTHEN
ROLLBACK;
dbms_output.put_line('Found Error!'||SQLERRM);
RETURNNULL;
end GET_SERVICE_YRS;
--函数的使用
DECLARE
v_employee_id NUMBER;
v_service_years NUMBER;
BEGIN
v_employee_id :=106;
v_service_years:=GET_SERVICE_YRS( p_employee_id =>v_employee_id
);
dbms_output.put_line('The employee ID --'||to_char(v_employee_id)||'services '||to_char(v_service_years));
EXCEPTION
WHENOTHERSTHEN
ROLLBACK;
dbms_output.put_line(SQLERRM);
END;
--函数的删除
DROPFUNCTIONGET_SERVICE_YRS;
PLSQL中可以使用用户自定义函数的ＳＱＬ语句：
Select 语句
Where条件和Having子句
CONNECT BY, START WITH, ORDER BY, 和GROUP BY子句
INSERT的Values子句
UPDATE的Set子句
SQL语句使用用户自定义函数的限制：
l 必须是个函数（不能是过程-Procedure）
l 只能用IN模式的参数（不能有OUT,IN OUT 模式的参数）
l 只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内
l 存表）
l 函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型
l 在SQL中使用的函数，其函数体内部不能有DML语句。
l 在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句
l 在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)
备注：当执行SQL语句时，如果函数内部有DML语句，会发生执行的歧义，系统无法知道用户是想要先进行内部的DML，然后得到新表，然后再基于新表进行外部的SQL还是先进行外部调用函数前的SQL，再执行内部的DML然后得到新表。正是因为执行上存在先后顺序的问题，所以函数内部如果带有commit等事务语句，会使得一旦发生错误，很难恢复。
PLSQL 内置的函数介绍：
dbms_output.put_line(‘ ’); 输出内容到控制台
RAISE_APPLICATION_ERROR() 函数：它可以无需预先定义错误，而在需要抛出错误的地方直接使用此函数抛出例外
PLSQL 权限的管理：
PLSQL 权限的分配：
1 定义者权限
2 使用者权限
函数执行时，对表的访问默认使用定义者权限（即只有表的定义者才能访问表），如果想让表的使用者也具有访问权限，需要在函数的定义的时候说明。
如下：
注意在SQL Window中执行 函数或过程的定义，即使有编译错误，也不会显示；
只有在Program Window中执行 函数或过程的定义的时候才会立即显示编译错误；
PLSQL内置的utl_file 包
普通用户使用utl_file前需要先开通权限：
1开通超级管理员权限
2赋予普通用户使用utl_file的完整权限
3用户创建文件路径
utl_file 的使用例子：
--PLSQL 内置工具包 UTL_file
DECLARE
CURSOR emp_infoIS
SELECT emp.last_name
,dep.department_name
,emp.salary
FROM employees emp
,departments dep
WHEREemp.department_id=dep.department_id(+)
ORDERBY emp.department_id;
v_summary employees.salary%TYPE;
v_filehandle UTL_FILE.FILE_TYPE;--文件类型
BEGIN
SELECTsum(salary)
INTO v_summary
FROM employees ;
v_filehandle := UTL_FILE.FOPEN('MY_DIR','empinfo.txt','w');
--打开文件，指明文件路径，文件名，打开文件模式
UTL_FILE.put_line(v_filehandle,rPAD('=',150,'='));
--UTL_FILE.put_line函数作用：写完之后，重新开始新的一行
UTL_FILE.put_line(v_filehandle,rPAD('Employee',50,' ')||rPAD('Department',50,' ')||'Salary');
UTL_FILE.put_line(v_filehandle,rPAD('=',150,'='));
FOR v_emp_recIN emp_infoLOOP
UTL_FILE.put_line(v_filehandle,RPAD(v_emp_rec.last_name,50,' ')||RPAD(nvl(v_emp_rec.department_name,' '),50,' ')||'$'||to_char(v_emp_rec.salary,'9,999,999.00'));
UTL_FILE.put_line(v_filehandle,rPAD('-',150,'-'));
ENDLOOP;
UTL_FILE.put_line(v_filehandle,RPAD(' ',90,' ')||'summary: '||'$'||to_char(v_summary,'9,999,999.00'));
UTL_FILE.put_line(v_filehandle,rPAD('=',150,'='));
UTL_FILE.putf(v_filehandle,'Author:小五',SYSDATE);
UTL_FILE.new_line(v_filehandle);
--关闭文件
UTL_FILE.FCLOSE (v_filehandle);
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLETHEN
RAISE_APPLICATION_ERROR (-20001,'Invalid File.');
WHEN UTL_FILE.WRITE_ERRORTHEN
RAISE_APPLICATION_ERROR (-20002,'Unable to write tofile');
WHENOTHERSTHEN
IF(UTL_FILE.is_open(v_filehandle))THEN
UTL_FILE.FCLOSE(v_filehandle);
dbms_output.put_line(SQLERRM);
ENDIF;
END;
PLSQL的包
PLSQL的包的结构：1 包说明 2包体
PLSQL的包常用SQL：
PLSQL使用包的好处：
1、模块化：一般把有相关性的函数和过程放到一个Package中；
2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利
于分工合作；
3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有
函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数
和过程相当于私有的。
4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该
Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。 这个特性也提醒
我们不要去搞巨无霸的Package, 把你用到的任何 函数都写到一个Package中，这会导致严重的内存浪费。
5、重载：一个package 中可以定义同名、不同参数的函数或过程。
PLSQL中包的向前声明特性：
PLSQL中包初始化说明：
PLSQL包的完整结构：
--包声明部分
CREATEORREPLACEPACKAGE cux_hr_reports_pkg IS
--procedure declarations
PROCEDUREregn_sal_range_report(p_region_id INNUMBER
,x_return_code OUTNUMBER
,x_return_messageOUTVARCHAR2);
END cux_hr_reports_pkg;
--包体部分
CREATEORREPLACEPACKAGEBODY cux_hr_reports_pkg IS
--procedure implementations
procedureREGN_SAL_RANGE_REPORT( p_region_id INNUMBER
,x_return_code OUTNUMBER
,x_return_message OUTVARCHAR2)
IS
--异常变量声明
e_region_id_is_invalid EXCEPTION;
v_file_name VARCHAR2(400);
v_region_name VARCHAR2(200);
--变量声明
v_counter NUMBER;
v_region_num NUMBER:=0;
v_range1_num NUMBER;
v_range2_num NUMBER;
v_range3_num NUMBER;
v_range4_num NUMBER;
v_range5_num NUMBER;
v_range_total NUMBER;
--文件类型变量
v_filehandle UTL_FILE.FILE_TYPE;
BEGIN
--初始化变量
x_return_code :=1 ;
x_return_message :=NULL;
IF(p_region_idISNOTNULL)THEN
SELECTCOUNT(region_id)
INTO v_counter
FROM regions
WHERE region_id=p_region_id;
IF(v_counter=0)THEN
RAISE e_region_id_is_invalid;
ELSE
v_region_num :=1;
ENDIF;
ELSE
SELECTCOUNT(region_id)
INTO v_region_num
FROM regions;
ENDIF;
v_file_name :='REGN_SAL_RANGE_REPORT_'||to_char(SYSDATE,'YYYYMMDD')||'_'||to_char(SYSDATE,'HH24MISS')||'_'||USERENV('sessionid')||'.txt';
v_filehandle := UTL_FILE.FOPEN('MY_TEST_DIR',v_file_name,'w');
UTL_FILE.put_line(v_filehandle,rPAD('Report Name',20,' ')||rPAD('REGN: Range Of Salary',60,' '));
UTL_FILE.put_line(v_filehandle,rPAD('Print Date',20,' ')||rPAD(to_char(SYSDATE,'YYYY-MM-DDHH24:MI:SS'),60,' '));
UTL_FILE.put_line(v_filehandle,rPAD('=',20*(v_region_num+2)+5,'='));
IF(v_region_num=1)THEN
SELECT region_name
INTO v_region_name
FROM regions
WHERE region_id=p_region_id;
SELECTCOUNT(emp.employee_id)
INTO v_range1_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=p_region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND emp.salary<5000;
SELECTCOUNT(emp.employee_id)
INTO v_range2_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=p_region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND (emp.salary>=5000AND emp.salary<10000)
;
SELECTCOUNT(emp.employee_id)
INTO v_range3_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=p_region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND (emp.salary>=10000AND emp.salary<15000)
;
SELECTCOUNT(emp.employee_id)
INTO v_range4_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=p_region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND (emp.salary>=15000AND emp.salary<20000)
;
SELECTCOUNT(emp.employee_id)
INTO v_range5_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=p_region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND emp.salary>=20000
;
UTL_FILE.put_line(v_filehandle,rPAD('Range',20,' ')||' '||lPAD(v_region_name,20,' ')||' '||lPAD('Total',20,' '));
UTL_FILE.put_line(v_filehandle,rPAD('=',20,'=')||' '||lPAD('=',20,'=')||' '||lPAD('=',20,'='));
UTL_FILE.put_line(v_filehandle,rPAD('<5000',20,' ')||' '||lPAD(nvl(to_char(nullif(v_range1_num,0)),' '),20,' ')||' '||lPAD(v_range1_num,20,' '));
UTL_FILE.put_line(v_filehandle,rPAD('5000-10000',20,' ')||' '||lPAD(nvl(to_char(nullif(v_range2_num,0)),' '),20,' ')||' '||lPAD(v_range2_num,20,' '));
UTL_FILE.put_line(v_filehandle,rPAD('10000-15000',20,' ')||' '||lPAD( nvl(to_char(nullif(v_range3_num,0)),' '),20,' ')||' '||lPAD(v_range3_num,20,' '));
UTL_FILE.put_line(v_filehandle,rPAD('15000-20000',20,' ')||' '||lPAD(nvl(to_char(nullif(v_range4_num,0)),' '),20,' ')||' '||lPAD(v_range4_num,20,' '));
UTL_FILE.put_line(v_filehandle,rPAD('>=20000',20,' ')||' '||lPAD(nvl(to_char(nullif(v_range5_num,0)),' '),20,' ')||' '||lPAD(v_range5_num,20,' '));
ELSE
---range
UTL_FILE.putf(v_filehandle,rPAD('Range',20,' ')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
SELECT region_name
INTO v_region_name
FROM regions
WHERE region_id=v_emp.region_id;
UTL_FILE.putf(v_filehandle,lPAD(v_region_name,20,' ')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD('Total',20,' '));
UTL_FILE.new_line(v_filehandle);
--==
UTL_FILE.putf(v_filehandle,lPAD('=',20,'=')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
UTL_FILE.putf(v_filehandle,lPAD('=',20,'=')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD('=',20,'='));
UTL_FILE.new_line(v_filehandle);
--<5000
v_range_total:=0;
UTL_FILE.putf(v_filehandle,rPAD('<5000',20,' ')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
SELECTCOUNT(emp.employee_id)
INTO v_range1_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=v_emp.region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND emp.salary<5000;
v_range_total:=v_range_total+v_range1_num;
UTL_FILE.putf(v_filehandle,lPAD(nvl(to_char(nullif(v_range1_num,0)),' '),20,' ')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD(to_char(v_range_total),20,' '));
UTL_FILE.new_line(v_filehandle);
--5000-10000
v_range_total:=0;
UTL_FILE.putf(v_filehandle,rPAD('5000-1000',20,' ')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
SELECTCOUNT(emp.employee_id)
INTO v_range2_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=v_emp.region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND (emp.salary>=5000AND emp.salary<10000);
v_range_total:=v_range_total+ v_range2_num;
UTL_FILE.putf(v_filehandle,lPAD(nvl(to_char(nullif(v_range2_num,0)),' '),20,' ')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD(to_char(v_range_total),20,' '));
UTL_FILE.new_line(v_filehandle);
--10000-15000
v_range_total:=0;
UTL_FILE.putf(v_filehandle,rPAD('10000-15000',20,' ')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
SELECTCOUNT(emp.employee_id)
INTO v_range3_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=v_emp.region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND (emp.salary>=10000AND emp.salary<15000) ;
v_range_total:=v_range_total+ v_range3_num;
UTL_FILE.putf(v_filehandle,lPAD( nvl(to_char(nullif(v_range3_num,0)),' '),20,' ')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD(to_char(v_range_total),20,' '));
UTL_FILE.new_line(v_filehandle);
--15000-20000
v_range_total:=0;
UTL_FILE.putf(v_filehandle,rPAD('15000-20000',20,' ')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
SELECTCOUNT(emp.employee_id)
INTO v_range4_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=v_emp.region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND (emp.salary>=15000AND emp.salary<20000) ;
v_range_total:=v_range_total+ v_range4_num;
UTL_FILE.putf(v_filehandle,lPAD(nvl(to_char(nullif(v_range4_num,0)),' '),20,' ')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD(to_char(v_range_total),20,' '));
UTL_FILE.new_line(v_filehandle);
--15000-20000
v_range_total:=0;
UTL_FILE.putf(v_filehandle,rPAD('>=20000',20,' ')||' ');
FOR v_empIN (
SELECT reg.region_id
FROM regions reg
ORDERBY reg.region_nameASC
)LOOP
SELECTCOUNT(emp.employee_id)
INTO v_range5_num
FROM regions reg
,countries cou
,locations loc
,departments dep
,employees emp
WHERE reg.region_id=v_emp.region_id
AND reg.region_id=cou.region_id
AND cou.country_id=loc.country_id
AND loc.location_id=dep.location_id
AND dep.department_id=emp.department_id
AND emp.salary>=20000 ;
v_range_total:=v_range_total+ v_range5_num;
UTL_FILE.putf(v_filehandle,lPAD(nvl(to_char(nullif(v_range5_num,0)),' '),20,' ')||' ');
ENDLOOP;
UTL_FILE.putf(v_filehandle,lPAD(to_char(v_range_total),20,' '));
UTL_FILE.new_line(v_filehandle);
ENDIF;
UTL_FILE.put_line(v_filehandle,rPAD('=',20*(v_region_num+2)+5,'='));
UTL_FILE.FCLOSE (v_filehandle);
COMMIT;
x_return_code :=0;
x_return_message:=NULL;
EXCEPTION
WHEN UTL_FILE.INVALID_FILEHANDLETHEN
RAISE_APPLICATION_ERROR (-20001,'Invalid File.');
WHEN UTL_FILE.WRITE_ERRORTHEN
RAISE_APPLICATION_ERROR (-20002,'Unable to write tofile');
WHEN e_region_id_is_invalidTHEN
x_return_message:='Region ID iswrong!';
dbms_output.put_line(x_return_message);
WHENOTHERSTHEN
ROLLBACK;
IF(UTL_FILE.is_open(v_filehandle))THEN
UTL_FILE.FCLOSE(v_filehandle);
x_return_message:=SQLERRM;
dbms_output.put_line(x_return_message);
ENDIF;
endREGN_SAL_RANGE_REPORT;
END cux_hr_reports_pkg;
--包的过程的调用例子
DECLARE
region_id NUMBER;
cod NUMBER;
mess VARCHAR2(100);
BEGIN
cux_hr_reports_pkg.regn_sal_range_report(p_region_id => region_id
,x_return_code => cod
,x_return_message => mess);
END;
注意：
开发过程中，注释语言一切都为英语，应付国际化需要，适应不同编码。windows系统会在文本前面增加一个BOM字段，BOM字段用来表示文本的编码方式，在其他系统上会报错。但如果文本全部都为英文，则不会出现出现因为BOM字段带来的错误。
1662
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
