Oracle Pl/SQL编程基础
Pl/SQL简介
提高应用程序的运行性能,
提供模块化的程序设计,
自定义标示符,
具有过程语言控制结构,
良好的兼容性,
处理运行错误.
Pl/SQL语言基础
sql是关系数据库的基本操作语言.
sql语言包括:数据查询,数据操纵,事物控制,数据定义和数据控制语言登5个部分.
Pl/SQL块
Pl/SQ的基本单元
DECLARE /*定义部分-变量,常量,游标,列解*/ BEGIN /*定义部分-PL/SQL语句*/ EXCEPTION /*异常处理部分-处理运行错误*/ END;/*结束块标志*/
注意:"="表示判断,":="赋值,"||"连接.
标量类型
VARCHAR2(n),CHAR(n),NUMBER(p,s),DATE,TIMESTAMP,BOOLEAN
if语句
-- 结构: if 条件 then = 表示判断
-- end if;
select * from scott.emp;
declare
v_name varchar(30);
begin
select scott.emp.ename into v_name from scott.emp where scott.emp.empno = 7499;
if v_name = 'SMITH' then
begin
dbms_output.put_line('没有该员工');
end;
elsif xxx then
begin
end;
else
dbms_output.put_line('员工姓名' || v_name);
end if;
end;
CASE语句
-- case
switch(xxx) {
case '':
break;
default:
}
case xxx
when xxx then
-- 处理语句
when xxx then
--
else
end case;
select ename,
case deptno
when 10 then
'综合部门'
when 20 then '技术部门'
when 30 then '市场部门'
else '其他部门'
end case from scott.emp;
循环语句(loop,while,for)
条件:循环初始值,循环结束之,改变值.
-- 循环 loop while for 循环起始值 循环结束条件 改变值
declare
i int := 0;
begin
loop
i := i + 1;
dbms_output.put_line(i);
exit when i = 10;
end loop;
end;
declare
i int := 0;
begin
while i < 101 loop
i := i + 1;
dbms_output.put_line(i);
end loop;
end;
declare
begin
for i in reverse 1..101 loop
dbms_output.put_line(i);
end loop;
end;
select emp.ename from emp;
-- 类型 存储更多的数据 %type %rowtype record(自定义类型) table varray
declare
v_name emp.ename%type;
begin
select emp.ename into v_name from emp where empno = 7369;
dbms_output.put_line(v_name);
end;
declare
v_name dept%rowtype;
begin
select * into v_name from dept where deptno = 10;
dbms_output.put_line(v_name.deptno || v_name.dname);
end;
declare
type r_name is record (
v_dname dept.dname%type,
v_loc dept.loc%type
);
rec_v r_name;
begin
select dname,loc into rec_v.v_dname,rec_v.v_loc from dept where deptno = 10;
dbms_output.put_line(rec_v.v_dname || rec_v.v_loc);
end;
select * from dept
异常处理
-- 异常处理
exception
when 异常 then
--
when 异常 then
when others then
-- 预定义异常
declare
v_name emp.ename%type;
begin
select to_number(ename) into v_name from emp where empno = &empno;
exception
when no_data_found then -- ora-01403
dbms_output.put_line('没有数据');
when invalid_number then -- ora-01403
dbms_output.put_line('转换异常');
end;
-- 非预定义异常 + 自定义异常
declare
myexception exception;
v_name emp.ename%type;
pragma exception_init(myexception,-30000);
begin
select ename into v_name from emp where empno = &empno;
if v_name = 'SMITH' then
raise myexception;
end if;
exception
when myexception then -- ora-30000
dbms_output.put_line('SMITH不能更改');
end;
select * from emp;
游标
-- 游标 如果你在pl/sql 操作多行数据 必须游标
-- 1定义游标 2.打开游标 3 提取数据 4关闭游标
declare
cursor cus_emp is select * from emp;
v_rowemp emp%rowtype;
v_count int;
begin
select count(*) into v_count from emp;
open cus_emp;
for i in 1..v_count
loop
fetch cus_emp into v_rowemp;
dbms_output.put_line(v_rowemp.empno || ' ' || v_rowemp.ename);
end loop;
end;
declare
cursor cus_emp is select empno,ename from emp;
type record_type is record (
v_empno emp.empno%type,
v_ename emp.ename%type
);
res record_type;
v_count int;
begin
select count(*) into v_count from emp;
open cus_emp;
for i in 1..v_count
loop
fetch cus_emp into res;
dbms_output.put_line(res.v_empno || ' ' || res.v_ename);
end loop;
close cus_emp;
end;
declare
cursor cus_emp(v_no emp.empno%type) is select empno,ename from emp where empno = v_no;
type record_type is record (
v_empno emp.empno%type,
v_ename emp.ename%type
);
res record_type;
v_count int;
i int := 0;
begin
open cus_emp(7369);
loop
fetch cus_emp into res;
exit when cus_emp%notfound;
dbms_output.put_line(res.v_empno || ' loop ' || res.v_ename);
end loop;
close cus_emp;
end;
select count(*) into v_count from emp;
open cus_emp(7369);
while i<v_count
loop
fetch cus_emp into res;
dbms_output.put_line(res.v_empno || ' while ' || res.v_ename);
i := i + 1;
end loop;
close cus_emp;
