数据库从入门到删库跑路(二) - - PL/SQL

数据库从入门到删库跑路 - - PL/SQL

1.PL/SQL(Procedure Language & Structured Query Language)

• 定义:一种高级数据库程序设计语言,该程序专门用于在各种环境下对ORACLE数据库进行访问,由于该语言集成于数据库服务器中,所以PL/SQL可以对数据进行快速高效的处理

• PL/SQL块

  • 声明部分
  • 执行部分
  • 异常处理部分

• 变量命名在PL/SQL中有特殊的讲究

  • 程序变量 V_name
  • 程序常量 C_name
  • 游标变量 Name_cursor
  • 异常标识 E_name
  • 表类型 Name_table_type
  • 表 Name_table
  • 记录类型 Name_record
  • SQL*PLUS 替代变量 P_name
  • 绑定变量 G_name

DECLARE
  // 声明部分
BEGIN
  // 执行部分
EXCEPTION
  // 执行异常部分
END;
其中执行部分是必须的

1.写一个简单的PL/SQL语句
a.命令行窗口处输入:set serveroutput on
b.
declare
--- 声明的变量,类型,游标
begin
--- 程序的执行部分(类似于java的main()方法)
dbms_output.put_line('Hello World');

exception
--- 针对begin块出现的异常,提供处理的机制
--- when .... then
--- when .... then
end;
2.查询100号员工的工资
DECLARE 
   v_sal NUMBER(10,2);
   v_email VARCHAR2(20);
   v_hire_date date;
BEGIN
   select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id=100'
   dbms_output.put_lines(v_sal||','||v_email||','||v_hire_date);
END

注:在声明部分获取变量类型的方式: v_sal employees.salary%type;
使用%type特性的优点:
  - 所引用的数据索引的数据类型可以不必知道
  - 所引用的数据库列的数据类型可以实时改变

2.记录类型

• 定义:记录类型是把逻辑相关的数据作为一个单元存储起来,称作PL/SQL RECORD 的域(FIELD),其作用是存放互不相同但逻辑相关的信息

declare
--- 声明一个记录类型
  type emp_record is record(
    v_sal employees.salary%type,
    v_email employees.email%type,
    v_hire_date employees.hire_date%type
  );
--- 定义一个记录类型
  v_emp_record emp_record;
begin
---sql语句的操作: select...into...from...where...
  select salary,email,hire_date into v_emp_record from employees where employee_id=100;
  dbms_output.println(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hiredate)
end;

eg2
declare
   v_sal number(5,2):=0;
   v_emp_id number(10);
begin
   select salary,employee_id into v_sal,v_employee_id
   from employees
   where employee_id=123

   dbms_output.println('employeeid:'||v_emp_id||'salary:'||v_sal);


end;

3.流程控制

• 条件判断
  • if…then elsif then…else…end if;
  • case…when…when…end;
• 循环结构
  • loop…exit when… end loop;
  • while…loop…end loop
  • for i in … loop…end
• goto 相当于break,exit

1.if类型
查询出150号员工的工资,若其工资大于等于10000 则打印'salary>=10000';
若在500到10000之间,则打印'5000<=salary<10000' 否则打印'salary<5000'

declare
  
  v_salary employees.salary%type;

begin

select salary into v_salary from employees where employee_id=150;

if v_sal>=10000 then dbms_output.put_line('salary>=100000');
elsif v_sal>=5000 then dbms_output.put_line('5000<salary<10000');
else dmbs_output.put_line('salary<5000');
end if;

end;
2.case类型(缺点:只能对离散的值有用)
eg1.
declare
  v_sal employee.salary%type
  v_temp varchar(30)
begin
  select salary into v_sal from employees where employee_id=150
  v_temp:=
  case trunc(v_sal/100) when 0 then 'salary<=5000'
                        when 1 then '5000<=salary<10000'
                        else 'salary?=10000'
  end;
  dbms_output.put_line(v_sal||','||v_temp);
