day13_存储过程小记

1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字

(

参数1 IN NUMBER,

参数2 OUT NUMBER

) IS

变量1 INTEGER :=0;

变量2 DATE;

BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条

记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

例子：

BEGIN

SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;

EXCEPTION

WHEN NO_DATA_FOUND THEN

xxxx;

END;

...

3.IF 判断

IF V_TEST=1 THEN

BEGIN

do something

END;

END IF;

4.while 循环

WHILE V_TEST=1 LOOP

BEGIN

XXXX

END;

END LOOP;

5.变量赋值

V_TEST := 123;

6.用for in 使用cursor

...

CURSOR cur IS SELECT * FROM xxx;

BEGIN

FOR cur_result in cur LOOP

BEGIN

V_SUM :=cur_result.列名1+cur_result.列名2

END;

END LOOP;

END;

7.带参数的cursor

CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;

OPEN C_USER(变量值);

LOOP

FETCH C_USER INTO V_NAME;

EXIT FETCH C_USER%NOTFOUND;

do something

END LOOP;

CLOSE C_USER;

8.用pl/sql developer debug

连接数据库后建立一个Test WINDOW

在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

简单实例，通过DBMS_OUTPUT来看结果

CREATE OR REPLACE PROCEDURE bb (lic_para IN VARCHAR2,out_para OUT VARCHAR2)

temp VARCHAR2 (100);

BEGIN

SELECT lic_no

INTO temp

FROM t_vehicle_info

WHERE lic_no = lic_para;

out_para:=temp;

DBMS_OUTPUT.put_line (out_para);

END bb;

下面是调用：

begin

-- Call the procedure

bb(lic_para => :lic_para,

out_para => :out_para);

end;

1.编写。编写一个最最简单的存储过程，给它起个名字叫做proc_helloworld

CREATE OR REPLACE PROCEDURE proc_helloworld

BEGIN

DBMS_OUTPUT.put_line ('Hello World!');

END;

2.创建。在sqlplus命令行界面创建该存储过程

sys@ora10g> conn sec/sec

Connected.

sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld

2 IS

3 BEGIN

4 DBMS_OUTPUT.put_line ('Hello World!');

5 END;

6 /

Procedure created.

3.运行。两种方法运行存储过程

1).需要在屏幕上显示出"DBMS_OUTPUT.put_line"的输出字符串，需要做一个小小的设置

sec@ora10g> show serveroutput

serveroutput OFF

sec@ora10g> set serveroutput on

sec@ora10g> show serveroutput

serveroutput ON SIZE 10000 FORMAT WORD_WRAPPED

2).在sqlplus命令行界面使用"EXECUTE"命令（简写做"EXEC"）执行

sec@ora10g> exec proc_helloworld

Hello World!

PL/SQL procedure successfully completed.

3).在一个无名PL/SQL块内运行存储过程

BEGIN

proc_helloworld;

END;

sec@ora10g> BEGIN

2 proc_helloworld;

3 END;

4 /

Hello World!

PL/SQL procedure successfully completed.

4.修改。修改一个存储过程只需要将修改好的存储过程在sqlplus界面先重新执行一下即可，因为在创建过程中使用的是"CREATE OR REPLACE PROCEDURE"，也就是说如果没有就创建，如果已经存在了这个存储过程，就替换它

CREATE OR REPLACE PROCEDURE proc_helloworld

BEGIN

DBMS_OUTPUT.put_line ('Hello World! '||chr(10)||'I am a Happy DBA Secooler!');

END;

或者

CREATE OR REPLACE PROCEDURE proc_helloworld

BEGIN

DBMS_OUTPUT.put_line ('Hello World!

I am a Happy DBA Secooler!');

END;

sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld

2 IS

3 BEGIN

4 DBMS_OUTPUT.put_line ('Hello World! '||chr(10)||'I am a Happy DBA Secooler!');

5 END;

6 /

Procedure created.

看一下执行效果：

sec@ora10g> exec proc_helloworld;

Hello World!

I am a Happy DBA Secooler!

PL/SQL procedure successfully completed.

5.调试。对于非常复杂的存储过程的调试是真正体现个人魅力和能力的地方，往往需要很多的经验，这个急不得，只能慢慢来 Take it easy.

在sqlplus下调试存储过程时，如果出现错误，时刻使用"show errors"命令查看哪里出错了，例如：

sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld

2 IS

3 BEGIN

4 DBMS_OUTPUT.put_line ('Hello World!');

5 END

6 /

Warning: Procedure created with compilation errors.

sec@ora10g> show errors

Errors for PROCEDURE PROC_HELLOWORLD:

LINE/COL ERROR

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

5/3 PLS-00103: Encountered the symbol "end-of-file" when expecting

one of the following:

; <an identifier> <a double-quoted delimited-identifier>

delete exists prior <a single-quoted SQL string>

The symbol ";" was substituted for "end-of-file" to continue.

通过提示，问题出现在END后面没有分号结束符号，修改后问题得到处理

sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld

2 IS

3 BEGIN

4 DBMS_OUTPUT.put_line ('Hello World!');

5 END;

6 /

Procedure created.

6.删除。使用drop语句删除存储过程

sec@ora10g> select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

OBJECT_NAME OBJECT_TYPE STATUS

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

PROC_HELLOWORLD PROCEDURE VALID

sec@ora10g> DROP PROCEDURE proc_helloworld;

Procedure dropped.

sec@ora10g> select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

no rows selected

7.获取。在维护存储过程的过程中往往需要快速的获取存储过程的SQL创建语句，我经常使用的有如下两种方法

1).使用DBMS_METADATA包获得

