PL/SQL实例
来源:互联网 发布:心事谁人知歌词意思 编辑:程序博客网 时间:2024/06/04 19:05
DECLARE
声明部分
BEGIN
编写主题
EXCEPTION
捕获异常
END;
//结果不显示时 set serveroutput on
SSH模式下:打开输出
SQL> set serveroutput on
SQL语句最后加上斜杠 /
例1:
DECLARE
i number;
BEGIN
i:=30;
dbms_output.put_line(i);
end;
例2:
DECLARE
i number;
BEGIN
i:=1/0;
exception
when zero_divide THEN
dbms_output.put_line('error');
end;
例3Loop 循环(do…while):
DECLARE
cou number;
BEGIN
cou:=1;
loop
dbms_output.put_line(cou);
exit when cou>10;
cou:=cou+1;
end loop;
end;
例4(while 循环):
DECLARE
x number;
BEGIN
x:=1;
while(x<5)loop
dbms_output.put_line(x);
x:=x+1;
end loop;
end;
例5(for 循环):
DECLARE
x number;
BEGIN
FOR x IN 1..7LOOP
DBMS_OUTPUT.put_line(x) ;
end loop;
end;
例6(IF 语句):
DECLARE
x number;
BEGIN
x:=3;
if x>2 THEN
dbms_output.put_line(x);
end if;
end;
DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
ELSE
dbms_output.put_line('fail');
end IF;
end;
例8(IF…ELSIF…ELSE 语句):
DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
elsif x>3 THEN
dbms_output.put_line('less');
else
dbms_output.put_line('fail');
end if;
end;
例9
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('big');
else
dbms_output.put_line('small');
end if;
end;
/
例10
declare
x number;
en varchar2(30);
begin
x:=&no;
select ename into en from emp where empno=x;
dbms_output.put_line(en);
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例11
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('>10');
elsif x<5 then
dbms_output.put_line('<5');
else
dbms_output.put_line('error');
end if;
end;
/
例12
declare
x number;
s number;
begin
x:=&no;
select sal into s from emp where empno=x;
if s>3000 then
dbms_output.put_line('high');
elsif s>2000 then
dbms_output.put_line('mid');
else
dbms_output.put_line('low');
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例13
declare
x number;
s number;
d number;
begin
x:=&no;
select deptno,sal into d,s from emp where empno=x;
if d=10 then
if s*1.1>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.1 where empno=x;
end if;
elsif d=20 then
if s*1.2>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.2 where empno=x;
end if;
elsif d=30 then
if s*1.3>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.3 where empno=x;
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
声明部分
BEGIN
编写主题
EXCEPTION
捕获异常
END;
//结果不显示时 set serveroutput on
SSH模式下:打开输出
SQL> set serveroutput on
SQL语句最后加上斜杠 /
例1:
DECLARE
i number;
BEGIN
i:=30;
dbms_output.put_line(i);
end;
例2:
DECLARE
i number;
BEGIN
i:=1/0;
exception
when zero_divide THEN
dbms_output.put_line('error');
end;
例3Loop 循环(do…while):
DECLARE
cou number;
BEGIN
cou:=1;
loop
dbms_output.put_line(cou);
exit when cou>10;
cou:=cou+1;
end loop;
end;
例4(while 循环):
DECLARE
x number;
BEGIN
x:=1;
while(x<5)loop
dbms_output.put_line(x);
x:=x+1;
end loop;
end;
例5(for 循环):
DECLARE
x number;
BEGIN
FOR x IN 1..7LOOP
DBMS_OUTPUT.put_line(x) ;
end loop;
end;
例6(IF 语句):
DECLARE
x number;
BEGIN
x:=3;
if x>2 THEN
dbms_output.put_line(x);
end if;
end;
/
例7(IF…ELSE 语句):DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
ELSE
dbms_output.put_line('fail');
end IF;
end;
例8(IF…ELSIF…ELSE 语句):
DECLARE
x number;
BEGIN
x:=1;
if x>5 THEN
dbms_output.put_line(x);
elsif x>3 THEN
dbms_output.put_line('less');
else
dbms_output.put_line('fail');
end if;
end;
例9
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('big');
else
dbms_output.put_line('small');
end if;
end;
/
例10
declare
x number;
en varchar2(30);
begin
x:=&no;
select ename into en from emp where empno=x;
dbms_output.put_line(en);
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例11
declare
x number;
begin
x:=&no;
if x>10 then
dbms_output.put_line('>10');
elsif x<5 then
dbms_output.put_line('<5');
else
dbms_output.put_line('error');
end if;
end;
/
例12
declare
x number;
s number;
begin
x:=&no;
select sal into s from emp where empno=x;
if s>3000 then
dbms_output.put_line('high');
elsif s>2000 then
dbms_output.put_line('mid');
else
dbms_output.put_line('low');
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
例13
declare
x number;
s number;
d number;
begin
x:=&no;
select deptno,sal into d,s from emp where empno=x;
if d=10 then
if s*1.1>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.1 where empno=x;
end if;
elsif d=20 then
if s*1.2>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.2 where empno=x;
end if;
elsif d=30 then
if s*1.3>5000 then
update emp set sal=5000 where empno=x;
else
update emp set sal=s*1.3 where empno=x;
end if;
end if;
exception
when no_data_found then
dbms_output.put_line('No Data');
end;
/
- oracle pl/sql实例
- pl/sql 实例
- pl/sql 初学实例
- PL/SQL实例分析
- PL/SQL实例
- pl/sql 块 实例
- PL/SQL 实例总结
- oracle pl/sql实例练习
- oracle pl/sql 完整实例
- oracle pl/sql实例练习
- pl/sql 存储过程实例
- pl/sql 游标变量实例
- oracle pl/sql实例练习
- ORACLE PL/SQL实例精解笔记
- ORACLE PL/SQL实例精解笔记
- PL/SQL学习代码实例1
- 从PL/SQL调用web services实例
- Pl sql case语句应用实例
- NHibernate 优秀博客专题
- Glib编程
- libmysqlclient.so.16: cannot open shared object file: No such file or directory
- C++ 全局对象 全局变量和局部变量 静态变量
- Windows权限
- PL/SQL实例
- 封装的IATHOOK类的使用
- 24种榨干时间精力的“生命水蛭”:替工作狂卖命
- CSDN第一次写博
- 回顾Java给我们带来什么
- Matlab2012调用opencv2.3.1时的配置
- 判断单链表是否存在环,判断两个链表是否相交问题详解
- 用代码操纵剪切板
- STM32 串口发数据丢失问题