———————————————— 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中调用存储过程