sec@ora10g> SELECT DBMS_METADATA.get_ddl ('PROCEDURE', 'PROC_HELLOWORLD') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','PROC_HELLOWORLD')

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

CREATE OR REPLACE PROCEDURE "SEC"."PROC_HELLOWORLD"

BEGIN

DBMS_OUTPUT.put_line ('Hello World!');

END;

2).使用"USER_SOURCE"视图获得，本人推荐使用这种方法查看，格式标准，内容也全面

sec@ora10g> SELECT text FROM user_source WHERE NAME = 'PROC_HELLOWORLD';

TEXT

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

PROCEDURE proc_helloworld

BEGIN

DBMS_OUTPUT.put_line ('Hello World!');

END;

8.小结

上面的实验，我通过编写、创建、运行、修改、调试、删除和获取七个内容描述了一个存储过程的开发和维护过程。

试验，实验还有实践，技术工作者永远不变的途径！

Goodluck Friends!

一、过程 (存储过程)

过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。

示例1：声明存储过程，该过程返回dept表行数

DECLARE

PROCEDURE getDeptCount

deptCount INT;

BEGIN

SELECT COUNT(*) INTO deptCount FROM DEPT;

DBMS_OUTPUT.PUT_LINE('DEPT表的共有记录数：'||deptCount);

END getDeptCount;

BEGIN

getDeptCount[()];

END;

注意：此存储过程getDeptCount只在块运行时有效。

示例2：创建不带参数的存储过程，该过程返回dept表行数

CREATE OR REPLACE PROCEDURE getDeptCount

AS | IS

deptCount int;

BEGIN

SELECT COUNT(*) INTO deptCount FROM dept;

DBMS_OUTPUT.PUT_LINE('dept表共有'||deptCount||'行记录');

END [getDeptCount];

当我们创建的存储过程没有参数时，在存储过程名字后面不能有括号。在AS或者IS后至BEGIN之前是声明部分，存储过程中的声明不使用DECLARE关键字。同匿名PL/SQL块一样，EXCEPTION和声明部分都是可选的。

当我们创建的过程带有错误时，我们可以通过SELECT * FROM USER_ERRORS查看，或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。

使用以下代码可以执行存储过程：

BEGIN

getDeptCount;

END;

以上存储过程还可以通过以下代码来简化调用：

EXEC getDeptCount[;]

CALL getDeptCount();

注意：

并不是所有的存储过程都可以用这种方式来调用

定义无参存储过程时，存储过程名后不能加()

在块中或是通过EXEC调用存储过程时可以省略()

通过CALL调用无参存储过程必须加上()

示例3：创建带有输入参数的存储过程，该过程通过员工编号打印工资额

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER) --参数的数据类型不能指定长度

salary emp.sal%TYPE;

BEGIN

SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo;

DBMS_OUTPUT.PUT_LINE(eNo||'号员工的工资为'||salary);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');

END;

当定义的存储过程含有参数时，参数的数据类型不能指定长度。参数还有输入和输出之分，本例中没有指定，默认情况为输入参数，也可显示的指定某个参数是输入参数，如(eNo IN NUMBER)。同示例1不同，该例中加入了异常处理。同示例1类似可以使用下面的两种方式调用存储过程：

BEGIN

getSalaryByEmpNo(7788);

END;

或者

EXEC getSalaryByEmpNo(7788); 或者

CALL getSalaryByEmpNo(7788);

但是如果传给一个存储过程的参数是变量时，必须使用BEGIN END块，如下：

DECLARE

no emp.empNo%TYPE;

BEGIN

no:=7788;

getSalaryByEmpNo(no);

END;

如果某个包中含有常量，也可以通过如下的方式调用：

EXEC getSalaryByEmpNo(ConstantPackage.no);

但这种方式不能再使用CALL调用。

示例4：创建含有输入和输出参数的存储过程，该过程通过员工编号查找工资额，工资额以输出参数返回

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)

BEGIN

SELECT SAL INTO salary FROM EMP WHERE EMPNO=eNo;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');

END;

当过程中含有输出参数时，调用时必须通过BEGIN END块，不能通过EXEC或CALL调用。如：

DECLARE

salary NUMBER(7,2);

BEGIN

getSalaryByEmpNo(7788,salary);

DBMS_OUTPUT.PUT_LINE(salary);

END;

示例5：创建参数类型既是输入参数也是输出参数的过程

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(noSalary IN OUT NUMBER)

BEGIN

SELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');

END;

调用如下：

DECLARE

no NUMBER(7,2);

BEGIN

no:=7788;

getSalaryByEmpNo(no);

DBMS_OUTPUT.PUT_LINE(no);

END;

示例6：创建带有默认值的过程

