Oracle

1. PL/SQL中动态执行SQL语句

EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]

--例
DECLARE
    e emp%rowtype;
BEGIN
    EXECUTE IMMEDIATE
    'select * from emp where empno = :1'
    INTO e -- 将结果集赋值给变量e
    USING 7788; --给参数传值，sql中的:1类似于占位符?
    dbms_output.put_line(e.empno || ' ---- ' || e.ename);
END;

-- RETURNING 子句
DECLARE
    emp_id NUMBER(4) := 7788;
    salary NUMBER(7,2);
BEGIN
    EXECUTE IMMEDIATE
    'UPDATE emp SET sal = 2222 WHERE empno = :1 RETURNING sal INTO :2'
    USING emp_id
    RETURNING INTO salary; --在动态执行的过程中就将执行过后的结果赋值给salary
    dbms_output.put_line(salary);
END;

2.PL/SQL的异常处理

1.自定义异常

PL/SQL中异常处理的语法
BEGIN
    --可执行部分
    EXCEPTION -- 异常处理开始
    WHEN 异常名1 THEN
    --对应异常处理
    WHEN 异常名2 THEN
    --对应异常处理
    ……
    WHEN OTHERS THEN
    --其他异常处理
END;
-- 例
DECLARE 
  s emp.sal %type;
  myexp EXCEPTION;  --声明异常
BEGIN 
  SELECT SAL INTO s from emp where empno = 7788;
  if s<5000 then 
    raise myexp;
  end if;
  exception
    when myexp then 
      dbms_output.put_line('自定义异常'); --捕获异常
      when others then
         dbms_output.put_line('其他异常');
end;

2.应用程序异常

自定义异常码和异常信息在控制台抛出（异常码的范围在-20000到-20999）之间

DECLARE 
  s emp.sal %type;
  myexp EXCEPTION;
BEGIN 
  SELECT SAL INTO s from emp where empno = 7788;
  if s<5000 then 
    raise myexp;
  end if;
  exception
    when myexp then 
      raise_application_error(-20001,'自定义程序异常');
      when others then
         dbms_output.put_line('其他异常');
end;

3.子程序

以及命名过后的PL/SQL块，编译过后就会存储在数据库中，类似于java中定义了一个方法，可以随时调用。

3.1过程

-- 创建过程的语法 CREATE OR REPLACE 不存在时创建存在时替换
CREATE OR REPLACE PROCEDURE 过程名[(参数名 in/out 数据类型)]
{IS|AS}
--声明部分（参数的声明）
BEGIN
--可执行部分
[EXCEPTION]
--异常处理部分
END [过程名];

--例
CREATE OR REPLACE PROCEDURE Comm(eno IN NUMBER, salary OUT NUMBER)
-- 参数中也可以定义一个默认值 def_num NUMBER default 5000,如果没有传参就使用默认值，如果传了参数就使用传入的参数
AS
       s emp.sal%type;
BEGIN
  SELECT sal, nvl(comm, 0) INTO s, salary FROM emp WHERE empno = eno;
  --nvl(comm, 0) 如果comm就赋值0，避免查询结果为null没有输出
  IF s > 5000 THEN
  salary := salary + 1000;
END IF;
END Comm;
-- 调用过程
DECLARE
   eno NUMBER := 7566;
   d NUMBER; --这里定义一个变量d，便于接收返回值，将d作为参数传入到过程中赋值后返回
BEGIN
  Comm(eno, d);
  dbms_output.put_line(eno || '实际应发的奖金是：' || d);
END;
--sqlplus
EXECUTE DisplaySal;

3.2 函数

函数中必须由返回值（return字句）

-- 创建函数语法
CREATE OR REPLACE FUNCTION 函数名[(过程参数列表)] RETURN 数据类型
{IS|AS}
    --声明部分
BEGIN
    --可执行部分
[EXCEPTION]
    --异常处理部分
END [函数名]; 
--例
CREATE OR REPLACE FUNCTION getRevenue(emp_no NUMBER,base_sal NUMBER default 2000) RETURN NUMBER --返回值类型
IS
       reve NUMBER;
BEGIN
  SELECT sal+comm INTO reve FROM emp WHERE empno=emp_no;
  IF reve>base_sal THEN
     RETURN base_sal*0.2;
  END IF;
      RETURN 0;
EXCEPTION
      WHEN no_data_found THEN
      dbms_output.put_line('没有找到这个员工！');
END getRevenue;

--调用函数
declare
    n number ; --定义一个变量来接收返回值
begin
  n := getRevenue(7499);
  dbms_output.put_line('个人税为：'|| n);
end;
-- 函数也可以被sql语句调用

4.触发器

-- 触发器语法
CREATE [or REPLACE] TRIGGER 触发器名
    {BEFORE | AFTER}
    {DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
    [FOR EACH ROW [WHEN(条件) ] ]
begin
    -- PLSQL 块
End 触发器名;

--例
create or replace trigger testTrigger
       after insert on emp --after 可以替换为before
begin
       dbms_output.put_line('插入了一个员工');
end testTrigger;

伪记录变量

--例
create or replace trigger addsal
    before update of sal on myemp
for each row
begin
    if :old.sal >= :new.sal then
    raise_application_error(-20002, '涨前的工资不能大于涨后的工资');
end if;
end;