ORACLE Procedure 存储过程语法 - ...-Oracle面试题 - 蒋叶湖-4、总结：基于Oracle Logminer数据同步

当前位置: 数据库>oracle

本页文章导读:

▪ORACLE Procedure 存储过程语法 - 上善若水. 子程序和程序包总结：子程序是命名的PL/SQL块，可带参数并可在需要时随时调用。PL/SQL有两种类型的子程序，即过程和函数。过程用于执行特定的任务，函数用于执行任务并返回值。程序包是.........

▪Oracle面试题 - 蒋叶湖 1.对字符串操作的函数?答：ASCII() –函数返回字符表达式最左端字符的ASCII 码值CHR() –函数用于将ASCII 码转换为字符–如果没有输入0 ~ 255 之间的ASCII 码值CHR 函数会返回一个NULL 值&ndas.........

▪4、总结：基于Oracle Logminer数据同步 - shishanyuan 最近开发Oracle数据同步功能，做了些调研和验证，这个工作过去有段时间，怕时间长了忘记，故用博客共享出来。在这系列中共写了三篇文章，第一篇是写LogMiner配置及使用，第二篇是测试了L.........

[1]ORACLE Procedure 存储过程语法 - 上善若水.

来源: 发布时间: 2013-10-29

子程序和程序包
总结：
子程序是命名的PL/SQL块，可带参数并可在需要时随时调用。
PL/SQL有两种类型的子程序，即过程和函数。
过程用于执行特定的任务，函数用于执行任务并返回值。
程序包是对相关类型、变量、常量、游标、异常、过程和函数的封装。
程序包由包规范和包主体两部分组成。
包规范是包的接口，包含公用对象及其类型。
包主体实现包规范中的游标和子程序，包主体中的声明仅限于在包内使用。
程序包中游标的定义分为游标规范和游标主体两部分。

语法及示例：
1、存储过程
创建存储过程的语法：
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_list)]
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [procedure_name];
其中：procedure_name是过程的名称。
parameter_list是参数列表。
local_declarations是局部声明。
executable_statements是可执行语句。
exception_handlers是异常处理程序。
示例1：演示创建过程(参数列表中为IN参数赋予一个默认值，不能为OUT、IN OUT参数赋予默认值)
create or replace procedure find_emp(emp_no in number:=7900)
as
empname varchar2(20);
begin
select ename into empname from emp where empno=emp_no;
dbms_output.put_line('雇员姓名是 '||empname);
exception
when no_data_found then
dbms_output.put_line('雇员编号未找到');
end find_emp;

调用过程：EXECUTE procudure_name(parameters_list);
也可以在过程里面调用，直接写上procudure_name而不必写EXECUTE。

示例2：演示创建带OUT参数的过程
create or replace procedure test(value1 varchar2,value2 out number)
is
identity number;
begin
select sal into identity from emp where empno=value1;
if identity<2000 then
value2:=1000;
else
value2:=500;
end if;
end;

调用带OUT参数的过程：
declare
value2 number;
begin
test('7900',value2);
dbms_output.put_line(value2);
end;

示例3：演示创建带IN OUT参数的过程
create or replace procedure swap(p1 in out number,p2 in out number)
is
v_temp number;
begin
v_temp:=p1;
p1:=p2;
p2:=v_temp;
end;

调用带IN OUT参数的过程：
declare
num1 number:=100;
num2 number:=200;
begin
swap(num1,num2);
dbms_output.put_line('num1= '||num1);
dbms_output.put_line('num2= '||num2);
end;

示例4：将过程的执行权限授予其他用户
GRANT EXECUTE ON find_emp TO scott;
GRANT EXECUTE ON swap TO PUBLIC;
将find_emp过程的执行权限授予给用户scott，将执行swap过程的权限授予所有数据库用户。

删除过程语法：DROP PROCEDURE procudure_name;

2、函数
定义函数的语法如下：
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_list)]
RETURN datatype
{IS|AS}
[local_declarations]
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [function_name];
其中：function_name是函数的名称。
parameter_list是参数列表。
local_declarations是局部声明。
executable_statements是可执行语句。
exception_handlers是异常处理程序。
使用函数时注意：形式参数必须只使用数据库类型，不得使用PL/SQL类型。函数的返回类型也必须是数据库类型。
函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。
示例5：演示如何创建函数
create or replace function fun_hello
return varchar2 is
begin
return '朋友，您好';
end;
调用函数：select fun_hello from dual;

函数的授权：同过和的授权一样具体请看示例4。
删除函数：DROP FUNCTION function_name

