Review Oracle stored procedures and timers

I haven't written a stored procedure for a long time. In the past few days, the stored procedure has just been used in the project. I didn't expect it to be remembered at all. I just finished it by turning over the previously recorded materials.

-- Create a stored procedure and input two parameters. In indicates the input parameters, and the returned parameters are represented by out.

Create or replace procedure count1 (starttime in varchar2, endtime in varchar2)
-- Receive data with a cursor
Cursor mycu is select T1. *, b1.name
From (
Select p. duty, sum (P. invest) as num_ztz, sum (B. accumulatinvest) as num_ljwc, sum (B. planinvest) as num_dn1_, sum (B. completedinvest) as num_dnwc,

Sum (B. monthinvest) as num_dywc, count (P. ID) as lj_xm,
Count (case when (s2.name = 'already in production' or s2.name = 'already in production' or s2.name = 'already in production' or s2.name = '') then p. ID end) as lj_kg,

Count (case when S. Name = 'new' then P. ID end) as xj_xm,
Count (case when S. name = 'new' and (s2.name = 'already put into production' or s2.name = 'already started' or s2.name = 'finished 'or s2.name = 'preemptible') then p. ID end) as xj_kg,

Count (case when S. Name = 'resumable' then P. ID end) as xjs_xm,
Count (case when S. name = 'continued builder' and (s2.name = 'put into production 'or s2.name = 'started' or s2.name = 'finished 'or s2.name = 'trial run') then p. ID end) as xjs_kg

From pro_project P left join pro_build B on B. proid = P. id left join pro_basevalue s on (S. id = P. protype) left join pro_basevalue S2 on s2.id = P. prostatus left join pro_basevalue S3 on s3.id = P. duty

Where delstate is null and S. Name! = 'Talking About' and S. Name! = 'Followed by 'and to_char (P. dynamictime, 'yyyy-mm')> = starttime and to_char (P. dynamictime, 'yyyy-mm') <= endtime

Group by P. Duty, s3.priority order by s3.priority
) T1 left join pro_basevalue B1 on b1.id = t1.duty;
Per_ndwc _ number (16, 4): = 0;
Per_wcztz _ number (16, 4): = 0;
Lj_kgl _ number (16, 4): = 0;
Xj_kgl _ number (16, 4): = 0;
Xjs_kgl _ number (16, 4): = 0;
Begin
For Cu IN mycu-there are other recycling methods to start the loop. I personally think this is more convenient.
Loop
If cu. num_dn1! = 0 then
Per_ndwc _: = Cu. num_dnwc/Cu. num_dnwc;
End if;
If cu. num_ztz! = 0 then
Per_wcztz _: = Cu. num_ljwc/Cu. num_ztz;
End if;
If cu. lj_xm! = 0 then
Lj_kgl _: = Cu. lj_kg/Cu. lj_xm;
End if;
If cu. xj_xm! = 0 then
Xj_kgl _: = Cu. xj_kg/Cu. xj_xm;
End if;
If cu. xjs_xm! = 0 then
Xjs_kgl _: = Cu. xjs_kg/Cu. xjs_xm;
End if;
Insert into pro_statement (ID, num_ztz, num_ljwc, num_dnjh, num_dnwc, num_dywc, per_ndwc, per_wcztz,
Lj_xm, lj_kg, lj_kgl, xj_xm, xj_kg, xj_kgl, xjs_xm, xjs_kg, xjs_kgl, create_time, data_type, tname)
Values (seq_pro_statement.nextval, Cu. num_ztz, Cu. num_ljwc, Cu. num_dnjh, Cu. num_dnwc, Cu. num_dywc, per_ndwc _, per_wcztz _,
CU. lj_xm, Cu. lj_kg, lj_kgl _, Cu. xj_xm, Cu. xj_kg, xj_kgl _, Cu. xjs_xm, Cu. xjs_kg, xjs_kgl _, sysdate, '1', Cu. name );
End loop;
End;

-- It is also a stored procedure. Two parameters are returned. One is the cursor type and the other is the data column number (because the number of columns is dynamic, the number of Columns cannot be determined when the value is set in resultset)

