Rocho.J

人脑是不可靠的, 随时记录感悟并且经常重复!

 

学习笔记---Oracle操作总结

 

代码
--###################################################################################
/*
版本号: Oracle9i或者Oracle9g---i表示internet, g表示grid
iSqlplus (DBA) URL: http://localhost:5560/isqlplus(/dba)
完全卸载oracle: 卸载完成后, 手工删除项 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
服务: OracleService实例名---Oracle核心服务
OracleOraDb10g_home1TNSListener---通过网络访问数据库
OracleOraDb10g_home1iSql*Plus---通过浏览器访问
端口号: Oracle---5560 Sql---1433,1434
登陆: 在cmd中输入 sqlplus; 系统用户名: system 或者 sysdba, 密码:安装时设置
强制登陆: 在cmd中输入 sqlplus / as sysdba
默认帐户: scott 密码:tiger, 默认是locked; hr 密码:tiger

Schema方法: 是与用户关联的表、视图、簇、过程和程序包等对象的命名的集合, 该方案schema在Oracle创建用户时自动创建.
Oracle数据库字典:
USER_*:用户视图，即用户schema中的视图
ALL_*：扩展用户视图，即用户可以访问的视图, 包含自己的用户视图和一个其他的可访问的视图
DBA_*：所有方案的视图

缩写:
DDL(Database Definition Language): 数据库定义语言
DML(Database Manipulation Language): 数据库操作语言
DCL(Database Control Language): 数据库控制语言
DTM(Database Trasaction Management): 数据库事物管理

知识概要:
|---1.查询Select
|
|---2.数据库定义语言DDL: 对表,视图等的操作, 包括create,drop,alter,rename,truncate
|
数据库操作--|---3.数据库操作语言DML: 对记录进行的操作, 包括insert,delete,update
|
|---2.数据库控制语言DCL: 对访问权限等的操作, 包括grant,revoke
|
|---2.数据库事物管理DTM: 对事物的操作, 包括commit,rollback,savepoint

约束: 主键(Primary Key)、外键(Foreign Key)、非空(not null)、唯一(unique)和检查(check)

事物的是由DML(insert,delete,update)开启的, 在Oracle中需要执行commit才能看到DML操作的结果;
而引起事物的提交或结束原因有:
1.DTM操作: commit,rollback,savepoint
2.系统崩溃宕机: 隐式rollback
3.正常: 隐式commit
4.DDL和DCL操作: DDL(create,drop,alter,rename,truncate)
DCL(grant,revoke)

备注:
Oracle中, 字符串和日期区分大小写, 且需要用单引号进行表示. 若要输出单引号, 用''表示一个单引号
Oracle中使用||拼接字符串, Sql中使用+拼接字符串
C#数据类型:
整数: sbyte,byte,short,ushort,int,uint,long,ulong
实数: float,double,decimal
字符: char,string
布尔: boolean
日期: datetime
对象: object
Oracle数据类型:
整数: number(38) --n表示位数
字符: char(2000), nchar(1000), varchar2(4000),nvarcahr2(2000), long
日期: date
Sql注入测试串: ' or 1=1 --
解决注入攻击的手段:
1. 不拼接字符串, 有字符串操作时, 要留心.
2. 使用参数进行赋值, 尽量使用强类型.
3. 对用户输入的字符进行过滤.
4. 若非用串的话(如:用户名登陆过程), 可以用代码先到数据库中查一下是否有这个名字, 没有则禁止操作
*/
--###################################################################################
--查询系统资源:
--常用命令
set linesize
set pagesize
clear screen
show user
show errors --显示错误, 如在存储过程中显示错误
edit ----可以使用edit(ed)编辑刚才输入的语句, 编辑结束后用"/"提交
list --列出sqlplus缓冲区中的命令, 简写为l
begin
dbms_output.put_line('hello, world!'); --Orcle中输出字符, 注意输出语句在begin和end之间, 且注意分号
end;

--表及表信息
decribe user_tables
select*from user_tables
select table_name from all_tables

select procedure_name from user_procedures --查询存储过程

select*from user_sequences --查询序列
select*from all_sequences
select Seq_Test.nextval from dual --查询下一个递增序列的值
select Seq_Test.currval from dual --查询当前序列的值

--视图
select*from user_views

--查看约束名称、表名字、约束类型及字段名
select table_name, constraint_name, constraint_type, search_condition from User_Constraints --查看表约束
select table_name,constraint_name, column_name from user_cons_columns --查看列约束

select c.table_name,c.constraint_name, u.constraint_type, c.column_name
from user_cons_columns c innerjoin user_constraints u
on c.constraint_name = u.constraint_name

grant unlimited tablespace to scott --无法将表空间授予角色, 只能授予用户
revoke unlimited tablespace from scott

--用户及用户角色信息
show user
select*from all_users
select username from user_users
select username,default_tablespace from user_users --查看缺省用户表空间
select*from user_role_privs --查看用户具有的角色

--授权及权限
select*from user_sys_privs --授予用户的系统权限
select*from role_sys_privs --授予角色的系统权限
select*from role_tab_privs --授予角色的表的权限
select*from dba_sys_privs where grantee ='SYS'--查看'SYS'的具体权限, 注意大小写
select grantee,owner,table_name,privilege from dba_tab_privs --查看所有用户的权限
select name from sys.system_privilege_map --查看Oracle提供的系统权限
select*from user_tab_privs_made --用户对象创建时的权限
select*from user_tab_privs_recd --用户对象接收的权限
select*from user_col_privs_made --用户对象创建时列上的权限
select*from user_col_privs_recd --用户对象列接收的权限
--查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs where grantee='RESOURCE'
union
select privilege from dba_sys_privs where grantee in (
select granted_role from dba_role_privs where grantee='RESOURCE'
)


select*from dba_roles

createuser TestUser identitied by TestUser account unlock
grantcreate session to TestUser

create role TestUserRole
grant TestUserRole to scott
grantcreate session, createview, resource, connect to TestUserRole --通常给的用户权限
grant resource, connect to scott

dropuser TestUser
revokecreate seeion from hr

drop role TestUserRole
revoke resource,connect from TestUserRole

alteruser hr identitied by tiger account unlock

--###################################################################################

--创建表, 约束类型: 主键、外键、唯一、check约束、非空约束
-- 注意事项: 序列不保证连续、关键字不必要连续、业务数据不适合作为关键字
droptable T_Event;
drop sequence seq_T_Event;
create sequence seq_T_Event start with1 increment by1;
createtable T_Event(
seqid number(8),
constraint pk_T_Event primarykey(seqid), --单独写约束, 可以方便添加联合主键(在seqid后便添加即可)
 title varchar2(200) constraint nn_T_Event_title notnull,
startdate date constraint nn_T_Event_startdate notnull,
enddate date constraint nn_T_Event_enddate notnull,
constraint ck_T_Event_enddate check(enddate > startdate),
detail varchar2(500),
userid number(8),
constraint fk_T_Event_userid foreignkey(userid) references T_UserInfo(seqid)
)


droptable T_UserInfo;
drop sequence seq_T_UserInfo;
create sequence seq_T_UserInfo start with1 increment by1;
createtable T_UserInfo
(
seqid number(8),
constraint pk_T_UserInfo primarykey(seqid),
username nvarchar2(30) constraint nn_T_UserInfo_username notnull,
constraint uq_T_UserInfo_username unique(username),
create_data date default sysdate
)

--修改表结构
altertable T_Event
add userid number(8)

altertable T_Event
dropcolumn test

--修改表约束
altertable T_Event
addconstraint fk_T_Event_userid foreignkey(userid) references T_UserInfo(seqid)

--###################################################################################

--创建视图
createorreplaceview View_DepEmp
as
select last_name Given_Name,salary
from employees e innerjoin departments d on e.department_id = d.department_id

--###################################################################################

--新增(插入)数据(DML操作, 在commit之后才会提交)
insertinto T_Event(seqid,title,startdate,enddate,detail,userid)
values(seq_T_Event.nextval,'吃饭',to_date('2010-11-5','yyyy-mm-dd'),
to_date('2010-12-1 8:30','yyyy-mm-dd hh-mi'),'有人请吃大餐, 不吃白不吃!','1');
insertinto T_Event(seqid,title,startdate,enddate,detail,userid)
values(seq_T_Event.nextval,'喝酒',to_date('11-5-2012','mm-dd-yyyy'),
to_date('13-12-2012 12:45','dd-mm-yyyy hh-mi'),'有人请喝酒, 不喝白不喝!','1');
commit


insertinto T_UserInfo(seqid,username)
values(seq_T_UserInfo.nextval,'admin');
insertinto T_UserInfo(seqid,username)
values(seq_T_UserInfo.nextval,'user');
commit

--删除数据
truncatetable T_Event --只删除表的数据
deletefrom T_Event where title ='喝酒'
commit

--修改数据
update T_UserInfo set username ='Client'where username='user'; --注意set后边仍为=
commit

--###################################################################################

--查询数据
selectdistinct department_id, job_id from employees --distinct
select department_id as "部门编号", job_id "职位编号" from employees --起别名的方式as和空格, 注意""
select department_id ||' 联合 '|| job_id as "唯一标识" from employees --别名双引号, 连接字符串用单引号
select first_name,last_name,salary from employees where salary >=8000and salary <=10000
select first_name,last_name,salary from employees where salary between8000and10000
select department_id, job_id from employees where department_id isnull
--模糊查询呢: 通配符: %(0-任意字符), _(1个字符),[a,b,c](选择含a或b或c),[^a,b,c](选择不含a或b或c)
select first_name, last_name from employees where last_name like'D%'or last_name like'E%'
select first_name, last_name from employees where last_name like('D%') or last_name like('E%')
select first_name, last_name from employees where last_name between'D'and'F'--注意这里取左不取右
select first_name, last_name from employees where substr(last_name,1,1) in('D','E')SYSTEM

--嵌套查询(子查询): 分为单行子查询和多行子查询, 区别就是子查询的结果是单条记录还是结果集
--1. dual是系统的虚表, 可以用不需要From的select语句中
--2. rownum成为伪列, 用于生成序号.原理: 生成结果集后, 再加上序号
select next_day(sysdate,3) from dual --返回下周2的日期, 周的计算从1开始

--利用伪列进行分页: 将子查询作为虚拟表再查询
--1. 输出前10条排序后的记录
select rownum,department_name from employees where rownum <=10--未排序
select rownum, department_name --排序后的记录, 因为select基本最后执行, 所以select两次
from (
select rownum, department_name
from departments
orderby department_name
)
where rownum <=10
--2. 输出前11-20条排序后的记录
select rn,department_name
from(
select rownum as rn, department_name
from (
select rownum, department_name
from departments
orderby department_name
)
)
where rn between11and20--rownum根据结果集来生成, 一直没有rownum=1的记录, 所以得不到11的记录

--单行子查询: =、>、<
select last_name,salary
from employees
where salary > (selectavg(salary) from employees) --子查询结果为单挑记录
--多行子查询: in(在结果集里面即可)、any(符合任一个即可)、all(完全符合才可)
select last_name,salary --在结果集中
from employees
where salary in (select salary from employees where department_id =110)

select last_name,salary --大于所有的, 也就是大于最大的(替代mssql的top?)
from employees
where salary >all(select salary from employees where department_id =110)

selet last_name,salary --大于任何一个, 也就是大于最小的
from employees
where salary >any(select salary from employees where department_id =110)

--###################################################################################

--排序
selectdistinct department_id, job_id from employees orderby job_id desc--默认是升序asc

--###################################################################################

--聚合函数
selectsum([sid]) from T_StuScore
selectcount([sid]) from T_StuScore --count(*)表示记录数, 而count(字段)忽略掉null值
selectavg([sid]) from T_StuScore
selectmax([sid]) from T_StuScore
selectmin([sid]) from T_StuScore

