oracle常用命令举例
- 基本语法
- %type用法【%type用法，提取%type所在字段的类型】
declare
myid dept.id%type;
myname dept.name%type;
begin
select id,name into myid,myname from dept;
dbms_output.put_line(myid);
dbms_output.put_line(myname);
end;
/
- %rowtype用法【提取%rowtype所在的字段的类型】
declare
type type_dept is table of dept%rowtype;
index by binary_integer;
tb type_dept;
begin
tb(1).id:='001';
tb(2).id:='001';
dbms_output.put_line(tb.COUNT);
end;
/
- TYPE用法【相当于结构体】
declare
lv_order_date Date :=sysdate;
lv_last_txt varchar2(5) default '001';
lv_last varchar2(10) not null:='us';
TYPE type_test is rocord(
myid dept.id%type,
myname dept.name%type
);
rec type_test;
begin
lv_order_date :=sysdate;
dbms_output.out_line(lv_last);
select id , name into rec from dept;
dbms_output.put_line(rec.myid);
dbms_output.put_line(rec.myname);
end;
/
- 游标的使用
declare
g_id char(10):='002';
find_not char(1):='N';
cursor cur is
select * from dept; [cur 指向表]
TYPE type_dept is record(
myid dept.id%type,
myname dept.name%type,
myaddr dept.addr%type
);
rect type_dept;
begin
open cur;
loop
fetch cur into rect; 【提取cur指向的记录到rect结构中】
exit when cur%NOTFOUND;
if rect.myid=g_id then
find_not :='Y';
dbms_output.put_line('Find it!');
dbms_output.put_line('DEPT ID:'||rect.myid);
dbms_output.put_line('NAME:'||rect.myname);
dbms_output.put_line('ADDR:'||rect.myaddr);
end if;
end loop;
close cur;
if find_out='N' then
dbms_output.put_line('no record');
end if;
end;
/
- for循环
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
/
- loop循环
declare
v number:=1;
begin
loop
dbms_output.put_line(v);
exit when v>5;
v:=v+1;
end loop;
end;
/
- while循环
v number:=1;
begin
while v<5 loop
dbms_output.put_line(v);
v:=v+1;
end loop;
end;
/
- if/else的用法
declare
v1 number:=90;
begin
if v1=10 then
dbms_output.put_line('v1 is 10');
elsif v1=20 then
dbms_output.put_line('v1 is 20');
else
dbms_output.put_line('v1 is others');
end if;
end;
/
- case的用法
v number:=10;
begin
case :v
when 10 then
dbms_output.put_line('v is 10');
when 20 then
dbms_output.put_line('v is 20');
else
dbms_output.put_line('v is not 10 and 20');
end case;
end;
/
- 错误定义
- error的设定
declare
v1 number:=90;
begin
if v1=10 then
dbms_output.put_line('v1 is 10');
elsif v1=20 then
dbms_output.put_line('v1 is 20');
else goto err;
dbms_output.put_line('normal end');
<<err>>
dbms_output.put_line('error found');
end if;
end;
/
- exception用法
ex Exception;
begin
update dept set name='Edison'
where id='100';
if SQL%NOTFOUND then
Rais ex;
end if;
Exception
when ex then
dbms_output.put_line('update failed');
end;
/
declare
type rc_dept is record(
myid dept.id%type,
myname dept.name%type,
myaddr dept.addr%type
);
tb rc_dept;
begin
select id,name,addr into tb from dept where id=:gb_id;
dbms_output.put_line('id:'||tb.myid);
dbms_output.put_line('name:'||tb.myname);
dbms_output.put_line('addr:'||tb.myaddr);
exception
when NO_DATA_FOUND then
dbms_output.put_line('no rocord is found');
when TOO_MANY_ROWS then
dbms_output.put_line('too many rows are selected);
when OTHERS then
dbms_output.put_line('undefine error');
dbms_output.put_line('error code:'||SQLCODE);
dbms_output.put_line('error message:'||SQLERRM);
end;
/
declare
type rc_dept is record(
myid dept.id%type,
myname dept.name%type,
myaddr dept.addr%type
);
tb rc_dept;
begin
begin
select id ,name ,addr into tb from dept where id=:gb_id;
dbms_output.put_line('id:'||tb.myid);
dbms_output.put_line('name:'||tb.myname);
dbms_output.put_line('addr:'||tb.myaddr);
exception
when NO_DATA_FOUND then
dbms_output_line('no record is found,occur in inner');
end;
exception
when TOO_MANY_ROWS then
dbms_output.put_line('too many rows are selected ,occur in outer');
when OTHERS then
dbms_output.put_line('undefine error');
dbms_output.put_line('error code:'||SQLCODE);
dbms_output.put_line('error message:'||SQLMESSAGE);
end;
/
- 存储过程及函数
- procedure的建立和调用
create or replace procedure test_sp(
test in number,
outtest out number,
) is
begin
if test>10 then
printsomething('test is over 10!');
else
begin
outtest:=test;
printsomething(outtest);
end;
end if;
end;
/
create or replace procedure printsomething(
print in number
)is
begin
dbms_output.put_line(print);
end;
/
create or replace procedure printsomething(
print in char
)is
begin
dbms_output.put_line(print);
end;
/
- function的建立和调用
is
begin
if t>10 then
dbms_outpu.put_line(t);
elsif t<10 then
dbms_output.put_line(t);
end if;
return t;
end;
/
- 参数的调用【in模式为按址调用，out模式为按值调用。NOCPOY强行转成按址调用】
p_in in number,
p_out in out nocopy number
)is
begin
p_out:=5;
if p_in=1 then
raise no_data_found;
end if;
end;
/
create or replace procedure run_nocopy_sp
is
lv_test_num number
begin
lv_test_num:=1;
test_nocopy_sp(1,lv_test_num);
exception
when other then
dbms_output.put_line('error happened'||lv_test_num);
end;
- 软件包及封装
- 软件包（package）的建立和调用【包含了函数的重载】
create or replace package test_package
is
procedure test_sp(test in number ,outtest out number);
procedure printsomething
(print in number);
procedure printsomething
(print in number);
procedure printsomething
(print int char);
function test
(t in number) return number;
end;
/
软件包体的建立
create or replace package body test_package
is
procedure test_sp
(test in number,outtest out number)
is
begin
if test>10 then
printsomething('test is over 10!!');
else
begin
outtest:=test;
printsomething(test);
end;
end if;
end test_sp;
procedure printsomething
(print in number)
is
begin
dbms_output.put_line(print);
end printsomething;
procedure printsomething
(print in char)
is
begin
dbms_output.put_line(print);
end printsomething;
function test(t in number) return numnber
is
begin
if t>10 then
dbms_output.put_line(t);
elsif t<10 then
dbms_output.put_line(t);
end if;
return t
end test;
end;
/
- 软件包的全局结构
create or replace package test_global
is
global_v number(3):=0;
procedure setValue(p1 in number);
end;
/
create or repalce package body test_global
is
procedure setValue(p1 in number)
is
begin
global_v:=p1;
dbms_output.put_line(global_v);
end setValue;
end;
/
- 封装函数的纯度
- 查看源代码及建立用户、用户的权限
- 源代码的查看
- 建立用户及登录
create user username_huazi identified by mima_hahaha;
grant create session to username_huazi;
- 授予权限和权限回收
grant all on test_package to username_huazi;
grant execute on test_package to username_huazi;
revoke all on test_package from username_huazi;
- 依赖
- 直接依赖
- 查看依赖
select referenced_name,referenced_type from user_dependencies where name='TEST_DEPENDENCY';
- 包之间调用
- 触发器
- 建立简单的触发器
- 触发器分类
- 稍复杂的触发器
- 条件谓词
- 触发器中不可使用commit
- 系统触发器举例（LOGON）
- instead of触发器
扫一扫
1918
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
