【Oracle】第五次作业
%type基本四步
SQL> set serveroutput on
SQL>
declare
cursor c_1 is select empno from scott.emp;
v_1 scott.emp.empno %type;
begin
open c_1;
fetch c_1 into v_1;
dbms_output.put_line(v_1);
fetch c_1 into v_1;
dbms_output.put_line(c_1%rowcount);
close c_1;
end;
/
7369
2
PL/SQL procedure successfully completed
V_1%rowtype
SQL> set serveroutput on
SQL> declare
cursor c_1 is select * from scott.emp;
v_1 scott.emp%rowtype;
begin
open c_1;
fetch c_1 into v_1;
dbms_output.put_line(v_1.empno);
fetch c_1 into v_1;
dbms_output.put_line(c_1%rowcount);
close c_1;
end;
/
7369
2
PL/SQL procedure successfully completed
Cursor%rowtype
SQL> set serveroutput on
SQL> declare
cursor c_3 is select empno,ename,emp.deptno,dname from scott.emp,scott.dept
where scott.emp.deptno=scott.dept.deptno;
v_1 c_3%rowtype;
begin
open c_3;
fetch c_3 into v_1;
dbms_output.put_line(v_1.empno);
fetch c_3 into v_1;
dbms_output.put_line(c_3%rowcount);
close c_3;
end;
/
7782
2
PL/SQL procedure successfully completed
带参数的cursor（引用游标）
SQL> set serveroutput on
SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp;
注: c:\xskc.dmp在ftp里面.
SQL> select * from xs;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
001 张琼 计算机 45 三好学生
121112 王小二 计算机 男 1986/1/30 36
SQL>declare
cursor c_4(v_xb xs.xb%type) is select * from xs where xb=v_xb;
v_4 xs%rowtype;
begin
open c_4(‘男’);
fetch c_4 into v_4;
dbms_output.put_line(v_4.xm);
close c_4;
end;
/
王林
PL/SQL procedure successfully completed
Loop
SQL> set serveroutput on
SQL> declare
cursor c_4(v_xb xs.xb%type) is select * from xs where xb=v_xb;
v_4 xs%rowtype;
begin
open c_4(‘男’);
loop
fetch c_4 into v_4;
exit when c_4%notfound;
dbms_output.put_line(v_4.xm);
end loop;
close c_4;
end;
/
王林
李明
王小二
PL/SQL procedure successfully completed
SQL>
利用while循环检索索引
SQL> set serveroutput on
SQL> declare
2 cursor c_4(v_xb xs.xb%type) is select * from xs where xb=v_xb;
3 v_4 xs%rowtype;
4 begin
5 open c_4(‘男’);
6 fetch c_4 into v_4;
7 while c_4%found loop
8 dbms_output.put_line(v_4.xm);
9 fetch c_4 into v_4;
10 end loop;
11 close c_4;
12 end;
13 /
王林
李明
王小二
PL/SQL procedure successfully completed
利用for循环检索索引
SQL> set serveroutput on
SQL> declare
2 cursor c_4(v_xb xs.xb%type) is select * from xs where xb=v_xb;
3 v_4 xs%rowtype;
4 begin
5 for v_4 in c_4(‘男’)loop
6 dbms_output.put_line(v_4.xm);
7 end loop;
8 end;
9 /
王林
李明
王小二
PL/SQL procedure successfully completed
Merge(多做几遍)
根据 ppt创建products和newproducts表
建表：
create table PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
create table NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
插入数据：
begin
insert into PRODUCTS values (1501, ‘VIVITAR 35MM’, ‘ELECTRNCS’);
insert into PRODUCTS values (1502, ‘OLYMPUS IS50’, ‘ELECTRNCS’);
insert into PRODUCTS values (1600, ‘PLAY GYM’, ‘TOYS’);
insert into PRODUCTS values (1601, ‘LAMAZE’, ‘TOYS’);
insert into PRODUCTS values (1666, ‘HARRY POTTER’, ‘DVD’);
commit;
Insert into NEWPRODUCTS values (1502, ‘OLYMPUS CAMERA’, ‘ELECTRNCS’);
insert into NEWPRODUCTS values (1601, ‘LAMAZE’, ‘TOYS’);
insert into NEWPRODUCTS values (1666, ‘HARRY POTTER’, ‘TOYS’);
insert into NEWPRODUCTS values (1700, ‘WAIT INTERFACE’, ‘BOOKS’);
commit;
end;
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME CATEGORY
1501 VIVITAR 35MM ELECTRNCS
1502 OLYMPUS CAMERA ELECTRNCS
1600 PLAY GYM TOYS
1601 LAMAZE TOYS
1666 HARRY POTTER TOYS
SQL> select * from newproducts;
PRODUCT_ID PRODUCT_NAME CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
1502 OLYMPUS CAMERA ELECTRNCS
1601 LAMAZE TOYS
1666 HARRY POTTER TOYS
1700 WAIT INTERFACE BOOKS
SQL> merge into products p
2 using newproducts np
3 on(p.product_id=np.product_id)
4 when matched then update set p.product_name=np.product_name,p.category=np.category
5 when not matched then insert values(np.product_id,np.product_name,np.category);
4 rows merged
SQL> select * from products;
PRODUCT_ID PRODUCT_NAME CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
1501 VIVITAR 35MM ELECTRNCS
1502 OLYMPUS CAMERA ELECTRNCS
1600 PLAY GYM TOYS
1601 LAMAZE TOYS
1666 HARRY POTTER TOYS
1700 WAIT INTERFACE BOOKS
6 rows selected