1、定义记录类型的语法
type worker_record_type is record(
id number(4),
name varchar2(20));
worker_record worker_record_type;
2、表类型定义语法（用下标区分表类型变量中的每一个元素如：表类型变量（下标））；
type my_table_type is table of varchar2(20)
index by binary_integer;
my_table my_table_type;
TYPE table_type IS TABLE OF VARCHAR2(20)
INDEX BY BINARY_INTEGER;
TYPE my_table_type IS TABLE OF table_type
INDEX BY BINARY_INTEGER;
my_table my_table_type;
3、定义数组的语法
TYPE my_varray_type IS VARRAY(2) OF NUMBER;
v_array my_varray_type := my_varray_type(0,0);
4、for循环
FOR i IN 1 .. 2
LOOP
v_array(i) := i*100;
DBMS_OUTPUT.PUT_LINE('Index '||to_char(i)||' is: '||to_char(v_array(i)));
END LOOP;
5、例外处理；
SET serveroutput ON
DECLARE
v_dname dept.dname%TYPE;
v_dept_rec dept%ROWTYPE;
BEGIN
BEGIN
SELECT dname INTO v_dname FROM dept;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('取部门名称错误处理放在这儿。');
WHEN OTHERS THEN
NULL;
END;
BEGIN
SELECT * INTO v_dept_rec FROM dept;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('取部门信息的错误处理放在这儿。');
WHEN OTHERS THEN
NULL;
END;
DBMS_OUTPUT.PUT_LINE('取得结果部门的名称是：' || v_dname);
DBMS_OUTPUT.PUT_LINE('取得结果部门的信息如下：');
DBMS_OUTPUT.PUT_LINE('编 号：' || v_dept_rec.deptno);
DBMS_OUTPUT.PUT_LINE('名 称：' || v_dept_rec.dname);
DBMS_OUTPUT.PUT_LINE('驻在地：' || v_dept_rec.loc);
END;
/
6、分支语句；
DECLARE
grade char:='B';
BEGIN
IF grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
DBMS_OUTPUT. PUT_LINE('Fair');
ELSIF grade = 'F' THEN
DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
7、循环语句
LOOP
DBMS_OUTPUT.put(ret);
ret:=ret+1;
EXIT WHEN ret>5;
END LOOP;
again:=again-1;
IF again>=0 THEN
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('下面转到标号label0处继续执行');
GOTO label0;
END IF;
FOR ret IN 1..5 LOOP
DBMS_OUTPUT.put(ret);
END LOOP;
8、定义游标
cursor 游标名[(形式参数表)]
is select 子句；
打开游标：open 游标名[(实
际参数表)];
fetch语句语法：fetch 游标名 into 变量名表；
关闭游标的语法：close 游标名；
9、判断是否有满足条件的被修改记录，如果没有，则插入新记录。
update emp set sal=sal*1.05 where empno=v_empno;
if sql%notfound then
insert into emp values(v_empno,v_ename,...);
end if;
10、例外实例
SET serveroutput ON
DECLARE
ret NUMBER;
BEGIN
SELECT COUNT(*) INTO ret FROM emp ;
--NO_DATA_FOUND
IF ret=0 THEN
DBMS_OUTPUT.PUT_LINE('没有符合条件的记录！');
return;
END IF;
--TOO_MANY_ROWS
IF ret>1 THEN
DBMS_OUTPUT.PUT_LINE('数据有错误，符合条件的记录有多条！');
return;
END IF;
END;
11、局部子程序
CREATE OR REPLACE PROCEDURE dele_emp
(p_no IN emp.empno%TYPE)
IS
PROCEDURE write_log
IS
BEGIN
INSERT INTO log_table(empno,user_id,log_date)
VALUES(p_no,USER,SYSDATE);
END;
BEGIN
DELETE
FROM emp
WHERE empno = p_no;
write_log;
END;
12、在两台数据库服务器之间保持数据实时同步
CONN scott/tiger@orcl
ALTER TABLE dept ADD (flag char);
CONN scott/tiger@cemerp
ALTER TABLE dept ADD (flag char);
CONN scott/tiger@cemerp
CREATE OR REPLACE TRIGGER dept_replication
BEFORE INSERT OR UPDATE ON dept
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :NEW.flag IS NULL THEN
INSERT INTO
dept@cemerp_orcl_dblink(deptno,dname,loc,flag)
VALUES(:new.deptno,:new.dname,:new.loc,'B');
:NEW.flag := 'A';
END IF;
ELSE --UPDATE操作
IF :NEW.flag = :OLD.flag THEN
UPDATE dept@cemerp_orcl_dblink
SET deptno=:new.deptno, dname=:new.dname,
loc=:new.loc, flag=:new.flag
WHERE deptno = :NEW.deptno;
END IF;
IF :OLD.flag = 'A' THEN :NEW.flag := 'B';
ELSE :NEW.flag := 'A';
END IF;
END IF;
END;
/
CONN scott/tiger@orcl
CREATE OR REPLACE TRIGGER dept_replication
BEFORE INSERT OR UPDATE ON dept
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :NEW.flag IS NULL THEN
INSERT INTO
dept@orcl_cemerp_dblink(deptno,dname,loc,flag)
VALUES(:new.deptno,:new.dname,:new.loc,'B');
:NEW.flag := 'A';
END IF;
ELSE --UPDATE操作
IF :NEW.flag = :OLD.flag THEN
UPDATE dept@orcl_cemerp_dblink
SET deptno=:new.deptno, dname=:new.dname,
loc=:new.loc, flag=:new.flag
WHERE deptno = :NEW.deptno;
END IF;
IF :OLD.flag = 'A' THEN :NEW.flag := 'B';
ELSE :NEW.flag := 'A';
END IF;
END IF;
END;
/
13、bfile的使用
CONN sys/syspwd@orcl AS SYSDBA
CREATE OR REPLACE DIRECTORY "pict_dir" AS 'D:\data\emp\pict';
CREATE OR REPLACE DIRECTORY "video_dir" AS 'D:\data\emp\video';
GRANT READ ON DIRECTORY "pict_dir" TO PUBLIC;
GRANT READ ON DIRECTORY "video_dir" TO PUBLIC;
CONN scott/tiger@orcl
SET SERVEROUTPUT ON
CREATE OR
REPLACE PROCEDURE load_emp_bfile
(p_file_loc IN VARCHAR2)
IS
v_file BFILE;
v_filename VARCHAR2(16);
BEGIN
v_filename := '7369.avi';
v_file := BFILENAME(p_file_loc, v_filename);
DBMS_LOB.FILEOPEN(v_file);
UPDATE emp SET video = v_file
WHERE empno=7369 ;
DBMS_OUTPUT.PUT_LINE('LOADED FILE: '||v_filename
|| ' SIZE: '||DBMS_LOB.GETLENGTH(v_file));
DBMS_LOB.FILECLOSE(v_file);
END load_emp_bfile;
/
EXECUTE load_emp_bfile('video_dir')
UPDATE emp
SET video = BFILENAME('video_dir', '7369.avi')
WHERE empno=7369 ;
14、可创建一过程实现对状态非法的PL/SQL程序进行重新编译
CREATE OR REPLACE PROCEDURE compile_obj
IS
CURSOR obj_cur IS
SELECT object_type, object_name
FROM user_objects
WHERE status = 'INVALID' AND object_type IN
('PROCEDURE', 'FUNCTION','PACKAGE','PACKAGE BODY')
ORDER BY object_type;
BEGIN
FOR obj_rec IN obj_cur LOOP
DBMS_DDL.ALTER_COMPILE
(obj_rec.object_type, user,obj_rec.object_name);
END LOOP;
END compile_obj;
/
15、从orcl数据库通过数据库连接向数据库cemerp的表emp插入记录；
INSERT INTO emp@orcl_cemerp_dblink
(deptno, ename, empno, sal, comm, mgr)
SELECT deptno, ename, empno, sal, comm, mgr FROM emp;
16、将emp表中30部门的雇员记录合并到bonus表中（练习MERGE）;
MERGE INTO bonus D
USING (SELECT ename,sal,job,comm FROM emp WHERE deptno=30) S
ON (D.ename = S.ename)
WHEN MATCHED THEN
UPDATE SET https://www.doczj.com/doc/528975545.html,m = https://www.doczj.com/doc/528975545.html,m + S.sal
WHEN NOT MATCHED THEN
INSERT (D.ename, D.job, D.sal, https://www.doczj.com/doc/528975545.html,m)
VALUES (S.ename, S.job, S.sal, S.sal*0.1);
COMMIT;
17、假设ORCL数据库为主数据库，CEMERP数据库为从数据库。将ORCL中scott用户下部门表dept的数据5分钟刷新一次，写到CEMERP的scott用户的实体化视图mv_dept中。
(1)主数据库服务器上执行
CONNECT scott/tiger@orcl
SELECT owner,table_name,constraint_type
FROM user_constraints
WHERE owner='SCOTT' AND table_name='DEPT'
ORDER BY constraint_type;
ALTER TABLE dept ADD(PRIMARY KEY(deptno));
DROP MATERIALIZED VIEW LOG ON dept;
CREATE MATERIALIZED VIEW LOG ON dept
WITH PRIMARY KEY
INCLUDING NEW VALUES;
SELECT * FROM tab ORDER BY tname;
DESC MLOG$_DEPT
(2)从数据库服务器CEMERP上执行
CONNECT scott/tiger@cemerp
SELECT * FROM user_sys_privs WHERE username='SCOTT';
CONNECT system/systempwd@cemerp
GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;
CONNECT scott/tiger@cemerp
COL username FORMAT A8
SELECT * FROM user_sys_privs WHERE username='SCOTT';
ORCL_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
DROP DATABASE LINK cemerp_orcl_dblink;
CREATE DATABASE LINK cemerp_orcl_dblink
CONNECT TO scott
IDENTIFIED BY tiger USING 'orcl_db';
D
ROP MATERIALIZED VIEW mv_dept;
CREATE MATERIALIZED VIEW mv_dept
BUILD IMMEDIATE
REFRESH FAST
NEXT SYSDATE+5 / 1440
AS SELECT *
FROM dept@cemerp_orcl_dblink;
SELECT * FROM tab;
DESC mv_dept
(3)测试
CONNECT scott/tiger@orcl
INSERT INTO dept(deptno,dname,loc) VALUES(1,'部门1','地点1');
INSERT INTO dept(deptno,dname,loc) VALUES(2,'部门2','地点2');
COMMIT;
COL CHANGE_VECTOR$$ FORMAT A20
SELECT * FROM MLOG$_DEPT;
CONNECT scott/tiger@cemerp
SELECT * FROM mv_dept ;