【Oracle】第六次实验报告

【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