selectdistinct(department_id), job_id from employees
selectdistinct department_id, job_id from employees

--###################################################################################

--分组函数, where用于对记录的筛选, having用于对组的筛选, 并且组函数将忽略结果为null的字段
select gender,Counter =count(*) from T_Stuinfo groupby gender
select gender,Counter =count(*) from T_Stuinfo groupby gender havingcount(*) >=2

--###################################################################################

--表连接: 笛卡尔积(m*n条记录), 内连接, 外连接(左外连接、右外连接、全外连接), 自连接
--内连接: 先从m和n中选择, 然后再连接
select sname,sid,cid,score
from T_StuInfo s innerjoin T_StuScore c on s.sid = c.sid

--左外连接(左连接): 内连接 + 左表剩余记录(右表记录置null)
select sname,sid,cid,score
from T_StuInfo s leftjoin T_StuScore c on s.sid = c.sid

--右外连接(右连接): 内连接 + 右表剩余记录(左表记录置null)
select sname,sid,cid,score
from T_StuInfo s rightjoin T_StuScore c on s.sid = c.sid

--全外连接(全连接): 内连接 + 左表剩余记录(右表记录置null) + 右表剩余记录(左表记录置null)
select sname,sid,cid,score
from T_StuInfo s fullouterjoin T_StuScore c on s.sid = c.sid

--自连接(全连接): 本表与本表连接, 可以是外连接或内连接
select sname,sid,cid,score
from T_StuInfo s innerjoin T_StuInfo c on s.sid = c.sid

--###################################################################################

--函数: Oracle中的函数分为单行函数和组函数两种. 组函数用于Group by字句中.
-- 单行函数包括: 字符函数、数字函数、日期函数、Sysdate以及一些其他函数.
--系统函数 注意: 如果要在sqlplus中看到输出, 需要先执行set serveroutput on
begin
--nvl('expression',value)函数, expression为null便用后便的value代替
 dbms_output.put_line(nvl(null,0));
dbms_output.put_line(to_char(sysdate));
dbms_output.put_line(to_number(123.567));
dbms_output.put_line(to_char(to_date('2010-08-09','yyyy-mm-dd')));
end;

--字符函数
begin
dbms_output.put_line(lower('I love CHINA!')); --大写变小写
 dbms_output.put_line(upper('You don''t even have a dog!')); --小写变大写
 dbms_output.put_line(initcap('poo poo! my sweaty! ')); --每单词首字母大写其余小写
 dbms_output.put_line(length('Rocho^_^J')); --求串长
 dbms_output.put_line(substr('I love CHINA!',3,4)); --求子串, Oracle中下表从1开始
 dbms_output.put_line(instr('I love CHINA!','CHINA')); --求字串的下表
 dbms_output.put_line(instr('IN love with CHINA!','IN',5)); --求第5位后的字串的下表
 dbms_output.put_line(concat('I love',' you')); --串连接, 不常用. 可用||代替
 dbms_output.put_line('I love'||' you'); --串连接||
 dbms_output.put_line(lpad('I love CHINA!',20,'*')); --填充, 15为总长度, 不够补*
 dbms_output.put_line(rpad('I love CHINA!',20,'*'));
dbms_output.put_line(trim('C'From'Carton MovieC')); --去除两边的一个字符
 dbms_output.put_line(replace('I love CHINA','CHINA','you')); --替换子串
end;

--数字函数
begin
dbms_output.put_line(round(156.26789,2)); --四舍五入, 2为保留2位小数
 dbms_output.put_line(round(156.26789,-2)); --四射五入, -2为保留整数, 从.开始
 dbms_output.put_line(trunc(123.4567,3)); --截取.后多少位, 类似上边, 但是不进位
 dbms_output.put_line(16 mod 3 );
end;

--日期函数: 中文系统下, 默认日期显示格式为: 日-月-年
--通过alter Session set nls_date_format = 'yyyy-mm-dd'
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd hh:mi:ss')); --DateTime.Now
 dbms_output.put_line(to_char(sysdate,'yyyy')); --年份
 dbms_output.put_line(to_char(sysdate,'mm')); --月份
 dbms_output.put_line(to_char(sysdate,'dd')); --天书
 dbms_output.put_line(to_char(sysdate,'d')); --星期几, 结果需要-1
 dbms_output.put_line(to_char(sysdate,'ddd')); --一年中的第几天
 dbms_output.put_line(to_char(sysdate,'hh')); --小时
 dbms_output.put_line(to_char(sysdate,'hh12')); --12小时制小时
 dbms_output.put_line(to_char(sysdate,'hh24')); --24小时制小时
 dbms_output.put_line(to_char(sysdate,'mi')); --分
 dbms_output.put_line(to_char(sysdate,'ss')); --秒
 dbms_output.put_line(to_char(sysdate,'q')); --季度
 dbms_output.put_line(to_char(sysdate +2)); --加减2，得到的前或后两天
 dbms_output.put_line(to_char(
months_between(
to_date('2011-08-08','yyyy-mm-dd'),
to_date('2010-11-9','yyyy-mm-dd')
)
)
); --相差几个月
 dbms_output.put_line(to_char(
add_months(to_date('2011-08-08','yyyy-mm-dd'),3)
)); --相差几个月
 dbms_output.put_line(to_char(
add_months(to_date('2011-08-08','yyyy-mm-dd'),3)
)); --相差几个月
 dbms_output.put_line(to_char(
last_day(to_date('2011-08-08','yyyy-mm-dd'))
)); --该月最后一天
 dbms_output.put_line(to_number(
to_char(
to_date('2011-8-8','yyyy-mm-dd'),'yyyy')
)
); --日期变数字
end;

select next_day(sysdate,3) from dual --返回下周2的日期, 周的计算从1开始

--###################################################################################

--范式: 1NF: 原子性, 2NF: 单主键, 3NF: 去除传递依赖, BCNF: 决定每个记录的因素都包含关键字, 但不是关键字的子集
--E-R模型(表间关系): 1对1： 任何一个表添加外键; 1对多: 在多的一方添加外键; 多对多: 需要第三个表, 添加前两表的外键

--###################################################################################

--Oracle程序块: declare、begin、exception、end四个关键字, 注意: end后面的";"不能省略, 且打开输出set serveroutput on
--系统变量:
sqlcode --在异常处理中, 返回当前的错误号
sqlerrm --在异常处理中, 返回当前的错误信息
%rowcount--获得sql命令影响的行数
%rowtype --获得表或视图的记录类型
%type --变量类型参考表或视图中字段的类型
%found --用于游标, 表示是否找到数据
%notfound --用于游标, 表示是否没有找到

--自定义变量
declare
v_name nvarchar2(30);
v_email nvarchar2(50) default'webmaster@google.com';
begin
v_name :='张三';
dbms_output.put_line(v_name);
dbms_output.put_line(v_email);
exception --可省略
when others then
dbms_output.put_line('其他异常');
end;

--条件表达式: 相等为:=, 比较相等为=
declare
v_name nvarchar2(30) :='张三';
v_pass nvarchar2(10) :='zhangsan';
v_inputname nvarchar2(30);
v_inputpass nvarchar2(10);
begin
v_inputname :='张三';
v_inputpass :='zhangsan';
if v_inputname <> v_name then
dbms_output.put_line('不存在用户名'|| v_inputname);
elsif v_inputpass != v_pass then
dbms_output.put_line('密码错误!');
elsif v_inputname = v_name and v_inputpass = v_pass then
dbms_output.put_line('登陆成功!');
else
dbms_output.put_line('用户名和密码不匹配! ');
endif;
exception
when others then
dbms_output.put_line('系统异常! ');
end;

--循环
declare--输出fibonacci前10项
 v_num1 number(5) :=1;
v_num2 number(5) :=1;
v_num3 number(5); --前两个数的和
 v_i number(5) :=0; --控制循环次数
begin
dbms_output.put(v_num1); --输出初始的1,1
 dbms_output.put(''|| v_num2);
loop
exitwhen v_i >=8; --前两个已有
 v_num3 := v_num1 + v_num2;
dbms_output.put(''|| v_num3);
v_num1 := v_num2;
v_num2 := v_num3;
v_i := v_i +1;
end loop;
dbms_output.put_line(''); --一定要有put_line才能显示put的信息
--省略exception
end;

--case when
--搜索case when(用于一个范围), Oracle中不支持?
--简单case when(类似swtich, 用于一个定值)
select
case table_name
when'JOBS'then'工作表'
when'REGIONS'then'地区表'
when'DEPARTMENTS'then'部门表'
else'其他表'
end
from user_tables

declare
v_gender number(1) :=0;
begin
case v_gender
when0then dbms_output.put_line('男');
when1then dbms_output.put_line('女');
else dbms_output.put_line('暂时未知');
endcase;
end;

--查询处理: 单行结果可以直接处理, 而多行结果需要用游标处理
--单行结果: 必须有一行结果, 不能是多行也不能是空行
declare
v_title nvarchar2(200);
v_detail T_Event.detail%type; --%type参照表或视图的字段类型
begin
select title,detail into v_title,v_detail
from T_Event
where seqid =1;
dbms_output.put_line('记事详情: '|| v_title ||''||v_detail);
end;
--定义记录类型
declare
type T_UserInfo is record(
seqid number(8),
username nvarchar2(30),
create_data date
);
v_row T_UserInfo;
begin
select seqid,username,create_data into v_row
from T_UserInfo
where seqid =3;
dbms_output.put_line('用户详情: 序号< '|| v_row.seqid ||' > , 用户姓名: < '||v_row.username ||
' > , 用户创建时间: < '||v_row.create_data ||' > .');
end;
--参考记录的类型
declare
v_row T_UserInfo%rowtype; --参考原表的记录类型
begin
select seqid,username,create_data into v_row
from T_UserInfo
where seqid =3;
dbms_output.put_line('用户详情: 序号< '|| v_row.seqid ||' > , 用户姓名: < '||v_row.username ||
' > , 用户创建时间: < '||v_row.create_data ||' > .');
end;

--多行结果--->游标: 1. 定义游标cursor 2. 打开游标open 3. 从游标中读取fetch 4. 判断游标状态 5. 关闭游标close
declare
v_row T_UserInfo%rowtype; --参考原表的记录类型
cursor v_cs isselect*from T_UserInfo; --1. 定义游标
begin
open v_cs; --2. 打开游标
 loop
fetch v_cs into v_row; --3. 从游标读取数据
exitwhen v_cs%notfound; --4. 判断游标状态
 dbms_output.put_line('序号< '|| v_cs%rowcount||' > ');
dbms_output.put_line('用户姓名: < '||v_row.username ||' > ');
dbms_output.put_line('用户创建时间: < '||v_row.create_data ||' > ');
end loop;
close v_cs; --5. 关闭游标
end;

--游标2：
declare
type type_cs is ref cursor; --系统游标
 v_cs type_cs;
v_title T_Event.title%type;
v_startdate date;
v_enddate date;
v_detail T_Event.detail%type;
begin
GetNoticesByDateParametersOut(to_date('2010-7-8','yyyy-mm-dd'),v_cs); --掉存储过程
 loop
fetch v_cs into v_title, v_startdate, v_enddate, v_detail;
exitwhen v_cs%notfound;
dbms_output.put_line('记事详情: 标题< '|| v_title ||' > , 开始时间: < '||v_startdate ||
' > , 结束时间: < '||v_enddate ||' > , 内容: < '||v_detail ||' >. ');
end loop;
close v_cs;
end;

--###################################################################################

