Compiling the basic structure of Oracle stored procedure and testing and execution of PLSQL

Create or replace procedure merge (v_phone_number in varchar2, v_tone_id in varchar2, v_tone_code in varchar2, v_tone_name in varchar2, v_tone_type in number, v_num out varchar2, -- serial number v_iswin out number, -- whether to win 0 not yet, 1 wins v_gift out number, and so on.) Is v_this_month varchar2 (20); -- v_black_count number this month; -- whether or not v_gift_rate1 number is in the blacklist; -- number of first prizes v_gift_rate2 number; -- number of second prizes v_gift_rate3 number; -- number of 4th prize v_gift_rate5 number; -- number of 5th Prize; -- maximum number of 4th prize daily v_gift_rate5_dmax number; -- maximum number of 5th prize daily v_temp_count number; v_temp_amount number; v_temp_num number; type t_varray is varray (2) of number; Type t_v Array_varray is varray (21) of t_varray; v_var t_varray_varray: = t_varray_varray (t_varray (1, 5), t_varray (6, 10); -- errno number is used for 2D arrays; -- Exception Code errterm varchar2 (1000); -- exception information begin -- v_this_month: = to_char (sysdate, 'yyyymm'); v_gift_rate1: = 1; v_gift_rate2: = 3; v_gift_rate3: = 15; v_gift_rate4: = 60; v_gift_rate5: = 600; v_gift_rate4_dmax: = 2; -- 4 and other awards daily quota v_gift_rate5_dmax: = 20; -- 5 and other awards daily quota -- Raise_application_error ('-20002', 'you can not change the data! '); -- You can take the initiative to throw an exception -- to control the number of wins, you need to create another winning information statistics table. -- If you win the prize, modify the winning record table select Ceil (dbms_random.value (0, 10000000) | Ceil (dbms_random.value (0, 8) | Ceil (dbms_random.value (0, 8 )) | Ceil (dbms_random.value (0, 8) into v_random_num from dual; -- random if v_gift = 1 then select T. one into v_temp_count from t_rbt_gift9_result t for update; If v_temp_count <v_gift_one then update t_rbt_gift9_result t set T. one = (T. one + 1) where T. id = 1; -- 1 prize random code select Ceil (dbms_random.value (0, 10000000) | '000000' into v_random_num from dual; else v_gift: = 0; end if; insert into values (ID, phone_number, gift, create_time, random_num) values (values, v_phone_number, v_gift, sysdate, v_random_num); elsif v_gift = 2 then ...... end if; commit; Exception -- Exception Handling when others then errno: = sqlcode; errterm: = sqlerrm; begin rollback; insert into t_rbt_job_logs values (seq_job_log.nextval, errno, errterm, 'P _ gift8_lottery ', sysdate); v_gift: = 0; -- 0 prize v_result: = '000000'; select Ceil (dbms_random.value (0, 100003 )) | Ceil (dbms_random.value (0, 8) | Ceil (dbms_random.value (0, 8) | Ceil (dbms_random.value (0, 8) into v_random_num from dual; -- unwon random code insert into t_rbt_gift9_info (ID, phone_number, gift, create_time, random_num) values (values, v_phone_number, v_gift, sysdate, v_random_num); Commit; end limit;

Declare v_phone_number varchar2 (20); v_tone_id varchar2 (20); v_tone_code varchar2 (20); v_tone_name varchar2 (20); v_tone_type number; v_num varchar2 (20); -- v_iswin number; -- whether or not to win 0 not yet, 1 to win v_gift number; -- a few prizes. I number: = 0; beginv_phone_number: = '000000'; v_tone_id: = '000000'; v_tone_code: = '000000'; v_tone_name: = 'aab'; v_tone_type: = 3; for I in 1 .. 1000 loop/* Run your pro */example (v_phone_number, v_tone_id, v_tone_code, v_tone_name, v_tone_type, v_num, v_iswin, v_gift);/* dbms_output.put_line ('vnum: '| v_num); dbms_output.put_line ('v _ iswin:' | v_iswin); dbms_output.put_line ('v _ gift: '| v_gift ); dbms_output.put_line ('=================' | I ||' ============== '); */end loop; end;