Call Oracle procedure operations in pl/SQL

The following articles mainly describe how to create and call Oracle procedure in pl/SQL. This article describes the actual procedure of Oracle procedure through the actual operations of the relevant application code, the following is an introduction to the main content of the text. I hope you will have some gains.

 
 

  
  
-- Create table  
  
  
create table A  
  
  
(  
  
  
USERID NUMBER(38),  
  
  
PWD VARCHAR2(30)  
  
  
)  
  
  
tablespace USERS  
  
  
pctfree 10  
  
  
initrans 1  
  
  
maxtrans 255  
  
  
storage  
  
  
(  
  
  
initial 64K  
  
  
minextents 1  
  
  
maxextents unlimited  
  
  
);  
 
 

Create procedure

 
 

  
  
create or replace procedure up_sel(cur_test out sys_refcursor)  
  
  
is  
  
  
begin  
  
  
open cur_test for  
  
  
select * from a;  
  
  
end;  
 
 

Delete stored procedure

 
 

  
  
drop procedure up_sel 
 
 

Submit

 
 

  
  
commit 
 
 

Execute procedure in PL/SQL

 
 

  
  
---// file-->>new -->>test window  
  
  
begin  
  
  
-- Call the procedure  
  
  
up_sel(cur_test => :cur_test);  
  
  
end;  
 
 

In variable, enter the defined cursor name cur_test.

In Type, enter the Cursor Type Cursor.

Click the... icon on the right of the Value to display the expected result.

If you create an Oracle procedure in pl/SQL and call it, you can also right-click the stored procedure and click TEST.

Stored Procedure for deleting data

 
 

  
  
create or replace procedure up_del  
  
  
(userid in varchar2)  
  
  
is  
  
  
begin  
  
  
delete from a where USERID=userid;  
  
  
end;  
 
 

Enter the value to be passed in the value.

Add data

 
 

  
  
create or replace procedure up_add  
  
  
(  
  
  
userid in varchar2,  
  
  
pwd in varchar2  
  
  
)  
  
  
is  
  
  
begin  
  
  
insert into a(USERID,PWD) values(userid,pwd);  
  
  
commit;  
  
  
end;  
 
 

Run

 
 

  
  
declare  
  
  
begin  
  
  
up_add(11,'222');  
  
  
end;   
 
 

The preceding content describes how to create and call Oracle procedure in pl/SQL. We hope this will help you in this regard.