Oracle creation-stored procedures and functions

Oracle creation-stored procedures and functions

-- Create a stored procedure

Create or replace procedure xxxxxxxxxxx_p (-- Parameter IN indicates the input parameter, -- OUT indicates the output parameter, and the type can be of any legal type IN Oracle. Is_ym in char) AS -- defines the variable vs_msg VARCHAR2 (4000); -- error message variable vs_ym_beg CHAR (6); -- start month vs_ym_end CHAR (6 ); -- end month vs_ym_sn_beg CHAR (6); -- vs_ym_sn_end CHAR (6) in the starting month of the same period; -- end month in the same period -- 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 region WHERE ym> = region AND ym <= vs_ym_sn_end GROUP BY area_code, CMC ODE; BEGIN -- assign the initial value to the variable using the input parameter, and use very common functions such as Oralce's SUBSTR TO_CHAR ADD_MONTHS TO_DATE. Substring: = SUBSTR (is_ym, 1, 6); vs_ym_end: = SUBSTR (is_ym, 7, 6); substring: = TO_CHAR (ADD_MONTHS (TO_DATE (vs_ym_beg, 'yymm '), -12), 'yyymm'); vs_ym_sn_end: = TO_CHAR (ADD_MONTHS (TO_DATE (vs_ym_end, 'yyyymm'),-12), 'yyyymm '); -- delete data with specific conditions in the table first. Delete from xxxxxxxxxxx_T WHERE ym = is_ym; -- use the put_line method of the built-in DBMS_OUTPUT object to print the number of affected records, one system variable SQL % rowcount DBMS_OUTPUT.put_line is used ('del last month record = '| SQL % rowcount |' bar '); insert into xxxxxxxxxxx_T (area_code, ym, CMCODE, RMB _amt, usd_amt) SELECT area_code, is_ym, CMCODE, SUM (RMB _amt)/10000, SUM (usd_amt)/10000 FROM region WHERE ym> = vs_ym_beg AND ym <= vs_ym_end GROUP BY area_code, C MCODE; DBMS_OUTPUT.put_line ('ins current month record = '| SQL % rowcount | 'barri'); -- traverses the cursor and updates it to the table. You can use the for statement to traverse a cursor in several ways. 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 indicates any errors except declarations. SQLERRM is a built-in variable that saves detailed information about the current error. Exception when others then vs_msg: = 'error IN xxxxxxxxxxx_p ('| is_ym |'): '| SUBSTR (SQLERRM, 1,500); ROLLBACK; -- Record the current error to 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, totime in Date, isAvailable in number) return number as -- defines the 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) into 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; return free_day; end get_publicholidaytime;

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


Differences:
Database tables or execute some DDL statements), so although their syntax is very similar, the functions that users need to complete when using them are mostly different.