PL/SQL 基础知识

-- PL/SQL
-- 声明变量

-- constant用于声明常量。
-- not null 表明该变量非空，必须指定默认值
-- default 与 := 都是赋值的意思
-- 常用的数据类型：char,varchar2,number,date等

-- 基本格式

-- Declare: 声明部分，可选，用来定义使用的变量、常量、游标等

-- Begin: 是核心部分，执行各种对数据库操作的语句

-- Exception:异常处理部分

-- End；

declare
  na varchar2(20) := '张三';
begin
  -- 重新赋值
  select ename into na from emp where empno = 7788;
  -- 打印  ||连接符
  dbms_output.put_line('你好:' || na);
end;

PL/SQL具有过程化语言的特征，可以使用顺序结构、选择结构、循环结构及GOTO结构等控制结构。

-- if语句
declare
  score number := 95;
begin
  -- if判断并打印
  if score >= 90 then
    dbms_output.put_line('优秀');
  elsif score >= 75 then
    dbms_output.put_line('良好');
  elsif score >= 60 then
    dbms_output.put_line('合格');
  else
    dbms_output.put_line('不合格');
  end if;
end;
-- 工资等级
declare
  sal number;
begin
  select sal into sal from emp where ename = 'SCOTT';
  if sal >= 3000 then
    dbms_output.put_line('神豪');
  elsif sal >= 2000 then
    dbms_output.put_line('土豪');
  elsif sal >= 1000 then
    dbms_output.put_line('还可以');
  else
    dbms_output.put_line('帝豪');
  end if;
end;

case语句也是一种选择结构，它类似于高级语言中的switch语句。但注意它有返回值

declare
  s varchar2(10) := 'B';
  r varchar2(20);
begin
  r := case s
         when 'A' then
          '优秀'
         when 'B' then
          '良好'
         when 'C' then
          '合格'
         when 'D' then
          '不合格'
       end;
  dbms_output.put_line(r);
end;

-- loop 循环

-- 10的阶乘
declare
  a int := 10;
  t int := 1;
begin
  loop
    t := t * a;
    a := a - 1;
    exit when a = 1;
  end loop;
  a := 10;
  dbms_output.put_line(a || '的阶乘是' || t);
end;

-- for循环
declare
  a int := 10;
  t int := 1;
  j int;
begin
  for j in 1 .. a loop
    t := t * j;
  end loop;
  dbms_output.put_line(a || '的阶乘是' || t);
end;

-- while
declare
  a int := 10;
  t int := 1;
begin
  while a >= 1 loop
    t := t * a;
    a := a - 1;
  end loop;
    a := 10;
  dbms_output.put_line(a || '的阶乘是' || t);
end;

-- for 循环嵌套 99乘法表
declare
  a int := 9;
  i int;
  j int;
begin
  for i in 1 .. a loop
    for j in 1 .. i loop
      dbms_output.put(j || '*' || i || '=' || j * i||'  ' );
    end loop;
    dbms_output.put_line('');
  end loop;
end;

goto结构又称跳转结构，可以在PLSQL块中设定一个标签，标签使用<<标签名>>来定义，然后使用goto 标签名;完成跳转。巧妙的使用goto语句能实现选择结构，也能实现循环结构。

-- goto
declare
  a int := 10;
  t int := 1;
begin
  <<abc>>
  t := t * a;
  a := a - 1;
  if a >= 1 then
    goto abc;
  end if;
  a := 10;
  dbms_output.put_line(a || '的阶乘是' || t);
end;

存储过程简称过程。在Oracle数据库中，它是一个能完成指定功能，并且可以独立编译和调用的语句块。

参数模式：
in：
用于向过程传入一个值，该值在过程体中不能更改
out：
用于从被调用的过程中返回一个值，该值可以更改，不能有默认值
in out：
用于向过程传入一个初始值，并返回更新后的值，不能有默认值

-- procedure
create or replace procedure p1(a in integer, b in integer, c out integer) as
  j integer;
begin
  c:=0;
  for j in a .. b loop
    c := c + j;
  end loop;
end;
-- 调用存储过程
declare 
 c integer;
begin
  p1(1,1000,c);
  dbms_output.put_line('c:'||c);
  end;

 -- 编写存储过程计算税后工资
  create procedure p2(a in out int)
  as
     
  begin
    if a <= 3500 then
      dbms_output.put_line('不用交税');
    elsif a <= 5000 then
      a := a - (a - 3500) * 3 / 100;
    elsif a <= 8000 then
      a := a - (a - 5000) * 10 / 100 - 105;
    elsif a <= 12500 then
      a := a - (a - 8000) * 20 / 100 - 555;
    end if;
  end;
  
  declare
    a int := 8888;
  begin
    p2(a);
    dbms_output.put_line(a);
  end;

函数和存储过程差不多 最大区别就在于函数有return返回值

过程用来完成一项任务，可以不返回值，也可以返回多个值
过程的调用是一条pl/sql语句；
函数包含return子句，用来返回一个单独的值
函数的调用可以在一个表达式中。

-- function 函数
create or replace function f1(a in emp.empno%type) return emp.ename%type
as
rname emp.ename%type;
begin
select ename into rname from emp where empno = a; 
return rname;
end;

declare
   rn emp.ename%type;
begin
  rn:=f1(7788);
  dbms_output.put_line(rn);
  end;

 触发器

类似过程和函数，在事件发生的时候被自动隐式触发，而且触发器不接受参数。

-- 给emp表创建一个语句级触发器，当添加员工后，触动触发器执行，打印出当前员工人数

create trigger t1 after insert on emp
declare
	cnt integer;
begin
	select count(*) into cnt from emp;
	dbms_output.put_line('目前员工人数:'||cnt);
end;

old:只能用于删除型触发器或更新型触发器。即只在delete语句或update语句触动的触发器中有效。它表示刚刚删除的那一条记录，或表示更新前的那一条记录。在使用时，必须是“:old.列名”的格式。
new:只能用于添加型触发器或更新型触发器。即只在insert语句或update语句触动的触发器中有效。它表示刚刚添加的那一条记录，或表示更新后的那一条记录。在使用时，必须是“:new.列名”的格式。
old与new只能用于行级触发器

-- 打断触发器
create or replace trigger t after delete on dept for each row
declare
    n int;
begin
  select count(*) into n from emp where deptno=:old.deptno;
  if n>0 then
    raise_application_error('-20000',:old.dname||'部门有人不能删除');
    end if;
  end;
  delete dept where deptno=10;