--事物: 事物的特性ACID(一致性(Consistency)、原子性(Atomicity)、隔离性(Isolation)、持久性(Durability))
--Oracle中的事物由任意一条DML语句开始, 不需要显示的开始事物
declare
v_username nvarchar2(30);
v_title nvarchar2(200);
v_startdate date;
v_enddate date;
v_detail nvarchar2(500);
v_userid number(8);
begin
v_username :='李四';
v_title :='喝喜酒';
v_startdate := to_date('2010-11-05','yyyy-mm-dd');
v_enddate := to_date('2010-12-05','yyyy-mm-dd');
v_detail :='准时到, 备好红包';
insertinto T_UserInfo(seqid,username) values(seq_T_UserInfo.nextval,v_username);
insertinto T_Event(seqid,title,startdate,enddate,detail,userid)
values(seq_T_Event.nextval,v_title,v_startdate,v_enddate,v_detail,seq_T_UserInfo.currval);
commit;
exception --异常处理部分如果不写, 则异常将升级到程序的调用环境中处理
when others then
rollback;
dbms_output.put_line('数据写入过程出错, 操作已回滚! ');
end;

--带异常控制的事物
--raise_application_error(-20999,'XXXXX')用于抛出xxxx的自定义异常, 异常号必须在-20000~-20999之间
begin
--raise zero_divide; --即使程序没有错误, 也可以手动抛出异常
insertinto T_UserInfo(seqid,username) values(seq_T_UserInfo.nextval,'王五');
insertinto T_Event(seqid,title,startdate,enddate,detail,userid)
values(seq_T_Event.nextval,'生日',
to_date('2010-11-05','yyyy-mm-dd'),
to_date('2010-12-05','yyyy-mm-dd'),
'准备蛋糕',
seq_T_UserInfo.currval);
commit;
exception --异常处理部分如果不写, 则异常将升级到程序的调用环境中处理
--Orale内部定义的异常, 资源忙错误号为: -54; sqlcode和sqlerrm用来返回当前错误号和错误信息
when no_data_found thenrollback;dbms_output.put_line('错误: 没有找到数据'); --错误号:-1403
when too_many_rows thenrollback;dbms_output.put_line('错误: 返回了多行数据! '); --错误号为: -1422
when invalid_cursor thenrollback;dbms_output.put_line('错误: 无效的游标 ');
when zero_divide thenrollback;dbms_output.put_line('错误: 除0错误 '); --错误号位: -1476
when dup_val_on_index then
rollback;
dbms_output.put_line('错误: 唯一索引不能有重复值! ');
dbms_output.put_line('数据写入过程出错, 操作已回滚! '||'当前错误号: '|| sqlcode ||'当前错误信息: '|| sqlerrm);
when others then
rollback; --手动抛出异常, 程序将中断执行, 因此提前rollback
 raise_application_error(-20011,'这是自定义的错误信息! '); --抛出自定义异常
end;

--###################################################################################

--索引: 列中包含大范围值、列中包含大量null、经常被用户查询的where或join的连接条件、表中数据多, 但常用的行少于2%-4%
createindex lower_Employees_Last_name_Idx on employees(lower(last_name)) --为提高查询效率, 先转一下小写

select*from user_indexes --查询索引
select*from user_ind_columns --索引中列的数据字典

--###################################################################################

--存储过程(Stored Procedure): 只需要定义参数的类型, 而不能指定参数的宽度,in、out、in out, SP用来存储程序块
--带参数的
createorreplaceprocedure SP_ShowFibonacci(
p_num innumber
)
is
v_num1 number(5) :=1;
v_num2 number(5) :=1;
v_num3 number(5) :=0;
v_i number(5) :=0;
begin
dbms_output.put(v_num1 ||'');
dbms_output.put(v_num2 ||'');
loop
exitwhen v_i >= p_num -2;
v_num3 := v_num1 + v_num2;
dbms_output.put(v_num3 ||'');
v_num1 := v_num2;
v_num2 := v_num3;
v_i := v_i +1;
end loop;
dbms_output.put_line(''); --显示结果
end Sp_ShowFibonacci;
--调用: sqlplus中, exec ShowFibonacci(15);
begin
ShowFibonacci(15); --在程序块中调用存储过程不需要exec
end;


--带输入参数和普通输出参数, in表示传入参数, out表示输出参数, returning用于返回刚刚插入的记录
createorreplaceprocedure SP_InsertAndReturnPKFromT_Event(
p_pkid out number, --输出参数
 p_title in nvarchar2,
p_startdate in date,
p_enddate date, --默认就是in
 p_detail in nvarchar2,
p_userid innumber
)
is
--v_pkid number(8);
begin
--v_pkid := seq_t_event.curral; --暂存currval
insertinto T_Event(seqid,title,startdate,enddate,detail,userid)
values(seq_t_event.nextval,p_title,p_startdate,p_enddate,p_detail,p_userid)
returning seqid into p_pkid; --注意: returning是insert的子句, 用于返回刚插入的记录, 所以前面无分号
--p_pkid := seq_t_event.currval; --这种返回方法, 需要在插入前缓存currval
commit;
exception
when others then
rollback;
end InsertAndReturnPKFromT_Event;
--调用: 带输出参数的存储过程在Sqlplus中调用也需要写程序块
--程序块调用, 不需要exec
declare
v_pkid number(8);
begin
SP_InsertAndReturnPKFromT_Event(v_pkid,'吃饭',to_date('2010-11-07','yyyy-mm-dd'),
to_date('2010-12-05','yyyy-mm-dd'),'带红包',1);
dbms_output.put_line(v_pkid);
end;


--带输入参数和系统引用输出游标
createorreplaceprocedure SP_GetEVentByDate(
p_date in date,
p_userid innumber,
p_cs out sys_refcursor
)
is
v_cs sys_refcursor; --1. 定义游标
begin
open v_cs --2. 打开游标
for
select title,startdate,enddate,detail,userid
from T_Event
where to_date(to_char(startdate,'yyyy-mm-dd'),'yyyy-mm-dd') = p_date and userid = p_userid;
--startdate要去掉时间, 才能查到多条记录. to_char之后再to_date即可
--3. 将查询结果以游标输出
 p_cs := v_cs;
end GetEventByDate;
--调用
declare
v_cs sys_refcursor;
v_title nvarchar2(200);
v_startdate date;
v_enddate date;
v_detail nvarchar2(500);
v_userid number(8);
begin
--程序块使用游标, 不需要加exec
 SP_GetEVentByDate(to_date('2010-11-05','yyyy-mm-dd'),1,v_cs);
--使用输出游标, 也不需要打开游标
 loop
fetch v_cs into v_title,v_startdate,v_enddate,v_detail,v_userid;
exitwhen v_cs%notfound;

dbms_output.put_line(v_title || v_startdate || v_enddate || v_detail || v_userid);
end loop;
close v_cs; --关闭游标
end;


--通过存储过程插入数据
createorreplaceprocedure SP_NewNoticeItem(
p_date in date,
p_start in date,
p_end in date,
p_title in NoticeItem.title%type,
p_content in NoticeItem.content%type
)
is
v_count calender.itemcount%type;
v_calender_dateid calender.dateid%type;
begin
--判断calender中是否有相应的记录
select nvl(sum(itemcount),0), sum(dateid) into v_count, v_calender_dateid
from calender
where dateitem = p_date;

--如果有, 则更新数量, 这里同样也需要暂存dateid, 以便在noticeitem表中进行添加
if v_count >0then
update calender set itemcount = v_count +1--update set之后是采用=号
where dateitem = p_date;
else
--如果无, 则增加新纪录
--v_calender_dateid := seq_calender.nextval; --不能直接使用sequence, 因多人访问, 需要用局部变量暂存sequence
select seq_calender.nextval into v_calender_dateid from dual; --以上表达式的另外一种写法
insertinto calender(dateid,dateitem,itemcount)
--values(seq_calender.nextval, p_date, 1);
values(v_calender_dateid,p_date, 1);
endif;
-- 在NoticeItem表中增加活动的内容
insertinto NoticeItem(itemid, dateid, start_time, end_time, title, content)
--values(seq_noticeitem.curval, seq_calender.curval) --因为多人访问, 所以seq_calender.NextVal可能被改变, 需暂存
values(seq_noticeitem.nextval, v_calender_dateid, p_start, p_end, p_title, p_content);
commit;
--如果中间有异常, 则rollback
exception
when others then
rollback;
end SP_NewNoticeItem;

--###################################################################################

--函数: 与存储过程相比, 函数必须有返回类型, 内部应只对数据进行运算, 避免在函数中处理记录--用来存储程序块
createorreplacefunction fn_truncdate(
p_date date
)
return date
is
result date;
begin
result := to_date(to_char(p_date,'yyyy-mm-dd'),'yyyy-mm-dd');
return result;
exception
when others then
dbms_output.put_line('截取日期出现错误');
end;
--调用
select fn_truncdate(to_date('1985-11-11 8:30','yyyy-mm-dd hh-mi')) from dual

--###################################################################################

--触发器: 分为基于表(before,after)和基于视图(instead of). :new表示新纪录, :old表示旧记录.
-- 还可以通过deleting、inserting和updating获得引起触发器的动作
--视图存储的是Sql语句, 所以通常只对表进行DML操作的触发器
createorreplacetrigger tg_noticeitem
after deleteon noticeitem
for each row
declare
v_count calender.itemcount%type;
begin
--触发器中的隐士参数(:new 代表操作之后的记录 :old 代表操作之前的记录)
--(:old.dateid)表示noticeitem中的外键
--读取calender表中的活动的数量itemcount
select itemcount into v_count
from calender
where dateid = :old.dateid;

if v_count >1then
update calender
set itemcount = itemcount -1
where dateid = :old.dateid;
else--注意elsif的写法
deletefrom calender
where dateid = :old.dateid;
endif;
end tg_noticeitem;


--创建监视表
droptable T_Monitor
createtable T_Monitor(
table_name nvarchar2(30),
constraint pk_T_monitor primarykey(table_name),
countnumber(8),
create_date date default(sysdate)
)

insertinto T_Monitor(table_name,count) values('T_EVENT',0)
commit

createorreplacetrigger tg_monitor --触发器没有参数
 after insertordeleteorupdateon T_EVENT
--for each row --默认为statement level表示语句级, 表示一条语句执行一次, 而each row表示每行
declare

begin
if deleting then
dbms_output.put_line('删除操作! ');
elsif inserting then
dbms_output.put_line('插入操作! ');
elsif updating then
dbms_output.put_line('更新操作! ');
endif;

update T_Monitor setcount=count+1
where table_name ='T_EVENT';
end tg_monitor;

 

 

 

//Sql语句注入模拟代码:

代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SqiInjection
{
class Program
{
staticvoid Main(string[] args)
{

//1. 连接串, 通常写在配置文件中:
string constr ="Data Source=Orcl;User ID=system;Password=sa";

//2. 连接对象
using (Oracle.DataAccess.Client.OracleConnection connection
=new Oracle.DataAccess.Client.OracleConnection(constr))
{
Console.Write("请输入用户名: ");
string name = Console.ReadLine();
Console.Write("\n请输入id: ");
string id = Console.ReadLine();
//3. 命令对象
string query1 ="select * from T_UserInfo where UserName= '"+ name +"' and seqid = '"+ id +"'";
string praquery =@"select * from T_UserInfo where UserName=:username and seqid =:seqid";

Oracle.DataAccess.Client.OracleCommand command =new Oracle.DataAccess.Client.OracleCommand();
command.Connection = connection;
command.CommandType = System.Data.CommandType.Text;

//参数对象
 Oracle.DataAccess.Client.OracleParameter v_username
=new Oracle.DataAccess.Client.OracleParameter(":username", Oracle.DataAccess.Client.OracleDbType.NVarchar2, 30);
Oracle.DataAccess.Client.OracleParameter v_seqid
=new Oracle.DataAccess.Client.OracleParameter(":seqid", Oracle.DataAccess.Client.OracleDbType.NVarchar2, 30);

v_username.Value = name;
v_seqid.Value = id;


try
{
connection.Open();
Console.WriteLine("连接已建立!");
command.CommandText = query1;
getSelectResult(command);

Console.WriteLine("\n---------------使用参数登陆-----------------\n");
command.CommandText = praquery;
command.Parameters.Add(v_username);
command.Parameters.Add(v_seqid);
getSelectResult(command);
}
catch (Oracle.DataAccess.Client.OracleException oex)
{
Console.WriteLine("发现异常, 异常信息为: ", oex);
}
finally
{
Console.WriteLine("连接已关闭!");
}

}
}

privatestaticvoid getSelectResult(Oracle.DataAccess.Client.OracleCommand command)
{
if (command.ExecuteScalar() !=null)
{
Console.WriteLine("登陆已成功! ");
}
else
{
Console.WriteLine("登陆失败! ");
}
}
}
}

 

 

