oracle 过程 如何执行,Oracle如何执行存储过程

示例：有如下创建索引的存储过程

--procedure create_index

create or replace procedure create_index(indexName in varchar2, tableName in varchar2, columnName in varchar2)

is

tmp_name VARCHAR2(50);

TMP_SQL VARCHAR2(1024);

cnt NUMBER;

begin

select table_name into tmp_name from user_tables where upper(table_name)=upper(tableName);

if length(tmp_name)>0 then

SELECT COUNT(*) INTO cnt FROM USER_INDEXES T WHERE T.TABLE_NAME=''|| tableName ||'' AND T.INDEX_NAME=''|| indexName;

IF cnt = 0 THEN

execute immediate 'create index ' || indexName ||' on ' || tableName ||'('|| columnName ||') TABLESPACE TBS_TXNIDX';

END IF;

end if;

EXCEPTION

WHEN NO_DATA_FOUND THEN

BEGIN

NULL;

END;

end;

/

1)在命令窗口下: exec 存储过程(参数...);

exec create_index('IDX_ORG_SET_PROCESS_1','CPS_ORG_SETTLEMENT_PROCESS','ORG_IDENTITY_ID ASC');

2)在sql窗口下:

begin

create_index('IDX_STATEMENT_DETAIL_LINK','CPS_BANK_STATEMENT_DETAIL','LINK_TXN_ID ASC');

end;