1.基本语法
Create or replace procedure POC_TEST
(
参数1 in Number;
参数2 in Number;
参数3 表名.字段名%Type
)IS
变量1 Integer :=0;
变量2 Date
begin
do something
end POC_TEST
调用: Call POC_TEST(参数1,参数2,参数3);
2 Select into Statement
将 select 查询的值存人变量中,可以同时为多个变量赋值 必须有一条记录
如果没记录,抛出 (NO_DATA_FOUND)
Create or replace procedure POC_TEST
(
参数1 in Number;
)IS
var1 Integer :=0;
var2 Date
begin
select col1,col2 into var1,var2 from table1 where xxx;
do something other;
Exception
When NO_DATA_FOUND then
do something else;
end;
end POC_TEST
3 判断
if V_TEST=1 then
begin
do something;
end;
end if;
4 while 循环
WHILE V_TEST<3 LOOP
BEGIN
do something;
END;
END LOOP;
5 变量赋值
V_TEST := 3;
6 用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7 带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;
8 用pl/sql developer debug
连接数据库后建立一个test Windows
在窗口输入调用sp的代码,F9 开始debug,Ctrl+n单步调试