//批量删除多张表

DECLARE
  I INTEGER;
BEGIN
  FOR TODROP IN (SELECT OBJECT_NAME
                   FROM USER_OBJECTS
                  WHERE OBJECT_TYPE = 'TABLE' and  created > to_date('2014-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
  LOOP
     --EXECUTE IMMEDIATE 'drop table ' || TODROP.OBJECT_NAME || '';
     dbms_output.put_line('drop table "' || TODROP.OBJECT_NAME || '";');
  END LOOP;
END;
View Code

 

 

//创建用户和表空间

Oracle建立表空间和用户                 
 
[sql] view plain copy
建立表空间和用户的步骤：  
用户  
建立：create user 用户名 identified by "密码";  
授权：grant create session to 用户名;  
            grant create table to  用户名;  
            grant create tablespace to  用户名;  
            grant create view to  用户名;  

[sql] view plain copy
表空间  
建立表空间(一般建N个存数据的表空间和一个索引空间)：  
create tablespace 表空间名  
datafile ' 路径(要先建好路径)\***.dbf  ' size *M  
tempfile ' 路径\***.dbf ' size *M  
autoextend on  --自动增长  
--还有一些定义大小的命令，看需要  
 default storage(  
 initial 100K,  
 next 100k,  
);  
[sql] view plain copy
例子：创建表空间  
create tablespace DEMOSPACE   
datafile 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf'   
size 1500M   
autoextend on next 5M maxsize 3000M;  
删除表空间  
drop tablespace DEMOSPACE including contents and datafiles  

[sql] view plain copy
用户权限  
授予用户使用表空间的权限：  
alter user 用户名 quota unlimited on 表空间;  
或 alter user 用户名 quota *M on 表空间;  

完整例子：
[sql] view plain copy
--表空间  
CREATE TABLESPACE sdt  
DATAFILE 'F:\tablespace\demo' size 800M  
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;   
--索引表空间  
CREATE TABLESPACE sdt_Index  
DATAFILE 'F:\tablespace\demo' size 512M           
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;       
  
--2.建用户  
create user demo identified by demo   
default tablespace demo;  
   
--3.赋权  
grant connect,resource to demo;  
grant create any sequence to demo;  
grant create any table to demo;  
grant delete any table to demo;  
grant insert any table to demo;  
grant select any table to demo;  
grant unlimited tablespace to demo;  
grant execute any procedure to demo;  
grant update any table to demo;  
grant create any view to demo;  
[sql] view plain copy
--导入导出命令     
ip导出方式： exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y  
exp demo/demo@orcl file=f:/f.dmp full=y  
imp demo/demo@orcl file=f:/f.dmp full=y ignore=y
View Code

 

//导入指定用户的表空间

oracle 如何导入dmp文件到指定表空间
1. 打开工具Oracle SQL Plus 以dba身份登录sys用户
user: sys
password: sys 
主机字符串(H)：orcl as sysdba

2. 创建用户并指定表空间  
--create user 用户名 identified by 密码 default tablespace 缺省表空间 Temporary tablespace 临时表空间;
drop user jandardb cascade; 
create user jandardb identified by jandardb; 
alter user jandardb default tablespace jandardb;  
grant connect,resource,dba to jandardb;  --grant
connect,resource,dba to 用户名;
revoke unlimited tablespce from jandardb;     --revoke
unlimited tablespace from 用户名;
alter user jandardb quota 0 on users;      --alter user 用户名 quota 0 on Users; 
alter user jandardb quota unlimited on jandardb;   --alter user 用户名 quota unlimited on 用户缺省表空间;

3. 使用imp工具导入dmp数据文件
imp jandardb/jandardb@orcl file=c:\jandardb.dmp fromuser=jandardb touser=jandardb log=c:\log.txt                 




数据库中用户try的数据一直放在system表空间中；
今天把该用户的所有数据exp到文件try.dmp中，准备再导入到另一个测试数据数据中的test用户中，同时放在test表空间中.

1、在第一个数据库导出数据：exp try/try wner=try file=/try.dmp log=try.log
2、将try.dmp ftp到第二个数据库所在主机上
3、在第二个数据库导入数据：imp test/test fromuser=try touser=test file=/try.dmp log=test.log
但是导完后发现数据任然被导入到了system表空中。       
后通过查询后得知，要成功导入其他表空间需要:
1、先将test用户在system空间中的UNLIMITED TABLESPACE权限回收：REVOKE UNLIMITED TABLESPACE FROM test 
2、设置默认表空间：alter user test default tablespace  test  
3、设置默认的表空间无限配额：alter user test quota unlimited on test 
4、设置特斯通用户对其他表空间的quota为0：alter user test quota 0 on system。。。。。。。  

再重新导入try.dmp，这是数据全部导入到test表空中了。
View Code

 

 

 

 

//Oracle数据库导入导出

删除用户 
drop user monitor cascade; 
删除表空间和数据文件 
drop tablespace monitor_ts including contents and datafiles; 



创建用户 
create user monitor identified by monitor; 
创建表空间 
create tablespace ts_wangf datafile 'C:\oracle\product\10.2.0\tablespaceBIMS\monitor-data.dbf' size 100m autoextend on; 
将表空间分配给用户 
alter user monitor default tablespace monitor_ts; 
给用户授权 
//grant create session,create table,create view,unlimited tablespace to wangf; 
grant dba to monitor; 



//============================imp导入DMP文件到指定表空间所需作的操作=============================================================================================
找了一个几百万行数据的库，准备导入的本地Oracle中： 
SAM用户的缺省表空间是SAM，但是数据却导入到了system表空间。Google了一下，应该这样做： 
1.收回unlimited tablespace权限revoke unlimited tablespace from sam; 
2.设置缺省表空间alter user sam default tablespace sam; 
3.设置SAM表空间的unlimited配额alter user sam quota unlimited on sam; 
4.设置其他表空间的0配额alter user sam quota 0 on system; 

最后在cmd中执行（不要进入sqlplus）：imp wangf/wangf@orcl file='D:\1\内部资源\河北联通BMIS管理平台\数据库结构\BimsManager.DMP' fromuser=monitor touser=wangf 
//===============================================================================================================================================================





我将公司oracle9i中的BimsManager数据库导入我自己的10G的数据库，所做的事： 
1、查看了导出的日志文件，为全库导出，即full=y,而fromuser=monitor 
2、创建BimsManager数据库 
3、创建用户monitor 
create user monitor identified by monitor; 
4、创建表空间(必须要 autoextend on，不然报出表空间配额不够的错,估计是因为导出时的表空间比较大) 
create tablespace monitor_ts datafile 'C:\oracle\product\10.2.0\tablespaceBIMS\monitor-data.dbf' size 100m autoextend on; 
  5、将表空间分配给用户 
  alter user monitor default tablespace monitor_ts; 
  分配之后查看： 
  select username,default_tablespace from dba_users; 
  6、给用户monitor授予dba的权限(因为导出时的monitor用户貌似是dba权限) 
  grant dba to monitor; 
  7、接下来的三条语句都与将DMP文件导入到指定表空间(monitor的表空间monitor_ts)有关，quota是配额 
  revoke unlimited tablespace from monitor; 
  alter user monitor quota unlimited on monitor_ts; 
  alter user monitor quota 0 on system; 
  alter user monitor quota unlimited on monitor_ts;
  8、很重要的一步：删除sysman用户的一个JOB，不然导入的时候最后会报一个违反唯一性约束的错 
  因为导出的9i中的monitor用户占了JOB_ID为1，而10G中sysman占用了JOB_ID是1 
  
  查看系统job：select job from dba_jobs: 
  删除job：以sysman登录sqlplus，首先执行:exec dbms_job.remove(1); 
                                 再执行：commit; 
  
  9、最后在cmd中执行（不要进入sqlplus）： 
  imp monitor/monitor@bimsmana file='D:\1\内部资源\河北联通BMIS管理平台\数据库结构\BimsManager.DMP' fromuser=monitor touser=monitor 
  
  
  最后说一句：oracle的提示“成功终止导入”其实意思是“成功完成导入”，终止是完成的意思。。。。。。。。。。。。
View Code

 

首先运行 cmd

然后：

C:\Documents and Settings\wzq>imp
Import: Release 10.2.0.1.0 - Production on 星期日 11月 7 13:29:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

分别输入 用户名  密码

Username: hr
Password:


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

下面提示你 输入 要导入的 dmp 文件名

Import file: EXPDAT.DMP >
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)

这里问，是否仅仅列内容，不导入
List contents of import file only (yes/no): no >

这里问 如果对象已经存在了， 是否忽略创建的错误
Ignore create error due to object existence (yes/no): no >

这里问，是否导入权限
Import grants (yes/no): yes > no

这里问，是否导入表数据
Import table data (yes/no): yes >

这里问，是否导入整个文件
Import entire export file (yes/no): no > yes
. importing HR's objects into HR
. importing HR's objects into HR
. . importing table                            "A"          8 rows imported
Import terminated successfully without warnings.

然后就结束了。
View Code

 

//一个用户2个表空间的导入导出实例

-- -- 删除表空间和数据文件
-- drop tablespace gxHis including contents and datafiles;
-- drop user sa cascade;

-- 创建用户:源用户root，目标用户：sa
create user sa identified by sa123;

-- 创建表空间:tb3，tbExt_DATA
create tablespace tb3 datafile 'D:\Databases\Oracle\oradata\OrclSql\tb3-data.dbf' size 200m autoextend on;
create tablespace tbExt_DATA datafile 'D:\Databases\Oracle\oradata\OrclSql\tbExt-data.dbf' size 200m autoextend on;
alter user sa default tablespace tb3;
grant dba to sa;

-- select * from dba_tablespaces;
-- select username，default_tablespace from dba_users;

revoke unlimited tablespace from sa;
alter user sa quota unlimited on tb3 quota unlimited on tbExt_DATA;
alter user sa quota 0 on system;


-- 在cmd窗口下输入：
 imp sa/sa123 file='D:\Databases\Oracle\oradata\123.dmp' fromuser=root touser=sa log='D:\Databases\Oracle\oradata\log.txt'
View Code

 

 

//Oracle的tnsnames配置，和listener配置

本机PL/Sql、SqlPlus正常， 远程连接OrclSql提示ora-12541，无法解析请求。原因是由于后来配置了新实例，tnsping只是解析是否有实例能通，这个远程执行是通的。但是sqlplus连接时，就需要具体的实例名字，实例名字不对是连不上的。

# listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = OrclSql)
      (ORACLE_HOME = C:\Oracle\product\11.2.0\dbhome_1)
      (SID_NAME = OrclSql)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.103)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle



========================


# tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCLSQL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.103)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = OrclSql)
    )
  )

LISTENER_ORCLSQL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.103)(PORT = 1521))
View Code

 

 

 

