oracle 存储过程和function

ajzvrgtvck

function
   function 语法中本地调用用 测试用
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL

-------------------------
create or replace function getmonthfunction(yearandmonth  varchar,paten  varchar)
return varchar
as
v_sal varchar;
begin
select  to_char(add_months(TO_DATE('2014-5', 'YYYY-MM'),-1), 'mm')    v_sal from dual ;
return v_sal;
end ；
drop function getmonthfunction;

select getmonthfunction(2014-11,mm) from  dual;



select getmonthFunc('2014-12',-5) from  dual;

CREATE OR REPLACE  FUNCTION getmonthFunc (yearandmonth IN varchar, num2 IN varchar)  
        RETURN VARCHAR2   
    IS
        month VARCHAR2(100);
        cmonth VARCHAR2(100);   
    BEGIN  
        month:=to_char(add_months(TO_DATE(yearandmonth, 'YYYY-MM'),num2), 'mm');
        if month=1
          THEN
            cmonth:='AMOUNTJANUARY';
          end if;
        if month=2
          THEN
            cmonth:='AMOUNTFEBRUARY';
          end if;
        if month=3
          THEN
            cmonth:='AMOUNTMARCH';
          end if;
         if month=4
          THEN
            cmonth:='AMOUNTAPRIL';
          end if;
          if month=5
          THEN
            cmonth:='AMOUNTMAY';
          end if;
        if month=6
          THEN
            cmonth:='AMOUNTJUNE';
          end if;
        if month=7
          THEN
            cmonth:='AMOUNTJULY';
          end if;
       if month=8
          THEN
            cmonth:='AMOUNTAUGUST';
          end if;
      if month=9
          THEN
            cmonth:='AMOUNTSEPTEMBER';
          end if;
     if month=10
          THEN
            cmonth:='AMOUNTOCTOBER';
             end if;
      if month=11
          THEN
            cmonth:='AMOUNTNOVEMBER';
             end if;
      if month=12
          THEN
            cmonth:='AMOUNTDECEMBER';
          end if;   
       RETURN cmonth;   
    END getmonthFunc;  
   
drop function  getmonthFunc
------------------------------------------------------------------------------------------------
select getmonthforNumFunc('2014-12',-5) from  dual;

CREATE OR REPLACE  FUNCTION getmonthforNumFunc (yearandmonth IN varchar, num2 IN varchar)  
        RETURN VARCHAR2   
    IS
        month VARCHAR2(100);
    BEGIN  
        month:=to_number(to_char(add_months(TO_DATE(yearandmonth, 'YYYY-MM'),num2), 'mm'));
       RETURN month;   
    END getmonthforNumFunc;  
   
select getyearforNumFunc('2014-12',0) from  dual;   
CREATE OR REPLACE  FUNCTION getyearforNumFunc (yearandmonth IN varchar, num2 IN varchar)  
        RETURN VARCHAR2   
    IS
        year VARCHAR2(100);
    BEGIN  
        year:=to_char(add_months(TO_DATE(yearandmonth, 'YYYY-MM'),num2), 'YYYY');
       RETURN year;   
    END getyearforNumFunc;  
   
==================================================
select getskuassessmenntFunc('2014-12',-3) from  dual;

CREATE OR REPLACE  FUNCTION getskuassessmenntFunc (yearandmonth IN varchar, num2 IN varchar)  
        RETURN VARCHAR2   
    IS
        msg VARCHAR2(100);
        num VARCHAR2(100);
    BEGIN  
       num:=substr(num2,2,1);
        msg:='skuassessmennt'||num ;
       RETURN msg;   
    END getskuassessmenntFunc;      
   
============================================================
select getskuassessmenntLvFunc('2014-12',-1,'8a8ad0a038d53d3a0138d58b16cf006b','297e57f448a1a8220148a56095613717') from  dual;
CREATE OR REPLACE  FUNCTION getskuassessmenntLvFunc (yearandmonth IN varchar, num2 IN varchar,departmentid IN varchar,checkty IN varchar )  
        RETURN VARCHAR2   
    IS
        msg VARCHAR2(100);
        num VARCHAR2(100);
        Result varchar2(50);
        y VARCHAR2(100);   
    BEGIN  
        num:=substr(num2,2,1);
        msg:='skuassessmennt'||num ;
        y:=to_char(add_months(TO_DATE(yearandmonth, 'YYYY-MM'),num2), 'YYYY');
        if num=1
          THEN
             select skuassessmennt1  into Result from uf_accuracyparameters where year=y and department=departmentid and checktype=checkty;
          end if;
        if num=2
          THEN
             select skuassessmennt2  into Result from uf_accuracyparameters where year=y and department=departmentid and checktype=checkty;
          end if;
           if num=3
          THEN
             select skuassessmennt3  into Result from uf_accuracyparameters where year=y and department=departmentid and checktype=checkty;
          end if;
       RETURN Result;   
    END getskuassessmenntLvFunc;

