数据库系统概论-008： 游标和存储过程

一：嵌入式SQL
1： 术语
    主变量：SQL语句中使用的主语言程序变量。
    输入主变量：应用程序对其赋值，SQL语句引用。
    输出主变量：SQL语句对其赋值或设置状态，返回给应用程序。
    所有主变量和指示变量必须在BEGIN DECLARE SECTION 和 END DECLARE SECTION之间进行说明。
2：游标
    一条SQL语句可以产生多条或处理多条记录。而主语言是面向记录的，一组变量只能存放一条记录。
    游标是系统为用户开设的一个数据缓冲区，存在SQL语句的执行结果，每个游标去都有一个名字。
    用户可以通过游标注意获取记录，并赋给主变量，交由主程序进一步处理。

二：游标操作
    1： 声明游标
        声明一个叫做CustomerCuresor的游标用以查询地址在北京的客户姓名，账号及其余额。
        DECLARE CustomerCuresor CURSOR FOR
         SELECT * FROM acct_no, name, balance
          FROM curstomer
           WHERE province='北京';
    2： 打开游标
        OPEN CustomerCuresor;
    3： 提取数据
        当用OPEN语句打开了游标并在数据库中执行了查询后，您不能立即利用在查询结果集中的数据，您必须用FETCH语句来取得数据。
        一条FETCH语句一次可以将一条记录放入程序员指定的变量中。
        FETCH CustomerCuresor INTO:Is_acct_no, Is_name, Is_balance;
    4： 关闭游标
        CLOSE CustomerCuresor;
    5： 删除游标
        DEALLOCATE  CustomerCuresor;

        //SCROLL表示游标可随意移动（否则只能向前）
        //DYNAMIC表示可以读写游标（否则游标只读）
        DECLARE my_cursor CURSOR SCROLL DYNAMIC FOR SELECT productname FROM product;
        OPEN my_cursor;
        SYSNAME pname;
        FETCH NEXT FROM my_cursor INTO @pname; //下一条
        while(@@fetch_status=0)
         BEGIN
          FETCH NEXT FROM my_cursor INTO @pname;
         END
        FETCH PRIOR FROM my_cursor INTO @pname; //前一条
        FETCH LAST FROM my_cursor INTO @pname; //最后一条
        FETCH FIRST FROM my_cursor INTO @pname; //首条
        CLOSE my_cursor;
        DEALLOCATE my_cursor;

三：存储过程
    1： 块结构
        每次执行都要编译，它不能存储到数据库中，也不能在其他PL/SQL块中调用。
        IF condition THEN
         sequence_of_statements;
        END IF;

        IF condition THEN
         sequence_of_statements1;
        ELSE
         sequence_of_statements2;
        END IF;

        LOOP
         sequence_of_statements;
        END LOOP; 

        WHILE condition LOOP
         sequence_of_statements;
        END LOOP;

        FOR count IN bound1...bound2 LOOP
         sequence_of_statements;
        END LOOP
    2： 存储过程
        被编译后保存在数据库中，可以被反复调用，运行速度较快。
        A： 优点
            不象解释执行的SQL语句那样在提出操作请求时才进行语法分析和优化工作，因而工作效率高。
            存储过程降低了客户机和服务器之间的通讯量。
            方便实施企业规则。可以把企业规则的运算写成存储过程放入数据库服务器中。
    3： 存储过程的使用
        CREATE Procedure 过程名([参数1， 参数2....]) AS <PL/SQL块>；

        如从一个账号转指定数额的款到另一个账户中
        CREATE PROCEDURE transfer(INT inAccount, INT outAccount, FLOAT amount)
         AS DECLARE
          FLOAT totalDeposit;
         BEGIN
          //查询账户余额
          SELECT total INTO totalDeposit FROM ACCOUT WHERE ACCOUNTNUM=outAccount;
          IF totalDeposit IS NULL THEN //账户不存在或没有存款
           ROLLBACK;
           RETURN;
          END IF;
          IF totalDeposit < amount THEN //余额不足
           ROLLBACK;
           RETURN;
          END IF;
          //修改转出账户，减去转出额
          UPDATE account SET total=total-amount WHERE ACCOUNTNUM=outAcount;
          //修改转入账户，增加转出额
          UPDATE account SET total=tatal+amount WHERE ACCOUNTNUM=inAccount;
          COMMIT;
         END;
        执行存储过程：CALL Procedure TRANSFER（01003815868， 01003813828， 10000）；
        删除存储过程：DROP Procedure TRANSFER;