day13_存储过程小记
1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 OUT NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN 转载于:https://www.cnblogs.com/xiaoxiao5ya/p/760aa0f4516356e837940ad6c759ac46.html
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: ;
通过提示，问题出现在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;
来自为知笔记(Wiz)
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！