oracle复习笔记之PL/SQL程序所要了解的知识点(4)

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('违反了完整性约束，故不能删除此用户');
  when others then dbms_output.put_line('出现其它的异常了');
end;

declare
  e_sal_hight 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_sal_hight;
  end if;
exception
  when e_sal_hight then dbms_output.put_line('工资太高了');
  when others then dbms_output.put_line('出现其它的异常了');
end;

declare
  v_sal employees.salary%type;
begin 
  select salary into v_sal from employees where employee_id = 1001;
exception
  when no_data_found then dbms_output.put_line('未找到此数据');
  when others then dbms_output.put_line('出现其它的异常了');
end;
更新指定员工工资，如工资小于300，则加100，对NO_DATA_FOUND异常，TOO_MANY_ROWS进行处理。
declare
  v_sal employees.salary%type;
begin 
  select salary into v_sal from employees where employee_id = 1001;
  if v_sal < 300 then update employees set salary = salary + 100 where employee_id =101;
  end if;
exception
  when no_data_found then dbms_output.put_line('未找到此数据');
  when too_many_rows then dbms_output.put_line('输出的行数太多了');
  when others then dbms_output.put_line('出现其它的异常了');
end;

declare 
  no_result exception;
begin 
  update employees set salary = salary + 100 where employee_id = 1001;
  if sql%notfound then raise no_result;
  end if;
exception 
  when no_result then dbms_output.put_line('查无此数据,更新失败'); 
  when others then dbms_output.put_line('出现其它异常'); 
end;

create or replace function hello_world 
return varchar2
is （相当于declare，可以在其后面定义变量、记录、游标）
begin 
  return 'helloworld';
end;
存储函数的调用：
begin 
  dbms_output.put_line(hello_world);
end;
或者：
select hello_world from dual;

create or replace function hello_world1(v_logo varchar2)
return varchar2
is 
begin 
  return 'helloworld'||v_logo;
end;
调用：
select hello_world1('shellway') from dual
或者：
begin 
  dbms_output.put_line(hello_world1('shellway'));
end;

create or replace function get_sysdate
return varchar2
is 
begin 
  return to_char(sysdate,'yyyy-MM-dd HH24:mi:ss');
end;

create or replace function add_param(v_num1 number,v_num2 number)
return number
is
  v_num3 number(10);
begin
  v_num3 := v_num1 + v_num2;
  return v_num3;
end;
调用：
select add_param(2,5) from dual;
或者：
begin 
  dbms_output.put_line(add_param(5,4));
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;
调用：
select get_sal(80) from dual;

create or replace function get_sal(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_count number(4);
begin 
  dbms_output.put_line(get_sal(80,v_count));
  dbms_output.put_line(v_count);
end;