PL/SQL 总结

begin
  dbms_output.put_line('HelloWorld');
end;

declare
  v_sal number(20) :=0;    --声明变量，要注意的是，数据类型和要查询的表中的数据类型是对应得
			  --附一个默认值，:= 
  v_email varchar2(20);--另一种写法，v_email employees.email % type
  v_hire_date date;    --v_hire_date employees.hire_date % type   //v_hire_date与employees表中的hire_date类型一样
			--%type ，如果自定义类型的精度不够，那么%type就是动态的获取表中的类型及精度
begin 
  select salary,email , hire_date into v_sal,v_email,v_hire_date
   from employees where employee_id = 100;
  --实现select操作，注意是select into
  dbms_output.put_line(v_sal||','||v_email||','||v_hire_date);--打印
end;

declare
     v_emp_id employees.employee_id%type;
begin
     v_emp_id :=100;
     update employees
     set salary = salary + 100
     where employee_id = v_emp_id;
     dbms_output.put_line('OK');
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
      SELECT salary , email, hire_date INTO v_emp_record
      FROM employees WHERE employee_id = 101;
      dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||v_emp_record.v_hire_date);
END;

declare
     v_emp_record employees%rowtype;
     v_emp_id number(10);
begin
     v_emp_id  := 100;
     select * into v_emp_record
     from employees
     where employee_id = v_emp_id;
     dbms_output.put_line(v_emp_record.salary||' , '||v_emp_record.last_name);--用表中的列名字即可
end; 

declare
     v_sal employees.salary%type;
     v_result varchar2(20);
begin
     select salary into  v_sal from employees where employee_id = 150;
     if v_sal >= 10000 then v_result := 'salary >= 10000';
     elsif v_sal >=5000 then v_result := 'salary >= 5000';
     else v_result := 'salary < 5000';
     end if;
     dbms_output.put_line(v_sal||' , '||v_result);
end;

declare
     v_sal employees.salary%type;
     v_result varchar2(20);
begin
     select salary into  v_sal from employees where employee_id = 150;
     v_result :=
     case trunc(v_sal/5000) when 2  then 'salary >= 10000'
                            when 1  then '5000 <= salary < 10000'
                            when 0  then 'salary < 5000'
     end;
     dbms_output.put_line(v_sal||' , '||v_result);
end;

declare
       --(1)
       v_i number(3) := 1;
/*方式一
begin
       loop
       --(2)
           dbms_output.put_line(v_i);
           --(4)
          v_i := v_i + 1;
       --(3)
       exit when v_i >100;
       end  loop;--注意每一行，都要有“分号”
end;*/
方式二：（推荐使用 ）
begin
       while v_i <= 100 loop
             dbms_output.put_line(v_i);
             v_i := v_i + 1;
        end loop;
end;


/*方式三
begin
        for i in 1..100 loop --for i in reverse 1..100 loop，这样就是反着，从100-1，还要注意in后面是两个“.”
            dbms_output.put_line(i);
        end loop;
end;
*/

declare
  v_i number(3) := 2;
  v_j number(3) := 2;
  v_flag number(3) := 1;
begin
  /*while v_i<=100 loop
  
        while v_j <= sqrt(v_i) loop
              if mod(v_i,v_j)=0 then v_flag := 0;
              goto OK;
              end if;
          v_j := v_j + 1;
      
        end loop;
  <<OK>>
  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;
  */
  for i in 2..100 loop
      for j in 2..sqrt(i) loop
          if mod(i,j)=0 then v_flag := 0;
          goto OK;
          end if;
      end loop;
      <<OK>>
            
      if v_flag = 1 then dbms_output.put_line(i);
      end if;
      v_flag := 1;
  end loop;      
end;

declare
  --定义一个记录类型
  type v_record is record(
       v_sal employees.salary%type,
       v_last_name employees.last_name%type
  );
  --声明一个记录类型
  v_emp_record v_record;
  --定义一个游标
  cursor emp_sal_cursor is select salary,last_name 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(v_emp_record.v_last_name||' : '||v_emp_record.v_sal);
        fetch emp_sal_cursor into v_emp_record;
  end loop;
  close emp_sal_cursor;
end;

declare
  cursor emp_sal_cursor is select salary,last_name from employees where department_id = 80;
begin


  for c in emp_sal_cursor loop 
      dbms_output.put_line(c.last_name||c.salary);
      end loop;
end;

declare
    --定义游标
    cursor emp_sal_cursor(dept_id number, sal number) is 
           select salary + 1000 sal, employee_id id 
           from employees 
           where department_id = dept_id and salary > sal;
    
    --定义基数变量
    temp number(4, 2);
begin
    --处理游标的循环操作
    for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
          --判断员工的工资, 执行 update 操作
          --dbms_output.put_line(c.id || ': ' || c.sal);
          
          if c.sal <= 5000 then
             temp := 0.05;
          elsif c.sal <= 10000 then
             temp := 0.03;   
          elsif c.sal <= 15000 then
             temp := 0.02;
          else
             temp := 0.01;
          end if;
          
          dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
          --update employees set salary = salary * (1 + temp) where employee_id = c.id;
    end loop;
end;

declare 
  v_sal employees.salary%type;
  v_empid employees.employee_id%type := 101;
begin
  update employees
  set last_name = 'ABC'
  where employee_id = v_empid;
  if sql%notfound then --
     dbms_output.put_line('查无此人');
  end if;
end;

EXCEPTION
WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >
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;

SQL>delete employees
where employee_id = 100
ORA-02292: 违反完整约束条件 (SCOTT.DEPT_MGR_FK) - 已找到子记录
因为自己表里有manager_id也指向自己表里的employee_id，而删除emp_id，因为manager_id指向，所以或无法删除

declare
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);--将2292这个错和自己定义的异常名称关联起来
begin
  delete employees
  where employee_id = 100;
exception
  when e_deleteid_exception then dbms_output.put_line('违反完整性约束异常!');
end;

用户自定义的异常处理

declare
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);
  e_too_high_sal_exception exception; --自定义异常
  v_sal employees.salary % type;
begin 
  delete from employees where employee_id = 100;
  select salary into v_sal from employees where employee_id = 100;
  if v_sal > 10000 then 
  raise e_too_high_sal_exception;   --抛出异常
  end if;
exception
  when e_too_high_sal_exception then dbms_output.put_line('工资太高了');
  when e_deleteid_exception then dbms_output.put_line('违反完整性约束异常');
  when others then dbms_output.put_line('其他异常');
end;

创建函数
1.  建立内嵌函数
语法如下：
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;

create or replase function func_name (depart_id number, salary number)
return number --返回某部门所有的工资
is     
       --函数在使用过程中，需要声明的变量、记录类型record、游标cursor
begin
       --函数的执行体
exception
       --处理函数执行过程中的异常
end;

create or replace function HelloDemo
return varchar2
is
begin
       return 'Hello,World';
end;

begin
  dbms_output.put_line(HelloDemo);
end;

SQL>select HelloDemo from dual; 

/*
create or replace function HelloDemo(v_world varchar2)
return varchar2
is
begin
       return 'Hello'||v_world;
end;
*//*
select HelloDemo('Worrld') from dual
*/

create or replace function get_sysdate
return date
is
   v_date date;
begin
       v_date := sysdate;
       return v_date;
end;

select get_date from  dual;

定义两个数相加的函数

create or replace function add_func(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_func(1,2) from dual;

获取指定部门的工资总和

create or replace function get_all_sal(dept_id number)
return number
is
   v_sumsal number(20,2) := 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;

PL/SQL 总结

原文：http://blog.csdn.net/wjw0130/article/details/43890207