1.如何使用Oracle存储过程的一个简单例子

如何使用Oracle存储过程的一个简单例子

       æ¥¼ä¸»æ‚¨å¥½

       ---创建表

       create table TESTTABLE

       (

        id1 VARCHAR2(),存储存储查询标准函数源码

        name VARCHAR2()

       )

       select t.id1,t.name from TESTTABLE t

       insert into TESTTABLE (ID1, NAME)

       values ('1', 'zhangsan');

       insert into TESTTABLE (ID1, NAME)

       values ('2', 'lisi');

       insert into TESTTABLE (ID1, NAME)

       values ('3', 'wangwu');

       insert into TESTTABLE (ID1, NAME)

       values ('4', 'xiaoliu');

       insert into TESTTABLE (ID1, NAME)

       values ('5', 'laowu');

       ---创建存储过程

       create or replace procedure test_count

       as

       v_total number(1);

       begin

        select count(*) into v_total from TESTTABLE;

        DBMS_OUTPUT.put_line('总人数：'||v_total);

       end;

       --准备

       --线对scott解锁：alter user scott account unlock;

       --应为存储过程是在scott用户下。还要给scott赋予密码

       ---alter user scott identified by tiger;

       ---去命令下执行

       EXECUTE test_count;

       ----在ql/spl中的sql中执行

       begin

        -- Call the procedure

        test_count;

       end;

       create or replace procedure TEST_LIST

        AS

        ---是用游标

        CURSOR test_cursor IS select t.id1,t.name from TESTTABLE t;

        begin

        for Test_record IN test_cursor loop---遍历游标，在打印出来

        DBMS_OUTPUT.put_line(Test_record.id1||Test_record.name);

        END LOOP;

        test_count;--同时执行另外一个存储过程（TEST_LIST中包含存储过程test_count）

        end;

        -----执行存储过程TEST_LIST

        begin

        TEST_LIST;

        END;

        ---存储过程的参数

        ---IN 定义一个输入参数变量，用于传递参数给存储过程

        --OUT 定义一个输出参数变量，用于从存储过程获取数据

        ---IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能

        --这三种参数只能说明类型，不需要说明具体长度 比如 varchar2()，defaul 可以不写，但是作为一个程序员最好还是写上。

        ---创建有参数的存储过程

        create or replace procedure test_param(p_id1 in VARCHAR2 default '0')

        as v_name varchar2();

        begin

        select t.name into v_name from TESTTABLE t where t.id1=p_id1;

        DBMS_OUTPUT.put_line('name：'||v_name);

        end;

        ----执行存储过程

        begin

        test_param('1');

        end;

        default '0'

       ---创建有参数的存储过程

        create or replace procedure test_paramout(v_name OUT VARCHAR2 )

        as

        begin

        select name into v_name from TESTTABLE where id1='1';

        DBMS_OUTPUT.put_line('name：'||v_name);

        end;

        ----执行存储过程

        DECLARE

        v_name VARCHAR2();

        BEGIN

        test_paramout(v_name);

        DBMS_OUTPUT.PUT_LINE('name：'||v_name);

        END;

        -------IN OUT

        ---创建存储过程

        create or replace procedure test_paramINOUT(p_phonenumber in out varchar2)

        as

        begin

        p_phonenumber:='-'||p_phonenumber;

        end;

        ----

        DECLARE

        p_phonenumber VARCHAR2();

        BEGIN

        p_phonenumber:='';

        test_paramINOUT(p_phonenumber);

        DBMS_OUTPUT.PUT_LINE('新的电话号码：'||p_phonenumber);

        END;

        -----sql命令下，查询当前用户的存储过程或函数的源代码，

        -----可以通过对USER_SOURCE数据字典视图的查询得到。USER_SOURCE的结构如下：

        SQL> DESCRIBE USER_SOURCE ;

        Name Type Nullable Default Comments

       ---- -------------- -------- -------

       -------------------------------------------------------------------------------------------------------------

        NAME VARCHAR2() Y Name of the object

       TYPE VARCHAR2() Y Type of the object: "TYPE", "TYPE BODY", "PROCEDURE", "FUNCTION",

        "PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"

        LINE NUMBER Y Line number of this line of

       source

        TEXT VARCHAR2() Y Source text

       SQL>

        ---查询出存储过程的定义语句

        select text from user_source WHERE NAME='TEST_COUNT';

        ----查询存储过程test_paramINOUT的参数

        SQL> DESCRIBE test_paramINOUT;

        Parameter Type Mode Default?

        ------------- -------- ------ --------

        P_PHONENUMBER VARCHAR2 IN OUT

        SQL>

        ---查看当前的存储过程的状态是否正确，

        ---VALID为正确，INVALID表示存储过程无效或需要重新编译

        SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='TEST_COUNT';

        -----如果要检查存储过程或函数的依赖性，可以通过查询数据字典USER_DENPENDENCIES来确定，该表结构如下：

        SQL> DESCRIBE USER_DEPENDENCIES;

        Name Type Nullable Default Comments

        -------------------- ------------- -------- ------- ----------------------------------------------------------

        NAME VARCHAR2() Name of the object

        TYPE VARCHAR2() Y Type of the object

        REFERENCED_OWNER VARCHAR2() Y Owner of referenced object (remote owner if remote object)

        REFERENCED_NAME VARCHAR2() Y Name of referenced object

        REFERENCED_TYPE VARCHAR2() Y Type of referenced object

        REFERENCED_LINK_NAME VARCHAR2() Y Name of dblink if this is a remote object

        SCHEMAID NUMBER Y

        DEPENDENCY_TYPE VARCHAR2(4) Y

        SQL>

        ---查询存储过程TEST_COUNT的依赖关系

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