oracle plsql 常用命令

1 查看oracle的版本信息

（1）用客户端连接到数据库，执行select * from v$instance
查看version项

（2）select * from product_component_version

（3）或查询V$VERSION查看组件级信息

2 merge into


1，变量类型

binary_integer
number
date
boolean
varchar2
char


2 声明变量
declare
v_num number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then
dbms_output.put_line('error');
end ;

/

3、dbms_output.put_line('error'); 不能打印boolean false true null(默认)

4、rowtype
declare
v_temp lifztest3%rowtype;
begin
v_temp.xh := 'rp';
dbms_output.put_line(v_temp.xh);
end;
/


5、type
declare
v_temp lifztest3.xh%type;
begin
v_temp.xh := 'rp';
dbms_output.put_line(v_temp.xh);
end;
/

6
declare
i binary_integer :=1 ;
begin
loop
dbms_output.put_line(i);
i := i+1;
exit when ( i>=11 );
end loop;
end;
/

7 SQLCODE
8 SQLERRM

9游标

declare
cursor c is
select * from lifztest3;
v_xh c%rowtype;
begin
open c;
loop
fetch c into v_xh;
exit when ( c%notfound );
if(v_xh.xh in ('1','2')) then
dbms_output.put_line(v_xh.xh);
else
dbms_output.put_line('aaa'||v_xh.xh);
end if;
end loop;
close c;
end;

10 for 循环游标

declare
cursor c is
select * from lifztest3;
begin
for v_xh in c loop
dbms_output.put_line(v_xh.xh);
end loop;
end;
/


11 游标更新数据

declare
cursor c is
select * from lifztest3 for update;
begin
for v_xh in c loop
if(v_xh.xh = '1001') then
update lifztest3 set xh = '00001' where current of c;
else
update lifztest3 set xh = '1111' where current of c;
end if;
end loop;
commit;
end;
/

12 elsif

declare
cursor c is
select * from lifztest3 for update;
begin
for v_xh in c loop
if(v_xh.xh = '1001') then
update lifztest3 set xh = '00001' where current of c;
elsif(v_xh.xh <> '1111') then
update lifztest3 set xh = '100' where current of c;
end if;
end loop;
commit;
end;
/


13 存储过程

create or replace procedure lifzprot
is
cursor c is
select * from lifztest3 for update;
begin
for v_xh in c loop
if(v_xh.xh = '1001') then
update lifztest3 set xh = '00001' where current of c;
elsif(v_xh.xh <> '1111') then
update lifztest3 set xh = '111' where current of c;
end if;
end loop;
commit;
end;
/

14计算一个表字段数


select a.tname,count(*) field_count from tab a,user_col_comments b
where a.tabtype='TABLE' and a.tname=b.table_name and a.tname='表名' group by a.tname