CREATE OR REPLACE PROCEDURE addEmp

(

empNo NUMBER,

eName VARCHAR2,

job VARCHAR2 :='CLERK',

mgr NUMBER,

hiredate DATE DEFAULT SYSDATE,

sal NUMBER DEFAULT 1000,

comm NUMBER DEFAULT 0,

deptNo NUMBER DEFAULT 30

)

BEGIN

INSERT INTO emp VALUES(empNo,eName,job,mgr,hiredate,sal,comm,deptNo);

END;

调用如下：

EXEC addEmp(7776,'zhangsan','CODER',7788,'06-1月-2000',2000,0,10); --没有使用默认值

EXEC addEmp(7777,'lisi','CODER',7788,'06-1月-2000',2000,NULL,10); --可以使用NULL值

EXEC addEmp(7778,'wangwu',mgr=>7788); --使用默认值

EXEC addEmp(mgr=>7788,empNo=>7779,eName=>'sunliu'); --更改参数顺序

示例7：使用NOCOPY编译提示

当参数是大型数据结构时，如集合、记录和对象实例，把它们的内容全部拷贝给形参会降低执行速度，消耗大量内存。为了防止这样的情况发生，我们可以使用 NOCOPY提示来让编译器按引用传递方式给IN OUT模式的参数。

DECLARE

TYPE DeptList IS TABLE OF VARCHAR2(10);

dList DeptList:=DeptList('CORESUN','CORESUN','CORESUN','CORESUN');

PROCEDURE My_Proc(d IN OUT NOCOPY DeptList)

AS...

注意：NOCOPY只是一个提示，而不是指令。即使有时候我们使用了NOCOPY,但编译器有可能仍然会进行值拷贝。通常情况下NOCOPY是可以成功的。

二、维护过程

1、删除存储过程

DROP PROCEDURE Proc_Name;

2、查看过程状态

SELECT object_name,status FROM USER_OBJECTS WHERE object_type='PROCEDURE';

3、重新编译过程

ALTER PROCEDURE Proc_Name COMPILE;

4、查看过程代码

SELECT * FROM USER_SOURCE WHERE TYPE='PROCEDURE';

三、参数的理解

-- 输出参数不可以修改解决的方法有两种

--1 把参数改成输入参数

--2 就是参数改成可输入输出的参数;

调用过程的三个方式

1 就是使用call

在只用call方式调用函数的时候,必须加要括号,有参数,还要加参数值

这个方式在命令窗口,调用过程,将不会出现输入的数据.

2 就是使用exec 命令,进行命令调用过程, 使用命令,就必须在命令行里面输入

过程名,这个命令窗口中,可加可不加() ,如果有参数的,就一定要加,还有参数值,参数值的类型要与

变量类型相同.

3 在语句块中进行调用过程,这个方式和命令模式类似,他们都是可要可不要(),

-- 在2 和 3 中的没有括号的情况是,过程没有参数 ,如果有,就必须要有()

输出参数的特点

1 一个过程中,如果有输出参数(OUT 参数),在调用过程的使用,也要传入一个参数, 这个参数可以不用在调用的地方

进行赋值,就直接传入一个声明好的一个变量,用来接受存储过程中的输出参数的值(OUT 参数)

2 输入参数值不可以改变在过程中,

注意: 在存储过程中,他的参数类型不可以设置它的大小 ;

例如;

CREATE OR REPLACE PROCEDURE hello(

p_name IN VARCHAR2(12),

p_age OUT NUMBER(10,2)

)

BEGIN

如果有输出参数就必须有有一个参数进行接收 ;

CREATE OR REPLACE PROCEDURE hello(

p_name IN VARCHAR2,

p_age OUT emp.sal%TYPE

)

BEGIN

SELECT emp.sal + 3131 INTO p_age FROM emp WHERE empno = 7788 ;

dbms_output.put_line( p_age);

END ;

--------- 块中调用方法

DECLARE

v_nanme varchar2(12);

v_age NUMBER (12,2);

BEGIN

hello (v_nanme,v_age);

dbms_output.put_line(v_age);

END ;

-- 在这个过程中传入的v_age 就是接受存储过程输出参数的值 ; 类似于Java的中的返回值

-- 理解 in out 参数

CREATE OR REPLACE PROCEDURE hello1 (

p_name IN OUT emp.ename%TYPE

)

BEGIN

-- SELECT emp.ename INTO p_name FROM emp ;

p_name:='a;sk , ' || p_name ;

END ;

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

DECLARE

v_nanme varchar2(12);

BEGIN

v_nanme:='12312';

hello1(v_nanme);

补充：sqlplus中执行含有输出参数为游标的存储过程

实例6：

sqlplus创建存储过程，使用如下：

SQL>create or replace procedure test1(rcursor out sys_refcursor) as

begin

open rcursor for

select decode(row_number() over(partition by deptno order by ename),

deptno,

null) deptno,

t.ename

from scott.emp t;

end;

--使用sqlplus执行上面创建的带有游标输出参数的存储过程

SQL> var cur refcursor

SQL> exec test1(:cur);

PL/SQL procedure successfully completed.

SQL> print cur;