过程和函数的差异
过程函数
作为PL/SQL语句执行作为表达式的一部分调用
在规范中不包含RETURN子句必须在规范中包含RETURN子句
不返回任何值必须返回单个值
可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN语句

3、程序包
创建包规范的语法：
CREATE [OR REPLACE] PACKAGE package_name
IS|AS
[Public type and item declarations]
[Subprogram specifications]
END [package_name];
其中：package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram specifications声明PL/SQL子程序。

示例6：演示创建程序包规范
create or replace package pack_op is
procedure pro_print_ename(id number);
procedure pro_print_sal(id number);
function fun_re_date(id number) return date;
end;

创建包主体的语法：
CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
[Public type and item declarations]
[Subprogram bodies]
[BEGIN
Initialization_statements]
END [package_name];
其中：package_name是包的名称。
Public type and item declarations是声明类型、常量、变量、异常和游标等。
Subprogram bodies是定义公共和私有PL/SQL子程序。

示例7：演示创建程序包主体
create or replace package body pack_op is
procedure pro_print_ename(id number) is
name emp.ename%type;
begin
select ename into name from emp where empno=id;
dbms_output.put_line('职员姓名：'||name);
end pro_print_ename;
procedure pro_print_sal(id number) is
salary emp.sal%type;
begin
select sal into salary from emp where empno=id;
dbms_output.put_line('职员工资：'||salary);
end pro_print_sal;
function fun_re_date(id number) return date is
bedate emp.hiredate%type;
begin
select hiredate into bedate from emp where empno=id;
return bedate;
end fun_re_date;
end pack_op;

示例8：调用程序包中创建的过程和函数
exec pack_op.pro_print_ename(7900);
exec pack_op.pro_print_sal(7900);
select pack_op.fun_re_date(7900) from dual;

示例9：演示程序包中的游标
创建包规范
create or replace package pack_emp is
cursor cur_emp return emp%rowtype;
procedure pro_cur;
end pack_emp;

创建包主体
create or replace package body pack_emp is
cursor cur_emp return emp%rowtype is
select * from emp;
procedure pro_cur is
rec_emp emp%rowtype;
begin
open cur_emp;
loop
fetch cur_emp into rec_emp;
exit when cur_emp%notfound;
if rec_emp.sal<1000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，需加倍努力争取提高工资');
elsif rec_emp.sal>=1000 and rec_emp.sal<2000 then
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资一般，争取搞个部门经理做做');
else
dbms_output.put_line('员工工资：'||rec_emp.sal||'，工资不错，争取搞个总经理做做');
end if;
end loop;
end pro_cur;
end pack_emp;

调用程序包中的过程以调用程序包中的游标
exec pack_emp.pro_cur;

示例10：存储过程返回游标的子程序包(此程序包返回r_cur游标)
CREATE OR REPLACE package SCOTT.pk_wt
is
type mytype is ref cursor;
procedure p_wt(mycs out mytype);
end;

CREATE OR REPLACE package BODY SCOTT.pk_wt
is
procedure p_wt(mycs out mytype)
is
r_cur mytype;
begin
open r_cur for select * from emp;
mycs:=r_cur;
end p_wt;
end pk_wt;

查询有关过程、函数和程序包的信息：USER_OBJECTS数据字典视图
column object_name format a18
select object_name,object_type from user_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY');

注意事项：

1，存储过程参数不带取值范围，in表示传入(只能位于等号右边)，out表示输出(只能位于等号左边),in out表示该类型既能被赋值也能传值;

类型可以使用任意Oracle中的合法类型。

2，变量带取值范围，后面接分号

3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录

4，用select 。。。into。。。给变量赋值

5，在代码中抛异常用 raise+异常名

6，用select时，后面必须接into字段，在游标和循环里面就另当别论了

7，在package里，不能create or replace

8，不写in，out，默认为in

本文链接：http://www.cnblogs.com/load/archive/2013/06/04/3118077.html，转载请注明。

[2]Oracle面试题 - 蒋叶湖

来源: 发布时间: 2013-10-29

1.对字符串操作的函数?
答：ASCII() –函数返回字符表达式最左端字符的ASCII 码值
CHR() –函数用于将ASCII 码转换为字符
–如果没有输入0 ~ 255 之间的ASCII 码值CHR 函数会返回一个NULL 值
–应该是必须给chr()赋数字值
concat(str,str)连接两个字符串
LOWER() –函数把字符串全部转换为小写
UPPER() –函数把字符串全部转换为大写
LTRIM() –函数把字符串头部的空格去掉
RTRIM() –函数把字符串尾部的空格去掉
TRIM() –同时去掉所有两端的空格
实际上LTRIM(),RTRIM(),TRIM()是将指定的字符从字符串中裁减掉
其中LTRIM(),RTRIM()的格式为xxxx(被截字符串，要截掉的字符串)，但是TRIM()的格式为TRIM(要截掉的一个字符 from 被截的字符串)
SUBSTR() –函数返回部分字符串
INSTR(String,substring) –函数返回字符串中某个指定的子串出现的开始位置，如果不存在则返回0
REPLACE(原来的字符串，要被替换掉的字符串，要替换成的字符串)
SOUNDEX() –函数返回一个四位字符码
–SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回NULL 值

