首页 > 代码库 > plsql基础
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);
plsql基础