1. 什么是pl/sql?
pl/sql是oracle 公司在标准sql的基础之上,增加了一些过程化控制的语言的的一种语句
2. 在pl/sql中只能进行DML语句和事务控制语句.
3. pl/sql 的构成:
declare
声明部分
begin
执行部分
Exception
异常处理部分
end;
/
4. 标示符和命名规则: 注:大小不敏感
1.标示符:
字母: a-z/A-Z
数字:0-9
空白:space,回车,tab
常用符号
注:大小不敏感
2. 开头: 字母,数字,非空格字符,货币符号,下划线,#
最 大长度 30
3. 注释:
单行: --
多行 /* 注释内容 */
4. 数据类型:
a. 标量类型: 数字型 number binary_integer(只能用于存放整形)
字符型 varchar2 ,varchar ,string.....
char
布尔 boolean
日期 date
b. 组合型:
record table varray
c 参考型:
ref cursor , ref object_type
d. LOB 大容量:
BLOB 等等
5. 变量的声明:
语法: var_name [constant] type [not null][:=value]
变量名 标记为常量 类型 是否为空 初始化值
例:
var number not null :=3;
------------------------------------------------
6. 其他类型:
1. %type 表示所声明的变量与数据库中的某张表的某个数据列有相同的类型
例:
declare
var_name t_student.name%type := 'sw';
begin
dbms_output.put_line(var_name);
end;
/
(定义一个与t_student表中name的列相同类型的一个变量)
--------------------------------------------------------------------
2. %rowtype 表示所声明的变量与数据库中的某张表有相同的类型
例:
declare
var_stu t_student%rowtype;
begin
var_stu.id :=1234;
var_stu.name :='sw';
dbms_output.put_line(var_stu.id||','||var_stu.name);
end;
/
------------- ------------------------------------------------
3. record类型(类似于数据结构)与%rowtype 不同时记录必须自己定义内容,而%rowtype 依据已存在的表格
declare
type record_name is record (
id number(10) ,
name varchar2(30)
);
t_rec record_name ;
begin
t_rec.id :=1234;
t_rec.name := 'sw';
dbms_output.put_line(t_rec.id||' , '||t_rec.name);
end;
/
-------------------------------------------------------------------
4. table (类似于数组) 一般用于保存从某个表格查询出的数据
declare
type stu_tab is table of t_student%rowtype
index by binary_integer ;
t_tab stu_tab;
begin
select * into t_tab(10)
from t_student
where id=1;
dbms_output.put_line(t_tab(10).name);
end;
/
----------------------------------------------------------------------
5. if 条件语句:
if ... then
elsif ... then
else...
end if;
6.loop 循环语句
loop
....
if ... then exit;
end loop;
7. for 循环:
for var in [reverse]1..100 loop
end loop;
例:
declare
v_num number:=1;
v_sum number:=0;
begin
for v_num in 1..100
loop
v_sum:=v_sum+v_num;
end loop;
dbms_output.put_line('sum='||v_sum);
end;
/
8 while 循环
declare
v_num number:=1;
begin
while v_num < 5
loop
dbms_output.put_line(var);
v_num:=v_num+1;
end loop;
end;
/
------------------------------------------------------
9 . cursor 游标(类似于指针,迭代器)
declare
--声明游标
cursor v_cur is select id ,name from test;
v_id number;
v_name varchar(10);
begin
--打开游标
open v_cur;
--提取数据
fetch v_cur into v_id,v_name;
--处理数据
while v_cur%found loop
dbms_output.put_line('id:'||v_id);
dbms_output.put_line('name:'||v_name);
fetch v_cur into v_id,v_name;
end loop;
--关闭游标
close v_cur;
end;
有参:(给定子查询的条件)
declare
cursor v_cur(var number) is select id ,name
from test
where id<number;
v_id number;
v_name varchar(10);
begin
open v_cur(20);
--提取数据
fetch v_cur into v_id,v_name;
--处理数据
loop
fetch v_cur into v_id,v_name;
if v_cur%found then
dbms_output.put_line('id:'||v_id||' , '||'name:'||v_name);
else
exit;
end if;
end loop;
close v_cur;
end;
/
游标的其他参数:
%found 若能够提取到数据则返回true,否则,ora-1001
%nofound 与%found 相反
%isopen 判断游标是否打开
%rowcount 返回游标当前指向的个数,若游标没有打开就是用则返回ora-1001
------------------------------------------------------
10 异常处理:
declare
cursor v_cur(var number) is select id ,name
from test
where id<var;
v_id number;
v_name varchar(10);
e Exception; --异常声明
begin
open v_cur(20);
--提取数据
fetch v_cur into v_id,v_name;
--处理数据
loop
fetch v_cur into v_id,v_name;
if v_cur%found then
dbms_output.put_line('id:'||v_id||' , '||'name:'||v_name);
else
exit;
end if;
raise e; --异常抛出
end loop;
close v_cur;
Exception
when e then --异常处理
--when others then
dbms_output.put_line('cursor is not open!');
end;
/
declare
e1 exception;
e2 exception;
v_errcode varchar2(100);
v_errinf varchar2(100);
begin
raise e1;
exception
when e1 then
dbms_output.put_line('e1');
when e2 then
dbms_output.put_line('e2');
when others then
v_errcode:=SQLCODE;
v_errinf:=SUBSTR(SQLERRM,1,200);
INSERT INTO log_file values(v_errcode,v_errinf);
dbms_output.put_line('others');
end;
/
---------------------------------------------------------
11. procedure (过程)
方法创建:
create or replace procedure sw_proc
is
var varchar2(30):='helloworld!';
begin
dbms_output.put_line(var);
end;
/
begin
sw_proc;
end;
/
------------------------------------------
create or replace procedure stu_pro(
id out number,
name out varchar2,
var in number
)
is
v_id number(3);
stu_t t_t%rowtype;
cursor v_cur is
select id ,name
from t_student;
begin
open v_cur;
fetch v_cur into stu_t;
while v_cur%found loop
dbms_output.put_line('id:'||stu_t.id||' , '||'name:'||stu_t.name);
fetch v_cur into stu_t;
end loop;
end;
/
------------------------------------------------
create or replace procedure proc_sw(
v1 in number,
v2 out date,
v3 in out varchar2
)
is
v11 number;
v22 date:= sysdate;
v33 varchar(30):='hello';
begin
v11 := v1;
v2 := v22;
v3 :=v33;
dbms_output.put_line('resulte: '||v11||' '||v3||' '||v3);
end;
/
declare
v1 number :=888;
v2 date;
v3 varchar2(30) :='helloworld';
begin
proc_sw(v1,v2,v3);
end;
/
-------------------------------------------------
create or replace procedure stu_pro(
id out number,
name out varchar2,
var in number
)
is
v_id number(3);
stu_t t_t%rowtype;
cursor v_cur(var number) is
select id ,name
from t_student
where id < var;
begin
open v_cur(var);
fetch v_cur into stu_t;
while v_cur%found loop
id :=stu_t.id;
name :=stu_t.name;
dbms_output.put_line('id:'||stu_t.id||' , '||'name:'||stu_t.name);
fetch v_cur into stu_t;
end loop;
end;
/
---------------------------------------------------------------
调用方法:
declare
var_id number ;
var_name varchar2(10);
var number;
begin
var :=10;
stu_pro(var_id,var_name,var);
--传入参数有两种: 1. 按顺序直接传入
--2. 按指向( stu_pro(id=>var_id,name=>var_name, var=>var); )
dbms_output.put_line(var_id||var_name);
end;
/
---------------------------------------------------------------------
12 .function
create or replace function stu_func(
name out varchar,
var in number
)
return varchar2;
is
v_id number(3);
cursor v_cur(var number) is
select id ,name
from t_student
where id < var;
begin
open v_cur(var);
fetch v_cur into stu_t;
while v_cur%found loop
id :=stu_t.id;
name :=stu_t.name;
dbms_output.put_line('id:'||stu_t.id||' , '||'name:'||stu_t.name);
fetch v_cur into stu_t;
end loop;
return stu_t.name;
end ;
/
------------------------------------------------
create or replace function func_sw
return varchar2;
is
result varchar2(30);
v_sum number;
begin
select count(*) into v_sum from test;
if v_sum <= 0 then
result :='no record';
elsif v_sum > 0 and v_sum< 100 then
result := v_sum;
else
result := 'more than 100';
end if;
return result;
end;
/
declare
v_result varchar2(30);
begin
v_result :=func_sw;
dbms_output.put_line('result: '||v_result);
end;
/
-------------------------------------------------------
13 ..package
create or replace package pac_sw
is
var varchar2(30);
procedure pro_sw;
function fun_sw return varchar2;
cursor cur is select id,name from test;
end pac_sw;
/
create or replace package body pac_sw
is
procedure pro_sw
is
var1 varchar2(30) :='hello';
begin
dbms_output.put_line(var);
end;
function fun_sw return varchar2
is
var2 varchar2(30) := 'nihao';
begin
var :=var2;
return var;
end;
end pac_sw;
/
---------------------------------------------------------
15 .trigger 触发器(给其他操作提供信号)
触发器类型:
1. 语句:
insert , update , delete
2. 定时:
before , after
3. 级别:
行级别 for ench row
表级别 (默认)
create or replace trigger trig_sw
after insert on test
declare
v varchar2(100);
begin
v :='insert into date!';
dbms_output.put_line(v);
end;
/
create or replace trigger trig_sw
after update on test
declare
v varchar2(100);
begin
v :='insert into date!';
dbms_output.put_line(v);
end;
/
create or replace trigger trig_sw
after update on test
for each row
declare
v varchar2(100);
begin
v :='insert into date!';
dbms_output.put_line(v);
end;
/
---------------------------------
:old (保存update或删除以前的那条数据) (只能用在行级别触发中)
:new (保存更新后的数据) (只能用在行级别触发中)
使用:
create or replace trigger del_trig
after delete on test
for each row
declare
var_id number;
var_name varchar2(10);
begin
var_id := :old.id;
var_name := :old.name;
dbms_output.put_line('delete data is: '||var_id||' ,'||var_name);
end;
/
触发器的限制:
a. 不应该使用事务控制语句
b. 由触发器调用的任何过程与方法都不能使用事务控制语句
c. 不能声明任何 long 或 龙raw 变量
d 可以访问的表有限
触发器不可以访问的表:
a. 变化表或变异表(被DML正在修改的表)
b. 限制表,约束表(对于引用完整性约束(主键外键,)需要从中读取数据的表)
c. 触发器主体中的限制
不可读取任何变化表
不可读取或修改限制表的主键,唯一值,外键列
pl/sql基础
发表回复