PL/SQL 开发实例

语法

条件判断

(1) if … then elsif then … else … end if;

(2) case … when … then … end;

循环语句

(1)loop …exit when … end loop;

(2) while … loop … end loop;

(3) for i in 1…30 loop …end loop;

游标

多条查询

declare
    cursor curEmployees(v_employee_id varchar2) is
        select
            salary,
            email
        from employees
        where
            employee_id <> v_employee_id;
    stCurEmp curEmployees%rowtype;
begin
    --打开游标
    open curEmployees
        --循环开始
        loop
            --获取一条数据
            fetch curEmployees(100) into stCurEmp;
            --循环终止条件
            exit when curEmployees%notfound;
            --循环主逻辑代码
            dbms_output.put_line(stCurEmp.salary || ',' || stCurEmp.email);
        --循环结束
        end loop;
    --关闭游标
    close curEmployees;
end;

单条查询

declare
    cursor curEmployees(v_employee_id varchar2) is
        select
            salary,
            email
        from employees
        where
            employee_id = v_employee_id;
    stCurEmp curEmployees%rowtype;
begin
    open curEmployees
    --获取一条数据
    fetch curEmployees(100) into stCurEmp;
    --循环主逻辑代码
    dbms_output.put_line(stCurEmp.salary || ',' || stCurEmp.email);
    close curEmployees;
end;

异常处理

异常处理代码

declare
    cursor curEmployees(v_employee_id varchar2) is
        select
            salary,
            email
        from employees
        where
            employee_id = v_employee_id;
    stCurEmp curEmployees%rowtype;
    salary_exp Exception; --自定义异常
begin
    open curEmployees
    fetch curEmployees(100) into stCurEmp;

    --发生异常
    if stCurEmp.salary <= 2000 then
        --抛出异常
        raise salary_exp;
    end if;
    close curEmployees;
    dbms_output.put_line('正常终了');
Exception
    --异常处理
    when salary_exp then
        dbms_output.put_line('异常终了');
end;

record

多个字段存储到一个变量中（单条查询）

--实例1：把单条查询的多个字段存储到一个record类型中。
declare
    --定义record类型
    type emp_record is record (
        v_sal employees.salary%type,
        v_email employees.email%type,
        v_hire_date employees.hire_date%type
    );
    --创建record类型的对象
    v_emp_record emp_record;
begin
    select
        salary,
        email,
        hire_date
    into
        v_emp_record
    from employees
    where
        employee_id = 100;
end;