Oracle常用Sql语句
1. 创建视图：
CREATE OR REPLACE VIEW SM_V_UNIT_AUTH AS
SELECT T2.UNIT_ID,
T2.SUPER_UNIT_ID,
T1.AUTH_ID,
T1.AUTH_NAME,
T1.AUTH_TYPE,
T1.AUTH_DESC,
T1.CREATE_UNIT,
T1.CREATE_DATE,
(SELECT UNIT_NAME from SM_T_UNIT C WHERE C.UNIT_ID=T1.CREATE_UNIT) CREATE_UNIT_NAME,
T1.VALID_FLAG,
T1.UNIT_NAME,
T1.EDITABLE,
T1.AUTHABLE
from
(SELECT A.*,
B.UNIT_ID,
B.UNIT_NAME,
(SELECT UAR1.EDITABLE from SM_T_UNIT_AUTH_REL UAR1 WHERE UAR1.AUTH_ID = A.AUTH_ID
AND UAR1.UNIT_ID = B.UNIT_ID) AS EDITABLE,
(SELECT UAR1.AUTHABLE from SM_T_UNIT_AUTH_REL UAR1 WHERE UAR1.AUTH_ID = A.AUTH_ID
AND UAR1.UNIT_ID = B.UNIT_ID) AS AUTHABLE from SM_T_AUTH A,SM_T_UNIT B
WHERE A.AUTH_ID IN (SELECT UAR1.AUTH_ID from SM_T_UNIT_AUTH_REL UAR1
WHERE UAR1.UNIT_ID = B.UNIT_ID)
ORDER BY A.AUTH_NAME) T1,SM_T_UNIT T2 where T1.UNIT_ID=T2.UNIT_ID
;
==========================================================================================
==========================================================================================
2. 创建存储过程：
create or replace procedure sm_sp_update_unitAuthRel is
cursor cur_unitAuthRels is select * from sm_t_unit_auth_rel uar where uar.editable = 'F';
v_unitAuthRel sm_t_unit_auth_rel%rowtype;
v_today varchar2(10);
v_validdate varchar2(10);
begin
open cur_unitAuthRels;
v_today :=to_char(sysdate,'yyyy-mm-dd');
loop
&
相关文档：
DML:Data Manipulation Language 数据操作语言
包括：CRUD
1. insert语句
(1) 从其它表中复制数据,实现方法:在insert 语句中加入查询语句
insert into sales_reps(id,name,salary,commission_pct) select employee_id,last_name,salary,commission_pct
from employees where job_id like '%rep';
(2) up ......
以上为转载,多数并未验证.
其中,select trunc(sysdate,'dd') from dual,得到的是: 2009-3-23 上午12:00:00 ,与下文并不一样!
oracle trunc()函数的用法
1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date 一个日期值
fmt 日期格式，该日期将 ......
一、设置初始化参数 job_queue_processes
sql> alter system set job_queue_processes=n;（n>0）
job_queue_processes最大值为1000
查看job queue 后台进程
sql>select name,description from v$bgprocess;
二，dbms_job package 用法介绍
包含以下子过程：
......
数据字典dict总是属于Oracle用户sys的。
1、用户：
select username from dba_users;
改口令
alter user spgroup identified by spgtest;
2、表空间：
select * from dba_data_files;
select * from dba_tablespaces;//表空间
select tablespace_name,sum(bytes), sum(b ......
查询及删除重复记录的SQL语句
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据 ......