//表空间操作

1、查询数据库中的表空间名称

1)查询所有表空间

select tablespace_name from dba_tablespaces; 
select tablespace_name from user_tablespaces; 

2)查询使用过的表空间  

select distinct tablespace_name from dba_all_tables;

select distinct tablespace_name from user_all_tables; 

2、查询表空间中所有表的名称

select table_name from dba_all_tables where tablespace_name = tablespacename

3、查询系统用户

select * from all_users
select * from dba_users

4、查看当前连接用户

select * from v$session

5、查看当前用户权限

select * from session_privs

6、查看所有的函数和存储过程

select * from user_source

其中TYPE包括：PROCEDURE、FUNCTION

7、查看表空间使用情况

select a.file_id "FileNo",
       a.tablespace_name "表空间",
       a.bytes "Bytes",
       a.bytes - sum(nvl(b.bytes, 0)) "已用",
       sum(nvl(b.bytes, 0)) "空闲",
       sum(nvl(b.bytes, 0)) / a.bytes * 100 "空闲百分率"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name;
View Code

 

 

//附录

附录一： 
给用户增加导入数据权限的操作 
第一,启动sql*puls 
第二，以system/manager登陆 
第三，create user 用户名 IDENTIFIED BY 密码 （如果已经创建过用户，这步可以省略） 
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , 
   DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, 
      DBA,CONNECT,RESOURCE,CREATE SESSION  TO 用户名字 
第五, 运行-cmd-进入dmp文件所在的目录, 
      imp userid=system/manager full=y file=*.dmp 
      或者 imp userid=system/manager full=y file=filename.dmp 

执行示例: 
F:/Work/Oracle_Data/backup>imp userid=test/test full=y file=inner_notify.dmp 

屏幕显示 
Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006 
(c) Copyright 2000 Oracle Corporation.  All rights reserved. 

连接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production 
With the Partitioning option 
JServer Release 8.1.7.0.0 - Production 

经由常规路径导出由EXPORT:V08.01.07创建的文件 
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入 
导出服务器使用UTF8 NCHAR 字符集 (可能的ncharset转换) 
. 正在将AICHANNEL的对象导入到 AICHANNEL 
. . 正在导入表                  "INNER_NOTIFY"          4行被导入 
准备启用约束条件... 
成功终止导入，但出现警告。 


附录二： 
Oracle 不允许直接改变表的拥有者, 利用Export/Import可以达到这一目的. 
  先建立import9.par, 
  然后，使用时命令如下：imp parfile=/filepath/import9.par 
  例 import9.par 内容如下： 
        FROMUSER=TGPMS        
        TOUSER=TGPMS2     （注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同）           
        ROWS=Y 
        INDEXES=Y 
        GRANTS=Y 
        CONSTRAINTS=Y 
        BUFFER=409600 
        file==/backup/ctgpc_20030623.dmp 
        log==/backup/import_20030623.log
View Code

 

 

 

/// 补充操作: RunSql

--通过存储过程插入数据
CREATE OR REPLACE PROCEDURE SP_RUNSQL(
       sqlText varchar2
       ,sqlParm varchar2
       ,sqlVal  varchar2
       -- 以下参数为返回信息(不要写长度)
       ,pout_msg out varchar2
)
IS
       v_split char(1) := '¿';
       /*
       v_count calender.itemcount%type;
       v_calender_dateid calender.dateid%type;*/
       
       -- 以下变量用于返回信息
       v_exception exception;
       PARM_VAL_NOT_ exception;
       v_isOk varchar2(5);
       v_msgCode number;
       v_msgText varchar2(4000);
BEGIN
       
        
       --成功执行, 则commit 
        COMMIT;
        v_isOk := 'TRUE';      
        v_msgCode := 200;
        v_msgText := 'OK';
        
        --如果中间有异常, 则rollback
        EXCEPTION
        WHEN v_exception THEN
        BEGIN
            ROLLBACK;
            v_isOk := 'FALSE';
            v_msgCode := -512;
            v_msgText := '自定义的错误信息';
        END;
        WHEN OTHERS THEN
        BEGIN
            -- 未知异常直接抛出
            ROLLBACK;
            v_isOk := 'FALSE';
            v_msgCode := SQLCODE;
            v_msgText := SUBSTR(SQLERRM, 1, 200);
            RAISE_APPLICATION_ERROR(v_msgCode,v_msgText);
        END;
        pout_msg := '['||v_isOk||']'||v_msgCode||':'||v_msgText;
        --DBMS_OUTPUT.PUT_LINE(pout_msg);     
END SP_RUNSQL;


-- 测试执行解雇
DECLARE 
   v_sql varchar2(4000);
   v_parms varchar2(1000);
   v_vals varchar2(1000);
BEGIN
  SP_RUNSQL(v_flag,v_code,v_msg);
  --SP_RUNSQL();
  --DBMS_OUTPUT.PUT_LINE('['||v_flag||']'||v_code||':'||v_msg);
END;
View Code

 

 

////分组取最新的一条

Select kd.CREATEUSERID as userid,kd.LOCATION,kd.createtime as location from KT_DEVICESTRACK kd where rownum=1 order by kd.createtime

SELECT * FROM (
SELECT *,ROWNUM rn FROM t ORDER BY date_col DESC
) WHERE rn = 1

update cg_berthtrace cg 
set cg.chagedtime=sysdate,cg.parkduration=(cg.chagedtime-cg.createtime)*24*60,cg.berthstatus='11'
where cg.berthno in(select po.bowei from pda_order po where po.starttime=(select max(starttime) from pda_order))
View Code

 

 

 

///oracle中exception执行完后, 不继续向下执行的处理方式

ORA-01403:未找到任何数据的问题
begin
--加入匿名块，进行处理 
    begin
      Select   id   Into   newid From   table Where   XXX=XXXX; 
      EXCEPTION   WHEN   NO_Data_Found   THEN
                                    newid :=1; --当捕获到 未找到任何数据 异常是,给newid赋值
    end; 
--你接下来的语句 这里的语句不会因为捕获异常而终止
end;
View Code

 

///游标的使用, for循环方式和fetch方式

-- 声明游标；CURSOR cursor_name IS select_statement

--For 循环游标
--（1）定义游标
--（2）定义游标变量
--（3）使用for循环来使用这个游标
declare
       --类型定义
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_job%rowtype;
begin
       for c_row in c_job loop
         dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
       end loop;
end;


      
--Fetch游标
--使用的时候必须要明确的打开和关闭

declare 
       --类型定义
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义一个游标变量
       c_row c_job%rowtype;
begin
       open c_job;
         loop
           --提取一行数据到c_row
           fetch c_job into c_row;
           --判读是否提取到值，没取到值就退出
           --取到值c_job%notfound 是false 
           --取不到值c_job%notfound 是true
           exit when c_job%notfound;
            dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
         end loop;
       --关闭游标
      close c_job;
end;

--1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
       begin
         update emp set ENAME='ALEARK' WHERE EMPNO=7469;
         if sql%isopen then
           dbms_output.put_line('Openging');
           else
             dbms_output.put_line('closing');
             end if;
          if sql%found then
            dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行
            else
              dbms_output.put_line('Sorry');
              end if;
              if sql%notfound then
                dbms_output.put_line('Also Sorry');
                else
                  dbms_output.put_line('Haha');
                  end if;
                   dbms_output.put_line(sql%rowcount);
                   exception 
                     when no_data_found then
                       dbms_output.put_line('Sorry No data');
                       when too_many_rows then
                         dbms_output.put_line('Too Many rows');
                         end;
declare
       empNumber emp.EMPNO%TYPE;
       empName emp.ENAME%TYPE;
       begin
         if sql%isopen then
           dbms_output.put_line('Cursor is opinging');
           else
             dbms_output.put_line('Cursor is Close');
             end if;
             if sql%notfound then
               dbms_output.put_line('No Value');
               else
                 dbms_output.put_line(empNumber);
                 end if;
                 dbms_output.put_line(sql%rowcount);
                 dbms_output.put_line('-------------');
                 
                 select EMPNO,ENAME into  empNumber,empName from emp where EMPNO=7499;
                 dbms_output.put_line(sql%rowcount);
                 
                if sql%isopen then
                dbms_output.put_line('Cursor is opinging');
                else
                dbms_output.put_line('Cursor is Closing');
                end if;
                 if sql%notfound then
                 dbms_output.put_line('No Value');
                 else
                 dbms_output.put_line(empNumber);
                 end if;
                 exception 
                   when no_data_found then
                     dbms_output.put_line('No Value');
                     when too_many_rows then
                       dbms_output.put_line('too many rows');
                       end;
                   
                 
       
--2,使用游标和loop循环来显示所有部门的名称
--游标声明
declare 
       cursor csr_dept
       is
       --select语句
       select DNAME
       from Depth;
       --指定行指针,这句话应该是指定和csr_dept行类型相同的变量
       row_dept csr_dept%rowtype;
begin
       --for循环
       for row_dept in csr_dept loop
           dbms_output.put_line('部门名称:'||row_dept.DNAME);
       end loop;
end;


--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）
declare
       --游标声明
       cursor csr_TestWhile
       is
       --select语句
       select  LOC
       from Depth;
       --指定行指针
       row_loc csr_TestWhile%rowtype;
begin
  --打开游标
       open csr_TestWhile;
       --给第一行喂数据
       fetch csr_TestWhile into row_loc;
       --测试是否有数据，并执行循环
         while csr_TestWhile%found loop
           dbms_output.put_line('部门地点：'||row_loc.LOC);
           --给下一行喂数据
           fetch csr_TestWhile into row_loc;
         end loop;
       close csr_TestWhile;
end; 
select * from emp



       
--4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  

declare 
      CURSOR 
      c_dept(p_deptNo number)
      is
      select * from emp where emp.depno=p_deptNo;
      r_emp emp%rowtype;
begin
        for r_emp in c_dept(20) loop
            dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
        end loop;
end;
select * from emp   
--5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)
declare 
       cursor
       c_job(p_job nvarchar2)
       is 
       select * from emp where JOB=p_job;
       r_job emp%rowtype;
begin 
       for r_job in c_job('CLERK') loop
           dbms_output.put_line('员工号'||r_job.EMPNO||' '||'员工姓名'||r_job.ENAME);
        end loop;
end;
SELECT * FROM EMP

--6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来 
--http://zheng12tian.iteye.com/blog/815770 
        create table emp1 as select * from emp;
        
declare
        cursor
        csr_Update
        is
        select * from  emp1 for update OF SAL;
        empInfo csr_Update%rowtype;
        saleInfo  emp1.SAL%TYPE;
begin
    FOR empInfo IN csr_Update LOOP
      IF empInfo.SAL<1500 THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000 THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000 THEN
        saleInfo:=empInfo.SAL*2;
      END IF;
      UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
     END LOOP;
END;

--7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
declare 
     cursor
      csr_AddSal
     is
      select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL;
      r_AddSal csr_AddSal%rowtype;
      saleInfo  emp1.SAL%TYPE;
begin
      for r_AddSal in csr_AddSal loop
          dbms_output.put_line(r_AddSal.ENAME||'原来的工资:'||r_AddSal.SAL);
          saleInfo:=r_AddSal.SAL*1.1;
          UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;
      end loop;
end;
--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500
declare
      cursor
          csr_AddComm(p_job nvarchar2)
      is
          select * from emp1 where   JOB=p_job FOR UPDATE OF COMM;
      r_AddComm  emp1%rowtype;
      commInfo emp1.comm%type;
begin
    for r_AddComm in csr_AddComm('SALESMAN') LOOP
        commInfo:=r_AddComm.COMM+500;
         UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;
    END LOOP;
END;

