oracle 10g
/****************************************************************
* 建立用户 用户权限 改变用户密码 锁定帐号及解锁 删除用户 *
*****************************************************************/
建立用户:
create user angeos identified by "angeospwd";
建立了用户：angeos，密码为：angeospwd
用户权限:
grant connect,resource to angeos;
对用户angeos授予了连接数据库和访问资源的权限
grant create session,dba to angeos;
CREATE SESSION是一个系统特权，它可以为用户提供连接数据库的能力。
DBA是一个具有超过120个系统特权的角色，所以它可以让用户在数据库中完成几乎任何工作。
改变用户密码:
alter user angeos identified by oracle;
将用户angeos的密码改变为：oracle.
锁定帐号及解锁:
??
删除用户:
drop user angeos;
由于用户angeos有一张表table1,所以删除用户时，我们需要指定关键字CASCADE
drop user angeos cascade;
oracle 9i 以下创建用户及权限分配:
Oracle-OraHome92-->Enterprinse Manager Console-->登陆数据库
SYS sys SYSTEM system (tiger)老虎 SYSDBA
建立用户 安全性-->用户-->新建(赋予权限)
[角色权限]
1:CONNECT 管理选项 默认值
2:DBA
3:RESOURCE
/****************************************************************
* 创建数据库 监听器 *
*****************************************************************/
创建数据库:
Datebase Configuration Assistant--->dbca
Net Configuration Assistant
-->监听程序配置
要从远程位置连接Oracle数据库,必须至少配置一个Oracle Net监听程序(监听外部)。
-->本地Net服务名配置
配置tnsnames.ora文件 按配置顺序介绍
1.orcl -->服务名(外部) 2. TCP -->访问协议 3. 192.0.50.103 -->访问主机IP(外部) 4.COAL_7GS -->本地设置的数据库名,可任意设置(内部)
COAL_7GS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.50.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
/****************************************************************
* 导入导出数据库 *
*****************************************************************/
导入导出数据库:
exp ysugwj/8508991@gs6db owner = ysugwj FILE = "D:\gwj.dmp"
imp benben1/841003@SYSZB file=D:\3.dmp fromuser=ysugwj touser=benben1
/****************************************************************
* Trigger -->序列自增 -->触发事件 *
*****************************************************************/
Trigger:
- - - - - - 序列自增 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1:新建--->序列
create sequence s_sys_USER1 --s_sys_USER1 序列名
minvalue 1 --最大值
maxvalue 999999999999 --最小值
start with 41 --初始值
increment by 1 --增量
cache 20 --高速缓存
order; --排序
2:在Triggers中可以见到触发
create or replace trigger TRI_SYS_USER --TRI_SYS_USER 触发器名
before insert on t_sys_user --t_sys_user 表名
for each row
declare
-- local variables here
begin
SELECT s_sys_USER1.NEXTVAL INTO :NEW.FID FROM DUAL; --s_sys_USER1 序列名 FID 递增列
end TRI_SYS_USER; --TRI_SYS_USER 触发器名
- - - - - - 触发事件 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
create or replace trigger TRI_SYS_USER
before insert on t_sys_user --before/after insert/update/delete
for each row
declare
-- local variables here
begin --:new.frlts新数据 :old.frlts老数据
if :new.frlts =0 then
:new.frlts:=null;
end if;
end TRI_SYS_USER;
/****************************************************************
* 存储过程 -->package -->package body *
*****************************************************************/
存储过程:
package -->声明 package body -->实现
任务提交 -->Commit; --DML需要提交(insert、update、delete) DDL会自动提交(create、alter ...)
- - - - package - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
create or replace package pag_ce_loadship_m is
type result is ref cursor;--当使用游标,需对其进行说明
Procedure frmShipMain_insert(li_fid Number,ls_fname Varchar2,ldt_ftime Date); --声明变量类型时无需指定大小
Procedure frmCbjjqd_delete; --无参数存储过程无需括号
Procedure p_gs_owner(ls_gsdm out varchar2); --返回结果集(当不写,默认为输入in)
Procedure qhdb(ls_fmonth Varchar2,myresult Out result); --返回myresult结果集
end pag_ce_loadship_m;
- - - - package body - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
create or replace package body pag_ce_loadship_m is
---------装船主表插入的功能-----
procedure frmShipMain_insert
(
li_fid Number, --输入参数,无需指定大小
ls_fname Varchar2,
ldt_ftime Date
)
Is
ls_fgsdm Varchar2(2); --声明变量 Varchar(?) Number(?) Char(?) Date
ls_fgsmc Varchar2(50);
li_fage Number(10);
ls_fcollege Varchar2(20);
select fage,ls_fcollege into li_fage,ls_fcollege from t_sysre_config; --变量赋值
pag_common.p_gs_owner(ls_fgsdm,ls_fgsmc); --调用存储过程给变量赋值，调用的存储过程也及是out两个varchar2
Begin
insert into t_ce_loadship_m(fid,fname,ftime) values(li_fid,ls_fname,ldt_ftime);
End frmShipMain_Add_Edit_insert;
---------装船主表删除的功能-----
procedure frmCbjjqd_delete
Is
begin
delete from t_ce_loadship_m where ldt_time = sysdate
end frmCbjjqd_delete;
---------out Varchar2-----
Procedure p_gs_owner(ls_gsdm out varchar2)
Is
Begin
Select fsubcompanynumvc Into ls_gsdm --out参数赋值
From t_sysre_config Where rownum=1;
End p_gs_owner;
---------游标返回结果集----
Procedure qhdb(ls_fmonth Varchar2,myresult Out result)
Is
...
Begin
...
Open myresult For --结果集赋值
select * from tb_table;
end qhdb;
end pag_ce_loadship_m; --package body结束
/****************************************************************
* 游标 *
*****************************************************************/
游标:
CREATE OR REPLACE Package pkg_gwj_report is
type result is ref cursor;
-------秦皇岛港煤炭调进统计同比报表查询的过程
Procedure qhdgmtdjtjtbbb(ls_fmonth Varchar2,myresult Out result);
end pkg_gwj_report;
procedure train_h_insert is
cursor mytrainno is ---游标赋值
select ftraincodevcr,ftrainno from t_ci_trainmain;
c_1 mytrainno%Rowtype; --对游标声明对象
cursor my_train_s(ls_lcbh varchar2) is --对游标赋参数
select * from t_ci_train_s where ftraincodevcr = ls_lcbh;
c_2 my_train_s%Rowtype;
li_row number;
begin
select count(*) into li_row from t_ci_trainmain;
if li_row=0 then
rollback; --回滚
return;
end if;
begin
open mytrainno; --打开游标
loop
fetch mytrainno into c_1; --插入c_1对象中 以后调用直接c_1.
Exit When mytrainno%Notfound; --当游标中没有数据 则Exit
select count(*) into li_row from ysuequ.ope_metage_target_head where date_no =c_1.ftrainno;
if li_row>0 then
select to_date(t.metage_date||' '||t.metage_time,'yyyy-mm-dd hh24:mi:ss')
into myfweighttime
from ysuequ.ope_metage_target_head t where date_no = c_1.ftrainno;
open my_train_s(c_1.ftraincodevcr); --赋参数
loop
fetch my_train_s into c_2;
Exit When my_train_s%Notfound;
......
end loop;
close my_train_s; --关闭游标
end if;
end loop;
close mytrainno;
exception
when others then
rollback;
return;
end;
Commit; --提交
end train_h_insert;
end pag_ci_train_h;
/****************************************************************
* view *
*****************************************************************/
View:
create or replace view v_base_b_itemtype as
--视图常用left/right/inner join ...on
select p.fid, p.fcode from t_base_b_itemtype p
left outer join t_sys_user u1 on u1.fid = p.fadduser
left outer join t_sys_user u2 on u2.fid = p.fmoduser
/****************************************************************
* Job *
*****************************************************************/
Job:
执行在指定时间或指定时间间隔执行某存储过程
begin
sys.dbms_job.submit(job => :job,
what => 'pkg_gwj_report.gwj_webf_ydjhldxqk();',
next_date => to_date('01-08-2009 08:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'add_months(trunc(sysdate),1)+8/24');
commit;
end;
--从01-08-2009 08:00:00开始 间隔 add_months(trunc(sysdate),1)+8/24
/****************************************************************
* oralce循环语句 *
*****************************************************************/
if--then--else--elsif--end if:
DECLARE --必须要有 声明变量
a number;
b varchar2(10);
begin
a:=2;
if a=1 then
b:='a';
elsif a=2 then
b:='b';
else
b:='c';
end if;
DBMS_OUTPUT.PUT_LINE('b is '||b); --输出
end;
case--when--then--else--end case:
DECLARE
a number;
b varchar2(10);
begin
a := 2;
case
when a = 1 then
b := 'a';
when a = 2 then
b := 'b';
when a = 3 then
b := 'c';
else
b := 'others';
end case;
DBMS_OUTPUT.PUT_LINE('b is' || b);
end;
- - - select中嵌套 - - - - 方法一 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
case to_char(p.fstate) when '0' then '未审核'
when '1' then '通过'
else '不通过'
end
- - - - - - - - - - - - - - - - - 方法二 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
case when fmatchcoalnum='0'
then '否'
else '是'
end
loop--exit--when--end loop:
- - - - - 方法一 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
declare
x number;
begin
x := 0;
loop
x := x + 1;
if x >= 3 then
exit;
end if;
dbms_output.put_line('内:x=' || x);
end loop;
dbms_output.put_line('外:x=' || x);
end;
- - - - - 方法二 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
declare
x number;
begin
x := 0;
loop
x := x + 1;
exit when x >= 3;
dbms_output.put_line('内:x=' || x);
end loop;
dbms_output.put_line('外:x=' || x);
end;
for--in(reverse)--loop--end loop:
- - - - - 如果指定了reverse选项，则循环控制变量会自动减1，否则自动加1 - - - - - - - - - - - - - - - - -
begin
for i in reverse 1 .. 5 loop
dbms_output.put_line('i=' || i);
end loop;
dbms_output.put_line('end of for loop');
end;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
for i in 1 .. 5 loop
dbms_output.put_line('i=' || i);
end loop;
SQL语法
/****************************************************************
* 基本语法 *
*****************************************************************/
基本语法:
select * from dual
insert into tb_table(...) values(...)
insert into tb_table(...) select * from dual --dual表信息插入tb_tab1e,需表中列相互匹配
update tb_table set ...
delete from tb_table
常用简单的关键字:
1.for update -->允许更新 2.is null / is not null -->判空 3.like 'abc%' / not like -->模糊查询
4.upper / lower -->大小写新 5.group by xxx having xxx
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
count avg(num), sum(num), max(num), min(num) -->平均,总和,最大,最小
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
nvl(row7,0) -->如果row7是null,则取0值
/****************************************************************
* distinct union random *
*****************************************************************/
distinct:
distinct -->distinct关键字被用作返回唯一的值 [与all相反]
union:
union -->合并两个SQL语句 (结果无重复)
union all -->(有重复)
random:
[oracle]
dbms_random.value() -->[0,1)
dbms_random.value(1,5000) -->[1,5000)
dbms_random.value('a',10) -->大于字符a的10个字符随机字符串
chr(round(dbms_random.value(97,122))) -->单个小写随机字符
[MS-SQL]
rand() -->[0,1)
/****************************************************************
* in exists *
*****************************************************************/
in:
select * from table1 a where a.name in (select b.name from table2 b)
--执行的过程相当于
select * from table1 a left join table2 b on b.name=a.name
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
exists:
select * from table1 a where exists (select null from table2 b where b.name=a.name)
--执行的过程相当于
for a.name in ( select * from table1 a)
loop
if (exists (select null from table2 where b.name=a.name) then
OUTPUT THE RECORD
end if
end loop
2.请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询) -->任何值X not in (null) 结果都不成立
/****************************************************************
* 拆分字符串 数字字符串加减 between *
*****************************************************************/
拆分字符串:
[oracle]
substr -->substr("user",1,3) --从第1位起，数3位的字符
substr -->substr("user",4) --从第4位起，所有后面的字符(包括第4位)
[MS-SQL]
substring -->用法同上
数字字符串加减:
[oracle]
数字 --> + - * / -->select t.fid + t.fid from t_sys_userip t
字符串 --> concat || -->select concat('010-','88888888')||'转23' tel from dual --010-88888888转2
[MS-SQL]
数字,字符串相加 +
/****************************************************************
* round/trunc case *
*****************************************************************/
round/trunc:
round(row1,num) --四舍五入
trunc(row1,num) --不四舍五入
- - - - - eg - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
round(1.5469,2) --1.55 trunc(289.2,-1) --280 trunc(289.2) --289
trunc(sysdate) 2009-7-2 显示当前日期
trunc(sysdate,'mi') 2009-7-2 10:58:00 限定到分 'yy-mm-dd hh24:mi:ss'
/****************************************************************
* [MS-SQL]类型转换 *
*****************************************************************/
[MS-SQL]类型转换:
进行各个类型转化,本人常用 cast()
cast(343.725 as varchar)
cast(substring(convert(varchar,(endtime-starttime),108),1,2) as int)
cast(convert(CHAR(10),CURRENT_TIMESTAMP,102) as datetime) --cast('2004-09-12 11:06:08' as datetime)
- - - - - 时间转换常用 convert() - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CONVERT(varchar(12), getdate(), 120 ) 2004-09-12 11:06:08
replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\') 20040912110608
CONVERT(varchar(12) , getdate(), 111 ) 2004/09/12
CONVERT(varchar(12) , getdate(), 112 ) 20040912
CONVERT(varchar(12) , getdate(), 102 ) 2004.09.12
CONVERT(varchar(12) , getdate(), 101 ) 09/12/2004
CONVERT(varchar(12) , getdate(), 103 ) 12/09/2004
CONVERT(varchar(12) , getdate(), 104 ) 12.09.2004
CONVERT(varchar(12) , getdate(), 105 ) 12-09-2004
CONVERT(varchar(12) , getdate(), 106 ) 12 09 2004
CONVERT(varchar(12) , getdate(), 107 ) 09 12, 2004
CONVERT(varchar(12) , getdate(), 108 ) 11:06:08
CONVERT(varchar(12) , getdate(), 109 ) 09 12 2004
CONVERT(varchar(12) , getdate(), 110 ) 09-12-2004
CONVERT(varchar(12) , getdate(), 113 ) 12 09 2004
CONVERT(varchar(12) , getdate(), 114 ) 11:06:08.177
/****************************************************************
* [oracle]类型转换 [oracle]时间间隔 *
*****************************************************************/
[oracle]类型转换:
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
to_date('2006-05-01','yyyy-mm-dd hh24:mi:ss')
to_number()
[oracle]时间间隔:sysdate -->2009-7-31 17:16:12
select sysdate + interval '1' hour from dual -->2009-7-31 18:16:12 (year,month,day,hour,minute,second)
select trunc(sysdate) + 1 from dual -->2009-8-1 17:16:12
select sysdate - 5/(24*60) from dual -->2009-7-31 17:11:12
--trunc(sysdate,'mi') 2009-7-31 17:16:00 限定到分 'yy-mm-dd hh24:mi:ss'
/****************************************************************
* next_day last_day add_months *
*****************************************************************/
next_day:
select next_day(sysdate,2) from dual
select next_day(sysdate,'MONDAY') from dual
--如果今天是星期二,如果要查询下一个 星期二,则为下周的星期二;如果要查询下一个星期三,要注意,返回值是这个周的星期三
SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
1 2 3 4 5 6 7
last_day:
--函数用于所属月份的最后一天
select last_day(sysdate) from dual -->2009-7-31 11:04:39
select last_day(trunc(sysdate)) from dual -->2009-7-31
add_months:
add_months(sysdate,3) -->当前时间+3个月
369
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
