--1.创建包说明------
CREATE PACKAGE my_Package
IS
man_num NUMBER; ---定义了2个全局变量
woman_num NUMBER;
CURSOR itemInfo_Cur; ---定义游标
CREATE FUNCTION F_Count_Num(in_sex IN TEST_CUR.sex% TYPE)
RETURN NUMBER; -----定义了一个函数 其中TEST_CUR是表名
CREATE PROCEDURE P_Count_Num
(in_sex IN TEST_CUR.sex% TYPE,out_num OUT NUMBER); ----定义存储过程
END my_Package;
--2.创建包体--------
CREATE PACKAGE BODY my_Package
AS
CURSOR itemInfo_Cur IS
SELECT qccd, genno, lotno, itemcd FROM MQCITEM
WHERE lotno ='';
FUNCTION F_Count_Num(in_sex IN TEST_CUR.sex% TYPE)
RETURN NUMBER
AS
out_num NUMBER;
BEGIN
IF in_sex ='0' THEN
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='0';
ELSE
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='1';
END IF;
RETURN out_num;
END F_Count_Num;
PROCEDURE P_Count_Num
(in_sex IN TEST_CUR.sex% TYPE,out_num OUT NUMBER)
AS
BEGIN
IF in_sex ='0' THEN
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='0';
ELSE
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='1';
END IF;
END P_Count_Num;
END my_Package;
---drop Package my_Package
CREATE PACKAGE my_Package
IS
man_num NUMBER; ---定义了2个全局变量
woman_num NUMBER;
CURSOR itemInfo_Cur; ---定义游标
CREATE FUNCTION F_Count_Num(in_sex IN TEST_CUR.sex% TYPE)
RETURN NUMBER; -----定义了一个函数 其中TEST_CUR是表名
CREATE PROCEDURE P_Count_Num
(in_sex IN TEST_CUR.sex% TYPE,out_num OUT NUMBER); ----定义存储过程
END my_Package;
--2.创建包体--------
CREATE PACKAGE BODY my_Package
AS
CURSOR itemInfo_Cur IS
SELECT qccd, genno, lotno, itemcd FROM MQCITEM
WHERE lotno ='';
FUNCTION F_Count_Num(in_sex IN TEST_CUR.sex% TYPE)
RETURN NUMBER
AS
out_num NUMBER;
BEGIN
IF in_sex ='0' THEN
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='0';
ELSE
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='1';
END IF;
RETURN out_num;
END F_Count_Num;
PROCEDURE P_Count_Num
(in_sex IN TEST_CUR.sex% TYPE,out_num OUT NUMBER)
AS
BEGIN
IF in_sex ='0' THEN
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='0';
ELSE
SELECT COUNT(sex) INTO out_num FROM TEST_CUR WHERE sex='1';
END IF;
END P_Count_Num;
END my_Package;
---drop Package my_Package