SQL:简洁易懂的进阶教程1（views, stored procedures and transactions）

一、views, stored procedures and transactions

1. views

• 可以从tables, 当前的views选取特定的columns 共同组建新的view
• 一旦创建完成，view可以像table一样被查询
• 只有view的定义被存储，相关data不会占用额外的存储空间

使用value的好处：

创建view的命令：

CREATE VIEW <view name> (<column_alias_1>, <column_alias_2>, ... <column_alias_n>)
AS SELECT <column_1>, <column_2>, ... <column_n>
FROM <table name>
WHERE <predicate>;

举例：

删除view：

DEOP VIWE <view_name>

2. stored procedures

定义：

好处：

语法:

看一个例子：

--#SET TERMINATOR @
CREATE PROCEDURE RETRIEVE_ALL       -- Name of this stored procedure routine

LANGUAGE SQL                        -- Language used in this routine 
READS SQL DATA                      -- This routine will only read data from the table

DYNAMIC RESULT SETS 1               -- Maximum possible number of result-sets to be returned to the caller query

BEGIN 

    DECLARE C1 CURSOR               -- CURSOR C1 will handle the result-set by retrieving records row by row from the table
    WITH RETURN FOR                 -- This routine will return retrieved records as a result-set to the caller query
    
    SELECT * FROM PETSALE;          -- Query to retrieve all the records from the table
    
    OPEN C1;                        -- Keeping the CURSOR C1 open so that result-set can be returned to the caller query

END
@                                   -- Routine termination character

生成了procedure后， 我们想要执行该功能的时候只需要调用一下即可。

CALL RETRIEVE_ALL;      -- Caller query

删除procedure：

DROP PROCEDURE RETRIEVE_ALL;

第二个稍微复杂点的例子，用到了变量的定义：

--#SET TERMINATOR @
CREATE PROCEDURE UPDATE_SALEPRICE ( 
    IN Animal_ID INTEGER, IN Animal_Health VARCHAR(5) )     -- ( { IN/OUT type } { parameter-name } { data-type }, ... )

LANGUAGE SQL                                                -- Language used in this routine
MODIFIES SQL DATA                                           -- This routine will only write/modify data in the table

BEGIN 

    IF Animal_Health = 'BAD' THEN                           -- Start of conditional statement
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE - (SALEPRICE * 0.25)
        WHERE ID = Animal_ID;
    
    ELSEIF Animal_Health = 'WORSE' THEN
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE - (SALEPRICE * 0.5)
        WHERE ID = Animal_ID;
        
    ELSE
        UPDATE PETSALE
        SET SALEPRICE = SALEPRICE
        WHERE ID = Animal_ID;

    END IF;                                                 -- End of conditional statement
    
END
@                                                           -- Routine termination character

3. ACID transactions

定义

ACID：

• 不可分割的工作单位
• 由一到多个sql statements组成
• 要么不发生，要么都发生

语法
三个主要的关键字：

BEGIN
COMMIT
ROLLBACK

例子

通常发生在交易场景。

例子说明：

两个table，分别为个人账户和商店账户。

ros原账户为300元，消费200之后还要消费300，导致系统的SQLCODE返回负数，同样的retcode为负数，所以本次操作取消。所以在begin之后，if之前的update都取消。所有交易都失败。

--#SET TERMINATOR @
CREATE PROCEDURE TRANSACTION_ROSE                           -- Name of this stored procedure routine

LANGUAGE SQL                                                -- Language used in this routine 
MODIFIES SQL DATA                                           -- This routine will only write/modify data in the table

BEGIN

        DECLARE SQLCODE INTEGER DEFAULT 0;                  -- Host variable SQLCODE declared and assigned 0
        DECLARE retcode INTEGER DEFAULT 0;                  -- Local variable retcode with declared and assigned 0
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION           -- Handler tell the routine what to do when an error or warning occurs
        SET retcode = SQLCODE;                              -- Value of SQLCODE assigned to local variable retcode
        
        UPDATE BankAccounts
        SET Balance = Balance-200
        WHERE AccountName = 'Rose';
        
        UPDATE BankAccounts
        SET Balance = Balance+200
        WHERE AccountName = 'Shoe Shop';
        
        UPDATE ShoeShop
        SET Stock = Stock-1
        WHERE Product = 'Boots';
        
        UPDATE BankAccounts
        SET Balance = Balance-300
        WHERE AccountName = 'Rose';

        
        IF retcode < 0 THEN                                  --  SQLCODE returns negative value for error, zero for success, positive value for warning
            ROLLBACK WORK;
        
        ELSE
            COMMIT WORK;
        
        END IF;
        
END
@                                                            -- Routine termination character