plsql导成java包_PLSQL编程程序包实例

PLSQL编程程序包实例：

create or replace package myPackage0136 is

-- Author  : 李助新

-- Created : 2008-7-28 14:10:10

function get_3avg_profit(m_sale_man in varchar2,m_bill_date IN VARCHAR2) return NUMBER;

function get_6avg_profit(m_sale_man in varchar2,m_bill_date IN VARCHAR2) return NUMBER;

-- Purpose : 创建表 tableName

procedure myPro0136(tableName in varchar2) ;

-- Purpose :删除表tableName

procedure  dropTable(tableName in varchar2);

-- Purpose : 判断表tableName是否存在

function  tableIsExist(tableName in varchar2)  return boolean;

-- Purpose :根据员工编号，返回员工的兴趣

function myfunction0136(empId in varchar2) return varchar2;

-- Purpose :将最后结果插入到表 tableName中

procedure  insertToTable(tableName in varchar2);

-- Purpose : 完成整个处理过程

procedure  dataManage ;

end myPackage0136;

/

create or replace package body myPackage0136 IS

/*

函数名：get_3avg_profit

作  者：李助新

参数：传入员工编号，月份

功  能：根据员工编号，月份求出前三个月平均利润

返回值：返回前半年平均利润

*/

FUNCTION GET_3AVG_PROFIT(M_SALE_MAN IN VARCHAR2, M_BILL_DATE IN VARCHAR2)

RETURN NUMBER IS

AVG1_PROFIT NUMBER;

CURSOR CUR IS

SELECT AVF3_PROFIT

FROM (SELECT SALE_MAN,

BILL_DATE,

PROFIT,

TRUNC((LAG(PROFIT, 2, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 1, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 0, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE)) / 3,

2) AVF3_PROFIT

FROM (SELECT SALE_MAN,

BILL_DATE,

SUM(PROFIT) PROFIT,

ROW_NUMBER() OVER(PARTITION BY BILL_DATE ORDER BY SUM(PROFIT)) RW

FROM (SELECT C.BILL_ID_D,

C.BILL_CODE,

C.GOOD_NUMS,

C.COST_PRICE,

C.PROFIT PROFIT,

D.SALE_MAN SALE_MAN,

TO_CHAR(D.BILL_DATE, 'yyyy-mm') BILL_DATE

FROM (SELECT A.BILL_ID_D BILL_ID_D,

A.BILL_CODE BILL_CODE,

A.GOOD_NUMS GOOD_NUMS,

A.PRICE * A.GOOD_NUMS PRICE,

B.COST_PRICE * A.GOOD_NUMS COST_PRICE,

(A.PRICE - B.COST_PRICE) *

A.GOOD_NUMS PROFIT

FROM DSS_INFO_SALE_BILL_D A,

DSS_INFO_GOOD        B

WHERE A.GOOD_ID = B.GOOD_ID) C,

DSS_INFO_SALE_BILL D

WHERE C.BILL_CODE = D.BILL_CODE AND D.SALE_MAN = M_SALE_MAN)

GROUP BY SALE_MAN, BILL_DATE

ORDER BY BILL_DATE))

WHERE  BILL_DATE = M_BILL_DATE;

R_EMP CUR%ROWTYPE;

BEGIN

OPEN CUR;

FETCH CUR

INTO R_EMP;

AVG1_PROFIT := R_EMP.AVF3_PROFIT;

CLOSE CUR;

RETURN AVG1_PROFIT;

END GET_3AVG_PROFIT;

/*

函数名：get_6avg_profit

作  者：李助新

参数：传入员工编号，月份

功  能：根据员工编号，月份求出前半年平均利润

返回值：返回前半年平均利润

*/

FUNCTION GET_6AVG_PROFIT(M_SALE_MAN IN VARCHAR2, M_BILL_DATE IN VARCHAR2)

RETURN NUMBER IS

AVG1_PROFIT NUMBER;

CURSOR CUR IS

SELECT AVF6_PROFIT

