plsql基础

语法：declare-->声明变量

begin-->执行部分

exception-->异常

end-->结束

/

最简单的程序：begin

null;

end;

输出语句：DBMS_output.put_line(‘****‘);

begin

dbms_output.put_line(‘hello world.‘);

end;

/

首次执行不输出结果，原因在于数据库默认是不显示输出结果的，需要设置。

set serveroutput on；--->sql*plus上执行---->显示输出结果

set serveroutput off；--->sql*plus上执行---->关闭输出结果显示

pl/sql developer上执行语句与上述语法无关，即都有结果输出。

定义变量：

declare v_num number；

declare v_num number;

begin

v_num := 30;

dbms_output.put_line(‘v_num的内容是‘||v_num);

end;

接收雇员编号，输出雇员姓名。

declare v_eno number;

v_ename varchar2(20);

begin

v_eno:=&empno;

select ename into v_ename from emp where empno=v_eno;

dbms_output.put_line(‘编号为‘||v_eno||‘的雇员姓名为‘||v_ename||‘.‘);

end;

变量的声明与赋值：

declare constant v_eno not null :=100;

使用%type定义变量类型：在pl/sql编程中如果想定义某一变量于指定列的类型相同，可以使用：表名称.字段名称%type

declare

v_eno emp.empno%type;

declare v_eno emp.empno%type;

v_ename emp.ename%type;

begin

v_eno:=&empno;

select ename into v_ename from emp where empno=v_eno;

dbms_output.put_line(‘编号为‘||v_eno||‘的雇员姓名为‘||v_ename||‘==============.‘);

end;

使用%rowtype声明变量类型：行类型

表名称%rowtype

declare v_dept dept%rowtype;

begin

select * into v_dept from dept where deptno= 10;

dbms_output.put_line(v_dept.deptno||‘,‘||v_dept.dname);

end;

注意：输出结果只能返回一行。

运算符：赋值=、连接||、关系>\<、<>\in、like等、逻辑运算符and、not、or

数据类型：

标量类型：数值型：number、pls_integer；

字符型：char和varchar2的区别：char：长度不够，自动用空格填充。

varchar2长度不够，不填充，oracle中只有varchar2类 型，如果用了varchar类型也默认是varchar2类型。

length（字符串）：字符串取长度函数

rowid和urowid：取地址类型。

日期型：date

sysdate：表示当前时间

to_char（date，‘yyyy-mm-dd’）:将日期改为字符串类型

declare

v_date1 date := sysdate;

v_date2 date := systimestamp;

v_date3 date := ‘15-3月-1990‘;

begin

dbms_output.put_line(‘日期数据‘||to_char（v_date1,‘yyyy-mm-dd hh24:mi:ss‘）);

dbms_output.put_line(‘日期数据‘||to_char（v_date2,‘yyyy-mm-dd hh24:mi:ss‘）);

dbms_output.put_line(‘日期数据‘||to_char（v_date3,‘yyyy-mm-dd hh24:mi:ss‘）);

end;

timestamp：该类型可以包含到毫秒

interval：1）interval year（3） to month ：=interval ‘19-11’ to month；年月间隔

2）interval day（3） to second：= interval ‘8 10:11:12.3373’to second；秒的间隔

布尔型变量：boolean：true、false

子类型：subtype

程序结构：

分支结构：if。。。else。。。end if；语句和case。。。when。。。then。。。end case；

if...end if语句

declare

v_a number;

begin

select count(empno) into v_a from emp;

if v_a>10 then

dbms_output.put_line(‘empno大于10条记录‘);

end if;

end;

如下：if .. else ..end if

declare

v_a number;

begin

select count(deptno) into v_a from dept;

if v_a>10 then

dbms_output.put_line(‘dept大于10条记录‘);

else

dbms_output.put_line(‘dept表记录小于10行‘);

end if;

end;

if..elsif..else..end if

declare

v_a number;

begin

select count(empno) into v_a from emp ;

if v_a>10 then

dbms_output.put_line(‘emp大于10条记录‘);

elsif v_a<10 then

dbms_output.put_line(‘emp表记录小于10行‘);

else

dbms_output.put_line(‘emp表记录等于10行‘);

end if;

end;

declare

v_a number;

begin

select count(empno) into v_a from emp where deptno=10;

if v_a>10 then

dbms_output.put_line(‘emp大于10条记录‘);

elsif v_a<10 then

dbms_output.put_line(‘emp表记录小于10行‘);

else

dbms_output.put_line(‘emp表记录等于10行‘);

end if;

end;

实例程序：

declare

v_a number;

v_b number;

begin

v_a := &empno;

select sal into v_b from emp where empno=v_a;

if v_b>3000 then

dbms_output.put_line(‘高工资‘);

elsif v_b>2000 then

dbms_output.put_line(‘中等工资‘);

else

dbms_output.put_line(‘低工资‘);

end if;

end;

注意：此处必须用v_b作为判断条件，即select语句需要into条件。

declare

v_bianhao number;

v_bumen number;

v_gongzi number;

begin

v_bianhao := &empno;

select deptno,sal into v_bumen,v_gongzi from emp where empno=v_bianhao;

if v_bumen=10 then

update emp set sal=v_gongzi*1.1 where deptno=v_bumen;

dbms_output.put_line(‘涨了工资10%‘);

elsif v_bumen=20 then

update emp set sal=v_gongzi*1.2 where deptno=v_bumen;

dbms_output.put_line(‘涨了工资20%‘);

elsif v_bumen=30 then

update emp set sal=v_gongzi*1.3 where deptno=v_bumen;

dbms_output.put_line(‘涨了工资30%‘);

end if;

end;

case语句：多条件判断语句

