学习总结

1 动态执行SQL语句

在PL/SQL程序开发中，可以使用DML语句和事务控制语句，但是还有很多语句（比如DDL语句）不能 直接在PL/SQL中执行。这些语句可以使用动态SQL来实现。

PL/SQL块先编译然后再执行，动态SQL语句在编译时不能确定，只有在程序执行时把SQL语句作为字符 串的形式由动态SQL命令来执行。在编译阶段SQL语句作为字符串存在，程序不会对字符串中的内容进 行编译，在运行阶段再对字符串中的SQL语句进行编译和执行，动态SQL的语法是：

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

如果动态语句是SELECT语句，可以把查询的结果保存到INTO后面的变量中。

如果动态语句中存在参数，USING为语句中的参数传值。

动态SQL中的参数格式是：[:参数名]，参数在运行时需要使用USING传值。

在Oracle的insert，update，delete语句都可以使用RETURNING子句把操作影响的行中的数据返回，对SQL语句中存在RETURNING子句时，在动态执行时可以使用RETURNING INTO来接收。

对动态查询语句可以使用INTO子句把查询的结果保存到一个变量中，要求该结果只能是单行。

动态执行参数中可以是：[:数字]也可以是[:字符串]。

-- 无子句
begin
  execute immediate 'create table tt as select * from emp';
end;  
-- into子句 using子句
declare
  e emp%rowtype;
begin
  execute immediate
  'select * from emp where empno = :1'
  into e
  using 7788;
  dbms_output.put_line(e.empno || '------' || e.ename);
end;

-- returning
declare
  emp_id number(4) := 7788;
  salary number(7 , 2);
begin
  execute immediate
  'update tt set sal = sal + 500 where empno = :1 returning sal into :2'
  using emp_id
  returning into salary;
  dbms_output.put_line(salary);
end;

2 PL/SQL的异常处理

异常定义：在PL/SQL块的声明部分采用EXCEPTION关键字声明异常，定义方法与定义变量相同。 比如声明一个myexception异常方法是：

myexception EXCEPTION；

异常引发：在程序可执行区域，使用RAISE关键字进行引发。比如引发myexception方法是：

RAISE myexception;

在Oracle开发中，遇到的系统异常都有对应的异常码，在应用系统开发中，用户自定义的异常也可以指 定一个异常码和异常信息，Oracle系统为用户预留了自定义异常码，其范围介于-20000到-20999之间 的负整数。引发应用程序异常的语法是：

RAISE_APPLICATION_ERROR(异常码，异常信息)

PRAGMA 由编译器控制， PRAGMA 在编译时处理，而不是在运行时处理。 EXCEPTION_INIT 告诉编译器 将异常名与 ORACLE 错误码绑定起来，这样可以通过异常名引用任意的内部异常，并且可以通过异常名 为异常编写适当的异常处理器。 PRAGMA EXCEPTION_INIT 的语法是：

PRAGMA EXCEPTION_INIT(异常名,异常码)

declare
  newSal emp.sal%type;
begin
  select sal into newSal from emp;
  exception
    when too_many_rows then
      dbms_output.put_line('返回太多记录了');
    when others then
      dbms_output.put_line('未知异常');
end;

declare
  s emp.sal%type;
  myexp exception;
begin
  select sal into s from emp where empno = 7788;
  if s < 5555 then
    raise myexp;
  end if;
  exception
    when myexp then
      dbms_output.put_line('自定义异常');
    when others then
      dbms_output.put_line('未知异常');
end;

declare
  s emp.sal%type;
  myexp exception;
begin
  select sal into s from emp where empno = 7788;
  if s < 5555 then
    raise myexp;
  end if;
  exception
    when myexp then
      raise_application_error(-20001 , '自定义异常');
    when others then
      dbms_output.put_line('未知异常');
end;

declare
  null_salary exception;
  pragma exception_init(null_salary , -20101);
begin
  declare
    curr_comm number;
  begin
    select comm into curr_comm from emp where empno = &empno;
    if curr_comm is null then
      raise_application_error(-20101 , 'Salary is missing');
    else
      dbms_output.put_line('有津贴');
    end if;
  end;
exception
  when no_data_found then
    dbms_output.put_line('没有发现行');
  when null_salary then
    dbms_output.put_line('津贴未知');
  when others then
    dbms_output.put_line('未知异常');
end;

3 子程序

1 过程

2 函数

4 触发器

create or replace procedure DisplaySal
as
  newsal emp.sal%type;
  oldsal emp.sal%type;
begin
  select sal into oldsal from emp where empno = 7654;
  execute immediate 
  'update emp set sal = sal + sal * 0.05 where empno = 7654 returning sal into :1'
  returning into newsal;
  dbms_output.put_line(7655 || '原来的工资是：'  || oldsal || '，更新后的工资是：' || newsal);
  exception
    when no_data_found then
      dbms_output.put_line('查无此人！');
end;

begin
  DisplaySal;
end;

create or replace procedure updateSal(var_per number , var_empno number)
is
  en emp.ename%type;
  newsal emp.sal%type;
  oldsal emp.sal%type;
begin
  select ename , sal into en , oldsal from emp where empno = var_empno;
  execute immediate
  'update emp set sal = sal + sal * :1 where empno = :2 returning sal into :3'
  using var_per , var_empno
  returning into newsal;
  dbms_output.put_line(en || '原来的工资是：'  || oldsal || '，更新后的工资是：' || newsal);
  exception
    when no_data_found then
      dbms_output.put_line('查无此人！');
end;

begin
  updateSal(0.1 , 7654);
end;

create or replace procedure displayComm(eno in number , mycomm out number)
as
  s emp.sal%type;
begin
  select sal , nvl(comm , 0) into s , mycomm from emp where empno = eno;
  if s > 1500 then
    mycomm := mycomm + 1000;
  end if;
end displayComm;

declare
  eno number := 7654;
  d number;
begin
  displayComm(eno , d);
  dbms_output.put_line(eno || '实际应发的奖金是：' || d);
end; 

create or replace procedure displayTax(eno number , base number default 1500)
as
  s number;
  t number := 0;
begin
  select sal into s from emp where empno = eno;
  if s > base then
    t := (s - base) * 0.2;
  end if;
  dbms_output.put_line(eno || '应交纳税额是：' || t);
end displayTax;

begin
  displayTax(7654);
  displayTax(7654 , 1200);
end;

create or replace function
getTax(emp_no number , base_sal number default 1500) return number
as
  esal number;
begin
  select sal + nvl(comm , 0) into esal from emp where empno = emp_no;
  if esal > base_sal then
    return base_sal * 0.2;
  end if;
  return 0;
  EXCEPTION
    WHEN no_data_found THEN
    dbms_output.put_line('查无此人！');
end getTax;

declare
  tax number;
begin
  tax := getTax(7654);
  dbms_output.put_line('按默认基数个人所得税是：' || tax);
end;

select getTax(7654 , 1200) from dual;

create or replace trigger ww
before insert on emp
declare
  weekend varchar2(10);
begin
  select trim(to_char(sysdate , 'day')) into weekend from dual;
  dbms_output.put_line('一个员工被插入');
  if weekend in ('friday') then
    raise_application_error(-20001 , '不能在非法时间插入员工');
  end if;
end ww;

insert into emp(empno) values(9999);

心得体会

Oracle的学习基本完成，他的基本语法和MySQL的语法基本相同，有些许差异但是不大，对于高级特性部分，Oracle相较于MySQL的体系更加的完善，当然，其中很多的地方也有相似之处。