Oracle.PL/SQL高级

 一、匿名块
 1、使用returning ... INTO 保存增删改表数据时的一些列的值
 （01）增加数据时保存数据
DECLARE
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  INSERT INTO emp(empno,ename,JOB,sal,comm,deptno) VALUES(&no,'zhaoliu','deve',3000,500,10) RETURNING ename,sal INTO v_ename,v_sal;
  dbms_output.put_line(v_ename || ','||v_sal);
end;

---打开控制台输出开关
SET serveroutput ON;

(02)修改数据时保存数据
DECLARE
  v_ename emp.ename%TYPE;
  v_sal emp.sal%type;
BEGIN
  UPDATE emp SET sal=sal+100 WHERE empno=&no RETURNING ename,sal INTO v_ename,v_sal;
  dbms_output.put_line(v_ename || ','||v_sal);
END;

(03)删除数据时保存数据
DECLARE
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  DELETE FROM emp WHERE empno=&no RETURNING ename,sal INTO v_ename,v_sal;
  dbms_output.put_line(v_ename || ','||v_sal);
END;

二、分支结构
1、if
IF 条件 THEN 结果; 
END IF;

IF 条件 THEN 结果;
ELSE 结果;
END IF;

IF 条件 THEN 结果;
elsif 条件 THEN 结果;
....
ELSE 结果;
END IF;

---查询一个员工薪水，判断员工薪水如果大于3000，输出他是一个白领；如果工资小于等于3000大于1500，输出他是一个蓝领；其它情况输出是一个灰领;
DECLARE
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno=&no;
  IF v_sal>3000 THEN 
    dbms_output.put_line('白领');
  elsif v_sal>1500 THEN
    dbms_output.put_line('蓝领');
  ELSE dbms_output.put_line('灰领');
  END IF;
END;

2、case:
格式一：
  CASE WHEN 条件 THEN 结果;
       WHEN 条件 THEN 结果;
       ...
       ELSE 结果;
  END CASE;

格式二：
  CASE 条件表达式 WHEN 值1 THEN 结果1;
                WHEN 值2 THEN 结果2;
                ....
                ELSE 结果m;
  END CASE;
  
---输入一个成绩，判断该成绩等级，如果在99~90之间，返回为A，如果在89~80之间，返回为B，如果在79~70之间，返回C，其它返回为D
DECLARE
  v_score NUMBER(3);
  v_flag VARCHAR2(1);
BEGIN
  v_score:=&score;
  CASE trunc(v_score/10) WHEN 9 THEN v_flag:='A';
                         WHEN 8 THEN v_flag:='B';
                         WHEN 7 THEN v_flag:='C';
                         ELSE v_flag:='D';
  END CASE;
  dbms_output.put_line(v_score || '，该 成绩的等级为：' || v_flag);
END;

三、循环结构
1、直到型循环
loop
  执行循环体;
  exit WHEN 条件;
END loop;

---在控制台输出1~5
DECLARE
  v_i NUMBER(1):=1;
BEGIN
  loop
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
    exit WHEN v_i>5;
  END loop;
end;

2、while:
while 条件
  loop
    执行循环体;
  END loop;
  
---在控制台输出1~5
DECLARE
  v_i NUMBER(1):=1;
BEGIN
  while v_i<=5
    loop
      dbms_output.put_line(v_i);
      v_i:=v_i+1;
    END loop;
end;

3、fOR:
FOR 循环变量 IN 最小值..最大值
  loop
    执行循环体;
  END loop;

---在控制台输出1~5
BEGIN
  for v_i in 1..5
    loop
      dbms_output.put_line(v_i);
    END loop;
end;

练习：
创建一张表，只包含一个字段id
先在表中插入一条记录5
然后将10—20之间的数插入两遍

四、存储过程
语法：
  CREATE [OR REPLACE] PROCEDURE 存储过程名(形参1 IN|out 数据类型,形参2 IN|out 数据类型...)
  IS | AS
    定义部分;
  begin
    执行部分;
  exception
    异常部分;
  END;
  
调用过程：
  exec 存储过程名(实参...);
  call 存储过程名(实参...);   ---如果调用的是无参存储过程，过程名后面必须()

1、创建一个无参存储过程 
CREATE PROCEDURE pro_demo1
IS
BEGIN
  INSERT INTO dept VALUES(50,'deve','nanjing');
end;
  