FROM (SELECT SALE_MAN,

BILL_DATE,

PROFIT,

TRUNC((LAG(PROFIT, 5, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 4, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 3, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 2, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 1, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE) +

LAG(PROFIT, 0, 0)

OVER(PARTITION BY SALE_MAN ORDER BY BILL_DATE)) / 6,

2) AVF6_PROFIT

FROM (SELECT SALE_MAN,

BILL_DATE,

SUM(PROFIT) PROFIT,

ROW_NUMBER() OVER(PARTITION BY BILL_DATE ORDER BY SUM(PROFIT)) RW

FROM (SELECT C.BILL_ID_D,

C.BILL_CODE,

C.GOOD_NUMS,

C.COST_PRICE,

C.PROFIT PROFIT,

D.SALE_MAN SALE_MAN,

TO_CHAR(D.BILL_DATE, 'yyyy-mm') BILL_DATE

FROM (SELECT A.BILL_ID_D BILL_ID_D,

A.BILL_CODE BILL_CODE,

A.GOOD_NUMS GOOD_NUMS,

A.PRICE * A.GOOD_NUMS PRICE,

B.COST_PRICE * A.GOOD_NUMS COST_PRICE,

(A.PRICE - B.COST_PRICE) *

A.GOOD_NUMS PROFIT

FROM DSS_INFO_SALE_BILL_D A,

DSS_INFO_GOOD        B

WHERE A.GOOD_ID = B.GOOD_ID) C,

DSS_INFO_SALE_BILL D

WHERE C.BILL_CODE = D.BILL_CODE AND D.SALE_MAN = M_SALE_MAN )

GROUP BY SALE_MAN, BILL_DATE

ORDER BY BILL_DATE))

WHERE  BILL_DATE = M_BILL_DATE;

R_EMP CUR%ROWTYPE;

BEGIN

OPEN CUR;

FETCH CUR

INTO R_EMP;

AVG1_PROFIT := R_EMP.AVF6_PROFIT;

CLOSE CUR;

RETURN AVG1_PROFIT;

END GET_6AVG_PROFIT;

/*过程名：myPro0136

功能：  创建一张临时表

*/

procedure myPro0136(tableName in varchar2) is

sql_str long;

begin

sql_str :='create table '||tableName||' (

EMP_ID  varchar(32),

EMP_NAME VARCHAR2(32),

GENDER    CHAR(1),

ADDRESS   VARCHAR2(64),

PHONE     NUMBER,

DEGREE_ID VARCHAR2(16),

DEP_ID    VARCHAR2(20),

INTEREST  VARCHAR2(100)

)';

Execute Immediate sql_str;

commit;

end myPro0136;

/*函数名：tableIsExist

参数：tableName，传入一张表的名称

功能：判断该表是否存在，如果存在就返回true

否则返回false

返回类型：boolean

*/

function tableIsExist(tableName in varchar2)return boolean

is

Result boolean;

i INTEGER;

begin

SELECT COUNT(*)

INTO   i

FROM   user_tables

WHERE   table_name = UPPER(tableName);

if i = 1

then

Result :=true;

else

Result:=false;

end if;

return(Result);

end tableIsExist;

/*过程名：dropTable

参数：tableName，传入一张表的名称

功能：删除tableName表

*/

procedure dropTable(tableName in varchar2)

is

sql_str long;

begin

sql_str :='drop table '||tableName;

Execute Immediate sql_str;

commit;

end dropTable;

/*函数名：myFunction0136

功能：  根据员工编号查找员工的兴趣，以字符串的形式返回

返回类型：varchar2

*/

function myFunction0136(empId in varchar2) return varchar2

is

Result varchar2(50);

temp varchar2(16);

sql_str long;

cur sys_refcursor;

begin

sql_str :='select interest_desc from code_interest0136 where emp_id ='|| empId ;

open cur for sql_str;

loop

fetch cur into temp;

Result := Result || temp||',';

exit when cur%notfound;

end loop;

close cur;

Result := substr(Result,1,length(Result)-1);

return (Result);

end myFunction0136;

/*过程名：insertToTable

参数：tableName，传入一张表的名称

功能：将结果集插入到表tableName中

*/

procedure  insertToTable(tableName in varchar2)

is

sql_str long;

begin

sql_str := 'insert into '|| tableName ||' (EMP_ID,EMP_NAME,GENDER,ADDRESS,

PHONE,DEGREE_ID,DEP_ID,INTEREST)'

||'( select a.EMP_ID,a.EMP_NAME,a.GENDER,a.ADDRESS,a.PHONE,a.DEGREE_ID,a.DEP_ID,

myPackage0136.myFunction0136(a.emp_id) from info_emp0136 a )';

dbms_output.put_line(sql_str);

execute immediate sql_str;

commit;

end insertToTable;

/*过程名：dataManage

功能：实现整个处理过程

*/

procedure  dataManage

is

begin

--如果存在表info_employer就先删除，然后创建表

if myPackage0136.tableIsExist('info_employer')

then

myPackage0136.dropTable('info_employer');

end if;

myPackage0136.myPro0136('info_employer');

myPackage0136.insertToTable('info_employer');

--异常处理

exception

when others then

dbms_output.put_line('error');

end dataManage;

end myPackage0136;

/