pl/sql编程

什么是pl/sql:

• PL/SQL是 Procedure Language & Structured Query LanguProcedure的缩写
• 是一种过程处理语言
• PL/SQL是 ORACLE系统的核心语言，ORACLE的许多部件都是由 PL/SQL编写的
• PL/SQL的好处： PL/SQL在数据库服务器上运行，节约网络带宽，在数据库端运行效率更高

PL/SQL块:

PL/SQL程序由三个块组成：声明部分，执行部分，异常处理部分

PL/SQL程序的结构如下：

标识符：

与SQL标识符要求相同

• 标识符不能超过30个字符
• 第一个字符必须是字母
• 不分大小写
• 不能使用“-”（减号）
• 不能是SQL保留字

注意：一般不要把变量名与表中字段名完全一样，可能会得到不正确的结果

变量类型：

 

%TYPE：

定义一个变量，其数据类型与已经定义的某个变量的类型相同，或者与表中某个列的数据类型相同

代码示例：

--打印员工编号为102的薪水
declare
i_sal employees.salary%type;
begin

select salary into i_sal from employees where employee_id=102;
dbms_output.put_line('102 salary is ' ||  i_sal);
end;

运行结果：

%ROWTYPE：

与表中一条记录的类型相同

代码示例：

declare 
  v_emp employees%rowtype;
  begin
   select * into v_emp from employees where employee_id=200;
   dbms_output.put_line('employee_id:' ||v_emp.employee_id); 
   dbms_output.put_line('first_name:' ||v_emp.first_name); 
   dbms_output.put_line('salary:' ||v_emp.salary); 
   dbms_output.put_line('department_id:' ||v_emp.department_id); 
  end;

运行结果：

关系运算符：

一般运算符：

逻辑运算：

变量赋值：

variable := expression ;

布尔值只有 TRUE, FALSE，NULL三个值

代码示例：

 --删除id是5的学生
  declare
  v_stud_id number:=5;
  begin
    delete from student where stud_id=v_stud_id;
    dbms_output.put_line('stud_id:'||v_stud_id||'is deleted');
    end;
    

   --打印id为102 员工的名字和薪水
    declare
    v_emp_id number:=102;
    v_fname employees.first_name%type;
    v_salary employees.salary%type;
    begin
    select first_name,salary into v_fname,v_salary from employees where 
    employee_id=v_emp_id;
    dbms_output.put_line('emp_is:' || v_emp_id || 'name:'||v_fname||
    'salary:'|| v_salary);
    end;

 

注释：

--

/*  */

条件语句：

代码示例：

  declare
    v_emp_id number:=102;
    v_sal employees.salary%type;
    v_mag varchar2(30);
    begin
    select salary into v_sal from employees where employee_id=v_emp_id;
    if v_sal>15000 then v_mag:='非常高';
      elsif v_sal >10000 then v_mag:='还可以';
        else v_mag:='不够花';
     end if;
     dbms_output.put_line(v_sal|| ':' ||v_mag);
    end;

运行结果：

CASE表达式：

代码示例：

   --用case语句
    declare
    v_level char(1):='A';
    v_msg varchar2(20);
    begin 
      v_msg :=case v_level when 'A' then '优秀'
      when 'B' then '良好'
      when 'C' then '及格'
        else '不及格'
          end; 
     dbms_output.put_line(v_msg);
     end;  

简单循环：

语法：

Loop 要执行的语句；

Exit when<条件语句>;/*满足条件则退出

End loop;

代码示例：

    --loop循环
    declare
    v_count number :=0;
    begin
      loop
        v_count :=v_count+1;
        dbms_output.put_line(v_count);
        exit when v_count=10;
        end loop;
     end;

输出：

While循环：

语法:

代码示例：

 --while循环
     declare
     v_count number :=0;
     begin 
       while v_count<10
         loop
           v_count:=v_count+1;
           dbms_output.put_line(v_count);
           
           end loop;
      end;

for 循环：

语法：

说明：

• 每循环一次，循环变量自动加1（使用reverse时自动减1）
• 下限和上限必须是由小到大，而且必须是数字
• 可以使用exit退出循环

代码示例：

  
      begin
        for counter in reverse 1..10
          loop
            dbms_output.put_line(counter);
            end loop;
            
       end;

标号和goto:

 

函数和存储过程:

• Oracle可以把pl/sql程序存储在数据库中，你可以在需要的时候运行它，这就是存储过程和函数
• 存储过程和函数就是被命名的pl/sql块
• 通过返回值，输入输出参数和调用者交换信息
• 存储过程和函数的区别是函数总向调用者返回数据，而存储过程不返回数据。

函数语法:

代码示例：

 --获取系统时间
 create or replace function fun_get_sysdate
 return date
 is
 v_date date;
 begin
 select sysdate into v_date  from dual;
 dbms_output.put_line('this is function!');
 return v_date;
 end;
 
 declare
 my_date date;
 begin
   my_date:=fun_get_sysdate;
   dbms_output.put_line(my_date);
   end;
   

  
  --获取某部门的工资总和
create or replace function fun_sum
(v_dept_id in number,
v_emp_count out number)
return number
is
v_sum number;
begin
  select sum(salary),count(*) into v_sum,v_emp_count
  from employees
  where department_id=v_dept_id;
  return v_sum;
exception
    when no_data_found then
      dbms_output.put_line('部门编号错误，无此部门');
      when others then
      dbms_output.put_line(sqlcode || ':'||sqlerrm);
end;
  
   
 declare
 v_sum number;
 v_empnum number;
 begin 
   v_sum:=fun_sum(60,v_empnum);
   dbms_output.put_line(v_sum ||':empnum-'||v_empnum);
 end;
     

参数类型有in，out，in out，如不指定，默认为in

存储过程语法：

代码示例：

 --存储过程
 --查询指定员工
 create or replace procedure proc_emp_sal
 (p_emp_id employees.employee_id%type default 100,
 p_name out employees.first_name%type,
 p_sal out employees.salary%type
 )
 is
 begin
 
 select first_name,salary into p_name,p_sal  from 
 employees where employee_id=p_emp_id;
 exception
   when no_data_found then
     dbms_output.put_line('查无此人');
     when others then
       dbms_output.put_line('error:'||sqlcode||'--'||sqlerrm);
  end;
  
  declare
  v_name varchar2(50);
  v_sal number;
  begin
    proc_emp_sal(102,v_name,v_sal);
    dbms_output.put_line(102||'name:'||v_name||'salary:'||v_sal);
  end;