PL/SQL - 基础知识(2)

-- PLSQL记录
-- 与3GL中的记录结构相似，与数据库表是两回事
-- 是一个方便的途径FETCH一些行FROM一个表来进行相关处理
declare
    type emp_record_type is record
        (empno number not null:=100, ename emp.ename%type, job　emp.job%type);
    -- 也可以利用原有的表结构：EMP_RECORD EMP%ROWTYPE
    emp_record emp_record_type;
begin
    null;
end;
/

-- 显式游标 - 操纵步骤如下：声明游标、打开游标、从游标中取回数据、关闭游标
DECLARE CURSOR_NAME
IS
SELECT STATMENT

-- 说明游标可以用子查询
DECLARE
　　CURSOR C_NAME
　　IS
　　    SELECT ENAME FROM EMP
　　    WHERE DEPTNO IN 
            (SELECT DEPTNO FROM DEPT WHERE CITY_ID=‘BJ’)

-- 这时游标将它的指针指向活动集的开始，
-- 指针指向第一条记录的前面是因为它还没有执行FETCH命令。
-- 如果试图打开一个已经打开的游标，将出错：
--     ORA-06511：PL/SQL：CURSOR ALREADY OPEN
OPEN CURSOR_NAME;

IF NOT C_NAME%ISOPEN
THEN
　　OPEN C_NAME；
END IF;

-- 从游标中取回数据:
FETCH CURSOR_NAME INTO RECOR-LIST;
-- 关闭游标：
CLOSE CURSOR_NAME

-- ========================================

DECLARE
　　myname varchar2(22);
　　
   CURSOR C_NAME
   IS
　　　　SELECT ENAME FROM EMP;
BEGIN
　　IF NOT C_NAME%ISOPEN
　　THEN
　　    OPEN C_NAME;
　　END IF;
　　
   LOOP
　　　　FETCH c_name into myname;
　　　　　　DBMS_OUTPUT.PUT_LINE(myname);
　　　　EXIT WHEN c_name%notfound;
　　END LOOP;
    
   CLOSE c_name;
END;
/

-- 显式游标的自动化
FOR emp_record in (SELECT * FROM DEPT) loop

-- 这里使用了游标FOR循环，分别隐式进行了游标的打开、FETCH和CLOSE。
DECLARE
　　ii number;
　　
    CURSOR c_name
　　IS
　　    SELECT * FROM EMP order by deptno desc;
　　emp_record c_name%rowtype;
　　
BEGIN
　　ii:=1;
　　FOR emp_record IN c_name LOOP
　　    DBMS_OUTPUT.PUT_LINE(ii);
　　    ii:=ii+1;
　　END LOOP;
END;
/

DECLARE
　　myname varchar2(22);
　　thisdeptno scott.emp.deptno%type;
　　
    CURSOR C_NAME
　　IS
　　    SELECT ENAME,deptno FROM EMP order by deptno desc;
BEGIN
　　IF NOT C_NAME%ISOPEN
　　THEN
　　    OPEN C_NAME;
　　END IF;

　　LOOP
　　FETCH c_name INTO myname,thisdeptno;
　　    DBMS_OUTPUT.PUT_LINE (myname||','||thisdeptno || ',' || TO_CHAR(c_name%rowcount));
    EXIT WHEN c_name%notfound;
　　END LOOP;

　　DBMS_OUTPUT.PUT_LINE ('the Total record is fetched is ' || TO_CHAR(c_name%rowcount));
    
    CLOSE c_name;
END;
/

create or replace PROCEDURE change_salary
　　(v_emp_id IN NUMBER, -- formal parameters
　　v_new_salary IN NUMBER)
IS
BEGIN -- begin PL/SQL block
　　UPDATE emp SET sal = v_new_salary
　　    WHERE empno = v_emp_id;
　　COMMIT;
END change_salary;
/

-- 在SQLPLUS中：
CALL change_salary(7369,9000);
EXECUTE change_salary(7369,9000);

-- 在一个块中，如：
begin
　　change_salary(7369,9000);
end;
/

-- 异常是由ORACLE错误或显式的抛出一个错误产生的
-- 如何处理：用一个处理程序来捕获它；将它传递给CALLING ENVIRONMENT

-- 异常类型：
--     ORACLE SERVER 预定义错误
--     非ORACLE SERVER 预定义错误，但也是ORACLE SERVER 的标准错误
--     用户自定义异常

/*
 * Place the WHEN OTHERS clause after all other exception handling clauses.
 * You can have at most one WHEN OTHERS clause.
 * Begin exception-handling section of the block with the keyword EXCEPTION.
 * Define several exception handlers, each with their own set of actions, for the block.
 * When an exception occurs, PL/SQL will process only one handler before leaving the block.
*/

EXCEPTION
　　WHEN exception1 [OR exception2 . . .] THEN
　　statement1;

PROCEDURE elim_inventory
    　　(v_product_id IN s_product.id%TYPE) 
    IS
　　    v_id s_product.id%TYPE;
　　BEGIN
    　　SELECT id INTO v_id FROM s_product WHERE id = v_product_id;
    　　DELETE FROM s_inventory WHERE product_id = v_product_id;
    　　COMMIT;
    
    EXCEPTION
    　　WHEN NO_DATA_FOUND THEN
    　　    ROLLBACK;
    　　    TEXT_IO.PUT_LINE(TO_CHAR(v_product_id)||’ is invalid.’);
    　　WHEN TOO_MANY_ROWS THEN
    　　    ROLLBACK;
    　　    TEXT_IO.PUT_LINE(’Data corruption in S_PRODUCT.’);
    　　WHEN OTHERS THEN
    　　    ROLLBACK;
    　　    TEXT_IO.PUT_LINE(’Other error occurred.’);
　　END elim_inventory;

-- 使用non-predefined Oracle Server error
DECLARE
　　E_PRO　 EXCEPTION;
　　PRAGMA EXCEPTION_INIT(E_PRO,ERROR_NUMBER);
BEGIN
　　......
EXCEPTION
　　WHEN E_PRO THEN
　　    DBMS_OUTPUT.PUT_LINE('ASDLKFJKASDJFASJDFLKASDF');
　　    ......
END;

/*
 * SQLCODE ----Returns the numeric value for the error code. You can assign it to a NUMBER variable.
 * SQLERRM ----Returns character data conta
*/