SQL范例

1，在一已经存在的表里，插入新属性，附初始值

[list]
[*]alter table `cloudmw`.`vpdc_detail` add column `status_change_date` datetime NULL after `last_update_date`;
[*]update `cloudmw`.`vpdc_detail` set `status_change_date`= now();
[/list]

修改已存在属性的定义
[list]
[*]ALTER TABLE billing_site_info MODIFY ban_number VARCHAR(16);
[*]ALTER TABLE customer_info MODIFY ban_number VARCHAR(16);
[/list]

2，存储过程

创建存储过程
[quote]DELIMITER $$

DROP PROCEDURE IF EXISTS `add_create_modified_date_to_vpdc`$$
CREATE
PROCEDURE `add_create_modified_date_to_vpdc`()
BEGIN
DECLARE vpdc_detail_id INT;
DECLARE create_date datetime;
DECLARE last_update_date datetime;
DECLARE source, target VARCHAR(255);
DECLARE record_not_found INT DEFAULT 0;
DECLARE record_count INT DEFAULT 0;

DECLARE cursor_vpdc_detail CURSOR FOR
SELECT vd.id, vdFirst.create_date, vdLast.last_update_date FROM vpdc_detail vd, vpdc_detail vdFirst, vpdc_detail vdLast
WHERE vdFirst.id = (select id from vpdc_detail where vpdc_id = vd.vpdc_id order by id LIMIT 1)
and vdLast.id = (select id from vpdc_detail where vpdc_id = vd.vpdc_id order by id DESC LIMIT 1)
and vd.vpdc_status != 'Archived';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1;
OPEN cursor_vpdc_detail;
updateRecords: LOOP
FETCH cursor_vpdc_detail INTO vpdc_detail_id, create_date, last_update_date;
IF record_not_found THEN
LEAVE updateRecords;
END IF;
SET source = '</schemaVersion>';
SET target = CONCAT('</schemaVersion>\n<createDate>',DATE_FORMAT(create_date, '%a %e %b %Y %T %+0000'),'</createDate>\n<modifiedDate>', DATE_FORMAT(last_update_date, '%a %e %b %Y %T %+0000'), '</modifiedDate>\n');
UPDATE vpdc_detail SET
transient_yfiles=REPLACE(transient_yfiles, source, target),
gui_xml=REPLACE(gui_xml, source, target)
WHERE id = vpdc_detail_id;
SET record_count = record_count + 1;
END LOOP updateRecords;
CLOSE cursor_vpdc_detail;
SELECT CONCAT('Number of records updated : ', record_count) STATUS;
END$$

DELIMITER ;[/quote]

呼叫存储过程
[quote]CALL `add_create_modified_date_to_vpdc`;
[/quote]
删除存储过程
[quote]DROP PROCEDURE `add_create_modified_date_to_vpdc`; [/quote]