===============================================================================

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS
TYPE Test_CURSOR IS REF CURSOR;
END TESTPACKAGE;

CREATE OR REPLACE PROCEDURE test2(typestring IN VARCHAR2) IS
BEGIN
  INSERT INTO B_ID_temp ( I_ID ,I_NAME)VALUES SELECT I_ID ,I_NAME FROM B_ID
END test2


CREATE OR REPLACE PROCEDURE Proc_Insert(P_CURSOR OUT TESTPACKAGE.TEST_CURSOR,
                                  yearandmonth IN VARCHAR2,
                                  checktypeid IN VARCHAR2,
                                  departmentid IN VARCHAR2,
                                  curtuerid IN VARCHAR2)
                                   IS
str_sql varchar2(4000):='';
   amountmonth varchar2(400):='';
    amountmonth2 varchar2(400):='';
     amountmonth3 varchar2(400):='';
BEGIN
  amountmonth:=getmonthFunc(''||yearandmonth||'',-1);
   amountmonth2:=getmonthFunc(''||yearandmonth||'',-2);
    amountmonth3:=getmonthFunc(''||yearandmonth||'',-3);
  str_sql:='INSERT INTO UF_MONTHASSESSMENTTEMPORARY
    (id,
    department,
    province,
    area,
    customercode,
    customer,
    productcode,
    product,
    price,
    amountexpect,
    amounthistory,
    departmentcn,
    provincecn,
    areacn,
    year,
    month,
    isaccord,
    currentUserId
      )
       SELECT aa.id,
     aa.DEPARTMENT,
     aa.PROVINCE,
     aa.AREA,
     aa.CUSTOMERCODE,
     aa.CUSTOMER,
     aa.PRODUCTCODE,
     aa.PRODUCT,
     aa.PRICE,
     aa.AMOUNTEXPECT,
     aa.AMOUNTHISTORY,
     aa.DEPARTMENTCN,
     aa.PROVINCECN,
     aa.AREACN,
     aa.YEARBUDGET,
     aa.monthbudget,
     aa.ISACCORD,
     '''||curtuerid||'''currentUserId from (
   SELECT zz.id,
     zz.DEPARTMENT,
     zz.PROVINCE,
     zz.AREA,
     zz.CUSTOMERCODE,
     zz.CUSTOMER,
     zz.PRODUCTCODE,
     zz.PRODUCT,
     zz.PRICE,
     zz.AMOUNTEXPECT,
     zz.AMOUNTHISTORY,
     zz.DEPARTMENTCN,
     zz.PROVINCECN,
     zz.AREACN,
     zz.YEARBUDGET,
     zz.monthbudget,
     CASE when zz.lv between zz.startlv and zz.endlv then 1 else 0 end as ISACCORD from (
SELECT
uf1.id,
     uf1.DEPARTMENT,
     uf1.PROVINCE,
     uf1.AREA,
     uf1.CUSTOMERCODE,
     uf1.CUSTOMER,
     uf1.PRODUCTCODE,
     uf1.PRODUCT,
     uf1.PRICE,
     uf1.monthprice1 AMOUNTEXPECT,
     uf2.'||amountmonth||' AMOUNTHISTORY,
     uf1.DEPARTMENTCN,
     uf1.PROVINCECN,
     uf1.AREACN,
     uf1.YEARBUDGET,
     uf1.monthbudget,
     1 as ISACCORD,  
     cc.startlv,
     cc.endlv,
     CASE NVL(UF2.'||amountmonth||',0) WHEN ''0'' THEN 0 ELSE round(NVL(NVL(UF1.monthprice1, 0) / NVL(UF2.'||amountmonth||', 0)*100, 0),2) END  lv   
  FROM UF_MONTHINCOMETEST UF1
  LEFT JOIN UF_MIDTBMONTHLICBUDGET UF2
   ON UF1.DEPARTMENT = UF2.DEPARTMENT
   AND UF1.PRODUCTCODE = UF1.PRODUCTCODE
    and uf1.customer=uf2.customercode
   AND UF1.PROVINCE = UF2.PROVINCE
   AND UF1.AREA = UF2.AREA
   AND UF2.YEAR = getyearforNumFunc('''||yearandmonth||''',0)-1
   left join (select (100-(100-getskuassessmenntLvFunc('''||yearandmonth||''',-1,'''||departmentid||''','''||checktypeid||'''))) startlv,(100+(100-getskuassessmenntLvFunc('''||yearandmonth||''',-1,'''||departmentid||''','''||checktypeid||'''))) endlv, getskuassessmenntLvFunc('''||yearandmonth||''',-1,'''||departmentid||''','''||checktypeid||''') ,year, department from uf_accuracyparameters  ) cc  
  on uf1.YEARBUDGET=cc.year and uf1.department=cc.department
WHERE UF1.DEPARTMENT = '''||departmentid||'''
   AND UF1.YEARBUDGET =getyearforNumFunc('''||yearandmonth||''',-1)
   and uf1.monthbudget=getmonthforNumFunc('''||yearandmonth||''',-1) ) zz  
) aa';
execute immediate str_sql;
  OPEN P_CURSOR FOR
    SELECT * FROM UF_MONTHASSESSMENTTEMPORARY;
