Oracle 温故
来源：互联网 发布：单机版数据库 编辑：程序博客网 时间：2024/04/29 13:43
create user test identified by a123456 default tablespace SYSTEM --创建 用户
___________+++++++++++++++++++++++++++++++++++++++_+_++++++++++++++++++++++++++++++++
oracle的参数传递 案例如下:
string strSql = "select t.exmaster_id from emr_cp_exec_master t where inp_no =:inp_no and not exists(select exmaster_id from emr_cp_exec_out t2 where t2.inp_no = t.inp_no and t2.exmaster_id = t.exmaster_id)";
OracleParameter[] param = new OracleParameter[1];
param[0] = new OracleParameter("inp_no", OracleType.VarChar);
param[0].Value = inp_no;
object obj = DALUse.GetSingle(strSql, param);
return obj == null ? 0 : Convert.ToInt32(obj);
解析: where条件后跟的"inp_in=:inp_in" 在里面的冒号 就是传参的关键字符;
传参的过程 :
OracleParameter[] param = new OracleParameter[1]; //一个参数 数组
param[0] = new OracleParameter("inp_no", OracleType.VarChar); //参数的列名和 类型 如":inp_no 和类型 Varchar
param[0].Value = inp_no; //参数的赋值
object obj = DALUse.GetSingle(strSql, param); //代入数据访问层方法 返回结果
***********************************************************************+++++___________
函数
lpad() 向左补全字符串 rpad()向右补全字符串
lower()小写 upper()大写
initcap()单词手写字母大写 length() 长度
substr()截取字符串 instr()获取字符串出现的位置 ltrim()删
除字符串左边空格 rtrim()删除字符串右边的空格 删除两侧的空格 concat() 串联字符串 翻译字符串translate() 反转字符串 reverse()
数值类型的处理函数
abs() 绝对值 四舍五入 round() 向上取整ceil() 向下取整floor() 去摸mod() 截取数字trunc() 返回数字正负性sign() 平方根sqrt() 乘方power()
获取当前时间 sysdate()
在日期上加月份add_months()
返回特定日期的最后一天 last_day()
返回两个月份之间的差值months_between()
截取日期 trunc()
返回特定日期一周后的日期next_day()
返回当前会话日期 current_date() 放回当前会话时区的时间截
返回某个时间域extract()
转换字符串 to_char()
多值判断decode()
extract (Miniute from sysdate )
空值处理nvl() "如: nvl('a','b') 判断a是否为零 如果为零则 返回值为b 否则 a"
结果集的行号 rownum()
强制转换数据类型 cast()
序列 关键字sequence 语法 create sequence 序列名称 重要属性 currval(获取当前值) 和nextval ()获取下一个值
drop 进行删除
rownum=x "x表示行数" 和top类似 写法不同 它写在 where条件中
**********自定义函数的调用 语法 select 方法 from dual ;
********字符串拼接用到 "||" 如: 在一个数字后加上"%" 写法 为: 数字||"%"
___创建函数 如:加法
create or replace function add(a integer,b integer)
return number
is c number;
begin
c:=a+b;
return c;
end add;
---调用 方法 如: select add(1,2) from dual; return 3;
---序列
创建 一个自增长为一的序列
create sequence x nocycle
调用时 用到 它的两个伪列 crruval 和 nextval
如:创建表a 的b 列
create table a ( b integer );
insert a values(x.crruval) ; 添加
定义多项的序列
create sequence test
statr with 10 increment by 5 从10开始 自增长的数值为5
minvalue 10 maxvalue 20 定义最大值和最小值
cycle cache 2 order; 默认缓存
------------创建一个语句块 如: 根据高和面积 求 宽
declare
width integer ;
height integer :=2;
area integer;
begin
area:=6;
width :=area/height;
dbms_output.put_line('width='||width);
exception --异常处理
when zero_divide then
dbms_output.put_line('Division by zero');
end;
------------
_________________________________________________________________
使用%type 对表里的列进行声明变量的 类型(自动声明为正确的类型)
如: product 表中
declare
v_product products.product_id%type;
_____________________________________________
______________定义游标 关键字cursor
定义:可以理解为一次访问一个组记录.
>声明一些变量 用于保存select语句返回的列值 --declare v_product_id products.product_id%type;
>声明游标 并指定select 语句 -- cursor cv_cursor is select product_id from products order by product_id ;
begin
>打开游标 -- open cv_cursor;
>从游标中获取记录 -- loop --循环
-- fetch cv_cursor into v_product_id;
-- exit when cv_cursor%notfound --如果没找到就退出
dbms_output.put_line('v_product_id'||v_product_id);
--end loop;4820
>关闭游标\ -- close cv_cursor;
______________________________________________________
___________________存储过程
create or replace procedure proc_add_unit --创建存储的关键字procedure
(
dwdm in code_jldw.dwdm%type, --参数 类型在 code_jldm表的dwdm列 的类型
dwmc in code_jldw.dwmc%type, --同上
pydm in code_jldw.pydm%type -- 同上
)
is --is 关键字
begin -- begin 开始
execute immediate 'insert into code_jldw(dwdm,dwmc,pydm) values(:1,:2,:3)' using dwdm,dwmc,pydm; --execute immediate '立即执行' 插入数据 'insert into code_jldw(dwdm,dwmc,pydm) values(:1,:2,:3)' using dwdm,dwmc,pydm; --参数性 赋值
dbms_output.put_line(sql%rowcount); --输出
end; --end 结束
---存储的调用
*************注意: oracle中的相同格式的时间可以进行比较大小 如:
tv.datetime_in>= to_date('" + firstdt.ToString() + @"','yyyy-MM-dd hh24:mi:ss')
这位一个表列的时间 和 把变量时间 变换成相同格式的时间 进行比较 判断 是否小于等于 表列 中的时间
-----------解决当导入数据库时 的空间不足到不进去的
首先: 查看表空间大小
sql :: select T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024*1024)),0).TS_SIZE FROM DBA_TABLESPACE T,DBA_DATA_FILES D WHERE T.TABLE SACE_NAME=D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
然后再查看 修改后的表空间的数据文件名称
select file_name,blocks,tablespace_name from dba_date_files
在 将对应的表空间的dpf文件路径记下来 比如 :
:/opt/orale/oradate/TEST/test.dbf;
最后修改表空间的数据文件
sql :
alter database datafile 'opt/oracle/test/test.dbf' resize 2000m;
_________________________
--表空间的重要作用之一 : 规划数据表 ;
select * from dba_data_files order by file_name ; --查看数据库的表空间 信息
create tablespace test datafile 'E:\database\data\test_data.dbf' size 20m autoextend on next 5m ;--当表空间的大小不够用时 自动扩充 为5 兆 的空间大小;
--避免一直扩充 可以 添加限制 : maxsize 200m 解析: 最大为200m ;
select user_id,username,default_tablespace from dba_users order by user_id --用户数据库的默认表空间
alter database default tablespace test; --修改数据库的默认空间为 test
alter tablespace test rename to test_data --修改表空间的名称为 test_data
drop tablespace test including contents and datafiles -- 删除表空间 包涵内容和数据文件
alter table student add (class_id number)-- 为已有的表添加列
alter table student modify(class_id varchar2(20)) --修改表中的已有的列
alter table DICT_CP_SLAVE drop column S_ID; --删除表中的某一列
alter table student rename clolum student_id to id -- 修改列的列名
alter table student move tablespace users --将表转移到表空间users中;
drop table student cascade constraints; --删除约束;
dual -- oracle 中非常特殊的数据表 ;实际属于系统用户sys,具有基本用户权限的都可以使用 查询该表内容
select * from user_tab_cols --获取在此表空间中的所有表的信息和列
minus --求差集合 intersect -- natural --自然连接 就是 两个表连接 获取 两个表 相同的列同时列的值相同;
full join -- 完全连接 -- 外连接 的另一种写法 : 如 a表 和b 表 select * from a, b where a.id=b.id(+) 表示 a为主表 b 为附注表
start with 开始条件 connect by 递归条件--层次化查询 如 select * from student start with name=zangshan connect by id<2;
解析: 查询student表中 的信息 从 name='zangshang' 开始 name等于zangshang的的所有子信息
select bin_to_num(1,0,1) a from dual -- 二进制转换成数字函数
insert into c_students (student_id,student_name) select student_id,student_name from students where student_id<=10; --案例 :批量插入 没有values --自己用到是在琢磨
--delete 与truncate table 删除表的区别是 delete是删除单个或多行数据的 truncate table 删除的是表的内容 (数据) drop 是彻底的删除表
select * from students for update -- 针对students表锁定 进行修改
使用命令添加注释 : 写法如下:
comment on column 表名.列名 ---为表列添加注释
is '注释 ';
comment on table 表名
is '注释' ; --为表添加注释
------------------------------
包的创建:定义
create or replace package types as TYPE cursortype is ref cursor; end;
把 数据放入游标方法 1 大开游标>into 游标>关闭
----
修改内测问题
案例:
// 1. 存储的使用:查询配置相中是否 有此项
create or replace procedure proc_dict_config
(
name_value in dict_config.key%type, --传入的key值
outValue out dict_config.value%type --输出相关的值
)
as
name_num integer;---查询出的数量
begin
--查询传入的key值是否存在
select count(key) into name_num from dict_config where key=name_value;
--如果存在将key所对应的value值赋给outValue
if name_num=1
then
select value into outValue from dict_config where key=name_value;
else
outValue:='';
end if;
end proc_dict_config;
// 2 . 在存储中一般对数据增删改查的操作 都会是有 下面这一句 "dbms_output.put_line(sql%rowcount);" 用来返回受影响的行数
// 3. 关键字"execute immediate" 立即执行
// 4. oracle 数据库中的异常信息 是"SQLCODE" 错误码 ,"SQLERRM"错误信息
------
//5 触发器的 案例学习
--当患者的信息中没有省份的时候自动添加 "area"表示省份
create or replace trigger TRI_CODE_PROVINCE_INSERT
after insert on CODE_PROVINCE
for each row
declare
-- local variables here
--by liuxiaoke Add
patientCount number;
begin
--插入不存在的数据
select count(*)
into patientCount
from HIS_DICT_DICT b
where B.C_CODE = 'area'
AND b.d_code = :NEW.PROVINCE_ID;
if patientCount = 0 then
begin
--插入不存在的数据
insert into HIS_DICT_DICT
(C_CODE, D_CODE, D_NAME, INPUT, SNO, LAST_MODIFY_TIME)
values
('area',
:new.PROVINCE_ID,
:new.PROVINCE_NAME,
:new.SRM1,
:NEW.SXH,
:NEW.XGSJ);
end;
end if;
end TRI_CODE_PROVINCE_INSERT;
________________________________________________________________
学习数据库
关系数据库有数据结构 关系操作集合 关系完整性 组成
关系运算的特点是操作对象和操作结过都是集合
运算符: 并 交 差 及广义笛卡尔积 (传统) ;
(选择 投影 连接 以及除法) (专业)
关系代数运算符
运算符 含义
集合运算符 U 并 比较运算符 > 大于
>= ,< .<= ,=,
- 差
n 交
x 笛卡尔积
专门的关系运算符
在关系代数运算中,笛卡尔积,连接运算最浪费时间和空间,
一 /. 数据库优化的准则:**************************************************
1.提前执行选取运算,原因:以较小的中间结果,减少运算量和从外存读快的次数
2. 合兵乘积与其后的选择运算为连接运算. 原因:把选择与乘积一道完成再合并,以避免做完乘积后再对一个大的乘积关系运算选择运算
3.将投影运算与其后的其他运算同时进行,以避免重复扫描关系
4.将投影运算和其前后的二目运算结合起来,使得没有必要为去掉某写字段在扫描一遍关系
5. 在执行连接之前对关系做适当的预处理,就能快速地查到要连接的元组.
方法: 1即 索引连接法 和 排序合并连接法.
6. 存储公共子表达式. 公共子表达式的结果应存于外存(中间结果),这样,当从外存读出它的时间比较计算时间少时,就可节约操作时间
*****************************************************************
二 ./ 关系代述表达式的等价变换规则
三 .关系数据库设计的目标 是生成一组适合的
性能 良好的关系模式 以减少系统中信息存储的冗余度,又可以获取信息
第一范式 ; 元素是不可再分割的数据项
第二范式 : 符合第一范式 且 每一个非主属性完全依赖于码(主键)
第三范式 :符合第二范式 且 消除了非主属性对码的传递函数依赖(有外键)
四 . BCNF(巴克斯范式) :
第四范式: 多值依赖 第五范式: 连接依赖
-------------SQL的核心功能
数据查询 select
数据定义 create drop alter
数据操作 insert update delete
数据控制 grant revoke
--------------sQL 的三级模式
视图 : >>外模式
基本表 :?>>> 模式
存储过程 >>> 内模式
create 创建表
修改表
alter 表名 [drop <完整性约束名>]
alter 表名 [add <lieming><deng>]
alter 表名 [modify <列名>]
创建视图
create view 名称
as select 语句 如果 要对视图操作 侧添加 "with check option";
集合操作 : 并(union) 交 (intersect ) 差 (except) (需练习不熟悉)
-----------------
WITH 字句 (把一个大的查询分成一些小的视图)
***********************
授权语句格式
grant <权限>[,<权限>]....
[on <对象类型><对象名>]
to<用户>[,<用户>]...
[with grant option];
::::::::::::::::::::::
收回权限
revoke <权限>[,<权限>]...
[on <对象类型><对象名>]
from<用户>[,<用户>]....
::::::::::::::::::::::::::::::::::::::::::触发器 trigger
定义:是一种特殊类型的存储过程,它通过实践出发而执行,而存储过程可以通过存储过程的名称被直接调用,
触发器使每个站点在数据修改时自动强制执行其业务规则,并且可以用于 sql server 约束,默认值,和规则的完整性检查
1 触发器 >首先是 被激活 条件判断后执行 不是直接执行的 事件之前 (激活)
==============================嵌入式sql
结构类型 :
方便与使用相同类型的几个字段重用 对表创建的时候
结构类型的继承 如: create yupe t under a ; t 继承a
表继承 create table b of of b under p b继承 p
引用类型 :一个类型的属性可以是对一个执行类型的对象的而引用
可以使用 call 调用 存储
===++++++++++++++++++系统开发与运行
开发模式 : 瀑布模式 演化模式 螺旋模式 喷泉模式
软件生存周期 :计划制定 (文档) 可行性分析报告 项目计划书
>需求分析 需求规格说明数
> 软件设计 概要设计说明书 和 详细设计说明数
> 程序编制(写代码) 源程序清单
＞测试 软件测试计划 软件测试报告
> 运行维护 使用说明书
项目管理知识 :
成本估算 :
开发费用 =人月数 x 每个人月的代价 或 开发费用 =源代码行数 X每行平均费用
代码的幻术估算
成本估算的模式 : 普特南 模型 和cocomo 模型等
风险分析 > 进度管理 任务有松弛时间 >人员管理
软件开发的方法 :
结构化 严格的开发程序 要求有完整的文档记录
面向对象 和 原型法
软件工具 开发环境
软件质量管理和质量保证
1 <功能, 可靠,易使用,效率 ,可移植 ,可维护
软件能力成熟的模式 CMM 5个级别
系统的分析
面向对象分析 OOA
(文档)
系统设计说明数
系统总体技术方案
1模块设计 >代码设计>输入设计>输出设计>数据库设计说明> 模型库及方法库设计
>网络设计>安全保密设计> 实施方案说明书
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
数据库的恢复 和日志有关 ()
并发 事务会造成破坏事务的隔离性和一致性 解决方法 :枷锁;
排它锁(修改) ,共享锁(只读) ,封锁协议
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>数据
库系统的审计 :可以很好的监视都是谁对数据库修改操作的情况 但是 有对性能上有影响
对数据库的备份计划的制定和实施 要求:
1.
根据数据变更情况,设计合理的备份周期和备份时间,最好是在业务量最小的时段进行备份
2.把事务日志文件保存在最稳定的存储设备上;
3.定期在事务日志文件中加入检查点
检查点记录了数据库的正确状态点,在数据库恢复过程中,可以反向扫描日志文件,找到第一个检查点,执行UNdo 和Redo 操作,减少恢复的时间开销
模糊查询时间方法: like to_date('2011-8-27','yyyy-MM-dd')
---------------------------------------
对于多个组分组基表不确定出现 重复的 基表重复列解决的方法 调用函数
声明 序列号列 : 最常用的分析函数 排名函数 : rank ,dense_rank ,row_number() ;
--1 一种写法
Row_number() over(partition by col1,col2 (可以是多列) order by col3 (可以是多列)) as rn
此方法记录当前所在的行号
2种写法 : --
rank () over(order by col1) as rn
此方法 的排名 具有"跳跃性 " 如 张三 ,李四 都是 排名第二 , 那么下一个紧挨的 王五, 是排名 第四
3. dense_rank() over(order by col1 ) as rn
此方法 的排名 不具有"跳跃性 " 排名同名次可以
------
分区窗体
创建分区窗体的 语法 : partition by
--窗体字句 追随 前后的信息
over ( order by 列名 rows between 位移量 preceding and 位移量 following )
案例 ::
selecT cp_name ,cp_id ,dense_rank() over(order by cp_id )as recodern,
sum(cp_id) over(order by cp_id rows between 1 preceding and 1 following ) sumcpid
from dict_cp_master
--------------------------------------------------------------------
4 fist_value () ,last_value() , lead() ,lag()
然后 查找是 加上 where 条件 : where rn=1
获取时间 小时 加 8 加的是时区
select extract(hour from systimestamp )+8 newdate from dual
------------------------------------------------------关于sql------------------------------------------------
--1.批量生成sql语句(检测表中主键字段作于是否含有空格)
select 'select * from ' || b.table_name || ' where ' ||'substr('||b.column_name||',0,1)='' '' ' || 'or substr('||b.column_name ||',length('||b.column_name||'),1)='' '''
from user_constraints a,user_cons_columns b
where a.CONSTRAINT_NAME = b.constraint_name
AND a.CONSTRAINT_TYPE = 'P'
AND b.constraint_name not like 'SYS%'
AND b.table_name IN (select TABLE_NAME from user_tables)
ORDER BY b.TABLE_NAME
--2.关于锁表问题的解决：
select * from v$locked_object ;--查看被锁定的数据，得到object_id在dba_objects中可以查看出来被锁定的表
select * from dba_objects where object_id ='119689';--可以看出来哪个表被锁住
select * from v$session where sid =371;--从v$locked_object得到sid
alter system kill session '371,274';--传入sid和serial值
--3.修改
a.修改约束(先增后删)：
b.修改表中子段类型:
b1.alter table code_cwxxb modify hlks varchar2(4);--将表code_cwwxxb中hlks字段类型改为:varchar2(4)
b2.修改char类型为varchar类型(使用转换的思想)--先改名,再添加,后赋值,最后删除
alter table zy_cnext_hf rename column hlks to hlks_back
alter table zy_cnext_hf a a.hlks varchar2(4)
update zy_cnext_hf a set a.hlks=(select trim(b.hlks_back) from zy_cnext_hf b where a.rowid=b.rowid)
alter table zy_cnext_hf drop column hlks_back
--4.关于oracle中的系统表：注意(对于字段区分大小写)
select * from user_tables --查出该登陆用户系统中的所有表
select * from user_tab_columns-- 查出登陆用户系统中所有表的列
select * from dba_tables --查询系统中所有的表
--5.关于系统表的比对：
--1>.缺少的表
SELECT C.*
FROM (SELECT A.TABLE_NAME A_TABLE,
B.TABLE_NAME B_TABLE
FROM (SELECT TABLE_NAME
FROM Dba_Tables
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) A
LEFT JOIN
(SELECT TABLE_NAME
FROM Dba_Tables
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) B
ON A.TABLE_NAME = B.TABLE_NAME) C
WHERE C.B_TABLE IS NULL
ORDER BY C.A_TABLE
--2>.缺少的视图或视图的内容不一致
SELECT C.*
FROM (SELECT A.VIEW_NAME A_VIEW,
A.TEXT_LENGTH A_TEXT_LENGTH,
B.VIEW_NAME B_VIEW,
B.TEXT_LENGTH B_TEXT_LENGTH
FROM (SELECT VIEW_NAME,TEXT_LENGTH
FROM DBA_VIEWS
WHERE OWNER = 'TPHIS_TH' --用标准库代替
) A
LEFT JOIN
(SELECT VIEW_NAME,TEXT_LENGTH
FROM DBA_VIEWS
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
) B
ON A.VIEW_NAME = B.VIEW_NAME) C
WHERE C.B_VIEW IS NULL OR C.A_TEXT_LENGTH <> C.B_TEXT_LENGTH
ORDER BY C.A_VIEW
--3>.缺少字段或类型不同或长度不同
SELECT C.* --DISTINCT C.A_TABLE
FROM (SELECT A.TABLE_NAME AS A_TABLE,
A.COLUMN_NAME AS A_COL,
A.DATA_TYPE AS A_TPYE,
A.DATA_LENGTH AS A_LEN,
B.TABLE_NAME AS B_TABLE,
B.COLUMN_NAME AS B_COL,
B.DATA_TYPE AS B_TPYE,
B.DATA_LENGTH AS B_LEN
FROM (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) A
LEFT JOIN
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) B
ON A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME=B.TABLE_NAME ) C
WHERE C.B_COL IS NULL OR (C.A_TPYE<>C.B_TPYE OR C.A_LEN <> C.B_LEN )
ORDER BY C.A_TABLE,C.A_COL
--4>.表中缺少存储
SELECT C.A_NAME, C.B_NAME
FROM (SELECT A.NAME A_NAME, B.NAME B_NAME
FROM (SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TYPE = 'PROCEDURE') A
LEFT JOIN
(SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TYPE = 'PROCEDURE') B
ON A.NAME = B.NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
--5>.表中缺少函数
SELECT C.A_NAME, C.B_NAME
FROM (SELECT A.NAME A_NAME, B.NAME B_NAME
FROM (SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TYPE = 'FUNCTION') A
LEFT JOIN
(SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TYPE = 'FUNCTION') B
ON A.NAME = B.NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
--6>表中存储或函数内容不一样
SELECT *
FROM (SELECT A.TYPE A_TYPE,
A.NAME A_NAME,
A.TEXT A_TEXT,
B.TEXT B_TEXT
FROM (SELECT TYPE,NAME, LINE, TEXT
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_DEV' --用标准库代替
AND ((TYPE = 'PROCEDURE') OR (TYPE = 'FUNCTION') OR (TYPE = 'TRIGGER') OR (TYPE='PACKAGE'))) A
LEFT JOIN
(SELECT TYPE,NAME, LINE, TEXT
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_LN' --用被比较的库代替
AND ((TYPE = 'PROCEDURE') OR (TYPE = 'FUNCTION') OR (TYPE = 'TRIGGER') OR (TYPE='PACKAGE'))) B
ON A.NAME = B.NAME AND length(replace(to_char(decode(A.TEXT,null,' ',A.TEXT)),' ',''))
=length(replace(to_char(decode(B.TEXT,null,' ',B.TEXT)),' ',''))) C
WHERE C.B_TEXT IS NULL
ORDER BY C.A_NAME,C.A_TEXT;
--7>表中缺少包
SELECT C.A_NAME, C.B_NAME
FROM (SELECT A.NAME A_NAME, B.NAME B_NAME
FROM (SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPEMR_TH' --用标准库代替
AND TYPE = 'PACKAGE') A
LEFT JOIN
(SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPEMR_ZH0621' --用被比较的库代替
AND TYPE = 'PACKAGE') B
ON A.NAME = B.NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
--6系统函数：
substr(bmdm,0,1)--从第一位开始截取一位
substr(bmdm,length(bmdm),1)--从最后一位开始截取一位
--7.为表中特定的列添加说明
comment on column CODE_YPDM.CBBZ
is '0没有拆包1已拆包'
--8.关于表之间缺少字段或类型不同或长度不同
SELECT C.* --DISTINCT C.A_TABLE
FROM (SELECT A.TABLE_NAME AS A_TABLE,
A.COLUMN_NAME AS A_COL,
A.DATA_TYPE AS A_TPYE,
A.DATA_LENGTH AS A_LEN,
B.TABLE_NAME AS B_TABLE,
B.COLUMN_NAME AS B_COL,
B.DATA_TYPE AS B_TPYE,
B.DATA_LENGTH AS B_LEN
FROM (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPEMR_DEV' --用标准库代替
AND TABLE_NAME = ''
) A
LEFT JOIN
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPEMR_DEV' --用被比较的库代替
AND TABLE_NAME = ''
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME=B.TABLE_NAME ) C
WHERE C.B_COL IS NULL OR (C.A_TPYE<>C.B_TPYE OR C.A_LEN <> C.B_LEN )
ORDER BY C.A_TABLE,C.A_COL;
--实际应用比对tpemr与tphis同名字典表
SELECT C.* --DISTINCT C.A_TABLE
FROM (SELECT A.TABLE_NAME AS A_TABLE,
A.COLUMN_NAME AS A_COL,
A.DATA_TYPE AS A_TPYE,
A.DATA_LENGTH AS A_LEN,
B.TABLE_NAME AS B_TABLE,
B.COLUMN_NAME AS B_COL,
B.DATA_TYPE AS B_TPYE,
B.DATA_LENGTH AS B_LEN
FROM (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPHIS_DEV' --用标准库代替
AND TABLE_NAME in
(select table_name
from dba_tables
where owner = 'TPEMR_DEV'
and table_name like '%CODE%'
and table_name in
(select table_name
from dba_tables
where owner = 'TPHIS_DEV'
and table_name like '%CODE%'))) A
LEFT JOIN (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPEMR_DEV' --用被比较的库代替
AND TABLE_NAME in
(select table_name
from dba_tables
where owner = 'TPEMR_DEV'
and table_name like '%CODE%'
and table_name in
(select table_name
from dba_tables
where owner = 'TPHIS_DEV'
and table_name like '%CODE%'))) B ON A.COLUMN_NAME =
B.COLUMN_NAME
AND A.TABLE_NAME =
B.TABLE_NAME) C
WHERE C.B_COL IS NULL
OR (C.A_TPYE <> C.B_TPYE OR C.A_LEN <> C.B_LEN)
ORDER BY C.A_TABLE, C.A_COL;
--9.关于数据库完整性sql
--eg：code_ypdm<规格和计量单位存在对应不一致>
select 'code_ypdm' table_name,
ypdm||'(ypdm) '||ypmc||'(ypmc) '||dw||'(dw) '||jldw||'(jldw) '||gg||'(gg) '||jlbl||'(jlbl) ' Error_Field,
'在规格和剂量单位的处理上可能存在问题' Error_Explain
from (select case when substr(gg, 1, 2) = '0.'
then substr(gg, 2, length(to_char(jlbl)))
else
substr(gg, 1, length(to_char(jlbl)))
end as gg0,
to_char(jlbl) as jlbl0,length(to_char(jlbl)),
gg,jlbl,ypdm,dw,jldw,ypmc
from (select replace(gg,' ','') as gg,ypdm,dw,jlbl,jldw,ypmc from code_ypdm))
where gg0 <> jlbl0 and ypdm not in (select ypdm from code_ypdm where dw = jldw and jlbl = 1)
--10.修改统计报表中名称字段长度
select 'alter table ' || table_name || ' modify ' || column_name || ' ' ||
data_type || '(' || data_length || ');'
from (select table_name,
column_name,
data_type,
(select data_length
from (select column_name, max(data_length) as data_length
from user_tab_columns
where table_name not like 'BIN%'
and column_name in
(select distinct column_name
from user_tab_columns
where table_name in (select table_name from user_tables where table_name like 'TJ%SHOW')
and column_name like '%MC')
group by column_name
order by column_name) a
where a.column_name = b.column_name) as data_length
from user_tab_columns b
where table_name in (select table_name from user_tables where table_name like 'TJ%SHOW') and column_name like '%MC'
order by table_name)
--11.解决操作员主键报错问题
select * from user_source where name='TRI_CODE_KSDM_INSERT'
select * from user_source where name='TRI_CODE_KSDM_UPDATE'
insert into his_dict_dept (dept_id,dept_name,input,clinic_attr,last_modify_time,outp_or_inp,sno)
select ksdm,ksmc,pydm,1,xgsj,mzzy,sxh
from code_ksdm
where ksdm not in (select dept_id from his_dict_dept);
--12.关于job的创建
declare job1 number;
begin
dbms_job.submit(job1,'PJ_CWSYQQ_SHOW2;',sysdate,'sysdate+1');
--(参数1:job号,参数2:存储名称,参数3:下次执行时间,参数4:每次执行的间隔时间)
end;
begin
dbms_job.remove(jobno); --删除
end;
begin
dbms_job.next_date(job,next_date); --修改下次执行时间
end;
begin
dbms_job.interval(job,interval); --修改间隔执行时间
end;
--13.在v$session表中显示ip信息以及触发器的创建
--1).trriger实现:
create or replace trigger on_logon_trriger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
--2).查询当前登录客户端的机器名和ip地址
select machine,client_info from v$session where audsid=userenv('sessionid');
--3).授权用户实现v$session的使用(对用户tpsoft_lp1011分配访问v$session的权限)
grant select on v$session to tpsoft_lp1011
--4).创建实例:
--函数:
create or replace function fun_isdb_ipaddress
return varchar2 as
client_ipaddress varchar2(50);
begin
select client_info into client_ipaddress from v$session where audsid=userenv('sessionid');
return client_ipaddress;
end;
--关于添加的触发器:
create or replace trigger tri_sis_xtcs_log_insert
after insert on sis_xtcs
for each row
declare ip varchar(50);
mc varchar(50);
begin
select machine into mc from v$session where audsid=userenv('sessionid');
select client_info into ip from v$session where audsid=userenv('sessionid');
begin
insert into sis_xtcs_log(xtmk,csmc,xgpb,csz,mrz,bz,xgrdm,xgrmc,xglb,xgsj)
values(:new.xtmk,:new.csmc,:new.xgpb,:new.csz,:new.mrz,:new.bz,ip,mc,'INSERT',sysdate);
end;
end tri_sis_xtcs_log;
--关于删除的触发器:
create or replace trigger tri_sis_xtcs_log_delete
after delete on sis_xtcs
for each row
begin
begin
insert into sis_xtcs_log(xtmk,csmc,xgpb,csz,mrz,bz,xgrdm,xgrmc,xglb,xgsj)
values(:old.xtmk,:old.csmc,:old.xgpb,:old.csz,:old.mrz,:old.bz,'temp','temp','DELETE',sysdate);
end;
end tri_sis_xtcs_log;
--14.所有表空间的使用情况
select b.file_id 文件ID号,b.tablespace_name 表空间名,b.bytes/1024/1024||'M' 字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用,100-sum(nvl(a.bytes,0))/(b.bytes)*100 占百分比,
sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id
--15.查看用户默认的表空间
select a.username,a.default_tablespace from dba_users a
--3.查看要扩展的表空间使用的数据文件路径与名字
select * from dba_data_files where tablespace_name='TSP_TPHY'
--4.扩展表空间
alter tablespace TSP_TPHY
add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TPHY\TSP_TPHY1.DBF' size 500M
autoextend on
next 50M
maxsize 3000M
----------------------关联相应的表插入数据
merge into dict_cp_fhzdwjyyb jyyb
using(select * from(select dcv.cp_id , dcv.diag_code, Row_number() over(partition by dcv.diag_code order by dcv.diag_code) as rn from dict_cp_vs_icd10 dcv ) where rn=1 ) sour
on (jyyb.diag_no = sour.diag_code)
when matched then
update
set jyyb.cp_id = sour.cp_id
--------获取某表中的列数
select count(1) from user_col_comments where table_name =upper('his_patient_base')
--循环的使用
--语句块
declare v_id number :=0;
v_name varchar2(30) ;
begin
for v_id in 1..3 loop
select colls.table_name into v_name from (select tu.table_name ,
row_number() over(partition by tu.tablespace_name order by tu.table_name) rn
from user_tables tu
where tu.table_name like 'DICT_CP%') colls where colls.rn = v_id;
dbms_output.put_line(v_id || '测试' || v_name);
-- truncate table v_name; 不行
end loop;
end;
--测试 语句块
declare cursor cu_reurn is
select cp_name ,cp_id from dict_cp_master;
v_name dict_cp_master.cp_name%type;
v_id dict_cp_master.cp_id%type;
begin
open cu_reurn ;
fetch cu_reurn into v_name ,v_id;
while cu_reurn % found loop
dbms_output.put_line(v_name || ' 路径' || v_id);
fetch cu_reurn into v_name ,v_id ;
end loop;
close cu_reurn;
end ;
----------------------------------
select * from dba_users
create user test identified by a123456 default tablespace SYSTEM --创建用户
select * from dba_objects --查看对象 所有的
select sys_context('userenv','current_user') current_user,sys_context('userenv' ,'current_schema') current_schema from dual; --获取当前数据库 和用户
alter USER TPEMR ACCOUNT UNLOCK --解锁
select * from dba_sys_privs --系统 权限
select * from system_privilege_map -- 系统权限信息
--授权 grant 回收权限 revoke
--1
grant create session ,create table to dev_std with admin_option;
revoke create session ,create table to dev_std
--行变列\
create or replace function row2colunm(sqlString varchar2)
return varchar2 as
begin
declare type cu_type is ref cursor ;
tpm_cursor cu_type;
tmp_row varchar2(20);
_return varchar2(500);
begin
open tmp_cursor is sqlString ;
fetch tmp_cursor into tmp_row;
while tmp_cursor%found loop
_return := _return ||tmp_row ||',';
fetch tmp_cursor into tmp_row ;
end loop ;
return _return;
end
end row2colunm;
--测试
select row2colunm('select cp_name from dict_cP_master where rownum <10') reu from dual;
--包 package 就像是接口
select * from v$sgastat --查看系统全区
select namespace ,gets, gethits ,gethitratio ,pins,pinhits,pinhitratio from v$librarycache;
--修改 系统的缓存区的内存
alter SYSTEM set sga_max_size =1000m scope = both ;spfile ;
--索引
create index objindex on test_index(object_name);
create table test_index
as select * from dba_objects;
select * from test_index --0.031
select * from dba_objects --0.063
--索引 使用规则:
--不宜使用
-- 1. 数据量较小的表 2 . 有着频繁数据变更的表不宜使用索引
------------------
start with lei=' ' connect by prior mariket_id
--
管道的学习 ： pipelined ;
首先 声明 一个table 中所需的列 放在一个类型中； 再 定义一个table 对象 ， 最后使用管道；
如下 ：
1 、create or replace type maType_objType as object
(
医嘱类型编码 varchar2(20),
医嘱类型名称 varchar2(10),
拼音码 varchar2(10),
自定义码 varchar2(10)
)
2、
create or replace type maType_tableType as table of maType_objType
3、
create or replace function fun_get_maType
return maType_tableType pipelined
is
v_table_object maType_objType;
begin
for t_row in (select * from code_ma_type t order by t.ma_type_code asc)
loop
v_table_object := maType_objType(t_row.ma_type_code,t_row.ma_type_name,t_row.char_code,t_row.custom_code);
pipe row(v_table_object);
end loop;
return;
end;
-------------
alter table code_cwxxb modify hlks varchar2(4);
----------死锁解决方法 ：
“”
--2.关于锁表问题的解决：
select * from v$locked_object ;--查看被锁定的数据，得到object_id在dba_objects中可以查看出来被锁定的表
select * from dba_objects where object_id ='69684';--可以看出来哪个表被锁住
select * from v$session where sid =104;--从v$locked_object得到sid
alter system kill session '104,61103';--传入sid和serial值
create user test identified by a123456 default tablespace SYSTEM --创建 用户
___________+++++++++++++++++++++++++++++++++++++++_+_++++++++++++++++++++++++++++++++
oracle的参数传递 案例如下:
string strSql = "select t.exmaster_id from emr_cp_exec_master t where inp_no =:inp_no and not exists(select exmaster_id from emr_cp_exec_out t2 where t2.inp_no = t.inp_no and t2.exmaster_id = t.exmaster_id)";
OracleParameter[] param = new OracleParameter[1];
param[0] = new OracleParameter("inp_no", OracleType.VarChar);
param[0].Value = inp_no;
object obj = DALUse.GetSingle(strSql, param);
return obj == null ? 0 : Convert.ToInt32(obj);
解析: where条件后跟的"inp_in=:inp_in" 在里面的冒号 就是传参的关键字符;
传参的过程 :
OracleParameter[] param = new OracleParameter[1]; //一个参数 数组
param[0] = new OracleParameter("inp_no", OracleType.VarChar); //参数的列名和 类型 如":inp_no 和类型 Varchar
param[0].Value = inp_no; //参数的赋值
object obj = DALUse.GetSingle(strSql, param); //代入数据访问层方法 返回结果
***********************************************************************+++++___________
函数
lpad() 向左补全字符串 rpad()向右补全字符串
lower()小写 upper()大写
initcap()单词手写字母大写 length() 长度
substr()截取字符串 instr()获取字符串出现的位置 ltrim()删
除字符串左边空格 rtrim()删除字符串右边的空格 删除两侧的空格 concat() 串联字符串 翻译字符串translate() 反转字符串 reverse()
数值类型的处理函数
abs() 绝对值 四舍五入 round() 向上取整ceil() 向下取整floor() 去摸mod() 截取数字trunc() 返回数字正负性sign() 平方根sqrt() 乘方power()
获取当前时间 sysdate()
在日期上加月份add_months()
返回特定日期的最后一天 last_day()
返回两个月份之间的差值months_between()
截取日期 trunc()
返回特定日期一周后的日期next_day()
返回当前会话日期 current_date() 放回当前会话时区的时间截
返回某个时间域extract()
转换字符串 to_char()
多值判断decode()
extract (Miniute from sysdate )
空值处理nvl() "如: nvl('a','b') 判断a是否为零 如果为零则 返回值为b 否则 a"
结果集的行号 rownum()
强制转换数据类型 cast()
序列 关键字sequence 语法 create sequence 序列名称 重要属性 currval(获取当前值) 和nextval ()获取下一个值
drop 进行删除
rownum=x "x表示行数" 和top类似 写法不同 它写在 where条件中
**********自定义函数的调用 语法 select 方法 from dual ;
********字符串拼接用到 "||" 如: 在一个数字后加上"%" 写法 为: 数字||"%"
___创建函数 如:加法
create or replace function add(a integer,b integer)
return number
is c number;
begin
c:=a+b;
return c;
end add;
---调用 方法 如: select add(1,2) from dual; return 3;
---序列
创建 一个自增长为一的序列
create sequence x nocycle
调用时 用到 它的两个伪列 crruval 和 nextval
如:创建表a 的b 列
create table a ( b integer );
insert a values(x.crruval) ; 添加
定义多项的序列
create sequence test
statr with 10 increment by 5 从10开始 自增长的数值为5
minvalue 10 maxvalue 20 定义最大值和最小值
cycle cache 2 order; 默认缓存
------------创建一个语句块 如: 根据高和面积 求 宽
declare
width integer ;
height integer :=2;
area integer;
begin
area:=6;
width :=area/height;
dbms_output.put_line('width='||width);
exception --异常处理
when zero_divide then
dbms_output.put_line('Division by zero');
end;
------------
_________________________________________________________________
使用%type 对表里的列进行声明变量的 类型(自动声明为正确的类型)
如: product 表中
declare
v_product products.product_id%type;
_____________________________________________
______________定义游标 关键字cursor
定义:可以理解为一次访问一个组记录.
>声明一些变量 用于保存select语句返回的列值 --declare v_product_id products.product_id%type;
>声明游标 并指定select 语句 -- cursor cv_cursor is select product_id from products order by product_id ;
begin
>打开游标 -- open cv_cursor;
>从游标中获取记录 -- loop --循环
-- fetch cv_cursor into v_product_id;
-- exit when cv_cursor%notfound --如果没找到就退出
dbms_output.put_line('v_product_id'||v_product_id);
--end loop;4820
>关闭游标\ -- close cv_cursor;
______________________________________________________
___________________存储过程
create or replace procedure proc_add_unit --创建存储的关键字procedure
(
dwdm in code_jldw.dwdm%type, --参数 类型在 code_jldm表的dwdm列 的类型
dwmc in code_jldw.dwmc%type, --同上
pydm in code_jldw.pydm%type -- 同上
)
is --is 关键字
begin -- begin 开始
execute immediate 'insert into code_jldw(dwdm,dwmc,pydm) values(:1,:2,:3)' using dwdm,dwmc,pydm; --execute immediate '立即执行' 插入数据 'insert into code_jldw(dwdm,dwmc,pydm) values(:1,:2,:3)' using dwdm,dwmc,pydm; --参数性 赋值
dbms_output.put_line(sql%rowcount); --输出
end; --end 结束
---存储的调用
*************注意: oracle中的相同格式的时间可以进行比较大小 如:
tv.datetime_in>= to_date('" + firstdt.ToString() + @"','yyyy-MM-dd hh24:mi:ss')
这位一个表列的时间 和 把变量时间 变换成相同格式的时间 进行比较 判断 是否小于等于 表列 中的时间
-----------解决当导入数据库时 的空间不足到不进去的
首先: 查看表空间大小
sql :: select T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024*1024)),0).TS_SIZE FROM DBA_TABLESPACE T,DBA_DATA_FILES D WHERE T.TABLE SACE_NAME=D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
然后再查看 修改后的表空间的数据文件名称
select file_name,blocks,tablespace_name from dba_date_files
在 将对应的表空间的dpf文件路径记下来 比如 :
:/opt/orale/oradate/TEST/test.dbf;
最后修改表空间的数据文件
sql :
alter database datafile 'opt/oracle/test/test.dbf' resize 2000m;
_________________________
--表空间的重要作用之一 : 规划数据表 ;
select * from dba_data_files order by file_name ; --查看数据库的表空间 信息
create tablespace test datafile 'E:\database\data\test_data.dbf' size 20m autoextend on next 5m ;--当表空间的大小不够用时 自动扩充 为5 兆 的空间大小;
--避免一直扩充 可以 添加限制 : maxsize 200m 解析: 最大为200m ;
select user_id,username,default_tablespace from dba_users order by user_id --用户数据库的默认表空间
alter database default tablespace test; --修改数据库的默认空间为 test
alter tablespace test rename to test_data --修改表空间的名称为 test_data
drop tablespace test including contents and datafiles -- 删除表空间 包涵内容和数据文件
alter table student add (class_id number)-- 为已有的表添加列
alter table student modify(class_id varchar2(20)) --修改表中的已有的列
alter table DICT_CP_SLAVE drop column S_ID; --删除表中的某一列
alter table student rename clolum student_id to id -- 修改列的列名
alter table student move tablespace users --将表转移到表空间users中;
drop table student cascade constraints; --删除约束;
dual -- oracle 中非常特殊的数据表 ;实际属于系统用户sys,具有基本用户权限的都可以使用 查询该表内容
select * from user_tab_cols --获取在此表空间中的所有表的信息和列
minus --求差集合 intersect -- natural --自然连接 就是 两个表连接 获取 两个表 相同的列同时列的值相同;
full join -- 完全连接 -- 外连接 的另一种写法 : 如 a表 和b 表 select * from a, b where a.id=b.id(+) 表示 a为主表 b 为附注表
start with 开始条件 connect by 递归条件--层次化查询 如 select * from student start with name=zangshan connect by id<2;
解析: 查询student表中 的信息 从 name='zangshang' 开始 name等于zangshang的的所有子信息
select bin_to_num(1,0,1) a from dual -- 二进制转换成数字函数
insert into c_students (student_id,student_name) select student_id,student_name from students where student_id<=10; --案例 :批量插入 没有values --自己用到是在琢磨
--delete 与truncate table 删除表的区别是 delete是删除单个或多行数据的 truncate table 删除的是表的内容 (数据) drop 是彻底的删除表
select * from students for update -- 针对students表锁定 进行修改
使用命令添加注释 : 写法如下:
comment on column 表名.列名 ---为表列添加注释
is '注释 ';
comment on table 表名
is '注释' ; --为表添加注释
------------------------------
包的创建:定义
create or replace package types as TYPE cursortype is ref cursor; end;
把 数据放入游标方法 1 大开游标>into 游标>关闭
----
修改内测问题
案例:
// 1. 存储的使用:查询配置相中是否 有此项
create or replace procedure proc_dict_config
(
name_value in dict_config.key%type, --传入的key值
outValue out dict_config.value%type --输出相关的值
)
as
name_num integer;---查询出的数量
begin
--查询传入的key值是否存在
select count(key) into name_num from dict_config where key=name_value;
--如果存在将key所对应的value值赋给outValue
if name_num=1
then
select value into outValue from dict_config where key=name_value;
else
outValue:='';
end if;
end proc_dict_config;
// 2 . 在存储中一般对数据增删改查的操作 都会是有 下面这一句 "dbms_output.put_line(sql%rowcount);" 用来返回受影响的行数
// 3. 关键字"execute immediate" 立即执行
// 4. oracle 数据库中的异常信息 是"SQLCODE" 错误码 ,"SQLERRM"错误信息
------
//5 触发器的 案例学习
--当患者的信息中没有省份的时候自动添加 "area"表示省份
create or replace trigger TRI_CODE_PROVINCE_INSERT
after insert on CODE_PROVINCE
for each row
declare
-- local variables here
--by liuxiaoke Add
patientCount number;
begin
--插入不存在的数据
select count(*)
into patientCount
from HIS_DICT_DICT b
where B.C_CODE = 'area'
AND b.d_code = :NEW.PROVINCE_ID;
if patientCount = 0 then
begin
--插入不存在的数据
insert into HIS_DICT_DICT
(C_CODE, D_CODE, D_NAME, INPUT, SNO, LAST_MODIFY_TIME)
values
('area',
:new.PROVINCE_ID,
:new.PROVINCE_NAME,
:new.SRM1,
:NEW.SXH,
:NEW.XGSJ);
end;
end if;
end TRI_CODE_PROVINCE_INSERT;
________________________________________________________________
学习数据库
关系数据库有数据结构 关系操作集合 关系完整性 组成
关系运算的特点是操作对象和操作结过都是集合
运算符: 并 交 差 及广义笛卡尔积 (传统) ;
(选择 投影 连接 以及除法) (专业)
关系代数运算符
运算符 含义
集合运算符 U 并 比较运算符 > 大于
>= ,< .<= ,=,
- 差
n 交
x 笛卡尔积
专门的关系运算符
在关系代数运算中,笛卡尔积,连接运算最浪费时间和空间,
一 /. 数据库优化的准则:**************************************************
1.提前执行选取运算,原因:以较小的中间结果,减少运算量和从外存读快的次数
2. 合兵乘积与其后的选择运算为连接运算. 原因:把选择与乘积一道完成再合并,以避免做完乘积后再对一个大的乘积关系运算选择运算
3.将投影运算与其后的其他运算同时进行,以避免重复扫描关系
4.将投影运算和其前后的二目运算结合起来,使得没有必要为去掉某写字段在扫描一遍关系
5. 在执行连接之前对关系做适当的预处理,就能快速地查到要连接的元组.
方法: 1即 索引连接法 和 排序合并连接法.
6. 存储公共子表达式. 公共子表达式的结果应存于外存(中间结果),这样,当从外存读出它的时间比较计算时间少时,就可节约操作时间
*****************************************************************
二 ./ 关系代述表达式的等价变换规则
三 .关系数据库设计的目标 是生成一组适合的
性能 良好的关系模式 以减少系统中信息存储的冗余度,又可以获取信息
第一范式 ; 元素是不可再分割的数据项
第二范式 : 符合第一范式 且 每一个非主属性完全依赖于码(主键)
第三范式 :符合第二范式 且 消除了非主属性对码的传递函数依赖(有外键)
四 . BCNF(巴克斯范式) :
第四范式: 多值依赖 第五范式: 连接依赖
-------------SQL的核心功能
数据查询 select
数据定义 create drop alter
数据操作 insert update delete
数据控制 grant revoke
--------------sQL 的三级模式
视图 : >>外模式
基本表 :?>>> 模式
存储过程 >>> 内模式
create 创建表
修改表
alter 表名 [drop <完整性约束名>]
alter 表名 [add <lieming><deng>]
alter 表名 [modify <列名>]
创建视图
create view 名称
as select 语句 如果 要对视图操作 侧添加 "with check option";
集合操作 : 并(union) 交 (intersect ) 差 (except) (需练习不熟悉)
-----------------
WITH 字句 (把一个大的查询分成一些小的视图)
***********************
授权语句格式
grant <权限>[,<权限>]....
[on <对象类型><对象名>]
to<用户>[,<用户>]...
[with grant option];
::::::::::::::::::::::
收回权限
revoke <权限>[,<权限>]...
[on <对象类型><对象名>]
from<用户>[,<用户>]....
::::::::::::::::::::::::::::::::::::::::::触发器 trigger
定义:是一种特殊类型的存储过程,它通过实践出发而执行,而存储过程可以通过存储过程的名称被直接调用,
触发器使每个站点在数据修改时自动强制执行其业务规则,并且可以用于 sql server 约束,默认值,和规则的完整性检查
1 触发器 >首先是 被激活 条件判断后执行 不是直接执行的 事件之前 (激活)
==============================嵌入式sql
结构类型 :
方便与使用相同类型的几个字段重用 对表创建的时候
结构类型的继承 如: create yupe t under a ; t 继承a
表继承 create table b of of b under p b继承 p
引用类型 :一个类型的属性可以是对一个执行类型的对象的而引用
可以使用 call 调用 存储
===++++++++++++++++++系统开发与运行
开发模式 : 瀑布模式 演化模式 螺旋模式 喷泉模式
软件生存周期 :计划制定 (文档) 可行性分析报告 项目计划书
>需求分析 需求规格说明数
> 软件设计 概要设计说明书 和 详细设计说明数
> 程序编制(写代码) 源程序清单
＞测试 软件测试计划 软件测试报告
> 运行维护 使用说明书
项目管理知识 :
成本估算 :
开发费用 =人月数 x 每个人月的代价 或 开发费用 =源代码行数 X每行平均费用
代码的幻术估算
成本估算的模式 : 普特南 模型 和cocomo 模型等
风险分析 > 进度管理 任务有松弛时间 >人员管理
软件开发的方法 :
结构化 严格的开发程序 要求有完整的文档记录
面向对象 和 原型法
软件工具 开发环境
软件质量管理和质量保证
1 <功能, 可靠,易使用,效率 ,可移植 ,可维护
软件能力成熟的模式 CMM 5个级别
系统的分析
面向对象分析 OOA
(文档)
系统设计说明数
系统总体技术方案
1模块设计 >代码设计>输入设计>输出设计>数据库设计说明> 模型库及方法库设计
>网络设计>安全保密设计> 实施方案说明书
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
数据库的恢复 和日志有关 ()
并发 事务会造成破坏事务的隔离性和一致性 解决方法 :枷锁;
排它锁(修改) ,共享锁(只读) ,封锁协议
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>数据
库系统的审计 :可以很好的监视都是谁对数据库修改操作的情况 但是 有对性能上有影响
对数据库的备份计划的制定和实施 要求:
1.
根据数据变更情况,设计合理的备份周期和备份时间,最好是在业务量最小的时段进行备份
2.把事务日志文件保存在最稳定的存储设备上;
3.定期在事务日志文件中加入检查点
检查点记录了数据库的正确状态点,在数据库恢复过程中,可以反向扫描日志文件,找到第一个检查点,执行UNdo 和Redo 操作,减少恢复的时间开销
模糊查询时间方法: like to_date('2011-8-27','yyyy-MM-dd')
---------------------------------------
对于多个组分组基表不确定出现 重复的 基表重复列解决的方法 调用函数
声明 序列号列 : 最常用的分析函数 排名函数 : rank ,dense_rank ,row_number() ;
--1 一种写法
Row_number() over(partition by col1,col2 (可以是多列) order by col3 (可以是多列)) as rn
此方法记录当前所在的行号
2种写法 : --
rank () over(order by col1) as rn
此方法 的排名 具有"跳跃性 " 如 张三 ,李四 都是 排名第二 , 那么下一个紧挨的 王五, 是排名 第四
3. dense_rank() over(order by col1 ) as rn
此方法 的排名 不具有"跳跃性 " 排名同名次可以
------
分区窗体
创建分区窗体的 语法 : partition by
--窗体字句 追随 前后的信息
over ( order by 列名 rows between 位移量 preceding and 位移量 following )
案例 ::
selecT cp_name ,cp_id ,dense_rank() over(order by cp_id )as recodern,
sum(cp_id) over(order by cp_id rows between 1 preceding and 1 following ) sumcpid
from dict_cp_master
--------------------------------------------------------------------
4 fist_value () ,last_value() , lead() ,lag()
然后 查找是 加上 where 条件 : where rn=1
获取时间 小时 加 8 加的是时区
select extract(hour from systimestamp )+8 newdate from dual
------------------------------------------------------关于sql------------------------------------------------
--1.批量生成sql语句(检测表中主键字段作于是否含有空格)
select 'select * from ' || b.table_name || ' where ' ||'substr('||b.column_name||',0,1)='' '' ' || 'or substr('||b.column_name ||',length('||b.column_name||'),1)='' '''
from user_constraints a,user_cons_columns b
where a.CONSTRAINT_NAME = b.constraint_name
AND a.CONSTRAINT_TYPE = 'P'
AND b.constraint_name not like 'SYS%'
AND b.table_name IN (select TABLE_NAME from user_tables)
ORDER BY b.TABLE_NAME
--2.关于锁表问题的解决：
select * from v$locked_object ;--查看被锁定的数据，得到object_id在dba_objects中可以查看出来被锁定的表
select * from dba_objects where object_id ='119689';--可以看出来哪个表被锁住
select * from v$session where sid =371;--从v$locked_object得到sid
alter system kill session '371,274';--传入sid和serial值
--3.修改
a.修改约束(先增后删)：
b.修改表中子段类型:
b1.alter table code_cwxxb modify hlks varchar2(4);--将表code_cwwxxb中hlks字段类型改为:varchar2(4)
b2.修改char类型为varchar类型(使用转换的思想)--先改名,再添加,后赋值,最后删除
alter table zy_cnext_hf rename column hlks to hlks_back
alter table zy_cnext_hf a a.hlks varchar2(4)
update zy_cnext_hf a set a.hlks=(select trim(b.hlks_back) from zy_cnext_hf b where a.rowid=b.rowid)
alter table zy_cnext_hf drop column hlks_back
--4.关于oracle中的系统表：注意(对于字段区分大小写)
select * from user_tables --查出该登陆用户系统中的所有表
select * from user_tab_columns-- 查出登陆用户系统中所有表的列
select * from dba_tables --查询系统中所有的表
--5.关于系统表的比对：
--1>.缺少的表
SELECT C.*
FROM (SELECT A.TABLE_NAME A_TABLE,
B.TABLE_NAME B_TABLE
FROM (SELECT TABLE_NAME
FROM Dba_Tables
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) A
LEFT JOIN
(SELECT TABLE_NAME
FROM Dba_Tables
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) B
ON A.TABLE_NAME = B.TABLE_NAME) C
WHERE C.B_TABLE IS NULL
ORDER BY C.A_TABLE
--2>.缺少的视图或视图的内容不一致
SELECT C.*
FROM (SELECT A.VIEW_NAME A_VIEW,
A.TEXT_LENGTH A_TEXT_LENGTH,
B.VIEW_NAME B_VIEW,
B.TEXT_LENGTH B_TEXT_LENGTH
FROM (SELECT VIEW_NAME,TEXT_LENGTH
FROM DBA_VIEWS
WHERE OWNER = 'TPHIS_TH' --用标准库代替
) A
LEFT JOIN
(SELECT VIEW_NAME,TEXT_LENGTH
FROM DBA_VIEWS
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
) B
ON A.VIEW_NAME = B.VIEW_NAME) C
WHERE C.B_VIEW IS NULL OR C.A_TEXT_LENGTH <> C.B_TEXT_LENGTH
ORDER BY C.A_VIEW
--3>.缺少字段或类型不同或长度不同
SELECT C.* --DISTINCT C.A_TABLE
FROM (SELECT A.TABLE_NAME AS A_TABLE,
A.COLUMN_NAME AS A_COL,
A.DATA_TYPE AS A_TPYE,
A.DATA_LENGTH AS A_LEN,
B.TABLE_NAME AS B_TABLE,
B.COLUMN_NAME AS B_COL,
B.DATA_TYPE AS B_TPYE,
B.DATA_LENGTH AS B_LEN
FROM (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) A
LEFT JOIN
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TABLE_NAME NOT LIKE ('STU%')
AND TABLE_NAME NOT LIKE ('T_CON%')
AND TABLE_NAME NOT LIKE ('KC%')
AND TABLE_NAME NOT LIKE ('WJDM%')
AND TABLE_NAME NOT LIKE ('CWTJ%')) B
ON A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME=B.TABLE_NAME ) C
WHERE C.B_COL IS NULL OR (C.A_TPYE<>C.B_TPYE OR C.A_LEN <> C.B_LEN )
ORDER BY C.A_TABLE,C.A_COL
--4>.表中缺少存储
SELECT C.A_NAME, C.B_NAME
FROM (SELECT A.NAME A_NAME, B.NAME B_NAME
FROM (SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TYPE = 'PROCEDURE') A
LEFT JOIN
(SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TYPE = 'PROCEDURE') B
ON A.NAME = B.NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
--5>.表中缺少函数
SELECT C.A_NAME, C.B_NAME
FROM (SELECT A.NAME A_NAME, B.NAME B_NAME
FROM (SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_TH' --用标准库代替
AND TYPE = 'FUNCTION') A
LEFT JOIN
(SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_MC' --用被比较的库代替
AND TYPE = 'FUNCTION') B
ON A.NAME = B.NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
--6>表中存储或函数内容不一样
SELECT *
FROM (SELECT A.TYPE A_TYPE,
A.NAME A_NAME,
A.TEXT A_TEXT,
B.TEXT B_TEXT
FROM (SELECT TYPE,NAME, LINE, TEXT
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_DEV' --用标准库代替
AND ((TYPE = 'PROCEDURE') OR (TYPE = 'FUNCTION') OR (TYPE = 'TRIGGER') OR (TYPE='PACKAGE'))) A
LEFT JOIN
(SELECT TYPE,NAME, LINE, TEXT
FROM DBA_SOURCE
WHERE OWNER = 'TPHIS_LN' --用被比较的库代替
AND ((TYPE = 'PROCEDURE') OR (TYPE = 'FUNCTION') OR (TYPE = 'TRIGGER') OR (TYPE='PACKAGE'))) B
ON A.NAME = B.NAME AND length(replace(to_char(decode(A.TEXT,null,' ',A.TEXT)),' ',''))
=length(replace(to_char(decode(B.TEXT,null,' ',B.TEXT)),' ',''))) C
WHERE C.B_TEXT IS NULL
ORDER BY C.A_NAME,C.A_TEXT;
--7>表中缺少包
SELECT C.A_NAME, C.B_NAME
FROM (SELECT A.NAME A_NAME, B.NAME B_NAME
FROM (SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPEMR_TH' --用标准库代替
AND TYPE = 'PACKAGE') A
LEFT JOIN
(SELECT DISTINCT NAME
FROM DBA_SOURCE
WHERE OWNER = 'TPEMR_ZH0621' --用被比较的库代替
AND TYPE = 'PACKAGE') B
ON A.NAME = B.NAME) C
WHERE C.B_NAME IS NULL
ORDER BY C.A_NAME
--6系统函数：
substr(bmdm,0,1)--从第一位开始截取一位
substr(bmdm,length(bmdm),1)--从最后一位开始截取一位
--7.为表中特定的列添加说明
comment on column CODE_YPDM.CBBZ
is '0没有拆包1已拆包'
--8.关于表之间缺少字段或类型不同或长度不同
SELECT C.* --DISTINCT C.A_TABLE
FROM (SELECT A.TABLE_NAME AS A_TABLE,
A.COLUMN_NAME AS A_COL,
A.DATA_TYPE AS A_TPYE,
A.DATA_LENGTH AS A_LEN,
B.TABLE_NAME AS B_TABLE,
B.COLUMN_NAME AS B_COL,
B.DATA_TYPE AS B_TPYE,
B.DATA_LENGTH AS B_LEN
FROM (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPEMR_DEV' --用标准库代替
AND TABLE_NAME = ''
) A
LEFT JOIN
(SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE,DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPEMR_DEV' --用被比较的库代替
AND TABLE_NAME = ''
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME=B.TABLE_NAME ) C
WHERE C.B_COL IS NULL OR (C.A_TPYE<>C.B_TPYE OR C.A_LEN <> C.B_LEN )
ORDER BY C.A_TABLE,C.A_COL;
--实际应用比对tpemr与tphis同名字典表
SELECT C.* --DISTINCT C.A_TABLE
FROM (SELECT A.TABLE_NAME AS A_TABLE,
A.COLUMN_NAME AS A_COL,
A.DATA_TYPE AS A_TPYE,
A.DATA_LENGTH AS A_LEN,
B.TABLE_NAME AS B_TABLE,
B.COLUMN_NAME AS B_COL,
B.DATA_TYPE AS B_TPYE,
B.DATA_LENGTH AS B_LEN
FROM (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPHIS_DEV' --用标准库代替
AND TABLE_NAME in
(select table_name
from dba_tables
where owner = 'TPEMR_DEV'
and table_name like '%CODE%'
and table_name in
(select table_name
from dba_tables
where owner = 'TPHIS_DEV'
and table_name like '%CODE%'))) A
LEFT JOIN (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM Dba_Tab_Columns
WHERE OWNER = 'TPEMR_DEV' --用被比较的库代替
AND TABLE_NAME in
(select table_name
from dba_tables
where owner = 'TPEMR_DEV'
and table_name like '%CODE%'
and table_name in
(select table_name
from dba_tables
where owner = 'TPHIS_DEV'
and table_name like '%CODE%'))) B ON A.COLUMN_NAME =
B.COLUMN_NAME
AND A.TABLE_NAME =
B.TABLE_NAME) C
WHERE C.B_COL IS NULL
OR (C.A_TPYE <> C.B_TPYE OR C.A_LEN <> C.B_LEN)
ORDER BY C.A_TABLE, C.A_COL;
--9.关于数据库完整性sql
--eg：code_ypdm<规格和计量单位存在对应不一致>
select 'code_ypdm' table_name,
ypdm||'(ypdm) '||ypmc||'(ypmc) '||dw||'(dw) '||jldw||'(jldw) '||gg||'(gg) '||jlbl||'(jlbl) ' Error_Field,
'在规格和剂量单位的处理上可能存在问题' Error_Explain
from (select case when substr(gg, 1, 2) = '0.'
then substr(gg, 2, length(to_char(jlbl)))
else
substr(gg, 1, length(to_char(jlbl)))
end as gg0,
to_char(jlbl) as jlbl0,length(to_char(jlbl)),
gg,jlbl,ypdm,dw,jldw,ypmc
from (select replace(gg,' ','') as gg,ypdm,dw,jlbl,jldw,ypmc from code_ypdm))
where gg0 <> jlbl0 and ypdm not in (select ypdm from code_ypdm where dw = jldw and jlbl = 1)
--10.修改统计报表中名称字段长度
select 'alter table ' || table_name || ' modify ' || column_name || ' ' ||
data_type || '(' || data_length || ');'
from (select table_name,
column_name,
data_type,
(select data_length
from (select column_name, max(data_length) as data_length
from user_tab_columns
where table_name not like 'BIN%'
and column_name in
(select distinct column_name
from user_tab_columns
where table_name in (select table_name from user_tables where table_name like 'TJ%SHOW')
and column_name like '%MC')
group by column_name
order by column_name) a
where a.column_name = b.column_name) as data_length
from user_tab_columns b
where table_name in (select table_name from user_tables where table_name like 'TJ%SHOW') and column_name like '%MC'
order by table_name)
--11.解决操作员主键报错问题
select * from user_source where name='TRI_CODE_KSDM_INSERT'
select * from user_source where name='TRI_CODE_KSDM_UPDATE'
insert into his_dict_dept (dept_id,dept_name,input,clinic_attr,last_modify_time,outp_or_inp,sno)
select ksdm,ksmc,pydm,1,xgsj,mzzy,sxh
from code_ksdm
where ksdm not in (select dept_id from his_dict_dept);
--12.关于job的创建
declare job1 number;
begin
dbms_job.submit(job1,'PJ_CWSYQQ_SHOW2;',sysdate,'sysdate+1');
--(参数1:job号,参数2:存储名称,参数3:下次执行时间,参数4:每次执行的间隔时间)
end;
begin
dbms_job.remove(jobno); --删除
end;
begin
dbms_job.next_date(job,next_date); --修改下次执行时间
end;
begin
dbms_job.interval(job,interval); --修改间隔执行时间
end;
--13.在v$session表中显示ip信息以及触发器的创建
--1).trriger实现:
create or replace trigger on_logon_trriger
after logon on database
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
--2).查询当前登录客户端的机器名和ip地址
select machine,client_info from v$session where audsid=userenv('sessionid');
--3).授权用户实现v$session的使用(对用户tpsoft_lp1011分配访问v$session的权限)
grant select on v$session to tpsoft_lp1011
--4).创建实例:
--函数:
create or replace function fun_isdb_ipaddress
return varchar2 as
client_ipaddress varchar2(50);
begin
select client_info into client_ipaddress from v$session where audsid=userenv('sessionid');
return client_ipaddress;
end;
--关于添加的触发器:
create or replace trigger tri_sis_xtcs_log_insert
after insert on sis_xtcs
for each row
declare ip varchar(50);
mc varchar(50);
begin
select machine into mc from v$session where audsid=userenv('sessionid');
select client_info into ip from v$session where audsid=userenv('sessionid');
begin
insert into sis_xtcs_log(xtmk,csmc,xgpb,csz,mrz,bz,xgrdm,xgrmc,xglb,xgsj)
values(:new.xtmk,:new.csmc,:new.xgpb,:new.csz,:new.mrz,:new.bz,ip,mc,'INSERT',sysdate);
end;
end tri_sis_xtcs_log;
--关于删除的触发器:
create or replace trigger tri_sis_xtcs_log_delete
after delete on sis_xtcs
for each row
begin
begin
insert into sis_xtcs_log(xtmk,csmc,xgpb,csz,mrz,bz,xgrdm,xgrmc,xglb,xgsj)
values(:old.xtmk,:old.csmc,:old.xgpb,:old.csz,:old.mrz,:old.bz,'temp','temp','DELETE',sysdate);
end;
end tri_sis_xtcs_log;
--14.所有表空间的使用情况
select b.file_id 文件ID号,b.tablespace_name 表空间名,b.bytes/1024/1024||'M' 字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用,100-sum(nvl(a.bytes,0))/(b.bytes)*100 占百分比,
sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id
--15.查看用户默认的表空间
select a.username,a.default_tablespace from dba_users a
--3.查看要扩展的表空间使用的数据文件路径与名字
select * from dba_data_files where tablespace_name='TSP_TPHY'
--4.扩展表空间
alter tablespace TSP_TPHY
add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TPHY\TSP_TPHY1.DBF' size 500M
autoextend on
next 50M
maxsize 3000M
----------------------关联相应的表插入数据
merge into dict_cp_fhzdwjyyb jyyb
using(select * from(select dcv.cp_id , dcv.diag_code, Row_number() over(partition by dcv.diag_code order by dcv.diag_code) as rn from dict_cp_vs_icd10 dcv ) where rn=1 ) sour
on (jyyb.diag_no = sour.diag_code)
when matched then
update
set jyyb.cp_id = sour.cp_id
--------获取某表中的列数
select count(1) from user_col_comments where table_name =upper('his_patient_base')
--循环的使用
--语句块
declare v_id number :=0;
v_name varchar2(30) ;
begin
for v_id in 1..3 loop
select colls.table_name into v_name from (select tu.table_name ,
row_number() over(partition by tu.tablespace_name order by tu.table_name) rn
from user_tables tu
where tu.table_name like 'DICT_CP%') colls where colls.rn = v_id;
dbms_output.put_line(v_id || '测试' || v_name);
-- truncate table v_name; 不行
end loop;
end;
--测试 语句块
declare cursor cu_reurn is
select cp_name ,cp_id from dict_cp_master;
v_name dict_cp_master.cp_name%type;
v_id dict_cp_master.cp_id%type;
begin
open cu_reurn ;
fetch cu_reurn into v_name ,v_id;
while cu_reurn % found loop
dbms_output.put_line(v_name || ' 路径' || v_id);
fetch cu_reurn into v_name ,v_id ;
end loop;
close cu_reurn;
end ;
----------------------------------
select * from dba_users
create user test identified by a123456 default tablespace SYSTEM --创建用户
select * from dba_objects --查看对象 所有的
select sys_context('userenv','current_user') current_user,sys_context('userenv' ,'current_schema') current_schema from dual; --获取当前数据库 和用户
alter USER TPEMR ACCOUNT UNLOCK --解锁
select * from dba_sys_privs --系统 权限
select * from system_privilege_map -- 系统权限信息
--授权 grant 回收权限 revoke
--1
grant create session ,create table to dev_std with admin_option;
revoke create session ,create table to dev_std
--行变列\
create or replace function row2colunm(sqlString varchar2)
return varchar2 as
begin
declare type cu_type is ref cursor ;
tpm_cursor cu_type;
tmp_row varchar2(20);
_return varchar2(500);
begin
open tmp_cursor is sqlString ;
fetch tmp_cursor into tmp_row;
while tmp_cursor%found loop
_return := _return ||tmp_row ||',';
fetch tmp_cursor into tmp_row ;
end loop ;
return _return;
end
end row2colunm;
--测试
select row2colunm('select cp_name from dict_cP_master where rownum <10') reu from dual;
--包 package 就像是接口
select * from v$sgastat --查看系统全区
select namespace ,gets, gethits ,gethitratio ,pins,pinhits,pinhitratio from v$librarycache;
--修改 系统的缓存区的内存
alter SYSTEM set sga_max_size =1000m scope = both ;spfile ;
--索引
create index objindex on test_index(object_name);
create table test_index
as select * from dba_objects;
select * from test_index --0.031
select * from dba_objects --0.063
--索引 使用规则:
--不宜使用
-- 1. 数据量较小的表 2 . 有着频繁数据变更的表不宜使用索引
------------------
start with lei=' ' connect by prior mariket_id
--
管道的学习 ： pipelined ;
首先 声明 一个table 中所需的列 放在一个类型中； 再 定义一个table 对象 ， 最后使用管道；
如下 ：
1 、create or replace type maType_objType as object
(
医嘱类型编码 varchar2(20),
医嘱类型名称 varchar2(10),
拼音码 varchar2(10),
自定义码 varchar2(10)
)
2、
create or replace type maType_tableType as table of maType_objType
3、
create or replace function fun_get_maType
return maType_tableType pipelined
is
v_table_object maType_objType;
begin
for t_row in (select * from code_ma_type t order by t.ma_type_code asc)
loop
v_table_object := maType_objType(t_row.ma_type_code,t_row.ma_type_name,t_row.char_code,t_row.custom_code);
pipe row(v_table_object);
end loop;
return;
end;
-------------
alter table code_cwxxb modify hlks varchar2(4);
----------死锁解决方法 ：
“”
--2.关于锁表问题的解决：
select * from v$locked_object ;--查看被锁定的数据，得到object_id在dba_objects中可以查看出来被锁定的表
select * from dba_objects where object_id ='69684';--可以看出来哪个表被锁住
select * from v$session where sid =104;--从v$locked_object得到sid
alter system kill session '104,61103';--传入sid和serial值
- Oracle 温故
- 温故
- Python温故
- Python温故
- Python温故
- Python温故
- php 温故
- C 温故
- 温故 epoll
- 温故递归
- 温故一
- Java 温故
- 温故统计学
- Python温故
- 温故微分方程
- 温故离散数学
- Aop温故
- Oracle语法温故（字符串相关函数、数值相关函数、单行函数、多行函数）
- 如何利用Google机器人进行SQL攻击
- 音量调大时出现爆音的问题解决（也是从我的新浪博客转来的）
- Java 空指针异常（java.lang.NullPointerException）
- hibernate缓存
- NYOJ-2-括号配对问题-2013年09月09日09:32:18
- Oracle 温故
- servlet初探
- tcmalloc源码阅读(三)---ThreadCache分析之线程局部缓存
- json和jsonp的区别
- 删除 所有 .o.文件
- 注册表操作详解（二）
- 最短路水题大合集
- C++语法switch-case中的变量定义（很久在新浪博客的整理）
- UVa 10916 Factstone Benchmark
