oracle sql exact,Oracle中的PL/SQL介绍详解

PL/SQL(Procedure Language/SQL)

PLSQL是Oracle对sql语言的过程化扩展

指在SQL命令语言中增加了过程处理语句(如分支、循环等)，使SQL语言具有过程处理能力。

PL/SQL程序结构

declare

说明部分 (变量说明，光标申明，例外说明 〕

begin

语句序列 (DML语句〕…

exception

例外处理语句

End;

/

PL/SQL 的hello word

set serveroutput on

declare

--变量的说明

begin

--程序

dbms_output.put_line('Hello World');

end;

/

PL/SQL 中的变量和常量

说明变量 (char, varchar2, date, number, boolean, long)

varl vhar(12) 说明变量名、数据类型和长度后用分号结束说明语句。

psal emp.sal%type; 引用型变量，即my_name的类型与emp表中ename列的类型一样

emp_rec emp%rowtype; 记录型变量

--查询并打印7839的姓名和薪水

set serveroutput on

declare

--定义引用型变量

pename emp.ename%type;

psal emp.sal%type;

begin

--得到7839的姓名和薪水

select ename,sal into pename,psal from emp where empno=7839;

--打印

dbms_output.put_line(pename||'的薪水是'||psal);

end;

/

----查询并打印7839的姓名和薪水

set serveroutput on

declare

-- 定义记录型变量 ，代表一行

emp_rec emp%rowtype;

begin

select * into emp_rec from emp where empno=7839;

dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);

end;

/

PL/SQL中的IF语句

IF 条件 THEN 语句1;

语句2;

END IF;

IF 条件 THEN 语句序列1；

ESLE 语句序列 2；

END IF；

IF 条件 THEN 语句;

ELSIF 语句 THEN 语句;

ELSE 语句;

END IF;

--判断用户从键盘上输入的数字

set serveroutput on

--键盘输入

--num 地址值，在该地址上，保存了键盘输入的值

accept num prompt '请输入一个数字';

declare

--定义变量保存输入的数字

pnum number := #

begin

--判断

if pnum = 0 then dbms_output.put_line('您输入的是0');

elsif pnum = 1 then dbms_output.put_line('您输入的是1');

elsif pnum = 2 then dbms_output.put_line('您输入的是2');

else dbms_output.put_line('其他数字');

end if;

end;

/

PL/SQL中的循环语句

Loop

EXIT [when 条件];

……

End loop

FOR I IN 1 . . 3 LOOP

语句序列 ;

END LOOP ;

--打印1~10

set serveroutput on

declare

pnum number := 1;

begin

loop

--退出条件

exit when pnum > 10;

dbms_output.put_line(pnum);

--加一

pnum := pnum + 1;

end loop;

end;

/

光标 Cursor

说明光标语法：

CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]...)]

IS SELECT 语句;

用于存储一个查询返回的多行数据

打开光标： open c1; (打开光标执行查询)

取一行光标的值：fetch c1 into pjob; (取一行到变量中)

关闭光标： close c1;(关闭游标释放资源)

注意： 上面的pjob必须与emp表中的job列类型一致：

定义：pjob emp.empjob%type;

--使用游标查询员工姓名和工资，并打印

set serveroutput on

/*

1. 光标的属性

%isopen 是否打开

%rowcount 记录数

%notfound 没有记录

2.

SQL> show parameters cursor

NAME TYPE VALUE

------------------------------------ ----------- ---------

cursor_sharing string EXACT

cursor_space_for_time boolean FALSE

open_cursors integer 300

session_cached_cursors integer 20

修改300

alter system set open_cursors=400 scope=both;

*/

declare

--定义光标

cursor cemp is select ename,sal from emp;

pename emp.ename%type;

psal emp.sal%type;

begin

open cemp;

loop

--取一条记录

fetch cemp into pename,psal;

--退出: 光标中没有值

exit when cemp%notfound;

--打印

dbms_output.put_line(pename||'的薪水是'||psal);

end loop;

close cemp;

end;

/

带参数的光标

--查询某个部门中员工的姓名

set serveroutput on

declare

--定义带参数的光标

cursor cemp(dno number) is select ename from emp where deptno=dno;

pename emp.ename%type;

begin

open cemp(20);

loop

fetch cemp into pename;

--退出

exit when cemp%notfound;

--打印

dbms_output.put_line(pename);

end loop;

close cemp;

end;

/

Oracle的异常处理

1.系统定义例外

No_data_found (没有找到数据)

Too_many_rows (select …into语句匹配多个行)

Zero_Divide ( 被零除)

Value_error (算术或转换错误)

Timeout_on_resource (在等待资源时发生超时)

--被0除

set serveroutput on

declare

pnum number;

begin

pnum := 1/0;

exception

when Zero_Divide then dbms_output.put_line('1:0不能做被除数');

dbms_output.put_line('2:0不能做被除数');

when Value_error then dbms_output.put_line('算术或转换错误');

when others then dbms_output.put_line('其他例外');

end;

/

2.用户定义的例外

--查询50号部门的员工姓名

set serveroutput on

declare

--定义光标代表50号部门的员工

cursor cemp is select ename from emp where deptno=50;

pename emp.ename%type;

--自定义例外

no_emp_found exception;

begin

open cemp;

--取一个员工

fetch cemp into pename;

if cemp%notfound then

--抛出例外

raise no_emp_found;

end if;

--如果产生例外，自动关闭

close cemp;

exception

when no_emp_found then dbms_output.put_line('没有找到员工');

when others then dbms_output.put_line('其他例外');

end;

/

demo

--给员工涨工资 总裁1000 经理800 其他400

set serveroutput on

declare

--定义光标

cursor cemp is select empno,empjob from emp;

pempno emp.empno%type;

pjob emp.empjob%type;

begin

rollback;

open cemp;

loop

--取一个员工

fetch cemp into pempno,pjob;

exit when cemp%notfound;

--判断职位

if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;

elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;

else update emp set sal=sal+400 where empno=pempno;

end if;

end loop;

close cemp;

--提交

commit;

dbms_output.put_line('完成');

end;

/