oracel 2 pl/sql

1.什么是PL/SQL

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural
Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL
只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的，但支持Navicat Premium。

2.赋值

DECLARE
v_ename varchar2(40);--声明变量
v_rate number;
c_rate constant number(7,2):=1.10;--声明常量
begin
  select ename,sal*c_rate into v_ename ,v_rate from emp where empno = 7788;
  dbms_output.put_line(v_ename);
end;

DECLARE
v_ename emp.empno%type:=7788;--v_eanme类型与emp表empno字段一样
v_rec emp%rowtype;--v_rec  类型与emp中的一行字段一样
begin
  select * into v_rec from emp where empno = v_ename;
  dbms_output.put_line(v_rec.sal);
end;

3.条件语句

DECLARE
    v_counter NUMBER := 5;
BEGIN
       DBMS_OUTPUT.PUT_LINE('v_counter的当前值为:'||v_counter);
       IF v_counter >= 10 THEN
          NULL; --为了使语法变得有意义,去掉NULL会报语法错误
       ELSE
	       v_counter := v_counter + 10;
		  DBMS_OUTPUT.PUT_LINE('v_counter的改变后值为:'||v_counter);
       END IF;
 END;
--判断工资的级别
DECLARE
   v_grade  NUMBER(2);
   v_sal emp.sal%TYPE;
BEGIN
   SELECT sal INTO v_sal   FROM emp
    WHERE ename='SCOTT';
   CASE 
   WHEN v_sal<=3200 AND v_sal>=700 THEN
      v_grade:=1;
   WHEN v_sal<=4400 THEN
      v_grade:=2;
   WHEN v_sal<=5000 THEN 
      v_grade:=3;
   WHEN v_sal<=7000 THEN 
      v_grade:=4;
   WHEN v_sal<=9999 THEN 
      v_grade:=5;
   ELSE 
      v_grade:=0;
   END CASE;
   IF v_grade=0 THEN
      dbms_output.put_line('SCOTT 薪水：'||v_sal||' 工资无级别');
   ELSE
      dbms_output.put_line('SCOTT 薪水：'||v_sal||' 工资级别:'||v_grade);      
   END IF;
END;

4.异常

在程序开发之中经常会由于设计错误、编码错误、硬件故障或其他原因引起程序的运行错误。虽然不可能预测所有错误，但在程序中可以规划处理某些类型的错误。在PL/SQL程序中的异常处理机制使得在出现某些错误的时候程序仍然可以执行。比如内部溢出或者零除等等。
用户可以处理的只有运行时异常，而对于编译的异常，只能通过语法解决。
异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.

自定义异常
declare
v_comm emp.comm%type;
e_comm_is_null exception;--定义异常类型变量
begin
  select comm into v_comm from emp where empno = 7788;
--raise  抛出异常
  if v_comm is null THEN  raise e_comm_is_null;
  end if;
  exception --异常处理
    when no_data_found then
    --sqlcode为错误号 sqlerrm为错误说明
      dbms_output.put_line('员工不存在：'||sqlcode||sqlerrm);
    when e_comm_is_null then
      dbms_output.put_line('该员工没有补助');
    when others then
      dbms_output.put_line('出现其他异常');
end;

5.游标

使用显式游标输出每个员工的姓名和薪水。

*/
DECLARE
 v_name emp.ename%type; 
 v_sal emp.sal%type; --定义两个变量来存放ename和sal的内容
 CURSOR emp_cursor IS   SELECT ename,sal   FROM emp;
BEGIN
OPEN emp_cursor; --打开游标
LOOP
   FETCH emp_cursor INTO v_name,v_sal; --提取游标
   EXIT WHEN emp_cursor%NOTFOUND; --判断是否有数据
   DBMS_OUTPUT.PUT_LINE
      ('第'||emp_cursor%ROWCOUNT||'个雇员:'||v_name||v_sal);
END LOOP;
CLOSE emp_cursor;--关闭游标
END;

/*
===========================================================
|        循环游标的用法。
============================================================
*/
--显示雇员表中所有雇员的姓名和薪水
DECLARE
	CURSOR emp_cursor IS
	   SELECT ename,sal FROM emp;
BEGIN
	FOR emp_record IN emp_cursor LOOP
	   DBMS_OUTPUT.PUT_LINE
          ('第'||emp_cursor%ROWCOUNT||'个雇员：'
           ||emp_record.ename|| emp_record.sal);
	END LOOP;
END;

6.存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL
语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

添加员工记录

============================================================
*/
CREATE OR REPLACE PROCEDURE add_emp(
    eno NUMBER,  								--输入参数,雇员编号
    name VARCHAR2,								--输入参数,雇员名称
    salary NUMBER,								--输入参数,雇员薪水
    job VARCHAR2 DEFAULT 'CLERK',				--输入参数,雇员工种默认'CLERK'
    dno NUMBER 								--输入参数,雇员部门编号
)
IS
BEGIN
   INSERT INTO emp
      (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
END;
exec add_emp(111,'test',200,'manager',10)
7.存储函数
--创建一个函数返回hello
create  function helloword()
return varchar2
begin
  return 'helloword';
end;

--创建一个函数返回当前日期
create or replace 
function get_sysdate
return date
is
begin
  return sysdate;
end;
--创建一个函数查询某部门的总工资
create or replace 
function sum_sal(v_deptno number)
RETURN NUMBER
is
  v_sum number :=0;
  --ResultSet
  cursor sals_cursor is select sal from emp where deptno = v_deptno;
begin
   for c in  sals_cursor loop
    v_sum := v_sum+c.sal;
   end loop ;
   return v_sum;
end;

8.触发器

触发器（trigger）是数据库提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会**它执行。触发器经常用于加强数据的完整性约束和业务规则等。

  比如 ，一个员工表，当有员工离职，把员工从员工表从删除，然后自动插入离职员工表。
 
 select * from emp1 --员工表
  select * from emp2  --离职人员表
  
  --员工表与离职表的触发关系
  create trigger  emp1_delete
  after delete -- 删除之后执行
  on emp1 --作用在哪张表上面
  for each row --频率
  begin
    insert into emp2 (empno,ename,job,sal) 
    values(:old.empno,:old.ename,:old.job,:old.sal);
  end;

  delete from emp1 where empno = 7566

  
  --订单表（数量）与库存表（数量）的触发关系
  --库存表
  create table stock(
  sname varchar2(10), 
  scount number 
  );
  --订单表
  create table orders(
  oname varchar2(10), 
  ocount number 
  );
  
  insert into stock values('IphoneX',100);
  insert into orders values('IphoneX',0);
  select * from stock
  select * from orders
  --订单表（数量）与库存表（数量）的触发关系
create trigger  orders_update
after update
on orders
for each row
begin
  update stock set scount =scount- (:new.ocount-:old.ocount)  
              where sname = :new.oname;
end;

update orders set ocount = ocount +5 where oname = 'IphoneX'