包，存储过程的编写

定义包头：

CREATE OR REPLACE PACKAGE DYG_TEST_2016 IS

  --定义类型
  TYPE T_CURSOR IS REF CURSOR;

  FUNCTION COMPUTE_TAX(SALARY NUMBER) RETURN NUMBER;

  PROCEDURE GETALL(NAMESTR IN VARCHAR2, IO_CURSOR OUT T_CURSOR); --模糊查询
  PROCEDURE TESTDATE; --测试
  PROCEDURE GETBYPAGE(P_TABLENAME    VARCHAR2,
                      P_FIELDS       VARCHAR2,
                      P_FILTER       VARCHAR2,
                      P_SORT         VARCHAR2,
                      P_CURPAGE      NUMBER,
                      P_PAGESIZE     NUMBER,
                      P_CURSOR       OUT T_CURSOR,
                      P_TOTALRECORDS OUT NUMBER); --分页
  PROCEDURE MODIFY(V_ID IN VARCHAR2, V_MSG OUT VARCHAR2);
  PROCEDURE DELDATA(V_ID IN VARCHAR2, V_MSG OUT VARCHAR2);

END DYG_TEST_2016;

包体编写：

CREATE OR REPLACE PACKAGE BODY DYG_TEST_2016 IS

  FUNCTION COMPUTE_TAX(SALARY NUMBER) RETURN NUMBER IS
  BEGIN
    IF SALARY < 5000 THEN
      RETURN SALARY * .15;
    ELSE
      RETURN SALARY * .33;
    END IF;
  END COMPUTE_TAX;

  PROCEDURE GETALL(NAMESTR IN VARCHAR2, IO_CURSOR OUT T_CURSOR) IS
    V_CURSOR T_CURSOR;
    V_SQL    VARCHAR2(2000);
 
  BEGIN
 
    V_SQL := 'select p.* from zy_test_product p where p.product_name like ''' ||
             NAMESTR || '%''';
    OPEN V_CURSOR FOR V_SQL;
    IO_CURSOR := V_CURSOR;
 
    --dyg_test_2016.testdate();
 
  END GETALL;

  PROCEDURE TESTDATE IS
 
  BEGIN
 
    DBMS_OUTPUT.PUT_LINE(SYSDATE);
 
  END TESTDATE;

  PROCEDURE GETBYPAGE(P_TABLENAME    VARCHAR2, --要查询的表名
                      P_FIELDS       VARCHAR2, --要查询的字段
                      P_FILTER       VARCHAR2, --过滤条件
                      P_SORT         VARCHAR2, --排序字段及方向
                      P_CURPAGE      NUMBER, --当前页
                      P_PAGESIZE     NUMBER, --页大小
                      P_CURSOR       OUT T_CURSOR,
                      P_TOTALRECORDS OUT NUMBER) IS
 
    V_SQL         VARCHAR2(1000) := '';
    V_STARTRECORD NUMBER(4);
    V_ENDRECORD   NUMBER(4);
 
  BEGIN
    --获取总的记录数
    V_SQL := 'select to_number(count(*)) from ' || P_TABLENAME;
    IF P_FILTER IS NOT NULL THEN
      V_SQL := V_SQL || ' where 1=1 and ' || P_FILTER;
    END IF;
    EXECUTE IMMEDIATE V_SQL
      INTO P_TOTALRECORDS;
 
    V_STARTRECORD := (P_CURPAGE - 1) * P_PAGESIZE;
    V_ENDRECORD   := P_CURPAGE * P_PAGESIZE;
 
    V_SQL := 'select ' || P_FIELDS || ' from (select A.' || P_FIELDS ||
             ' ,rownum r from ' || '(select ' || P_FIELDS || ' from ' ||
             P_TABLENAME;
    IF P_FILTER IS NOT NULL THEN
      V_SQL := V_SQL || ' where 1=1 and ' || P_FILTER;
    END IF;
    IF P_SORT IS NOT NULL THEN
      V_SQL := V_SQL || ' order by ' || P_SORT;
    END IF;
    V_SQL := V_SQL || ') A where rownum<=' || TO_CHAR(V_ENDRECORD) ||
             ') B where r>' || TO_CHAR(V_STARTRECORD);
 
    --DBMS_OUTPUT.put_line(v_sql);
    OPEN P_CURSOR FOR V_SQL;
 
  END GETBYPAGE;

  PROCEDURE MODIFY(V_ID IN VARCHAR2, V_MSG OUT VARCHAR2) IS
 
  BEGIN
    --dbms_output.put_line('Hello world');
    IF V_ID IS NULL THEN
      V_MSG := '参数错误';
    ELSE
      BEGIN
          --执行SQL
        UPDATE ZY_TEST_PRODUCT P
           SET P.PRODUCT_NAME = 'ccc--'
         WHERE P.PID = TO_NUMBER(V_ID);
      
        IF SQL%ROWCOUNT > 0 THEN
          V_MSG := '更新成功';
          COMMIT;
        ELSE
          V_MSG := '更新失败';
          ROLLBACK;
        END IF;
      
      EXCEPTION
        WHEN OTHERS THEN
          -- dbms_output.put_line('更新失败');
          V_MSG := '更新失败';
      END;
    END IF;
 
  END MODIFY;

 
 
  PROCEDURE DELDATA(V_ID IN VARCHAR2, V_MSG OUT VARCHAR2) IS
 
  BEGIN
    IF V_ID IS NULL THEN
      V_MSG := '参数错误';
    ELSE
      DELETE ZY_TEST_PRODUCT P WHERE P.PID = TO_NUMBER(V_ID);
      IF SQL%ROWCOUNT > 0 THEN
        COMMIT;
        V_MSG := '删除成功';
      ELSE
        V_MSG := '删除失败';
        ROLLBACK;
      END IF;
    
    END IF;
 
  END DELDATA;

END DYG_TEST_2016;