plsql

使用前必须的操作，让输出可见

set serveroutput on

##plsql学习内容

1.plsql基本的语法格式
2.记录类型type…is record(…);
3.流程控制：
- 条件判断（两种）
  - 方式一： if ..then elsif then…else…end if;
  - 方式二：case…when …then…end;
- 循环结构（三种）
  - 方式一：loop ….exit when… end loop;
  - 方式二：while…loop….end loop;
  - 方式三：for i in …loop…end loop;
- goto ，exit
4.游标的使用（类似java中的iterator）
5.异常的处理（三种方式）
6.会写一个存储函数（有返回值），存储过程（无返回值）
7.会写一个触发器

plsql的第一个hello，world程序

declare
  --声明变量，类型，游标
begin
  --程序的执行部分（类似java main方法）
  dbms_output.put_line('hello,world!');
--exception
  --针对begin块出现的异常，提供处理的机制
  --when...then...
  --when..then...
end;

查询员工id为100的员工的工资

declare
--声明变量
  v_sal varchar2(20);
begin
--sql语句操作，select...into...from ....where..
  select salary into v_sal from employees where employee_id = 100;
--打印
  dbms_output.put_line(v_sal);
end;

查询员工id为100的员工的工资，邮箱，雇佣日期（在声明数据类型大小的时候不能小于源数据大小）

declare
--声明变量
  v_sal number(10,2);
  v_email varchar2(20);
  v_hire_date date;
begin
--sql语句操作，select...into...from ....where..
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;
--打印
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

使用%type动态获取数据类型

declare
--声明变量
--动态获取数据类型
  v_sal employees.salary%type;
  v_email employees.email%type;
  v_hire_date employees.hire_date%type;
begin
--sql语句操作，select...into...from ....where..
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100;
--打印
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);
end;

使用记录类型来封装对象（好繁琐）

declare
--声明一个记录类型(相当于java中的类)
  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.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);
end;

####练习：

使用 := 来初始化值

declare
--定义并初始化
v_sal number(8,2) :=0;
v_emp_id number(10);

begin
--程序执行部分
      select salary,employee_id into v_sal,v_emp_id from employees where employee_id = 123;
      dbms_output.put_line('employee_id:'||v_emp_id||','||'salary:'||v_sal);
--exception
--异常处理部分

end;

同样使用记录类型封装

declare
--定义记录类型并初始化
type emp_record is record(
v_sal number(8,2) :=0,
v_emp_id number(10)
);

v_emp_record emp_record;
begin
--程序执行部分
      select salary,employee_id into v_emp_record from employees where employee_id = 123;
      dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||','||'salary:'||v_emp_record.v_sal);
--exception
--异常处理部分

end;

自己定义的记录类型

declare 
  type salary_record is record(
       v_name varchar2(20),
       v_sal number(10,2)
  ); 
  v_salary_record salary_record;
begin
  v_salary_record.v_name :='刘德华';
  v_salary_record.v_sal :=120000;
  dbms_output.put_line('name:'||v_salary_record.v_name||',salary:'|| v_salary_record.v_sal);

end;

使用pl/sql对数据的更新操作

declare
  v_emp_id number(10);

begin
  v_emp_id := 123;
  update employees
  set salary = salary+100
  where employee_id = v_emp_id;
  dbms_output.put_line('执行成功');
end;

####流程控制

使用if ..then..elsif..then…else..end id;

--查询150号员工的工资，如果工资大于等于10000则打印'salary>10000';
--若在5000-10000之间，则打印'5000<salary<10000';否则 打印'salary<5000';
declare
   v_sal employees.salary%type;

begin
  select salary into v_sal from employees where employee_id = 150;
  if v_sal >=10000 then dbms_output.put_line('salary>=10000');
  elsif 5000<=v_sal then dbms_output.put_line('5000<=salary<10000');
  else dbms_output.put_line('salary<5000');
  end if;
end;

或者直接将输出语句变成一个变量最后程序结束的时候输出变量就ok

