打印输出： dbms_output.put_line('AA');
显示服务器输出信息 set serveroutput on;
打印出eid=1的员工姓名：
declare
v_name varchar2(20);
begin
select ename into v_name
from emp
where eid = 1;
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;
/
使用%type进行目标类型赋值（若表字段类型改变时变量的类型也会实时更改）
declare
v_eid emp.eid%type;
v_ename emp.ename%type;
v_ename2 v_ename%type;
常用变量类型
1. binary_integer:：整数，主要用来计数而不是用来表示字段类型。
2. number：数字类型，可以表示整数和小数。
3. char：定长字符串。
4. varchar2：变长字符串
5. date：日期
6. long：长字符串，最长2GB
7. boolean：布尔类型（true，false，null值）
复合数据类型：
Table变量类型（类比java中的数组）
declare
-- 首先声明名字为type_table_emp_eid类型的数组
type type_table_emp_eid is table of EMP.EID%type index by binary_integer;
v_eids type_table_emp_eid;
begin
v_eids(0) := 12;
v_eids(1) := 11;
v_eids(2) := 2;
v_eids(-1) := 6;
dbms_output.put_line(v_eids(-1));
end;
/
Record变量类型（类比java中的类）
declare
type type_record_emp is record
(
eid emp.eid%type,
ename emp.ename%type,
cid emp.cid%type
);
v_temp type_record_emp;
begin
v_temp.eid := 4;
v_temp.ename := 'rose';
v_temp.cid := 4;
dbms_output.put_line(v_temp.eid || '-' || v_temp.ename);
end;
/
使用%rowtype声明record变量（表结构发生变化时，rocord变量也会实时变化）
接下来是上面代码的优化版：
declare
v_temp emp%rowtype;
begin
v_temp.eid := 4;
v_temp.ename := 'rose';
v_temp.cid := 4;
dbms_output.put_line(v_temp.eid || '-' || v_temp.ename);
end;
/
PL_SQL里的select查询必须和into一块用，而且保证有且只有一条记录
declare
v_ename emp.ename%type;
v_cid emp.cid%type;
begin
select ename,cid into v_ename,v_cid from emp where eid = 1;
dbms_output.put_line(v_ename || '-' ||v_cid);
end;
/
select 与 %rowtype的联合运用
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where eid = 1;
dbms_output.put_line(v_emp.eid || '-'|| v_emp.ename || '-' || v_emp.cid);
end;
/
insert 的使用，别忘了commit
declare
v_eid emp.eid%type := 4;
v_ename emp.ename%type := 'rose';
v_cid emp.cid%type := 5;
begin
insert into emp values(v_eid,v_ename,v_cid);
commit;
end;
/
update的使用与sql%rowcount（影响记录条数统计）
declare
v_eid emp.eid%type := 2;
v_ename emp.ename%type := 'jeck';
begin
update emp set ename = v_ename where eid = v_eid;
dbms_output.put_line(sql%rowcount || '条记录被影响');
commit;
end;
/
select语句into一条数据给变量，所以影响一行。
declare
v_count number;
begin
select count(0) into v_count
from company;
dbms_output.put_line(sql%rowcount || '条记录被影响');
end;
/
PL_SQL中执行DDL语句时需要添加execute immediate(' xxx ')
begin
execute immediate
'create table test2(
c1 varchar(20)
)';
end;
/
PL_SQL中的条件判断 if .. then .. elsif .. then .. else .. end if
declare
v_cname company.cname%type;
begin
select c.cname into v_cname
from company c
where cid = 3;
if v_cname = '腾讯' then
dbms_output.put_line('qq微信');
elsif v_cname = '百度' then
dbms_output.put_line('搜索引擎');
else
dbms_output.put_line('其他公司');
end if;
end;
/
PL_SQL中的循环
类似java中do...while循环
declare
i binary_integer :=1;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when(i > 10);
end loop;
end;
/
类似java中while循环
declare
i binary_integer := 1;
begin
while i <= 10 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
/
类似java中for循环
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/
异常处理（实际返回行数大于请求行数）
declare
v_temp number(11);
begin
select cid into v_temp
from company
where cname in ('百度','腾讯');
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 cname into v_temp
from company
where cid = 6;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
/
记录错误信息表以及主键自增序列创建
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar(1024),
errdate date
);
create sequence seq_errorlog_id start with 1 increment by 1;
将删除错误回滚并将出错误信息记录在错误信息表中
declare
v_cid company.cid%type := 1;
v_errcode errorlog.errcode%type;
v_errmsg errorlog.errmsg%type;
begin
delete from company where cid = v_cid;
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;
/
游标 CURSOR
CURSOR与“do...while”遍历表中数据
declare
cursor c is
select * from company;
v_company c%rowtype;
begin
open c;
loop
fetch c into v_company;
exit when (c%notfound);
dbms_output.put_line(v_company.cname);
end loop;
close c;
end;
/
CURSOR与“while”遍历表中数据
declare
cursor c is
select * from company;
v_company company%rowtype;
begin
open c;
fetch c into v_company;
while (c%found) loop
dbms_output.put_line(v_company.cname);
fetch c into v_company;
end loop;
close c;
end;
/
CURSOR与“for”遍历表中数据（for循环开始时cursor自动打开，循环结束时cursor自动关闭）
declare
cursor c is
select * from company;
begin
for v_company in c loop
dbms_output.put_line(v_company.cname);
end loop;
end;
/
带参数的游标
declare
cursor c(v_cid company.cid%type, v_cname company.cname%type)
is
select * from company where cid = v_cid and cname = v_cname;
begin
for v_company in c(1,'百度') loop
dbms_output.put_line(v_company.cname);
end loop;
end;
/
可更新游标（一般游标用处是只读，而可更新游标可以更改）
declare
cursor c
is
select * from company for update;
begin
for v_company in c loop
if(v_company.cname = '字节跳动') then
update company set cname = 'gg' where current of c;
end if;
end loop;
commit;
end;
/
存储过程PROCEDURE
简单存储过程创建
create or replace procedure p1
is
cursor c
is
select * from emp;
begin
for v_emp in c loop
if(v_emp.cid = 1) then
dbms_output.put_line('百度1');
elsif(v_emp.cid = 2) then
dbms_output.put_line('阿里巴巴1');
elsif(v_emp.cid = 3) then
dbms_output.put_line('腾讯1');
elsif(v_emp.cid = 4) then
dbms_output.put_line('字节跳动1');
else
dbms_output.put_line('google1');
end if;
end loop;
end;
/
-- 执行存储过程p1
execute p1;
带参数的存储过程创建
create or replace procedure p2(
v_a in number,
v_b in number,
v_max out number,
v_temp in out number
)
is
begin
if(v_a > v_b) then
v_max := v_a;
else
v_max := v_b;
end if;
v_temp := v_temp + 1;
end;
/ -- 执行p2
declare
v_a number := 3;
v_b number := 4;
v_max number;
v_temp number := 5;
begin
p2(v_a, v_b, v_max, v_temp);
dbms_output.put_line(v_max);
dbms_output.put_line(v_temp);
end;
/
函数FUNCTION
create or replace function sal_tax(
v_sal number
)
return number
is
v_rate number;
begin
if(v_sal < 2000) then
v_rate := 0.1;
elsif (v_sal < 3000) then
v_rate := 0.2;
else
v_rate := 0.3;
end if;
return v_sal*v_rate;
end;
/
-- 执行函数sal_tax
select sal_tax(3000) from company;
触发器TRIGGER
-- 用户对员工表的操作日志表
create table emp_log
(
ename varchar2(20),
eaction varchar2(20),
etime date
); -- 创建触发器，将用户对emp表的增删改操作记录插入emp_log表中
create or replace trigger trig
after insert or delete or update on emp （for each row）
begin
if inserting then
insert into emp_log values(user, 'insert', sysdate);
elsif updating then
insert into emp_log values(user, 'update', sysdate);
elsif deleting then
insert into emp_log values(user, 'delete', sysdate);
end if;
end;
/ update emp set cid = 4 where ename = 'rose';