Plsql编程

Plsql编程

plsql基本结构

declare
    --声明变量、类型、游标
    --声明变量
    v_sal number(8,2);
    --类型与students表中的email字段保持一致
    v_email students.email%type;

    --声明记录类型变量
    type emp_record is record(
    v_name number(8,2),
    v_email varchar(20)
    );
    v_emp_record emp_record;
begin
--不能将select语句中的列赋值给布尔变量
	select salary into v_sal from employee;
	select name,email into v_emp_record from employee;
    --程序的执行部分（类似于java中的main()方法）
    dbms_output.put_line('helloworld');
    dbms_output.put_line(salary);
	dbms_output.put_line(v_emp_record.name||','||v_emp_record.email);
exception
    --针对begin中出现的异常，提供处理机制
    --when ... then ...

end;

输出语句

dbms_output.put_line(‘hello world’);

注意：set serveroutput on

语法

:= 赋值

=> 关系号

%type 取某一列的数据类型

%rowtype 取一行的所有字段类型作为一个记录类型

v_name varchar2(20) := 'hello';
v_emp_record employees%rowtype;

条件语句

-- if表达式
IF <布尔表达式> THEN
	PL/SQL 和SQL语句;
END IF;

IF <布尔表达式> THEN
	PL/SQL 和SQL语句;
ELSE 
	其他语句;
END IF;

IF <布尔表达式> THEN
	PL/SQL 和SQL语句;
ELSIF <其他布尔表达式> THEN
	其他语句;
ELSE 
	其他语句;
END IF;
-- ELSEIF 不能写成 ELSEIF

-- case表达式
CASE selector 
	WHEN expression1 THEN result1
	WHEN expression2 THEN result2
	[ELSE resultN+1]
END;

样例代码

-- if else 表达式
declare
	v_age employees.age%type;
	v_desc varchar2(30);
begin
	select age into v_age from employees where id=100;
	
	if v_age<12 then v_desc :='儿童';
	elsif v_age <30 then v_desc :='青年';
	elsif v_age <50 then v_desc :='壮年';
	else v_desc :='老年';
	
	dbms_output.put_line(v_desc);
end;

-- case 表达式
declare
	v_age employees.age%type;
	v_desc varchar2(30);
begin
	select age into v_age from employees where id=100;
	v_desc :=
	case v_age/10 when 0 then '儿童'
			   when  1 then '青年'
			   when  2 then '壮年'
			   else '老年' end;
	
	dbms_output.put_line(v_desc);
end;

循环语句

--简单循环
LOOP
	要执行的语句;
	EXIT WHEN <布尔表达式>>;
END LOOP;

-- WHILE循环
WHILE <布尔表达式> LOOP
	要执行的语句;
END LOOP;

-- FOR 循环
-- 每循环一次循环遍历自动加1；使用关键字REVERSE，循环变量自动减1。跟在IN [REVERSE]后面的数字必须是
-- 从小到大的顺序,必须是整数。可以使用EXIT退出循环
FOR 循环计数器 IN [REVERSE] 下限 .. 上限 LOOP
	要执行的语句;
END LOOP;

样例

--简单循环
declare
	v_i number(8):=1;
begin
	loop 
	dbms_output.put_line(v_i);
	v_i:=v_i+1;
	exit when v_i>100;
	end loop;
end;

--while循环
declare
	v_i number(8):=1;
begin
	while v_i<100 loop
	dbms_output.put_line(v_i);
	v_i:=v_i+1;
	end loop;
end;

--for 循环 
begin
	for i in 1..100 loop
		dbms_output.put_line(i);
	end loop;
end;

--for 循环 exit
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;

--for 循环 label
begin
	for i in 1..100 loop
		if i=50 then 
			dbms_output.put_line('打印结束');
			goto label;
		end if;
		<<label>>
		dbms_output.put_line(i);
	end loop;
end;

游标

对于多行记录的事务经常使用游标来实现。

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

样例

declare
	v_name students.name%type;
	--定义游标
	cursor emp_name_cursor is select name from students;
begin
	-- 打开游标
	open emp_name_cursor;
	-- 提取游标
	fetch emp_name_cursor into v_name;
	
	while emp_name_cursor%found loop
		dbms_output.put_line(name);
		fetch emp_name_cursor into v_name;
	end loop;
	--关闭游标
	close emp_name_cursor;

end;

游标属性

%FOUND 布尔类型属性，当最近一次读记录时成功返回，则值为true;

%NOTFOUND 布尔类型属性，与%FOUND相反；

%ISOPEN 布尔类型属性，当游标打开时返回TRUE;

%ROWCOUNT 数字类型属性返回已从游标中读取的记录数。

游标FOR 循环

游标 FOR 循环语句自动执行游标的OPEN、FETCH、CLOSE 语句和循环语句的功能；

自动打开、关闭游标。

