PLSQL学习笔记总结：
目录
2.1.1 双表同步数据的oracle package使用触发器实现
2.1.2 双表同步数据的oracle package使用存储过程
一 plsql 基本语法
1.1 块结构
PL/SQL 程序由三个块组成， 即声明部分DECLARE、 BEGIN 执行部分、 异常处理部分 EXCEPTION
PL/SQL 块的结构：
DECLARE
BEGIN
EXCEPTION
END;1.2 标识符
标识符的要求
- 第一个字符必须为字母；
- 不分大小写；
- 不能用’-‘(减号);
- 不能是 SQL 保留字。
1.3 变量类型
char (定长字符串)
varchar2 (可变字符串)
binary _integer （带符号整数）
number(p,s) （小数）
long
date
boolean
rowid（存放数据库行号）
urowid（通用行标识符）
1.3.1 记录类型
记录类型语法
TYPE record_type I S RECORD(
Field1 type1 [ NOT NULL] [: = exp1 ],
Field2 type2 [ NOT NULL] [: = exp2 ],
. . . . . .
Fieldn typen [ NOT NULL] [ : = expn ]
) ;记录类型示例
-- Created on 2022/8/2 by YINGYING01.YU
--打印出部门为80的所有员工的薪水
declare
--声明一个记录型
type emp_record is record(
v_sal employees.salary%type,
v_empid employees.employee_id%type
);
--声明一个记录行型的变量
v_emp_record emp_record;
cursor emp_sal_cursor is select salary,employee_id from employees where department_id=80;
begin
open emp_sal_cursor;
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
dbms_output.put_line('empid:'|| v_emp_record.v_empid||'salary：'|| v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
close emp_sal_cursor;
end;1.3.2 使用%type
使用 %type 定义变量，动态的获取数据的声明类型
declare
--定义一个记录类型
type emp_record is record(
v_name employees.last_name%type,
v_email employees.email%type,
v_salary employees.salary%type,
v_job_id employees.job_id%type);
--声明自定义记录类型的变量
v_emp_record emp_record;
begin
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;
dbms_output.put_line(v_emp_record.v_name || ', ' ||
v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' ||
v_emp_record.v_job_id);
end;1.3.2 使用 %rowtype
declare
--声明一个记录类型的变量
v_emp_record employees%rowtype;
begin
--通过 select ... into ... 语句为变量赋值
select * into v_emp_record
from employees
where employee_id = 186;
dbms_output.put_line(v_emp_record.last_name || ', ' ||
v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;1.3.3 变量赋值
通过变量实现查询语句
declare
v_emp_record employees%rowtype;
v_employee_id employees.employee_id%type;
begin
--使用赋值符号位变量进行赋值
v_employee_id := 186;
select * into v_emp_record
from employees
where employee_id = v_employee_id;
-- 打印变量的值
dbms_output.put_line(v_emp_record.last_name || ', ' ||
v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;通过变量实现DELETE、INSERT、UPDATE等操作
declare
v_emp_id employees.employee_id%type;
begin
v_emp_id := 109;
delete from employees
where employee_id = v_emp_id;
--commit;
end;类型转换
可转换的类型赋值
CHAR 转换为 NUMBER ：
使用 TO_NUMBER 函数来完成字符到数字的转换，
如： v_total : = TO_NUMBER(‘100.0’) + sal;
NUMBER 转换为 CHAR ：
使用 TO_CHAR 函数可以实现数字到字符的转换，
如： v_comm := TO_CHAR(‘123.45’) | | ’ 元 ’ ;
字符转换为日期：
使用 TO_DATE 函数可以实现 字符到日期的转换，
如： v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
日期转换为字符
使用 TO_CHAR 函数可以实现日期到字符的转换，
如： v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;
1.3.4 注释
- 使用双 ‘-‘ ( 减号) 加注释
- 使用 /* */ 来加一行或多行注释
1.4 流程控制
一共有三类：
控制语句 : I F 语句
循环语句 : LOOP 语句 , EXIT 语句
顺序语句 : GOTO 语句 , NULL 语句
1.4.1 条件判断
使用 IF ... THEN ... ELSIF ... THEN ...ELSE ... END IF;
declare
v_salary employees.salary%type;
begin
select salary into v_salary
from employees
where employee_id = 150;
dbms_output.put_line('salary: ' || v_salary);
if v_salary >= 10000 then
dbms_output.put_line('salary >= 10000');
elsif v_salary >= 5000 then
dbms_output.put_line('5000 <= salary < 10000');
else
dbms_output.put_line('salary < 5000');
end if;使用 CASE ... WHEN ... THEN ...ELSE ... END
declare
v_sal employees.salary%type;
v_msg varchar2(50);
begin
select salary into v_sal
from employees
where employee_id = 150;
v_msg :=
case trunc(v_sal / 5000)
when 0 then 'salary < 5000'
when 1 then '5000<= salary < 10000'
else 'salary >= 10000'
end;
dbms_output.put_line(v_sal ||','||v_msg);
end;使用CASE ... WHEN ... THEN ... ELSE ... END
declare
v_grade char(1);
v_job_id employees.job_id%type;
begin
select job_id into v_job_id
from employees
where employee_id = 122;
dbms_output.put_line('job_id: ' || v_job_id);
--根据 v_job_id 的取值, 利用 case 字句为 v_grade 赋值
v_grade :=
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('GRADE: ' || v_grade);
end; 1.4.2 循环语句
使用 LOOP ... EXIT WHEN ... END LOOP
declare
v_i number(3) := 1;
begin
loop
dbms_output.put_line(v_i);
exit when v_i = 100;
v_i := v_i + 1;
end loop;
end;使用 WHILE ... LOOP ... END LOOP
declare
v_i number(3) := 1;
begin
while v_i <= 100 loop
dbms_output.put_line(v_i);
v_i := v_i + 1;
end loop;
end;
直接循环
begin
for i in 1 .. 100 loop
dbms_output.put_line(i);
end loop;
end;1.5 游标
游标是一个指向上下文的句柄( handle)或指针 注意:定义的游标不能用 INTO 。
1.5.1游标语法和属性哦
CURSOR cursor_name[(parameter[, parameter]…)]
IS select_statement
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
FETCH cursor_name INTO {variable_list | record_variable };
FETCH cursor_name INTO {variable_list | record_variable };%FOUND 布尔型属性，当最近一次读记录时成功返回,则值为 TRUE；
%NOTFOUND 布尔型属性，与%FOUND 相反；
%ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
1.5.2 游标示例
- Created on 2022/8/2 by YINGYING01.YU
--打印出部门为80的所有员工的薪水
declare
v_sal employees.salary%type;
v_empid employees.employee_id%type;
--定义游标
cursor emp_sal_cursor is select salary,employee_id f
rom employees where department_id=80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal,v_empid;
while emp_sal_cursor%found loop
dbms_output.put_line('empid:'||v_empid||'salary：'||v_sal);
fetch emp_sal_cursor into v_sal,v_empid;
end loop;
--关闭游标
close emp_sal_cursor;
end;1.5.3 游标示例 使用记录类型
-- Created on 2022/8/2 by YINGYING01.YU
--打印出部门为80的所有员工的薪水
declare
--声明一个记录型
type emp_record is record(
v_sal employees.salary%type,
v_empid employees.employee_id%type
);
--声明一个记录行型的变量
v_emp_record emp_record;
--定义游标
cursor emp_sal_cursor is select salary,employee_id from employees
where department_id=80;
begin
--打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_emp_record;
while emp_sal_cursor%found loop
dbms_output.put_line('empid:'|| v_emp_record.v_empid||
'salary：'|| v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
--关闭游标
close emp_sal_cursor;
end;1.5.4 游标for循环 可以省略游标的定义提取和关闭
-- Created on 2022/8/2 by YINGYING01.YU
--打印出部门为80的所有员工的薪水
declare
v_sal employees.salary%type;
v_empid employees.employee_id%type;
--定义游标
cursor emp_sal_cursor is select salary,employee_id from employees
where department_id=80;
begin
/*---打开游标
open emp_sal_cursor;
--提取游标
fetch emp_sal_cursor into v_sal,v_empid;
while emp_sal_cursor%found loop
dbms_output.put_line('empid:'||v_empid||'salary：'||v_sal);
fetch emp_sal_cursor into v_sal,v_empid;
end loop;
--关闭游标
close emp_sal_cursor;
*/
for c in emp_sal_cursor loop
dbms_output.put_line('empid:'||v_empid||'salary：'||v_sal);
end loop;
end; 1.5.5 隐式游标
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句， 如修改、删除操作 使用隐式游标
隐式游标属性 SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 TRUE； SQL%NOTFOUND 布尔型属性,与%FOUND 相反； SQL %ROWCOUNT 数字型属性, 返回已从游标中读取得记录数； SQL %ISOPEN 布尔型属性, 取值总是 FALSE。SQL 命令执行完毕立即关闭隐式游标。
隐式游标示例
begin
update employees set salary = salary + 10 where employee_id = 1005;
if sql%notfound then
dbms_output.put_line('查无此人!');
end if;
end;1.6 异常处理
1.6.1异常处理有三类
1 ． 预定义 ( Predefined ) 错误 ORACLE 预定义的异常情况大约有 24 个。 对这种异常情况的处理， 无需在程序中定义， 由 ORACLE 自动 将其引发。
2 ． 非预定义 ( Predefined ) 错误 即其他标准的 ORACLE 错误。 对这种异常情况的处理， 需要用户在程序中定义， 然后由 ORACLE 自动将 其引发。
3 ． 用户定义 (User_define) 错误 程序执行过程中， 出现编程人员认为的非正常情况。对这种异常情况的处理， 需要用户在程序中定义， 然后显式地在程序中将其引发。
1.6.2 异常处理结构
EXCEPTION WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >
END;
1.6.3 预定义异常
对这种异常情况的处理，只需在 PL/SQL 块的异常处理部分，直接引用相应的异常情况名，并对其完成 相应的异常错误处理即可。
预定义异常示例
declare
v_salary employees.salary%type;
begin
select salary into v_salary
from employee_id > 100;
dbms_output.put_line(v_salary);
exception
when too_many_rows then dbms_output.put_line('输入行数太多了！')；
when others then dbms_output.put_line('出现其他类型的异常！')；
end;
1.6.4 非预定义异常
对于这类异常情况的处理，首先必须对非定义的 ORACLE 错误进行定义。
非预定义异常示例
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('违反完整性约束条件,不可删除此用户！');
end;
1.6.5 自定义异常
用户定义的异常错误是通过显式使 用 RAISE 语句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION 块异常错误部分
自定义异常示例
-- Created on 2022/8/3 by YINGYING01.YU
--用户自定义异常
declare
e_too_high_sal 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_too_high_sal;
end if;
exception
when e_too_high_sal then dbms_output.put_line('工资太高了');
end;1.7 存储函数和存储过程
ORACLE 提供可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过 程或函数
过程和函数的唯一区别是函数总向调 用者返回数据，而过程则不返回数据。
1.7.2 存储函数
1) OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突
2) 函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN 参数 标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参 数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数 隐含为 IN。
3) 因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类
存储函数语法
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END存储函数示例
1.7.3存储过程
在 ORACLE SERVER 上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储 过程传回参数
存储过程语法
CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
<类型.变量的说明>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END;存储过程示例
1.8 包的创建和使用
1.8.1 创建包示例
create or replace package PKG_YYY is
procedure EMP_INSERT;
procedure EMP_SELECT;
procedure EMP_DELETE;
procedure EMP_UPDATE;
end PKG_YYY;
1.8.2 创建包主体示例
create or replace package body PKG_YYY is
procedure EMP_INSERT is
cursor cursor_insert is
select * from emp03;
v_all cursor_insert%rowtype;
begin
open cursor_insert;
loop
fetch cursor_insert into v_all;
exit when cursor_insert%notfound;
insert into emp03 values(v_all.EMPLOYEE_ID,v_all.LAST_NAME,v_all.SALARY,
v_all.DEPARTMENT_ID,v_all.HIRE_DATE);
commit;
end loop;
close cursor_insert;
end EMP_INSERT;
end PKG_YYY;
1.9 触发器
1.9.1 触发器组成
触发事件：即在何种情况下触发 TRIGGER; 例如：INSERT, UPDATE, DELETE。 触发时间：即该 TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和 该 TRIGGER 的操作顺序。
触发器本身：即该 TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。
触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；
行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一 次
1.9.2 触发器语法
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.9.3 触发器示例
create or replace trigger A_TRIGGER
after
insert or update or delete on emp03
for each row
begin
if inserting then
insert into emp04(employee_id,last_name,salary,
department_id,hire_date)
values(:new.employee_id,:new.last_name,:new.salary
,:new.department_id,:new.hire_date);
elsif updating then
update emp04 set salary =:new.salary
where employee_id=:new.employee_id;
elsif deleting then
delete from emp04 where employee_id= :new.employee_id;
end if;
end A_TRIGGER;二 plsql 双表同步案例
2.1在同一个数据库里面实现双表同步
两种方法:触发器和存储过程
2.1.1 双表同步数据的oracle package使用触发器实现
Oracle会自动地执行触发器中定义的语句序列,对表中数据进行增删改查的时候自动触动执行触发器中定义的语句
https://blog.csdn.net/loveforever__/article/details/126153944?spm=1001.2014.3001.5502
思考:
触发器是实时的
触发器每执行一条语句就会同步一次,还有可能会锁表,如果数据量大的话,负担会比较重
若果双向同步数据,两边都触发不知道会不循环
2.1.2 双表同步数据的oracle package使用存储过程
存储过程:事先编译好的plsql程序块,经编译后存储在数据库中,用户通过指定存储过程的名字给出参数来执行它
定时任务可能会有延迟
思路:可以结合定时任务
IUD 同步标志位
1的话没有同步 0已经同步
思想:扫描A_TEST表中没有同步的然后遍历插入B_TEST表,最后把IUD标志位改成0,下次就不用同步了
2.2在不同数据库里面实现双表同步
两个数据库
2.2.1利用触发器+dblink
命令行里面ipconfig找到IP地址
创建link发现
排除网络不通
可能不是用管理员身份登陆的数据库 没有权限
2.2.2 利用Oracle ogg (Golden Gate)
Oracle ogg是一种基于日志结构化复制软件,通过捕捉源数据库重做日志,获得数据库的增删改查变化下形成tail队列文件
然后会把这些文件通过网络协议传送到目标数据库,再通过解析将数据插入到源数据库,从而实现不同数据库的数据同步
然后Oracle ogg也是需要管理员身份登录的
1175
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
