Oracle在项目开发中使用比较多,例如需要持久化,数据安全性,保证一致性等方面要求很高的项目使用更多,尤其是银行,证券,金融理财等方面;快速掌握Oracle相关的SQL进行存储过程,函数,触发器和连接等很有必要,其相关精简语法基础,力争几分钟复习完,大致如下:
一,Oracle的启动,关闭,启动连接,排序等一般操作;
//启动oracle数据库
(1)$ sqlplus "/ as sysdba" //windowsOS DOS下
(2)startup
//关闭oracle数据库
shutdown (normal | immediate | abort)
//连接oracle数据库
connect userName/password@oracleSID
//example
conn scott/tiger@oracleDB01
//oracle的sqlplus退出结束程序
exit; //退出并结束程序, quit与exit有相同功能;
return //仅仅用于返回,或退出循环,不结束程序;
//oracle 中的序列sequence创建
create sequence 序列名称 minValue 1 start with 1 increment by 1 nocache nocycle;
//oracle 中的序列sequence删除
DROP SEQUENCE 序列名称;
//使用sequence
select 序列名称.nextval from dual;
//sequence使用例子
create sequence mytb_id_seq minValue 1 start with 1 increment by 1 nocache nocycle;
select mytb_id_seq.nextval from dual;
DROP SEQUENCE mytb_id_seq;
//oracle的sqlplus中部分基础知识
/ 等同于 commit;
@ 以当前所在路径为相对起点,查找本级或以下的脚本,如果出现嵌套则以当前路径为起点;
@@ 不管如何嵌套,都以当前脚本所在路径为相对起点,可在本级或其下指定路径中运行某脚本;
&1, &2 表示脚本对应位置1和2处的需要输入的参数,等待用户输入的参数;
//Oracle的分页查询
//比如查询第 a 到 b 条记录(a < b),效率最高,推荐使用
SELECT * FROM (
SELECT T.*, ROWNUM RN
FROM (SELECT * FROM myTable [where 条件] [order by 排序字段]) T
WHERE RN <= b
)
WHERE RN >= a;
//比如查询第 pageNo 页,每页 pageSize 条记录,效率最高,推荐使用
select * from (
select T.*, ROWNUM rn (
select * from myTable [where 条件] [order by 排序字段]
) T where rn <= pageNo*pageSize
) where rn >= (pageNo-1)*pageSize
二,Oracle的存储过程建立删除等相关写法;
//创建DML存储过程需要CREATE procedure 系统权限,
//如下oracle中授予用户username任意表和存储过程(和函数)的创建权限
grant create any table to username;
grant create any procedure to username;
grant execute any procedure to username;
//创建
create or replace proc 存储过程名(param1 in int, param2 out varchar)
as
内部变量1 数据类型 := 默认值;
内部变量2 数据类型 := 默认值;
begin
//...存储过程体代码...
exception
when 异常标识1 then
//...相关代码1...
when 异常标识2 then
//...相关代码2...
when others then
//...其它异常相关代码3...
end;
end 存储过程名;
//调用过程
//调用过程
begin
declare
var1 int;
var2 varchar(200);
set var1 := 123;
myProcName(var1,var2);
end;
//或
execute myProcName(123,var2);
//删除存储过程
DROP PROCEDURE 存储过程名;
//新编译存储过程
ALTER PROCEDURE 存储过程名 COMPILE;
//例子
create or replace proc myProc01
is
var1 int;
var2 varchar(200);
begin
select count(1) into var1 from dual;
select to_char(sysdate,'yyyy-mm-dd') into var2 from dual;
DBMS_OUTPUT.put_line('total num:'||var1);
DBMS_OUTPUT.put_line('cur date:'||var2);
end;
//call
begin
myProc01
end;
---------------------------------------------------
create or replace proc myProc02(var1 in int)
is
var2 varchar(100)
begin
select c_name into var2 from mytb1 where id=var1;
DBMS_OUTPUT.put_line('店主名称:'||var2);
exception
when no_data_fount then
DBMS_OUTPUT.put_line('no data');
end;
//call
begin
myProc02(123);
end;
三,Oracle的函数建立删除等相关写法;
//创建DML存储过程需要CREATE procedure 系统权限
//如下oracle中授予用户username任意表和存储过程(和函数)的创建权限
grant create any table to username;
grant create any procedure to username;
grant execute any procedure to username;
//创建
create or replace function 函数名称(param1 in 数据类型, param2 out 数据类型)
return 返回数据类型
as
内部变量1 数据类型 := 默认值;
内部变量2 数据类型 := 默认值;
begin
//...函数体...
return 返回结果表达式(数据类型须和定义的返回数据类型一致)
exception
when 异常标识1 then
//...相关代码1...
when 异常标识2 then
//...相关代码1...
when other then
//...其它异常相关代码...
end;
end 函数名称;
//函数调用
declare
param2 数据类型;
param3 数据类型;
begin
param3 := 函数名称(param1,param2);
end;
或
execute ...函数名称(param1,param2);
//删除函数
DROP FUNCTION 函数名称;
//确定函数状态
SELECT object_name FROM user_objects
WHERE status='INVALID' AND object_type='FUNCTION' AND object_name='函数名称';
//编译函数
ALTER FUNCTION 函数名称 COMPILE;
//查看函数代码
SELECT text FROM user_source WHERE name='函数名称';
//例子
create or replace myFun1
return varchar2
as
begin
return to_char(sysdate,'YYYY-MM-DD HH:MI:SS');
end;
/
begin
DBMS_OUTPUT.put_line('current day time:' || myFun1);
end;
-----------------------------------------------
create or replace myFun2(param1 in varchar, param2 in out varchar)
return varchar2
as
var1 varchar := '';
var2 mytable.id%type := 0;
begin
select cel1 into var1 from mytable where cel2=param1;
return var1;
exception
when no_data_found then
DBMS_OUTPUT.put_line('no found data');
when others then
DBMS_OUTPUT.put_line('other exception');
end;
end;
/
declare
var1 varchar := '';
begin
DBMS_OUTPUT.put_line('output data:' || myFun2('u001',var1));
end;
四,Oracle的触发器建立删除等相关写法;
//创建DML触发器需要CREATE TRIGGER系统权限
grant create any trigger to username;
//创建
create or replace trigger 触发器名
{BEFORE|AFTER|INSTEAD [OF 某字段]} --触发器类型
(DELETE OR INSERT OR UPDATE) --触发条件
ON 表名 --作用的表
[for each row] --触发级别
[WHEN 触发条件] --注意:在WHEN条件中引用new和old不需要在前面加冒号:
DECLARE
声明部分 --触发器内部变量声明
begin
//...触发操作体...
if inserting then
//...insert触发代码...
elseif deleting then
//...delete触发代码...
elseif updating then
//...update触发代码...
else
//...else部分触发代码...
end if;
exception
when 异常标识1 then
//...异常代码1...
when 异常标识2 then
//...异常代码2...
when others then
//...其它异常代码...
end;
end;
//删除触发器
DROP TIRGGER 触发器名
//暂时关闭或重新打开
ALTER TRIGGER 触发器名 {DISABLE(失效)|ENABLE(生效)}
//例子
//例子1
create or replace trigger myTrig1
before
update of salary
on mytable
for each row
when (new.job='teacher' and (abs(new.salary-old.salary)) > 2000)
declare
vNo number;
begin
select count(*) into vNo from userLoger;
insert into userLoger(logSn,logText) values (vNo+1,
'雇员:'||:new.enName||'的原工资'||:old.salary||',新工资'||:new.salary);
end;
--------------------------------------
//例子2
create or replace trigger myTrig2
before
delete or insert or update
on mytable2
for each row
begin
if inserting then
insert into userlog(logSn,logText) values (logid_squ.nextval,
'insert,'||to_char(sysdate,'yyyy-mm-dd HH:mi:ss')||:new.userName);
elseif deleting then
insert into userlog(logSn,logText) values (logid_squ.nextval,
'delete,'||to_char(sysdate,'yyyy-mm-dd HH:mi:ss')||:old.userName);
else
insert into userlog(logSn,logText) values (logid_squ.nextval,
'update,'||to_char(sysdate,'yyyy-mm-dd HH:mi:ss')||:new.userName);
end if;
end;
五,Oracle相关的系统函数;
to_char() ,to_number() , 等等有待更新
OK,简要复习到此,后面有时间再逐步修改,欢迎拍砖...