--查询150号员工的工资，如果工资大于等于10000则打印'salary>10000';
--若在5000-10000之间，则打印'5000<salary<10000';否则 打印'salary<5000';
declare
   v_sal employees.salary%type;
   v_temp varchar2(20);

begin
  select salary into v_sal from employees where employee_id = 150;
  if v_sal >=10000 then v_temp :='salary>=10000';
  elsif 5000<=v_sal then v_temp :='5000<=salary<10000';
  else v_temp :='salary<5000';
  end if;
  dbms_output.put_line(v_temp);
end;

case 语句的使用（个人感觉不好用因为 case 中的when后边只能写具体的值不能写范围）

--查询150号员工的工资，如果工资大于等于10000则打印'salary>10000';
--若在5000-10000之间，则打印'5000<salary<10000';否则 打印'salary<5000';
declare
   v_sal employees.salary%type;
   v_temp varchar2(20);

begin
  select salary into v_sal from employees where employee_id = 150;
  v_temp :=
  case trunc(v_sal/5000) when 0 then  'salary<5000'
                         when 1 then '10000>salary>=5000'
                         else  'salary>=10000'
  end;
  dbms_output.put_line(v_temp);
end;

查询122号员工的JOB_ID，若其值为’IT_PROG’,则打印’GRADE: A’;为’AC_MGT’,打印’GRADE: B’;为’AC_ACCOUNT’,打印’GRADE: C’;否则打印’GRADE: D’;

--查询122号员工的JOB_ID，若其值为'IT_PROG',则打印'GRADE: A';
--为'AC_MGT',打印'GRADE: B';
--为'AC_ACCOUNT',打印'GRADE: C';
--否则打印'GRADE: D';
declare
   v_job_id varchar2(10);
   v_temp varchar2(10);

begin
  select job_id into v_job_id from employees where employee_id = 122;
  v_temp := 
          case v_job_id when 'IT_PROG' then 'GRADE: A'
                         when 'AC_MGT'  then 'GRADE: B'
                         when 'AC_ACCOUNT' then 'GRADE: C'
                         else 'GRADE: D'
                           end;
 dbms_output.put_line(v_temp);
end;

##循环结构

1.使用循环语句打印1-100

--使用循环语句打印1-100
--1.初始化条件2.循环体3.循环条件4.迭代条件
declare
  --1
  v_i number(10):=0;

begin
  loop
    --2
    dbms_output.put_line(v_i);
    --3
    exit when v_i >= 100;
    --4
    v_i := v_i + 1;
    end loop;
end;

2.使用while循环语句

--使用循环语句打印1-100
--1.初始化条件2.循环体3.循环条件4.迭代条件
declare
  --1
  v_i number(10):=0;

begin
  while v_i<=100 loop
    dbms_output.put_line(v_i);
    v_i := v_i + 1;
  end loop;
end;

3.使用For倒序循环打印 reverse是倒序

--使用循环语句打印1-100
begin
  for c in reverse 1..100 loop
    dbms_output.put_line(c);
    end loop;
end;

4.使用while循环输出2-100的质数

--输出2-100的质数
declare
  v_i number(3) := 2;
  v_j number(3) := 2;
  v_flag number(1) :=1;  
begin
  while v_i<=100 loop
    while v_j<=sqrt(v_i) loop
      if v_i mod v_j = 0 then v_flag :=0;
      end if;
      v_j := v_j + 1;
      end loop;
      if v_flag = 1 then dbms_output.put_line(v_i); 
      end if;
      v_i := v_i + 1;
      v_j := 2;
      v_flag := 1; 
      end loop;
end;

使用if来解决使用goto语句提高效率

--输出2-100的质数
declare
  v_i number(3) := 2;
  v_j number(3) := 2;
  v_flag number(1) :=1;  
begin
  for v_i in 2..100 loop
    for v_j in 2..sqrt(v_i) loop 
      if mod(v_i,v_j) = 0 then v_flag := 0;
      goto label;
      end if;
      end loop;
      <<label>>
      if v_flag = 1 then dbms_output.put_line(v_i);
      end if;
      v_flag :=1;
      end loop; 
