Create Oracle procedure in pl

The following articles will teach you how to skillfully create and implement Oracle procedure and call Oracle procedure in pl/SQL, we first use the relevant Code to introduce the actual operation steps. The following describes the specific content. I hope you will learn more about it.

 
 

  
  
 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 Oracle 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 Oracle 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.

Of course, 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 above content is a description of Oracle procedure, hoping to help you in this regard.