END Proc_Insert;

DROP TABLE B_ID_temp

DROP PROCEDURE TESTC
EXEC TESTC
SELECT COUNT(*) FROM UF_MONTHASSESSMENTTEMPORARY
DELETE FROM UF_MONTHASSESSMENTTEMPORARY

create global temporary table mytesttemp(id   VARCHAR2(32 CHAR) not null,
  requestid              VARCHAR2(32 CHAR),
  nodeid                 VARCHAR2(32 CHAR),
  rowindex               VARCHAR2(100 CHAR),
  productcode            VARCHAR2(256 CHAR),
  product                VARCHAR2(256 CHAR),
  year                   VARCHAR2(32 CHAR),
  updatedate             VARCHAR2(32 CHAR),
  isdelete               INTEGER default 0 not null,
  department             VARCHAR2(256 CHAR),
  province               VARCHAR2(256 CHAR),
  area                   VARCHAR2(256 CHAR),
  departmentcn           VARCHAR2(256 CHAR),
  provincecn             VARCHAR2(256 CHAR),
  areacn                 VARCHAR2(256 CHAR),
  isaccord               VARCHAR2(256 CHAR),
  currentuserid          VARCHAR2(256 CHAR),
  preprepremonthaccuracy VARCHAR2(256 CHAR),
  preprepremonthscore    VARCHAR2(256 CHAR),
  prepremonthaccuracy    VARCHAR2(256 CHAR),
  prepremonthscore       VARCHAR2(256 CHAR),
  premonthaccuracy       VARCHAR2(256 CHAR),
  premonthscore          VARCHAR2(256 CHAR),
  countscore             VARCHAR2(256 CHAR),
  checktypecn            VARCHAR2(256 CHAR),
  checktype              VARCHAR2(256 CHAR),
  recoder                VARCHAR2(256 CHAR),
  recodercn              VARCHAR2(256 CHAR),
  month                  VARCHAR2(256 CHAR)
  )  
on commit preserve rows  

SELECT * FROM mytesttemp
select * into mytesttemp  from UF_ASSESSMENTHISTORY

CREATE TABLE UF_ASSESSMENTHISTORY2 AS SELECT *   from UF_ASSESSMENTHISTORY

SELECT * FROM UF_ASSESSMENTHISTORY2

CREATE OR REPLACE PROCEDURE getdatefromtable
============================================================

CREATE TABLE SFZ_TEST_MANAGER_XG(  
       yxgh VARCHAR2(100),  
       ygxm VARCHAR2(100),  
       position_name VARCHAR2(100)  
);  
insert into SFZ_TEST_MANAGER_XG values ('abc1','bcd1','cde1');  
insert into SFZ_TEST_MANAGER_XG values ('abc2','bcd2','cde2');  
insert into SFZ_TEST_MANAGER_XG values ('abc3','bcd3','cde3');  
insert into SFZ_TEST_MANAGER_XG values ('abc4','bcd4','cde4');  