Create or replace procedure execbyjb_type (synch_cursor out synch. synch_cursor, column_count out number)
Cursor mycu is select jb_type from rep_synch group by jb_type;
Jb_type _ varchar2 (50): = '';
SQL _ varchar2 (2000): = 'select to_char (to_date (sj_time, ''yyyy-mm-dd hh24: MI: s''), ''mm '') month ';
Begin
For Cu IN mycu
Loop
Jb_type _: = Cu. jb_type;
Column_count: = mycu % rowcount;
SQL _: = SQL _ | ', count (case when jb_type = ''' | jb_type _ | ''' then ID end)' | jb_type _;
End loop;
SQL _: = SQL _ | ', count (ID) Total from rep_synch group by to_char (to_date (sj_time, ''yyyy-mm-dd hh24: MI: SS ''), ''m'') order by to_char (to_date (sj_time, ''yyyy-mm-dd hh24: MI: s ''), ''m '')';
Open synch_cursor for SQL _;
End;

// Call the stored procedure in Java code

Callablestatement cs = NULL;
Try {

String SQL = "{call pro (?,?) }";
Cs = conn. preparecall (SQL );
// Call the stored procedure. There are two returned values. The first returns the cursor and the second returns the number of columns.

CS. registeroutparameter (1, Oracle. JDBC. oracletypes. cursor );
CS. registeroutparameter (2, Oracle. JDBC. oracletypes. number );
Result=cs.exe cute ();

Int COUNT = cs. getbigdecimal (2). intvalue ();
Rs = (resultset) CS. GetObject (1 );
Resultsetmetadata metadata = Rs. getmetadata ();
While (RS! = NULL & Rs. Next ()){
Map <string, Object> map = new hashmap <string, Object> ();
For (INT I = 1; I <= count + 2; I ++ ){
Map. Put (metadata. getcolumnname (I), RS. GetObject (I); // use the current column name as the key and the current value as the value to save the current row
Columns. Add (metadata. getcolumnname (I); // obtain the column name of the current column
}
List. Add (MAP); // put a whole row of complete data into the list.
}
}

// Finally, the column name and dataset are returned, and you only need to loop in the foreground.

-------------------------- The following part is written in detail from the http://www.cnblogs.com/mingforyou/archive/2012/06/06/2538063.html

Declare

Jobno number;

Begin dbms_job.submit (

Jobno, -- timer ID, automatically obtained by the System

'Prc _ insert; ', -- what is the name of the execution process

Sysdate, -- next_date, the time when the timer starts to execute, so that write indicates immediate execution

'Sysdate + 100' -- interval, set the timer execution frequency, so that the write is executed every 15 minutes.

);

Commit;

End;

Here, the first parameter is the task number, which is automatically assigned a value. You can also use isubmit to manually specify

The second parameter is the task process to be executed. If the code is long, you can write it into a stored procedure and put it in it for calling, such as 'Pro _ test; '(pro_test is assumed to be a stored procedure name)

The third parameter is the time when the automatic task is executed for the first time. If you want it to be executed immediately, use sysdate

For the last parameter, the system specifies the next execution time based on the value of this parameter.

Declare

Jobno

Number;

Begin

Dbms_job.remove (45 );

Commit;

End;

Exec dbms_job.remove (83); -- deletes a timer.
Exec dbms_job.run (84); -- run a timer
Exec dbms_job.broken (83, SYS. diutil. int_to_bool (1); -- stop a timer
Exec dbms_job.interval (84, 'sysdate + 100'); -- change the execution frequency of a timer to execute every hour.

Select * From user_jobs; -- view the scheduling task

Select * From dba_jobs_running; -- view the ongoing scheduling task

Select * From dba_jobs; -- view the completed scheduling task

Description of timer parameters:

The myjob parameter is the binary_ineger returned by the submit () process. This value uniquely identifies a job;

What parameter is the PL/SQL code block to be executed. Here it refers to a stored procedure. Pay attention to the semicolon following the name;

The next_date parameter specifies when the job will be run. You can leave this value unspecified when writing a job;

The interval parameter indicates when the job will be re-executed.

For more information about Interval Settings, see the following examples:

1. Execution per minute

Interval => trunc (sysdate, 'mi') + 1/(24*60)

2. daily scheduled execution

Example: Execute at every day

Interval => trunc (sysdate) + 1 + 2/(24)

3. weekly scheduled execution

Example: Execute at every Monday

Interval => trunc (next_day (sysdate, 2) + 2/24 -- Monday, the second day of a week

4. scheduled monthly execution

For example, the task is executed at on the first day of every month.

Interval => trunc (last_day (sysdate) + 1 + 2/24

5. Periodical execution on a quarterly basis

For example, the statement is executed at on the first day of each quarter.

Interval => trunc (add_months (sysdate, 3), 'q') + 2/24

6. scheduled execution every six months

For example, at a.m. on January 1, July 1 and January 1, January 1

Interval => add_months (trunc (sysdate, 'yyyy'), 6) + 2/24

7. Regular annual execution

For example, it is executed at on January 1, January 1 every year.

Interval => add_months (trunc (sysdate, 'yyyy'), 12) + 2/24