PL/SQL之八 存储过程

1.概述

    1.1模块化代码的好处：复用性好、易于管理。

    1.2语句块结构：所有模块类型而言，语句块结构相同。首先是语句块头部、模块名称、参数列表；声明部分由变量、游标等组成；可执行部分；异常处理部分。

2.创建存储过程

2.1创建语法

CREATE OR REPLACE PROCEDURE name[(parmeter[,parmeter])]AS   [Local declarations]BEGIN   executable statements;[Exception    exception handlers;]END [name];

示例：

create or replace procedure pro_testisbegin  dbms_output.put_line('当前系统时间：'||sysdate);exception       when others then          dbms_output.put_line('发生错误！');end pro_test; 

2.2如何查询存储过程名称及源代码 

--查找存储过程名称、状态SELECT * FROM user_objects a where a.OBJECT_TYPE='PROCEDURE';--查找存储过程源代码SELECT to_char(line,99)||'>',textFROM USER_SOURCE b where b.name='PRO_TEST';

2.3存储过程传入和传出参数 

      参数在调用环境和服务器之间来回传递值，参数传递模式有三种：IN、OUT、IN OUT。通过参数传递模式来说明参数是传入还是传出的。

      三种参数类型：

            IN：给程序传值 常量、字面值或表达式 在程序的默认模式下不能修改；只读的值。

            OUT：从程序返回值，不能赋予默认值，必须是变量，只有当程序执行成功时才能赋值。只写的值。

            IN  OUT：传入值，也从程序返回；必须是变量。

      匹配形参和实参方法：位置表示法和命名表示法。位置表示法是根据位置序号来标识；命名表示法是显示的标识方法，使用符号‘=>’。

     示例：

--1.创建存储过程create or replace procedure pro_test1(p_c1 in varchar2,p_c2 out varchar2,p_c3 in out varchar2)isbegin      p_c2:=p_c1;      if(p_c1='cde') then      p_c3:='cde';      else      p_c3:=p_c1;       end if;end;--2.调用存储过程declare v_c2 varchar2(20); v_inOut varchar2(20):='cdef';begin  pro_test1(p_c1 => 'abc',            p_c2 => :v_c2,    --out模式的参数，            p_c3 => :v_inOut  --in out 模式参数，必须以变量来传递或接受。           );end;

3.特别说明

3.1定义变量的规范  变量以v_开头，常量以c_开头,游标以_cursor结尾，异常以e_开头。 

3.2存储过程防止 select into中取不到值，在存储过程中添加  异常处理部分，‘when no_data_found then dbms_output.put_line('no data found1')’;

3.3CURSOR,打开后，如果不fetch，是不能用游标的四大属性（SQL%rowcount,SQL%Found,SQL%notfound,SQL%isopen）来判断游标中是否有数据。原因：打开游标后，第一行成为当前行，这时只存在一个指向当前行的指针，它的数据是空的，无法判定；fetch时，行数据拷贝到变量或记录变量，并且指针指向下一行，这一行成为当前行。每fetch一次，指针向下移一行。

3.4.存储过程中捕获异常可以使用内置函数SQLCODE()，SQLERRM()来获得系统错误号和错误信息。

3.5.隐式游标的三种属性详细说胆

     SQL%FOUND：未执行任何DML语句之前，值为null；DML语句执行后，SQL%Found值变化如下：

               true:Insert\Delete\Update\Select into至少返回一行。

     SQL%RowCount:没有执行任何DML语句之前，SQL%RowCount值为null;对于 SELECT INTO语句 执行成功返回1；Insert into 时返回1；没有成功返回0，同时产生一个no_data_found异常。

     SQL%IsOpen:它是布尔值，如果游标打开，则为true;游标关闭，则为false。对于隐式游标 SQL%Isopen一直是close,隐式游标在DML语句执行时打开，结束时关闭。

4.练习

4.1显示当天是周几、当前登录用户名和系统时间；显示当前用户模式下有效和无效的存储过程数量。

create or replace procedure pro_test2is      v_day varchar2(20);      v_user varchar2(20);      v_time varchar2(20);            type rec_procedure is record  (      rec_statue varchar2(20),      rec_num number      );      v_rec_procdure rec_procedure;            cursor c_objects is     select status,count(*) num  from user_OBJECTS a where a.OBJECT_TYPE='PROCEDURE' group by status;  begin      select to_char(sysdate,'DAY'),user,to_char(sysdate,'HH24:mi:ss') into v_day,v_user,v_time  from dual;      dbms_output.put_line('当前星期几：'||v_day||',当前用户:'||v_user||',当前时间：'||v_time);       for r_object in c_objects loop            v_rec_procdure.rec_statue:=r_object.STATUS;            v_rec_procdure.rec_num:=r_object.NUM;            dbms_output.put_line(v_rec_procdure.rec_statue||'的个数为:'||v_rec_procdure.rec_num);      end loop;   end;

