PL/SQL学习笔记（一）

 

 

Lesson 1

variable : scalar、composite、reference、LOB

定义：

identifier [CONSTANT] datatype [not null]
    [:= | DEFAULT expr];

类型：

Char(len)、varchar2(len)、Long、Long RAW、Number(prec, scale)、Binary_number、PLS_Integer、Boolean
Date、TimeStamp、Timestamp with time zone...

%TYPE 取变量的类型，如：`Table.column%TYPE

DBMS_OUTPUT.PUT_LINE用来输出，可能需要先设置SET SERVEROUTPUT ON

:variable取引用变量的值

Lesson 2

  • TOCHAR
  • TO_DATE
    e.g. TO_DATE('12-Jan-2001', 'DD-MON-YYYY');
  • TO_NUMBER

Lesson 3

INTO子句把Select结果存到变量里

Merge 实例：

DECLARE
    v_empno employees.employee_id%TYPE := 100;
BEGIN
    MERGE INTO copy_emp c
    USING employees e
    ON (e.employee_id = v_empno)
    WHEN MATCHED THEN
    UPDATE SET
        c.first_name = e.first_name,
        c.last_name = e.last_name,
        c.email = e.email,
        . . .
    WHEN NOT MATCHED THEN
        INSERT VALUES(e.employee_id, e.first_name, e.last_name,
            . . ., e.department_id);
END;

命名规范：变量和列名一般不取同名；局部变量和形参优先于数据库的表名；数据库的列名优先于局部变量

commit和rollback可以结束会话

Lesson 4: 控制流

控制流

  1. IF

    IF-THEN-END
    IF-THEN-ELSE-END
    IF-THEN-ELSIF-END

  2. CASE

    CASE selector
    WHEN exp1 THEN res1
    WHEN exp2 THEN res2
        ...
    [ELSE resN;]
END;

  3. NULL
    NULL涉及的逻辑运算如果不能凭另一方单独决定结果，结果也为NULL

  4. LOOP

    --普通循环
LOOP
    stat1;
    EXIT [WHEN condition];
END LOOP;

--WHILE循环
WHILE condition LOOP
    statement1;
    statement2;
END LOOP;

--FOR 循环
--for的counter在循环外不可访问
FOR counter IN [REVERSE]
    lower_bound..upper_bound LOOP
    stat1;
    ....
END LOOP;

    利用标签退出多重循环

    BEGIN
    <<Outer_loop>>
    LOOP
        v_counter := v_counter+1;
    EXIT WHEN v_counter>10;
        <<Inner_loop>>
        LOOP
            ...
            EXIT Outer_loop WHENtotal_done = 'YES';
            -- Leave both loops
            EXIT WHEN inner_done = 'YES';
            -- Leave inner loop only
            ...
        END LOOPInner_loop;
        ...
    END LOOPOuter_loop;
END;

Lesson 5: Record

  • Record，类似于结构体

    --Syntax:
TYPE type_name IS RECORD
    (field_declaration[, field_declaration]...);
identifier type_name;

--field_declaration
field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [[NOT NULL] {:= | DEFAULT} expr]

    其中用到的%ROWTPE是取该表的一行的所有类型出来

  • INDEX BY, 类似于数组

    TYPE type_name IS TABLE OF
    {column_type | variable%TYPE
    | table.column%TYPE} [NOT NULL]
    | table.%ROWTYPE
    [INDEX BY BINARY_INTEGER];

    INDEX BY 表有如下属性
    EXISTS COUNT FIRST and LAST PRIOR NEXT TRIM DELETE

还可以有包含Record的INDEX BY表，示例：

SET SERVEROUTPUT ON
DECLARE
    TYPE emp_table_type is table of
        employees%ROWTYPE INDEXBY BINARY_INTEGER;
    my_emp_table emp_table_type;
    v_count NUMBER(3):= 104;
BEGIN
    FOR i IN 100..v_count
    LOOP
        SELECT * INTO my_emp_table(i) FROM employees
        WHERE employee_id = i;
    END LOOP;
    FOR i IN my_emp_table.FIRST..my_emp_table.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name);
    END LOOP;
END;

Lesson 6: Cursor

Cursor定义:

Cursor cursor_name IS
    select_statement;

Cursor使用的步骤:

1. OPEN cursor_name;
2. FETCH cursor_name into [var1, var2, ...] | record_name; （一般写循环处理）
3. CLOSE cursor_name;

Cursor的属性

AttributeDescription
%ISOPEN 是否已打开
%NOTFOUND 是否最近一条查询无返回数据
%FOUND 是否最近一条查询有返回
%ROWCOUNT 返回的行数

Example 1：

DECLARE
    Cursor emp_cursor IS
        SELECT last_name, department_id
        FROM employees;
    FOR emp_record IN emp_curosr LOOP
        IF emp_record.department_id = 80 THEN
            ...
    END LOOP;
END;

Example 2：

DECLARE
    v_empnoemployees.employee_id%TYPE;
    v_enameemployees.last_name%TYPE;
    CURSOR emp_cursor IS
        SELECT employee_id, last_name
        FROM employees;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_empno, v_ename;
        EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
            emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)
            ||'' || v_ename);
    END LOOP;
    CLOSE emp_cursor;
END ;

Lesson 7: Cursor

  • 带参数的Cursor，就可以在打开的时候制定不同的参数
DECLARE
    CURSOR emp_cursor
    (p_deptno NUMBER, p_job VARCHAR2) IS
        SELECT employee_id, last_name
        FROM employees
        WHERE department_id = p_deptno
        AND job_id = p_job;
BEGIN
    OPEN emp_cursor (80, 'SA_REP');
    . . .
    CLOSE emp_cursor;
    OPEN emp_cursor (60, 'IT_PROG');
    . . .
END;
  • FOR UPDATE子句，用于Cursor的时候是把查询的项锁定了，用于日后可能用Cursor来做更新操作
SELECT ...
FROM
FOR UPDATE [OF column] [NOWAIT];
  • WHERE CURRENT OF子句
    用Cursor来更新
Cursor sal_cusror IS ...

FOR emp_record IN sal_cursor
LOOP
    IF emp_record.salary < 5000 THEN
        UPDATE xxx
        SET salary = emp_record.salary * 1.1
        WHERE CURRENT OF sal_cursor;
    END IF;
END LOOP;

Lesson 8: Exception

异常或者可以在该过程的Exception部分中处理，如果没有处理则抛出到外层处理
常规的捕捉异常过程

EXCEPTION
    WHEN exception1 [OR exception2 ...] THEN
        stat1;
        sta2;
        ...
    [WHEN ...]

自定义的异常，也和系统定义的异常一样的捕捉过程

DECLARE
    --先定义
    e_emps EXCEPTION;
    --绑定到一个特定的异常编号
    PRAGMA EXCEPTION_INIT (e_emps, -2292);
BEGIN
    ...
EXCEPTION
    WHEN e_emps THEN
        ...
END;

SQLCODE SQLERRM 分别可以返回异常的编号和错误信息
自定义的异常要通过RAISE e_emps来主动抛出

RAISE_APPLICATION_ERROR是系统定义的过程可以抛出自定义但应用可以理解的异常信息，可以在执行部分，也可以在异常处理部分抛出。e.g.

raise_application_error (-20201, 'Something is wrong here');

Lesson 9: Procedure

过程定义
Create [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [mode1] datatype1, ...)]
IS | AS
    Declaration section
BEGIN
    Executable section;
EXCEPTION
    Exception section;
END;
参数的Mode
  • IN：默认、传入参数；
  • OUT：需要声明、保存返回值的参数、传入的必须是一个没被初始化的变量、不能赋默认值
  • IN OUT：需要声明、既是传入又是传出、传入的必须是被初始化过的变量、不能赋默认值
传参的方法
  • 按位置：传统的方法
    定义过程时可以为形参定义Default字段
  • 按名称
    传参可以按任意顺序但需指明实参名字。e.g. add_dept( p_loc => 2400, p_name => 'EDUCATION')

在过程的定义部分还可以定义子过程，给该过程的执行部分调用

DROP PROCEDURE procedure_name用来删除该过程

 
posted @ 2015-07-22 09:23  Kim5  阅读(390)  评论(0)    收藏  举报