Oracle存储过程

存储过程的创建：

定义部分、执行部分、异常部分。用户要在自己的模式中创建存储过程，则只需具有CREATE PROCEDURE 权限，想在其他模式下创建，用户必须具有create any procedure权限，拥有alter any procedure权限的用户可以替换其他模式的存储过程。

CREATE [OR REPLACE] PROCEDURE [schema.] procedure_name [parameter_list]

{AS | IS}

declaration_section

BEGIN

　　executable_section

EXCEPTION

　　exceptioon_section

END [procedure_name];

存储过程的调用：

execute proc(argument_list);

存储过程的编译:

alter procedure pro_name COMPILE;

存储过程的删除：

drop procedure pro_name;

(不能删除包中的存储过程）

几个例子：

在存储过程中创建触发器：

 1 CREATE OR REPLACE PROCEDURE C_trigger
 2 authid current_user is 
 3 p_string VARCHAR2(2000);
 4 BEGIN
 5 p_string := 'CREATE OR REPLACE TRIGGER tri_row 
 6 AFTER DELETE ON item.student
 7 DECLARE 
 8 v3 INTEGER;
 9 BEGIN 
10 SELECT COUNT(*) INTO v3 FROM item.student;
11 END;';
12 EXECUTE IMMEDIATE p_string;
13 END;
14 SQL>exec C_trigger;
15 SQL> select owner,trigger_name
16 2 from dba_triggers
17 3 where owner = 'ITEM';
18 
19 
20 OWNER TRIGGER_NAME
21 ------------------------------ -----------------------
22 ITEM TRI_ROW
23 ITEM TRIGGER_MYORDER
24 
25 已选择2行。
26 ---触发器创建成功。

如果无法删除存储过程？？？
---方法：先查看那些会话正在执行该存储过程

1 SELECT a.sid,a.serial#,b.sql_text 
2 FROM v$session a,v$sql b
3 WHERE a.sql_hash_value = b.hash_value
4 AND a.sql_address = b.address 
5 AND b.sql_text LIKE 'C_tri%';

--then，杀死那个session
ALTER SYSTEM KILL SESSION '123,231';--SID,SERIAL的号
--如果该命令执行失败，再试试：

1 SELECT spid
2 FROM v$process p,v$session s
3 WHERE s.paddr = p.ADDR and sid = 123;

然后杀死进程。

加密 procedure的时候使用命令： wrap inname=e:Scriptswrap_test.sql name=testwrap.out (注意 =号附近不能用空格！！)
将存储过程返回一个结果集：

使用游标变量

 1 --定义包头：
 2 CREATE OR REPLACE PACKAGE pack_return_result AS
 3 TYPE type_cursor IS REF CURSOR;--游标变量
 4 PROCEDURE pro_getresult(p IN OUT type_cursor);
 5 END pack_return_result;
 6 --定义包体：
 7 CREATE OR REPLACE PACKAGE BODY pack_return_result AS
 8 PROCEDURE pro_getresult (p IN OUT type_cursor) AS
 9 BEGIN
10 OPEN p FOR
11 SELECT name,producer,price
12 FROM phone_info 
13 WHERE price > 500;
14 END;
15 END;
16 --定义游标绑定变量：
17 VARIABLE v_bind_ref REFCURSOR 
18 --执行包中的存储过程：
19 EXECUTE pack_rerurn_result.pro_getresult(:v_bind_ref)
20 
21 PL/SQL 过程已成功完成。
22 
23 --执行结果，打印游标绑定变量中的内容：
24 SQL> PRINT v_bind_ref
25 
26 NAME PRODUCER PRICE
27 -------------------- ------------------------------ ----------
28 iphone APPLE 3999
29 C510c 爱立信 1000
30 
31 已选择2行。
32 
33 
34 已用时间: 00: 00: 00.01

1 SQL> VARIABLE v_bind_ref REF CURSOR
2 用法: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
3 VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
4 NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
5 REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]