PL/SQL program Unit

PL/SQL Program unit:

• /** Insert a data record to tab_stu In the first Stored procedure **/create or replace procedure firstPro -- is equivalent to the preceding declare statement variable is v_stu_age number: = 23 after is; begin insert into tab_stu (stu_id, stu_name, stu_age, class_id) values (5, 'defam', v_stu_age, 3); commit; end firstPro;

  
  

• /** Stored procedures with parameters **/create or replace procedure paramPro (-- by default, the input parameters v_stu_id varchar2, v_stu_name varchar2, v_stu_age number, v_class_id number) isbegin insert into tab_stu (stu_id, stu_name, stu_age, class_id) values (v_stu_id, v_stu_name, v_stu_age, v_class_id); commit; end paramPro;

  
  

• /** The stored procedure is more complex work requirements such as data integration on the database side: the idea of backing up the tab_stu table at the specified time every day is as follows: 1. back up the data that was not backed up last time. 2. You need an id to store the last backup. 3. After the backup, you need to modify the id value. 4. you also need to have a backup table **/create or replace procedure backPro (-- the last idmax_id number of the last backup) is -- stored with a cursor, cursor c_tab_stu is select * from tab_stu where stu_id> max_id; -- defines rowtyper_tab_stu tab_stu % rowtype; -- stores the value of the largest id after backup v_max_id number; -- index value v_index number: = 0; begin if c_tab_stu % isopen then null; else open c_tab_stu; end if; -- loop backup loop fetch c_tab_stu into r_tab_stu; exit when c_tab_stu % notfound; insert into tab _ Stu_back (stu_id, stu_name, stu_age, class_id) values (r_tab_stu.stu_id, r_tab_stu.stu_name, r_tab_stu.stu_age, r_tab_stu.class_id);/** write as follows: 1. if the data volume is large, one-time commit or insert one commit. They are all bad. 1. 1. One-time submission, because Oracle stores the rollback segments in the memory. If the data volume is large, the memory may be faulty.. If you insert a record and submit it once, the problem above will disappear. But the performance is very poor. 2. Let it commit every 2000 inserts. 3. Remember to return to zero here. **/If v_index> 2000 then commit; v_index: = 0; else v_index: = v_index + 1; end if; end loop; commit; close c_tab_stu; -- modify the value of max_id of tab_max_id to select max (stu_id) into v_max_id from tab_stu_back; update tab_max_id set max_id = v_max_id; commit; end backPro;

  
  

• -- Call another stored procedure through one stored procedure create or replace procedure invockParamisv_max_id number; begin select max_id into v_max_id from tab_max_id; -- call the backpro Stored procedure backpro (v_max_id); end invockParam;

  You only need to execute the above stored procedure to back up the data.

• -- Create the scheduling task timer declare jobno number; -- this can be customized as begin sys. dbms_job.submit (jobno, what => 'invockparam; ', -- invockParam is the name of the stored procedure Interval => 'trunc (sysdate, ''mi '') + 1/(24*60) '-- defines the interval per minute); commit; end;

  You can also use PL/SQL developer to create a scheduled task, or right-click DBMS_Jobs on the left to create a task. In the displayed window, view the SQL statement. Enter the above content. Run with a small gear.