plsql复习（基本语法、流程控制、游标、异常处理机制、存储函数和存储过程、触发器）

最新推荐文章于 2024-07-10 17:22:06 发布

乐楽樂o

最新推荐文章于 2024-07-10 17:22:06 发布

阅读量288

点赞数

分类专栏： Oracle 数据库-sql plsql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/Jack_Pearson/article/details/129157449

版权

Oracle 数据库-sql plsql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、基本语法

使用set serveroutput on 命令设置环境变量serveroutput为打开状态，从而使得pl/sql程序能够在SQL*plus中输出结果
使用函数dbms_output.put_line()可以输出参数的值。

set serveroutput on

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;

二、流程控制

--用while循环求2-100之间的质数（素数）
declare
  v_i number(3) := 2;
  v_j number(3) := 2;
  v_flag number(1) := 1;
begin
  while v_i <= 100 loop
--为什么是 j <= i/j 例如 i = 20，那么可能整除的组合就是 2 * 10、4 * 5、5 * 4、10 * 2。
--可以发现组合是重复的了一次的
--所以只要 j <= i/j 就能试完所有的情况，即j<=sqrt(i)    
        while v_j <= sqrt(v_i) loop
          
              if mod(v_i,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_j := 2;
        v_i := v_i + 1;
        v_flag := 1;
  end loop;
        
end;

--用for循环求2-100之间的质数（素数）
declare
  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时，跳出循环，输出“打印结束”
begin
  
  for i in 1..100 loop
    if i = 50 then goto label;
    end if;
    
    dbms_output.put_line(i);
    
  end loop;
  <<label>>
  dbms_output.put_line('打印结束');  
end;

三、游标

游标是一个指向上下文的句柄( handle)或指针。

--打印出80号部门所有员工的员工号和工资：empid:xxx 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;

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

  工资范围        调整基数
  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;

--使用sql中的decode函数实现
update employees
set salary = salary * (1 + (decode(trunc(salary/5000),0,0.05,
                                                      1,0.03,
                                                      2,0.02,
                                                      0.01)))

--使用for循环实现
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 employee_id = c.employee_id; 
  end loop;
    
end;

四、异常处理机制

1.预定义的异常处理

declare
  v_salary employees.salary%type;
begin
  select salary into v_salary
  from employees
  where employee_id > 100;
  
  dbms_output.put_line(v_salary);
exception
  when too_many_rows then dbms_output.put_line('输出的行数太多了！！');
  when others then dbms_output.put_line('出现其他类型的异常');
end;

--通过select...into...查询某人的工资。若没有查询到，则输出“未找到数据”
declare
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id = 1001;
  
  dbms_output.put_line(v_sal);
exception
  when no_data_found then dbms_output.put_line('未找到数据');
end;

2.非预定义的异常处理

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

3.用户自定义的异常处理

--查询员工号为100的员工工资，如工资大于10000，抛出异常并进行异常处理："工资太高了！！"
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_sal then dbms_output.put_line('工资太高了！！');

end;

*五、存储函数和存储过程

过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

1.存储函数(有返回值)

--定义一个函数hello_world：返回一个"helloworld"的字符串
create or replace function hello_world
return varchar2
is
begin
  return 'helloworld';
end;

--定义一个函数：获取给定部门的工资总和。要求：部门号定义为参数，工资总额定义为返回值
create or replace function get_sal(dept_id number)
return number
is
       v_sumsal number(10) := 0;
       cursor salary_cursor is select salary from employees where department_id = dept_id;
       
begin
       for c in salary_cursor loop
         v_sumsal := v_sumsal + c.salary;
       end loop;
       
       return v_sumsal;
end;

函数名后面是一个可选的参数列表，其中包含 IN、OUT 或 IN OUT 标记。参数之间用逗号隔开。
IN 参数标记表示传递给函数的值在该函数执行中不改变；
OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句；
IN OUT 标记表示传递给函数的值可以变化并传递给调用语句。
若省略标记，则参数隐含为 IN。

--定义一个函数：获取给定部门的工资总和 和 该部门的员工总数（定义为OUT类型的参数）。
--要求：部门号定义为参数，工资总额定义为返回值
create or replace function get_sal1(dept_id number,total_count out number)
return number
is
       v_sumsal number(10) := 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;
----------------------------------------------

declare
  v_num number(5) := 0;
begin
  dbms_output.put_line(get_sal1(80,v_num));
  dbms_output.put_line(v_num);
end;

2.存储过程(没有返回值)

--定义一个存储过程：获取给定部门的工资总和（通过OUT函数）。要求：部门号和工资总额定义为参数
create or replace procedure get_sal2(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_sal2(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;

*六、触发器

1.修饰符:NEW :OLD

:NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的值

--编写一个触发器,在对my_emp记录进行删除的时候,在my_emp_bak表中备份对应的记录
1)准备工作:
create table my_emp as select employee_id id, last_name name, salary sal from employees

create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2
2)编写触发器
create or replace trigger delete_emp_trigger
before delete on my_emp
for each row
begin
  insert into my_emp_bak
  values(:old.id,:old.name,:old.sal);
end;