PL/SQL之八 存储过程

数据库 waitig 404℃ 百度已收录 0评论

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_test
is
begin
  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)||'>',text
FROM 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)
is
begin
      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_test2
is
      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;

--记录变量 语法格式:变量名 表名|视图名|游标名%RowType
	v_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;


本文由【waitig】发表在等英博客
本文固定链接:PL/SQL之八 存储过程
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)