oracle 常用sql
--###################################################################################
/*
版本号: 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操作的结果;
而引起事物的提交或结束原因有:
.DTM操作: commit,rollback,savepoint
.系统崩溃宕机: 隐式rollback
.正常: 隐式commit
.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 --
解决注入攻击的手段:
. 不拼接字符串, 有字符串操作时, 要留心.
. 使用参数进行赋值, 尽量使用强类型.
. 对用户输入的字符进行过滤.
. 若非用串的话(如:用户名登陆过程), 可以用代码先到数据库中查一下是否有这个名字, 没有则禁止操作
*/
--###################################################################################
--查询系统资源:
--常用命令
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 inner join 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
create user TestUser identitied by TestUser account unlock
grant create session to TestUser
create role TestUserRole
grant TestUserRole to scott
grant create session, create view, resource, connect to TestUserRole --通常给的用户权限
grant resource, connect to scott
drop user TestUser
revoke create seeion from hr
drop role TestUserRole
revoke resource,connect from TestUserRole
alter user hr identitied by tiger account unlock
--###################################################################################
--创建表, 约束类型: 主键、外键、唯一、check约束、非空约束
-- 注意事项: 序列不保证连续、关键字不必要连续、业务数据不适合作为关键字
drop table T_Event;
drop sequence seq_T_Event;
create sequence seq_T_Event start with 1 increment by 1;
create table T_Event(
seqid number(8),
constraint pk_T_Event primary key(seqid), --单独写约束, 可以方便添加联合主键(在seqid后便添加即可)
title varchar2(200) constraint nn_T_Event_title not null,
startdate date constraint nn_T_Event_startdate not null,
enddate date constraint nn_T_Event_enddate not null,
constraint ck_T_Event_enddate check(enddate > startdate),
detail varchar2(500),
userid number(8),
constraint fk_T_Event_userid foreign key(userid) references T_UserInfo(seqid)
)
drop table T_UserInfo;
drop sequence seq_T_UserInfo;
create sequence seq_T_UserInfo start with 1 increment by 1;
create table T_UserInfo
(
seqid number(8),
constraint pk_T_UserInfo primary key(seqid),
username nvarchar2(30) constraint nn_T_UserInfo_username not null,
constraint uq_T_UserInfo_username unique(username),
create_data date default sysdate
)
--修改表结构
alter table T_Event
add userid number(8)
alter table T_Event
drop column test
--修改表约束
alter table T_Event
add constraint fk_T_Event_userid foreign key(userid) references T_UserInfo(seqid)
--###################################################################################
--创建视图
create or replace view View_DepEmp
as
select last_name Given_Name,salary
from employees e inner join departments d on e.department_id = d.department_id
--###################################################################################
--新增(插入)数据(DML操作, 在commit之后才会提交)
insert into 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');
insert into 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
insert into T_UserInfo(seqid,username)
values(seq_T_UserInfo.nextval,'admin');
insert into T_UserInfo(seqid,username)
values(seq_T_UserInfo.nextval,'user');
commit
--删除数据
truncate table T_Event --只删除表的数据
delete from T_Event where title = '喝酒'
commit
--修改数据
update T_UserInfo set username = 'Client' where username='user'; --注意set后边仍为=
commit
--###################################################################################
--查询数据
select distinct 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 >= 8000 and salary <= 10000
select first_name,last_name,salary from employees where salary between 8000 and 10000
select department_id, job_id from employees where department_id is null
--模糊查询呢: 通配符: %(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
order by 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
order by department_name
)
)
where rn between 11 and 20 --rownum根据结果集来生成, 一直没有rownum=1的记录, 所以得不到11的记录
--单行子查询: =、>、<
select last_name,salary
from employees
where salary > (select avg(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)
--###################################################################################
--排序
select distinct department_id, job_id from employees order by job_id desc --默认是升序asc
--###################################################################################
--聚合函数
select sum([sid]) from T_StuScore
select count([sid]) from T_StuScore --count(*)表示记录数, 而count(字段)忽略掉null值
select avg([sid]) from T_StuScore
select max([sid]) from T_StuScore
select min([sid]) from T_StuScore
select distinct(department_id), job_id from employees
select distinct department_id, job_id from employees
--###################################################################################
--分组函数, where用于对记录的筛选, having用于对组的筛选, 并且组函数将忽略结果为null的字段
select gender,Counter = count(*) from T_Stuinfo group by gender
select gender,Counter = count(*) from T_Stuinfo group by gender having count(*) >= 2
--###################################################################################
--表连接: 笛卡尔积(m*n条记录), 内连接, 外连接(左外连接、右外连接、全外连接), 自连接
--内连接: 先从m和n中选择, 然后再连接
select sname,sid,cid,score
from T_StuInfo s inner join T_StuScore c on s.sid = c.sid
--左外连接(左连接): 内连接 + 左表剩余记录(右表记录置null)
select sname,sid,cid,score
from T_StuInfo s left join T_StuScore c on s.sid = c.sid
--右外连接(右连接): 内连接 + 右表剩余记录(左表记录置null)
select sname,sid,cid,score
from T_StuInfo s right join T_StuScore c on s.sid = c.sid
--全外连接(全连接): 内连接 + 左表剩余记录(右表记录置null) + 右表剩余记录(左表记录置null)
select sname,sid,cid,score
from T_StuInfo s full outer join T_StuScore c on s.sid = c.sid
--自连接(全连接): 本表与本表连接, 可以是外连接或内连接
select sname,sid,cid,score
from T_StuInfo s inner join 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('用户名和密码不匹配! ');
end if;
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
exit when 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
when 0 then dbms_output.put_line('男');
when 1 then dbms_output.put_line('女');
else dbms_output.put_line('暂时未知');
end case;
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 is select * from T_UserInfo; --1. 定义游标
begin
open v_cs; --2. 打开游标
loop
fetch v_cs into v_row; --3. 从游标读取数据
exit when 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;
exit when 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 := '准时到, 备好红包';
insert into T_UserInfo(seqid,username) values(seq_T_UserInfo.nextval,v_username);
insert into 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; --即使程序没有错误, 也可以手动抛出异常
insert into T_UserInfo(seqid,username) values(seq_T_UserInfo.nextval,'王五');
insert into 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 then rollback;dbms_output.put_line('错误: 没有找到数据'); --错误号:-1403
when too_many_rows then rollback;dbms_output.put_line('错误: 返回了多行数据! '); --错误号为: -1422
when invalid_cursor then rollback;dbms_output.put_line('错误: 无效的游标 ');
when zero_divide then rollback;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%
create index 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用来存储程序块
--带参数的
create or replace procedure SP_ShowFibonacci(
p_num in number
)
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
exit when 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用于返回刚刚插入的记录
create or replace procedure 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 in number
)
is
--v_pkid number(8);
begin
--v_pkid := seq_t_event.curral; --暂存currval
insert into 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;
--带输入参数和系统引用输出游标
create or replace procedure SP_GetEVentByDate(
p_date in date,
p_userid in number,
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;
exit when v_cs%notfound;
dbms_output.put_line(v_title || v_startdate || v_enddate || v_detail || v_userid);
end loop;
close v_cs; --关闭游标
end;
--通过存储过程插入数据
create or replace procedure 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 >0 then
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; --以上表达式的另外一种写法
insert into calender(dateid,dateitem,itemcount)
--values(seq_calender.nextval, p_date, 1);
values(v_calender_dateid,p_date, 1);
end if;
-- 在NoticeItem表中增加活动的内容
insert into 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;
--###################################################################################
--函数: 与存储过程相比, 函数必须有返回类型, 内部应只对数据进行运算, 避免在函数中处理记录--用来存储程序块
create or replace function 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操作的触发器
create or replace trigger tg_noticeitem
after delete on 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 > 1 then
update calender
set itemcount = itemcount -1
where dateid = :old.dateid;
else--注意elsif的写法
delete from calender
where dateid = :old.dateid;
end if;
end tg_noticeitem;
--创建监视表
drop table T_Monitor
create table T_Monitor(
table_name nvarchar2(30),
constraint pk_T_monitor primary key(table_name),
count number(8),
create_date date default(sysdate)
)
insert into T_Monitor(table_name,count) values('T_EVENT',0)
commit
create or replace trigger tg_monitor --触发器没有参数
after insert or delete or update on 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('更新操作! ');
end if;
update T_Monitor set count = count + 1
where table_name = 'T_EVENT';
end tg_monitor;
浙公网安备 33010602011771号