DEPTNO ENAME

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

10 CLARK

KING

MILLER

20 ADAMS

FORD

JONES

SCOTT

SMITH

30 ALLEN

BLAKE

JAMES

DEPTNO ENAME

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

MARTIN

TURNER

WARD

14 rows selected.

简要记录存储过程语法与Java程序的调用方式

　　一存储过程

　　　　首先，我们建立一个简单的表进行存储过程的测试

create table

xuesheng(id integer, xing_ming varchar2(25), yu_wen number, shu_xue number);

insert into xuesheng values(1,'zhangsan',80,90)

insert into xuesheng values(2,'lisi',85,87)

1）无返回值的存储过程

create or replace procedure xs_proc_no is

begin

insert into xuesheng values (3, 'wangwu', 90, 90);

commit;

end xs_proc_no;

2）有单个数据值返回的存储过程

复制代码

create or replace procedure xs_proc(temp_name in varchar2,

temp_num out number) is

num_1 number;

num_2 number;

begin

select yu_wen, shu_xue

into num_1, num_2

from xuesheng

where xing_ming = temp_name;

--dbms_output.put_line(num_1 + num_2);

temp_num := num_1 + num_2;

end;

复制代码

其中，以上两种与sql server基本类似，而对于返回数据集时，上述方法则不能满足我们的要求。在Oracle中，一般使用ref cursor来返回数据集。示例代码如下：

3）有返回值的存储过程(列表返回)

首先，建立我们自己的包。并定义包中的一个自定义ref cursor

create or replace package mypackage as

type my_cursor is ref cursor;

end mypackage;

在定义了ref cursor后，可以书写我们的程序代码

create or replace procedure xs_proc_list(shuxue in number,

p_cursor out mypackage.my_cursor) is

begin

open p_cursor for

select * from xuesheng where shu_xue > shuxue;

end xs_proc_list;

二、程序调用

在本节中，我们使用java语言调用存储过程。其中，关键是使用CallableStatement这个对象，代码如下：

String oracleDriverName = "oracle.jdbc.driver.OracleDriver";

// 以下使用的Test就是Oracle里的表空间

String oracleUrlToConnect = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

Connection myConnection = null;

try {

Class.forName(oracleDriverName);

} catch (ClassNotFoundException ex) {

ex.printStackTrace();

}

try {

myConnection = DriverManager.getConnection(oracleUrlToConnect,

"xxxx", "xxxx");//此处为数据库用户名与密码

} catch (Exception ex) {

ex.printStackTrace();

}

try {

CallableStatement proc=null;

proc=myConnection.prepareCall("{call xs_proc(?,?)}");

proc.setString(1, "zhangsan");

proc.registerOutParameter(2, Types.NUMERIC);

proc.execute();

String teststring=proc.getString(2);

System.out.println(teststring);

} catch (Exception ex) {

ex.printStackTrace();

}

对于列表返回值的存储过程，在上述代码中做简单修改。如下

复制代码

CallableStatement proc=null;

proc=myConnection.prepareCall("{call getdcsj(?,?,?,?,?)}");

proc.setString(1, strDate);

proc.setString(2, jzbh);

proc.registerOutParameter(3, Types.NUMERIC);

proc.registerOutParameter(4, OracleTypes.CURSOR);

proc.registerOutParameter(5, OracleTypes.CURSOR);

proc.execute();

ResultSet rs=null;

int total_number=proc.getInt(3);

rs=(ResultSet)proc.getObject(4);

复制代码

上述存储过程修改完毕。另外，一个复杂的工程项目中的例子：查询一段数据中间隔不超过十分钟且连续超过100条的数据。即上述代码所调用的getdcsj存储过程

create or replace procedure getDcsj(var_flag in varchar2,

var_jzbh in varchar2,

number_total out number,

var_cursor_a out mypackage.my_cursor,

var_cursor_b out mypackage.my_cursor) is

total number;

cursor cur is

select sj, flag

from d_dcsj

where jzbh = var_jzbh

order by sj desc

for update;

last_time date;

begin

for cur1 in cur loop

if last_time is null or cur1.sj >= last_time - 10 / 60 / 24 then

update d_dcsj set flag = var_flag where current of cur;

last_time := cur1.sj;

else

select count(*) into total from d_dcsj where flag = var_flag;

dbms_output.put_line(total);

if total < 100 then

update d_dcsj set flag = null where flag = var_flag;

last_time := null;

update d_dcsj set flag = var_flag where current of cur;

else

open var_cursor_a for

select *

from d_dcsj

where flag = var_flag

and jzbh = var_jzbh

and zh = 'A'

order by sj desc;

number_total := total;

open var_cursor_b for

select *

from d_dcsj

where flag = var_flag

and jzbh = var_jzbh

and zh = 'B'

order by sj desc;

number_total := total;

exit;

end if;

end loop;

select count(*) into total from d_dcsj where flag = var_flag;

dbms_output.put_line(total);

if total < 100 then

open var_cursor_a for

select * from d_dcsj where zh = 'C';

open var_cursor_b for

select * from d_dcsj where zh = 'C';

else

open var_cursor_a for

select *

from d_dcsj

