PL/SQL练习题目

1 参照变量

--参照变量是指用于存放数值指针的变量（游标的使用）
declare
    -- 游标类型定义，不同于变量
    type sp_emp_cursor is ref cursor;
    -- 定义游标变量
    v_emp sp_emp_cursor;
    --普通变量
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    -- 打开游标（让游标指向了结果集）
    open v_emp for select ename,sal from emp where deptno=&no;
    -- 循环遍历游标指向的值
    loop
        -- 遍历数据
        fetch v_emp into v_ename,v_sal;
        -- 结束条件
        exit when v_emp%notfound;
        -- 输出
        dbms_output.put_line(v_ename || ',' || v_sal);
        end loop;
end;

2 游标

-- 游标基础语法：维护查询结果的内存中的一个区域；%rowcount遍历计数 %isopen 游标是否打开
declare
    -- 创建游标
    cursor emp_cursor is select ename,sal from emp where deptno=&no;
    -- 定义表的行类型变量，此处的表用的游标
    v_emp_row emp_cursor%rowtype;
begin
    -- 打开游标
    open emp_cursor;
    if emp_cursor%isopen then
        dbms_output.put_line('游标已打开');
    end if;
    loop
        fetch emp_cursor into v_emp_row;
        exit when emp_cursor%notfound;
        dbms_output.put_line(v_emp_row.ename || ',' || v_emp_row.sal);
    end loop;
    -- 关闭游标
    close emp_cursor;
end;

/*
在员工表中有 sal(薪水列)，实现按员工编号排序，并实现工资不断累加，输出如
下效果：
员工 id， 员工名称， 薪水， 累加薪水
1 A 800 800
2 B 500 1300
3 C 1000 2300
..........
要求创建一个存储过程，使用游标的方式实现
*/
create or replace procedure add_pro is
    cursor emp_cursor is select empno,ename,sal,comm from emp order by empno for update;
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_sal emp.sal%type;
    v_comm emp.comm%type;
    v_sum number; --累计工资
begin
    v_sum :=0;
    open emp_cursor;
    loop
        fetch emp_cursor into v_empno,v_ename,v_sal,v_comm;
        v_sum := v_sum+v_sal;
        v_comm := v_sum;
        exit when emp_cursor%notfound;
        dbms_output.put_line(v_empno||','||v_ename||','||v_sal||','||v_comm);
    end loop;
    close emp_cursor;
end;
-- 设置输出显示
set serveroutput on;
-- 过程调用
call add_pro();

3 触发器

/*
在 oracle11g 数据库中创建 D 表，复制 dept 表的结构和数据，创建 E 表复制 emp表的结构和数据。
创建触发器，当对 D 表中的部门编号 deptno 中的值进行修改时，触发器将 E 表中对应部门编号进行修改；
当删除 D 表中某一条数据信息时，触发器会将 E 表部门编号与 D 表中删除数据部门编号相等的数据删除。
*/

-- 复制表结构和数据
create table D as select * from dept;  
create table E as select * from emp; 
/* 
不管是after ,还是 before  在update、insert、delete 时，一定是在事务提交之后才会触发触发器 
before 和after的区别:before、insert、update可以对new进行修改；after不能对new 进行修改；二者都不能对old 进行修改 
*/
-- 创建触发器
create or replace trigger update_d
after
    -- 行触发器for each row和语句触发器:行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器
    insert or update or delete on D for each row
begin
    -- 更新的时候有new和old
    if updating then
        update E e1 set e1.deptno=:new.empno where e1.empno=:old.empno;
    -- 删除时只有old值
    elsif deleting then
        delete from E e2 where e2.Deptno = :old.Deptno;
    end if;
end update_d;

4 过程

-- 过程：无返回值
create or replace procedure update_sal(v_ename varchar2,v_sal number) is
begin
    update emp set sal=v_sal where ename=v_ename;
    commit;
end;

-- 调用
call update_sal('SCOTT',123);

-- 查看过程是否执行成功
select * from emp;

5 函数

--函数：用于返回特定的数据，当建立函数时，在函数头部必须包含return字句
create or replace function get_sal(v_ename varchar2) return number is
-- 接收返回值
v_sal number;
begin
    select sal into v_sal from emp where ename=v_ename;
    return v_sal;
end;
/*
sqlplus中调用函数：
SQL> var sal number;
SQL> call get_sal('SMITH') into:sal;

调用完成。

SQL> print sal;

       SAL
----------
       800
*/

6 包

-- 包：用于在逻辑上组合过程和函数（类似于接口）
-- 创建包
create package sp_package is
    procedure update_sal(v_ename varchar2,v_sal number);
    function get_sal(v_ename varchar2) return number;
end;
-- 实现包体
create or replace package body sp_package is
    -- 过程/函数实现
end;
-- sqlplus中调用包中过程/函数
call sp_package.update_sal('SCOTT',1500);

7 复合类型 

-- 1 标量 即常亮，使用:=赋值 2 声明未知类型的变量：%type 3 复合类型（类比泛型、结构体理解）4 %rowtyp行类型
declare
    type emp_record_type is record(
        v_sal emp.sal%type,
        v_empno emp.empno%type
    );
    v_record emp_record_type;
    -- v_record scott.emp%roetype
begin
    select sal,empno into v_record from emp where ename='SMITH';
    dbms_output.put_line(v_record.v_sal || ',' || v_record.v_empno);
end;

set serveroutput on
begin
dbms_output.put_line('hello');
end;