oracle pl/sql笔记

-- if
declare
    pname varchar2(20) := 1;
begin
    if pname = 1 then
        dbms_output.PUT_LINE('是1');
    elsif pname = 2 then
        dbms_output.PUT_LINE('是2');
    else
        dbms_output.PUT_LINE('其他');
    end if;
end;

-- while loop
declare
    total varchar2(4) := 0;
begin
    while total <= 100
        loop
            dbms_output.PUT_LINE(total);
            total := total + 1;
        end loop;
end;

-- loop exit when
declare
    total varchar2(3) := 0;
begin
    loop
        dbms_output.PUT_LINE(total);
        total := total + 1;
        exit when total >= 100;
    end loop;
end;

-- for in (适用于连续数值 total 不用自身j +1)
declare
    total varchar2(3) := 0;
begin
    for total in 1..100
        loop
            dbms_output.PUT_LINE(total);
        end loop;
end;

-- 游标 循环表数据
declare
    -- 定义游标
    cursor c1 is select *
                 from emp;
    -- 定义行级变量 变量名 表名%rowtype(行类型)
    item emp%rowtype;
begin
    -- 打开游标
    open c1;
    loop
        fetch c1 into item;
        exit when c1%notfound;
        dbms_output.PUT_LINE(item.ENAME);
    end loop;
    -- 关闭游标 必须放在循环外面
    close c1;
end;

-- 异常处理
declare
    pnum number(4) := 5;
begin
    pnum = pnum / 0;
exception
    -- 捕获异常
    when zero_divide then
        dbms_output.PUT_LINE('除数为0');
    when others then
        dbms_output.PUT_LINE('其他异常');
end;

-- 自定义异常
declare
    prec emp%rowtype;
    cursor c1 is select *
                 from emp;
    -- 定义异常
    not_data exception;
begin
    open c1;
    loop
        fetch c1 into prec;
        if c1%notfound then
            -- 抛出异常
            raise not_data;
        end if;
    end loop;
    close c1;
exception
    --捕获异常
    when not_data then
        dbms_output.PUT_LINE('没有数据');
end;

-- 带有输入参数的存储过程
create or replace procedure addSal(pno in emp.empno%type)
as
    prec emp%rowtype;
begin
    select * into prec from emp t where t.EMPNO = pno;
    update emp t set t.sal = t.sal + 100 where t.EMPNO = pno;
    DBMS_OUTPUT.PUT_LINE('涨工资前:' || prec.sal || '涨工资后:' || (prec.sal + 100));
end;

-- 带有输入和输出参数的存储过程
create or replace procedure countSal(pno in emp.empno%type, yasl out number)
as
    psal  emp.sal%type;
    pcomm emp.comm%type;
begin
    select t.sal, t.COMM into psal,pcomm from emp t where t.EMPNO = pno;
    yasl := psal * 12 + nvl(pcomm, 0);
end;
-- 调用带输出参数的存储过程
declare
    ysal number(10);
begin
    countSal(7369, ysal);
    DBMS_OUTPUT.PUT_LINE(ysal);
end;

-- 存储函数
create or replace function countysalfun(pno in emp.empno%type) return number
as
    psal  emp.sal%type;
    pcomm emp.comm%type;
begin
    select t.sal, t.COMM into psal,pcomm from emp t where t.EMPNO = pno;
    return psal * 12 + nvl(pcomm, 0);
end;
-- 调用存储过程
begin
    DBMS_OUTPUT.PUT_LINE(countysalfun(7369));
end;

-- 触发器
create or replace trigger insterptrg
    before insert
    on person
begin
    DBMS_OUTPUT.PUT_LINE('person 表被插入了数据');
end;

insert into person(userid, pname, gender, birthday)
values (4, '张娟', 2, sysdate);

select *
from person;

-- 不能在指定条件下插入数据
create or replace trigger vailed_inster_p
    before insert
    on person
declare
    cruday char(10);
begin
    select to_char(sysdate, 'day') into cruday from dual;
    DBMS_OUTPUT.PUT_LINE(cruday);
    if cruday = '星期一' then
        raise_application_error(-20001, '星期一不允许插入数据');
    end if;
end;


-- 行级触发器
create or replace trigger vailed_addsal
    before update of sal
    on emp
    for each row
begin
    if :new.sal<= :old.sal then
        raise_application_error(-20002,'工资不能小于之前工资');
    end if;
end;

update emp set sal = sal - 100 where EMPNO = 7369;