Oracle数据库复习小点

Oracle数据库复习小点

Oracle执行命令显示命令运行结果之前要执行以下命令：

set serveroutput on;

表:

创建表:

create table table_name (
	column_name type primary key,
    ......
    ......
    foreign key (column_name) references table_name(column_name)
)

索引:

创建索引:

create index index_name on table_name(column_name)

删除索引:

drop index index_name

查询索引:

select * from user_ind_columns where index_name = 'column_name'

创建表、索引等的同义词:

create public synonym synonym_name for name

查询同义词:

select * from user_synonym

序列:

• start with(默认1)/ maxvalue/ nomaxvalue/ minvalue/ cycle(nocycle(默认))/ increment

create sequence sequence_name start with start_num maxvalue max_num cycle(默认为 nocycle(不可循环))
drop sequence sequence_name

alter sequence sequence_name minvalue min_num
alter sequence sequence_name maxvalue max_num
alter sequence sequence_name nomaxvalue
alter sequence sqquence_name increment by increment_num

用户操作:

create user user_name identified by password
alter user user_name identified by newPassword
grant connect, resource to user_name
grant create session to user_name
alter user user_name account lock/unlock
drop user user_name

部分函数使用：

rank()

• dense_rank()：数字排列连续
• rank()：数字排列不连续
• partition: （分类）分组 类似于group by

select rank() over(partition by column_name order by column_name desc) as new_name from table_name where ....

round(num, num_digits)

• 四舍五入处理函数
• num : 待处理数
• num_digits:保留几位小数

round(avg(分数)， 2) # 平均分保留2位小数

floor(num)

• 向下取整

floor(1.234) = 1

ceil()

• 向上取整

ceil(1.234) = 2

trunc(num, decimals)

• 有很多用法这里只介绍处理数的功能：与round类似，只是直接截取不会四舍五入
• num：待处理的数字
• decimals：指明保留几位小数，省略则保留到整数**（取整）**，还可以是负数，表示从小数点左边第n位起往右全部归0

trunc(100.56) = 100
trunc(100.56, 1) = 100.5
trunc(101.56, -1) = 100

nvl(column, replace_value)

• 替换column中的空值（NULL）为replace_value

lpad（str, n,pad_str）/ rpad（str, n,pad_str）

• 将str填充或删减到n的长度，lpad填充在左边，rpad填充在右边，删减都是从尾部开始删减
• str：待处理字符串
• n：结果字符串长度
• pad_str:用于填充的字符串

rpad(ename, 5, '*')

sqrt()

• 开平方计算

mod()

• 取模，与C/C++或java中的%号类似

Oracle相关函数列举：

存储过程：

• 创建
• 参数
• 变量：行变量、列变量、显式游标变量
• 分支
• 输出
• 抛异常

create or replace procedure procedure_name (params...)--参数（可选）（参数定义与变量定义格式相同）
as
-- 变量（可选）
var1 type;
var2 table_name.column_name%type;
row_var table_name%rowtype --行变量
cursor sursor_name is select_statement(声明、打开、提取、关闭)
begin
	var1 := some_value --赋值用:= 
	-- 变量赋值，参数可使用在where等后面充当条件
	select column_name into var1 from table_name where ....
	select * into row_var from table_name where .... 
	
	-- 抛异常：
	exception
		when no_data_found then
			-- do somthing;
			dbms_output.put_line('No data found!');
		when too_many_rows then 
			dbms_output.put_line('error!');
		when others then 
			dbms_output.put_line('sonthing wrong!')
	-- if分支：
	if var1 = target_value then
    	do something;
    elsif ...
    	do ...
    end if;
    
    -- case分支：
    case(var1)
    	when(target_value1) then do somthing;
    	when(target_value2) then do somthing;
    	...
    end case;
    
    -- 循环游标操作（for语句中的变量可以不用在首部声明）
    for cur in cursor_name loop
    	exit when cursor_name%notfound; -- 注意终止循环的条件
    	if some_conditions then
    		exit; --终止循环
    	end if;
    	if some_conditions then
    		continue; --跳过本次循环
    	end if;
    	if some_conditions then
    		return; --停止
    	end if;
    	dbms_output.put_line(....);
    end loop;
    close cursor_name;
	...
	
	-- 打印：
	dbms_output.put_line( var1 || ',' || var2 '\n' || row_var);
end;

存储函数：

• 创建
• 参数
• 变量
• 返回值

create or replace function fun_name (参数...) -- 参数可选,形式：param_name paramTpye...
return return_type as 
var_name var_type;
...
begin
	...--语法与存储过程一致，只是存储函数带返回值
	--循环样例
	for i in 1..value_n loop 
	...
	end loop;
	
	for row in (select * from table_name where ....) loop
	...
    end loop;
	return return_name;
end;

程序包：

• 包定义：

  包定义部分是为应用程序的接口，声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素

• 包主体：

  包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。如果在包主体中的游标或子程序并没有在包头中定义，那么这个游标或子程序是私有的

create or replace package package_name as
	procedure procedure_name(param1 type1, ...);
	function function_name(param1 type1, ...) return type;
	...
end;


create or replace package body package_name as
	-- 实现方法和过程
	function function_name(param1 type1, ...) return type as
	var_name var_type;
	...
	begin
		...
		return value;
	end;
	
	procedure procedure_name(param1 type1, ...) as
	var_name var_type;
	begin
		...
	end;

• 调用：

  select package_name.function_name(params...) (into value_var) from dual;
  select package_name.procedure_name(params...) (into value_var) from dual;
  ...
  

触发器：

• before、after
• and、or，of，on
• insert、update、delete
• for each row

create [or replace] trigger trigger_name 
before/after 触发事件（增删改任意组合） [of column_name] on table_name
[for each row]
var var_type;
cursor cursor_name is select_statment;
...
begin
	if something then
	raise_application_error(error_num, 'tip_string'); --error_num:错误值<0, tip_string 提示语句
	end if;
	--新值记录操作
	:new.column_name
	--旧值记录操作 
	:old.column_name
	...
end;