校园网站建设需要哪些,苏州建设交易中心网站,网站权重怎么查询,国家高新技术企业的好处当前位置:我的异常网 数据库 oracle 存储过程跟functionoracle 存储过程跟functionwww.myexceptions.net 网友分享于#xff1a;2014-11-26 浏览#xff1a;0次oracle 存储过程和functionfunctionfunction 语法中本地调用用 测试用SELECT TO_DATE(2006-05, YYYY-MM) FROM …当前位置:我的异常网» 数据库 » oracle 存储过程跟functionoracle 存储过程跟functionwww.myexceptions.net 网友分享于2014-11-26 浏览0次oracle 存储过程和functionfunctionfunction 语法中本地调用用 测试用SELECT TO_DATE(2006-05, YYYY-MM) FROM DUAL-------------------------create or replace function getmonthfunction(yearandmonth varchar,paten varchar)return varcharasv_sal varchar;beginselect 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 VARCHAR2ISmonth VARCHAR2(100);cmonth VARCHAR2(100);BEGINmonth:to_char(add_months(TO_DATE(yearandmonth, YYYY-MM),num2), mm);if month1THENcmonth:AMOUNTJANUARY;end if;if month2THENcmonth:AMOUNTFEBRUARY;end if;if month3THENcmonth:AMOUNTMARCH;end if;if month4THENcmonth:AMOUNTAPRIL;end if;if month5THENcmonth:AMOUNTMAY;end if;if month6THENcmonth:AMOUNTJUNE;end if;if month7THENcmonth:AMOUNTJULY;end if;if month8THENcmonth:AMOUNTAUGUST;end if;if month9THENcmonth:AMOUNTSEPTEMBER;end if;if month10THENcmonth:AMOUNTOCTOBER;end if;if month11THENcmonth:AMOUNTNOVEMBER;end if;if month12THENcmonth: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 VARCHAR2ISmonth VARCHAR2(100);BEGINmonth: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 VARCHAR2ISyear VARCHAR2(100);BEGINyear: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 VARCHAR2ISmsg VARCHAR2(100);num VARCHAR2(100);BEGINnum: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 VARCHAR2ISmsg VARCHAR2(100);num VARCHAR2(100);Result varchar2(50);y VARCHAR2(100);BEGINnum:substr(num2,2,1);msg:skuassessmennt||num ;y:to_char(add_months(TO_DATE(yearandmonth, YYYY-MM),num2), YYYY);if num1THENselect skuassessmennt1 into Result from uf_accuracyparameters where yeary and departmentdepartmentid and checktypecheckty;end if;if num2THENselect skuassessmennt2 into Result from uf_accuracyparameters where yeary and departmentdepartmentid and checktypecheckty;end if;if num3THENselect skuassessmennt3 into Result from uf_accuracyparameters where yeary and departmentdepartmentid and checktypecheckty;end if;RETURN Result;END getskuassessmenntLvFunc;CREATE OR REPLACE PACKAGE TESTPACKAGE ASTYPE Test_CURSOR IS REF CURSOR;END TESTPACKAGE;CREATE OR REPLACE PROCEDURE test2(typestring IN VARCHAR2) ISBEGININSERT INTO B_ID_temp ( I_ID ,I_NAME)VALUES SELECT I_ID ,I_NAME FROM B_IDEND test2CREATE OR REPLACE PROCEDURE Proc_Insert(P_CURSOR OUT TESTPACKAGE.TEST_CURSOR,yearandmonth IN VARCHAR2,checktypeid IN VARCHAR2,departmentid IN VARCHAR2,curtuerid IN VARCHAR2)ISstr_sql varchar2(4000):;amountmonth varchar2(400):;amountmonth2 varchar2(400):;amountmonth3 varchar2(400):;BEGINamountmonth: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 (SELECTuf1.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 lvFROM UF_MONTHINCOMETEST UF1LEFT JOIN UF_MIDTBMONTHLICBUDGET UF2ON UF1.DEPARTMENT UF2.DEPARTMENTAND UF1.PRODUCTCODE UF1.PRODUCTCODEand uf1.customeruf2.customercodeAND UF1.PROVINCE UF2.PROVINCEAND UF1.AREA UF2.AREAAND UF2.YEAR getyearforNumFunc(||yearandmonth||,0)-1left 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 ) ccon uf1.YEARBUDGETcc.year and uf1.departmentcc.departmentWHERE UF1.DEPARTMENT ||departmentid||AND UF1.YEARBUDGET getyearforNumFunc(||yearandmonth||,-1)and uf1.monthbudgetgetmonthforNumFunc(||yearandmonth||,-1) ) zz) aa;execute immediate str_sql;OPEN P_CURSOR FORSELECT * FROM UF_MONTHASSESSMENTTEMPORARY;END Proc_Insert;DROP TABLE B_ID_tempDROP PROCEDURE TESTCEXEC TESTCSELECT COUNT(*) FROM UF_MONTHASSESSMENTTEMPORARYDELETE FROM UF_MONTHASSESSMENTTEMPORARYcreate 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 rowsSELECT * FROM mytesttempselect * into mytesttemp from UF_ASSESSMENTHISTORYCREATE TABLE UF_ASSESSMENTHISTORY2 AS SELECT * from UF_ASSESSMENTHISTORYSELECT * FROM UF_ASSESSMENTHISTORY2CREATE OR REPLACE PROCEDURE getdatefromtableCREATE 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_XGDROP 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_XGselect count(*) from SFZ_TEST_MANAGER_XG;SELECT * FROM SFZ_TEMP_MANAGER_XGcreate or replace package sfz_objastype 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 asprocedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out sfz_cursor)isBEGININSERT 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_XGselect * from product_component_version;create or replace procedure areaisnum number ;v_areaRecord dual%ROWTYPE;beginselect 12345 into num from dual;end area;begincommit;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)ISstr_sql varchar2(4000):;amountmonth varchar2(400):;amountmonth2 varchar2(400):;amountmonth3 varchar2(400):;BEGINamountmonth: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 (SELECTuf1.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 lvFROM UF_MONTHINCOMETEST UF1LEFT JOIN UF_MIDTBMONTHLICBUDGET UF2ON UF1.DEPARTMENT UF2.DEPARTMENTAND UF1.PRODUCTCODE UF1.PRODUCTCODEand uf1.customeruf2.customercodeAND UF1.PROVINCE UF2.PROVINCEAND UF1.AREA UF2.AREAAND UF2.YEAR getyearforNumFunc(||yearandmonth||,0)-1left 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 ) ccon uf1.YEARBUDGETcc.year and uf1.departmentcc.departmentWHERE UF1.DEPARTMENT ||departmentid||AND UF1.YEARBUDGET getyearforNumFunc(||yearandmonth||,-2)and uf1.monthbudgetgetmonthforNumFunc(||yearandmonth||,-2) ) zz) aa;execute immediate str_sql;OPEN P_CURSOR FORSELECT * FROM UF_MONTHASSESSMENTTEMPORARY;END Proc_Insert2;调用beginProc_Insert3(2014-11,297e57f448a1a8220148a56095613717,8a8ad0a038d53d3a0138d58b16cf006b,111111);commit;end;PROCEDURE 和 function 区别1.procedure 中调用 function 时 function 变量 用||XXXX||, 然而produre中则用||||2.function sql 查询字段的话 字段不可以拼变量DROP TABLE UF_MONTHASSESSMENTTEMPORARYcreate 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_MONTHASSESSMENTTEMPORARYadd primary key (ID);文章评论
