oracle利用存储过程备份,ORACLE编程，存储过程，自学笔记（备份）转

--创建过程名称

--create procedure 存储过程名字 is begin

--create or replace procedure  如果有就替换掉

----------------------------------------------

案例1：

--创建一个表

create table mytest(name varchar2(30),passwd varchar2(30));

--创建过程

create procedure sq_pro1 is

begin

--执行部分

insert into mytest values('zgx','888666');

end；

-- / 斜线回车

----------------------------------------------

如何查看错误信息：

show error  回车

--调用存储过程

1.exec 过程名(参数1，2....);

2.call 过程名(参数1，2....);

---------------------------------------------------

set serveroutput on;打开输出选项

set serveroutput off;关闭输出选项

dbms_  是包名的意思！

案例2：

dbms_output.put_line('helloWorld');

-----------------

declare

v_ename varchar2(5);--定义字符串变量

begin

--into v_ename意思:把查询出来数据 赋值给 v_ename;&no是执行的时候会弹出输入框

select ename into v_ename from emp where empno=&no;

--||代表 连接符号；

dbms_output.put_line('用户名是：'||v_ename);

end;

-----------

案例3：

declare

v_ename varchar2(5);--定义字符串变量

v_sal number(7,2);--定义字符串变量

begin

--如果是多个字段，用逗号隔开，顺序必须一样！！

select ename,sal into v_ename,v_sal from emp where empno=&no;

--||代表 连接符号；

dbms_output.put_line('用户名是：'||v_ename||'工资：'||v_sal);

end;

---------------------

--异常的捕获

exception

when no_data_found then --如果出现no_data_found异常就执行下一句

dbms_output.put_line('输入有误！');

end;

------------

过程：

案例4：

创建带输入参数的过程；

create procedure sp_pro3(spNma varchar2,newSal number) is

begin

update emp set sal=newSal where ename=spName;

end;

------------

函数：

函数用于返回特定的数据，当建立函数时，在函数头部要求有return语句；

案例5：

--输入雇员姓名，返回该雇员的年薪

--返回一个number类型；返回值名字是yearSal，类型是number(7,2);

create function sp_fun1(spName varchar2) return number is yearSal number(7,2);

begin

--执行部分

select sal*12+nvl(comm,0)*12 into yearSal from emp where enamee=spName;

return yearSal;

end;

调用函数中

--随便定义一个值

var abc number;

--掉用函数把结果赋值给 abc

call sp_fun1()'SCOTT' into:abc;

-------------

包

创建包：

--创建了一个包 sp_package

--声明该包里有一个过程update_sal

--生命该包里有一个函数annual_income

create package sp_package is

procedure update_sal(name,varchar2,newsal number);

function annual_income(name varchar2, return number;

end;

给包sp_package 实现包体--把定义包中的 过程和函数实现；

create package body sp_package is

procedure update_sal(name,varchar2,newsal number) is

begin

update emp set sal=newsal where ename=name;

end;

function annual_income(name varchar2)

return number isannual_salary number;

begin

select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;

return annual_salary;

end;

end;

--------------

调用包中的过程或函数

exec sp_package.update_sal('SCOTT','120');

---------------------

触发器

触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的时间和触发的操作，常用触发包括insert，pudate，delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。

触发器是非常有用的，可维护数据库的安全和一致性。

---------

定义并使用变量

包括：

1.标量类型(scalar)

2.符合类型()

---------

标量(scalar)-常用类型

语法：

identifier [constant] datatype [not null] [:=| default expr]

identifier:名称

constant：指定常量。需要指定它的初始值，且其值是不能改变的

datatype：数据类型

not null： 指定变量值不能为null

：= 给变量或是常量指定初始值

default 用于指定初始值

expr：指定初始值的pl/sql表达式，文本值、其他变量、函数等

------------

标量定义的案例

1.定义一个变长字符串

v_ename varchar2(10)

2.定义一个小数 范围 -9999.99~9999.99

v_sal number(6,2)

3.定义一个小数并给一个初始值为5.4 :=pl/sql的赋值号

v_sal2 number(6,2):=5.4

4.定义一个日期类型的数据

v_hiredate date;

5.定义一个布尔变量，不能为空，初始值为false

v_valid boolean not null default false;

---------------

如何使用标量

定义好变量后，就可以使用这些变量。这里需要说明的是pl/sql块为变量赋值不同于其他的编程语言，需要在等号前面加冒号(:=)

下面以输入员工号，显示雇员名称、工资、个人所得税(税率为0.03为例)。说明变量的使用，看看如何编写：

declare

c_tax_rate number(3.2):=0.03; --定义赋值

--用户名

v_ename varchar2(5);

v_sal number(7,2);

v_tax_sal number()7,2;

begin

--执行

select ename,sal into v_ename,v_sal from emp where empno=$no;

--计算所得税

v_tax_sal:=v_sal*c_tax_rate;

--输出

dbms_output.put_line('姓名是:'||v_ename||'工资：'||v_sal||'所得税：'||v_tax_sal);

end;

-----

标量(scalar)--使用%type类型

对于上面的pl/sql块有一个问题：

就是如果员工的姓名超过了5字符的话，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样他会按照数据库列来确定你定义的变量的类型和长度。

看看怎么使用。

语法： 标识符名 表名.列名%type;

declare

v_ename emp.ename%type; --定义变量v_ename 和emp表中列名ename大小类型保持一致；

---

复合变量(composite)

用于存放多个值的变量。

包括：

1.pl/sql记录

2.pl/sql表

---------------

复合类型-pl/sql记录

类似与高级语言的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)

