1. Oracle 查看数据库字符集和客户端字符集 SQL 语句
查询数据库字符集：
select * from nls_database_parameters t where t.parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
查询客户端字符集：
SELECT USERENV('language') FROM DUAL;
2. 查看log和logfile大小
SELECT GROUP#,members,BYTES / 1024 / 1024 AS "SIZE M",STATUS,ARCHIVED FROM v$log;
SELECT * from v$logfile;
col member for a45
select group#, status, type, member from v$logfile;
SELECT GROUP#,
members, BYTES/1024/1024 AS "SIZE M",
STATUS,
ARCHIVED
FROM
v$log;
3. 数据库查看版本
set linesize 100
col CON_ID for a45
col BANNER for a100
select * from v$version;
查看安装了哪些选项
col PARAMETER format a60
col VALUE format a10
select * from sys.v_$option;
4. 数据库勒索病毒查询
select * from dba_objects where object_name like '%DBMS_SUPPORT_INTERNAL%';
select * from dba_objects where object_name like '%DBMS_ CORE_INTERNAL%';
select * from dba_objects where object_name like '%DBMS_STANDARD_FUN9%';
select * from dba_objects where object_name like '%DBMS_SUPPORT_INTERNAL%';
select * from dba_objects where object_name like '%DBMS_ SYSTEM_INTERNAL%';
select * from dba_objects where object_name like '%DBMS_ CORE_INTERNAL%';
查看输出是否含有上面7个名称，下条命令相同
select * from dba_objects where object_name like '%INTERNAL%';
select 'DROP
TRIGGER '||owner||'."'||TRIGGER_NAME||'";' from dba_triggers where
TRIGGER_NAME like 'DBMS_%_INTERNAL%' union all select 'DROP PROCEDURE
'||owner||'."'||a.object_name||'";' from dba_procedures a where
a.object_name like 'DBMS_%_INTERNAL% ';
正常没输出结果。
5. 数据闪回
备份数据
create table au_user_bak as select * from au_user;
启动行移动
alter table au_user enable row movement;
恢复数据
flashback table au_user to timestamp to_timestamp('2024-01-09 11:00:00', 'YYYY-MM-DD HH24:MI:SS');
关闭行移动
6. 使用logminer查看归档日志
@?/rdbms/admin/dbmslm.sql;
@?/rdbms/admin/dbmslmd.sql;
exec sys.dbms_logmnr.add_logfile(logfilename => '/dbfile/archivelog/1_607438_963928319.arc',options => dbms_logmnr.new);
exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);
select seg_owner,count(*) from v$logmnr_contents group by seg_owner;
select count(1),substr(sql_redo,1,60) from v$logmnr_contents group by substr(sql_redo,1,60) order by count(1) desc ;
增加日志
exec sys.dbms_logmnr.add_logfile(logfilename=>'/dbfile/archivelog/1_607439_963928319.arc');
create table logmnr_tab as select * from V$LOGMNR_CONTENTS;
查看日志分析结果，说明：日志分析结果只能在当前会话查看。
显示DML分析结果
select operation,sql_redo,sql_undo from v$logmnr_contents where seg_name='TEMP';
显示DDL分析结果
select to_cahr(timestamp,'yyyy-mm-dd hh23:mi:ss') time,sql_redo from v$logmnr_contents where sql_redo like '%create%' or sql_redo like '%create%';
显示在用字典文件
select db_name,filename from v$logmnr_dictionary;
结束LogMiner
execute dbms_logmnr.end_logmnr;
7. APEX测试Windows AD
# ldapsearch -x -H ldap://192.168.164.2 -D "CN=weishi,CN=Users,DC=wagne,DC=com" -w 1qaz@WSX -b "DC=wagne,DC=com"
8. --更新terry该GP用戶信息
update TIAN_GSDSN
set MONTH='2024.1',
zxdate = to_date('2012/06/19','YYYY/MM/DD'), --日期格式化
zxuser = DEFAULT --用DEFAULT提供的默认值
where zx01 = 'terry';
desc TIAN_GSDSN
update TIAN_GSDSN set MONTH='2024.1'
delete TIAN_GSDSN where 序号 is NULL and 合同号 is NULL and 物料号 is NULL
select count(*) from TIAN_GSDSN_CP
select * from TIAN_GSDSN where 序号 is NULL
9. Oracle PLSQL
DDL数据库定义语言
CREATE TABLE/VIEW/INDEX/...
DROP TABLE/VIEW/INDEX/...
ALTER TABLE/VIEW/INDEX/...
DQL数据库查询语言
SELECT FROM ;.
DML数据库操作语言
INSERT INTO ...
DELETE
UPDATE
PLSQL块编程
declare (可选)
变量定义 (可选)
光标定义 (可选)
begin
程序体 (必须要有)
exception (可选)
例外处理 (可选)
end;
PLSQL块编程-例子
declare
L_HTH varchar2(50);
begin
select 合同号
into L_HTH
from TRANS_PZQD
where 箱号 = '280583863';
dbms_output.put_line(L_HTH);
exception
when no_data_found then
dbms_output.put_line('Error 合同号, cannot found the record.');
end;
PLSQL存储过程procedure-示例处理数据
create or replace procedure update_emp
(p_emp_no in number,
p_emp_name in varchar2,
p_emp_salary in number)
is
v_memo varchar(240);
begin
v_memo := p_emp_no ||'_'|| p_emp_name;
update employee set emp_name = p_emp_name where emp_no=p_emp_no;
update employee set emp_salary = p_emp_salary where emp_no=p_emp_no;
update employee set emp_memo = v_memo where emp_no=p_emp_no;
exception
log('Error on update:' || to_char(p_emp_no));
end;
PLSQL存储过程procedure-返回多个值
create or replace procedure update_emp
(p_emp_no in number,
p_emp_name out varchar2,
p_emp_salary out number)
is
cursor c_emp is
select emp_name,emp_salary
from employee
where emp_no = p_emp_no;
begin
open c_emp;
fetch c_emp into p_emp_name, p_emp_salary;
close c_emp;
exception
log('Error on read employee:' || to_char(p_emp_no));
end;
PLSQL函数Function-示例
create or replace function read_emp _name
(p_emp_no in number)
return varchar2
is
v_emp_name vachar2;
cursor c_emp_name is
select emp_name
from employee
where emp_no = p_emp_no;
begin
open c_emp_name;
fetch c_emp_name into v_emp_name;
close c_emp_name;
exception
log('Error on read employee:' || to_char(p_emp_no));
return null;
end;
包package-示例-规范
保存名:pk_emp.pks
create or replace package pck_emp as
function read_emp_name (p_emp_no in number)
return varchar2;
procedure update-emp
(p_emp_no in number,
p_emp_name in varchar2,
p_emp_salary in numer);
end;
/
包 package - 示例-包体
保存为：pck_emp.pkb
create or replace package body pck_emp as
function read_emp_name ( p_emp_no in number)
return varchar2 is
v_emp_name varchar2;
cursor c_emp_name is
select emp_name
from employee
where emp_no = p_emp_no;
begin
open c_emp_name;
fetch c_emp_name into v_emp_name;
close c_emp_name;
return v_emp_name;
exception
log('Error on read employee:' || to_char(p_emp_no);
return null;
end;
procedure update_emp (p_emp_no in number,
p_emp_name in varchar2
p_emp_salary in number)
is
v_memo varchar(240);
begin
v_memo := p_emp_no ||'_'I|p_emp_name;
update employee set emp_name = p_emp_name where emp_no=p_emp_no;
update employee set emp_salary = p_emp_salary where emp_no=p_emp_no;
update employee set emp_memo = v_memo where emp_no =p_emp_no;
exception
log ('Error on update:' || to_char(p_emp_no);
end;
end;
变量
建议以v_-开头
number; number(12,2), varchar2(20); date;boolean
放在begin前
可以定义时同时赋值：v_date date := sysdate；
可以是%type
v_emp_name employee.emp_name%type;
decare
v_num number;
v_amount number(12,2) :=0.00;
v_date date;
v_flag booleam ;=true;
begin
游标进阶
1） c_emp%found：（如果找到值，就为true）
2） c_emp%notfound：（如果找不到值，就为true）
3） c_emp%rowcount：（返回游标缓冲的记录数）
4） c_emp%isopen：（判断右边是否打开着）
True 是打开着的
False 是关闭的
1) cursor c_emp (p_emp_no in number)
is
select emp_name ,emp_salary
from employee
where emp_no = p_emp_no,
2) v_emp_no number:=1;
begin
open c_emp (v_emp_no);
fetch c_emp into p_emp_name,p_emp_salary;
close c_emp
end;
控制结构-如果1
1）if 条件 then
执行语句；
end if ;
2) if v_emp_level <100 then
emp_salary := emp_salary *110% ;
end if;
3）最简单的if 语句
4）if 语句在程序段内执行
begin
end;
控制结构-如果2
1） if 条件 then
执行源句1；
else
执行语句2
end if ;
2) if V_emp_level <100 then
emp_salary := emp_salary *110% ;
else
emp_salary := emp_salary *120% ;
end if;
3） if 语句在程序段内执行
begin
end;
控制结构 - 如果3
1） if 条件1 then
执行语句1；
elsif 条件2 then
执行语句2；
elsif ... then
执行语句…
end if ；
2) if v_emp_level <100 then
emp_salary := emp_salary *110% ;
elsif v_emp_level <200 then
emp_salary := emp_salary *120% ;
elsif v_emp_level <300 then
emp_salary := emp_salary *130% ;
elsif v_emp_level <400 then
emp_salary := emp_salary *140% ;
else
emp_salary := emp_salary *150% ;
end if;
3） if 语句在程序段内执行
begin
end;
控制结构 -循环oop
1）loop
执行语句；
exit when条件；
end loop ;
2）至少执行一次
3 ） 注意死循环（exit 的条件一定能成立）
declare
V_emp_name varchar2(240);
cursor c_emp is
select emp_name
from employee;
begin
open c_emp;
loop;
fetch c_emp into v_emp_name;
exit when c_emp%notfound;
dbms_outpu.put_line(v_emp_name);
end loop;
close c_emp;
end;
九、控制结构- 循环for
1） for 变量 in reserve 开始..结束 loop
执行语句；
end loop ;
2）变量在开始和结束之间就执行
declare
v_i number;
begin
for v_i in reserve 1..100 loop
insert into employee (emp_no, emp_name, emp_salary) values (v_i,to_char(v_i),v_i *1000);
end loop;
end;
3） 游标在for中使用
for 变量 in 游标 loop
变量操作；
end loop;
declare
cursor c_emp is
select emp_name from employee;
begin
for v_emp in c_emp loop;
dbms_outpu.put_line(v_emp.emp_name);
end loop;
end;
控制结构 - 循环 while
1） while 条件 loop
执行语句；
end loop :
2）可能一次也执行不了
3） 注意死循环
（While条件一定要有不符合的时候）
declare
v_i number;
begin
v_i := 1;
while v_i<=100 loop
insert into employee (emp_no, emp_name, emp_salary) values (v_i,to_char(v_i), v_i *1000);
v_i := v_i +1;
end loop;
end;
Oracle APEX笔记
declare
V_TIAN_XH varchar2(100);
cursor c_xh is
select distinct 箱号 from TIAN_XH;
begin
open c_xh;
loop
fetch c_xh into V_TIAN_XH;
exit when c_xh%notfound;
dbms_output.put_line(V_TIAN_XH);
end loop;
close c_xh;
end;
Oracle APEX页面分页打印，在html语言中加入分页符。
DECLARE
V_TIAN_XH varchar2(100);
V_TIAN_wl varchar2(100);
V_TIAN_mc varchar2(100);
V_TIAN_inner_xh varchar2(100);
cursor c_xh is
select distinct 箱号 from TIAN_XH;
BEGIN
OPEN c_xh;
LOOP
fetch c_xh into V_TIAN_XH;
exit when c_xh%notfound;
dbms_output.put_line(V_TIAN_XH);
Htp.p('
<p>
<p class="MsoNormal" align="right" style="text-align:right;">
'||V_TIAN_XH||'<span> </span>
<div style="page-break-after:always"></div>
</p>
</p>
');