where flag = var_flag

and jzbh = var_jzbh

and zh = 'A'

order by sj desc;

number_total := total;

open var_cursor_b for

select *

from d_dcsj

where flag = var_flag

and jzbh = var_jzbh

and zh = 'B'

order by sj desc;

number_total := total;

end if;

commit;

end;

关于游标 if,for 的例子

create or replace procedure peace_if

cursor var_c is select * from grade;

begin

for temp in var_c loop

if temp.course_name = 'OS' then

dbms_output.put_line('Stu_name = '||temp.stu_name);

elsif temp.course_name = 'DB' then

dbms_output.put_line('DB');

else

dbms_output.put_line('feng la feng la ');

end if;

end loop;

end;

---关于游标 for,case 的例子1

create or replace procedure peace_case1

cursor var_c is select * from test_case;

begin

for temp in var_c loop

case temp.vol

when 1 then

dbms_output.put_line('haha1');

when 2 then

dbms_output.put_line('haha2');

when 3 then

dbms_output.put_line('haha3');

when 4 then

dbms_output.put_line('haha4');

else

dbms_output.put_line('qita');

end case ;

end loop;

end;

---关于游标 for,case 的例子2

create or replace procedure peace_case2

cursor var_c is select * from test_case;

begin

for temp in var_c loop

case

when temp.vol=1 then

dbms_output.put_line('haha1');

when temp.vol=2 then

dbms_output.put_line('haha2');

when temp.vol=3 then

dbms_output.put_line('haha3');

when temp.vol=4 then

dbms_output.put_line('haha4');

else

dbms_output.put_line('qita');

end case ;

end loop;

end;

---关于for 循环的例子

create or replace procedure peace_for

sum1 number :=0;

temp varchar2(500);

begin

for i in 1..9 loop

temp := '';

for j in 1 .. i

loop

sum1 := i * j;

temp := temp||to_char(i) || ' * ' ||to_char(j) ||' = ' ||to_char(sum1) ||' ';

end loop;

dbms_output.put_line(temp );

end loop;

end;

---关于 loop循环的例子

create or replace procedure peace_loop

sum1 number := 0;

temp number :=0 ;

begin

loop

exit when temp >= 10 ;

sum1 := sum1+temp;

temp := temp +1;

end loop;

dbms_output.put_line(sum1 );

end;

---关于游标和loop循环的例子

create or replace procedure loop_cur

stu_name varchar2(100);

course_name varchar2(100);

cursor var_cur is select * from grade ;

begin

open var_cur;

loop

fetch var_cur into stu_name,course_name;

exit when var_cur%notfound;

dbms_output.put_line(stu_name|| course_name);

end loop;

close var_cur;

end;

---关于异常处理的例子

create or replace procedure peace_exp(in1 in varchar2)

c_n varchar2(100);

begin

select course_name into c_n from grade where stu_name = in1;

dbms_output.put_line(c_n);

exception

when no_data_found

then

dbms_output.put_line('try');

when TOO_MANY_ROWS

then

dbms_output.put_line('more');

end;

---关于异常处理的例子2

create or replace procedure peace_insert ( c_n in varchar2)

error EXCEPTION;

begin

if c_n = 'OK'

then

insert into course (course_name) values (c_n);

elsif c_n = 'NG' then

insert into course (course_name) values (c_n);

raise error;

else

Dbms_Output.put_line('c_n' || c_n);

end if;

commit;

exception

when error then

rollback;

Dbms_Output.put_line('ERRO');

end;

---关于包的例子定义包

create or replace package peace_pkg

function test1(in1 in varchar2)

return number;

procedure test2 (in2 in varchar2);

end peace_pkg;

---关于包的例子定义包体

create or replace package body peace_pkg

function test1(in1 in varchar2)

return number

temp number;

begin

temp := 0;

return temp;

end;

procedure test2 (in2 in varchar2)

begin

dbms_output.put_line(in2);

end;

end peace_pkg;

用了两年Oracle还没写过存储过程，真是十分惭愧，从今天开始学习Oracle存储过程，完全零起点，争取每日一篇学习笔记，可能开始认识的不全面甚至有错误，但坚持下来一定会有收获。

1. 建立一个存储过程

create or replace PROCEDURE firstPro

BEGIN

DBMS_OUTPUT.PUT_LINE('Hello World!');

END;

其中IS关键字替换为AS关键字结果不会出现任何变化，大多认为他们是等同的，但也有一种说法解释为：一般PACKAGE 或者单独的FUNCTION, PROCEDURE 都用AS，PACKAGE 中的FUNCTION, PROCEDURE 用IS。

DBMS_OUTPUT.PUT_LINE('Hello World!'); 是一个输出语句。

2. 执行存储过程

Oracle返回结果需要使用包，那么存储过程似乎只能在数据库中执行或被其他调用，编程语言似乎并不能直接调用存储过程返回数据，是否能执行他有待研究。那么首先在数库中执行上面的存储过程。

BEGIN

FirstPro（）；//注意有括号

END;

运行后输出Hello World。

3. 下面写一个稍复杂的存储过程，他定义了变量，进行了运算，输出一个count操作所用的时间。