如下：

declare

--定义一个pl/sql记录类型是：emp_record_type，类型包括三个数据name，salary，title；该类型中可以存放三个类型的数据；

type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title  emp.job%type);

--定义了一个sp_record变量，类型是emp_record_type

sp_record emp_record_type;

begin

select ename,sal,job into sp_record from emp where empno=7788;

dbms_output.put_line('员工名：'||sp_record.name); --显示定义emp_record_type类型中 name的值；

end;

end;

----------------

复合类型--pl/sql表

相当于高级语言中的数组。但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。实例如下：

declare

--定义了一个pl/sql表类型sp_table_type，该类型是用于存放emp.ename%type类型的数组

--index by binary_integer标识下标是整数

type sp_table_type is table of emp.ename%type index by binary_integer;

--定义了一个sp_table变量，变量类型是sp_table_type

sp_table sp_table_type;

begin

--把查询出来的ename放到 table(0)下标为0的数据

select ename into sp_table(0) from emp where empno=7788;

dbms_output.put_lin('员工名：'||sp_table(0)); --要和存放下标一样

end;

说明：

sp_table_type  是pl/sql表类型

emp.ename%type 指定了表的元素的类型和长度

sp_table       为pl/sql表变量

sp_table(0)    表示下标为0的

---------------

参照变量

参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使用得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量和对象类型变量两种参照变量类型

游标变量用的最多

-----------

参照变量---游标变量

使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时需要指定select语句，这样一个游标就与一个select语句结合了。

如下

1.请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。

declare

--定义游标类型

type sp_emp_cursor is ref cursor；

--定义一个游标变量

test_cursor sp_emp_cursor；

--定义变量

v_ename emp。ename%type；

v_sal emp。sal%type；

begin

--执行

--打开一个游标test_cursor和一个select结合

open test_cursor for select ename,sal from emp where deptno=&no;

--循环取出

loop

--fetch就是取出。取出test_cursor中的数据放到 v_ename,v_sal里面去；

fetch test_cursor into v_ename,v_sal；

--判断是否test_cursor为空

exit when test_cursor%notfound；

dbms_output.put_line('名字：'||v_ename||'工资：'||v_sal);

end loop；

end；

2.在1。基础上，如果某个员工的工资低于200元，就增加100元。

declare

--定义游标类型

type sp_emp_cursor is ref cursor；

--定义一个游标变量

test_cursor sp_emp_cursor；

--定义变量

v_ename emp。ename%type；

v_sal emp。sal%type；

begin

--执行

--打开一个游标test_cursor和一个select结合

open test_cursor for select ename,sal from emp where deptno=&no;

--循环取出

loop

--fetch就是取出。取出test_cursor中的数据放到 v_ename,v_sal里面去；

fetch test_cursor into v_ename,v_sal；

if v_sal<200 then

update emp set sal=sal+100 where ename=v_ename;

end if;

--判断是否test_cursor为空

exit when test_cursor%notfound；

dbms_output.put_line('名字：'||v_ename||'工资：'||v_sal);

end loop；

end；

----------

条件分支语句

if--then，

if--then--else，

if--then--elsif--else

----

循环语句

loop --end loop；至少会执行一次。

create or replace procedure sp_pro6() is

--定义赋值

v_num number:=1;

begin

loop

insert into users1 values(v_num,spName);

--判断是否要退出循环

exit when v_num=10;

--自增

v_num:=v_num+1;

end loop;

end;

-------------

循环语句-while先判断后执行

create or replace procedure sp_pro6() is

--定义赋值

