select * from v$version --查看版本
-----------------------------------------------------------------------------------
create table tempppp
(exception number(1)
)
insert into tempppp values(1)
select "EXCEPTION" from tempppp
select "exception" from tempppp --failed
引征区分大小写，数据字典里都为大写
select exception from tempppp
insert into tempppp values('2')
-----------------------------------------------------------------------------------
<<ppp>>
declare
Orgid number(19):=-1;--变量名不要和表列名相同
v_tmp number(19);
begin
select count(orgid) into v_tmp from organization where
orgid=Orgid;
Dbms_Output.put_line(v_tmp);
select count(orgid) into v_tmp from organization where
orgid=ppp.Orgid;
end;
--------------------------------------------------------------------------
declare
inxx varchar(2):='N ';--都为定长采用填充空格式
--inxx varchar(2):='N ';--有一个为变长采用非填充空格式
v_tmp number(19);
begin
select count(1) into v_tmp from dcperioddetail where inuse=inxx;
Dbms_Output.put_line(v_tmp);
end;
----------------------------------------------------------------------------
--伪列
select productseq.nextval from dual
select productseq.currval from dual
--只有发出至少一条nextval才能在会话中访问curval
select rowid from product
select rowidtochar(rowid) from product
select prodid from product where rownum<3
SELECT contactid ,level
FROM contact
--where contactid=100000020
START WITH contactid = (
select b.contactid
from usr b
where b.userid=1)
CONNECT BY parentcontactid = PRIOR contactid
------------------------------------------------------------------------------------
--常用函数
select upper(prodcode) from product where prodcode like 'qs040089'
--substr
select substr('qs040089',0) from dual
select substr('qs040089',1) from dual
select substr('qs040089',2) from dual
select substr('qs040089',-3) from dual
select substr('qs040089',1,1.5) from dual
--instr
select instr('qs040089','04') from dual
select instr('qs040089','04',4) from dual
select instr('qs040089','04',-1) from dual
--length
select length('qs040089') from dual
--decode
select decode(prodcode,'qs040089',1,2) from product where prodcode like
'qs040089'
--trunc
select trunc(datelastupdated),datelastupdated from product where
prodcode like 'qs040089'
--round
select round(datelastupdated),datelastupdated from product where
prodcode like 'qs040089'
--to_char
select to_char(sysdate,'DD-MM-YY HH24:MI:SS') from dual
--to_date
declare
v_CurrentDate DATE;
begin
v_CurrentDate :=to_date('11 07, 1973','MM DD, YYYY');
Dbms_Output.put_line(v_CurrentDate);
end;
--nullif
select nullif(1,12) from dual
select nullif(12,1) from dual
select nullif(12,12) from dual
--nvl
select nvl(2,1) from dual
select nvl(null,1) from dual
select user from dual
select USERENV('TERMINAL'),USERENV('LANGUAGE') from dual
--CURSOR
游标----------------------------------------------------------------
--sql
游标------------------------------------------------------------------
begin
update product
set prodcode='8800000000000'
where prodcode='qs04008911111';
if SQL%NOTFOUND THEN
DBMS_OUTPUT.put_line(11111);
end if;
end;
begin
update product
set prodcode='8800000000000'
where prodcode='qs04008911111';
if SQL%ROWCOUNT=0 THEN
DBMS_OUTPUT.put_line(11111);
end if;
end;
DECLARE
v_tmp product%rowtype;
begin
select * into v_tmp from product where prodid=-1;
--SELECT INTO 未找到会报错！
if SQL%notfound THEN
DBMS_OUTPUT.put_line(222222);
end if;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.put_line(3333333);
end;
-- for update
select * from productlang a for update of productlangid
select * from productlang a for update
select * from productlang a for update nowait
select * from productlang a for update wait 10
declare
cursor c_allproduct is
select * from product
for update;--for update不能在循环中使用commit
v_oneprd c_allproduct%rowtype;
begin
open c_allproduct;
fetch c_allproduct into v_oneprd;
commit;--for update不能在循环中使用commit
fetch c_allproduct into v_oneprd;
end;
declare
cursor c_allproduct is
select * from product ;
v_oneprd c_allproduct%rowtype;
begin
open c_allproduct;
loop
fetch c_allproduct into v_oneprd;
exit when c_allproduct%notfound;
commit;
end loop;
close c_allproduct;
--close c_allproduct;-- INVALID_CURSOR
end;
declare
type t_ddd is ref cursor return product%rowtype;
v_CursorVar t_ddd;
begin
open v_CursorVar for select * from
organization;--类型与游标变量返回类型不同
close v_CursorVar;
end;
--错误处理--------------------------------------------------------------------------
declare v_NumStudents number;
begin
select count(1)
into v_NumStudents
from sstudents;--编译错误
end;
declare
x number:=1;
y number:=2;
z number:=3;
begin
y:=x/0;--运行时异常
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
declare
type t_NumberTableType is table of number
index by binary_integer;
v_NumberTable t_NumberTableType;
v_temp number;
begin
v_temp:=v_NumberTable(1);--NO_DATA_FOUND
end;
DECLARE
v_TempVar varchar2(2);
begin
v_TempVar:='111';--NUMERIC OR VALUE ERROR
end;
declare
e_TooManyStudents exception;--自定义异常
v_currentStudents number(4);
v_Max number(4);
begin
select 10,count(1) into v_Max,v_currentStudents from product ;
if v_Max<v_currentStudents then
raise e_TooManyStudents;
end if;
exception
when e_TooManyStudents then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
--7.1.3
declare
v_xxx varchar2(200);
begin
v_xxx :=sqlerrm(1);
dbms_output.put_line('sqlerrm: '''||sqlerrm||'''');
dbms_output.put_line('sqlerrm(0): '''||sqlerrm(0)||'''');
dbms_output.put_line('sqlerrm(100): '''||sqlerrm(100)||'''');
dbms_output.put_line('sqlerrm(-1): '''||sqlerrm(-1)||'''');
dbms_output.put_line('sqlerrm(-54): '''||sqlerrm(-54)||'''');
dbms_output.put_line('sqlerrm(10): '''||sqlerrm(10)||'''');
end;
--7.1.4
declare
e_missingNull exception;
pragma exception_init(e_missingNull,-1400); --编译指示
begin
insert into dictionary(dictionaryid) values(null);
exception
when e_missingNull then
dbms_output.put_line(sqlcode||' '||sqlerrm);--不显示"user-defined
exception"
end;
--7.1.5
declare
e_TooManyStudents exception;--自定义异常
v_currentStudents number(4);
v_Max number(4);
begin
select 10,count(1) into v_Max,v_currentStudents from product ;
if v_Max<v_currentStudents then
--raise e_TooManyStudents;
raise_application_error(-20001,'long long ago there was an
idiot!');
end if;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
--delete jjl;
--commit;
--raise;
end;
begin
declare
e_TooManyStudents exception;--自定义异常
begin
raise e_TooManyStudents;
end;
exception
when e_TooManyStudents then --自定义异常超出作用域
raise;
end;
declare
v_Tmp number(6):='ABC';--声明中的错误直接传到最外层
begin
null;--begin后面一定要有东西
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
begin
declare
v_Tmp number(6):='ABC';--声明中的错误直接传到最外层
begin
null;--begin后面一定要有东西
exception
when others then
dbms_output.put_line('2222');
end;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
--集合--------------------------------------------------------------------------------
declare
type NumberTab is table of number index by binary_integer;
v_Numbers NumberTab;
begin
for v_Count in 1..10 loop
v_Numbers(v_Count):=v_Count;
end loop;
for v_Count in 1..10 loop
dbms_output.put_line('number['||to_char(v_count)||']='||'
'||v_Numbers(v_Count));
end loop;
dbms_output.put_line('number['||to_char(11)||']='||'
'||v_Numbers(11));
exception
when no_data_found then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
declare
type ProdTab is table of product%rowtype index by binary_integer;
v_Prods ProdTab;
begin
if v_Prods is null then
dbms_output.put_line('v_Prods is null');
end if;
select *
into v_Prods(2000001160)
from product
where prodid=2000001160;
dbms_output.put_line('the code of prod['||to_char(2000001160)||']='||'
'||v_Prods(2000001160).prodcode);
exception
when no_data_found then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
--嵌套表构造器初始化
declare
type NumberTab is table of number;
v_Tab1 NumberTab:=NumberTab();
v_Tab2 NumberTab:=NumberTab(2);
v_Tab3 NumberTab;
begin
if v_Tab1 is null then
dbms_output.put_line('v_Tab1 is Null');
else
dbms_output.put_line('v_Tab1 is not Null');
end if;
--v_Tab1(1):=1;--wrong 元素不存在 subscript beyond count
if v_Tab2 is null then
dbms_output.put_line('v_Tab2 is Null');
else
dbms_output.put_line('v_Tab2 is not Null ' || v_Tab2(1));
end if;
if v_Tab3 is null then
dbms_output.put_line('v_Tab3 is Null');
else
dbms_output.put_line('v_Tab3 is not Null');
end if;
end;
declare
type Numbers is array(20) of number(3);--可变数组
v_NullList Numbers;
v_List1 Numbers:=Numbers(1,2);--数组大小由构造器决定
v_List2 Numbers:=Numbers(null);
begin
if v_NullList is null then
dbms_output.put_line('v_NullList is Null');
end if;
if v_List1(1) is null then
dbms_output.put_line('v_List1(1) is Null');
end if;
if v_List2(1) is null then
dbms_output.put_line('v_List2(1) is Null');
end if;
--v_List1(3):=1;-- subscript beyond count 数组大小由构造器决定
v_List1.extend;
v_List1(3):=1;
end;
--多层集合
declare
type t_Numbers is table of number index by binary_integer;
type t_MultiNumbers is table of t_Numbers index by binary_integer;
type t_MultiArray is varray(10) of t_Numbers;
type t_MultiNested is table of t_Numbers;
v_MultiNumbers t_MultiNumbers;
begin
v_MultiNumbers(1)(1):=1;
end;
-- 过程，函数，包
create or replace procedure ModeTest(p_InParam in number,p_OutParam out
number
,p_InOutParam in out number)is
v_localVariable number :=0;
begin
dbms_output.put_line('Inside ModeTest');
if(p_InParam is null) then
dbms_output.put_line('p_InParam is null');
else
dbms_output.put_line('p_InParam is '||p_InParam);
end if;
if(p_OutParam is null) then
dbms_output.put_line('p_OutParam is null');
else
dbms_output.put_line('p_OutParam is '||p_OutParam);
end if;
if(p_InOutParam is null) then
dbms_output.put_line('p_InOutParam is null');
else
dbms_output.put_line('p_InOutParam is '||p_InOutParam);
end if;
v_localVariable := p_InParam; --legal
--p_InParam:=7;--illegal
p_OutParam:=7;
v_localVariable:=p_OutParam;--possibly illegal,illegal prior to
7.3.4
v_localVariable:=p_InOutParam;
p_InOutParam:=8;
dbms_output.put_line('at the end of ModeTest');
if(p_InParam is null) then
dbms_output.put_line('p_InParam is null');
else
dbms_output.put_line('p_InParam is '||p_InParam);
end if;
if(p_OutParam is null) then
dbms_output.put_line('p_OutParam is null');
else
dbms_output.put_line('p_OutParam is '||p_OutParam);
end if;
if(p_InOutParam is null) then
dbms_output.put_line('p_InOutParam is null');
else
dbms_output.put_line('p_InOutParam is '||p_InOutParam);
end if;
end ModeTest;
declare
v_a number :=1;
v_b number :=2;
begin
ModeTest(1,v_a,v_b);
end;
drop procedure ModeTest;
-----------------------
create or replace procedure ParamLength(p_param1 in out varchar2,
p_param2 in out number)is
begin
p_param1:='abcdefghijklmno';
p_param2:=12.3;
end;
declare
v_a varchar(40);
--v_a varchar(10);--形参约束从实际参数获得
v_b number(7,3);
begin
ParamLength(v_a,v_b);
end;
drop procedure ParamLength;
-------------------------
create or replace procedure RaiseError(
p_Raise in boolean,
p_ParamA out number)as
begin
p_ParamA:=7;
if p_Raise then
raise dup_val_on_index;--当存储过程发生错误时,out
inout形式参数值不会返回给实际参数
else
return;
end if;
end RaiseError;
create or replace procedure RaiseError(
p_Raise in boolean,
p_ParamA out nocopy number)as--传引用,即使报错,值也会被修改
begin
p_ParamA:=7;
if p_Raise then
raise dup_val_on_index;
else
return;
end if;
end RaiseError;
declare v_tmp number:=1;
--declare v_tmp number(2):=1;--实际参数受精度可读或not
null约束，nocopy无效
begin
dbms_output.put_line('initial value: '||v_tmp);
raiseError(false,v_tmp);
dbms_output.put_line('after false: '||v_tmp);
v_tmp:=2;
dbms_output.put_line('before true: '||v_tmp);
raiseError(true,v_tmp);
exception
when others then
dbms_output.put_line('after unsuccessful: '||v_tmp);
end;
drop procedure RaiseError;
------------------------------
create or replace procedure DefaultTest(
p_ParameterA number default 10,
p_ParameterB varchar2 default 'abcdef',
p_ParameterC date default sysdate)as
begin
dbms_output.put_line(
'A: '|| p_ParameterA||
' B: '||p_ParameterB||
'C:'||to_char(p_ParameterC,'DD-MON-YYYY')
);
end;
begin
DefaultTest(p_ParameterA=>7,p_ParameterC =>
to_date('30-11-2005','DD-MM-YYYY'));
end;--命名符
begin
DefaultTest(7);--定位符
end;
--9.1.3 call语句
call DefaultTest(10);
call DefaultTest();
begin
--call DefaultTest();
execute immediate 'call DefaultTest()';
end;
drop procedure DefaultTest;
------------------------------------
create or replace function CallFunc(p1 in varchar2)
return varchar2 as
begin
dbms_output.put_line('callfunc called with '||p1);
return p1;
end;
declare
v_Result varchar2(50);
begin
execute immediate
'call CallFunc(''hello from pl/sql'')into :v_Result '
using out v_Result;
end;
drop function CallFunc;
---------------------------------------
create or replace package packA as
procedure xxxx1(p_var1 in number);
end packA;
create or replace package body packA as
procedure xxxx2(p_var1 in number) is
begin
dbms_output.put_line('xxxx2 '||p_var1);
end;--xxxx2必须在xxxx1之前，xxxx1才能调用到xxxx2
procedure xxxx1(p_var1 in number) is
begin
dbms_output.put_line('xxxx1 '||p_var1);
dbms_output.put_line('xxxx1 call xxxx2');
xxxx2(2);
end;
end packA;
begin
packA.xxxx1(1);
--packA.xxxx2(1);--not visible from outside
end;
drop package packA;
-------------------------------------------------------------------
--chapter 10
--10.1.1
create or replace procedure Simple as
v_Counter number;
begin
v_Counter:=7;
end Simple;
select *
from user_objects
where object_name='SIMPLE'--存储在数据字典中的都是大写
select *
from user_source--源代码
where name='SIMPLE' order by line
create or replace procedure Simple as
v_Counter number;
begin
v_Counter:=7--;
end Simple;
select *
from user_errors
where name='SIMPLE'
begin
Simple;
end;
drop procedure Simple;
--------------------------------
--10.1.2
create or replace procedure Simple as
v_Counter number;
function formatName(p_FirstName in varchar2,p_LastName in
varchar2)--只能放在声明最后
return varchar2 is
begin
dbms_output.put_line(p_FirstName||' '||p_LastName);
end formatName;
begin
v_Counter:=7;
end Simple;
create or replace procedure Simple2 as
procedure BBB;--预先声明
procedure AAA is
begin
BBB;
end AAA;
procedure BBB is
begin
AAA;
end BBB;
begin
null;
end Simple2;
------------------------------------------------------------------------------------------
begin
dbms_shared_pool.sizes(1);
end;
-------------------------------------------------------------------------------------------
--charter 12
--绑定变量
declare
v_Type dictionary.type%type;
v_Name dictionary.valuename%type;
v_Sql varchar(200);
begin
v_Type:='Status';
v_Sql := 'select valuename from dictionary where rownum=1 and
type=:type and languagecode=''cn''';
execute immediate v_Sql into v_Name using v_Type;
dbms_output.put_line(v_Name);
end;
--集体绑定
declare
type t_Table is table of varchar(20) index by binary_integer;
v_Table t_Table;
begin
for v_Count in 1..100 loop
v_Table(v_Count):=to_char(v_Count);
end loop;
forall v_Count in 1..100
insert into jjl values(v_Table(v_Count));
dbms_output.put_line(sql%rowcount);
commit;
forall v_Count in 1..100
delete jjl where a1=(v_Table(v_Count));
dbms_output.put_line(sql%rowcount);
commit;
end;
select * from jjl
--bulk collect
declare
type t_Table is table of jjl.a1%type index by binary_integer;
v_Table t_Table;
v_var jjl.a1%type;
begin
select a1
bulk collect into v_Table
from jjl;
--for v_count in 1..v_Table.count loop
dbms_output.put_line(v_Table.count);
-- end loop;
for v_count in 1..6 loop
dbms_output.put_line(substr(v_Table(v_count)));
end loop;
end;
declare
v_xxx number(10):=1;
begin
dbms_output.put_line('v_xxx is '||v_xxx);
v_xxx:=null;
dbms_output.put_line('v_xxx is '||to_char(v_xxx));
end;
declare
type t_cur is ref cursor;
v_cur t_cur;
v_usr usr%rowtype;
v_sql varchar2(100):='select userid,username from usr';
v_userid usr.userid%type;
v_username usr.username%type;
--'select userid,username from usr' 报错数量不一致
begin
open v_cur for v_sql;
loop
fetch v_cur into v_userid,v_username;
exit when v_cur%notfound;
dbms_output.put_line(to_char(v_userid));
end loop;
close v_cur;
end;
select instr('aaa','b') from dual
declare
v_str varchar2(1000):='aaa,bbb,ccc,ddd,eee,fff';
v_tmpstr varchar2(1000);
v_dest varchar2(1000);
begin
v_str := ''''||v_str||'''';
v_str := replace(v_str,',',''',''');
dbms_output.put_line(v_str);
end;
create or replace function xjp3addquote(p_str in varchar2) return
varchar2 as
v_desc varchar2(2000);
begin
v_desc := ''''||p_str||'''';
v_desc := replace(v_desc,',',''',''');
return v_desc;
end;
declare
v_aa number(3):=2;
v_bb number(3):=0;
v_cc number(4,2);
begin
v_cc := v_aa/v_bb;
--dbms_output.put_line(substr(sqlerrm,1,128));
end;