oracle plsql

数据库 专栏收录该内容
3 篇文章 0 订阅

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 自定义函数:

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中可以使用用户自定义函数的SQL语句:

 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字段带来的错误。



  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值