oracle存储函数存储过程语法及基本示例
基本语法:
declare
说明部分(声明变量,游标,例外说明)
begin
语句序列(逻辑代码DML语句)
exception
例外处理语句
end;
/
个人理解:if…end if;loop…end loop;等语法的使用相当于java中的花括号,即告诉计算机我开始执行到结束的位置。
个人案例:
CREATE OR REPLACE PROCEDURE MYTEST1 --声明存储过程名字
AS
--声明部分s
CURSOR EMP_CURSOR IS
SELECT * FROM EMP;--cursor必须在方法外面声明
COLVALUE EMP%ROWTYPE;
--声明部分e
BEGIN
--逻辑部分s
OPEN EMP_CURSOR;--打开游标
LOOP--循环开始
FETCH EMP_CURSOR
INTO COLVALUE;--取出每次循环的数据
DBMS_OUTPUT.PUT_LINE(COLVALUE.ename);--打印员工名字
EXIT WHEN EMP_CURSOR%NOTFOUND;
END LOOP;--循环结束
CLOSE EMP_CURSOR;--关闭游标
--逻辑部分e
END;
IF语句:
1.IF 条件 THEN 语句1;
语句2;
END IF;
2.IF 条件 THEN 语句;
ELSE 语句;
END IF;
3.IF 条件 THEN 语句;
ELSIF 语句;
ELSIF 语句;
…;
ELSE 语句;
END IF;
declare
num integer;
inputno number(10);
begin
inputno :='&请输入';
select sal into num from scott.emp where empno=inputno;
if(num<2000) then
dbms_output.put_line('薪水低于2000');
elsif (num>=2000 and num<=3000) then
dbms_output.put_line('薪水在3000-2000之间');
else
dbms_output.put_line('薪水高于3000');
end if;
end;
--case语句
declare
v_deptno number:=10;
v_sal number;
begin
case v_deptno
when 10 then v_sal:=1;
when 20 then v_sal:=2;
else
v_sal:=3;
end case;
update scott.emp set sal=sal+v_sal where deptno=v_deptno ;
commit;
end;
--目标 使用case 语句统计员工薪水等级
select ename,sal,case
when sal<2000 then '低等'
when sal>=2000 and sal<3000 then '中等'
when sal>=3000 and sal<4000 then '上等'
else '高等'
end 薪水等级
from emp;
--循环控制语句 loop...exit when...end loop循环控制
declare
v_i int:=1;
begin
loop
v_i:=v_i+1;
exit when v_i=20;
dbms_output.put_line(v_i);
end loop;
end;
--while...loop...end loop循环控制
--九九乘法表
declare
v_i number:=1;
v_j number;
begin
while(v_i<10) loop
v_j:=1;
loop
dbms_output.put(v_j||'*'||v_i||'='||v_j*v_i||' ');
v_j:=v_j+1;
exit when v_j>v_i;
end loop;
dbms_output.put_line('');
v_i:=v_i+1;
end loop;
end;
--for循环
--for 循环变量 in [reverse] 循环下界..循环上界 loop
--循环处理语句段;
--end loop;
declare
v_sum number:=1;
begin
for i in 1..5 loop
v_sum:=v_sum*i;
end loop;
dbms_output.put_line('阶乘结果:'||v_sum);
end;
变量类型:number varchar2 date CHAR CLOB…
引用类型:变量名 引用变量%type 例: ename emp.name%type
记录型变量: 变量名 记录变量%rowtype
案例:
declare emp_list emp%rowtype --声明数据
begin
select * into emp_list from emp where empno='7839';
dbms_output.put_line(emp_list.ename||'的薪水是'||emp_list.sal);
解释:相当于把7839员工数据查出来放入emp_list中,再从中取出名字和薪水
循环语句:
loop循环:
create or replace procedure pro_test_loop is
i number;
begin
i:=0;
loop
ii:=i+1;
dbms_output.put_line(i);
if i>5 then
exit;
end if;
end loop;
end pro_test_loop;
while循环:
create or replace procedure pro_test_while is
i number;
begin
i:=0;
while i<5 loop
ii:=i+1;
dbms_output.put_line(i);
end loop;
end pro_test_while;
for循环1:
create or replace procedure pro_test_for is
i number;
begin
i:=0;
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end pro_test_for;
for循环2:
create or replace procedure pro_test_cursor is
userRow t_user%rowtype;
cursor userRows is
select * from t_user;
begin
for userRow in userRows loop
dbms_output.put_line(userRow.Id||','||userRow.Name||','||userRows%rowcount);
end loop;
end pro_test_cursor;
cursor:游标,相当于java中的list集合,可以存多组数据
fetch:获取当前指针指向的行数据
游标的属性返回值类型意义
%ROWCOUNT 整型获得FETCH语句返回的数据行数
%FOUND 布尔型最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型与%FOUND属性返回值相反
%ISOPEN 布尔型游标已经打开时值为真,否则为假
【填充,无意义】 | 【填充,无意义】 | 【填充,无意义】 |
---|---|---|
CURSOR—> | 第一行 | fetch 字段1 into 变量1; |
【填充,无意义】 | 第二行 | 上述语句将第一行数据取出放到变量1中。 |
【填充,无意义】 | 第三行 | |
【填充,无意义】 | … | |
【填充,无意义】 | 第N行 |
集合:
对于显式游标的运用分为四个步骤:
定义游标—Cursor [Cursor Name] IS;
打开游标—Open [Cursor Name];
操作数据—Fetch [Cursor name]
关闭游标—Close [Cursor Name],这个Step绝对不可以遗漏。
–定义一个带参数的光标
cursor 游标名(参数名 类型)is select ename from emp where deptno=实参;
open 游标名(10)–10相当于参数,10号部门。
既游标数据为:select ename from emp where deptno=10;
1)直接声明
declare
cursor emp_cur is select * from emp;
emp_record emp%rowtype;
begin
open emp_cur;
loop
fetch emp_cur into emp_record;
exit when emp_cur%notfound;
dbms_output.put_line('name is:' || emp_record.ename ||' and sal is:' || emp_record.sal);
end loop;
close emp_cur;
end;
/
(2)ref cursor:分为强类型(有return子句的)和弱类型,强类型在使用时,其返回类型必须和return中的类型一致,否则报错,而弱类型可以随意打开任何类型。
例如:
强类型
declare
type emp_cur_type is ref cursor return emp%rowtype;
emp_cur emp_cur_type;
emp_record emp%rowtype;
begin
open emp_cur for select * from emp;
loop
fetch emp_cur into emp_record;
exit when emp_cur%notfound;
dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' || emp_record.sal);
end loop;
close emp_cur;
--open emp_cur for select * from dept; 错误的,类型不一致。
--close emp_cur;
end;
/
弱类型:
declare
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
emp_record emp%rowtype;
dept_record dept%rowtype;
begin
open emp_cur for select * from emp;
loop
fetch emp_cur into emp_record;
exit when emp_cur%notfound;
dbms_output.put_line('name is:' || emp_record.ename || ' and sal is:' || emp_record.sal);
end loop;
close emp_cur;
open emp_cur for select * from dept; --可再次打开,不同类型的
loop
fetch emp_cur into dept_record;
exit when emp_cur%notfound;
dbms_output.put_line('dname is:' || dept_record.dname);
end loop;
close emp_cur;
end;
/
预定义说明的部分ORACLE异常错误
set serveroutput on;
declare
var_name varchar(60);
begin
select ename into var_name from emp
where deptno=&deptno;
exception
when no_data_found then
dbms_output.put_line('没有匹配数据!');
when too_many_rows then
dbms_output.put_line('返回多行数据!');
when others then
dbms_output.put_line('提示错误不明!');
end;
上一篇: Oracle sql技巧--行列转换
下一篇: 二叉搜索树