exec pro_demo1;
call pro_demo1();

2、创建一个输入参数存储过程
---根据输入的员工工号和新的月薪修改数据库中该 员工的薪水
CREATE OR REPLACE PROCEDURE pro_demo1(NO emp.empno%TYPE,newsal emp.sal%TYPE)
is
BEGIN
  update emp set sal=newsal where empno=no;
end;

一个为雇员加薪的过程
CREATE OR REPLACE PROCEDURE pro_demo1(NO emp.empno%TYPE,addsal emp.sal%TYPE)
is
BEGIN
  UPDATE emp SET sal=sal+addsal WHERE empno=NO;
end;


CALL pro_demo1(7788,10000);

SELECT * FROM emp;

3、创建一个即有输入参数也有输出参数存储过程
---输出指定员工的姓名和工资
CREATE OR REPLACE PROCEDURE pro_demo2(NO emp.empno%TYPE,NAME out emp.ename%TYPE,salary out emp.sal%TYPE)
IS
BEGIN
  SELECT ename,sal INTO NAME,salary FROM emp WHERE empno=NO;
END;


--使用匿名块调用存储过程
DECLARE
  v_name VARCHAR2(30);
  v_sal NUMBER(7);
BEGIN
  pro_demo2(&no,v_name,v_sal);
  dbms_output.put_line(v_name || ',' || v_sal);
end;
  
五、函数
CREATE OR REPLACE FUNCTION 函数名(形参 数据类型,...) RETURN 数据类型
IS
  定义部分;
BEGIN
  执行部分;
exception
  异常部分;
END;

---返回指定员工的年薪
CREATE OR REPLACE FUNCTION fun_demo1(NO emp.empno%TYPE) RETURN NUMBER
IS
  v_salary NUMBER(10,2);
BEGIN
  SELECT sal*12 INTO v_salary FROM emp WHERE empno=NO;
  RETURN v_salary;
END;

SELECT fun_demo1(7788) FROM dual;

---写一个函数，返回用户的姓名，入职日期，工资
CREATE OR REPLACE FUNCTION fun_demo2(NO emp.empno%TYPE) RETURN VARCHAR2
IS
  v_ename emp.ename%TYPE;
  v_hiredate varchar2(50);
  v_sal emp.sal%TYPE;
  v_info varchar2(200);
BEGIN
  SELECT ename,to_char(hiredate,'yyyy-mm-dd'),sal INTO v_ename,v_hiredate,v_sal FROM emp WHERE empno=NO;
  v_info:=v_ename ||','|| v_hiredate ||','|| v_sal;
  RETURN v_info;
end;

SELECT fun_demo2(7788) FROM dual;

存储过程和函数的区别：
1.返回值的区别，函数返回值只有一个，而存储过程是通过输出参数来返回，可以有多个
2.调用的区别，函数可以在查询语句中直接调用，而存储过程必须单独调用
3.应用场合的区别，函数一般用来计算并返回计算的结果，而存储过程一般用来执行特定的操作，如创建表、创建用户等DDL语句

五、包,里面包含的内容：存储过程，函数
语法：包括两个部分
声明部分：
  CREATE OR REPLACE PACKAGE 包名
  IS
    声明内容;
  END;

包体的实体：
  CREATE OR REPLACE PACKAGE BODY 包名
  is
    实现部分;
  end;

CREATE OR REPLACE PACKAGE pac_demo1
IS
  PROCEDURE pro_demo1(NO emp.empno%TYPE,salary emp.sal%TYPE);
  PROCEDURE pro_demo2(NO emp.empno%TYPE);
  FUNCTION fun_demo1(NO emp.empno%TYPE) RETURN NUMBER;
end;

create or replace package body pac_demo1
IS
  PROCEDURE pro_demo1(NO emp.empno%TYPE,salary emp.sal%TYPE)
  is
  BEGIN
    UPDATE emp SET sal=salary WHERE empno=NO;
  END;
  PROCEDURE pro_demo2(NO emp.empno%TYPE)
  IS
  BEGIN
    DELETE FROM emp WHERE empno=NO;
  END;
  FUNCTION fun_demo1(NO emp.empno%TYPE) RETURN NUMBER
  IS
    v_salary number(10,2);
  BEGIN
    SELECT sal*12 INTO v_salary FROM emp WHERE empno=NO;
    RETURN v_salary;
  END;
end;

