oracle 学习之 PL/SQL
/*
PL/SQL : Oracle内部语言
SQLServer的是:TSQL
Procedural Language/SQL 是oracle在标准的SQL语言上的扩展,PL/SQL不仅允许嵌入SQL语言,还可以
定义变量和常量,允许使用条件语句和循环语句,允许使用例外处理各种错误,这样使得它的功能变得
更加的强大.
优点:提高应用程序的运行性能,模块化的设计思想,减少网络传输量,提高安全性
SQL语言:有两种SQL1992/SQL1996 没有循环控制结构
PL/SQL块由三个部分组成:定义部分,执行部分,例外处理部分
declare
定义部分 -- 定义常量,变量,游标,例外,复杂数据类型
begin
执行部分 -- 要执行的PL/SQL语句和SQL语句
exception
例外部分 -- 处理运行的各种错误
end;
调用过程
exec 过程名
call 过程名
*/
--匿名块
--简单打印一句话
--打开显示
set serveroutput on ;
begin
dbms_output.put_line('Hello PL/SQL!');
end ;
/
/*
变量声明的规则
1 变量名不能够使用保留字
2 第一个字符必须是字母
3 变量名最多包含30个字符
4 不要与数据库的表或者列同名
5 每一行只能声明一个变量
常用的变量类型
1 binary_integer : 整数,主要用来计数而不是用来表示字段类型
2 number: 数字字符
3 char : 定长字符串
4 varchar2 : 变长字符串
5 data : 日期
6 long : 长字符串最长2GB
7 boolean : 布尔类型,可以取值true|false和null值
*/
--声明变量
declare v_name varchar2(20) ;
declare
v_temp number(1);
v_count binary_integer :=0;
v_sal number(7,2) :=4000.00;
v_date date :=sysdate ;
v_pi constant number(3,2) :=3.14;
v_valid boolean :=false;
v_name varchar2(20) not null :='hello plsql' ;
begin
dbms_output.put_line('v_temp value :'||v_temp);
end;
--根据表中字段类型声明变量类型的
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('test');
end;
/*
复杂变量
Table变量类型 : 集合 (表示集合时要自己定义的一个新的数据类型)
Record变量类型 : 类 (代表实体,一条记录)
*/
--定义Table变量类型
--Table 变量类型(数组)
declare
type type_newname is table of emp.empno%type index by binary_integer; --声明一个数组类型 index by binary_integer 指系统创建一个主键索引，以引用记录表变量中的特定的行。
v_empnos type_newname ; --用数组类型定义变量
begin
v_empnos(0) :=7369;
v_empnos(2) :=7839;
v_empnos(-1):=9999;
dbms_output.put_line(v_empnos(-1));
end;
--Record变量类型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept ;
begin
v_temp.deptno :=50 ;
v_temp.dname :='aaa';
v_temp.loc :='bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
--使用%rowtype 声明record变量
declare
v_temp dept%rowtype; --直接用一张表的行来定义record,表结构变化时record也在变
begin
v_temp.deptno :=50;
v_temp.dname :='aaa';
v_temp.loc :='bj';
dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end ;
/*
PL/SQL中的SQL语句
select 语句返回有且只有一条记录
*/
--select语句
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno =7369;
dbms_output.put_line(v_ename || ' '||v_sal);
end;
declare
v_temp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_temp.ename);
end;
--insert 语句
declare
v_deptno dept.deptno%type :=10;
v_dname dept.dname%type :='aaa';
v_loc dept.loc%type :='beijing' ;
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
commit;
end;
--update语句
declare
v_deptno emp2.deptno%type :=10 ;
v_count number;
begin
update emp2 set sal = sal/2 where deptno = v_deptno ;
--select deptno into v_deptno from emp2 where empno = 7369;
--select count(*) into v_count from emp2 ;
dbms_output.put_line(sql%rowcount||'条记被影响');
commit ;
end;
--DDL语句
begin
execute immediate 'create table t_temp(nnn varchar2(20) default "aaa")' ;
end ;
--语句块
declare
v_name varchar2(20);
begin
v_name := 'jack' ;
dbms_output.put_line(v_name);
end;
--带异常捕获的语句块
declare
v_num number :=0 ;
begin
v_num :=2/v_num ;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end;
--分支语句
--if 取出7369的薪水，如果<1200，则输出'low'，如果<2000则输出'middle'，否则'high'
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
if(v_sal<1200) then
dbms_output.put_line('low');
elsif(v_sal<2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
--循环 (相当于do...while)
declare
i binary_integer :=1;
begin
loop
dbms_output.put_line(i);
i := i+1 ;
exit when(i>=11);
end loop;
end;
declare
j binary_integer :=1;
begin
while j<11 loop
dbms_output.put_line(j);
j := j+1 ;
end loop ;
end;
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
--错误处理
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno =10 ;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno=2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
--错误记录
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
declare
v_deptno dept.deptno%type :=10 ;
v_errcode number ;
v_errmsg varchar2(1024) ;
begin
delete from dept where deptno=v_deptno;
commit;
exception
when others then
rollback;
v_errcode :=SQLCODE;
v_errmsg :=SQLERRM;
insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
select * from errorlog;
触发器
/* 触发器 trigger 触发器必须依附于表才能起作用 */ --创建日志表 emp_log,用于记录对表emp的所有操作日志 create table emp_log ( uname varchar2(20), action varchar2(10), atime date ); --创建触发器 /* 触发条件:insert|delete|update 对某张表做插入|删除|更新的操作 触发时间:after|before 操作之后|操作之前 触发行:each row 操作影响一行触发一次，不写表求一次操作触发一次 */ create or replace trigger trig after insert or delete or update on emp for each row begin if inserting then insert into emp_log(uname,action,atime) values(USER,'insert',sysdate); elsif updating then insert into emp_log(uname,action,atime) values(USER,'update',sysdate); elsif deleting then insert into emp_log(uname,action,atime) values(USER,'delete',sysdate); end if ; end; / --触发 update emp set sal=sal*2 where deptno=30; delete from emp where empno = 7369 ; --查看日志表 select * from emp_log; --? : 有外键关系时,主表中的主键存在外表的引用关系,因此不能随意更新,但可以用触发器解决这个问题 --如: update dept set deptno=99 where deptno=10 --删除触发器 drop trigger trig; create or replace trigger trig after update on dept for each row begin update emp set deptno = :NEW.deptno where deptno = :OLD.deptno ; end; / /* 通常一条update语句会产生新旧两个状态 :NEW代表新状态 :OLD代表旧状态 */ update dept set deptno=99 where deptno=10;