SELECT * FROM SFZ_TEST_MANAGER_XG

DROP TABLE SFZ_TEST_MANAGER_XG;


CREATE   GLOBAL TEMPORARY TABLE SFZ_TEMP_MANAGER_XG(  
       yxgh VARCHAR2(100),  
       ygxm VARCHAR2(100),  
       position_name VARCHAR2(100)  
)ON COMMIT PRESERVE ROWS;  

DROP TABLE SFZ_TEMP_MANAGER_XG;  
  
select * from SFZ_TEST_MANAGER_XG;
              SFZ_TEMP_MANAGER_XG  
select count(*) from SFZ_TEST_MANAGER_XG;

SELECT * FROM SFZ_TEMP_MANAGER_XG



create or replace package sfz_obj  
as  
type sfz_cursor is ref cursor;  
procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out sfz_cursor);  
end sfz_obj;  


create or replace package body sfz_obj as   
procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out sfz_cursor)  
is  
BEGIN  
   INSERT INTO SFZ_TEMP_MANAGER_XG(YXGH,YGXM,POSITION_NAME) SELECT YXGH,YGXM,POSITION_NAME FROM SFZ_TEST_MANAGER_XG;  
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');  
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');  
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');  
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');  
   open v_table for select * from SFZ_TEMP_MANAGER_XG;  
end proc_sfz_proc_test;  
end sfz_obj;  

SELECT * FROM SFZ_TEMP_MANAGER_XG
select * from product_component_version;

create or replace procedure area
is
num number ;
v_areaRecord  dual%ROWTYPE;
begin
select 12345 into num from dual;
end area;

begin

commit;
end;



CREATE OR REPLACE PROCEDURE Proc_Insert2(P_CURSOR OUT TESTPACKAGE.TEST_CURSOR,
                                  yearandmonth IN VARCHAR2,
                                  checktypeid IN VARCHAR2,
                                  departmentid IN VARCHAR2,
                                  curtuerid IN VARCHAR2)
                                   IS
str_sql varchar2(4000):='';
   amountmonth varchar2(400):='';
    amountmonth2 varchar2(400):='';
     amountmonth3 varchar2(400):='';