CALL pac_demo1.pro_demo1(7788,20000);
SELECT * FROM emp;
CALL pac_demo1.pro_demo2(7788);
select pac_demo1.fun_demo1(7369) from dual;



--1.创建一个包，包含一个为雇员加薪的过程，一个为雇员减薪的过程
CREATE OR REPLACE PACKAGE pac_test1
IS
  PROCEDURE addsal(NO emp.empno%TYPE,addsal emp.sal%TYPE);
  procedure minussal(no emp.empno%type,minussal emp.sal%type);
END;

CREATE OR REPLACE PACKAGE body pac_test1
IS
  PROCEDURE addsal(NO emp.empno%TYPE,addsal emp.sal%TYPE)
  is
  BEGIN
    UPDATE emp SET sal=sal+addsal WHERE empno=NO;
  end;
  PROCEDURE minussal(NO emp.empno%TYPE,minussal emp.sal%TYPE)
  IS
  BEGIN
    UPDATE emp SET sal=sal-minussal WHERE empno=NO;
  end;
END;

--2.编写一个过程，验证用户登陆。如果用户名、密码匹配，输出Y，否则输出N    emp表中的ename为用户名 empno为密码
CREATE OR REPLACE PROCEDURE pro_test2(username VARCHAR2,PASSWORD NUMBER,flag out VARCHAR2)
IS
  v_count number(2);
BEGIN
  SELECT count(empno) INTO v_count FROM emp WHERE ename=username AND empno=PASSWORD;
  IF v_count>0 THEN flag:='Y';
  ELSE flag:='N';
  END IF;
END;

--3.编写一个函数，根据雇员编号，计算调整后的工资
如果该雇员从事经理工作，且工资大于3000，则工资上涨20%，否则上涨35%
其他情况均上涨10%，输出雇员编号、姓名、上涨后的工资

CREATE OR REPLACE FUNCTION fun_test3(NO emp.empno%TYPE) RETURN VARCHAR2
IS
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  v_newsal emp.sal%TYPE;
  v_job emp.job%type;
  v_info varchar2(100);
BEGIN
  SELECT ename,sal,JOB INTO v_ename,v_sal,v_job FROM emp WHERE empno=NO;
  IF upper(v_job)='MANAGER' THEN
    IF v_sal>3000 THEN 
      v_newsal:=v_sal*1.2;
    ELSE v_newsal:=v_sal*1.35;
    END IF;
  ELSE v_newsal:=v_sal*1.1;
  END IF;
   v_info:= NO || ',' || v_ename || ',' || v_newsal;
  RETURN v_info;
end;

--4.创建一个包
包中含有一个存储过程，实现涨工资的功能，输入雇员姓名，如果其工作年限超过20年，工资增加2000
工作年限在10-20年，工资增加1000，工作年限在5-10年，工资增加500，输出雇员姓名和增涨后的工资
包中还含有一个函数，实现输入工资，判断工资如果大于4500，返回1，否则返回0

CREATE OR REPLACE PACKAGE pac_test4
IS
  PROCEDURE pro_addsal(NAME emp.ename%TYPE,info out VARCHAR2);
  FUNCTION fun_sal(salary emp.sal%TYPE) RETURN NUMBER;
end;

CREATE OR REPLACE PACKAGE body pac_test4
IS
  PROCEDURE pro_addsal(NAME emp.ename%TYPE,info out VARCHAR2)
  IS
    v_year NUMBER(5);
    v_sal emp.sal%TYPE;
  BEGIN
    SELECT trunc(months_between(SYSDATE,hiredate)/12),sal INTO v_year,v_sal FROM emp WHERE upper(ename)=upper(NAME);
    IF v_year>20 THEN v_sal:=v_sal+2000;
    elsif v_year>=10 THEN v_sal:=v_sal+1000;
    elsif v_year>=5 THEN v_sal:=v_sal+500;
    END IF;
    info:=NAME || ','||v_sal;
  end;
  FUNCTION fun_sal(salary emp.sal%TYPE) RETURN NUMBER
  IS
    v_flag NUMBER(1);
  BEGIN
    IF salary>4500 THEN 
      v_flag:=1;
    ELSE v_flag:=0;
    END IF;
    RETURN v_flag;
  end;
END;

