精华内容
下载资源
问答
  • 这种形式来写,但是条件一的时候,发现大量的or会导致语句特别慢,后改用动态sql拼接条件,但是发现这种写法非常不稳定,经常会特别慢,有时候在sql后面拼接一空格都会导致查询速度变化非常大。同样的语句,在...
  • ORACLE存储过程

    万次阅读 多人点赞 2018-11-02 18:14:48
    oracle存储过程 目录 一.什么是存储过程 二.为什么要写存储过程 三.存储过程基础 1.存储过程结构 2.存储过程语法 3.pl/sql处理存储过程 四.存储过程进阶 1.BUIK COLLECT 2.FORALL 3.pl/sql调试存储过程 ...

                                                  oracle存储过程

    目录

             一.什么是存储过程

    二.为什么要写存储过程

    三.存储过程基础

    1.存储过程结构

    2.存储过程语法

    3.pl/sql处理存储过程

    四.存储过程进阶

    1.BUIK COLLECT

    2.FORALL

    3.pl/sql调试存储过程

    4.案例实战

    附.参考资料


    一.什么是存储过程

    存储过程,百度百科上是这样解释的,存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。

    简单的说就是专门干一件事一段sql语句。

    可以由数据库自己去调用,也可以由java程序去调用。

    在oracle数据库中存储过程是procedure。

    二.为什么要写存储过程

    1.效率高

      存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的sql语句我们要保存到其他地方(例如:记事本  上),都要先分析编译才会执行。所以想对而言存储过程效率更高。

    2.降低网络流量

    存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的sql语句。

    3.复用性高

    存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。

    4.可维护性高

    当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。

    5.安全性高

    完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

    三.存储过程基础

    1.存储过程结构

    (1).基本结构

    Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常(可写可不写,要增强脚本的容错性和调试的方便性那就写上异常处理)

    (2).无参存储过程

    CREATE OR REPLACE PROCEDURE demo AS/IS
    	变量2 DATE;
    	变量3 NUMBER;
    BEGIN
    	--要处理的业务逻辑
    	EXCEPTION    --存储过程异常
    END 
    

    这里的as和is一样任选一个,在这里没有区别,其中demo是存储过程名称。

    (3).有参存储过程

    a.带参数的存储过程

    CREATE OR REPLACE PROCEDURE 存储过程名称(param1 student.id%TYPE)
    AS/IS
    name student.name%TYPE;
    age number :=20;
    BEGIN
      --业务处理.....
    END

    上面脚本中,

    第1行:param1 是参数,类型和student表id字段的类型一样。

    第3行:声明变量name,类型是student表name字段的类型(同上)。

    第4行:声明变量age,类型数数字,初始化为20

     

    b.带参数的存储过程并且进行赋值

    CREATE OR REPLACE PROCEDURE 存储过程名称(
           s_no in varchar,
           s_name out varchar,
           s_age number) AS
    total NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;
      dbms_output.put_line('符合该年龄的学生有'||total||'人');
      EXCEPTION
        WHEN too_many_rows THEN 
        DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 
    END

    上面脚本中:

    其中参数IN表示输入参数,是参数的默认模式。
    OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
    OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
    IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去

    第7行:查询语句,把参数s_age作为过滤条件,INTO关键字,把查到的结果赋给total变量。

    第8行:输出查询结果,在数据库中“||”用来连接字符串

    第9—11行:做异常处理

    2.存储过程语法

    (1).运算符

    这里s,m,n是变量,类型是number;

    分类

    运算符

    含义

    示例表达式

     

     

     

    算术运算符

    +

    s := 2 + 2;

    -

    s := 3 – 1;

    *

    s := 2 * 3;

    /

    s := 6 / 2;

    mod(,)

    取模,取余

    m : = mod(3,2)

    **

    乘方

    10**2 =100

     

     

     

    关系运算符

    =

    等于

    s = 2

    <>或!=或~=

    不等于

    s != 2

    <

    小于

    s < 3

    >

    大于

    s > 0

    <=

    小于等于

    s <= 9

    >=

    大于等于

    s >= 1

     

     

    比较运算符

    LIKE

    满足匹配为true

    ‘li’ like ‘%i’返回true

    BETWEEN

    是否处于一个范围中

    2 between 1 and 3 返回true

    IN

    是否处于一个集合中

    ‘x’ in (‘x’,’y’) 返回true

    IS NULL

    判断变量是否为空

    若:n:=3,n is null,返回false

     

    逻辑运算符

    AND

    逻辑与

    s=3 and c is null

    OR

    逻辑或

    s=3 or c is null

    NOT

    逻辑非

    not c is null

     

    其他

    :=

    赋值

    s := 0;

    ..

    范围

    1..9,即1至9范围

    ||

    字符串连接

    ‘hello’||’world’

     

    (2).SELECT INTO STATEMENT语句

    该语句将select到的结果赋值给一个或多个变量,例如:

    CREATE OR REPLACE PROCEDURE DEMO_CDD1 IS
    s_name VARCHAR2;   --学生名称
    s_age NUMBER;      --学生年龄
    s_address VARCHAR2; --学生籍贯
    BEGIN
      --给单个变量赋值
      SELECT student_address INTO s_address
      FROM student where student_grade=100;
       --给多个变量赋值
      SELECT student_name,student_age INTO s_name,s_age
      FROM student where student_grade=100;
      --输出成绩为100分的那个学生信息
      dbms_output.put_line('姓名:'||s_name||',年龄:'||s_age||',籍贯:'||s_address);
    END

    上面脚本中:

    存储过程名称:DEMO_CDD1, student是学生表,要求查出成绩为100分的那个学生的姓名,年龄,籍贯

    (3).选择语句

    a.IF..END IF

    学生表的sex字段:1-男生;0-女生

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    END IF

    b.IF..ELSE..END IF

    IF s_sex=1 THEN
      dbms_output.put_line('这个学生是男生');
    ELSE
      dbms_output.put_line('这个学生是女生');
    END IF

    (4).循环语句

    a.基本循环

    LOOP
      IF 表达式 THEN
        EXIT;
      END IF
    END LOOP;

    b.while循环

    WHILE 表达式 LOOP
      dbms_output.put_line('haha');
    END LOOP;

    c.for循环

    FOR a in 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
    END LOOP;

    (5).游标

        Oracle会创建一个存储区域,被称为上下文区域,用于处理SQL语句,其中包含需要处理的语句,例如所有的信息,行数处理,等等。

        游标是指向这一上下文的区域。 PL/SQL通过控制光标在上下文区域。游标持有的行(一个或多个)SQL语句返回。行集合光标保持的被称为活动集合。

    a.下表是常用的游标属性

    属性

    描述

    %FOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回true。否则,返回false。

    %NOTFOUND

    如果DML语句执行后影响有数据被更新或DQL查到了结果,返回false。否则,返回true。

    %ISOPEN

    游标打开时返回true,反之,返回false。

    %ROWCOUNT

    返回DML执行后影响的行数。

    b.使用游标

    声明游标定义游标的名称和相关的SELECT语句:

    CURSOR cur_cdd IS SELECT s_id, s_name FROM student;

    打开游标游标分配内存,使得它准备取的SQL语句转换成它返回的行:

    OPEN cur_cdd;

    抓取游标中的数据,可用LIMIT关键字来限制条数,如果没有默认每次抓取一条:

    FETCH cur_cdd INTO id, name ;

    关闭游标来释放分配的内存:

    CLOSE cur_cdd;

    3.pl/sql处理存储过程

    (1).新建存储过程:右键procedures,点击new,弹出PROCEDURE框,再点击OK,如下图:

     

    (2).在下面的编辑区,编写存储过程脚本

     

    (3).在这里我们编写一个demo_cdd存储过程,要求输出“hello world”,如下图:

     

    (4).右键刚才新建的存储过程名称,点击“Test”,在点击执行按钮

     

    4.案例实战

    场景:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    分析:

    如果我们直接运行运行这条sql,因数据量太大会把数据库undo表空间撑爆,从而发生异常。那我们来写个存储过程,进行批量更新,我们每10万条提交一次。

    CREATE OR REPLACE PROCEDURE process_student is
    total NUMBER := 0;
    i NUMBER := 0;
    BEGIN
      SELECT COUNT(1) INTO total FROM student;
      WHILE i<=total LOOP
        UPDATE student SET grade=grade+1 WHERE s_no=i;
        i := i + 1;
        IF i >= 100000 THEN
          COMMIT;
        END IF;
      END LOOP;
      dbms_output.put_line('finished!');
    END;
    

    四.存储过程进阶

           在上面的案例中,我们的存储过程处理完所有数据要多长时间呢?事实我没有等到它执行完,在我可接受的时间范围内它没有完成。那么对于处理这种千万级数据量的情况,存储过程是不是束手无策呢?答案是否定的,接下来我们看看其他绝招。

           我们先来分析下执行过程的执行过程:一个存储过程编译后,在一条语句一条语句的执行时,如果遇到pl/sql语句就拿去给pl/sql引擎执行,如果遇到sql语句就送到sql引擎执行,然后把执行结果再返回给pl/sql引擎。遇到一个大数据量的更新,则执行焦点(正在执行的,状态处于ACTIVE)会不断的来回切换。

           Pl/SQL与SQL引擎之间的通信则称之为上下文切换,过多的上下文切换将带来过量的性能负载。最终导致效率降低,处理速度缓慢。

           从Oracle8i开始PL/SQL引入了两个新的数据操纵语句:FORALLBUIK COLLECT,这些语句大大滴减少了上下文切换次数(一次切换多次执行),同时提高DML性能,因此运用了这些语句的存储过程在处理大量数据时速度简直和飞一样。

    1.BUIK COLLECT

        Oracle8i中首次引入了Bulk Collect特性,Bulk Collect会能进行批量检索,会将检索结果结果一次性绑定到一个集合变量中,而不是通过游标cursor一条一条的检索处理。可以在SELECT INTO、FETCH INTO、RETURNING INTO语句中使用BULK COLLECT,接下来我们一起看看这些语句中是如何使用BULK COLLECT的。

    (1).SELECT INTO

    查出来一个结果集合赋值给一个集合变量。

    语法结构是:

    SELECT field BULK COLLECT INTO var_conllect FROM table where colStatement;

    说明:

           field:要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应)。

           var_collect:集合变量(联合数组等),用来存放查到的结果。

           table:表名,要查询的表。

           colStatement:后面过滤条件语句。比如s_age < 10;

    例子:查出年龄小于10岁的学生姓名赋值给数组arr_name变量

    SELECT s_name BULK COLLECT INTO arr_name FROM s_age < 10;

    (2).FETCH INTO

    从一个集合中抓取一部分数据赋值给一个集合变量。

    语法结构如下:

    FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]

    说明:

            cur1:是个数据集合,例如是个游标。

            var_collect:含义同上。

            [LIMIT rows]:可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据。

    例子:给年龄小于10岁的学生的年级降一级。

    --查询年龄小于10岁的学生的学号放在游标cur_no里
    CURSOR cur_no IS 
    		SELECT s_no FROM student WHERE s_age < 10;
    
    --声明了一个联合数组类型,元素类型和游标cur_no每个元素的类型一致
    TYPE ARR_NO IS VARRAY(10) OF cur_no%ROWTYPE;
    
    --声明一个该数组类型的变量no
    no ARR_NO;
    BEGIN
      FETCH cur_no BULK COLLECT INTO no LIMIT 100;
      FORALL i IN 1..no.count SAVE EXCEPTONS
    	UPDATE student SET s_grade=s_grade-1 WHERE no(i);
    END;
    

    说明:先查出年龄小于10岁的学生的学号放在游标里,再每次从游标里拿出100个学号,进行更新,给他们的年级降一级。

    (3).RETURNING

    BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)。

    再看一眼学生表的字段情况:student(s_no, s_name, s_age, s_grade)

    语法结构如下:

    DMLStatement
           RETURNING field BULK COLLECT INTO var_field;

    说明:

            DMLStatement:是一个DML语句。

            field:是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3)。

            var_field:一个类型为该字段类型的集合,多个的话用逗号隔开,如下:

            (var_field1, var_field2, var_field3)

     

    例子:获取那些因为年龄小于10岁而年级被将一级的学生的姓名集合。

    TYPE NAME_COLLECT IS TABLE OF student.s_name%TYPE;
    names NAME_COLLECT;
    BEGIN
      UPDATE student SET s_grade=s_grade-1 WHERE s_age < 10
      RETURNING s_name BULK COLLECT INTO names;
    END;

    说明:

           NAME_COLLECT:是一个集合类型,类型是student表的name字段的类型。

           names:定义了一个NAME_COLLECT类型的变量。

    (4).注意事项

    a.不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。

    b.只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。

    c.BULK COLLECT INTO 的目标对象必须是集合类型。

    d.复合目标(如对象类型)不能在RETURNING INTO 子句中使用。

    e.如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。

    f.如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

    2.FORALL

    (1).语法

    FORALL index IN bounds [SAVE EXCEPTIONS]     
         sqlStatement;

    说明:

            index是指下标;

            bounds是一个边界,形式是start..end

            [SAVE EXCEPTIONS] 可写可不写,这个下面介绍;

            sqlStatement是一个DML语句,这里有且仅有一个sql语句;

    例子:

    --例子1:移除年级是5到10之间的学生
    FORALL i IN 5..10
           DELETE FROM student where s_grade=i;
    --例子:2,arr是一个数组,存着要升高一年级的学生名称
    FORALL s IN 1..arr.count SAVE EXCEPTIONS
           UPDATE student SET s_grade=s_grade+1 WHERE s_name=arr(i);

    (2).SAVE EXCEPTIONS

    通常情况写我们在执行DML语句时,可能会遇到异常,可能致使某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来。

           如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段,例如:(1, 02300);

           ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号;

           ERROR_CODE:存储对应异常的,oracle错误代码;

    SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。而这个异常记录集合异常的个数则由它的COUNT属性表示,即:

           SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

    (3). INDICES OF

    在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF。

    接下来我们来看看这个INDICES OF语句,用于处理稀疏数组或包含有间隙的数组(例如:一个集合的某些元素被删除了)。

    该语句语法结构是:

    FORALL i INDICES OF collection [SAVE EXCEPTIONS]
    
           sqlStatement;

    说明:

    i:集合(嵌套表或联合数组)下标。

    collection:是这个集合。

    [SAVE EXCEPTIONS]和sqlStatement上面已经解释过。

    例子:arr_std是一个联合数组,每个元素包含(name,age,grade),现在要向student表插入数据。

    FORALL i IN INDICES OF arr_stu
           INSERT INTO student VALUES(
               arr_stu(i).name,
                  arr_stu(i).age,
                  arr_stu(i).grade
           );

    (4). VALUES OF

    VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:

           如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;

           当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;

    该语句的语法结构是:

    FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
           sqlStatement;

    说明:i和collection含义如上

    联合数组请看文章(或自行百度):https://blog.csdn.net/leshami/article/details/7372061

    3.pl/sql调试存储过程

    首先,当前这个用户得有能调试存储过程的权限,如果没有的话,以数据库管理员身份给你这个用户授权:

    --userName是你要拿到调试存储过程权限的用户名
    GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO username;

    (1).右键一个存储过程名称,点击测试,如下图:

    这里我用的pl/sql是12.0.4版本的,下面截图中与低版本的pl/sql按钮位置都相同,只是图标不一样。

     

    (2).点击两次step into按钮,进入语句调试,如下图:

     

    (3).每点击一次step into按钮,会想下执行一条语句,也可以查看变量和表达式的值,如下图:

     

    查看变量值:在查看变量区域,在Variable列输入变量i,在Value列点击下,该变量的值就显示出来了。

    4.案例实战

    场景和上面的案例实战是同一个,如下:

    有表student(s_no, s_name, s_age, s_grade),其中s_no-学号,也是主键,是从1开始向上排的(例如:第一个学生学号是1,第二个是2,一次类推);s_name-学生姓名;s_age-学生年龄;s_grade-年级;这张表的数据量有几千万甚至上亿。一个学年结束了,我要让这些学生全部升一年级,即,让s_grade字段加1。

    这条sql,写出来如下:

    update student set s_grade=s_grade+1

    编写存储过程:

    (1).存储过程1

    名称为:process_student1,student表的s_no字段类型为varchar2(16)。

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT s_no FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE s_no=students(i);
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

            把student表中要更新的记录的学号拿出来放在游标CUR_STUDENT,每次从这个游标里抓取10万条数据赋值给数组students,每次更新这10万条记录。循环进行直到游标里的数据全部抓取完。

            FETCH .. BULK COLLECT INTO .. LIMIT rows语句中:这个rows我测试目前最大可以为10万条。

    (2).存储过程2(ROWID)

           如果我们这个student表没有主键,也没有索引呢,该怎么来做呢?

    分析下:

           ROWNUM是伪列,每次获取结果后,然后在结果集里会产生一列,从1开始排,每次都是从1开始排。

            ROWID在每个表中,每条记录的ROWID都是唯一的。在这种情况下,我们可以用ROWID。但要注意的是,ROWID是一个类型,注意它和VARCHAR2之间的转换。有两个方法:ROWIDTOCHAR()是把ROWID类型转换为CHAR类型;CHARTOROWID()是把CAHR类型转换为ROWID类型。

    接下来我们编写存储过程process_student2,脚本如下:

    CREATE OR REPLACE PROCEDURE process_student1 AS
        CURSOR CUR_STUDENT IS SELECT ROWIDTOCHAR(ROWID) FROM student;
        TYPE REC_STUDENT IS VARRAY(100000) OF VARCHAR2(16);
        students REC_STUDENT;
    BEGIN
      OPEN CUR_STUDENT;
      WHILE (TRUE) LOOP
        FETCH CUR_STUDENT BULK COLLECT INTO students LIMIT 100000;
        FORALL i IN 1..students.count SAVE EXCEPTIONS
          UPDATE student SET s_grade=s_grade+1 WHERE ROWID=CHARTOROWID(students(i));
        COMMIT;
        EXIT WHEN CUR_STUDENT%NOTFOUND OR CUR_STUDENT%NOTFOUND IS NULL;
      END LOO;
      dbms_output.put_line('finished');
    END;

    说明:

           我们首先查到记录的ROWID并把它转换为CHAR类型,存放到游标CUR_STUDENT里,

    再每次抓取10万条数据赋值给数组进行更新,更新语句的WHERE条件时,又把数组元素是CAHR类型的rowid串转换为ROWID类型。

    附.参考资料

    存储过程基础:

           https://www.yiibai.com/plsql/plsql_basic_syntax.html

    存储过程进阶之FORALL:

           https://blog.csdn.net/leshami/article/details/7536926

           https://blog.csdn.net/jie1336950707/article/details/49966753

    存储过程进阶之BUIL COLLECT:

           https://blog.csdn.net/leeboy_wang/article/details/7991021

           https://blog.csdn.net/leshami/article/details/7545597

    联合数组:

           https://blog.csdn.net/leshami/article/details/7372061

    展开全文
  • PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更的开发人员和DBA开始使用PL/SQL,本文讲述PL/SQL基础语法,结构和组件、以及如何设计并执行PL...
      PL/SQL是ORACLE对标准数据库语言的扩展,ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了,近几年中更多的开发人员和DBA开始使用PL/SQL,本文将讲述PL/SQL基础语法,结构和组件、以及如何设计并执行一个PL/SQL程序。
    

       PL/SQL的优点

      从版本6开始PL/SQL就被可靠的整合到ORACLE中了,一旦掌握PL/SQL的优点以及其独有的数据管理的便利性,那么你很难想象ORACLE缺了PL/SQL的情形。PL/SQL 不是一个独立的产品,他是一个整合到ORACLE服务器和ORACLE工具中的技术,可以把PL/SQL看作ORACLE服务器内的一个引擎,sql语句执行者处理单个的sql语句,PL/SQL引擎处理PL/SQL程序块。当PL/SQL程序块在PL/SQL引擎处理时,ORACLE服务器中的SQL语句执行器处理pl/sql程序块中的SQL语句。

       PL/SQL的优点如下:

      . PL/SQL是一种高性能的基于事务处理的语言,能运行在任何ORACLE环境中,支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。

      . PL/SQL支持所有SQL数据类型和所有SQL函数,同时支持所有ORACLE对象类型

      . PL/SQL块可以被命名和存储在ORACLE服务器中,同时也能被其他的PL/SQL程序或SQL命令调用,任何客户/服务器工具都能访问PL/SQL程序,具有很好的可重用性。

      . 可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。

      . PL/SQL代码可以使用任何ASCII文本编辑器编写,所以对任何ORACLE能够运行的操作系统都是非常便利的

      . 对于SQL,ORACLE必须在同一时间处理每一条SQL语句,在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理,这就占用大量的服务器时间,同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器,这就降低了网络拥挤。

       PL/SQL块结构

      PL/SQL是一种块结构的语言,组成PL/SQL程序的单元是逻辑块,一个PL/SQL 程序包含了一个或多个逻辑块,每个块都可以划分为三个部分。与其他语言相同,变量在使用之前必须声明,PL/SQL提供了独立的专门用于处理异常的部分,下面描述了PL/SQL块的不同部分:

       声明部分(Declaration section)

      声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始,如果不需要声明变量或常量,那么可以忽略这一部分;需要说明的是游标的声明也在这一部分。

       执行部分(Executable section)

      执行部分是PL/SQL块中的指令部分,由关键字BEGIN开始,所有的可执行语句都放在这一部分,其他的PL/SQL块也可以放在这一部分。

       异常处理部分(Exception section)

      这一部分是可选的,在这一部分中处理异常或错误,对异常处理的详细讨论我们在后面进行。

       PL/SQL块语法

    [DECLARE]
    ---declaration statements
    BEGIN
    ---executable statements
    [EXCEPTION]
    ---exception statements
    END

      PL/SQL块中的每一条语句都必须以分号结束,SQL语句可以使多行的,但分号表示该语句的结束。一行中可以有多条SQL语句,他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始,以END结束。注释由--标示。

       PL/SQL块的命名和匿名

      PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块。匿名程序块可以用在服务器端也可以用在客户端。

      命名程序块可以出现在其他PL/SQL程序块的声明部分,这方面比较明显的是子程序,子程序可以在执行部分引用,也可以在异常处理部分引用。

      PL/SQL程序块可背独立编译并存储在数据库中,任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块。ORACLE提供了四种类型的可存储的程序:

       . 函数

       . 过程

       . 包

       . 触发器

       函数

      函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义。定义函数的语法如下:

    FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS
    [local declarations]
    BEGIN
    execute statements
    [EXCEPTION
    exception handlers]
    END [name]

       过程

      存储过程是一个PL/SQL程序块,接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT),与函数不同,存储过程没有返回值,存储过程不能由SQL语句直接使用,只能通过EXECUT命令或PL/SQL程序块内部调用,定义存储过程的语法如下:

    PROCEDURE name [(parameter[,parameter,...])] IS
    [local declarations]
    BEGIN
    execute statements
    [EXCEPTION
    exception handlers ]
    END [name]


      包(package)

      包其实就是被组合在一起的相关对象的集合,当包中任何函数或存储过程被调用,包就被加载入内存中,包中的任何函数或存储过程的子程序访问速度将大大加快。
    包由两个部分组成:规范和包主体(body),规范描述变量、常量、游标、和子程序,包体完全定义子程序和游标。

      触发器(trigger)

      触发器与一个表或数据库事件联系在一起的,当一个触发器事件发生时,定义在表上的触发器被触发。

      变量和常量

      变量存放在内存中以获得值,能被PL/SQL块引用。你可以把变量想象成一个可储藏东西的容器,容器内的东西是可以改变的。

      声明变量

      变量一般都在PL/SQL块的声明部分声明,PL/SQL是一种强壮的类型语言,这就是说在引用变量前必须首先声明,要在执行或异常处理部分使用变量,那么变量必须首先在声明部分进行声明。

      声明变量的语法如下:

    Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]


      注意:可以在声明变量的同时给变量强制性的加上NOT NULL约束条件,此时变量在初始化时必须赋值。

      给变量赋值

      给变量赋值有两种方式:

      . 直接给变量赋值

       X:=200;
       Y=Y+(X*20);

      . 通过SQL SELECT INTO 或FETCH INTO给变量赋值

    SELECT SUM(SALARY),SUM(SALARY*0.1)
    INTO TOTAL_SALARY,TATAL_COMMISSION
    FROM EMPLOYEE
    WHERE DEPT=10;

      常量

      常量与变量相似,但常量的值在程序内部不能改变,常量的值在定义时赋予,,他的声明方式与变量相似,但必须包括关键字CONSTANT。常量和变量都可被定义为SQL和用户定义的数据类型。

    ZERO_VALUE CONSTANT NUMBER:=0;


      这个语句定了一个名叫ZERO_VALUE、数据类型是NUMBER、值为0的常量。

      标量(scalar)数据类型

      标量(scalar)数据类型没有内部组件,他们大致可分为以下四类:

       . number
       . character
       . date/time
       . boolean

      表1显示了数字数据类型;表2显示了字符数据类型;表3显示了日期和布尔数据类型。

      表1 Scalar Types:Numeric

    DatatypeRangeSubtypesdescription
    BINARY_INTEGER-214748-2147483647NATURAL
    NATURAL
    NPOSITIVE
    POSITIVEN
    SIGNTYPE
    用于存储单字节整数。
    要求存储长度低于NUMBER值。
    用于限制范围的子类型(SUBTYPE):
     NATURAL:用于非负数
     POSITIVE:只用于正数
     NATURALN:只用于非负数和非NULL值
     POSITIVEN:只用于正数,不能用于NULL值
     SIGNTYPE:只有值:-1、0或1.
    NUMBER1.0E-130-9.99E125DEC
    DECIMAL
    DOUBLE
    PRECISION
    FLOAT
    INTEGERIC
    INT
    NUMERIC
    REAL
    SMALLINT
    存储数字值,包括整数和浮点数。可以选择精度和刻度方式,语法:
    number[( [, ])]。
    缺省的精度是38,scale是0.
    PLS_INTEGER-2147483647-2147483647 与BINARY_INTEGER基本相同,但采用机器运算时,PLS_INTEGER提供更好的性能 。


      表2 字符数据类型

    datatyperangsubtypedescription
    CHAR最大长度32767字节 CHARACTER存储定长字符串,如果长度没有确定,缺省是1
    LONG最大长度2147483647字节 存储可变长度字符串
    RAW 最大长度32767字节 用于存储二进制数据和字节字符串,当在两个数据库之间进行传递时,RAW数据不在字符集之间进行转换。
    LONGRAW最大长度2147483647 与LONG数据类型相似,同样他也不能在字符集之间进行转换。
    ROWID18个字节 与数据库ROWID伪列类型相同,能够存储一个行标示符,可以将行标示符看作数据库中每一行的唯一键值。
    VARCHAR2最大长度32767字节STRINGVARCHAR与VARCHAR数据类型相似,存储可变长度的字符串。声明方法与VARCHAR相同


      表3 DATE和BOOLEAN

    datatyperange description
    BOOLEANTRUE/FALSE存储逻辑值TRUE或FALSE,无参数
    DATE01/01/4712 BC 存储固定长的日期和时间值,日期值中包含时间


      LOB数据类型

      LOB(大对象,Large object) 数据类型用于存储类似图像,声音这样的大型数据对象,LOB数据对象可以是二进制数据也可以是字符数据,其最大长度不超过4G。LOB数据类型支持任意访问方式,LONG只支持顺序访问方式。LOB存储在一个单独的位置上,同时一个"LOB定位符"(LOB locator)存储在原始的表中,该定位符是一个指向实际数据的指针。在PL/SQL中操作LOB数据对象使用ORACLE提供的包DBMS_LOB.LOB数据类型可分为以下四类:

      . BFILE
      . BLOB
      . CLOB
      . NCLOB

      操作符

      与其他程序设计语言相同,PL/SQL有一系列操作符。操作符分为下面几类:

      . 算术操作符

      . 关系操作符

      . 比较操作符

      . 逻辑操作符

      算术操作符如表4所示

    operatoroperation
    +
    -
    /
    *
    **乘方


      关系操作符主要用于条件判断语句或用于where子串中,关系操作符检查条件和结果是否为true或false,表5是PL/SQL中的关系操作符

    operatoroperation
    < 小于操作符
    <= 小于或等于操作符
    > 大于操作符
    >=大于或等于操作符
    = 等于操作符
    != 不等于操作符
    <> 不等于操作符
    := 赋值操作符


      表6 显示的是比较操作符

    operator operation
    IS NULL如果操作数为NULL返回TRUE
    LIKE比较字符串值
    BETWEEN验证值是否在范围之内
    IN验证操作数在设定的一系列值中


      表7.8显示的是逻辑操作符

    operatoroperation
    AND 两个条件都必须满足
    OR只要满足两个条件中的一个
    NOT取反


      执行部分

      执行部分包含了所有的语句和表达式,执行部分以关键字BEGIN开始,以关键字EXCEPTION结束,如果EXCEPTION不存在,那么将以关键字END结束。分号分隔每一条语句,使用赋值操作符:=或SELECT INTO或FETCH INTO给每个变量赋值,执行部分的错误将在异常处理部分解决,在执行部分中可以使用另一个PL/SQL程序块,这种程序块被称为嵌套块

      所有的SQL数据操作语句都可以用于执行部分,PL/SQL块不能再屏幕上显示SELECT语句的输出。SELECT语句必须包括一个INTO子串或者是游标的一部分,执行部分使用的变量和常量必须首先在声明部分声明,执行部分必须至少包括一条可执行语句,NULL是一条合法的可执行语句,事物控制语句COMMIT和ROLLBACK可以在执行部分使用,数据定义语言(Data Definition language)不能在执行部分中使用,DDL语句与EXECUTE IMMEDIATE一起使用或者是DBMS_SQL调用。

      执行一个PL/SQL块

      SQL*PLUS中匿名的PL/SQL块的执行是在PL/SQL块后输入/来执行,如下面的例子所示:

    declare
     v_comm_percent constant number:=10;
    begin
     update emp
     set comm=sal*v_comm_percent
     where deptno=10;
     end
    SQL> /
    PL/SQL procedure successfully completed.

    SQL>


      命名的程序与匿名程序的执行不同,执行命名的程序块必须使用execute关键字:

    create or replace procedure update_commission
     (v_dept in number,v_pervent in number default 10) is
    begin
     update emp
     set comm=sal*v_percent
     where deptno=v_dept;
    end

    SQL>/

    Procedure created

    SQL>execute update_commission(10,15);

    PL/SQL procedure successfully completed.

    SQL>



      如果在另一个命名程序块或匿名程序块中执行这个程序,那么就不需要EXECUTE关进字。

    declare
     v_dept number;
    begin
     select a.deptno
     into v_dept
     from emp a
     where job='PRESIDENT'
     update_commission(v_dept);
    end
    SQL>/
     PL/SQL procedure successfully completed
    SQL>

       控制结构

      控制结构控制PL/SQL程序流程的代码行,PL/SQL支持条件控制和循环控制结构。

      语法和用途

       IF..THEN

      语法:

    IF condition THEN
     Statements 1;
     Statements 2;
     ....
    END IF


      IF语句判断条件condition是否为TRUE,如果是,则执行THEN后面的语句,如果condition为false或NULL则跳过THEN到END IF之间的语句,执行END IF后面的语句。

      IF..THEN...ELSE

      语法:

    IF condition THEN
     Statements 1;
     Statements 2;
     ....
    ELSE
     Statements 1;
     Statements 2;
     ....
    END IF


      如果条件condition为TRUE,则执行THEN到ELSE之间的语句,否则执行ELSE到END IF之间的语句。

      IF 可以嵌套,可以在IF 或IF ..ELSE语句中使用IF或IF..ELSE语句。

    if (a>b) and (a>c) then
      g:=a;
    else
      g:=b;
      if c>g then
       g:=c;
      end if
    end if


      IF..THEN..ELSIF

      语法:

    IF condition1 THEN
     statement1;
    ELSIF condition2 THEN
     statement2;
    ELSIF condition3 THEN
     statement3;
    ELSE
     statement4;
    END IF;
     statement5;


      如果条件condition1为TRUE则执行statement1,然后执行statement5,否则判断condition2是否为TRUE,若为TRUE则执行statement2,然后执行statement5,对于condition3也是相同的,如果condition1,condition2,condition3都不成立,那么将执行statement4,然后执行statement5。

      循环控制

      循环控制的基本形式是LOOP语句,LOOP和END LOOP之间的语句将无限次的执行。LOOP语句的语法如下:

      LOOP
       statements;
      END LOOP

      LOOP和END LOOP之间的语句无限次的执行显然是不行的,那么在使用LOOP语句时必须使用EXIT语句,强制循环结束,例如:

    X:=100;
    LOOP
     X:=X+10;
     IF X>1000 THEN
      EXIT;
     END IF
    END LOOP;
    Y:=X;


      此时Y的值是1010.

      EXIT WHEN语句将结束循环,如果条件为TRUE,则结束循环。

    X:=100;
    LOOP
    X:=X+10;
    EXIT WHEN X>1000;
    X:=X+10;
    END LOOP;
    Y:=X;


      WHILE..LOOP

      WHILE..LOOP有一个条件与循环相联系,如果条件为TRUE,则执行循环体内的语句,如果结果为FALSE,则结束循环。

    X:=100;
    WHILE X<=1000 LOOP
     X:=X+10;
    END LOOP;
    Y=X;


      FOR...LOOP

      语法:

    FOR counter IN [REVERSE] start_range....end_range LOOP
    statements;
    END LOOP;


      LOOP和WHILE循环的循环次数都是不确定的,FOR循环的循环次数是固定的,counter是一个隐式声明的变量,他的初始值是start_range,第二个值是start_range+1,直到end_range,如果start_range等于end _range,那么循环将执行一次。如果使用了REVERSE关键字,那么范围将是一个降序。

    X:=100;
    FOR v_counter in 1..10 loop
    x:=x+10;

    end loop
    y:=x;


      如果要退出for循环可以使用EXIT语句。

      标签

      用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。标签的形式是<

     

    < >
    [DECLARE]
    ... ... ...
    BEGIN
    ........
    [EXCEPTION]
    .......
    END label_name


      标记循环

    < >
    LOOP
    .........
    < >
    loop
    ..........
    < >
    loop
    ....

    EXIT outer_loop WHEN v_condition=0;
    end loop innermost_loop;
    ..........
    END LOOP inner_loop;
    END LOOP outer_loop;


      GOTO语句

      语法:

      GOTO LABEL;

      执行GOTO语句时,控制会立即转到由标签标记的语句。PL/SQL中对GOTO语句有一些限制,对于块、循环、IF语句而言,从外层跳转到内层是非法的。

    X :=100;
    FOR V_COUNTER IN 1..10 LOOP
     IF V_COUNTER =4 THEN
      GOTO end_of_loop
     END IF
     X:=X+10;
     < >
     NULL
    END LOOP

    Y:=X;


      注意:NULL是一个合法的可执行语句。

      嵌套

      程序块的内部可以有另一个程序块这种情况称为嵌套。嵌套要注意的是变量,定义在最外部程序块中的变量可以在所有子块中使用,如果在子块中定义了与外部程序块变量相同的变量名,在执行子块时将使用子块中定义的变量。子块中定义的变量不能被父块引用。同样GOTO语句不能由父块跳转道子块中,反之则是合法的。

    《OUTER BLOCK》
    DECLARE
     A_NUMBER INTEGER;
     B_NUMBER INTEGER;
    BEGIN
     --A_NUMBER and B_NUMBER are available here
     < >
     DECLARE
     C_NUMBER INTEGER
     B_NUMBER NUMBER(20)
    BEGIN
     C_NUMBER:=A_NUMBER;
     C_NUMBER=OUTER_BLOCK.B_NUMBER;
    END SUB_BLOCK;
    END OUT_BLOCK;


      小结

      我们在这篇文章中介绍了PL/SQL的基础语法以及如何使用PL/SQL语言设计和运行PL/SQL程序块,并将PL/SQL程序整合到Oracle服务器中,虽然PL/SQL程序作为功能块嵌入Oracle数据库中,但PL/SQL与ORACLE数据库的紧密结合使得越来越多的Oracle数据库管理员和开发人员开始使用PL/SQL。


    我的正题
    以上内容部分是转载的,主要是介绍PL/SQL的基础语法以及如何使用PL/SQL语言设计和运行PL/SQL程序块,下面我谈一下如何在.net中一次执行多条SQL语句,在通常情况下除非用存储过程,否则一次只能执行一条SQL语句,假如有如下业务:删除一个用户的同时(假设UserID=5),还要删除用户在论坛的所有发帖及回帖(保证引用完整性嘛),假定用户表为Users(含有表示用户的主键UserID),发帖表为Articles(有UserID字段),回帖表为Replys(有UserID字段),通常情况分三步:(1),删除Replys表中所有UserID=5的回帖;(2)删除Articles表中所有UserID=5的帖子;(3)删除Users表中UserID=5的用户。
    在上面的每一步,先建立一个OracleConnection,然后再建立一个OracleCommand,再执行ExecuteNonQuery()方法,接着OracleCommand调用Dispose()方法,最后OracleConnection调用Close()方法。这个过程在上面的过程中执行3此,大家知道对数据库的连接操作是很费时间的,有没有比较好的办法呢?答案是有的。
    那就是利用PS/SQL。

    begin
    delete   from  Replys  where  UserId = 5 ; -- 删除回帖
    delete   from  Articles  where  UserId = 5 ; -- 删除发帖
    delete   from  Users  where  UserId = 5 ; -- 删除用户
    end ;
    那么整个过程可以这么写:
    private   void  deleteUser( int  userId)
            {
                
    string  deleteSql  =   " begin delete from Replys where UserId={0};delete from Articles where UserId={1};delete from Users where UserId={2};end; " ;
                deleteSql 
    =  String.Format(deleteSql, userId, userId, userId);
                OracleConnection connection 
    =   new  OracleConnection(connectionString);
                connection.Open();
                OracleCommand cmd 
    =   new  OracleCommand(deleteSql, connection);
                cmd.Dispose();
                connection.Close();
            }
    这样就能在一个Connection中一次执行完所有操作了,当然,这个代码中没有考虑事务处理,实际使用中大家自行考虑酌情添加。
    展开全文
  • Oracle存储过程及参数理解

    千次阅读 2015-01-07 14:38:45
     过程是一执行某个特定操作的子程序。使用CREATE OR REPLACE创建或者替换保存在数据库中的一子程序。 示例1:声明存储过程,该过程返回dept表行数 DECLARE  PROCEDURE getDeptCount  AS  deptCount INT; ...

    一、过程 (存储过程)
        过程是一个能执行某个特定操作的子程序。使用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);
                dbms_output.put_line(v_nanme);
         END ;
       
       
    SELECT emp.sal FROM emp WHERE emp.empno = 7788 ;

     

     

    附oracle 存储过程详细介绍(创建,删除存储过程,参数传递等):

     

    这篇文章主要介绍了oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包等相关资料,需要的朋友可以参考下

    oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包

    认识存储过程和函数

    存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比,存储过程有很多优点,具体归纳如下:
    * 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的,因为代码不保存在本地,用户可以在任何客户机上登录到数据库,并调用或修改代码。
    * 存储过程和函数可由数据库提供安全保证,要想使用存储过程和函数,需要有存储过程和函数的所有者的授权,只有被授权的用户或创建者本身才能执行存储过程或调用函数。
    * 存储过程和函数的信息是写入数据字典的,所以存储过程可以看作是一个公用模块,用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能,可以设计成为存储过程,比如:显示一张工资统计表,可以设计成为存储过程;一个经常调用的计算,可以设计成为存储函数;根据雇员编号返回雇员的姓名,可以设计成存储函数。
    * 像其他高级语言的过程和函数一样,可以传递参数给存储过程或函数,参数的传递也有多种方式。存储过程可以有返回值,也可以没有返回值,存储过程的返回值必须通过参数带回;函数有一定的数据类型,像其他的标准函数一样,我们可以通过对函数名的调用返回函数值。
       存储过程和函数需要进行编译,以排除语法错误,只有编译通过才能调用。

    创建和删除存储过程

    创建存储过程,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下:
    CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
    {AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    [EXCEPTION
    错误处理部分]
    END [过程名];

    其中:
    可选关键字OR REPLACE 表示如果存储过程已经存在,则用新的存储过程覆盖,通常用于存储过程的重建。
    参数部分用于定义多个参数(如果没有参数,就可以省略)。参数有三种形式:IN、OUT和IN OUT。如果没有指明参数的形式,则默认为IN。
    关键字AS也可以写成IS,后跟过程的说明部分,可以在此定义过程的局部变量。
    编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行,编写好的存储过程必须要在SQL*Plus环境下进行编译,生成编译代码,原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。
    一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下:

    DROP PROCEDURE 存储过程名;

    如果要重新编译一个存储过程,则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下:

    ALTER PROCEDURE 存储过程名 COMPILE;

    执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下:
    方法1:
    EXECUTE 模式名.存储过程名[(参数...)];
    方法2:
    BEGIN
    模式名.存储过程名[(参数...)];
    END;
    传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值,则调用时可以省略参数)。参数可以是变量、常量或表达式,用法参见下一节。
    如果是调用本账户下的存储过程,则模式名可以省略。要调用其他账户编写的存储过程,则模式名必须要添加。
    以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。

    【训练1】  创建一个显示雇员总人数的存储过程。

    步骤1:登录SCOTT账户(或学生个人账户)。
    步骤2:在SQL*Plus输入区中,输入以下存储过程:

    复制代码 代码如下:

    CREATE OR REPLACE PROCEDURE EMP_COUNT
    AS
    V_TOTAL NUMBER(10);
    BEGIN
     SELECT COUNT(*) INTO V_TOTAL FROM EMP;
     DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_TOTAL);
    END;

    步骤3:按“执行”按钮进行编译。
    如果存在错误,就会显示:
    警告: 创建的过程带有编译错误。
    如果存在错误,对脚本进行修改,直到没有错误产生。
    如果编译结果正确,将显示:

    复制代码 代码如下:

    过程已创建。

    步骤4:调用存储过程,在输入区中输入以下语句并执行:

    复制代码 代码如下:

    EXECUTE EMP_COUNT;

    显示结果为:

    复制代码 代码如下:

    雇员总人数为:14
    PL/SQL 过程已成功完成。

    说明:在该训练中,V_TOTAL变量是存储过程定义的局部变量,用于接收查询到的雇员总人数。
    注意:在SQL*Plus中输入存储过程,按“执行”按钮是进行编译,不是执行存储过程。
      如果在存储过程中引用了其他用户的对象,比如表,则必须有其他用户授予的对象访问权限。一个存储过程一旦编译成功,就可以由其他用户或程序来引用。但存储过程或函数的所有者必须授予其他用户执行该过程的权限。
    存储过程没有参数,在调用时,直接写过程名即可。

    【训练2】  在PL/SQL程序中调用存储过程。

    步骤1:登录SCOTT账户。
    步骤2:授权STUDENT账户使用该存储过程,即在SQL*Plus输入区中,输入以下的命令:

    复制代码 代码如下:

    GRANT EXECUTE ON EMP_COUNT TO STUDENT

    授权成功。

    步骤3:登录STUDENT账户,在SQL*Plus输入区中输入以下程序:

    复制代码 代码如下:

    SET SERVEROUTPUT ON
      BEGIN
      SCOTT.EMP_COUNT;
      END;

    步骤4:执行以上程序,结果为:

    复制代码 代码如下:

    雇员总人数为:14
      PL/SQL 过程已成功完成。 

    说明:在本例中,存储过程是由SCOTT账户创建的,STUDEN账户获得SCOTT账户的授权后,才能调用该存储过程。
      注意:在程序中调用存储过程,使用了第二种语法。

    【训练3】  编写显示雇员信息的存储过程EMP_LIST,并引用EMP_COUNT存储过程。

    步骤1:在SQL*Plus输入区中输入并编译以下存储过程:

    复制代码 代码如下:

    CREATE OR REPLACE PROCEDURE EMP_LIST
      AS
        CURSOR emp_cursor IS
        SELECT empno,ename FROM emp;
      BEGIN
    FOR Emp_record IN emp_cursor LOOP  
      DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
      END LOOP;
      EMP_COUNT;
      END;

    执行结果:

    过程已创建。

    步骤2:调用存储过程,在输入区中输入以下语句并执行:

    复制代码 代码如下:

    EXECUTE EMP_LIST

    显示结果为:

    复制代码 代码如下:

    7369SMITH
    7499ALLEN
    7521WARD
    7566JONES
          执行结果:
      雇员总人数为:14
      PL/SQL 过程已成功完成。

    说明:以上的EMP_LIST存储过程中定义并使用了游标,用来循环显示所有雇员的信息。然后调用已经成功编译的存储过程EMP_COUNT,用来附加显示雇员总人数。通过EXECUTE命令来执行EMP_LIST存储过程。

    【练习1】编写显示部门信息的存储过程DEPT_LIST,要求统计出部门个数。

    参数传递

    参数的作用是向存储过程传递数据,或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。
    参数的类型有三种,如下所示。

    复制代码 代码如下:

    IN 定义一个输入参数变量,用于传递参数给存储过程
    OUT 定义一个输出参数变量,用于从存储过程获取数据
    IN OUT 定义一个输入、输出参数变量,兼有以上两者的功能

    参数的定义形式和作用如下:
    参数名 IN 数据类型 DEFAULT 值;
    定义一个输入参数变量,用于传递参数给存储过程。在调用存储过程时,主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项,用来设定参数的默认值。如果在调用存储过程时不指明参数,则参数变量取默认值。在存储过程中,输入变量接收主程序传递的值,但不能对其进行赋值。
    参数名 OUT 数据类型;
    定义一个输出参数变量,用于从存储过程获取数据,即变量从存储过程中返回值给主程序。
    在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。在存储过程中,参数变量只能被赋值而不能将其用于赋值,在存储过程中必须给输出变量至少赋值一次。
    参数名 IN OUT 数据类型 DEFAULT 值;
    定义一个输入、输出参数变量,兼有以上两者的功能。在调用存储过程时,主程序的实际参数只能是一个变量,而不能是常量或表达式。DEFAULT 关键字为可选项,用来设定参数的默认值。在存储过程中,变量接收主程序传递的值,同时可以参加赋值运算,也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。
    如果省略IN、OUT或IN OUT,则默认模式是IN。
    【训练1】  编写给雇员增加工资的存储过程CHANGE_SALARY,通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。
    步骤1:登录SCOTT账户。
      步骤2:在SQL*Plus输入区中输入以下存储过程并执行:

    复制代码 代码如下:

    CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)
      AS
       V_ENAME VARCHAR2(10);
    V_SAL NUMBER(5);
      BEGIN
       SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
       UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
       DBMS_OUTPUT.PUT_LINE('雇员'||V_ENAME||'的工资被改为'||TO_CHAR(V_SAL+P_RAISE));
    COMMIT;
      EXCEPTION
       WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('发生错误,修改失败!');
       ROLLBACK;
      END;

    执行结果为:

    过程已创建。

    步骤3:调用存储过程,在输入区中输入以下语句并执行:

    复制代码 代码如下:

    EXECUTE CHANGE_SALARY(7788,80)

    显示结果为:

    雇员SCOTT的工资被改为3080  

    说明:从执行结果可以看到,雇员SCOTT的工资已由原来的3000改为3080。
    参数的值由调用者传递,传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致,可以采用以下调用方法。如上例,执行语句可以改为:
     EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788);
      可以看出传递参数的顺序发生了变化,并且明确指出了参数名和要传递的值,=>运算符左侧是参数名,右侧是参数表达式,这种赋值方法的意义较清楚。
    【练习1】创建插入雇员的存储过程INSERT_EMP,并将雇员编号等作为参数。
    在设计存储过程的时候,也可以为参数设定默认值,这样调用者就可以不传递或少传递参数了。
    【训练2】  调用存储过程CHANGE_SALARY,不传递参数,使用默认参数值。
    在SQL*Plus输入区中输入以下命令并执行:

    复制代码 代码如下:

    EXECUTE CHANGE_SALARY 

    显示结果为:

    雇员SCOTT的工资被改为3090  

    说明:在存储过程的调用中没有传递参数,而是采用了默认值7788和10,即默认雇员号为7788,增加的工资为10。
    【训练3】  使用OUT类型的参数返回存储过程的结果。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入并编译以下存储过程:

    复制代码 代码如下:

    CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)
      AS
      BEGIN
      SELECT COUNT(*) INTO P_TOTAL FROM EMP;
      END;

    执行结果为:

    1.过程已创建。 

    步骤3:输入以下程序并执行:

    复制代码 代码如下:

    DECLARE
      V_EMPCOUNT NUMBER;
      BEGIN
      EMP_COUNT(V_EMPCOUNT);
      DBMS_OUTPUT.PUT_LINE('雇员总人数为:'||V_EMPCOUNT);
      END;

    显示结果为:

    复制代码 代码如下:

    雇员总人数为:14
      PL/SQL 过程已成功完成。

     说明:在存储过程中定义了OUT类型的参数P_TOTAL,在主程序调用该存储过程时,传递了参数V_EMPCOUNT。在存储过程中的SELECT...INTO...语句中对P_TOTAL进行赋值,赋值结果由V_EMPCOUNT变量带回给主程序并显示。
    以上程序要覆盖同名的EMP_COUNT存储过程,如果不使用OR REPLACE选项,就会出现以下错误:

    复制代码 代码如下:

    ERROR 位于第 1 行:
      ORA-00955: 名称已由现有对象使用。

    【练习2】创建存储过程,使用OUT类型参数获得雇员经理名。
    【训练4】  使用IN OUT类型的参数,给电话号码增加区码。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入并编译以下存储过程:

    复制代码 代码如下:

    CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)
      AS
      BEGIN
       P_HPONE_NUM:='0755-'||P_HPONE_NUM;
      END;

    执行结果为:

    过程已创建。 

    步骤3:输入以下程序并执行:

    复制代码 代码如下:

    SET SERVEROUTPUT ON
    DECLARE
    V_PHONE_NUM VARCHAR2(15);
    BEGIN
    V_PHONE_NUM:='26731092';
    ADD_REGION(V_PHONE_NUM);
    DBMS_OUTPUT.PUT_LINE('新的电话号码:'||V_PHONE_NUM);
    END;

    显示结果为:

    复制代码 代码如下:

    新的电话号码:0755-26731092
      PL/SQL 过程已成功完成。

    说明:变量V_HPONE_NUM既用来向存储过程传递旧电话号码,也用来向主程序返回新号码。新的号码在原来基础上增加了区号0755和-。

    创建和删除存储函数

      创建函数,需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建存储函数的语法和创建存储过程的类似,即
    CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)]
    RETURN 数据类型
    {AS|IS}
    [说明部分]
    BEGIN
    可执行部分
    RETURN (表达式)
    [EXCEPTION
        错误处理部分]
    END [函数名];
    其中,参数是可选的,但只能是IN类型(IN关键字可以省略)。
    在定义部分的RETURN 数据类型,用来表示函数的数据类型,也就是返回值的类型,此部分不可省略。
    在可执行部分的RETURN(表达式),用来生成函数的返回值,其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句,但只有一个RETURN语句会被执行,一旦执行了RETURN语句,则函数结束并返回调用环境。
    一个存储函数在不需要时可以删除,但删除的人应是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人。其语法如下:
    DROP FUNCTION 函数名;
    重新编译一个存储函数时,编译的人应是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。重新编译一个存储函数的语法如下:
    ALTER PROCEDURE 函数名 COMPILE;
    函数的调用者应是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人,或是被函数的拥有者授予了函数执行权限的账户。函数的引用和存储过程不同,函数要出现在程序体中,可以参加表达式的运算或单独出现在表达式中,其形式如下:
    变量名:=函数名(...)

    【训练1】  创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。
    步骤1:登录SCOTT账户。
    步骤2:在SQL*Plus输入区中输入以下存储函数并编译:

    复制代码 代码如下:

    CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)
      RETURN VARCHAR2
      AS
       V_ENAME VARCHAR2(10);
      BEGIN
       ELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;
    RETURN(V_ENAME);
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('没有该编号雇员!');
      RETURN (NULL);
     WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('有重复雇员编号!');
      RETURN (NULL);
     WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其他错误!');
      RETURN (NULL);
    END;

    步骤3:调用该存储函数,输入并执行以下程序:

    复制代码 代码如下:

    BEGIN
        DBMS_OUTPUT.PUT_LINE('雇员7369的名称是:'|| GET_EMP_NAME(7369));
        DBMS_OUTPUT.PUT_LINE('雇员7839的名称是:'|| GET_EMP_NAME(7839));
      END;

    显示结果为:

    复制代码 代码如下:

    雇员7369的名称是:SMITH
      雇员7839的名称是:KING
      PL/SQL 过程已成功完成。

    说明:函数的调用直接出现在程序的DBMS_OUTPUT.PUT_LINE语句中,作为字符串表达式的一部分。如果输入了错误的雇员编号,就会在函数的错误处理部分输出错误信息。试修改雇员编号,重新运行调用部分。
    【练习1】创建一个通过部门编号返回部门名称的存储函数GET_DEPT_NAME。
       【练习2】将函数的执行权限授予STUDENT账户,然后登录STUDENT账户调用。
    存储过程和函数的查看
    可以通过对数据字典的访问来查询存储过程或函数的有关信息,如果要查询当前用户的存储过程或函数的源代码,可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下:

    复制代码 代码如下:

    DESCRIBE USER_SOURCE

    结果为:

    复制代码 代码如下:

    名称                                      是否为空? 类型
       ------------------------------------------------------------- ------------- -----------------------
     NAME                                             VARCHAR2(30)
     TYPE                                              VARCHAR2(12)
     LINE                                              NUMBER
     TEXT                                              VARCHAR2(4000)

    说明:里面按行存放着过程或函数的脚本,NAME是过程或函数名,TYPE 代表类型(PROCEDURE或FUNCTION),LINE是行号,TEXT 为脚本。
    【训练1】  查询过程EMP_COUNT的脚本。
    在SQL*Plus中输入并执行如下查询:

    复制代码 代码如下:

    select TEXT  from user_source WHERE NAME='EMP_COUNT';

    结果为:

    复制代码 代码如下:

    TEXT
    --------------------------------------------------------------------------------
    PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)
    AS
    BEGIN
     SELECT COUNT(*) INTO P_TOTAL FROM EMP;
    END;

    【训练2】  查询过程GET_EMP_NAME的参数。
    在SQL*Plus中输入并执行如下查询:

    复制代码 代码如下:

    DESCRIBE GET_EMP_NAME

    结果为:

    复制代码 代码如下:

    FUNCTION GET_EMP_NAME RETURNS VARCHAR2
      参数名称            类型          输入/输出默认值?
      ----------------------------------------- ----------------------------------- ----------------- -------------
       P_EMPNO             NUMBER(4) IN     DEFAULT

    【训练3】  在发生编译错误时,显示错误。

    复制代码 代码如下:

    SHOW ERRORS

    以下是一段编译错误显示:

    复制代码 代码如下:

    LINE/COL ERROR
      ------------- -----------------------------------------------------------------
      4/2       PL/SQL: SQL Statement ignored
      4/36      PLS-00201: 必须说明标识符 'EMPP'

    说明:查询一个存储过程或函数是否是有效状态(即编译成功),可以使用数据字典USER_OBJECTS的STATUS列。
    【训练4】  查询EMP_LIST存储过程是否可用:

    复制代码 代码如下:

    SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='EMP_LIST';

    结果为:

    复制代码 代码如下:

    STATUS
      ------------
      VALID

    说明:VALID表示该存储过程有效(即通过编译),INVALID表示存储过程无效或需要重新编译。当Oracle调用一个无效的存储过程或函数时,首先试图对其进行编译,如果编译成功则将状态置成VALID并执行,否则给出错误信息。
    当一个存储过程编译成功,状态变为VALID,会不会在某些情况下变成INVALID。结论是完全可能的。比如一个存储过程中包含对表的查询,如果表被修改或删除,存储过程就会变成无效INVALID。所以要注意存储过程和函数对其他对象的依赖关系。
    如果要检查存储过程或函数的依赖性,可以通过查询数据字典USER_DENPENDENCIES来确定,该表结构如下:

    复制代码 代码如下:

    DESCRIBE USER_DEPENDENCIES;

    结果:

    复制代码 代码如下:

    名称                     是否为空? 类型
       -------------------------------------------------------------- ------------- ----------------------------
       NAME            NOT NULL VARCHAR2(30)
       TYPE                       VARCHAR2(12)
      REFERENCED_OWNER                              VARCHAR2(30)
     REFERENCED_NAME                               VARCHAR2(64)
     REFERENCED_TYPE                                VARCHAR2(12)
    REFERENCED_LINK_NAME                         VARCHAR2(128)
       SCHEMAID                                        NUMBER
       DEPENDENCY_TYPE                                VARCHAR2(4)

    说明:NAME为实体名,TYPE为实体类型,REFERENCED_OWNER为涉及到的实体拥有者账户,REFERENCED_NAME为涉及到的实体名,REFERENCED_TYPE 为涉及到的实体类型。
    【训练5】  查询EMP_LIST存储过程的依赖性。

    复制代码 代码如下:

    SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='EMP_LIST';

    执行结果:

    复制代码 代码如下:

    REFERENCED_NAME                                         REFERENCED_TYPE
      ------------------------------------------------------------------------------------------ ----------------------------
    STANDARD                                               PACKAGE
      SYS_STUB_FOR_PURITY_ANALYSIS                           PACKAGE
      DBMS_OUTPUT                                              PACKAGE
      DBMS_OUTPUT                                             SYNONYM
    DBMS_OUTPUT                      NON-EXISTENT
      EMP                                                        TABLE
      EMP_COUNT                                                 PROCEDURE

    说明:可以看出存储过程EMP_LIST依赖一些系统包、EMP表和EMP_COUNT存储过程。如果删除了EMP表或EMP_COUNT存储过程,EMP_LIST将变成无效。
    还有一种情况需要我们注意:如果一个用户A被授予执行属于用户B的一个存储过程的权限,在用户B的存储过程中,访问到用户C的表,用户B被授予访问用户C的表的权限,但用户A没有被授予访问用户C表的权限,那么用户A调用用户B的存储过程是失败的还是成功的呢?答案是成功的。如果读者有兴趣,不妨进行一下实际测试。


    包的概念和组成

    包是用来存储相关程序结构的对象,它存储于数据字典中。包由两个分离的部分组成:包头(PACKAGE)和包体(PACKAGE BODY)。包头是包的说明部分,是对外的操作接口,对应用是可见的;包体是包的代码和实现部分,对应用来说是不可见的黑盒。
    包中可以包含的程序结构如下所示。

    复制代码 代码如下:

    过程(PROCUDURE) 带参数的命名的程序模块
    函数(FUNCTION) 带参数、具有返回值的命名的程序模块
    变量(VARIABLE) 存储变化的量的存储单元
    常量(CONSTANT) 存储不变的量的存储单元
    游标(CURSOR) 用户定义的数据操作缓存区,在可执行部分使用
    类型(TYPE) 用户定义的新的结构类型
    异常(EXCEPTION) 在标准包中定义或由用户自定义,用于处理程序错误

    说明部分可以出现在包的三个不同的部分:出现在包头中的称为公有元素,出现在包体中的称为私有元素,出现在包体的过程(或函数)中的称为局部变量。它们的性质有所不同,如下所示。

    复制代码 代码如下:

    公有元素(PUBLIC) 在包头中说明,在包体中具体定义 在包外可见并可以访问,对整个应用的全过程有效
    私有元素(PRIVATE) 在包体的说明部分说明 只能被包内部的其他部分访问
    局部变量(LOCAL) 在过程或函数的说明部分说明 只能在定义变量的过程或函数中使用

    在包体中出现的过程或函数,如果需要对外公用,就必须在包头中说明,包头中的说明应该和包体中的说明一致。
    包有以下优点:
    * 包可以方便地将存储过程和函数组织到一起,每个包又是相互独立的。在不同的包中,过程、函数都可以重名,这解决了在同一个用户环境中命名的冲突问题。
    * 包增强了对存储过程和函数的安全管理,对整个包的访问权只需一次授予。
      * 在同一个会话中,公用变量的值将被保留,直到会话结束。
    * 区分了公有过程和私有过程,包体的私有过程增加了过程和函数的保密性。
    * 包在被首次调用时,就作为一个整体被全部调入内存,减少了多次访问过程或函数的I/O次数。
    创建包和包体
    包由包头和包体两部分组成,包的创建应该先创建包头部分,然后创建包体部分。创建、删除和编译包的权限同创建、删除和编译存储过程的权限相同。
    创建包头的简要语句如下:
    CREATE [OR REPLACE] PACKAGE 包名
    {IS|AS}
    公有变量定义
    公有类型定义
    公有游标定义
    公有异常定义
    函数说明
    过程说明
    END;
    创建包体的简要语法如下:
    CREATE [OR REPLACE] PACKAGE BODY 包名
    {IS|AS}
    私有变量定义
    私有类型定义
    私有游标定义
    私有异常定义
    函数定义
    过程定义
    END;
    包的其他操作命令包括:
    删除包头:
    DROP PACKAGE 包头名
    删除包体:
    DROP PACKAGE BODY 包体名
    重新编译包头:
    ALTER PACKAGE 包名 COMPILE PACKAGE
    重新编译包体:
    ALTER PACKAGE 包名 COMPILE PACKAGE BODY
    在包头中说明的对象可以在包外调用,调用的方法和调用单独的过程或函数的方法基本相同,惟一的区别就是要在调用的过程或函数名前加上包的名字(中间用“.”分隔)。但要注意,不同的会话将单独对包的公用变量进行初始化,所以不同的会话对包的调用属于不同的应用。
    系统包
    Oracle预定义了很多标准的系统包,这些包可以在应用中直接使用,比如在训练中我们使用的DBMS_OUTPUT包,就是系统包。PUT_LINE是该包的一个函数。常用系统包下所示。

    复制代码 代码如下:

    DBMS_OUTPUT 在SQL*Plus环境下输出信息
    DBMS_DDL 编译过程函数和包
    DBMS_SESSION 改变用户的会话,初始化包等
    DBMS_TRANSACTION 控制数据库事务
    DBMS_MAIL 连接Oracle*Mail
    DBMS_LOCK 进行复杂的锁机制管理
    DBMS_ALERT 识别数据库事件告警
    DBMS_PIPE 通过管道在会话间传递信息
    DBMS_JOB 管理Oracle的作业
    DBMS_LOB 操纵大对象
    DBMS_SQL 执行动态SQL语句

    包的应用

    在SQL*Plus环境下,包和包体可以分别编译,也可以一起编译。如果分别编译,则要先编译包头,后编译包体。如果在一起编译,则包头写在前,包体在后,中间用“/”分隔。
    可以将已经存在的存储过程或函数添加到包中,方法是去掉过程或函数创建语句的CREATE OR REPLACE部分,将存储过程或函数复制到包体中 ,然后重新编译即可。
       如果需要将私有过程或函数变成共有过程或函数的话,将过程或函数说明部分复制到包头说明部分,然后重新编译就可以了。
    【训练1】  创建管理雇员信息的包EMPLOYE,它具有从EMP表获得雇员信息,修改雇员名称,修改雇员工资和写回EMP表的功能。
    步骤1:登录SCOTT账户,输入以下代码并编译:

    复制代码 代码如下:

    CREATE OR REPLACE PACKAGE EMPLOYE --包头部分
      IS
     PROCEDURE SHOW_DETAIL;
     PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);
     PROCEDURE SAVE_EMPLOYE;
     PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);
    PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER);
      END EMPLOYE;
      /
      CREATE OR REPLACE PACKAGE BODY EMPLOYE --包体部分
      IS
     EMPLOYE EMP%ROWTYPE;
      -------------- 显示雇员信息 ---------------
      PROCEDURE SHOW_DETAIL
       AS
       BEGIN
    DBMS_OUTPUT.PUT_LINE(‘----- 雇员信息 -----'); 
        DBMS_OUTPUT.PUT_LINE('雇员编号:'||EMPLOYE.EMPNO);
        DBMS_OUTPUT.PUT_LINE('雇员名称:'||EMPLOYE.ENAME);
        DBMS_OUTPUT.PUT_LINE('雇员职务:'||EMPLOYE.JOB);
        DBMS_OUTPUT.PUT_LINE('雇员工资:'||EMPLOYE.SAL);
        DBMS_OUTPUT.PUT_LINE('部门编号:'||EMPLOYE.DEPTNO);
       END SHOW_DETAIL;
    ----------------- 从EMP表取得一个雇员 --------------------
       PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)
       AS
       BEGIN
        SELECT * INTO EMPLOYE FROM EMP WHERE  EMPNO=P_EMPNO;
        DBMS_OUTPUT.PUT_LINE('获取雇员'||EMPLOYE.ENAME||'信息成功');
       EXCEPTION
        WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('获取雇员信息发生错误!');
       END GET_EMPLOYE;
    ---------------------- 保存雇员到EMP表 --------------------------
       PROCEDURE SAVE_EMPLOYE
       AS
       BEGIN
        UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=
     EMPLOYE.EMPNO;
       DBMS_OUTPUT.PUT_LINE('雇员信息保存完成!');
       END SAVE_EMPLOYE;
    ---------------------------- 修改雇员名称 ------------------------------
       PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2)
       AS
       BEGIN
        EMPLOYE.ENAME:=P_NEWNAME;
        DBMS_OUTPUT.PUT_LINE('修改名称完成!');
       END CHANGE_NAME;
    ---------------------------- 修改雇员工资 --------------------------
       PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)
       AS
       BEGIN
        EMPLOYE.SAL:=P_NEWSAL;
        DBMS_OUTPUT.PUT_LINE('修改工资完成!');
       END CHANGE_SAL;
      END EMPLOYE;


    步骤2:获取雇员7788的信息:

    复制代码 代码如下:

    SET SERVEROUTPUT ON
      EXECUTE EMPLOYE.GET_EMPLOYE(7788);

    结果为:

    复制代码 代码如下:

    获取雇员SCOTT信息成功
      PL/SQL 过程已成功完成。

    步骤3:显示雇员信息:

    复制代码 代码如下:

    EXECUTE EMPLOYE.SHOW_DETAIL;

    结果为:

    复制代码 代码如下:

    ------------------ 雇员信息 ------------------
      雇员编号:7788
      雇员名称:SCOTT
      雇员职务:ANALYST
      雇员工资:3000
      部门编号:20
      PL/SQL 过程已成功完成。

    步骤4:修改雇员工资:

    复制代码 代码如下:

    EXECUTE EMPLOYE.CHANGE_SAL(3800);

    结果为:

    复制代码 代码如下:

    修改工资完成!
      PL/SQL 过程已成功完成。

    步骤5:将修改的雇员信息存入EMP表

    复制代码 代码如下:

    EXECUTE EMPLOYE.SAVE_EMPLOYE;

    结果为:

    复制代码 代码如下:

    雇员信息保存完成!
      PL/SQL 过程已成功完成。

    说明:该包完成将EMP表中的某个雇员的信息取入内存记录变量,在记录变量中进行修改编辑,在确认显示信息正确后写回EMP表的功能。记录变量EMPLOYE用来存储取得的雇员信息,定义为私有变量,只能被包的内部模块访问。
      【练习1】为包增加修改雇员职务和部门编号的功能。

    阶段训练
    下面的训练通过定义和创建完整的包EMP_PK并综合运用本章的知识,完成对雇员表的插入、删除等功能,包中的主要元素解释如下所示。

    复制代码 代码如下:

    程序结构 类  型 说    明
    V_EMP_COUNT 公有变量 跟踪雇员的总人数变化,插入、删除雇员的同时修改该变量的值
    INIT 公有过程 对包进行初始化,初始化雇员人数和工资修改的上、下限
    LIST_EMP 公有过程 显示雇员列表
    INSERT_EMP 公有过程 通过编号插入新雇员
    DELETE_EMP 公有过程 通过编号删除雇员
    CHANGE_EMP_SAL 公有过程 通过编号修改雇员工资
    V_MESSAGE 私有变量 存放准备输出的信息
    C_MAX_SAL 私有变量 对工资修改的上限
    C_MIN_SAL 私有变量 对工资修改的下限
    SHOW_MESSAGE 私有过程 显示私有变量V_MESSAGE中的信息
    EXIST_EMP 私有函数 判断某个编号的雇员是否存在,该函数被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等过程调用

    【训练1】  完整的雇员包EMP_PK的创建和应用。
    步骤1:在SQL*Plus中登录SCOTT账户,输入以下包头和包体部分,按“执行”按钮编译:

    复制代码 代码如下:

    CREATE OR REPLACE PACKAGE EMP_PK
      --包头部分
      IS
       V_EMP_COUNT NUMBER(5);     
      --雇员人数
       PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER);  --初始化
       PROCEDURE LIST_EMP;      
      --显示雇员列表
    PROCEDURE INSERT_EMP(P_EMPNO   NUMBER,P_ENAMEVARCHAR2,P_JOB VARCHAR2,
       P_SAL NUMBER);       
      --插入雇员
      PROCEDURE DELETE_EMP(P_EMPNO NUMBER);   --删除雇员
       PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);
      --修改雇员工资
      END EMP_PK;
      /CREATE OR REPLACE PACKAGE BODY EMP_PK
       --包体部分
      IS
       V_MESSAGE VARCHAR2(50); --显示信息
    V_MAX_SAL NUMBER(7); --工资上限
       V_MIN_SAL NUMBER(7); --工资下限
       FUNCTION EXIST_EMP(P_EMPNO NUMBER)  RETURN  BOOLEAN; --判断雇员是否存在函数
       PROCEDURE SHOW_MESSAGE; --显示信息过程
      ------------------------------- 初始化过程 ----------------------------
       PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)
      IS
      BEGIN
          SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;
    V_MAX_SAL:=P_MAX;
        V_MIN_SAL:=P_MIN;
        V_MESSAGE:='初始化过程已经完成!';
        SHOW_MESSAGE;
       END INIT;
    ---------------------------- 显示雇员列表过程 ---------------------
      PROCEDURE LIST_EMP
       IS
       BEGIN
    DBMS_OUTPUT.PUT_LINE('姓名       职务      工资');
        FOR emp_rec IN (SELECT * FROM EMP)
        LOOP
      DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.ename,10,'')||RPAD(emp_rec.job,10,' ')||TO_CHAR(emp_rec.sal));
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('雇员总人数'||V_EMP_COUNT);
       END LIST_EMP;
    ----------------------------- 插入雇员过程 -----------------------------
       PROCEDUREINSERT_EMP(P_EMPNO  NUMBER,P_ENAMEVARCHAR2,P_JOB  VARCHAR2,P_SAL NUMBER)
       IS
       BEGIN
       IF NOT EXIST_EMP(P_EMPNO) THEN
        INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)   VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);
      COMMIT; 
         V_EMP_COUNT:=V_EMP_COUNT+1;
        V_MESSAGE:='雇员'||P_EMPNO||'已插入!';
        ELSE
    V_MESSAGE:='雇员'||P_EMPNO||'已存在,不能插入!';
       END IF;
       SHOW_MESSAGE;
      EXCEPTION
       WHEN OTHERS THEN
        V_MESSAGE:='雇员'||P_EMPNO||'插入失败!';
        SHOW_MESSAGE;
      END INSERT_EMP;
    --------------------------- 删除雇员过程 --------------------
       PROCEDURE DELETE_EMP(P_EMPNO NUMBER)
       IS
       BEGIN
        IF EXIST_EMP(P_EMPNO) THEN
         DELETE FROM EMP WHERE EMPNO=P_EMPNO;
         COMMIT;
         V_EMP_COUNT:=V_EMP_COUNT-1;
         V_MESSAGE:='雇员'||P_EMPNO||'已删除!';
        ELSE
    V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能删除!';
       END IF;
       SHOW_MESSAGE;
      EXCEPTION
       WHEN OTHERS THEN
        V_MESSAGE:='雇员'||P_EMPNO||'删除失败!';
        SHOW_MESSAGE;
      END DELETE_EMP;
    --------------------------------------- 修改雇员工资过程 ------------------------------------
       PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)
       IS
       BEGIN
        IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN
         V_MESSAGE:='工资超出修改范围!';
        ELSIF NOT EXIST_EMP(P_EMPNO) THEN
         V_MESSAGE:='雇员'||P_EMPNO||'不存在,不能修改工资!';
    ELSE
         UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;
         COMMIT;
         V_MESSAGE:='雇员'||P_EMPNO||'工资已经修改!';
        END IF;
        SHOW_MESSAGE;
       EXCEPTION
        WHEN OTHERS THEN
         V_MESSAGE:='雇员'||P_EMPNO||'工资修改失败!';
         SHOW_MESSAGE;
       END CHANGE_EMP_SAL;
    ---------------------------- 显示信息过程 ----------------------------
       PROCEDURE SHOW_MESSAGE
       IS
       BEGIN
        DBMS_OUTPUT.PUT_LINE('提示信息:'||V_MESSAGE);
       END SHOW_MESSAGE;
    ------------------------ 判断雇员是否存在函数 -------------------
       FUNCTION EXIST_EMP(P_EMPNO NUMBER)
       RETURN BOOLEAN
       IS
        V_NUM NUMBER; --局部变量
       BEGIN
        SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;
    IF V_NUM=1 THEN
         RETURN TRUE;
        ELSE
         RETURN FALSE;
        END IF;
       END EXIST_EMP;
      -----------------------------
      END EMP_PK;

    结果为:

    1.程序包已创建。  
    2.        程序包主体已创建。 

    步骤2:初始化包:

    复制代码 代码如下:

    SET SERVEROUTPUT ON
    EXECUTE EMP_PK.INIT(6000,600);

    显示为:

    提示信息:初始化过程已经完成! 

    步骤3:显示雇员列表:

    复制代码 代码如下:

    EXECUTE EMP_PK.LIST_EMP;

    显示为:

    复制代码 代码如下:

    姓名        职务       工资
      SMITH      CLERK      1560
      ALLEN      SALESMAN   1936
      WARD      SALESMAN   1830
      JONES      MANAGER    2975
      ...
      雇员总人数:14
      PL/SQL 过程已成功完成。

    步骤4:插入一个新记录:

    复制代码 代码如下:

    EXECUTE EMP_PK.INSERT_EMP(8001,'小王','CLERK',1000);

    显示结果为:

    复制代码 代码如下:

    提示信息:雇员8001已插入!
    PL/SQL 过程已成功完成。

    步骤5:通过全局变量V_EMP_COUNT查看雇员人数:

    复制代码 代码如下:

    BEGIN
    DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);
    END;

    显示结果为:

    复制代码 代码如下:

    15
    PL/SQL 过程已成功完成。

    步骤6:删除新插入记录:

    复制代码 代码如下:

    EXECUTE EMP_PK.DELETE_EMP(8001);

    显示结果为:

    复制代码 代码如下:

    提示信息:雇员8001已删除!
      PL/SQL 过程已成功完成。

    再次删除该雇员:

    复制代码 代码如下:

    EXECUTE EMP_PK.DELETE_EMP(8001);

    结果为:

    复制代码 代码如下:

    提示信息:雇员8001不存在,不能删除!

    步骤7:修改雇员工资:

    复制代码 代码如下:

    EXECUTE EMP_PK.CHANGE_EMP_SAL(7788,8000);

    显示结果为:

    复制代码 代码如下:

    提示信息:工资超出修改范围!
      PL/SQL 过程已成功完成。

    步骤8:授权其他用户调用包:
    如果是另外一个用户要使用该包,必须由包的所有者授权,下面授予STUDEN账户对该包的使用权:

    复制代码 代码如下:

    GRANT EXECUTE ON EMP_PK TO STUDENT;

    每一个新的会话要为包中的公用变量开辟新的存储空间,所以需要重新执行初始化过程。两个会话的进程互不影响。
    步骤9:其他用户调用包。
    启动另外一个SQL*Plus,登录STUDENT账户,执行以下过程:

    复制代码 代码如下:

    SET SERVEROUTPUT ON
      EXECUTE SCOTT.EMP_PK. EMP_PK.INIT(5000,700);

    结果为:

    复制代码 代码如下:

    提示信息:初始化过程已经完成!
      PL/SQL 过程已成功完成。

    说明:在初始化中设置雇员的总人数和修改工资的上、下限,初始化后V_EMP_COUNT为14人,插入雇员后V_EMP_COUNT为15人。V_EMP_COUNT为公有变量,所以可以在外部程序中使用DBMS_OUTPUT.PUT_LINE输出,引用时用EMP_PK.V_EMP_COUNT的形式,说明所属的包。而私有变量V_MAX_SAL和V_MIN_SAL不能被外部访问,只能通过内部过程来修改。同样,EXIST_EMP和SHOW_MESSAGE也是私有过程,也只能在过程体内被其他模块引用。
    注意:在最后一个步骤中,因为STUDENT模式调用了SCOTT模式的包,所以包名前要增加模式名SCOTT。不同的会话对包的调用属于不同的应用,所以需要重新进行初始化。
    练习
    1.如果存储过程的参数类型为OUT,那么调用时传递的参数应该为:
         A.常量 B.表达式                C.变量 D.都可以
    2.下列有关存储过程的特点说法错误的是:
         A.存储过程不能将值传回调用的主程序
         B.存储过程是一个命名的模块
         C.编译的存储过程存放在数据库中
         D.一个存储过程可以调用另一个存储过程
    3.下列有关函数的特点说法错误的是:
         A.函数必须定义返回类型
         B.函数参数的类型只能是IN
         C.在函数体内可以多次使用RETURN语句
         D.函数的调用应使用EXECUTE命令
    4.包中不能包含的元素为:
         A.存储过程 B.存储函数
         C.游标    D.表
    5.下列有关包的使用说法错误的是:
         A.在不同的包内模块可以重名
         B.包的私有过程不能被外部程序调用
         C.包体中的过程和函数必须在包头部分说明
         D.必须先创建包头,然后创建包体

     

     

    展开全文
  • 导读: C#+Oracle开发中执行存储过程问题 作者:dinya 关键字:C#, Oracle, C/S 内容摘要: 当前...但是在开发过程中需要通过前台程序调用数据库中的一些对象,本文将以实例的形式,对C#+Oracle数据库的开发中C#执
    导读: 
    
      C#+Oracle开发中执行存储过程问题
      作者: dinya
       关键字:C#, Oracle, C/S
      
       内容摘要
      当前开发过程中,微软的.NET以其易用和对网络的支持性好等而倍受开发人员的青睐,不少项目使用Oralce数据库作为后台数据库,但是在开发过程中需要通过前台程序调用数据库中的一些对象,本文将以一个实例的形式,对C#+Oracle数据库的开发中C#执行Oracle存储过程问题做一简要阐述。
      
       本文适宜读者范围
      .NET+Oracel开发人员,Oracle初级
      
       系统环境
       OS windows 2000 Professional (英文版)
       Oracle 8.1.7.1.0
       .NET .Net 2003
      
       正文:
      我们在使用C#+Oracle 数据库开发C/S结构的程序时。有相当大一部分的事务会考虑放在Oracle数据库中来处理,以减少网络数据流量、提高程序的性能。这样就要求我们将业务逻辑化,抽象化,使用Oracle的存储过程是一个不错的选择。下面我将使用一个实例来说明一下C#使用Oracle存储过程来执行业务操作的过程:
      
      需求描述:
      在前端界面上输入用户名、密码、用户姓名、用户电话号码及用户类型等基础资料后。调用Oracle数据库中的增加用户信息的存储过程来执行增加新用户信息的功能。要求用户名、密码及用户类型不能为空,用户名不能有重复,本例中使用OldDbConnection来连接Oracle数据库。
      
       1 、在数据库中建一用户表及用户ID 的序列:
       create sequenceseq_user_information
       increment by1
       start with1
       nomaxvalue
       nocycle
       cache10
      
       create tableuser_information
      (
      user_id number primary key, --用户序号
      user_login_name varchar2(30) not null,--登陆名
      user_password varchar2(50) not null, --用户密码
      user_name varchar2(20), --用户姓名
      user_telephone varchar2(20), --用户电话
      user_type number(5), --用户类型
      creation_date date not null, --创建日期
      last_update_date date not null --最后修改日期
      )
      
       2 、在Oracle 中建执行插入操作的存储过程:
       create or replace procedureinsert_user_information
      (
      p_user_login_name in varchar2,
      p_user_password in varchar2,
      p_user_name in varchar2,
      p_user_telephone in varchar2,
      p_user_type in number,
      p_out out number
      ) as
      v_count number
       begin
       ifp_user_login_name is null orp_user_password is null then
      p_out:=-1; --用户名和密码不能为空,
       return
       end if
       ifp_user_type is null then
      p_out:=-2; --用户类型不能为空
       return
       end if
       select count(*) intov_count fromuser_information a wherea.user_login_name=upper(p_user_login_name);
       ifv_count>0 then
      p_out:=-3; --该用户名已经存在
       return
       end if
       insert intouser_information values(seq_user_information. nextval,upper(p_user_login_name),p_user_password,p_user_name,p_user_telephone,p_user_type,
       sysdate, sysdate);
       commit
      p_out:=0; --操作成功
       return
       exception
       when others then
      p_out:=-4; --插入过程中出现异常
       return
       end
      
       3 、在.NET 项目建一个到数据库的联结:
      在项目中新增加一个类文件。名称:clsPublic,
      //添加引用:
      using System;
      using System.Data;
      using System.Data.OleDb;
      //连接字符串
      private string connectora="Provider=MSDAORA.1;Password=fran;User ID=fran;Data Source=demo;Persist Security Info=True";
      //连接Oracle数据库
      public OleDbConnection ConnectDB()
      {
      try
      {
      OleDbConnection conn=new OleDbConnection();
      conn.ConnectionString=connectora;
      conn.Open();
      return conn;
      }
      catch
      {
      return null;
      }
      }
      
       4、在类文件中添加如下内容,用来执行Oracle中的过程:
       public intInsert_User_Information( stringv_user_login_name, stringv_user_password, stringv_user_name, stringv_user_telephone, intv_user_type, stringproc_name)
      {
       inti;
      cmdOra. Parameters. Clear();
      cmdOra.CommandText=proc_name;
      cmdOra.CommandType=CommandType.StoredProcedure;
      cmdOra.Connection= newclsPublic().ConnectDB();
      cmdOra. Parameters. Add("p_user_login_name",OleDbType. VarChar);
      cmdOra. Parameters. Add("p_user_password",OleDbType. VarChar);
      cmdOra. Parameters. Add("p_user_name",OleDbType. VarChar);
      cmdOra. Parameters. Add("p_user_telephone",OleDbType. VarChar);
      cmdOra. Parameters. Add("p_user_type",OleDbType. Integer);
      cmdOra. Parameters. Add("p_out",OleDbType. Integer);
      cmdOra. Parameters["p_user_login_name"]. Value=v_user_login_name;
      cmdOra. Parameters["p_user_password"]. Value=v_user_password;
      cmdOra. Parameters["p_user_name"]. Value=v_user_name;
      cmdOra. Parameters["p_user_telephone"]. Value=v_user_telephone;
      cmdOra. Parameters["p_user_type"]. Value=v_user_type;
      cmdOra. Parameters["p_user_login_name"].Direction=ParameterDirection.Input;
      cmdOra. Parameters["p_user_password"].Direction=ParameterDirection.Input;
      cmdOra. Parameters["p_user_name"].Direction=ParameterDirection.Input;
      cmdOra. Parameters["p_user_telephone"].Direction=ParameterDirection.Input;
      cmdOra. Parameters["p_user_type"].Direction=ParameterDirection.Input;
      cmdOra. Parameters["p_out"].Direction=ParameterDirection.ReturnValue;
      try
      {
      cmdOra.ExecuteNonQuery();
      i=( int)cmdOra. Parameters["p_out"]. Value
      }
      catch
      {
      i=-88;
      }
      finally
      {
       if(cmdOra.Connection.State==ConnectionState. Open)
      {
      cmdOra.Connection. Close();
      cmdOra.Connection.Dispose();
      cmdOra. Parameters. Clear();
      cmdOra.Dispose();
      }
      }
       returni;
      }
       5、在窗体界面中调用执行存储过程
      private void button3_Click_1(object sender, System.EventArgs e)
      {
      int i=new clsPublic().Insert_User_Information("dinya","111","DINYA","13877778888",0,
      "cux_franchiser.insert_user_information");
      MessageBox.Show(i.ToString());
      }
      
      在本例第二步中,定义一个输出参数用来存储执行的结果,Oracle存储过程允许给输出参数直接赋值,在。NET中设置该参数类型:cmdOra. Parameters["p_out"].Direction=ParameterDirection.ReturnValue;
      将其设置为返回值,这样在调用Insert_User_Information执行存储过程后,该参数将执行结果返回给用户。
      在第五步窗体界面中调用存储过程的时候,最后一个参数为"cux_franchiser.insert_user_information",该值中cux_franchiser为自己定义的一个包,insert_user_information为包中的过程。(具体对包的使用请参考Oracle相关书籍中有关包的使用一节)。
      
      其中需要指出的是,用来连接Oracle数据库的联结串会因为OleDB的厂家不同而不同,本例中使用微软的,您在开发过程也可以使用Oracle公司的,可以到Oralce网站下载,地址:
      http://www.oracle.com/technology/software/tech/windows/ole_db/index.html。
      
       后记
      在.NET + Oracle的开发过程,当然还有很多其他的操作技巧,希望本文能够起到抛砖引玉的作用,刚接触开发的读者或Oracle初学者,可以参考本文,举一反三。此文您也可以在作者的Blog中找到:http://blog.csdn.net/dinya2003/
      
       作者简介
      曾就职于机械相关行业,后做Windows程序开发。现就职于一家制造企业,做Oracle ERP开发工作,现任CSDN论坛Oracle开发版版主。
      作者Mail: dinya20@tom.com
      分类:数据库技术
      评论()引用阅读()圈子编辑打印有奖举报
      前一篇:ADO.NET数据库访问技术
      后一篇:Oracle数据库的备份与恢复

    本文转自
    http://blog.sina.com.cn/s/blog_3fed6e0b010008vr.html
    展开全文
  • Oracle存储过程之带有返回参数

    千次阅读 2015-12-24 17:15:11
    1、基本语法 ...创建一个存储过程的基本语句如下: CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)] {AS|IS} [说明部分:参数定义、变量定义、游标定义] BEGIN  可
  • 一般我们会把数据的整合、预处理过程及需要展示的数据处理过程放到...代码的形式展示如下: import cx_Oracle as cx address = "用户名/密码@IP:端口/实例名" conn = cx.connect(address , encoding = 'UTF-8
  • 认识存储过程和函数 存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块。...* 存储过程和函数命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的,因为代码不保存在本
  • 个存储过程需要插入200笔数据到EOVHR,通过SQL1查询出需要插入的200笔数据,但插入到第90笔的时候报唯一性错误ORA-00001。怀疑是不是这200笔数据有重复,但查询出这200笔数据时发现并无重复数据。
  • oracle参数文件

    千次阅读 2008-07-30 09:23:00
    但是,oracle数据库中据是庞大的,且正因为参数文件包含了数据库的所有配置信息,所以参数,有几百,一时是难以熟悉和掌握的,这需要在实践中的配置,了解和领会。有关各个参 数的详细参考信息,请参阅or
  • Oracle存储过程

    万次阅读 多人点赞 2019-07-01 14:52:17
    Oracle存储过程详解 procedure 1.创建Oracle存储过程 prodedure create or replace procedure test(var_name_1 in type,var_name_2 out ty pe) as --声明变量(变量名 变量类型) begin --存储过程的执行体 end ...
  • ORACLE执行计划学习总结

    千次阅读 2016-04-06 18:54:01
    如何看懂ORACLE执行计划 一、什么是执行计划 An explain plan is a representation of the access path that is taken when a query is executed within Oracle. 二、如何访问数据 At the physical level Oracle ...
  • oracle 存储过程返回 结果集 table形式 (使用sys_refcursor 及程序包package 两种方式) 2015年11月27日 12:01:30 cherish1forever 阅读数:9231 标签: oracle存储过程sys_refcursor返回结果集返回table 更 ...
  • oracle 存储过程

    千次阅读 2014-01-21 13:59:42
    创建和删除存储过程   创建存储过程,需要有CREATE ...创建一个存储过程的基本语句如下: CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)] {AS|IS} [说明部分] BEGIN 可执行部分
  • 这篇文章主要介绍了oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包等相关资料,需要的朋友可以参考下 oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数...
  • oracle 建表相关参数

    千次阅读 2011-01-16 20:58:00
    建表一般来说是挺简单的事情,但是Oracle的建表语句有很可选的参数,有些我们可能平时不太用,用的时候又不知道怎么用,这里就写一较完整的建表的例 create table tp_serv_day ( serv_...
  • 若要为参数设置多值属性,则在“报表参数”对话框上选择“多值”选项。可以任何参数类型设置为多值(除布尔值之外...不过,如果要将多个参数值传回查询,则必须满足下列要求: 数据源必须为 SQL Server、Oracle 或 A
  • oracle10g初始化参数说明

    千次阅读 2012-09-04 16:28:52
    oracle10g初始化参数说明 参数名: O7_DICTIONARY_ACCESSIBILITY 类别:安全性和审计 说明: 主要用于从 Oracle7 移植到 Oracle8i。如果该值为 TRUE, SYSTEM 权限 (如SELECT ANY TABLE) 不限制对 SYS 方案中各...
  • ORACLE rman 备份工具参数设置

    千次阅读 2011-04-12 10:36:00
    1.configure retention policy to redundancy 1: 是用来决定那些备份不再需要了,它一共有三种可选项,分别是 CONFIGURE RETENTION POLICY TO ...第一种recover window是保持所有足够的备份,可以数据库系统恢复到
  • 陈焕生:深入理解Oracle 的并行执行

    千次阅读 2015-02-26 09:01:47
    执行一个sql 时,分配多个并行进程同时执行数据扫描,连接以及聚合等操作,使用更多的资源,得到更快的sql 响应时间。并行执行是充分利用硬件资源,处理大量数据时的核心技术。 Oracle并行执行是一种分而治之的...
  • oracle 优化器之执行计划

    千次阅读 2015-05-29 22:26:13
    什么是执行计划执行计划显示了执行sql语句所需步骤的...执行计划通常是表格形式来显示,这个执行计划实际上是一树形结构.例如下面是一基于sh方案的查询:SELECT prod_category, AVG(amount_sold) FROM sales s,
  • .net 调用Oracle存储过程

    千次阅读 2014-03-07 12:38:04
    在.NET中调用Oracle9i存储过程可以用多个数据提供者,比如OralceClient,OleDb。本文用OralceClient为例。.NET语言用C#。   一、调用一个带输入、输出参数存储过程 首先,在Oralce中创建存储过程如下: create ...
  • Oracle

    千次阅读 2018-04-15 22:05:57
    Oracle公司,数据库起家的,收购了sun【java mysql数据库】Oracle数据库是其中的产品之一。也是第一提出sql命令的数据库。Oracle提供了数据的解决方案。 解决十万百万级数据而Windows 系统无法处理庞大的数据...
  • Oracle sql执行计划解析

    万次阅读 2016-01-22 16:42:47
    设置缺省的优化器的方法,是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择,如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。 为了使用基于成本的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 56,799
精华内容 22,719
关键字:

oracle如何将多个存储以参数形式执行