首页 > 数据库 > Oracle > 正文

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

2024-08-29 13:58:10
字体:
来源:转载
供稿:网友
本文主要是前篇文章的续篇,主要来复习PL/SQL的基本语法、记录类型、流程控制、游标的使用、异常处理机制、存储函数/存储过程、触发器。好记性不如烂笔头,古人诚不欺我~
 
 

复习内容:

PL/SQL的基本语法、记录类型、流程控制、游标的使用、

异常处理机制、存储函数/存储过程、触发器。

为方便大家跟着我的笔记练习,为此提供数据库表文件给大家下载

为了要有输出的结果,在写PL/SQL程序前都在先运行这一句:
set serveroutput on
结构:
declare
--声明变量、类型、游标
begin
--程序的执行部分(类似于java里的main()方法)
exception
--针对begin块中出现的异常,提供处理的机制
--when...then...
--when...then...
end;
举例1:

declare  v_sal number(10); (注意每句话后面别忘记了分号,跟java中的一样)begin  select salary into v_sal from employees where employee_id = 100;  dbms_output.put_line(v_sal);end;

举例2:

declare  v_sal number(10); (注意,这里声明的空间大小不能比原表中的小)  v_email varchar2(20);  v_hire_date date;begin  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  v_sal employees.salary%type;  v_email employees.email%type;  v_hire_date employees.hire_date%type;begin  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   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 = 100;  dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','||   v_emp_record.v_hire_date);end;

1、pl/sql基本的语法格式
2、记录类型 type ... is ...record(,,,);
3、流程控制:
3.1 条件判断(两种)
方式一: if ... then elseif then ... else ... end if;
方式二: case ... when ... then ...end;
3.2 循环结构(三种)
方式一:loop ... exit when ... end loop;
方式二:while ... loop ... end loop;
方式三:for i in ... loop ... end loop;
3.3 goto、exit
4.游标的使用(类似于java中的Iterator)
5.异常的处理

6.会写一个存储函数(有返回值)、存储过程(没有返回值
7.会写一个触发器

复习记录类型:

declaretype emp_record is record(  -- v_emp_id employees.employee_id%type,  -- v_sal employees.salary%type  v_emp_id number(10) := 120,  v_sal number(10,2) :=12000);  v_emp_record emp_record;begin  -- select employee_id,salary 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);end;

也可以升级一下,要是想对表的所有列都输出,则:(须注意输出的列名要跟表中的列名要一样)

declare  v_emp_record employees%rowtype;begin  select * into v_emp_record from employees where employee_id = 123;  dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'||   v_emp_record.salary);end;使用记录来执行update操作: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;

流程控制:
查询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 v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000');  else dbms_output.put_line('salary < 5000');  end if;  dbms_output.put_line('salary:'||v_sal);end;利用case ... when ... then ... when ...then ... else ... end实现上题;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 '5000 <= salary < 10000'                  else 'salary >= 10000'                  end;  dbms_output.put_line('salary:'||v_sal||' '||v_temp);end;


查询出122号员工的job_id,若其值为 ‘IT_PROG', 则打印‘GRADE:A'
                                                ‘AC_MGT', 则打印‘GRADE:B'
                                                ‘AC_ACCOUNT', 则打印‘GRADE:B'
                                                 否则打印‘GRADE:D'

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

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

declare  v_i number(5) :=1;begin  loop  dbms_output.put_line(v_i);  exit when v_i >=100;  v_i := v_i + 1;  end loop;end;使用while实现:declare  v_i number(5) :=1;begin  while v_i <= 100 loop   dbms_output.put_line(v_i);   v_i := v_i + 1;  end loop;end;使用for...in...loop...end loop;实现:begin  for c in 1..100 loop   dbms_output.put_line(c);  end loop;end;


输出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 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;    end if;   end loop;   if v_flag=1 then dbms_output.put_line(v_i);   end if;   v_flag := 1;  end loop;end; 

可以用goto改进一下:

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;或者: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;

 

游标:
打印出80部门的所有的员工的工资:salary:XXX
declare
v_sal employees.salary%type;
--定义游标
cursor emp_sal_cursor is select salary from employees where department_id = 80;
begin 
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal;
while emp_sal_cursor%found loop
dbms_output.put_line('salary:'||v_sal);
fetch emp_sal_cursor into v_sal;
end loop;
--关闭游标
close emp_sal_cursor;
end;
可以进行优化如下:

declarev_empid employees.employee_id%type;v_lastName employees.last_name%type;v_sal employees.salary%type;cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80;begin open emp_sal_cursor;fetch emp_sal_cursor into v_empid,v_lastName,v_sal;while emp_sal_cursor%found loopdbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||', '||'salary:'||v_sal);fetch emp_sal_cursor into v_empid,v_lastName,v_sal;end loop;close emp_sal_cursor;end;