语法：case

when 表达式 then

执行语句块；

when表达式 then

执行语句块；

when表达式 then

执行语句块；

。。。。。

else

执行语句块；

end case；

declare

v_bianhao emp.empno%type;

v_job emp.job%type;

v_sal emp.sal%type;

begin

v_bianhao:=&empno;

select job,sal into v_job,v_sal from emp where empno=v_bianhao;

case v_job

when ‘CLERK‘ then

update emp set sal=v_sal*1.05;

dbms_output.put_line(‘clerk工资涨了5%‘);

when ‘SALESMAN‘ then

update emp set sal=v_sal*1.1;

dbms_output.put_line(‘salesman工资涨了10%‘);

when ‘MANAGER‘ then

update emp set sal=v_sal*1.2;

dbms_output.put_line(‘salesman工资涨了20%‘);

when ‘ANALYST‘ then

update emp set sal=v_sal*1.1;

dbms_output.put_line(‘salesman工资涨了10%‘);

when ‘PERSIDENT‘ then

dbms_output.put_line(‘工资不涨‘);

else

NULL;

end case;

end;

注意：程序中select语句只能返回一行。

循环语句：loop循环和for循环

loop语法：loop ------先执行后判断，至少执行一次

循环语句块；

exit when 循环结束条件；

循环结束条件修改；

end loop;

while ...loop 语法： ----------先判断在执行

while（循环结束条件） loop

程序执行块；

修改循环结束条件；

end loop；

for语法： --------明确知道循环次数

for 循环索引 in （reverse----反转循环：倒序循环） 循环下限。。。。。循环上限 loop

执行程序块；

end loop；

意见：当知道循环次数时，用for

但知道循环结束条件时，用loop

控制循环：exit和continue结束循环，一定要与if合用。

exit：直接跳出循环

continue：跳过不满足循环条件，继续执行其他。

附加：mod（v_i，2）=0 意识是偶数。

goto语句：无条件跳转指令，跳转到程序的某部分继续执行，不建议使用。

goto。。。。。。《goto语句跳转点》

内部程序块：oracle程序内部定义的包含declare。。。begin。。。exception。。。。end；的内部程序

异常处理：

编译时异常

运行时异常：用户只能解决此异常

sqlcode：获取异常代码

declare

v_chara varchar(1);

v_charb varchar(4) := ‘java‘;

begin

v_chara :=v_charb;

DBMS_OUTPUT.put_line(‘异常以后的语句不输出‘);

exception

when value_error then

DBMS_OUTPUT.put_line(‘赋值异常‘);

DBMS_OUTPUT.put_line(‘sqlcode=‘||sqlcode);

end;

/

输出结果:

赋值异常

sqlcode=-6502

例2：

declare

v_eno emp.empno%type;

v_ename emp.ename%type;

begin

v_eno:=&empno;

select ename into v_ename from emp where empno=v_eno;

DBMS_OUTPUT.put_line(‘编号为‘||v_eno||‘的雇员姓名为‘||v_ename);

exception

when no_data_found then

DBMS_OUTPUT.put_line(‘没有这个雇员。‘);

DBMS_OUTPUT.put_line(‘sqlcode=‘||sqlcode);

end;

例3：

declare

v_dno dept.deptno%type;

v_ename emp.ename%type;

begin

v_dno:=&deptno;

select ename into v_ename from emp where deptno=v_dno;

exception

when too_many_rows then

DBMS_OUTPUT.put_line(‘返回数据过多。‘);

DBMS_OUTPUT.put_line(‘sqlcode=‘||sqlcode);

end;

由于实际开发中异常的情况太多，我们不可能把每一个异常都记下来，所以我们可以用others代替各种异常类型。

sqlcode：返回异常代码。

sqlerrm：返回异常描述信息。

例4：

declare

v_dno dept.deptno%type;

v_ename emp.ename%type;

begin

v_dno:=&deptno;

select ename into v_ename from emp where deptno=v_dno;

exception

when others then

DBMS_OUTPUT.put_line(‘sqlcode=‘||sqlcode);

DBMS_OUTPUT.put_line(‘sqlerrm=‘||sqlerrm);

end;

以上异常都是由系统自动抛出来的，而在实际工作中，我们也可以人为的抛出异常。

用户自定义异常

异常依然可以用others代替。

declare

v_number number;

v_myexp exception;

pragma exception_init(v_myexp,-20789);

begin

v_number :=&inputnumber;

if v_number>30 and v_number <100 then

raise v_myexp;

end if;

exception

when v_myexp then

DBMS_OUTPUT.put_line(‘sqlcode=‘||sqlcode);

DBMS_OUTPUT.put_line(‘sqlerrm=‘||sqlerrm);

DBMS_OUTPUT.put_line(‘输入的数据不在正常范围内。‘);

end;

输出结果：

sqlcode=-20789

sqlerrm=ORA-20789:

输入的数据不在正常范围内。

自己定义错误信息

declare

v_number number;

v_myexp exception;

pragma exception_init(v_myexp,-20789);

begin

v_number :=&inputnumber;

if v_number>30 and v_number <100 then

raise_application_error(-20789,‘输入数字不能在30到100之间。‘);

end if;

exception

when v_myexp then

DBMS_OUTPUT.put_line(‘sqlcode=‘||sqlcode);

DBMS_OUTPUT.put_line(‘sqlerrm=‘||sqlerrm);

DBMS_OUTPUT.put_line(‘输入的数据不在正常范围内。‘);

end;

输出结果：

sqlcode=-20789

sqlerrm=ORA-20789: 输入数字不能在30到100之间。

输入的数据不在正常范围内。

pragma exception_init(x，y);

raise_application_error(x，y);