CREATE OR REPLACE procedure testtime

n_start number;

n_end number;

samplenum number;

use_time number;

begin

n_start:=dbms_utility.get_time;

select count(*) into samplenum from emp;

n_end:=dbms_utility.get_time;

use_time:= n_end - n_start;

dbms_output.put_line('This statement cost '|| use_time ||' miliseconds');

end;

4. 下面试验下怎么能给存储过程赋值

CREATE OR REPLACE procedure test(num in number) is

begin

dbms_output.put_line('The input numer is:' || num);

end ;

今天的就到这，明天将调用这个存储过程，并试验一写对表的操作。

1. 首先把昨天带参的存储过程执行一下

declare

n number;

begin

n:=1;

test(num=>n);

end;

注;在调用存储过程时，=>前面的变量为存储过程的形参且必须于存储过程中定义的一致，而=>后的参数为实际参数。当然也不可以不定义变量保存实参，可写成如下形式：

Begin

test(num=>1);

end;

这样我们就能更清楚得看到给存储过程赋值的格式了。后面打算用存储过程操作一些表，按照增删改查的顺序依次建立存储过程。

2. 插入

CREATE OR REPLACE

procedure proc_test_Insert_Single(e_no in number,e_name in varchar ,s in varchar,d in varchar)

begin

insert into emp (emp_id,emp_name,salary,birthday) values (e_no,e_name,s,d);

end;

调用：

DECLARE

i NUMBER;

n varchar(5);

s varchar(11);

d varchar(10);

BEGIN

i:=10;

n := 'text11';

s:='3998';

d:='1998-02-02';

PROc_TEST_Insert_single(e_no => i,e_name=>n,s=>s,d=>d);

END;

注：调用存储过程声明varchar时，必须限定长度，即斜体的部分不能少。同时如果给变量赋值时大于限定的长度了，则会提示ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小。

3．更新

create or replace procedure proc_test_update_Single(e_no in number,s in varchar)

begin

UPDATE emp set salary =s where emp_id=e_no;

end;

调用：

DECLARE

n NUMBER;

s varchar(11);

BEGIN

n := 2;

s:=3998;

PROc_TEST_UPdate_single(e_no => n,s=>s);

END;

4. 号外，今天在开发过程中正好有个数据库更新操作可用存储过程实现，顺便练习一下，需求是将一个表中的ID字段，查出来更新到另一个表中，两个表通过b_bs和b_kgh关联。存储过程如下：

create or replace procedure update_yygzdbid

bs varchar(20);

kgh varchar(20);

bid number;

cursor c_db is select b_id,b_bs,b_kgh from pmdcdb;

begin

for temp in c_db loop

update yygz_db set b_id= temp.b_id where g_bs=temp.b_bs and g_bh=temp.b_kgh;

end loop;

end;

运行这个存储过程：

Begin

update_yygzdbid();

end;

说明：

(1).在没有参数的存储过程定义时存储过程的名称不需要括号，写成update_yygzdbid()是错误的，

(2). cursor c_db是定义一个游标，获得查询语句的结果集，

(3). For temp in c_bd loop

Begin

End；

End loop

是循环游标，其形式类似于C#中的foreach, 获得字段：temp.b_id。

5. 查询

最后我们做一个查询的存储过程，能够返回一个值，注意不是结果集，结果集是明天的目标。

CREATE OR REPLACE

procedure proc_test_Select_Single(t in varchar,r out varchar )

begin

select salary into r from emp where emp_name=t;

end;

这个存储过程使用了2个参数，并分别出现了IN和OUT，in代表输入，out用于输出，从下面的语句也可以看到salary写入到变量r中了，这个r我们可以在调用存储过程后得到。

这时编译后会出现一个Warning(1,48): PLW-07203: 使用 NOCOPY 编译器提示可能对参数 'R' 有所帮助，那么nocopy是什么呢，nocopy主要是针对in|out record/index-by table/varray/varchar2提高效率使用的, 对于number使用nocopy与否基本没有影响.所以在'enable:performance'情况下不会对number提示warning.

我们把第一行改为：procedure proc_test_Select_Single(t in varchar,r out nocopy varchar )

现在即使对in的varchar没有使用nocopy也不会提示警告，

DECLARE

T varchar2(4);

R VARCHAR2(4);

BEGIN

T := 'zz';

PROC_TEST_SELECT_SINGLE(T => T,R => R );

DBMS_OUTPUT.PUT_LINE('R = ' || R);

END;

运行后即可在输出中看到结果了。

三、

1. 今天我们首先写一个涨工资的存储过程，给每个低于5k工资的人涨点钱。

CREATE OR REPLACE PROCEDURE p_test(forRaise in number)

begin

for v_emp in (select * from emp) loop

if(v_emp.salary<'5000') then

update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;

end if;

end loop;

end;

调用：

DECLARE

FORRAISE NUMBER;

BEGIN

FORRAISE :=1;

P_TEST(FORRAISE => FORRAISE);

END;

这里要注意两个地方：

（1）循环中begin和end不是必须的

（2）这里增加了if语句，其格式比较简单就不细说了。

（3）这里没有定义游标，在游标的位置直接用select语句代替了。

