SQL范例

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

• 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();

   修改已存在属性的定义

• ALTER TABLE billing_site_info MODIFY ban_number VARCHAR(16);
• ALTER TABLE customer_info MODIFY ban_number VARCHAR(16);

2，存储过程

   创建存储过程

引用

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 ;

   呼叫存储过程

引用

CALL `add_create_modified_date_to_vpdc`;

   删除存储过程

引用

DROP PROCEDURE `add_create_modified_date_to_vpdc`;