Oracle PLSQL笔记（过程的创建和及调用）

  过程(procedure)： 用于在数据库中完成特定的操作或者任务。是一个PLSQL程序块，可以永久的保存在数据库中以供其他程序调用。  

 

一、创建所需的表USERS

create table  users(id int primary key ,
name varchar2(20) not null,
pword varchar2(30) not null,
email varchar2(20));

 

 

二、创建过程

 
create or replace procedure users_insert_proc(
s_id in number,
s_name in varchar2,
s_pwd in varchar2,
s_email in varchar2 
)is  begin
insert into users(id,name,pword,email) values(s_id,s_name,s_pwd,s_email);
 end ;
/


create or replace procedure  users_updatebyId_proc(
s_id in number,
s_name in varchar2,
s_pwd in varchar2,
s_email in varchar2 
)is  begin
update   users set name=s_name ,pword=s_pwd ,email=s_email
 where id =s_id;
 end ;
/


create or replace procedure usersbyId_delete_proc(
s_id in number 
 )is  begin
delete from users where id =s_id;
 end ;
/

复制上述代码在命令窗口运行

 SQL> create or replace procedure users_insert_proc(
  2  s_id in number,
  3  s_name in varchar2,
  4  s_pwd in varchar2,
  5  s_email in varchar2
  6  )is  begin
  7  insert into users4(id,name,pword,email) values(s_id,s_name,s_pwd,s_email);
  8   end ;
  9  /
 
Procedure created
SQL> create or replace procedure  users4_updatebyId_proc(
  2  s_id in number,
  3  s_name in varchar2,
  4  s_pwd in varchar2,
  5  s_email in varchar2
  6  )is  begin
  7  update   users4 set name=s_name ,pword=s_pwd ,email=s_email
  8   where id =s_id;
  9   end ;
 10  /
 
Procedure created
SQL> create or replace procedure users4byId_delete_proc(
  2  s_id in number
  3   )is  begin
  4  delete from users4 where id =s_id;
  5   end ;
  6  /
 
Procedure created
 
SQL>

 

三、调用过程

1）在命令窗口输入exec users_insert_proc(2,'chenx','root','3243242@qq.com');

SQL> exec users_insert_proc(2,'chenx','root','3243242@qq.com');
 
PL/SQL procedure successfully completed

 

2）

SQL> exec users_updatebyId_proc(2,'wuyong','root','3243242@qq.com');
 
PL/SQL procedure successfully completed

 

3）

SQL> exec usersbyId_delete_proc(2) ;
PL/SQL procedure successfully completed