2、事务概念
答案：事务是这样一种机制，它确保多个SQL语句被当作单个工作单元来处理。事务具有以下的作用：
* 一致性：同时进行的查询和更新彼此不会发生冲突，其他用户不会看到发生了变化但尚未提交的数据。
* 可恢复性：一旦系统故障，数据库会自动地完全恢复未完成的事务。
3、oracle中查询系统时间
答：select sysdate from dual
4、触发器的作用，什么时候用触发器,创建触发器的步骤，触发器里是否可以有commit, 为什么?
答案：触发器是可以由事件来启动运行的，存在于数据库服务器中的一个过程。
他的作用：可以实现一般的约束无法完成的复杂约束，从而实现更为复杂的完整性要求。
使用触发器并不存在严格的限定，只要用户想在无人工参与的情况下完成一般的定义约束不可以完成的约束，来保证数据库完整性，那么就可以使用触发器。
由于触发器主要是用来保证数据库的完整性的，所以要创建一个触发器，首先要明确该触发器应该属于那一种（DML，INSTEAD OF，SYSTEM）因为他们各有个的用途；其次就是要确定触发器被触发以后所设计到的数据。
出发器中不可以使用COMMIT。
5．数字函数
abs()绝对值exp(value)e的value次幂ceil()大于等于该值的最小整数floor()小于等于该值的最大整数trunc(value,precision)保留precision个小数截取valueround(value,precision)保留precision个小数对value进行四舍五入 sign()根据值为正负零返回1，-1，0mod()取模操作power(value,exponent)value的exponent次幂sqrt()求平方根
1.初级
4、关系数据库系统与文件数据库系统的区别在那里?关系数据库系统一般适用那些方面?
答案：
关系数据库系统文件系统的区别在于：
首先，关系性数据库的整体数据是结构化的，采用关系数据模型来描述，这是它与文件系统的根本区别。（数据模型包括：数据结构，数据操作以及完整性约束条件）
其次，关系数据库系统的共享性高，冗余低可以面向整个系统，而文件系统则具有应用范围的局限性，不易扩展。
第三，关系数据库系统采用两级映射机制保证了数据的高独立性，从而使得程序的编写和数据都存在很高的独立性。这方面是文件系统无法达到的，它只能针对于某一个具体的应用。（两级映射：保证逻辑独立性的外模式/模式映射和保证物理独立性的内模式/模式映射。外模式：用户模式，是数据库用户的局部数据的逻辑结构特征的描述。模式：数据库全体数据的逻辑结构特征的描述。内模式：也就是数据最终的物理存储结构的描述。）
第四，就是关系性数据库系统由统一的DBMS进行管理，从而为数据提供了如安全性保护，并发控制，完整性检查和数据库恢复服务。

5、触发器的概念,存储过程的概念.
答案：
触发器：是存储在数据库中的过程，当表被修改（增、删、改）时它隐式地被激发。
存储过程：是数据库语言SQL的集合，同样也存储在数据库中，但是他是由其他应用程序来启动运行或者也可以直接运行。
6、基本SQL语句有哪些.
答案：
select、insert、update、delete、create、drop、truncate
1.中级
8、什么是事务一致性 ? 选择熟悉的数据库实现一个事务处理,如信用卡提款.
答案：
事务的一致性：是事务原子性的体现，事务所对应的数据库操作要么成功要么失败没有第三种情况。事务不管是提交成功与否都不能影响数据库数据的一致性状态。
事务：用户定义的一个数据库操作序列，这些操作要么全部成功完成要么全部不做，是一个不可分割的整体。定义事务的SQL语句有：BEGIN TRANSACTION，COMMIT，ROLLBACK。
事务的原子性：就是事务所包含的数据库操作要么都做,要么都不做.
事务的隔离性：事务对数据的操作不能够受到其他事务的影响。
事务的持续性：也就是说事务对数据的影响是永久的。
对’信用卡提款’这一事务而言就是要保证’提取到现金’和’卡帐号余额’的修改要同时成功或失败.
BEGIN TRANSACTION
读取A的帐户余额BALANCE；
BALANCE=BALANCE-AMOUNT转帐金额；
IF（BALANCE<0） THEN
ROLLBACK；
ELSE
BEGIN
将A的新余额写回；
读取B的帐户余额BALANCEB；
BALANCEB=BALANCEB+AMOUNT转帐金额；
将B的新余额写回；
COMMIT；
END IF；
END；