end;

打印1-100的自然数，当打印到50时，跳出循环，输出’打印结束’（使用if）

第一种方式使用 goto

--打印1-100的自然数，当打印到50时，跳出循环，输出'打印结束'
declare

begin

  for i in 1..100 loop
    if i =50 then goto lable;
    end if;
    dbms_output.put_line(i);

    end loop;
    <<lable>>
    dbms_output.put_line('打印结束');
end;

第二种方式使用exit

--打印1-100的自然数，当打印到50时，跳出循环，输出'打印结束'
declare

begin

  for i in 1..100 loop
    if i =50 then  dbms_output.put_line('打印结束');
    exit;
    end if;
    dbms_output.put_line(i);

    end loop;

end;

##游标

在pl/sql中处理多行的事务通常使用游标来实现。

显式的游标处理需要四个pl/sql步骤

1.定义游标
2.打开游标
3.提取游标数据
4.关闭游标

1.使用游标打印出80号部门所有员工的编号和工资：

--打印出80号部门所有员工的编号和工资
declare
  v_sal employees.salary%type;
  v_empid employees.employee_id%type;
  --定义游标
  cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 80;
begin
  --打开游标
  open emp_sal_cursor;
  --提取游标
  fetch emp_sal_cursor into v_sal,v_empid;
  --判断下一个是否有值
  while emp_sal_cursor%found loop
    dbms_output.put_line('empid:'||v_empid||',salary:'||v_sal);
    fetch emp_sal_cursor into v_sal,v_empid;
    end loop;
  --关闭游标
  close emp_sal_cursor;
end;

2.将declare定义的变量放入记录类型中

--打印出80号部门所有员工的工资：salary：xxx
declare
  --声明一个记录类型
  type emp_record is record(
  v_sal employees.salary%type,
  v_empid employees.employee_id%type

  );
  --声明一个记录类型的变量
  v_emp_record emp_record;
  --定义游标
  cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 80;
begin
  --打开游标
  open emp_sal_cursor;
  --提取游标
  fetch emp_sal_cursor into v_emp_record;
  --判断下一个是否有值
  while emp_sal_cursor%found loop
    dbms_output.put_line('empid:'||v_emp_record.v_empid||',salary:'||v_emp_record.v_sal);
    fetch emp_sal_cursor into v_emp_record;
    end loop;
  --关闭游标
  close emp_sal_cursor;
end;

###游标属性
%found 布尔型，当最近一次读记录时成功返回true

%nofound 布尔型，与%found相反

%isopen 布尔型，当游标已打开时返回true

%rowcount 数字型，返回已从游标中读取的记录数

使用for循环简化游标操作

--打印出80号部门所有员工的工资：salary：xxx
declare
  --定义游标
  cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 80;
begin
  for c in emp_sal_cursor loop
    dbms_output.put_line('empid:'||c.employee_id||',salary:'||c.salary);
    end loop;
    end;

利用游标，调整公司中员工的工资：

/*
  利用游标，调整公司中员工的工资：
      工资范围        调整基数
      0-5000            %5
      5000-10000        %3
      10000-15000       %2
      15000-            %1
*/

declare
      cursor emp_sal_cursor is select employee_id,salary from employees;
      --用于记录调整基数
      v_temp number(4,2);

      v_empid employees.employee_id%type;
      v_sal employees.salary%type;
begin
  open emp_sal_cursor;

  fetch emp_sal_cursor into v_empid,v_sal;

  while emp_sal_cursor%found loop
    if v_sal < 5000 then v_temp := 0.05;
    elsif v_sal < 10000 then v_temp := 0.03;
    elsif v_sal < 15000 then v_temp := 0.02;
    else v_temp := 0.01;
    end if;
    dbms_output.put_line(v_empid||','||v_sal);
    update employees
    set salary = salary*(1 + v_temp)
    where employee_id = v_empid;

    fetch emp_sal_cursor into v_empid,v_sal;
    end loop;
    close emp_sal_cursor;
    end;

使用if 来解决

