[OT]游标|异常|记录类型|内存表
--打开包得输出output
SET SERVEROUTPUT ON
create table test_tab(id int);
--1，定义变量，循环向test_tab写数据
declare
v_count number(2):=0;
begin
loop
insert into test_tab values(v_count);
v_count:=v_count+1;
exit when(v_count>=10);
end loop;
commit;
end;
select * from test_tab;
2，--使用for循环向test_tab写数据
declare
v_count number(2):=0;
begin
for cn in 10..20 loop
insert into test_tab values(cn);
end loop;
commit;
end;
select * from test_tab;
3，--使用for循环从emp表中取数据
declare
v_count number(2):=0;
begin
for item in (select * from emp) loop
dbms_output.put_line(item.ename||' '||item.empno||' '||SQL%ROWCOUNT||'-');
end loop;
end;
4，--while循环
declare
v_count number(2):=0;
begin
while v_count<10 loop
dbms_output.put_line('v_count='||v_count);
v_count:= v_count + 1;
end loop;
end;
5，--循环跳转
declare
total_done varchar2(20):='YES';
inner_done varchar2(20):='YES';
v_counter int:=0;
BEGIN
<>
Loop
v_counter:=v_counter+1;
EXIT WHEN v_counter > 10;
<>
Loop
EXIT Outer_loop WHEN total_done = 'NO';
--Leave both loops
EXIT WHEN inner_done ='YES';
--Leave inner loop only
END LOOP Inner_loop;
dbms_output.put_line('exit inner loop body');
END LOOP Outer_loop;
dbms_output.put_line('exit outer loop body');
END;
6，--定义记录类型
declare
TYPE emp_record_type IS RECORD
(ename varchar2(10),job varchar2(9),sal number(7,2));
emp_record emp_record_type;
begin
select ename,job,sal into emp_record from emp where rownum=1;
dbms_output.put_line(emp_record.ename||' '||emp_record.job);
end;
7，--定义EMP表记录类型
--定义EMP.ENAME字段类型
declare
emp_record emp%rowtype;
emp_name emp.ename%type;
begin
select * into emp_record from emp where rownum=1;
dbms_output.put_line(emp_record.ename||' '||emp_record.job);
end;
8，--PL/SQL内存表
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) :='CAMERON';
ename_table(2) :='KING';
hiredate_table(8) :=SYSDATE+7;
IF ename_table.EXISTS(1) THEN
dbms_output.put_line('First element is '||ename_table(2)||' hiredate='||hiredate_table(8));
end if;
END;
9，--定义游标，使用LOOP循环读取数据
declare
cursor emp_cur is select empno,ename from emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
begin
if not emp_cur%ISOPEN then
open emp_cur;
end if;
loop
fetch emp_cur into v_empno,v_ename;
dbms_output.put_line('empno='||v_empno||',ename='||v_ename);
exit when emp_cur%NOTFOUND;
end loop;
end;
10，--定义游标，游标记录类型，Loop读取数据
declare
cursor emp_cur is select empno,ename from emp;
rec_empno emp_cur%rowtype;
begin
if not emp_cur%ISOPEN then
open emp_cur;
end if;
loop
fetch emp_cur into rec_empno;
dbms_output.put_line('empno='||rec_empno.empno||',ename='||rec_empno.ename);
exit when emp_cur%NOTFOUND;
end loop;
end;
11，--使用for操作游标数据
declare
cursor emp_cur is select empno,ename from emp;
begin
for rec_empno in emp_cur
loop
dbms_output.put_line('empno='||rec_empno.empno||',ename='||rec_empno.ename);
end loop;
end;
12，--使用sys_refcursor引用类型定义游标
declare
my_cur sys_refcursor;
tname tab%rowtype;
begin
open my_cur for 'select * from tab';
loop
fetch my_cur into tname;
exit when my_cur%notfound;
dbms_output.put_line('TName='||tname.tname);
end loop;
close my_cur;
exception
when NO_DATA_FOUND then
dbms_output.put_line('NO data');
when OTHERS then
dbms_output.put_line('Other');
end;
13，--使用for操作隐式游标
begin
for item in (select empno,ename from emp)
loop
dbms_output.put_line(item.empno||' '||item.ename);
end loop;
end;
14，--使用for操作游标数据
declare
cursor c1 is select empno,ename from emp;
begin
for item in c1
loop
dbms_output.put_line(item.empno||' '||item.ename);
end loop;
end;
15，--定义引用游标类型
declare
type emp_cur is ref cursor return emp%rowtype;
my_cur emp_cur;
my_emp emp%rowtype;
begin
if not my_cur%ISOPEN then
open my_cur for select * from emp;
end if;
loop
fetch my_cur into my_emp;
exit when my_cur%notfound;
dbms_output.put_line(my_emp.empno||' '||my_emp.ename);
end loop;
close my_cur;
end;
16，--Exception处理
declare
deptno number(2);
dname varchar2(14);
loc varchar2(14);
begin
select deptno,dname,loc into deptno,dname,loc from dept where deptno=10;
insert into dept values(deptno,dname,loc);
exception
when DUP_VAL_ON_INDEX THEN
rollback;
dbms_output.put_line('Rollback transaction.....');
end;
17，--PL/SQL变量定义
declare
c_val constant number(2):=10;
c_val2 int;
c_val3 c_val2%type;
begin
--c_val:=11;
dbms_output.put_line('-------');
end;
18，--TURE/NULL/FALSE判断真假
declare
b_val1 boolean := true;
b_val2 boolean := null;
begin
if b_val1 and b_val2 then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
19，--循环标志及跳转
declare
total_done varchar2(20):='YES';
inner_done varchar2(20):='YES';
v_counter int:=0;
BEGIN
<>
LOOP
v_counter:=v_counter+1;
EXIT WHEN v_counter > 10;
<>
LOOP
EXIT Outer_loop when total_done='YES';
--leave both loops
EXIT WHEN inner_done = 'YES';
--Leave inner loop only
END LOOP Inner_loop;
END LOOP Outer_loop;
END;
20，--定义record类型
declare
type emp_record_type IS RECORD
(ename varchar2(10),job varchar2(9),sal number(7,2));
emp_record emp_record_type;
begin
dbms_output.put_line('recordtype');
end;
21，--pL/sQL内存表
declare
type e_table_type IS TABLE OF emp.ename%Type index by binary_integer;
e_tab e_table_type;
begin
e_tab(1):='SMITH';
update emp
set sal=1.1*sal
where ename=e_tab(1);
commit;
end;
22，--游标的定义
declare
cursor emp_cursor (p_deptno number,p_job varchar2)
is select empno,ename from emp
where deptno=p_deptno and job=p_job;
v_empno number(20);
v_ename varchar2(30);
begin
open emp_cursor(10,'CLERK');
loop
fetch emp_cursor into v_empno,v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line('empno='||v_empno||',ename='||v_ename);
end loop;
end;
23，--FOR操作游标
declare
cursor sal_cursor
is select sal from emp
where deptno=30
for update of sal nowait;
begin
for emp_record in sal_cursor loop
update emp set sal=emp_record.sal*1.10
where current of sal_cursor;
end loop;
commit;
end;
24，--非预定义异常
DECLARE
e_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(
e_emps_remaining,-2292);
v_deptno dept.deptno%TYPE := &p_deptno;
BEGIN
delete from dept
where deptno = v_deptno;
commit;
EXCEPTION
when e_emps_remaining THEN
DBMS_OUTPUT.PUT_LINE('Cannot remove dept'||
TO_CHAR(v_deptno)||'. Employees exit.');
end;
25，--用户定义异常
DECLARE
e_invalid_dept EXCEPTION;
Begin
update dept
set dname='TestDept'
where deptno = &v_deptno;
IF SQL%NOTFOUND THEN
RAISE e_invalid_dept;
end if;
commit;
EXCEPTION
WHEN e_invalid_dept THEN
DBMS_OUTPUT.PUT_LINE('Invalid dept number.');
DBMS_OUTPUT.PUT_LINE('SQLCODE='||SQLCODE||' SQLERRM='||SQLERRM);
END;
--调用DBMS_STANDARD（oracle提供给的包）包所定义的RAISE_APPLICATION_ERROR过程，可以重新
--定义异常错误信息，它为应用程序提供了一种于oracle互动的方法
--建立错误代码表：
CREATE TABLE errlog(Errcode NUMBER,Errtext char(40));
注：本文的接着下面一篇文章介绍《函数存储过程触发器登录触发器Insert[All|First]Merge》，敬请查阅。
SET SERVEROUTPUT ON
create table test_tab(id int);
--1，定义变量，循环向test_tab写数据
declare
v_count number(2):=0;
begin
loop
insert into test_tab values(v_count);
v_count:=v_count+1;
exit when(v_count>=10);
end loop;
commit;
end;
select * from test_tab;
2，--使用for循环向test_tab写数据
declare
v_count number(2):=0;
begin
for cn in 10..20 loop
insert into test_tab values(cn);
end loop;
commit;
end;
select * from test_tab;
3，--使用for循环从emp表中取数据
declare
v_count number(2):=0;
begin
for item in (select * from emp) loop
dbms_output.put_line(item.ename||' '||item.empno||' '||SQL%ROWCOUNT||'-');
end loop;
end;
4，--while循环
declare
v_count number(2):=0;
begin
while v_count<10 loop
dbms_output.put_line('v_count='||v_count);
v_count:= v_count + 1;
end loop;
end;
5，--循环跳转
declare
total_done varchar2(20):='YES';
inner_done varchar2(20):='YES';
v_counter int:=0;
BEGIN
<
Loop
v_counter:=v_counter+1;
EXIT WHEN v_counter > 10;
<
Loop
EXIT Outer_loop WHEN total_done = 'NO';
--Leave both loops
EXIT WHEN inner_done ='YES';
--Leave inner loop only
END LOOP Inner_loop;
dbms_output.put_line('exit inner loop body');
END LOOP Outer_loop;
dbms_output.put_line('exit outer loop body');
END;
6，--定义记录类型
declare
TYPE emp_record_type IS RECORD
(ename varchar2(10),job varchar2(9),sal number(7,2));
emp_record emp_record_type;
begin
select ename,job,sal into emp_record from emp where rownum=1;
dbms_output.put_line(emp_record.ename||' '||emp_record.job);
end;
7，--定义EMP表记录类型
--定义EMP.ENAME字段类型
declare
emp_record emp%rowtype;
emp_name emp.ename%type;
begin
select * into emp_record from emp where rownum=1;
dbms_output.put_line(emp_record.ename||' '||emp_record.job);
end;
8，--PL/SQL内存表
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) :='CAMERON';
ename_table(2) :='KING';
hiredate_table(8) :=SYSDATE+7;
IF ename_table.EXISTS(1) THEN
dbms_output.put_line('First element is '||ename_table(2)||' hiredate='||hiredate_table(8));
end if;
END;
9，--定义游标，使用LOOP循环读取数据
declare
cursor emp_cur is select empno,ename from emp;
v_empno emp.empno%type;
v_ename emp.ename%type;
begin
if not emp_cur%ISOPEN then
open emp_cur;
end if;
loop
fetch emp_cur into v_empno,v_ename;
dbms_output.put_line('empno='||v_empno||',ename='||v_ename);
exit when emp_cur%NOTFOUND;
end loop;
end;
10，--定义游标，游标记录类型，Loop读取数据
declare
cursor emp_cur is select empno,ename from emp;
rec_empno emp_cur%rowtype;
begin
if not emp_cur%ISOPEN then
open emp_cur;
end if;
loop
fetch emp_cur into rec_empno;
dbms_output.put_line('empno='||rec_empno.empno||',ename='||rec_empno.ename);
exit when emp_cur%NOTFOUND;
end loop;
end;
11，--使用for操作游标数据
declare
cursor emp_cur is select empno,ename from emp;
begin
for rec_empno in emp_cur
loop
dbms_output.put_line('empno='||rec_empno.empno||',ename='||rec_empno.ename);
end loop;
end;
12，--使用sys_refcursor引用类型定义游标
declare
my_cur sys_refcursor;
tname tab%rowtype;
begin
open my_cur for 'select * from tab';
loop
fetch my_cur into tname;
exit when my_cur%notfound;
dbms_output.put_line('TName='||tname.tname);
end loop;
close my_cur;
exception
when NO_DATA_FOUND then
dbms_output.put_line('NO data');
when OTHERS then
dbms_output.put_line('Other');
end;
13，--使用for操作隐式游标
begin
for item in (select empno,ename from emp)
loop
dbms_output.put_line(item.empno||' '||item.ename);
end loop;
end;
14，--使用for操作游标数据
declare
cursor c1 is select empno,ename from emp;
begin
for item in c1
loop
dbms_output.put_line(item.empno||' '||item.ename);
end loop;
end;
15，--定义引用游标类型
declare
type emp_cur is ref cursor return emp%rowtype;
my_cur emp_cur;
my_emp emp%rowtype;
begin
if not my_cur%ISOPEN then
open my_cur for select * from emp;
end if;
loop
fetch my_cur into my_emp;
exit when my_cur%notfound;
dbms_output.put_line(my_emp.empno||' '||my_emp.ename);
end loop;
close my_cur;
end;
16，--Exception处理
declare
deptno number(2);
dname varchar2(14);
loc varchar2(14);
begin
select deptno,dname,loc into deptno,dname,loc from dept where deptno=10;
insert into dept values(deptno,dname,loc);
exception
when DUP_VAL_ON_INDEX THEN
rollback;
dbms_output.put_line('Rollback transaction.....');
end;
17，--PL/SQL变量定义
declare
c_val constant number(2):=10;
c_val2 int;
c_val3 c_val2%type;
begin
--c_val:=11;
dbms_output.put_line('-------');
end;
18，--TURE/NULL/FALSE判断真假
declare
b_val1 boolean := true;
b_val2 boolean := null;
begin
if b_val1 and b_val2 then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
19，--循环标志及跳转
declare
total_done varchar2(20):='YES';
inner_done varchar2(20):='YES';
v_counter int:=0;
BEGIN
<
LOOP
v_counter:=v_counter+1;
EXIT WHEN v_counter > 10;
<
LOOP
EXIT Outer_loop when total_done='YES';
--leave both loops
EXIT WHEN inner_done = 'YES';
--Leave inner loop only
END LOOP Inner_loop;
END LOOP Outer_loop;
END;
20，--定义record类型
declare
type emp_record_type IS RECORD
(ename varchar2(10),job varchar2(9),sal number(7,2));
emp_record emp_record_type;
begin
dbms_output.put_line('recordtype');
end;
21，--pL/sQL内存表
declare
type e_table_type IS TABLE OF emp.ename%Type index by binary_integer;
e_tab e_table_type;
begin
e_tab(1):='SMITH';
update emp
set sal=1.1*sal
where ename=e_tab(1);
commit;
end;
22，--游标的定义
declare
cursor emp_cursor (p_deptno number,p_job varchar2)
is select empno,ename from emp
where deptno=p_deptno and job=p_job;
v_empno number(20);
v_ename varchar2(30);
begin
open emp_cursor(10,'CLERK');
loop
fetch emp_cursor into v_empno,v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line('empno='||v_empno||',ename='||v_ename);
end loop;
end;
23，--FOR操作游标
declare
cursor sal_cursor
is select sal from emp
where deptno=30
for update of sal nowait;
begin
for emp_record in sal_cursor loop
update emp set sal=emp_record.sal*1.10
where current of sal_cursor;
end loop;
commit;
end;
24，--非预定义异常
DECLARE
e_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT(
e_emps_remaining,-2292);
v_deptno dept.deptno%TYPE := &p_deptno;
BEGIN
delete from dept
where deptno = v_deptno;
commit;
EXCEPTION
when e_emps_remaining THEN
DBMS_OUTPUT.PUT_LINE('Cannot remove dept'||
TO_CHAR(v_deptno)||'. Employees exit.');
end;
25，--用户定义异常
DECLARE
e_invalid_dept EXCEPTION;
Begin
update dept
set dname='TestDept'
where deptno = &v_deptno;
IF SQL%NOTFOUND THEN
RAISE e_invalid_dept;
end if;
commit;
EXCEPTION
WHEN e_invalid_dept THEN
DBMS_OUTPUT.PUT_LINE('Invalid dept number.');
DBMS_OUTPUT.PUT_LINE('SQLCODE='||SQLCODE||' SQLERRM='||SQLERRM);
END;
--调用DBMS_STANDARD（oracle提供给的包）包所定义的RAISE_APPLICATION_ERROR过程，可以重新
--定义异常错误信息，它为应用程序提供了一种于oracle互动的方法
--建立错误代码表：
CREATE TABLE errlog(Errcode NUMBER,Errtext char(40));
注：本文的接着下面一篇文章介绍《函数存储过程触发器登录触发器Insert[All|First]Merge》，敬请查阅。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29611940/viewspace-1143695/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
