First Meeting of Oracle Stored Procedure (I)

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

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

Stored procedures are generally divided into three parts: Declaration, execution, and exception.

Variable declaration:

Syntax variable name + variable type

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

If judgment:

Syntax: If (expression) 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 Loop traversal:
Syntax: For... in... loop
(1) looping cursor

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) traverse arrays cyclically

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 loop traversal:

Syntax: While (expression) loop

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

Probably know how the stored procedure is, and then gradually learn about the stored procedure through the instance

Table creation:

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

Stored Procedure:

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) isbegin  INSERT INTO user_info    (id, name, pwd, address)  VALUES    (n_id, n_name, n_pwd, n_address);end AddNew

Stored Procedure Call:

Stored Procedure Call: (1) PL/SQL anonymous block call 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 ('user' | n_name | 'inserted successfully'); end;

(When an anonymous block is executed in PLSQL developer, after F8 is executed, press F10 to save the block to update it to the database table. The test is successful .)

(2) Calling stored procedures in JDBC