SQL & PL/SQL

Hi!

I am required to write a procedure, that when called , take in the value of the schema, tablename and date/partition name, and drop partitions according the input typed in.

The script below ( Just for the sake of example) describes the kind of tables Im talking about.

SQL> CREATE TABLE SYS.TEST
2 (
3 DATETIME DATE,
4 BSC VARCHAR2(50 BYTE),
5 CELL VARCHAR2(35 BYTE)
6 )
7 TABLESPACE TEST
8 PCTUSED 0
9 PCTFREE 40
10 INITRANS 255
11 MAXTRANS 255
12 NOLOGGING
13 PARTITION BY RANGE (DATETIME)
14 (
15 PARTITION P20100823 VALUES LESS THAN (TO_DATE(' 2010-08-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
16 NOLOGGING
17 NOCOMPRESS
18 TABLESPACE TEST
19 PCTFREE 40
20 INITRANS 255
21 MAXTRANS 255
22 STORAGE (
23 INITIAL 128K
24 NEXT 128K
25 MINEXTENTS 1
26 MAXEXTENTS 2147483645
27 PCTINCREASE 0
28 BUFFER_POOL DEFAULT
29 ),
30 PARTITION P20100824 VALUES LESS THAN (TO_DATE(' 2010-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
31 NOLOGGING
32 NOCOMPRESS
33 TABLESPACE TEST
34 PCTFREE 40
35 INITRANS 255
36 MAXTRANS 255
37 STORAGE (
38 INITIAL 128K
39 NEXT 128K
40 MINEXTENTS 1
41 MAXEXTENTS 2147483645
42 PCTINCREASE 0
43 BUFFER_POOL DEFAULT
44 ),
45 PARTITION P20100824 VALUES LESS THAN (TO_DATE(' 2010-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
46 NOLOGGING
47 NOCOMPRESS
48 TABLESPACE TEST
49 PCTFREE 40
50 INITRANS 255
51 MAXTRANS 255
52 STORAGE (
53 INITIAL 128K
54 NEXT 128K
55 MINEXTENTS 1
56 MAXEXTENTS 2147483645
57 PCTINCREASE 0
58 BUFFER_POOL DEFAULT
59 )
60 )
61 ;

Table created.


A procedure that could help drop old partitions within given number of days or till the partition name. Either I could use the procedure to drop partitions older then '2010-08-25' or till 'PARTITION P20100824 '


Please find below an example of such procedure in mySQL;

CREATE
OR REPLACE PROCEDURE SYS.partition_drop ( through_date date, tbl varchar(64), db varchar(64))
BEGIN
DECLARE delete_me varchar(64);
DECLARE notfound BOOL DEFAULT FALSE;
DECLARE pname CURSOR FOR SELECT PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=tbl AND TABLE_SCHEMA=db
AND DATE(PARTITION_NAME)!= 0
AND DATE(PARTITION_NAME) IS NOT NULL
AND DATE(PARTITION_NAME)<=through_date;
　
　
DECLARE CONTINUE HANDLER FOR NOT FOUND SET notfound:=TRUE;
OPEN pname;
　
　
cursor_loop
: LOOP
FETCH pname INTO delete_me;
IF notfound THEN LEAVE cursor_loop; END IF;
SET @alter_stmt:=CONCAT("ALTER TABLE ",db,".",tbl," DROP PARTITION ",delete_me);
　
　
# sanity
check commented out for production use
#
SELECT @alter_stmt;
PREPARE stmt_alter FROM @alter_stmt; EXECUTE stmt_alter; DEALLOCATE PREPARE stmt_alter;
　
　
END LOOP;
CLOSE pname;
END ||


Any help will be highly appriciated. Thanks in advace.