A simple Oracle Stored Procedure

This stored procedure creates and deletes tables and applies them to the date functions sysdate, add_months,

PL/SQL devoleper 8.0 Development

 

Create or replace procedure p_create_history_table

Is

V_ SQL string (31000): = '';

Begin

For I in 1 .. 2

Loop

Declare num number;

Begin select count (1) into num from user_tables where table_name = 'onoffhist _ '| to_char (add_months (sysdate, I), 'yyymmm ');

If num> 0

Then execute immediate 'drop table onoffhist _ '| to_char (add_months (sysdate, I), 'yyyymm ');

End if;

End;

V_ SQL: = v_ SQL |'

Create Table dtpgsis_onoff.onoffhist _ '| to_char (add_months (sysdate, I), 'yyyymm ')

| '(ID number (10, 0) not null enable,

SN number (*, 0 ),

Datetime varchar2 (20 ),

Maxvalue float (1, 126 ),

Minvalue float (126 ),

Value float (126 ),

Constraint onoffhist _ '| to_char (add_months (sysdate, I), 'yyymmm') | 'Primary key (ID)

Using index pctfree 10 initrans 2 maxtrans 255

Storage (initial 65536 next 1048576 minextents 1 maxextents 2147483645

Pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)

Tablespace "dtpgsis_onoff" enable

)';

-- Dbms_output.put_line (v_ SQL );

Execute immediate v_ SQL;

End loop;

Commit;

End;

 

If you encounter permission problems, refer to this article to solve the http://www.itpub.net/viewthread.php? Tid = 413853 & extra = & page = 1