六、游标：
1、显示游标（查）、隐式游标（增删改）
使用显示游标有四个步骤：
  （01）：声明游标：cursor 游标名 IS 查询语句;
  (02):打开游标：open 游标名;
  (03):获取数据：fetch 游标名 INTO 变量名;
  (04):关闭游标：close 游标名;
  
游标属性：
  %found:是否发现数据，如果发现，返回true，否则返回false
  %notfound:是否没有数据，如果没有，返回true，否则返回false
  %isopen:是否打开，如果打开，，返回true，否则返回false
  %rowcount:返回涉及的行数
  
  
DECLARE
  CURSOR cur IS SELECT * FROM dept;
  v_dept dept%rowtype;
BEGIN
  OPEN cur;
  fetch cur INTO v_dept;
  loop
    dbms_output.put_line(v_dept.deptno || ','|| v_dept.dname || ','||v_dept.loc);
    fetch cur INTO v_dept;
  exit WHEN cur%notfound;
  END loop;
  CLOSE cur;
end;

---使用for循环读取游标指向的数据，打开游标、获取游标数据、关闭游标都自动
DECLARE
  CURSOR cur IS SELECT * FROM dept;
BEGIN
  FOR v_dept IN cur
    loop
      dbms_output.put_line(v_dept.deptno || ','|| v_dept.dname || ','||v_dept.loc);
    END loop;
end;

2、隐式游标：游标名为SQL,增删改

---修改指定员工的工资，如果员工不存在，提示员工不存在，如果存在，输出更新数据的行数
BEGIN
  UPDATE emp SET sal=sal*1.2 WHERE empno=&no;
  IF SQL%found THEN
    dbms_output.put_line('更新数据的行数为：' || SQL%rowcount);
  ELSE
    dbms_output.put_line('对不起，该用户不存在');
  END IF;
end;

六、异常：
1、预定义异常:有异常号也有异常名
DECLARE
  v_emp emp%rowtype;
BEGIN
  SELECT * INTO v_emp FROM emp WHERE empno=&no;
exception
  WHEN no_data_found THEN 
    dbms_output.put_line('用户不存在');
  WHEN too_many_rows THEN 
    dbms_output.put_line('返回了多行数据');
  WHEN others THEN 
    dbms_output.put_line('程序出现了错误');
END;

2、非预定义异常：有异常号没有异常名
3、自定义异常：没有异常号也没有异常名

七、触发器
1、DML触发器：

CREATE OR REPLACE TRIGGER 触发器名
  AFTER|BEFORE INSERT|UPDATE|DELETE ON 表名
  [FOR EACH ROW]  ---行触 发器
BEGIN
  执行体;
end;

---如果有人删除emp表中的数据，给出警告信息
CREATE OR REPLACE trigger tri_demo1
  AFTER DELETE ON emp
BEGIN
  dbms_output.put_line('有人删除了emp表中的数据，请注意');
end;

DELETE FROM emp WHERE empno=7566;

CREATE OR REPLACE TRIGGER tri_demo1
  AFTER DELETE or update or insert ON emp
BEGIN
  IF deleting THEN
    dbms_output.put_line('有人在删除emp表的数据');
  elsif inserting THEN
    dbms_output.put_line('有人新增了数据');
  elsif updating THEN
    dbms_output.put_line('有人修改了数据');
  end if;
END;

        DELETE    INSERT    UPDATE
:NEW    无          有效      有效
:old    有效        无        有效

CREATE OR REPLACE TRIGGER tri_demo3
  before DELETE OR UPDATE OR INSERT ON emp
  for each row
BEGIN
  IF deleting THEN
    dbms_output.put_line('有人在删除emp表的数据');
    dbms_output.put_line(:old.ename ||','||:old.sal);
  elsif inserting THEN
    dbms_output.put_line('有人新增了数据');
    dbms_output.put_line(:new.ename ||','||:new.sal);
  elsif updating THEN
    dbms_output.put_line('有人修改了数据');
    dbms_output.put_line('原数据为：'||:OLD.ename ||','||:OLD.sal);
    dbms_output.put_line('修改后的数据为：'||:new.ename ||','||:new.sal);
  END IF;
END;


语句触发器：无论增加、删除、修改多少行数据，触发器只触发一次
行触发器：增加、删除、修改对于每一行涉及的数据，触发器都会执行

INSERT INTO emp(empno,ename,sal) VALUES(1000,'bbb',6000);
commit;

DELETE FROM emp WHERE deptno=10;
commit;