一个最简单的oracle存储过程"proc_helloworld"

???????? one of the following:
???????? ; <an identifier> <a double-quoted delimited-identifier>
???????? delete exists prior <a single-quoted SQL string>
???????? The symbol ";" was substituted for "end-of-file" to continue.

通过提示，问题出现在END后面没有分号结束符号，修改后问题得到处理
sec@ora10g> CREATE OR REPLACE PROCEDURE proc_helloworld
? 2? IS
? 3? BEGIN
? 4???? DBMS_OUTPUT.put_line ('Hello World!');
? 5? END;
? 6? /

Procedure created.

6.删除。使用drop语句删除存储过程
sec@ora10g> select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

OBJECT_NAME??????????????????? OBJECT_TYPE???????? STATUS
------------------------------ ------------------- -------
PROC_HELLOWORLD??????????????? PROCEDURE?????????? VALID

sec@ora10g> DROP PROCEDURE proc_helloworld;

Procedure dropped.

sec@ora10g> select object_name,object_type,status from user_objects where OBJECT_TYPE='PROCEDURE';

no rows selected

7.获取。在维护存储过程的过程中往往需要快速的获取存储过程的SQL创建语句，我经常使用的有如下两种方法
1).使用DBMS_METADATA包获得
sec@ora10g> SELECT DBMS_METADATA.get_ddl ('PROCEDURE', 'PROC_HELLOWORLD') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','PROC_HELLOWORLD')
--------------------------------------------------------------------------------

? CREATE OR REPLACE PROCEDURE "SEC"."PROC_HELLOWORLD"
IS
BEGIN
?? DBMS_OUTPUT.put_line ('Hello World!');
END;

2).使用"USER_SOURCE"视图获得，本人推荐使用这种方法查看，格式标准，内容也全面
sec@ora10g> SELECT text FROM user_source WHERE NAME = 'PROC_HELLOWORLD';

TEXT
------------------------------------------------------------------------------------------------------------------------------
PROCEDURE proc_helloworld
IS
BEGIN
?? DBMS_OUTPUT.put_line ('Hello World!');
END;

8.小结
上面的实验，我通过编写、创建、运行、修改、调试、删除和获取七个内容描述了一个存储过程的开发和维护过程。
试验，实验还有实践，技术工作者永远不变的途径！
Goodluck Friends!

-- The End --