end;
eg2.
decare 
 v_job_id varchar2(10)
 v_temp varchar2(10)
begin
 select job_id into v_job_id from employee employee_id=122

 v_temp:=
          case v_job_id when 'IT_PROG' then 'A'
                        when 'AC_NGT'  then 'B'
                        when 'AC_ACCOUNT' then 'C'
                        else 'D'
          end;
 dbms_output.put_line(v_job_id,||','|| v_temp)       
end

3. loop循环
declare
   v_i number(5):=1;
begin
  loop
      dbms_output_println(v_i);
  exit when v_i>=100;
       v_i=v_i+1
  end loop;

end;

4. while循环
declare
   v_i number(5):=1;
begin
   while v_i<=100 loop
         dbms_output_println(v_i);
         v_i=v_i+1;
   end loop;
end;

5.for循环
beign 
  for c in 1..100 loop
      dbms_output.printline(c);
  end loop;
end;

4.游标的使用(类似于java中的Iterator)

• 显示游标
• 隐式游标

打印出80部门的所有员工的工资:salary:xxx

declare 
    v_sal employees.salary%type
begin
   --- 定义游标
   cursor emp_sal_cursor is select salary from employees where department_id=80
   -- 打开游标
   open emp_sal_cursor
   -- 提取游标
   fetch emp_sal_cursor into v_sal;

   while emp_sal_cursor%found loop
         dbms_output.put_line('salary:'|| v_sal)
         fetch emp_sal_cursor into v_sal
   end loop;
  -- 关闭游标
   close emp_sal_cursor;
end;

5.异常的处理(三种方式)

• 异常处理:是用来处理正常执行过程中未预料的时间,程序块的异常处理预定义的错误和自定义错误和自定义错误
• 异常错误类型:
  • 预定义(Predefined) 错误
  • 非预定义的错误
  • 用户定义错误

1. 预定义异常
declare
  v_salary employees.salary%type;
begin
 select salary into v_salary
 from employees
 where employee_id>100

 dbms_output.println('查无此人')
exception
  when too_many then dbms_output.println('输出的行数太多了!!');
  when others then dbms_output.prinrln('出现其他类型的异常!');
end;


2.非预定异常
delete from employee
wheres where_id=100    ----产生外键异常

declare
   e_deleteid_exception exception;
   pragma exception_init(e_delete_id_exception,-2292);
begin
   delete from employees where employee_id=100
exception
  when e_deleteid_exception then dbms_output.println('违反完整性约束条件,不可删除此用户!!')

3.用户自定义异常
declare 
   e_too_high_sal exception;
   v_sal employees.salary%type;
begin
   select salary into v_sal from employees where employee_id=100;
   if v_sal>10000 then
   raise e_too_high_sal;
   end if;
exception
 when e_too_high dbms_output.println('工资太高了!!');
end;

6.存储函数(有返回值)与存储过程(无返回值)

1.存储函数基本格式
create or replace function func_name(dept_id,number,sal)
return number
is
          --- 函数使用过程中,需要声明的变量,记录类型,cursor
begin
          --- 函数的执行体
exception

end;
2.写一个简单的存储函数
create or replace function hello_world
return varchar2
is 
begin
   return 'Hello World'
end;
3.存储函数例2
create or replace function hello_word(v_logo varchar2)
return varchar2
is
begin
    return 'Helloworld'||v_logo;
end;
调用:hello_world(' xiaoyao')

7.触发器

• 是由一个事件来启动运行的
• 触发事件组成:
  • 触发事件
  • 触发时间
  • 触发器本省
  • 触发频率

1. 创建一个简单的触发器
create or replace trigger update_emp_trigger
after 
   update on employees
for each row;  // 每行执行
begin 
   dbms_output.println('Hello World')
end;
2.插入数据时执行触发器
create or replace trigger insert_emp_trigger
after
  dinsert on employees
begin
   dbms_output.println('Hello World')
end;
3.打印新的值与旧的值:old,:new