--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）
--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）
declare
    cursor crs_testComput
    is
    select * from emp1 order by HIREDATE asc;
    --计数器
    top_two number:=2;
    r_testComput crs_testComput%rowtype;
begin
    open crs_testComput;
       FETCH crs_testComput INTO r_testComput;
          while top_two>0 loop
             dbms_output.put_line('员工姓名：'||r_testComput.ENAME||' 工作时间：'||r_testComput.HIREDATE);
             --计速器减一
             top_two:=top_two-1;
             FETCH crs_testComput INTO r_testComput;
           end loop;
     close crs_testComput;
end;
    

--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，
--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来) 
declare
    cursor
        crs_UpadateSal
    is
        select * from emp1 for update of SAL;
        r_UpdateSal crs_UpadateSal%rowtype;
        salAdd emp1.sal%type;
        salInfo emp1.sal%type;
begin
        for r_UpdateSal in crs_UpadateSal loop
           salAdd:= r_UpdateSal.SAL*0.2;
           if salAdd>300 then
             salInfo:=r_UpdateSal.SAL;
              dbms_output.put_line(r_UpdateSal.ENAME||':  加薪失败。'||'薪水维持在：'||r_UpdateSal.SAL);
             else 
              salInfo:=r_UpdateSal.SAL+salAdd;
              dbms_output.put_line(r_UpdateSal.ENAME||':  加薪成功.'||'薪水变为：'||salInfo);
           end if;
           update emp1 set SAL=salInfo where current of crs_UpadateSal;
        end loop;
end;
     
--11:将每位员工工作了多少年零多少月零多少天输出出来   
--近似
  --CEIL(n)函数：取大于等于数值n的最小整数
  --FLOOR(n)函数：取小于等于数值n的最大整数
  --truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
declare
  cursor
   crs_WorkDay
   is
   select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,
       trunc(mod(months_between(sysdate, hiredate), 12)) AS months,
       trunc(mod(mod(sysdate - hiredate, 365), 12)) as days
   from emp1;
  r_WorkDay crs_WorkDay%rowtype;
begin
    for   r_WorkDay in crs_WorkDay loop
    dbms_output.put_line(r_WorkDay.ENAME||'已经工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天');
    end loop;
end;
  
--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来
--  deptno  raise(%)
--  10      5%
--  20      10%
--  30      15%
--  40      20%
--  加薪比例以现有的sal为标准
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
     cursor
         crs_caseTest
          is
          select * from emp1 for update of SAL;
          r_caseTest crs_caseTest%rowtype;
          salInfo emp1.sal%type;
     begin
         for r_caseTest in crs_caseTest loop
         case 
           when r_caseTest.DEPNO=10
           THEN salInfo:=r_caseTest.SAL*1.05;
           when r_caseTest.DEPNO=20
           THEN salInfo:=r_caseTest.SAL*1.1;
           when r_caseTest.DEPNO=30
           THEN salInfo:=r_caseTest.SAL*1.15;
            when r_caseTest.DEPNO=40
           THEN salInfo:=r_caseTest.SAL*1.2;
         end case;
          update emp1 set SAL=salInfo where current of crs_caseTest;
        end loop;
end;

--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。
--AVG([distinct|all] expr) over (analytic_clause)
---作用：
--按照analytic_clause中的规则求分组平均值。
  --分析函数语法:
  --FUNCTION_NAME(<argument>,<argument>...)
  --OVER
  --(<Partition-Clause><Order-by-Clause><Windowing Clause>)
     --PARTITION子句
     --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
     select * from emp1
DECLARE
     CURSOR 
     crs_testAvg
     IS
     select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG
     FROM EMP1 for update of SAL;
     r_testAvg crs_testAvg%rowtype;
     salInfo emp1.sal%type;
     begin
     for r_testAvg in crs_testAvg loop
     if r_testAvg.SAL>r_testAvg.DEP_AVG then
     salInfo:=r_testAvg.SAL-50;
     end if;
     update emp1 set SAL=salInfo where current of crs_testAvg;
     end loop;
end;
View Code

 

 

///oracle触发器, 对本表的操作:

oracle触发器中增删改查本表
 
(1)只有before insert触发器中才可以查询或更新本表
create or replace trigger tri_test_ins
before insert
on test
for each row
declare
v_cnt integer ;
begin
select count (*) into v_cnt from test;
dbms_output.put_line( 'test count:' ||to_char(v_cnt));
update test set a9= '99' ;
end ;
View Code
执行insert后，只有当前插入的记录值不是99，其它的记录都被更新成了99。
(2)before/after update、before/after delete、after insert5种情况都不可以查询或更新本表。
(3)使用自治事务可以实现任意触发器查本表。但不能实现在自治事务中更新本表。
查询本表的情况是最常见的。
create or replace trigger tri_test_ins
after update
on test
for each row
declare
v_cnt integer ;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
begin
select count (*) into v_cnt from test;
dbms_output.put_line( 'test count:' ||to_char(v_cnt));
end ;
end ;
View Code
(4)使用自治事务可以实现新增或删除本表的记录。这种情况一般不会用到。

 

其他回答:

oracle 触发器.
监听表A 更新 更新后触发.
其中 使用游标查询表A 数据.
OPEN 游标时
会报 ORA-04091：表A发生了变化，触发器/函数不能读它

于是增加了自定义事物 pragma autonomous_transaction; 
增加事物之后不报错了.但是OPEN 的游标 没有获取到任何数据.

想做到的效果是

是这样. 比如说表A里面有4条数据
1 aaaa
2 bbbb
3 cccc
4 dddd

完后在修改其中一条数据之后. 查询其余没有修改的3条数据.在插入到表B中.

所以这里我想查询触发器监听的表.

我应该怎么处理?有什么好的建议.我刚才尝试了一下勇士图 依然会同样报错

-----------------------------------------

或者参考一下这个

相信写过ORACLE行级触发器的IT同仁们大多遇到过ORA-04091问题，即在某表的行级触发器中不能读取当前表的问题，如：

create table test(id raw(16), name varchar2(100), primary key (id));
create table test_count(test_count int);
insert into test_count values(0);
commit;
create or replace trigger t_test
AFTER INSERT OR DELETE ON test
FOR EACH ROW
BEGIN
UPDATE test_count
SET test_count = (SELECT count(*) from test);
END t_test;
View Code

当您在插入test表时，系统会抱怨（当然计数这样的简单业务是不需要使用触发器来做的，仅用于举例）：

第 1 行出现错误:
RA-04091: 表 TEST.TEST 发生了变化, 触发器/函数不能读它
RA-06512: 在 "TEST.T_TEST", line 2
RA-04088: 触发器 'TEST.T_TEST' 执行过程中出错

前几天看到触发器的INSTEAD OF子句，顺便用它搞定：

drop trigger t_test;
create view v_test as select id, name from test;
create or replace trigger t_v_test
INSTEAD OF INSERT OR DELETE OR UPDATE ON v_test
FOR EACH ROW
DECLARE
BEGIN
IF inserting THEN
INSERT INTO test(id, name) values(:new.id, :new.name);
END IF;
IF deleting THEN
DELETE FROM test WHERE id = :old.id;
END IF;
IF updating THEN
UPDATE test
SET id = :new.id, name = :new.name
WHERE id = :old.id;
ELSE
UPDATE test_count
SET test_count = (SELECT count(*) from test);
END IF;
END t_v_test;
View Code

原来对test表的插入改为对v_test插入，一切OK，搞定。

-----------------------

ORACLE的触发器规则中，不能读取已经发生变更但没有提交事务的数据，修改前的内容使用 old.field 获取，修改后的值使用 new.field 获取,其中 field 是你的字段名

行级触发器是不允许select自身的，需要表级触发器才可以引用自身，建议你换个方法来实现

 

 

//oracle中通过存储过程返回表数据

方法之一:

-- 定义类型bai
CREATE OR REPLACE TYPE MyTable AS OBJECT(A int, B int, C int);
/
CREATE OR REPLACE TYPE MyTableResult IS TABLE OF MyTable;
/
CREATE OR REPLACE FUNCTION getTestTable return MyTableResult
IS
  -- 预期返回结果.
  return_Result  MyTableResult := MyTableResult();
BEGIN
  -- 结果追加一行.
  return_Result.EXTEND;
  -- 设置结果内容.
  return_Result(return_Result.COUNT) := MyTable(A   => 1, B=>2, C=>3);
  -- 结果追加一行.
  return_Result.EXTEND;
  -- 设置结果内容.
  return_Result(return_Result.COUNT) := MyTable(A   => 4, B=>5, C=>6);
  -- 结果追加一行.
  return_Result.EXTEND;
  -- 设置结果内容.
  return_Result(return_Result.COUNT) := MyTable(A   => 7, B=>8, C=>9);
  return return_Result;
END getTestTable;
/
SQL> SELECT
  2    A, B, C
  3  FROM
  4   table( getTestTable() );
         A          B          C
---------- ---------- ----------
         1          2          3
         4          5          6
         7          8          9
SQL> 
View Code

 

 

//oracle中的3种if和else判断

oracle中if/else功能的实现的3种写法
 
1、标准sql规范

复制代码
复制代码
一、单个IF
1、

if a=...  then
.........
end if;

2、

if a=... then
......
else
....
end if;

二、多个IF

if a=..  then
......
elsif a=..  then
....
end if;     
这里中间是“ELSIF”，而不是ELSE IF 。这里需要特别注意
复制代码
复制代码
 

2、decode函数

DECODE的语法：

DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。


3、case when

case when a='1'then 'xxxx'
     when a='2' then 'ssss'
else
　　'zzzzz'
end as
注意点： 

1、以CASE开头，以END结尾 
2、分支中WHEN 后跟条件，THEN为显示结果 
3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加 
4、END 后跟别名
View Code

 

 

//oracle中存储过程使用说明

-- 声明游标；CURSOR cursor_name IS select_statement

--For 循环游标
--（1）定义游标
--（2）定义游标变量
--（3）使用for循环来使用这个游标
declare
       --类型定义
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_job%rowtype;
begin
       for c_row in c_job loop
         dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
       end loop;
end;


      
--Fetch游标
--使用的时候必须要明确的打开和关闭

declare 
       --类型定义
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定义一个游标变量
       c_row c_job%rowtype;
begin
       open c_job;
         loop
           --提取一行数据到c_row
           fetch c_job into c_row;
           --判读是否提取到值，没取到值就退出
           --取到值c_job%notfound 是false 
           --取不到值c_job%notfound 是true
           exit when c_job%notfound;
            dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
         end loop;
       --关闭游标
      close c_job;
end;

--1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况。
       begin
         update emp set ENAME='ALEARK' WHERE EMPNO=7469;
         if sql%isopen then
           dbms_output.put_line('Openging');
           else
             dbms_output.put_line('closing');
             end if;
          if sql%found then
            dbms_output.put_line('游标指向了有效行');--判断游标是否指向有效行
            else
              dbms_output.put_line('Sorry');
              end if;
              if sql%notfound then
                dbms_output.put_line('Also Sorry');
                else
                  dbms_output.put_line('Haha');
                  end if;
                   dbms_output.put_line(sql%rowcount);
                   exception 
                     when no_data_found then
                       dbms_output.put_line('Sorry No data');
                       when too_many_rows then
                         dbms_output.put_line('Too Many rows');
                         end;