4.2 实现查找某个科室代码的相关信息

CREATE OR REPLACE PROCEDURE pro_01(deptCode NUMBER --科室代码，接受某个科室编号                                  )AS--标量变量v_varchar VARCHAR2(20);v_date DATE;v_language VARCHAR2(40);v_number NUMBER(3):=1;v_y_char VARCHAR2(20);v_y_date DATE;v_deptCode NUMBER(2):=deptCode;--定义游标CURSOR dualCur IS SELECT 'abcde',SYSDATE,USERENV('LANGUAGE') FROM dual;CURSOR empCur IS SELECT a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno FROM scott.emp a;--参数游标，查询某个科室人员信息CURSOR s_empCur(v_deptNo NUMBER) IS SELECT empno,ename,job FROM scott.emp WHERE deptno=v_deptNo;--定义基于游标的 记录变量empReocrd empCur%ROWTYPE;s_empRec s_empCur%ROWTYPE;

--记录变量 语法格式：变量名 表名|视图名|游标名%RowTypev_y_empRec scott.emp%ROWTYPE;--集合变量TYPE emp_table_type IS TABLE OF empCur%ROWTYPE INDEX BY BINARY_INTEGER;emp_table emp_table_type;--异常处理v_errCode VARCHAR2(20);--错误代码v_errMsg VARCHAR2(100);--错误明细v_errLine VARCHAR2(200);--错误所在行BEGIN  IF NOT dualCur%ISOPEN THEN     OPEN dualCur;  END IF;  IF dualCur%ISOPEN THEN     dbms_output.put_line('游标是否打开：打开');  ELSE     dbms_output.put_line('游标是否打开：关闭');  END IF;   FETCH dualCur INTO v_varchar,v_date,v_language;  --dbms_output.put_line(dualCur%FOUND);  IF dualCur%FOUND THEN  dbms_output.put_line('游标受影响行数：'||dualCur%Rowcount);  dbms_output.put_line('input Char:'||v_varchar||',input date:'||v_date||',input language:'||v_language);  ELSE     dbms_output.put_line('没有找到数据');  END IF ;  IF dualCur%ISOPEN THEN      CLOSE dualCur;  END IF;  IF dualCur%ISOPEN THEN     dbms_output.put_line('游标是否打开：打开');  ELSE     dbms_output.put_line('游标是否打开：关闭');  END IF;--显式游标：基本格式：打开游标、取数、关闭游标   IF NOT empCur%ISOPEN THEN     OPEN empCur;  END IF;   LOOP    FETCH empCur INTO empReocrd;    EXIT WHEN empCur%NOTFOUND;    dbms_output.put_line(empReocrd.Empno||empReocrd.Ename||empReocrd.Job||empReocrd.Mgr||empReocrd.Hiredate||empReocrd.Sal||empReocrd.Comm||empReocrd.Deptno);  END LOOP;  IF empCur%ISOPEN THEN    CLOSE empCur;  END IF;--显式游标，集合变量使用  IF NOT empCur%ISOPEN THEN    OPEN empCur;  END IF;   LOOP     FETCH empCur INTO emp_table(v_number);     EXIT WHEN empCur%NOTFOUND;     dbms_output.put_line('姓名：'||emp_table(v_number).ename);  END LOOP;  IF empCur%ISOPEN THEN    CLOSE empCur;  END IF; --显式游标，带参数游标  IF NOT s_empCur%ISOPEN THEN     OPEN s_empCur(v_deptCode);  END IF;  FOR s_empRow IN s_empCur LOOP    dbms_output.put_line('NO='||s_empRow.Empno||',name='||s_empRow.Ename);  END LOOP; --显式游标：for循环格式，这种格式不需要显式的打开游标，不需要显示的取数，不需要显示关闭游标  FOR empRow IN empCur LOOP    dbms_output.put_line('姓名：'||empRow.Ename||',工作:'||empRow.Job);  END LOOP; --隐式游标使用   --1)   SELECT 'hij',SYSDATE  INTO v_y_char,v_y_date FROM dual;   IF SQL%FOUND THEN      dbms_output.put_line(SQL%ROWCOUNT);      dbms_output.put_line('v_y_char:'||v_y_char||',v_y_date:'||v_y_date);   END IF ;   --2)   SELECT * INTO v_y_empRec FROM scott.emp a;   LOOP     EXIT WHEN SQL%NOTFOUND;     dbms_output.put_line(v_y_empRec.Empno||','||v_y_empRec.Ename||'。');   END LOOP;EXCEPTION   WHEN OTHERS THEN   v_errCode   := SQLCODE;   v_errMsg    := substr(SQLERRM, 1, 200);   v_errLine := SUBSTR(dbms_utility.format_error_backtrace, 1, 200);   dbms_output.put_line(v_errCode || '|' || v_errMsg || '|' || v_errLine);END pro_01;