9、实际编写和调试存储过程或触发器.
答案：
10、实现索引的方式? 索引的原理? 索引的代价? 索引的类型?
答案：实现索引的方式有两种：针对一张表的某些字段创建具体的索引,如对oracle: create index 索引名称 on 表名(字段名)；在创建表时为字段建立主键约束或者唯一约束，系统将自动为其建立索引。
索引的原理：根据建立索引的字段建立索引表，存放字段值以及对应记录的物理地址，从而在搜索的时候根据字段值搜索索引表的到物理地址直接访问记录。
引入索引虽然提高了查询速度,但本身占用一定的系统存储容量和系统处理时间,需要根据实际情况进行具体的分析.
索引的类型有：B树索引，位图索引，函数索引等。
11、view 的概念 ? 何时应用?
答案： view 是对表级数据的多角度的透视,适用于对查询安全性、灵活性有一定要求的环境
12、sql语句例外处理?举例说明?
答案：当sql 语句在执行过程中出现意外时,如查询时,未查询到结果;更改时无记录等情况的应采取的措施.
以oracle为例:测试其对exception的理解.如
select 字段 into 变量 from table ;
若查询出多条记录,应增加exception 例外处理.
Oracle处理异常有三种：
1．预定义的例外处理，EXCEPTION WHEN exception_name THEN
2．EXCEPTION_INIT产生例外
首先要declare 声明一个例外名；然后将例外名和错误代码绑定pragma exception_init(错误名,错误代码)；最后在EXCEPTION 处when 错误名then
3．用户自定义例外，不用EXCEPTION，当判断条件成熟时用raise_application_error(-20000~~~-20999，提示信息)。
13、判断下列论述是否正确。 (1) 一个事务中的某条SQL命令提交时，其它SQL命令可以不提交。 (2) 在一个关系表中，主键可唯一标识一行记录。 (3) 一个关系表中的外键必定是另一表中的主键。 (4) 回滚可以在事务失败时，回到操作事务前的数据状态。
答案： (1)错误 (2)正确 (3)正确 (4)正确
14、以下哪些手段可保证数据库中数据的完整性和一致性： (1)自定义数据类型 (2)触发器 (3)主键和外键 (4)事务
答案： (1)(2)(3)(4) （为什么有1）
15、使用存储过程访问数据库比直接用SQL语句访问有哪些优点?
答案：存储过程是预编译过的，执行时勿须编译，执行速度更快；存储过程封装了一批SQL语句，便于维护数据的完整性与一致性；可以实现代码的复用。
1.高级
16、对于精通的数据库系统描述其体系结构,主要包括存储机制、回滚机制、运行机制等.
答案：对oracle 系统而言,描述sga的结构; 后台pmon,ckpt、lgwr,smon等进程的功能;表空间的分配策略; 回滚段的结构
oracle的sga（系统全局区）包括的主要区有：数据库缓存区，重做日志缓存区，共享池（数据字典缓存和库缓存），大池等。数据库缓存区用来存放最近使用过的数据块主要和后台进程中的数据库写进程(DBWR)以及数据文件发生关系；重做日志缓存区用于存放操作数据库数据所产生的重做日志信息，与之合作的有重做日志写进程(LGWR)和重做日志文件；共享池主要缓存SQL/PLSQL，资源锁，控制信息等，其中的库缓存主要缓存被解析执行过的SQL/PLSQL库缓存可分为共享SQL和私有SQL两个区，共享SQL用于存放SQL语句的语法分析结

[3]4、总结：基于Oracle Logminer数据同步 - shishanyuan

来源: 发布时间: 2013-10-29

最近开发Oracle数据同步功能，做了些调研和验证，这个工作过去有段时间，怕时间长了忘记，故用博客共享出来。在这系列中共写了三篇文章，第一篇是写LogMiner配置及使用，第二篇是测试了LogMiner读取日志文件性能，验证可行性，第三篇是使用LogMiner进行数据同步Demo，在该Demo中通过LogMiner在源数据库读取在线/归档日志文件，然后在目标数据库上执行。

以下为三篇文章PDF文件：

1、1.OracleLogminer配置使用.pdf

2、2.OracleLogminer性能测试.pdf

3、3.使用OracleLogminer同步Demo.pdf

本文链接：http://www.cnblogs.com/shishanyuan/p/3142788.html，转载请注明。