declare
	--定义游标
	cursor emp_name_cursor is select name from students;
begin
 	for c in emp_name_cursor loop
 		dbms_output.put_line(name);
 	end loop;

end;

异常处理

-- others必须放在最后
EXCEPTION
	WHEN exception_1 THEN <执行语句>
	WHEN exception_2 THEN <执行语句>
	WHEN OTHERS THEN <执行语句>
END;

样例

declare
	v_name students.name%type;
begin
	select name into v_name from students;
	
	dbms_output.put_line(v_name);
exception
	when too_many_rows then dbms_output.put_line('输出行数太多！');
	when others then dbms_output.put_line('出现未知错误！')
end;

非预定义错误

即其他标准的ORACLE错误。需用户在程序中定义，有oracle自动引发

处理步骤

1. 在plsql中的定义部分定义异常

   <异常> EXCEPTION;
   

2. 将定义好的异常情况，与标准的ORACLE错误联系起来

   PRAGMA EXCEPTION_INIT(<异常>,<错误代码>);
   

3. 在plsql中的异常处理部分进行处理

declare
	e_deleteid_exception exception;
	-- ORA-02292: 违反完整约束条件
	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('违反完整性约束条件');

end;

用户定义错误

程序执行过程中，出现开发人员认为的非正常情况，需用户在程序中定义，并显示地在程序中引发。

处理步骤

1. 在plsql中的定义部分定义异常

   <异常> EXCEPTION;
   

2. 抛出异常

   RAISE <异常>;
   

3. 在plsql中的异常处理部分进行处理

   declare
   	v_age students.age%type;
   	e_too_old exception;
   	
   begin
   	select age info v_age from from students where id=100;
   	if v_age>100 then
   		raise e_too_old;
   	end if;
   exception 
   	when e_too_old then dbms_output.put_line('太老了');
   
   end;
   

存储过程和函数

ORACLE 提供可以把plsql 程序存储在数据库中，并可以在任何地方来运行，这些存储起来的plsql块就叫存储过程或函数。

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

函数

语法

--arg_type、return_type只需写类型即可，无需填写大小
CREATE [OR REPLACE] FUNCTION function_name
	[(argment[{IN | IN OUT}] arg_type,
      argment[{IN |OUT| IN OUT}] arg_type)]
     [AUTHID DEFINER | CURRENT_USER]
     RETURN return_type
{IS|AS}
--定义变量，相当于declare部分
     <类型.变量的说明>
BEGIN
	<执行语句>
EXCEPTION     
	<执行语句>
END;
/**
	说明：
	1） OR REPLACE 可选。可以用于创建或替换相同名字的函数
	2） IN 参数标记表示传递给函数的值在该函数执行中不改变；
		OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句；
		IN OUT 标记表示传递给函数的值可以变化并传递给调用语句;
		若省略，默认为IN 。
*/

样例

--创建函数
create or replace function hello_world(v_name varchar2)
return varchar2
is
/**
	v_name	名字
*/
begin
	return 'hello world ,'||v_name||'!';
end;

-- 使用方式1
select hello_world('long') from dual;

-- 使用方式2
begin
	dbms_output.put_line(hello_woeld('long'));
end;

out参数

--创建函数
create or replace function hello_world(v_name varchar2,v_age out number)
return varchar2
is
/**
	v_name	名字
*/
begin
	v_age:=10;
	return 'hello world ,'||v_name||'!';
end;


-- 使用方式
delcare
	v_age number(8) :=30;
begin
	--out参数不可省略且必须为变量，不可接受外部传入的值，强行打印为‘’
	--in out 参数可接收外部值
	dbms_output.put_line(hello_woeld('long',v_age));
	dbms_output.put_line(v_age);
end;

触发器

触发器类似过程和函数，都有声明，执行和异常处理过程的plsql块。

触发器在数据库里以独立的对象存储，由一个事件来启动运行。触发器不能接收参数。

语法

CREATE [OR REPLACE] TRIGGER trigger_name
	{BEFORE|AFTER}
	{INSERT|DELETE|UPDATE[OF column[,column ...]]}
	ON [schema.]table_name
	[FOR EACH ROW]
	[WHEN condition]
	trigger_body;
/**
	1. BEFORE 和AFTER 指出触发器的触发时序分别为前触发和后触发方式。
	2. FOR EACH ROW 选项说明触发器为行触发器，每变更一行触发一次。
*/

样例

-- 触发器1
create or replace trigger update_students_trigger
	after
	update on students
	for each row
begin
	-- :old :new 分别用于取变更前的值和变更后的值
	dbms_output.put_line('students 表已更新'||:old.name||'->'||:new.name);
end;

-- 触发器 备份表
create or replace trigger delete_students_trigger
before
delete on students
for each row
begin
	insert into students_bak
	values(:old.id,:old.name);
end;