在SQLPLUS中创建、运行、调试、查看、修改和删除存储过程2011年03月11日 星期五 16:01平时基本上都是用PL/SQL DEVELOPER对ORACLE进行操作,突然有一天没有PL/SQL DEVELOPER可以用
了,惨了,不会操作了,经常有这么让人不省心的环境,所以还是要全面一点,好的技术员要能够应付各种恶劣的环境,呵呵,利用一点时间学习和总结如何在SQLPLUS中操作存储过程。
一、创建
在SQLPLUS命令行界面创建存储过程:
SQL> create or replace procedure add_ord(p_ordid number,p_orddate date,p_custid
number,p_shipdate date,p_total number)
  2  is
  3  e_integrity exception;
  4  e_shipdate exception;
  5  pragma exception_init(e_integrity,-2291);
  6  begin
  7  if p_shipdate >= p_orddate then
  8  insert into ord values(p_ordid,p_orddate,p_custid,p_shipdate,p_total);
  9  dbms_output.put_line('operaton success');
 10  else
 11  raise e_shipdate;
 12  end if;
 13  exception
 14  when dup_val_on_index then
 15  raise_application_error(-20001,'the order has already exist');
 16  when e_integrity then
 17  raise_application_error(-20002,'the customer does not exist');
 18  when e_shipdate then
 19  raise_application_error(-20003,'delivery date can not earlier than the orde
r date');
 20  end;
 21  /
过程已创建。
二、运行
如果要在屏幕上显示出“dbms_output.put_line”中的字符串,则需要做一些设置,如下:
SQL> show serveroutput
serveroutput OFF
SQL> set serveroutput on
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
紧接着在SQLPLUS中输入“EXEC”命令执行存储过程,如下:
SQL> exec add_ord(600,'02-1月-11',215,'10-1月-11',100)
operaton success
PL/SQL 过程已成功完成。
三、调试
很多时候写存储过程的时候不可能一次就编译成功的,会出现各种各样的问题,这个时候就需要程序员耐心的进行调试,在SQLPLUS中有一个命令show error可以帮助程序员找到错误的地方,
假如我们把刚刚的那个例子的最后一个封号去掉看一下会报什么错。如下:
警告: 创建的过程带有编译错误。
SQL> show error
PROCEDURE ADD_ORD 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
20/3     PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
         ; <an identifier>
         <a double-quoted delimited-identifier> delete exists prior
         <a single-quoted SQL string>
         符号 ";" 被替换为 "end-of-file" 后继续。
找到错误,修改一下,加上个封号就OK了,当然调试的问题难易程度不同这只是举个简单的例子。重新编译一个存储过程可以用这个语句:
alter procedure prodedure_name compile;
四、查看
要想在SQLPLUS中查看已经存在的存储过程,需要写如下的一个SQL语句:
SQL> select text from all_source where name='ADD_ORD';
TEXT
--------------------------------------------------------------------------------
procedure add_ord(p_ordid number,p_orddate date,p_custid number,p_shipdate date,
p_total number)
is
e_integrity exception;
e_shipdate exception;
pragma exception_init(e_integrity,-2291);
begin
if p_shipdate >= p_orddate then
insert into ord values(p_ordid,p_orddate,p_custid,p_shipdate,p_total);
dbms_output.put_line('operation success');
TEXT
--------------------------------------------------------------------------------
else
raise e_shipdate;
end if;
exception
when dup_val_on_index then
raise_application_error(-20001,'the order has already exist');
when e_integrity then
raise_application_error(-20002,'the customer does not exist');
when e_shipdate then
raise_application_error(-20003,'delivery date can not earlier than the order dat
e');
TEXT
--------------------------------------------------------------------------------

end
已选择20行。
当然这只是查看存储过程而已,并不能对其进行编辑和修改,要想对已经存在的存储过程进行修改还是比较麻烦(按照目前我找到的方法,以后有好的方法再补上)。
五、修改
首先得先把存储过程给查找出来,语句:select text from all_source where name='ADD_ORD';,查找出来之后用SPOOL命令把它先存入一个文本档中,在文本文档中修改好,再把整个修改
过的存储过程粘贴到SQLPLUS中,再进行一次编译就行了。具体如下:
SQL> spool test.sql
SQL> select text from all_source where name='ADD_ORD';
TEXT
--------------------------------------------------------------------------------
procedure add_ord(p_ordid number,p_orddate date,p_custid number,p_shipdate date,
p_total number)
is
e_integrity exception;
e_shipdate exception;
pragma exception_init(e_integrity,-2291);
begin
if p_shipdate >= p_orddate then
insert into ord values(p_ordid,p_orddate,p_custid,p_shipdate,p_total);
dbms_output.put_line('operation success');
TEXT
--------------------------------------------------------------------------------
else
raise e_shipdate;
end if;
exception
when dup_val_on_index then
raise_application_error(-20001,'the order has alreadyexist');
when e_integrity then
raise_application_error(-20002,'the customer does not exist');
when e_shipdate then
raise_application_error(-20003,'delivery date can not earlier than the order dat
e');
TEXT
--------------------------------------------------------------------------------

end
已选择20行。
SQL> spool off
这样你就可以到test.sql这个文档中进行修改了,修改好再贴回去编译就行了,这个过程好像有点麻烦,但没办法目前我还没发现好的方法,如果你有,可以告诉我一下,我好改进,呵呵
六、删除
最后一步了,就是如何删除已经存在的存储过程,不多说,如下命令:
SQL> select object_name,object_type,status from user_objects where object_type='
PROCEDURE';
OBJECT_NAME     OBJECT_TYPE       STATUS
INSERT_DEPT      PROCEDURE           VALID
ADD_ORD             PROCEDURE           INVALID
ADD_DEPT           PROCEDURE           INVALID
SQL> drop procedure ADD_ORD;
过程已删除。
SQL> select object_name,object_type,status from user_objects where object_type='
PROCEDURE';
OBJECT_NAME         OBJECT_TYPE         STATUS
INSERT_DEPT           PROCEDURE           VALID
ADD_DEPT                PROCEDURE           INVALID