v_num number:=11;

begin

while v_num<=20 loop

insert into users1 values(v_num,spName);

v_num:=v_num+1;

end loop;

end;

------------------

循环语句--for循环(不建议)

begin

for i in reverse 1。。10 loop

insert into users1 values(i，'aaa')；

end loop;

end；

-------

循环语句--goto,null循环(不建议)

declare

i int:=1;

begin

loop

dbms_output.put_line('输出i='||i);

if i=10 then

goto end_loop;

end if;

i:=i+1;

end loop;

<>  --到i到10后直接跳到该标记

dbms_output.put_line('循环结束');

end;

---------------------------

无返回值的存储过程(有输入参数)

create table book(

bookId number;

bookName varchar2(100);

publishHouse varchar2(50);

);

--编写过程

--in表示这是一个输入参数，不写默认是in

--out 表示一个输出参数

create or replace procedure sp_pro7(spBookId in number，spbookName in varchar2,sppublishHouse in varchar2) is

begin

insert into book values(spBookId,spbookName,sppublishHouse);

end;

---------------

有返回值的存储过程(有输入和输出参数)

create or replace procedure sp_pro8(ename in number，spName out varchar2) is

begin

--spName自动返回 因为他是out

select ename into spName from emp where empno=spno;

end;

----------------

有返回值是集合数组的存储过程(有输入和输出参数)

1.建立一个包

--创建包 里面定义一个游标类型；

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage；

2.建立存储过程。

create or replace procedure sp_pro8(spNo in number，p_cursor out testpackage.test_cursor) is

begin

--spName自动返回 因为他是out

open p_cursor for select * from emp where deptno=spNo;

end;

－－－－－－－－－－－－

oracle的分页  rn是别名

select t1.*，rownum rn from(select * from emp) t1;//多加一个列记录个数

select t1.*，rownum rn from(select * from emp) t1 where rownum<10;

select * from (select t1.*，rownum rn from(select * from emp) t1 where rownum<10) where rn>=6;

编写oracle的分页

--建立一个包

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage；

--建立存储过程

create or replace procedure fenye

(tableName in varchar2,

pageSize in number, --一页显示几条记录

pageNow in number,  --显示哪一页

myrows out number, --总记录数

myPageCount out number,--总页数

p_cursor out testpackage.test_cursor --返回的记录集

) is

--定义部分

--定义sql语句 字符串

v_sql varchar2(1000);

--定义两个整数

v_begin number:=(pageNow-1)*pageSize+1;

v_end number:=pageNow*pageSize;

begin

--执行部分

v_sql:='select * from (select t1.*，rownum rn from(select * from '||tableName||') t1 where rownum<10'||?||') where rn>='||?||';';

--把游标和sql语句关联起来

open p_cursor for v_sql;

--计算myrows

v_sql:='select count(*) from '||tableName||'';

--执行sql，并把返回值，赋值给myrows;

execute immediate v_sql int myrows;

--计算myPagecount

if mod(myrows,pageSize)=0 then --mod()取余数

myPageCount:=myrows/pageSize;

else

myPageCount:=myrows/pagesize+1;

end if;

--关闭游标

--close p_cursor;

end;

------------------------

例外的分类

1.预定义例外用于处理常见的oracle错误

2.非预定义例外用于处理预定义例外不能处理的例外  6.53

3.自定义例外用于处理与oracle错误无关的其他情况

------------------------------------------------

-----------------------------------------------

-------ＪＡＶＡ中-调用无返回值的存储过程-----------------

try{

Class.forName();

Connection ct=DriverManager.getConnerction();

//调用无返回值存储过程

CallableStatement cs=ct.prepareCall("{call 存储过程名称(?,?,?)}") // ?代表存储过程参数

cs.setIn(1,10);

cs.setString(2,'java调用存储过程');

cs.setString(3,'人民出版社');

//执行

cs.execute();

}catch(Exception e)

{

e.printStackTrace();

}finally{

cs.close();

ct.close();

}

------------------------------------------------

-----------------------------------------------

------ＪＡＶＡ中--调用有回值的存储过程-----------------

try{

Class.forName();

Connection ct=DriverManager.getConnerction();

//调用有返回值存储过程

CallableStatement cs=ct.prepareCall("{call 存储过程名称(?,?)}") // ?代表存储过程参数 第一是输入，第二是输出

//第一个?输入参数

cs.setIn(1,10);

//给第二个？输出值赋值

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //

//执行

cs.execute();

//取出返回值,

String name=cs。getString(2);

System.out。println("名称是："+name);

}catch(Exception e)

