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
...
IS
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)
AS
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
IS
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
IS
BEGIN
DBMS_OUTPUT.put_line ('Hello World! '||chr(10)||'I am a Happy DBA Secooler!');
END;
/
或者
CREATE OR REPLACE PROCEDURE proc_helloworld
IS
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"
IS
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
IS
BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;
8.小结
上面的实验，我通过编写、创建、运行、修改、调试、删除和获取七个内容描述了一个存储过程的开发和维护过程。
试验，实验还有实践，技术工作者永远不变的途径！
Goodluck Friends!
一、过程 (存储过程)
过程是一个能执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一个子程序。
示例1：声明存储过程，该过程返回dept表行数
DECLARE
PROCEDURE getDeptCount
AS
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) --参数的数据类型不能指定长度
AS
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)
AS
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)
AS
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
)
AS
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)
)
IS
BEGIN
如果有输出参数就必须有有一个参数进行接收 ;
CREATE OR REPLACE PROCEDURE hello(
p_name IN VARCHAR2,
p_age OUT emp.sal%TYPE
)
IS
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
)
IS
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),
1,
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 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
is
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
is
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
is
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
is
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
is
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
is
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)
is
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)
is
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
as
function test1(in1 in varchar2)
return number;
procedure test2 (in2 in varchar2);
end peace_pkg;
---关于包的例子 定义包体
create or replace package body peace_pkg
as
function test1(in1 in varchar2)
return number
as
temp number;
begin
temp := 0;
return temp;
end;
procedure test2 (in2 in varchar2)
is
begin
dbms_output.put_line(in2);
end;
end peace_pkg;
用了两年Oracle还没写过存储过程，真是十分惭愧，从今天开始学习Oracle存储过程，完全零起点，争取每日一篇学习笔记，可能开始认识的不全面甚至有错误，但坚持下来一定会有收获。
1. 建立一个存储过程
create or replace PROCEDURE firstPro
IS
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
is
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)
as
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)
as
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
as
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 )
as
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)
as
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)
as
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)
is
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
AS
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)
IS
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 -- 第二个输出参数
)
as
-- 变量声明，每个声明用分号结束。可以在声明的同时初始化
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 -- 定义返回类型
as
-- 变量声明，每个声明用分号结束。可以在声明的同时初始化
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;