2．这里顺便介绍下另外一种循环，while循环，实现同样的功能

CREATE OR REPLACE PROCEDURE p_test(forRaise in number)

cursor c is select * from emp;

v_row emp%rowtype;

begin

open c;

fetch c into v_row;

while c%found Loop

if(v_row.salary<'5000') then

update emp set salary =(v_row.salary+forRaise) where emp_id=v_row.emp_id;

end if;

fetch c into v_row;

end loop;

close c;

end;

说明：

（1）这里需要定义一个游标，还要定义一个emp%rowtype类型的变量，%前面是表名，后面表示这个表的一行，

（2）在使用游标前还要显示的打开游标，并将其赋值到row中，使用后关闭游标。

（3） C%found表示只有row中有值的时候才会进行循环。

（4）经过对比发现于while循环相比，for循环更像是C#中的foreach，使用起来方便很多。

（5）另从9i开始提供的动态游标类型sys_refcursor，以前的版本必须要先创建一个ref cursor的类型，现在多个

3. 现在我们使用程序调用下涨工资的存储过程，这个存储过程是没有返回值的。

OracleConnection conn = new OracleConnection(); //创建一个新连接

conn.ConnectionString = "Data Source='ds';user id='id ';password='pwd';"; OracleCommand cmd = new OracleCommand("P_TEST", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);

p1.Value = 1;

p1.Direction = System.Data.ParameterDirection.Input;

cmd.Parameters.Add(p1);

conn.Open();

int r=cmd.ExecuteNonQuery();

conn.Close();

这样我们就可以给员工涨工资了，说明：

（1）虽然给多个人涨了公司，但r的值是1，他只调用了1个存储过程，或者说受影响的只是1个。

（2）参数P1的名字必须和存储过程中的一样否则会提示：调用 'P_TEST' 时参数个数或类型错误。

4. 现在我们试着从存储过程中得到点结果吧，我先看看我给几个人涨了工资，我每个月一共要多付多少钱了。

改动存储过程：

CREATE OR REPLACE PROCEDURE p_test(forRaise in number,res out number)

begin

res:=0;

for v_emp in (select * from emp) loop

if(v_emp.salary<'4000') then

update emp set salary =(v_emp.salary+forRaise) where emp_id=v_emp.emp_id;

res:=res+1;

end if;

end loop;

end;

增加了一个out 的number型，记录改动的次数。然后相应的调整C#程序，获得这个改动的次数。

