How to execute oracle stored procedure

In Oracle, you can use the "begin end" statement and specify the stored procedure name and give parameters to execute the stored procedure. The syntax is "create or replace procedure stored procedure name begin username procedure name (parameter); end;".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to execute oracle stored procedure

Example: There is the following stored procedure to create an index

--procedure create_index
create or replace procedure create_index(indexName in varchar2, tableName in varchar2, columnName in varchar2)
i
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) In the command window: exec stored procedure (parameters...);

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

2) Under the sql window:

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

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to execute oracle stored procedure. For more information, please follow other related articles on the PHP Chinese website!