Oracle creation-stored procedures and functions, and oracle creation of Stored Procedures

Source: Internet
Author: User
Tags rowcount

Oracle creation-stored procedures and functions, and oracle creation of Stored Procedures

-- 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:

A stored procedure is generally used to perform specific data operations (such as modifying, inserting database tables, or executing certain DDL statements ), so although their syntax is very similar, the functions that users need to complete when using them are mostly different.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.