declare
       empNumber emp.EMPNO%TYPE;
       empName emp.ENAME%TYPE;
       begin
         if sql%isopen then
           dbms_output.put_line('Cursor is opinging');
           else
             dbms_output.put_line('Cursor is Close');
             end if;
             if sql%notfound then
               dbms_output.put_line('No Value');
               else
                 dbms_output.put_line(empNumber);
                 end if;
                 dbms_output.put_line(sql%rowcount);
                 dbms_output.put_line('-------------');
                 
                 select EMPNO,ENAME into  empNumber,empName from emp where EMPNO=7499;
                 dbms_output.put_line(sql%rowcount);
                 
                if sql%isopen then
                dbms_output.put_line('Cursor is opinging');
                else
                dbms_output.put_line('Cursor is Closing');
                end if;
                 if sql%notfound then
                 dbms_output.put_line('No Value');
                 else
                 dbms_output.put_line(empNumber);
                 end if;
                 exception 
                   when no_data_found then
                     dbms_output.put_line('No Value');
                     when too_many_rows then
                       dbms_output.put_line('too many rows');
                       end;
                   
                 
       
--2,使用游标和loop循环来显示所有部门的名称
--游标声明
declare 
       cursor csr_dept
       is
       --select语句
       select DNAME
       from Depth;
       --指定行指针,这句话应该是指定和csr_dept行类型相同的变量
       row_dept csr_dept%rowtype;
begin
       --for循环
       for row_dept in csr_dept loop
           dbms_output.put_line('部门名称:'||row_dept.DNAME);
       end loop;
end;


--3,使用游标和while循环来显示所有部门的的地理位置（用%found属性）
declare
       --游标声明
       cursor csr_TestWhile
       is
       --select语句
       select  LOC
       from Depth;
       --指定行指针
       row_loc csr_TestWhile%rowtype;
begin
  --打开游标
       open csr_TestWhile;
       --给第一行喂数据
       fetch csr_TestWhile into row_loc;
       --测试是否有数据，并执行循环
         while csr_TestWhile%found loop
           dbms_output.put_line('部门地点：'||row_loc.LOC);
           --给下一行喂数据
           fetch csr_TestWhile into row_loc;
         end loop;
       close csr_TestWhile;
end; 
select * from emp



       
--4,接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息(使用循环游标)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--定义参数的语法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  

declare 
      CURSOR 
      c_dept(p_deptNo number)
      is
      select * from emp where emp.depno=p_deptNo;
      r_emp emp%rowtype;
begin
        for r_emp in c_dept(20) loop
            dbms_output.put_line('员工号：'||r_emp.EMPNO||'员工名：'||r_emp.ENAME||'工资：'||r_emp.SAL);
        end loop;
end;
select * from emp   
--5：向游标传递一个工种，显示此工种的所有雇员的所有信息(使用参数游标)
declare 
       cursor
       c_job(p_job nvarchar2)
       is 
       select * from emp where JOB=p_job;
       r_job emp%rowtype;
begin 
       for r_job in c_job('CLERK') loop
           dbms_output.put_line('员工号'||r_job.EMPNO||' '||'员工姓名'||r_job.ENAME);
        end loop;
end;
SELECT * FROM EMP

--6：用更新游标来为雇员加佣金：(用if实现,创建一个与emp表一摸一样的emp1表，对emp1表进行修改操作),并将更新前后的数据输出出来 
--http://zheng12tian.iteye.com/blog/815770 
        create table emp1 as select * from emp;
        
declare
        cursor
        csr_Update
        is
        select * from  emp1 for update OF SAL;
        empInfo csr_Update%rowtype;
        saleInfo  emp1.SAL%TYPE;
begin
    FOR empInfo IN csr_Update LOOP
      IF empInfo.SAL<1500 THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000 THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000 THEN
        saleInfo:=empInfo.SAL*2;
      END IF;
      UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
     END LOOP;
END;

--7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
declare 
     cursor
      csr_AddSal
     is
      select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL;
      r_AddSal csr_AddSal%rowtype;
      saleInfo  emp1.SAL%TYPE;
begin
      for r_AddSal in csr_AddSal loop
          dbms_output.put_line(r_AddSal.ENAME||'原来的工资:'||r_AddSal.SAL);
          saleInfo:=r_AddSal.SAL*1.1;
          UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;
      end loop;
end;
--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500
declare
      cursor
          csr_AddComm(p_job nvarchar2)
      is
          select * from emp1 where   JOB=p_job FOR UPDATE OF COMM;
      r_AddComm  emp1%rowtype;
      commInfo emp1.comm%type;
begin
    for r_AddComm in csr_AddComm('SALESMAN') LOOP
        commInfo:=r_AddComm.COMM+500;
         UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;
    END LOOP;
END;

--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）
--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）
declare
    cursor crs_testComput
    is
    select * from emp1 order by HIREDATE asc;
    --计数器
    top_two number:=2;
    r_testComput crs_testComput%rowtype;
begin
    open crs_testComput;
       FETCH crs_testComput INTO r_testComput;
          while top_two>0 loop
             dbms_output.put_line('员工姓名：'||r_testComput.ENAME||' 工作时间：'||r_testComput.HIREDATE);
             --计速器减一
             top_two:=top_two-1;
             FETCH crs_testComput INTO r_testComput;
           end loop;
     close crs_testComput;
end;
    

--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，
--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来) 
declare
    cursor
        crs_UpadateSal
    is
        select * from emp1 for update of SAL;
        r_UpdateSal crs_UpadateSal%rowtype;
        salAdd emp1.sal%type;
        salInfo emp1.sal%type;
begin
        for r_UpdateSal in crs_UpadateSal loop
           salAdd:= r_UpdateSal.SAL*0.2;
           if salAdd>300 then
             salInfo:=r_UpdateSal.SAL;
              dbms_output.put_line(r_UpdateSal.ENAME||':  加薪失败。'||'薪水维持在：'||r_UpdateSal.SAL);
             else 
              salInfo:=r_UpdateSal.SAL+salAdd;
              dbms_output.put_line(r_UpdateSal.ENAME||':  加薪成功.'||'薪水变为：'||salInfo);
           end if;
           update emp1 set SAL=salInfo where current of crs_UpadateSal;
        end loop;
end;
     
--11:将每位员工工作了多少年零多少月零多少天输出出来   
--近似
  --CEIL(n)函数：取大于等于数值n的最小整数
  --FLOOR(n)函数：取小于等于数值n的最大整数
  --truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
declare
  cursor
   crs_WorkDay
   is
   select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,
       trunc(mod(months_between(sysdate, hiredate), 12)) AS months,
       trunc(mod(mod(sysdate - hiredate, 365), 12)) as days
   from emp1;
  r_WorkDay crs_WorkDay%rowtype;
begin
    for   r_WorkDay in crs_WorkDay loop
    dbms_output.put_line(r_WorkDay.ENAME||'已经工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天');
    end loop;
end;
  
--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来
--  deptno  raise(%)
--  10      5%
--  20      10%
--  30      15%
--  40      20%
--  加薪比例以现有的sal为标准
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
     cursor
         crs_caseTest
          is
          select * from emp1 for update of SAL;
          r_caseTest crs_caseTest%rowtype;
          salInfo emp1.sal%type;
     begin
         for r_caseTest in crs_caseTest loop
         case 
           when r_caseTest.DEPNO=10
           THEN salInfo:=r_caseTest.SAL*1.05;
           when r_caseTest.DEPNO=20
           THEN salInfo:=r_caseTest.SAL*1.1;
           when r_caseTest.DEPNO=30
           THEN salInfo:=r_caseTest.SAL*1.15;
            when r_caseTest.DEPNO=40
           THEN salInfo:=r_caseTest.SAL*1.2;
         end case;
          update emp1 set SAL=salInfo where current of crs_caseTest;
        end loop;
end;

--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。
--AVG([distinct|all] expr) over (analytic_clause)
---作用：
--按照analytic_clause中的规则求分组平均值。
  --分析函数语法:
  --FUNCTION_NAME(<argument>,<argument>...)
  --OVER
  --(<Partition-Clause><Order-by-Clause><Windowing Clause>)
     --PARTITION子句
     --按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
     select * from emp1
DECLARE
     CURSOR 
     crs_testAvg
     IS
     select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG
     FROM EMP1 for update of SAL;
     r_testAvg crs_testAvg%rowtype;
     salInfo emp1.sal%type;
     begin
     for r_testAvg in crs_testAvg loop
     if r_testAvg.SAL>r_testAvg.DEP_AVG then
     salInfo:=r_testAvg.SAL-50;
     end if;
     update emp1 set SAL=salInfo where current of crs_testAvg;
     end loop;
end;
View Code

 

//ORACLE生成UUID的函数 和 根据起始日期获取一天的SQL

--生成UUID的函数
CREATE OR REPLACE FUNCTION FN_UUID
       RETURN VARCHAR
IS
       guid VARCHAR (50);
BEGIN
       guid := lower(RAWTOHEX(sys_guid()));
       RETURN substr(guid,1,8)||'-'||substr(guid,9,4)||'-'||substr(guid,13,4)||'-'||substr(guid,17,4)||'-'||substr(guid,21,12);
END;
--生成UUID的函数---END

--生成连续日期的函数
--创建返回表对象
CREATE OR REPLACE TYPE rtn_table IS TABLE OF obj_table;
--创建表结构对象
CREATE OR REPLACE TYPE obj_table AS OBJECT(
       CONTIDATE CHAR(10)
);
--创建函数
CREATE OR REPLACE FUNCTION FN_CONTIDATE(
       p_STARTDATE CHAR
       ,p_ENDDATE CHAR
)
RETURN rtn_table PIPELINED
IS 
       v_obj_tab obj_table;
BEGIN
       for i in (SELECT to_char(to_date(p_STARTDATE, 'yyyy-mm-dd') + rownum - 1,'yyyy-mm-dd') v_date
                 FROM all_tables
                 WHERE rownum < (to_date(p_ENDDATE,'yyyy-mm-dd') - to_date(p_STARTDATE,'yyyy-mm-dd') + 2)
       ) LOOP
           v_obj_tab := obj_table(i.v_date);
           pipe row(v_obj_tab);
           --DBMS_OUTPUT.PUT_LINE(i.v_date);
       END LOOP;
       RETURN;
END;
--测试函数
--select * from table(FN_CONTIDATE('2021-03-01','2021-03-23'))
--生成连续日期的函数---END
View Code

 

//Oracle查询某周的日期分别是几号,有两种做法, 参考自: https://blog.csdn.net/qq_33459369/article/details/80305175

//方法一:
select v_date,to_char( v_date, 'day') day
  from (select (to_date('201801', 'yyyymm') + rownum - 1) v_date
          from all_tables
         where rownum < 370)
 where to_char(v_date, 'yyyy-ww') = '2018-01';


//方法二:
select min_date, to_char(min_date, 'day') day
  from (select to_date(substr('2018-01', 1, 4) || '001' + rownum - 1,
                       'yyyyddd') min_date
          from all_tables
         where rownum <= decode(mod(to_number(substr('2018-01', 1, 4)), 4),
                                0,
                                366,
                                365)
        union
        select to_date(substr('2018-01', 1, 4) - 1 ||
                       decode(mod(to_number(substr('2018-01', 1, 4)) - 1, 4),
                              0,
                              359,
                              358) + rownum,
                       'yyyyddd') min_date
          from all_tables
         where rownum <= 7
        union
        select to_date(substr('2018-01', 1, 4) + 1 || '001' + rownum - 1,
                       'yyyyddd') min_date
          from all_tables
         where rownum <= 7)
 where to_char(min_date, 'yyyy-ww') = '2018-01';
View Code

 

//oralce 两天相隔的天数

select to_date('19930411','yyyymmdd')-to_date('19890507','yyyymmdd') from dual;
View Code

 

//oracle 返回表的表值函数,

需要: 1. 先定义返回表结构类型。 2. 定义表对象类型。3. 创建演示函数，有两种返回做法：（管道函数、普通函数）。

调用语句是：select * from table(函数名(参数1,参数2));

示例1:

--1. 创建成返回表结构 类型对象
create or replace type obj_table as object ( 
       id int, 
       name varchar2(50) 
); 
--2. 创建返回表 类型对象
create or replace type t_table is table of number; 