BEGIN
  amountmonth:=getmonthFunc(''||yearandmonth||'',-1);
   amountmonth2:=getmonthFunc(''||yearandmonth||'',-2);
    amountmonth3:=getmonthFunc(''||yearandmonth||'',-3);
  str_sql:='INSERT INTO UF_MONTHASSESSMENTTEMPORARY
    (id,
    department,
    province,
    area,
    customercode,
    customer,
    productcode,
    product,
    price,
    amountexpect,
    amounthistory,
    departmentcn,
    provincecn,
    areacn,
    year,
    month,
    isaccord,
    currentUserId
      )
       SELECT aa.id,
     aa.DEPARTMENT,
     aa.PROVINCE,
     aa.AREA,
     aa.CUSTOMERCODE,
     aa.CUSTOMER,
     aa.PRODUCTCODE,
     aa.PRODUCT,
     aa.PRICE,
     aa.AMOUNTEXPECT,
     aa.AMOUNTHISTORY,
     aa.DEPARTMENTCN,
     aa.PROVINCECN,
     aa.AREACN,
     aa.YEARBUDGET,
     aa.monthbudget,
     aa.ISACCORD,
     '''||curtuerid||'''currentUserId from (
   SELECT zz.id,
     zz.DEPARTMENT,
     zz.PROVINCE,
     zz.AREA,
     zz.CUSTOMERCODE,
     zz.CUSTOMER,
     zz.PRODUCTCODE,
     zz.PRODUCT,
     zz.PRICE,
     zz.AMOUNTEXPECT,
     zz.AMOUNTHISTORY,
     zz.DEPARTMENTCN,
     zz.PROVINCECN,
     zz.AREACN,
     zz.YEARBUDGET,
     zz.monthbudget,
     CASE when zz.lv between zz.startlv and zz.endlv then 1 else 0 end as ISACCORD from (
SELECT
uf1.id,
     uf1.DEPARTMENT,
     uf1.PROVINCE,
     uf1.AREA,
     uf1.CUSTOMERCODE,
     uf1.CUSTOMER,
     uf1.PRODUCTCODE,
     uf1.PRODUCT,
     uf1.PRICE,
     uf1.monthprice1 AMOUNTEXPECT,
     uf2.'||amountmonth2||' AMOUNTHISTORY,
     uf1.DEPARTMENTCN,
     uf1.PROVINCECN,
     uf1.AREACN,
     uf1.YEARBUDGET,
     uf1.monthbudget,
     1 as ISACCORD,  
     cc.startlv,
     cc.endlv,
     CASE NVL(UF2.'||amountmonth2||',0) WHEN ''0'' THEN 0 ELSE round(NVL(NVL(UF1.monthprice2, 0) / NVL(UF2.'||amountmonth2||', 0)*100, 0),2) END  lv   
  FROM UF_MONTHINCOMETEST UF1
  LEFT JOIN UF_MIDTBMONTHLICBUDGET UF2
   ON UF1.DEPARTMENT = UF2.DEPARTMENT
   AND UF1.PRODUCTCODE = UF1.PRODUCTCODE
    and uf1.customer=uf2.customercode
   AND UF1.PROVINCE = UF2.PROVINCE
   AND UF1.AREA = UF2.AREA
   AND UF2.YEAR = getyearforNumFunc('''||yearandmonth||''',0)-1
   left join (select (100-(100-getskuassessmenntLvFunc('''||yearandmonth||''',-2,'''||departmentid||''','''||checktypeid||'''))) startlv,(100+(100-getskuassessmenntLvFunc('''||yearandmonth||''',-2,'''||departmentid||''','''||checktypeid||'''))) endlv, getskuassessmenntLvFunc('''||yearandmonth||''',-2,'''||departmentid||''','''||checktypeid||''') ,year, department from uf_accuracyparameters  ) cc  
  on uf1.YEARBUDGET=cc.year and uf1.department=cc.department
WHERE UF1.DEPARTMENT = '''||departmentid||'''
   AND UF1.YEARBUDGET =getyearforNumFunc('''||yearandmonth||''',-2)
   and uf1.monthbudget=getmonthforNumFunc('''||yearandmonth||''',-2) ) zz  
) aa';
execute immediate str_sql;
  OPEN P_CURSOR FOR
    SELECT * FROM UF_MONTHASSESSMENTTEMPORARY;
END Proc_Insert2;

调用：
begin
Proc_Insert3('2014-11','297e57f448a1a8220148a56095613717','8a8ad0a038d53d3a0138d58b16cf006b','111111');
commit;
end;



PROCEDURE 和 function 区别：

1.procedure 中调用 function 时 function 变量 用''||XXXX||'', 然而produre中则用'''||||'''
2.function sql 查询字段的话 字段不可以拼变量





========================================

DROP TABLE UF_MONTHASSESSMENTTEMPORARY
create table UF_MONTHASSESSMENTTEMPORARY
(
  id            VARCHAR2(32 CHAR) not null,
  requestid     VARCHAR2(32 CHAR),
  nodeid        VARCHAR2(32 CHAR),
  rowindex      VARCHAR2(100 CHAR),
  customer      VARCHAR2(256 CHAR),
  customercode  VARCHAR2(256 CHAR),
  productcode   VARCHAR2(256 CHAR),
  product       VARCHAR2(256 CHAR),
  year          VARCHAR2(32 CHAR),
  updatedate    VARCHAR2(32 CHAR),
  isdelete      INTEGER default 0,
  department    VARCHAR2(256 CHAR),
  province      VARCHAR2(256 CHAR),
  area          VARCHAR2(256 CHAR),
  price         VARCHAR2(256 CHAR),
  numbers       VARCHAR2(256 CHAR),
  amountexpect  VARCHAR2(256 CHAR),
  amounthistory VARCHAR2(256 CHAR),
  month         VARCHAR2(256 CHAR),
  departmentcn  VARCHAR2(256 CHAR),
  provincecn    VARCHAR2(256 CHAR),
  areacn        VARCHAR2(256 CHAR),
  isaccord      VARCHAR2(256 CHAR),
  currentuserid VARCHAR2(256 CHAR),
  recodercn     VARCHAR2(256 CHAR),
  recoder       VARCHAR2(256 CHAR)
)
;
alter table UF_MONTHASSESSMENTTEMPORARY
  add primary key (ID);