或者使用记录再优化一下:

declaretype emp_record is record(v_empid employees.employee_id%type,v_lastName employees.last_name%type,v_sal employees.salary%type);v_emp_record emp_record;cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80;begin open emp_sal_cursor;fetch emp_sal_cursor into v_emp_record;while emp_sal_cursor%found loopdbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'|| v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal);fetch emp_sal_cursor into v_emp_record;end loop;close emp_sal_cursor;end; 

可以使用for循环最优化:(注意:在for循环中它会自动的打开游标、提取游标,当提取完里面的数据后也会自动 
的关闭游标

declarecursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80;begin for c in emp_sal_cursor loopdbms_output.put_line('employee_id:'||c.employee_id||', '||'last_name:'||c.last_name||', '||'salary:'||c.salary);end loop;end;


利用游标,调整公司中员工的工资:
工资范围            调整基数
0 - 5000              5%
5000 - 10000       3%
10000 - 15000     2%
15000 -               1%
实现:

declare   cursor emp_cursor is select employee_id,salary from employees;  v_empid employees.employee_id%type;  v_sal employees.salary%type;  v_temp number(4,2);begin   open emp_cursor;  fetch emp_cursor into v_empid,v_sal;  while emp_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_cursor into v_empid,v_sal;  end loop;  close emp_cursor;end;

用for循环实现

declare   cursor emp_cursor is select employee_id,salary from employees;  v_temp number(4,2); begin   for c in emp_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;

隐式游标:更新员工salary(涨工资10),如果该员工没有找到,则打印“查无此人”信息:

begin   update employees  set salary = salary + 10  where employee_id = 1001;   if sql%notfound then dbms_output.put_line('查无此人');   end if;end;

异常:
预定义异常:(有24个预定义异常,可查表

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('违反了完整性约束,故不能删除此用户');  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;

通过select...into...查询某人的工资,若没找到则打印出“未找到此数据”:

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;

自定义异常:
更新指定员工工资,增加100;若指定员工不在,则抛出异常:NO_RESULT; 

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;


存储过程:
写个简单的hello_world存储函数

create or replace function hello_world return varchar2is (相当于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 varchar2is 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_sysdatereturn varchar2is 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 numberis  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 numberis   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;


定义一个函数:获取给定部门的工资总和 和 该部门的员工总数(定义为OUT类型的参数)。
要求:部门号定义为参数,工资总额定义为返回值。

create or replace function get_sal(dept_id number,total_count out number)return numberis  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;


定义一个存储过程:获取给定部门的工资总和(通过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 out number)is  cursor sal_cursor is select employee_id,salary,hire_date   from employees where department_id = dept_id;  v_temp number(4,2):=0;begin   temp := 0;  for c in sal_cursor loop    if to_char(c.hire_date,'yyyy') < '1995' then v_temp:=0.05;    elsif to_char(c.hire_date,'yyyy') < '1998' then v_temp:=0.03;    else v_temp:=0.01;    end if;  update employees   set salary = salary * (1+v_temp)   where employee_id = c.employee_id;  temp := temp + c.salary*v_temp;   end loop;  dbms_output.put_line(temp);end;调用:declare   v_i number(10):=0;begin  add_sal(80,v_i);end;

触发器:
触发事件:在INSERT,UPDATE,DELETE情况下会触发TRIGGER
触发时间:该TRIGGER是在触发事件发生之前(BEFORE)还是之后(AFTER)
触发器本身:该TRIGGER被触发之后的目的和意图,正是触发器本身要做的事情,如PL/SQL块
触发频率:有语句级(STATEMENT)触发器和行级(ROW)触发器
写一个简单的触发器:

create or replace trigger update_emp_triggerafter  update on employeesfor each row (行级触发器,即每更新一条记录就会输出一次'helloworld',若没有这语句则是语句级触发器)begin  dbms_output.put_line('helloworld');end;


使用:new,:old修饰符:

1、create table emp1asselect employee_id,salary,email from employees where department_id = 80;2、create or replace trigger update_emp_trigger2after  update on emp1for each rowbegin  dbms_output.put_line('old salary:'||:old.salary||'new salary:'||:new.salary);end;3、update emp1 set salary = salary + 100 ;


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

1、创建my_emp表:create table my_emp asselect employee_id,salary from employees ;2、创建my_emp_bak表:create table my_emp_bakasselect employee_id,salary from employees where 1=2;3、检查创建的表中的记录:select * from my_empselect * from my_emp_bak4、创建一个触发器:create or replace trigger delete_emp_trigger before  delete on my_emp for each rowbegin  insert into my_emp_bak   values(:old.employee_id,:old.salary);end;5、执行含有触发器时间的语句:delete from my_emp6、检查触发器执行后的结果:select * from my_empselect * from my_emp_bak

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表