/*
  利用游标，调整公司中员工的工资：
      工资范围        调整基数
      0-5000            %5
      5000-10000        %3
      10000-15000       %2
      15000-            %1
*/

declare
      cursor emp_sal_cursor is select employee_id,salary from employees;
      --用于记录调整基数
      v_temp number(4,2);
begin
  for c in emp_sal_cursor loop
    if c.salary < 5000 then v_temp := 0.05;
    elsif c.salary <10000 then v_temp := 0.03;
    elsif c.salary <15000 then v_temp := 0.02;
    else v_temp :=  0.01;
    end if;
    update employees
    set salary = salary * (1 + v_temp)
    where c.employee_id = employee_id;
    end loop;
    end;

隐式游标：更新指定员工salary（涨工资10），如果该员工没有找到，则打印’查无此人’信息

--隐式游标：更新指定员工salary（涨工资10），如果该员工没有找到，则打印'查无此人'信息    
begin 
  update employees
  set salary = salary + 10
  where employee_id = 1001;
  if sql%notfound then dbms_output.put_line('查无此人');
  end if;
  end;

##异常处理

处理预定义的异常

declare
  v_sal employees.salary%type;

begin
  select salary into v_sal
  from employees
  where employee_id > 100;

  dbms_output.put_line(v_sal);

exception
  --捕获行数太多的异常
  when too_many_rows then dbms_output.put_line('输出的行数过多');
  --捕获预定义的异常
  when others then dbms_output.put_line('出现其他类型的异常');
end;

如果不是预定义异常就可以自定义异常

declare
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);
begin
  delete from employees
  where employee_id = 100; 

exception
  when e_deleteid_exception then dbms_output.put_line('违反完整性约束条件不可删除');

end;

使用自定义异常如果工资大于10000触发自定义异常

--使用自定义异常如果工资大于10000触发自定义异常
declare
  e_too_high_salary 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_salary;
    end if;
exception
  when e_too_high_salary then dbms_output.put_line('工资太高了！');
    end;

##存储函数和存储过程

存储函数的形式：

--存储函数
create or relace function func_name(dept_id number,salary number)
return number
is
       --函数的使用过程中，需要声明的变量。记录类型。cursor
begin
       --函数的执行体

exception
       --处理函数执行过程中的异常

end;

创建一个函数的hello_world 返回一个hello,world!的字符串

--函数的hello,world 返回一个hello,world!的字符串
create or replace function hello_world
return varchar2
is
begin
  return 'hello,world!';

end;

调用hello,world函数

第一种方式

begin
  dbms_output.put_line(hello_world);
end;

第二种方式调用

select hello_world from dual

增加一个v_logo参数调用方法的时候只需要在后边加上括号和参数就行

--函数的hello,world 返回一个hello,world!的字符串
create or replace function hello_world(v_logo varchar2)
return varchar2
is
begin
  return 'hello,world!'||v_logo;

end;

创建一个存储函数返回当前系统存储的时间

--创建一个存储函数返回当前系统存储的时间
create or replace function sys_date
return date
is
       v_date date;
begin
       v_date := sysdate;

       return v_date;
end;

定义带参数的函数两个数相加

--定义带参数的函数 两个数相加
create or replace function add_param(v_num1 number,v_num2 number)
return number
is
      v_sum number(10);
begin
  v_sum := v_num1 + v_num2;
  return v_sum;
end;

调用

select add_param(1,2) from dual;

定义一个函数获取给定部门的工资总和，要求部门号定义为参数，工资总额定义为返回值

--定义一个函数获取给定部门的工资总和，要求部门号定义为参数，工资总额定义为返回值

create or replace function sum_sal(dept_id number)
return number
is
v_sum_sal number(20) := 0;
cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
       for c in salary_cursor loop
         v_sum_sal := v_sum_sal +c.salary;
         end loop;
         return v_sum_sal; 
end;

使用out类型的参数相当于增加返回值的个数
要求: 定义一个函数: 获取给定部门的工资总和和该部门的员工总数(定义为 OUT 类型的参数).部门号定义为参数, 工资总额定义为返回值.

