ORACLE PROCEDURE 存儲過程

ORACLE PROCEDURE 存儲過程

如何寫出ORACLE PROCEDURE 存儲過程的講解

可讀性

性能（減少網絡，減少連接次數，事務管理）

方便管理

安全性

1.創建procedure

a.關鍵字 create or replace

b.參數 parameters(in & out & in out 默認為in)

/**SET SERVEROUTPUT ON

EXEC SP_TSS_CN0001 運行PROCEDURE**/

CREATE OR REPLACE PROCEDURE SP_TSS_CN0001

IS

BEGIN

DBMS_OUTPUT.put_line(SYSDATE);

END;

/**帶參數的procedure EXEC SP_TSS_CN0002(1234,'ROSE',300)**/

CREATE OR REPLACE PROCEDURE SP_TSS_CN0002

(ENO NUMBER,NAME VARCHAR2,SALARY NUMBER)IS

BEGIN

INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(ENO,NAME,SALARY);

END;

SELECT * FROM EMP WHERE ENAME='ROSE'

/**OUT 參數

var name varchar2; 定義變量接收輸出參數

var salary number;

exec SP_TSS_CN0003(1234,:name,:salary);

**/

CREATE OR REPLACE PROCEDURE SP_TSS_CN0003

(ENO NUMBER,NAME OUT VARCHAR2,SALARY OUT NUMBER)IS

BEGIN

SELECT ENAME,SAL INTO NAME,SALARY FROM EMP WHERE EMPNO=ENO;

END;

/**IN OUT 參數

VAR R1 NUMBER; 定義變量接收OUT參數數據

VAR R2 NUMBER;

EXEC :R1:=100; 為IN參數賦值

EXEC :R2:=50;

EXEC compute(:R1,:R2); 運算

**/

CREATE OR REPLACE PROCEDURE SP_TSS_CN0004

(R1 IN OUT NUMBER,R2 IN OUT NUMBER) IS

V1 NUMBER;

V2 NUMBER;

BEGIN

V1:=R1/R1;

V2:=R1-R2;

R1:=V1;

R2:=V2;

END;

procedure的授權

a.create public synonym

b.grant privilege to other user

/**刪除**/

drop procedure sp_xxx_xxx