OracleCommand cmd = new OracleCommand("P_TEST", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("forRaise", OracleType.UInt32);

p1.Value = 4;

p1.Direction = System.Data.ParameterDirection.Input;

OracleParameter p2 = new OracleParameter("res", OracleType.UInt32);

p2.Value = 10;

p2.Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

conn.Open();

int r=cmd.ExecuteNonQuery();

conn.Close();

MessageBox.Show(“你已经给:”+p2.Value.ToString()+“人涨了工资”);

好了，今天就到这，下次返回数据集。

Oracle使用存储过程返回结果集必须使用包，包包括包头和包体两部分，包头是定义部分包体是具体的实现

包头：

CREATE OR REPLACE

PACKAGE pkg_test_select_mul

TYPE myrctype IS REF CURSOR;

PROCEDURE proc(s number, res OUT myrctype);

END pkg_test_select_mul;

这里定义了个一个游标和一个存储过程。

包体：

CREATE OR REPLACE

PACKAGE BODY "PKG_TEST_SELECT_MUL" AS

PROCEDURE proc(s in number,res OUT myrctype)

BEGIN

OPEN res FOR Select emp_id,emp_Name, salary,birthday From emp where salary> s;

END proc;

END PKG_TEST_SELECT_MUL;

这里实现里包头中定义的存储过程，实现了查询工资超过一定数额的人的信息，而游标则不用重新定义了，且存储过程中的参数名必须和定义中的一致。下面我们看一下C#的调用部分。

OracleConnection conn = new OracleConnection(); //创建一个新连接

conn.ConnectionString = "Data Source='" + "MyTest" + "';user id='" + "azkaser" + "';password='" + "sti" + "';"; //写连接串

OracleCommand cmd = new OracleCommand("PKG_TEST_SELECT_MUL.proc", conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter("s", OracleType.Number);

p1.Value = 4000;

p1.Direction = ParameterDirection.Input;

OracleParameter p2 = new OracleParameter("res", OracleType.Cursor);

p2.Direction = ParameterDirection.Output;

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

conn.Open();

OracleDataReader myReader = cmd.ExecuteReader();

while (myReader.Read())

{

MessageBox.Show(myReader.GetString(1));

}

conn.Close();

程序将得到的结果存放在OracleDataReader的对象中。

到此简单的Oracle存储过程操作就此就全部完成了，程序写的很随便，目的就是实现功能，将来有时间会进一步

语句块定义：

Sql代码收藏代码

decalre

-- 变量声明

var1 number(2); -- 仅声明

var2 char(2) := '11'; -- 在声明的同时初始化

begin

-- 语句

end; -- 语句块结束

if 语句

Sql代码收藏代码

if a = 1 or b = 2 then

elsif c = 3 then

else

end if;

case 语句

case语句如果作为分支控制语句，最后结束语句是end case，如果是作为select语句里的控制语句则只需要end。

Sql代码收藏代码

declare

num number(10) := 1;

begin

case

when num = 0 then dbms_output.put_line( 'zero');

when num = 1 then dbms_output.put_line( 'one');

else dbms_output.put_line( 'default');

end case;

case num

when 0 then dbms_output.put_line( 'zero');

when 1 then dbms_output.put_line( 'one');

else dbms_output.put_line( 'default');

end case;

end;

for 循环

for循环主要有两个用处。

1、循环一个范围

格式：for i in [start .. end] loop ... end loop;

Sql代码收藏代码

for i in 0..9 loop

dbms_output.put_line('i:' || i);

end loop;

2、遍历隐式游标

隐式游标的好处是不需要手动关闭，方便

Sql代码收藏代码

for currow in (

select t.col1, t.col2

from tableName t

where ...

) loop

if currow.col1 = 0 then

return; -- 中止sp，返回

end if;

end loop;

while 循环

Sql代码收藏代码

isok := 9;

while isok >= 0 loop

isok := isok - 1;

if isok = 8 then

continue; -- 与编程语言的 continue 语义一样，跳过当前循环的剩余语句，回到循环开始

end if;

if isok = 4 then

exit; -- 与编程语言的 break 语义一样，跳出循环

end if;

dbms_output.put_line('isok:' || isok);

end loop;

dbms_output.put_line('outside while loop .');

存储过程定义

Sql代码收藏代码

create or replace procedure sp_name (

-- 入参、出参列表，逗号分隔。

uid in varchar2, -- 不能带长度信息

startDate in date, -- 第二个输入参数

defaultVar in varchar2 default "", -- 默认参数，如果不传，要注意参数的顺序

isok out number, -- 输出参数

result out varchar2 -- 第二个输出参数

)

-- 变量声明，每个声明用分号结束。可以在声明的同时初始化

var1 varchar2(11);

var2 number(2) := 123;

begin

-- 字符串拼接用 ||

dbms_output.put_line('isok:' || 'abc');

-- 调用其他存储过程

sub_sp_name(param1, prarm2, outParam1, outParam2);

end; -- 存储过程结束

函数定义

Sql代码收藏代码

create or replace function func (

-- 入参、出参列表，逗号分隔。

uid in varchar2, -- 不能带长度信息

startDate in date, -- 第二个输入参数

defaultVar in varchar2 default "", -- 默认参数，如果不传，要注意参数的顺序

isok out number, -- 输出参数

result out varchar2 -- 第二个输出参数

)

return number -- 定义返回类型

-- 变量声明，每个声明用分号结束。可以在声明的同时初始化

var1 varchar2(11);

var2 number(2) := 123;

begin

-- 字符串拼接用 ||

dbms_output.put_line('isok:' || 'abc');

return ret_val;

end;

存储过程与函数异同

1、两者定义类似，都可以带输入输出参数。

2、函数有返回值，存储过程没有。

3、函数的调用要在select语句里；而存储过程不用，可以独立调用。

游标

隐式游标

隐式游标的好处是不需要手动关闭，方便

Sql代码收藏代码

for currow in (

select t.col1, t.col2

from tableName t

where ...

) loop

if currow.col1 = 0 then

return; -- 中止sp，返回

end if;

end loop;

显式游标

Sql代码收藏代码

declare

isok integer;

v_event_id number(10);

v_isagain number(2);

v_rate number(2);

v_sender char(11) := '13800138000';

cursor cursorVar is select event_id, isagain, rate from call_event where sender = v_sender; -- 声明游标

begin

open cursorVar; -- 打开游标

loop

fetch cursorVar into v_event_id, v_isagain, v_rate; -- 取值

exit when cursorVar%notfound; --当没有记录时退出循环

dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);

end loop;

close cursorVar; -- 关闭游标

--游标的属性有：%FOUND,%NOTFOUNRD,%ISOPEN,%ROWCOUNT;

--%FOUND:已检索到记录时，返回true

--%NOTFOUNRD：检索不到记录时，返回true

--%ISOPEN:游标已打开时返回true

--%ROWCOUNT:代表检索的记录数，从1开始

end;

带参数游标

Sql代码收藏代码

declare

isok integer;

v_event_id number(10);

v_isagain number(2);

v_rate number(2);

v_sender char(11) := '13800138000';

cursor cursorVar(p_sender varchar2) is select event_id, isagain, rate from call_event where sender = p_sender; -- 声明游标

begin

open cursorVar(v_sender); -- 打开游标，在括号里传参。

loop

fetch cursorVar into v_event_id, v_isagain, v_rate; -- 取值

exit when cursorVar%notfound; --当没有记录时退出循环

dbms_output.put_line(v_event_id || ', ' || v_isagain || ', ' || v_rate);

end loop;

close cursorVar; -- 关闭游标

end;

来自为知笔记(Wiz)

posted @ 2016-03-21 21:31 暗夜小精灵～～阅读(362) 评论(0) 编辑收藏举报

刷新页面返回顶部

小小5呀

day13_存储过程小记

公告