Oracle creation-Stored procedures and functions

--Create a stored procedure

CREATE OR REPLACE PROCEDURE xxxxxxxxxxx_p (--parameter in represents the input parameter,                                          The--out represents an output parameter, and the type can use a legitimate type in any Oracle. Is_ym in CHAR) as--Define variable vs_msg VARCHAR2 (4000); --Error message variable Vs_ym_beg CHAR (6); --Starting month Vs_ym_end CHAR (6); --End Month Vs_ym_sn_beg CHAR (6); --Vs_ym_sn_end CHAR (6) for the same period of the month; --end of month--Define a cursor (simply a result set that can be traversed) cursor cur_1 is SELECT area_code, Cmcode, SUM (Rmb_amt)/10000       RMB_AMT_SN, SUM (Usd_amt)/10000 usd_amt_sn from bgd_area_cm_m_base_t WHERE ym >= Vs_ym_sn_beg and YM <= Vs_ym_sn_end GROUP by Area_code, Cmcode;  BEGIN--Assigning an initial value to a variable with input parameters, uses a function commonly used by Oralce substr To_char add_months to_date.  Vs_ym_beg: = SUBSTR (Is_ym, 1, 6);  Vs_ym_end: = SUBSTR (Is_ym, 7, 6);  Vs_ym_sn_beg: = To_char (Add_months (To_date (Vs_ym_beg, ' yyyymm '), -12), ' yyyymm '); Vs_ym_sn_end: = To_char (Add_months (To_date (vs_ym_end, ' yyyymm '), -12), ' yyyymm ');  --First delete the data for a specific condition in the table.  DELETE from xxxxxxxxxxx_t WHERE ym = Is_ym; --then prints the number of rows affected by the Put_Line method of the built-in Dbms_output object, which uses a system variable Sql%rowcount dbms_output.put_line (' del last month record = ' | | Sql%rowcount | |  ' article '); INSERT into xxxxxxxxxxx_t (Area_code, YM, Cmcode, Rmb_amt, Usd_amt) SELECT Area_code, Is_ym, CMC ODE, SUM (rmb_amt)/10000, SUM (USD_AMT)/10000 from bgd_area_cm_m_base_t WHERE ym >= vs_y  M_beg and YM <= Vs_ym_end GROUP by Area_code, Cmcode; Dbms_output.put_line (' ins month record = ' | | Sql%rowcount | |  ' article '); --Update to table after traversing cursor processing.  There are several ways to traverse a cursor, which is more intuitive with a for statement.  For rec in cur_1 LOOP UPDATE xxxxxxxxxxx_t SET rmb_amt_sn = rec.rmb_amt_sn, usd_amt_sn = Rec.usd_amt_sn WHERE Area_code = Rec.area_code and Cmcode = Rec.  Cmcode and ym = Is_ym;  END LOOP;  COMMIT; --Error Handling section. Others represents an arbitrary error except for declarations. SQLERRM is a system built-in variable that holds the details of the current error. EXCEPTION when OTHERS and vs_msg: = ' ERROR in xxxxxxxxxXx_p (' | | | is_ym | |              '):' ||    SUBSTR (SQLERRM, 1, 500);    ROLLBACK;    --Record the current error into the log table.    INSERT into Log_info (Proc_name, Error_info, op_date) VALUES (' xxxxxxxxxxx_p ', vs_msg, sysdate);    COMMIT; RETURN; END;

--Create a function

Create or Replace function Get_publicholidaytime (fromtime in Date, Toti Me in Date, isavailable in number) return number AS--Define variable Free_  Day number: = 0;  MinDate Date;  MaxDate Date;  Total number: = 0;  FromDate Date: = To_date (To_char (fromtime, ' yyyy/mm/dd '), ' yyyy/mm/dd ');      ToDate Date: = To_date (To_char (totime, ' yyyy/mm/dd '), ' yyyy/mm/dd '); Begin--IF Statement if isavailable = 1 then--sql statement Select min (calendar_date), Max (calendar_date), COUNT (calendar_date) to MinDate, MaxDate, total from T_bi_    Tnt_date where calendar_date between FromDate and todate and is_public_holiday = 1;      If mindate = FromDate then total: = TOTAL-1;    Free_day: = Free_day + ((mindate + 1)-fromtime);    End If;      If maxdate = ToDate then total: = TOTAL-1;    Free_day: = Free_day + (totime-maxdate);    End If;   If mindate = MaxDate Then   Free_day: = Totime-fromtime;    else Free_day: = Free_day + total;  End If;  End If; return free_day;end Get_publicholidaytime;

Related documents
Oracle stored procedure creation and invocation: http://www.cnblogs.com/chinafine/articles/1776094.html


The difference between the two:

It is used to calculate and return a calculation and the stored procedure is typically used to complete specific data operations (such as modifying, inserting database tables, or executing certain DDL statements, etc.), so although they are syntactically similar, the functionality that users need to do when they use them is mostly different.

Oracle creation-Stored procedures and functions