{

e.printStackTrace();

}finally{

cs.close();

ct.close();

}

------------------------------------------------

-----------------------------------------------

-------ＪＡＶＡ中-调用有回值是多个 数组2011-12-5的存储过程-----------------

try{

Class.forName();

Connection ct=DriverManager.getConnerction();

//调用有返回值存储过程

CallableStatement cs=ct.prepareCall("{call 存储过程名称(?,?)}") // ?代表存储过程参数 第一是输入，第二是输出

//第一个?输入参数

cs.setIn(1,10);

//给第二个？输出值赋值

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.cursor); //类型是cursor游标

//执行

cs.execute();

//取出返回值(结果集)

ReaultSet rs=(ResultSet)cs.getObject(2); //2是第二？

while(rs.next())

{

int =rs。getInt(1);

String name=rs。getString(2);

System.out。println("名称是："+name);

}

}catch(Exception e)

{

e.printStackTrace();

}finally{

cs.close();

ct.close();

}

------------------------------------------------

-----------------------------------------------

------ＪＡＶＡ中--调用有回值的存储过程-----------------

try{

Class.forName();

Connection ct=DriverManager.getConnerction();

//调用有返回值存储过程

CallableStatement cs=ct.prepareCall("{call 存储过程名称(?,?)}") // ?代表存储过程参数 第一是输入，第二是输出

//第一个?输入参数

cs.setIn(1,10);

//给第二个？输出值赋值

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //

//执行

cs.execute();

//取出返回值,

String name=cs。getString(2);

System.out。println("名称是："+name);

}catch(Exception e)

{

e.printStackTrace();

}finally{

cs.close();

ct.close();

}

------------------------------------------------

-----------------------------------------------

-------ＪＡＶＡ中-测试分页调用存储过程-----------------

try{

Class.forName();

Connection ct=DriverManager.getConnerction();

//调用有返回值存储过程

CallableStatement cs=ct.prepareCall("{call 分页存储过程名称(?,?,?,?,?,?)}") // ?代表存储过程参数 第一是输入，第二是输出

//?输入参数

cs.setString(1,'表名'); //表名

cs.setInt(2,5); //一页显示几条记录

cs.setInt(3,1); //显示第几页

//?输出参数

//注册总记录数

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

//注册总页数

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

//注册返回的结果集

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR); //类型是cursor游标

//执行

cs.execute();

//取出总记录数

int rowNum=cs.getInt(4);//4表示参数中第四个？

//总页数

int pageCount=cs.getInt(5);

//返回的记录结果

ReaultSet rs=(ResultSet)cs.getObject(6);

while(rs.next())

{

int =rs。getInt(1);

String name=rs。getString(2);

System.out。println("名称是："+name);

}

}catch(Exception e)

{

e.printStackTrace();

}finally{

cs.close();

ct.close();

}

A . 嵌套表

1. 声明数组类型

create or replace type tab_array is table of varchar2(38);暂时不要在包中声明该类型

2. 创建存储过程

-- 该例子存储过程是在包中创建的,包名 arraydemo

procedure testArray(resNumber in tab_array,procResult out tab_array) is

begin

procResult := new tab_array();

for i in 1..resNumber.Count loop

procResult.EXTEND;

procResult(i) := resNumber(i) || 'lucifer' || i;

end loop;

end;

3. Java调用代码

//必须使用Oracle的连接和Statement,使用了连接池的必须通过一些方法获取原始的连接

OracleConnection conn = null;

OracleCallableStatement stmt = null;

String[] param = { "1001", "1002", "1006" };

stmt =(转换类型) conn.prepareCall("{call arraydemo.testArray(?,?)}");

// 类型名必须大写

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TAB_ARRAY", conn);

stmt.setARRAY(1, new ARRAY(descriptor,conn,param));

stmt.registerOutParameter(2, OracleTypes.ARRAY, "TAB_ARRAY");

stmt.execute();

ARRAY array = stmt.getARRAY(2);

Datum[] data = array.getOracleArray();

for (int i = 0; i < data.length; i++) {

System.out.println(i + " : " + new String(data.shareBytes()));

}

4 . 注意的问题及尚未解决的问题

抛出:Non supported character set: oracle-character-set-852 异常---解决:添加 nls_charset12.jar 到classpath,该包在oracle/ora92/jdbc/lib目录下

待解决问题:

a) 如何调用在包声明的自定义类型

b) 比较不同声明类型的优缺点,及使用场合

嵌套表其它应用:http://zhouwf0726.itpub.net/post/9689/212253

B . 索引表

C . 内置数组

D . 游标方式