学步园

————————————————      Hello World           ——————————————————————

create or replace procedure HelloWorld AS
begin
  insert into MYPROCEDURE(id,name,city,MANAGER)values(1,'ss','ss','ss');
end HelloWorld;

存储过程一般分分三部分：声明部分、执行部分、异常部分

变量声明：

语法 变量名+变量类型

create or replace procedure HelloWorld AS
variable_a varchar(8);
variable_b varchar(5);
begin
  insert into MYPROCEDURE(id,name,city,MANAGER)values(1,'ss','ss','ss');
end HelloWorld;

if判断：

语法：if (表达式) then begin end;end if;

create or replace procedure test(prameter in number) is
     begin 
       if x > 0 then 
          begin
            x := 0 - x;
          end;
       end if;
          if x = 0 then 
          begin
             x := 1;
          end;
       end if;
end test;

for循环遍历：
语法：for ... in ... loop
（1）循环遍历游标

create or replace procedure test() as
  Cursor cursorName is
    select table from student;
  name varchar(20);
begin
  for name in cursorName LOOP
      begin
          dbms_output.putline(name);    
    end;  
  end LOOP;
end test;

（2）循环遍历数组

create or replace procedure test(array in TestArray) as
   i number;
begin
  i:=1;
  for i in TestArray LOOP
    dbms_output.put_line(array(i));
end test;

while循环遍历：

语法：while(表达式)loop

create or replace procedure test(i in number) as
begin
  while i < 10 LOOP
    begin
      i := i + 1;    
    end;  
  end LOOP;
  dbms_output.put_line(i);
end test;

大概的知道存储过程是怎么个情况，接下来通过实例逐步了解存储过程

建表：

create table USER_INFO
(
  ID      VARCHAR2(4),
  NAME    VARCHAR2(15),
  PWD     VARCHAR2(15),
  ADDRESS VARCHAR2(30)
)

存储过程：

create or replace procedure AddNewUser(n_id      user_info.id%TYPE,
                                       n_name    user_info.name%TYPE,
                                       n_pwd     user_info.pwd%TYPE,
                                       n_address user_info.address%TYPE) is
begin
  INSERT INTO user_info
    (id, name, pwd, address)
  VALUES
    (n_id, n_name, n_pwd, n_address);

end AddNew

存储过程调用：

存储过程的调用：（1）PL/SQL匿名块调用DECLARE

 
n_id user_info.id%TYPE := 'u002';
 
n_name user_info.name%TYPE := 'wish';
 
n_pwd user_info.pwd%TYPE := 'history';
 
n_add user_info.address%TYPE := 'shanghai';
 
BEGIN
 
AddNewUser(n_id,n_name,n_pwd,n_add);
 
DBMS_OUTPUT.PUT_LINE('用户 ' || n_name || ' 已经成功插入');
 
END;

（在PLSQL Developer中执行匿名块时，F8执行后，按F10保存才可更新到数据库表中，上述事例测试成功。）

(2)JDBC中调用存储过程