--要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
--要求: 部门号定义为参数, 工资总额定义为返回值.
create or replace function get_sal(dept_id number,total_count out number)
return number
is
       v_sumsal number := 0;
       cursor salary_cursor is select salary from employees where department_id = dept_id;

begin
  total_count := 0;
  for c in salary_cursor loop
    v_sumsal := v_sumsal + c.salary;
    total_count := total_count +1;
    end loop;

    return v_sumsal; 
    end;

调用函数的方法需要额外指定参数来接收out类型中的参数的值

declare
  v_num number(5) := 0;

begin
  dbms_output.put_line(get_sal(80,v_num));
  dbms_output.put_line(v_num);
end;

####存储过程

存储过程就是没有返回值的存储函数

1.要求: 定义一个存储过程，获取给定部门的工资总和（通过out参数）要求部门号和工资总额定义为参数

--要求: 定义一个存储过程，获取给定部门的工资总和（通过out参数）要求部门号和工资总额定义为参数
create or replace procedure get_sal1(dept_id number,sumsal out number)

is
       cursor salary_cursor is select salary from employees where department_id = dept_id;

begin
  sumsal := 0;
  for c in salary_cursor loop
    sumsal := sumsal + c.salary;
    end loop;
    dbms_output.put_line(sumsal);
    end;

调用存储过程

declare 
  v_sal number(10) := 0;

begin
  get_sal1(80,v_sal);
  end;

自定义一个存储过程完成以下操作:

    /*
自定义一个存储过程完成以下操作: 
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间,    为其加薪 %5
                                                               [95 , 98)             %3       
                                                               [98, ?)               %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).
*/
create or replace procedure add_sal(dept_id number,temp_sal  out number)
is
       cursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;
       v_i number(4,2) := 0;
begin
  temp_sal := 0;
  for c in sal_cursor loop
    if to_char(c.hire_date,'yyyy') < '1995' then v_i := 0.05;
    elsif to_char(c.hire_date,'yyyy') < '1998' then v_i := 0.03;
    else v_i := 0.01;
    end if;
    --1.更新工资
    update employees 
    set salary = salary * (1 + v_i) where employee_id = c.employee_id ;
    --2.付出的成本
    temp_sal := temp_sal + c.salary * v_i;
    end loop;
    dbms_output.put_line(temp_sal);
end;

执行

declare 
  v_temp number(10);

begin
  add_sal(80,v_temp);
  end;

##触发器

组成：

触发事件：在何种情况下触发TRIGGER；例如insert update delete
触发时间：是在触发事件发生之前还是之后触发，也就是触发事件和触发器的顺序
触发器本身：即该触发器被触发之后的目的和意图，正是触发器本身要做的事，例如PL/SQL块；
触发频率：说明触发器内定义动作被执行的次数，即语句级触发器和行级触发器。
- 语句级触发器：当某触发事件发生时，该触发器执行一次
- 行级触发器：当某触发事件发生，对受到该操作影响的每一行数据，触发器都单独执行一次。

创建一个触发器，当你对employees表执行update时，输出hello,world!.如果将for each row删除就是语句触发器

create or replace trigger update_emp_trigger
after
       update on employees
for each row
begin
  dbms_output.put_line('hello,world!');

end;

每添加一次触发一次

create or replace trigger update_emp_trigger3
after
       insert on emp_test
begin
  dbms_output.put_line('hello,world!');

end;

使用 :new :old修饰符
将更新前后的值全部输出

create or replace trigger update_emp_trigger2
after
       update on emp_test
for each row
begin
  dbms_output.put_line('old:salary:'||:old.salary||','||'new:salary:'||:new.salary);

end;

编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录

--编写一个触发器, 在对 my_emp 记录进行删除的时候, 在 my_emp_bak 表中备份对应的记录
create or replace trigger delete_emp_trigger
before
delete on my_emp
for each row
begin
  insert into my_emp_bak
  values(:old.employee_id,:old.salary);
end;

pl/sql

plsql

近期文章

近期评论

标签

热门

文章归档

分类目录

功能