--3. 创建表值演示函数
--3.1 管道化返回函数:
create or replace function f_pipe(
       s number
) 
return t_table pipelined 
as 
       v_obj_table obj_table; 
begin 
       for i in 1..5 loop 
       v_obj_table := obj_table(i,to_char(i*i)); 
       pipe row(v_obj_table); 
       end loop; 
       return; --这句必须写
end f_pipe; 


--3.2 普通返回函数:
create or replace function f_normal(
       s number
) 
return t_table 
as 
       rs t_table:= t_table(); 
begin 
       for i in 1..5 loop 
       rs.extend; 
       rs(rs.count) := obj_table(rs.count,'name'||to_char(rs.count)); 
       --rs(rs.count).name := rs(rs.count).name || 'xxxx'; 
       end loop; 
       return rs; 
end f_normal; 


--4 调用测试
select * from table(f_normal(5));
View Code

 

//示例2(参考一下):

create or replace type obj_table as object
(
      id varchar2(38),
      name varchar2(4000),
      count varchar2(10)
);

create or replace type t_table is table of obj_table;


create or replace function getStationListTb(s number)
return t_table
as
    rs t_table:= t_table();
    str varchar2(4000);
    i number := 0;
begin
    for c in (select DepartmentId from sys_department) loop
    str := ‘‘;
    i := 0;
    for c2 in (select t2.name from sys_departmentstation t1
               left join bsd_station t2 on t1.stationid=t2.stationid where t1.DepartmentId=c.departmentid) loop
      str := str || ‘<span class="btn btn-defaut select-btn select-site-wrap" title="‘ || c2.name ||  ‘">‘ || c2.name ||  ‘</span>‘;
      i := i + 1;
    end loop;
    str := substr(str,0,length(str)-1);
    rs.extend;
    rs(rs.count) := obj_table(c.departmentid,str,to_char(i));
  end loop;
return rs;
end getStationListTb;


select * from table(getStationListTb(1))


-- 另外一个函数
SELECT t1.*,t2.name StationList，t2.count StationCount FROM SYS_Department t1
      left join (select * from table(getStationListTb(1))) t2 on t1.DepartmentId=t2.id
      WHERE 1=1




create or replace function getStationList(DepartmentId  VARCHAR2) return VARCHAR2
    as
      --定义变量
      resultStr VARCHAR2(8000);
    begin
      for c in (select stationid
                  from sys_departmentstation
                  where DepartmentId=DepartmentId) loop
           resultStr := resultStr || c.stationid || ‘,‘;
       end loop;
      return substr(resultStr,0,length(resultStr)-1);
    end getStationList;



SELECT t1.*,getStationList(t1.DepartmentId) StationList FROM SYS_Department t1
View Code

 

//使用conect by 做结构化查询语句

select CONNECT_BY_ISLEAF AS IS_LEAF, level,ba.*
from bd_areacl ba
-- where pk_fatherarea = '~'
--where code like '1%' or code = 'CN'
start with pk_fatherarea = '~'
CONNECT BY PRIOR pk_areacl = pk_fatherarea;
View Code

 

//wm_concat、decode、coalesce函数的使用

 

----多条数据合并在一行显示
wm_concat(to_char(u.user_name)) as user_name       
wm_concat(to_char(u.id)) as org_user_id
----  id 为空就显示类型为个体，不为空就显示相对应的类型
decode(bob.id,'','个体',sd.name) as business_type
---- egal_person, manage_name  显示不为空的那个那    
COALESCE(bob.legal_person,e.manage_name) as legal_person
View Code

 

//oracle创建用户及表空间

Oracle创建用户和表空间
2019.04.01 10:03 1074浏览


在system下创建表空间：

create tablespace CASETABLE 
datafile 'D:\app\CASETABLE.dbf' size 1024M --存储地址 初始大小1G
autoextend on next 1024M maxsize unlimited   --每次扩展1G，无限制扩展
EXTENT MANAGEMENT local  autoallocate
segment space management auto;

在system下创建用户

-- 创建用户
  create user ONECASE
  default tablespace CASETABLE
  temporary tablespace TEMP 
  IDENTIFIED BY 123
  profile DEFAULT;

-- 给用户授权
-- Grant/Revoke role privileges 
  grant connect to ONECASE;
  grant dba to ONECASE;
-- Grant/Revoke system privileges 
   grant create database link to ONECASE;
   grant unlimited tablespace to ONECASE;

在ONECASE用户下创建用户信息表

-- Create table
create table case_user
(
  username VARCHAR2(32) not null,
  password  VARCHAR2(32) not null
)
tablespace CASETABLE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table case_user
  is '用户登入信息表';
-- Add comments to the columns 
comment on column case_user.username
  is '用户名';
comment on column case_user.password
  is '密码';
-- Create/Recreate primary, unique and foreign key constraints 
alter table case_user
  add constraint PK_LAS_CAMERABARCODE primary key (username)
  using index 
  tablespace CASETABLE
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
View Code

 

// 模仿雪花算法的伪字符串ID生成, 长度：20位，规则为：【14位时间戳（毫秒） + 2位服务器IP的主机号 + 4位随机字符】，连续插入5w条记录偶有重复。

如果随机字符长度放到6位，连续插入1000w记录偶有重复。

-- 测试1000w数据不重复
--生成20位主键的函数
CREATE OR REPLACE FUNCTION FN_KEY
       RETURN CHAR
IS
       ipstr varchar(32);
BEGIN
       --年,月,日,时,分,秒,毫秒14位16进制数,IP从右侧2位16进制数,4为随机数的16进制值(256~4095)或者4为随机字母
       --最小select to_char(19700101000000001,'XXXXXXXXXXXXXX') from dual;
       --正常select to_char(20220507202251766,'XXXXXXXXXXXXXX') from dual;
       --最大select to_char(69991231235959999,'XXXXXXXXXXXXXX') from dual;
       ipstr := UTL_INADDR.get_host_address();
       /*
        declare ipstr varchar(32);
        begin
          ipstr := '192.168.11.255';
          --ipstr := UTL_INADDR.get_host_address();
          -- Dbms_Output.put_line(ipstr);
          
          dbms_output.put_line(
                case when INSTR(ipstr,'%')>0 
                      then upper(substr(substr(ipstr,0,INSTR(ipstr,'%')-1),-3))
                 else substr(substr('0'||TRIM(to_char(substr(ipstr,instr(ipstr,'.',-1,2)+1,instr(ipstr,'.',-1)-instr(ipstr,'.',-1,2)-1),'XX')),-2)||substr('0'||TRIM(to_char(substr(ipstr,instr(ipstr,'.',-1)+1),'XX')),-2),-3)
                 end
           );
           
        end;*/
        --1000w不重复
       --RETURN substr(to_char(to_char(current_timestamp,'yyyymmddhh24missff3'),'XXXXXXXXXXXXXX'),-14)||substr(dbms_random.string('X',2),-2)||substr(dbms_random.string('X',4),-4) ;
       --RETURN substr(to_char(to_char(current_timestamp,'yyyymmddhh24missff3'),'XXXXXXXXXXXXXX'),-14)||substr(to_char(floor(dbms_random.value(1048576,16777215)),'XXXXXX'),-6);
       -- 5w条不重复
       RETURN substr(to_char(to_char(current_timestamp,'yyyymmddhh24missff3'),'XXXXXXXXXXXXXX'),-14)
        ||case when INSTR(ipstr,'%')>0 
               then upper(substr(substr(ipstr,0,INSTR(ipstr,'%')-1),-2))
               else substr(substr('0'||TRIM(to_char(substr(ipstr,instr(ipstr,'.',-1)+1),'XX')),-2),-2)
          end
        ||substr(dbms_random.string('X',1),-1)
        ||substr(dbms_random.string('X',3),-3);
      /*
       RETURN substr(to_char(to_char(current_timestamp,'yyyymmddhh24missff3'),'XXXXXXXXXXXXXX'),-14)
        ||case when INSTR(ipstr,'%')>0 
               then upper(substr(substr(ipstr,0,INSTR(ipstr,'%')-1),-2))
               else substr(substr('0'||TRIM(to_char(substr(ipstr,instr(ipstr,'.',-1)+1),'XX')),-2),-2)
          end
        ||substr(to_char(floor(dbms_random.value(4096,65535)),'XXXX'),-4);*/
END;
-- 测试语句
--select FN_KEY() from dual;
/*
declare x number;
begin
  x := 0;
  while x < 50000 LOOP
    x := x+1;
    --INSERT INTO testtab value(FN_KEY(),current_timestamp);
    INSERT INTO testtab
    SELECT FN_KEY(),to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss:ff3') from dual;
    --DBMS_OUTPUT.put_line('1');
  END LOOP;
end;
--truncate table testtab;
select * from testtab;
commit;
*/
-- 解决函数中没有ACL访问控制权限的问题
-- 1. 查询用户是否分配ACL权限
--select * from dba_network_acls;
--select * from dba_network_acl_privileges ;
-- 2. 创建acl文件(若没有的话)
/*
begin
  dbms_network_acl_admin.create_acl(
     acl         => 'UTL_INADDR.xml',                                 -- 命名 ,命名禁止和系统的相同，和系统的相同会把系统数据删掉，造成acl无法使用
     description => '自定义函数中需要获取IP地址来生成唯一ID',           -- 描述
     principal   => 'NC20220417',                                      -- 执行存储过程的用户
     is_grant    => TRUE,                                               -- true表示赋权，false表示取消赋权
     privilege   => 'resolve',                                          -- 权限限制
     start_date  => NULL,                                               --sysdate,
     end_date    => NULL);
  commit;                                              --add_months(sysdate,120)
end;

-- SELECT any_path FROM resource_view WHERE any_path like '/sys/acls/%.xml';
*/
-- 3. 若有的话直接在ACL中添加权限
/*
begin
  dbms_network_acl_admin.add_privilege(
     acl         => 'UTL_INADDR.xml',                                     -- 命名 ,命名禁止和系统的相同，和系统的相同会把系统数据删掉，造成acl无法使用
     principal   => 'NC20220417',                                       -- 要赋权限的用户
     is_grant    => TRUE,                                               -- true表示赋权，false表示取消赋权
     privilege   => 'connect',                                         -- 权限限制privilege   => 'connect')
     start_date  => null,
     end_date    => null);
  commit;
end;

*/
-- 4. 分配地址,端口
/*
begin
  dbms_network_acl_admin.assign_acl(
     acl         => 'UTL_INADDR.xml',                                     -- 命名 ,命名禁止和系统的相同，和系统的相同会把系统数据删掉，造成acl无法使用
     host        => '*'
  );  
  commit;                                                                    -- 权限限制
end;
*/

-- 5. 补充:删除用户权限及配置文件
/*
begin
  dbms_network_acl_admin.unassign_acl(
     acl         => 'UTL_INADDR.xml',                                     -- 命名 ,命名禁止和系统的相同，和系统的相同会把系统数据删掉，造成acl无法使用
     host        => '*'
  );                                              -- 权限限制
  commit;
end;
begin
  dbms_network_acl_admin.delete_privilege(
     'UTL_INADDR.xml',                                     -- 命名 ,命名禁止和系统的相同，和系统的相同会把系统数据删掉，造成acl无法使用
     'NC20220417',                                       -- 要赋权限的用户
     NULL,                                               -- true表示赋权，false表示取消赋权
     'connect'
  ); 
  commit;                                             -- 权限限制
end;
begin
  dbms_network_acl_admin.drop_acl(
     'UTL_INADDR.xml');
  commit;
end;

*/
View Code

 

posted on 2010-11-08 02:32  RJ  阅读(1104)  评论(1)  编辑  收藏  举报

导航