当前位置:首页 > 开发 > 数据库 > 正文

plSql介绍

发表于: 2015-06-27   作者:bijian1013   来源:转载   浏览:
摘要: /* * PL/SQL 程序设计学习笔记 * 学习plSql介绍.pdf * 时间:2010-10-05 */ --创建DEPT表 create table DEPT ( DEPTNO NUMBER(10), DNAME NVARCHAR2(255), LOC NVARCHAR2(255) ) delete dept; select
/*
 * PL/SQL 程序设计学习笔记
 * 学习plSql介绍.pdf
 * 时间:2010-10-05
*/

--创建DEPT表
create table DEPT
(
  DEPTNO NUMBER(10),
  DNAME  NVARCHAR2(255),
  LOC    NVARCHAR2(255)
)

delete dept;

select * from dept;


--插入一条记录并显示
DECLARE
       Row_id UROWID;
       info VARCHAR2(40);
BEGIN
     INSERT INTO dept VALUES (90, 'SERVICE', 'BEIJING')
     RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
     INTO row_id, info;
     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
     DBMS_OUTPUT.PUT_LINE(info);
END;


--修改一条记录并显示
DECLARE
       Row_id UROWID;
       info VARCHAR2(40);
BEGIN
     UPDATE dept SET deptno=80 WHERE DNAME='SERVICE'
     RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
     INTO row_id, info;
     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
     DBMS_OUTPUT.PUT_LINE(info);
END;


--删除一条记录并显示
DECLARE
       Row_id UROWID;
       info VARCHAR2(40);
BEGIN
     DELETE dept WHERE DNAME='SERVICE'
     RETURNING rowid, dname||':'||to_char(deptno)||':'||loc
     INTO row_id, info;
     DBMS_OUTPUT.PUT_LINE('ROWID:'||row_id);
     DBMS_OUTPUT.PUT_LINE(info);
END;


--复合类型-记录类型
DECLARE
       TYPE test_rec IS RECORD(
       Code VARCHAR2(10),
       Name VARCHAR2(30) NOT NULL :='a book');
       V_book test_rec;
BEGIN
     V_book.code :='123';
     V_book.name :='C++ Programming';
     DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name);
END;

--复合类型-使用%TYPE
--创建emp表
create table emp
(
  empno NUMBER(10),
  ename  NVARCHAR2(255),
  sal    NUMBER(10,2),
  hiredate Timestamp,
  comm NUMBER(10,2),
  job NVARCHAR2(255),
  deptno NUMBER(10)
)

insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(100,'Test',30.5,sysdate,10,'president',1);
insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(7788,'Test2',100.23,sysdate,10.2,'manager',2);
--drop table emp;
select * from emp;


DECLARE
--用%TYPE 类型定义与表相配的字段
          TYPE t_Record IS RECORD(
          T_no emp.empno%TYPE,
          T_name emp.ename%TYPE,
          T_sal emp.sal%TYPE );
          --声明接收数据的变量
          v_emp t_Record;
BEGIN
     SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788;
     DBMS_OUTPUT.PUT_LINE
     (TO_CHAR(v_emp.t_no)||v_emp.t_name||TO_CHAR(v_emp.t_sal));
END;


DECLARE
       v_empno emp.empno%TYPE :=&empno;
       Type r_record is record (
       v_name emp.ename%TYPE,
       v_sal emp.sal%TYPE,
       v_date emp.hiredate%TYPE);
       Rec r_record;
BEGIN
     SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;
     DBMS_OUTPUT.PUT_LINE(Rec.v_name||'--'||Rec.v_sal||'--'||Rec.v_date);
END;

--使用%ROWTYPE
DECLARE
       v_empno emp.empno%TYPE :=&empno;
       rec emp%ROWTYPE;
BEGIN
     SELECT * INTO rec FROM emp WHERE empno=v_empno;
     DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);
END;



--以下脚本只能在sqlplus命令行中执行
----start-----
VARIABLE result2 NUMBER
         BEGIN
              SELECT (sal*12)+nvl(comm, 0) INTO :result2 FROM emp WHERE empno=7788;
         END;
PRINT result2;
-----end-----


--INDEX BY TABLES
select * from dept;
insert into dept(deptno,dname,loc) values(1,'Test1','BEIJING');
insert into dept(deptno,dname,loc) values(10,'Test10','SHENGZHEN');
insert into dept(deptno,dname,loc) values(20,'Test20','CHANGSHA');
insert into dept(deptno,dname,loc) values(30,'Test30','ZHUHAI');
insert into dept(deptno,dname,loc) values(40,'Test40','SHAOYANG');

DECLARE
       TYPE dept_table_type IS TABLE OF
       dept%ROWTYPE INDEX BY BINARY_INTEGER;
       my_dname_table dept_table_type;
       v_count number(2) :=4;
BEGIN
     FOR i IN 1 .. v_count LOOP
         SELECT * INTO my_dname_table(i) FROM dept WHERE deptno=i*10;
     END LOOP;
     FOR i IN my_dname_table.FIRST.. my_dname_table.LAST LOOP
         DBMS_OUTPUT.PUT_LINE('Department number: '||my_dname_table(i).deptno);
         DBMS_OUTPUT.PUT_LINE('Department name: '|| my_dname_table(i).dname);
     END LOOP;
END;


--BOOLEAN 赋值
DECLARE
       done BOOLEAN;
       /* the following statements are legal: */
BEGIN
     done := FALSE;
     WHILE NOT done LOOP
           Null;
     END LOOP;
END;


SELECT * FROM EMP
--数据库赋值
DECLARE
       emp_id emp.empno%TYPE :=7788;
       emp_name emp.ename%TYPE;
       wages emp.sal%TYPE;
BEGIN
     SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages
     FROM emp WHERE empno = emp_id;
     Dbms_output.put_line(emp_name||'----'||to_char(wages));
END;



SELECT * FROM emp;

insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(8,'Test3',10.23,sysdate,1.2,'manager',3);
insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(12,'Test4',10.23,sysdate,1.2,'president',4);
delete emp where empno = 8;
delete emp where empno = 12;

--变量作用范围及可见性
DECLARE
       Emess char(80);
BEGIN
     DECLARE
     V1 NUMBER(4);
     BEGIN
          SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president';
          DBMS_OUTPUT.PUT_LINE(V1);
     EXCEPTION
     When TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE ('More than one president');
     END;
     DECLARE
     V1 NUMBER(4);
     BEGIN
          SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager';
          DBMS_OUTPUT.PUT_LINE(V1);
     EXCEPTION
     When TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE ('More than one manager');
     END;
EXCEPTION
When others THEN
     Emess:=substr(SQLERRM,1,80);
     DBMS_OUTPUT.PUT_LINE (emess);
END;



select * from emp;
/* 本例子仅是一个简单的插入,不是实际应用。*/
DECLARE
       v_ename VARCHAR2(20) := 'Bill';
       v_sal NUMBER(7,2) :=1234.56;
       v_deptno NUMBER(2) := 10;
       v_empno NUMBER(4) := 8888;
BEGIN
     INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )
     VALUES ( v_empno, v_ename, 'Manager', v_sal, v_deptno,TO_DATE('1954.06.09','yyyy.mm.dd') );
     COMMIT;
END;


/* 本例子仅是一个简单的删除例子,不是实际应用。*/
DECLARE
       v_empno number(4) := 8888;
BEGIN
     DELETE FROM emp WHERE empno=v_empno;
     COMMIT;
END;




select * from emp;

--条件语句
DECLARE
       v_empno emp.empno%TYPE :=&empno;
       V_salary emp.sal%TYPE;
       V_comment VARCHAR2(35);
BEGIN
     SELECT sal INTO v_salary FROM emp WHERE empno=v_empno;
     IF v_salary<1500 THEN
        V_comment:= 'Fairly less';
     ELSIF v_salary <3000 THEN
        V_comment:= 'A little more';
     ELSE
        V_comment:= 'Lots of salary';
     END IF;
     DBMS_OUTPUT.PUT_LINE(V_comment);
END;



--CASE 表达式
DECLARE
       V_grade char(1) := UPPER('&p_grade');
       V_appraisal VARCHAR2(20);
BEGIN
     V_appraisal :=
     CASE v_grade
     WHEN 'A' THEN 'Excellent'
     WHEN 'B' THEN 'Very Good'
     WHEN 'C' THEN 'Good'
     ELSE 'No such grade'
     END;
     DBMS_OUTPUT.PUT_LINE('Grade:'||v_grade||' Appraisal: '|| v_appraisal);
END;


--简单循环
DECLARE
       int NUMBER(2) :=0;
BEGIN
     LOOP
         int := int + 1;
         DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int);
         EXIT WHEN int =10;
     END LOOP;
END;


--WHILE 循环
DECLARE
       x NUMBER;
BEGIN
     x:= 1;
     WHILE x<10 LOOP
           DBMS_OUTPUT.PUT_LINE('X 的当前值为:'||x);
           x:= x+1;
     END LOOP;
END;


--数字式循环
BEGIN
     FOR int in 1..10 LOOP
         DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
     END LOOP;
END;

--数字式循环-循环变量自动减1
BEGIN
     FOR int in REVERSE 1..10 LOOP
         DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
     END LOOP;
END;


CREATE TABLE temp_table(num_col NUMBER);
DECLARE
       V_counter NUMBER := 10;
BEGIN
     INSERT INTO temp_table(num_col)VALUES (v_counter );
     FOR v_counter IN 20 .. 25 LOOP
         INSERT INTO temp_table (num_col ) VALUES ( v_counter );
     END LOOP;
     INSERT INTO temp_table(num_col)VALUES (v_counter );
     FOR v_counter IN REVERSE 20 .. 25 LOOP
         INSERT INTO temp_table (num_col ) VALUES ( v_counter );
     END LOOP;
END;

DROP TABLE temp_table;

select * from temp_table;



--标号和GOTO
DECLARE
       V_counter NUMBER := 1;
BEGIN
     LOOP
         DBMS_OUTPUT.PUT_LINE('V_counter 的当前值为:'||V_counter);
         V_counter := v_counter + 1;
         IF v_counter > 10 THEN
            GOTO l_ENDofLOOP;
         END IF;
     END LOOP;
<<l_ENDofLOOP>>
     DBMS_OUTPUT.PUT_LINE('V_counter 的当前值为:'||V_counter);
END;




select * from dept;


--游标参数的传递方法
DECLARE
     DeptRec dept%ROWTYPE;
     Dept_name dept.dname%TYPE;
     Dept_loc dept.loc%TYPE;
CURSOR c1 IS
       SELECT dname, loc FROM dept WHERE deptno <= 30;
CURSOR c2(dept_no NUMBER DEFAULT 10) IS
       SELECT dname, loc FROM dept WHERE deptno <= dept_no;
CURSOR c3(dept_no NUMBER DEFAULT 10) IS
       SELECT * FROM dept WHERE deptno <=dept_no;
BEGIN
     OPEN c1;
     LOOP
         FETCH c1 INTO dept_name, dept_loc;
         EXIT WHEN c1%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
         END LOOP;
     CLOSE c1;
     DBMS_OUTPUT.PUT_LINE('cl closed!');

     OPEN c2;
     LOOP
         FETCH c2 INTO dept_name, dept_loc;
         EXIT WHEN c2%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
     END LOOP;
     CLOSE c2;
     DBMS_OUTPUT.PUT_LINE('c2 closed!');
     
     OPEN c3(dept_no =>20);
     LOOP
         FETCH c3 INTO deptrec;
         EXIT WHEN c3%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(deptrec.deptno||'---'||deptrec.dname||'---'||deptrec.loc);
     END LOOP;
     CLOSE c3;
     DBMS_OUTPUT.PUT_LINE('c3 closed!');
END;


SELECT * FROM dept WHERE deptno <=30;


select * from emp;

--给工资低于1200的员工增加工资50
DECLARE
       v_empno emp.empno%TYPE;
       v_sal emp.sal%TYPE;
       CURSOR c IS SELECT empno, sal FROM emp;
BEGIN
     OPEN c;
     LOOP
         FETCH c INTO v_empno, v_sal;
         EXIT WHEN C%NOTFOUND;
         IF v_sal<=1200 THEN
            UPDATE emp SET sal=sal+50 WHERE empno=v_empno;
            DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');
         END IF;
         DBMS_OUTPUT.PUT_LINE('记录数:'||C%ROWCOUNT);
     END LOOP;
CLOSE c;
END;


--游标的FOR循环
DECLARE
       CURSOR c_sal IS SELECT empno, ename, sal FROM emp;
BEGIN
     --隐含打开游标
     FOR v_sal IN c_sal LOOP
     --隐含执行一个FETCH 语句
                         DBMS_OUTPUT.PUT_LINE( to_char(v_sal.empno)||'---'||v_sal.ename||'---'||to_char(v_sal.sal));
     --隐含监测c_sal%NOTFOUND
     END LOOP;
     --隐含关闭游标
END;


--当所声明的游标带有参数时,通过游标FOR 循环语句为游标传递参数
DECLARE
       CURSOR c1(dept_no NUMBER DEFAULT 10) IS
       SELECT dname, loc FROM dept WHERE deptno <= dept_no;
BEGIN
     DBMS_OUTPUT.PUT_LINE('dept_no 参数值为30:');
     FOR c1_rec IN c1(30) LOOP
         DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc);
     END LOOP;
     DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no 参数值10:');
     FOR c1_rec IN c1 LOOP
         DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc);
     END LOOP;
END;


--PL/SQL 还允许在游标FOR 循环语句中使用子查询来实现游标的功能。
BEGIN
     FOR c1_rec IN (SELECT dname, loc FROM dept) LOOP
         DBMS_OUTPUT.PUT_LINE(c1_rec.dname||'---'||c1_rec.loc);
     END LOOP;
END;


--删除EMP 表中某部门的所有员工,如果该部门中已没有员工,则在DEPT 表中删除该部门
DECLARE
       V_deptno emp.deptno%TYPE :=&p_deptno;
BEGIN
     DELETE FROM emp WHERE deptno=v_deptno;
     IF SQL%NOTFOUND THEN
        DELETE FROM dept WHERE deptno=v_deptno;
     END IF;
END;


select * from emp;
select * from dept;

--从EMP表中查询某部门的员工情况,将其工资最低定为1500
DECLARE
       V_deptno emp.deptno%TYPE :=&p_deptno;
       CURSOR emp_cursor IS SELECT empno, sal FROM emp WHERE deptno=v_deptno FOR UPDATE OF sal NOWAIT;
BEGIN
     FOR emp_record IN emp_cursor LOOP
     IF emp_record.sal< 1500 THEN
        UPDATE emp SET sal=1500 WHERE CURRENT OF emp_cursor;
     END IF;
     END LOOP;
     --COMMIT;
END;

select * from emp;

--预定义的异常处理-更新指定员工工资,如工资小于1500,则加100
DECLARE
       v_empno emp.empno%TYPE :=&empno;
       v_sal emp.sal%TYPE;
BEGIN
     SELECT sal INTO v_sal FROM emp WHERE empno=v_empno;
     IF v_sal<=1400 THEN
        UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');
     ELSE
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');
     END IF;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');
     WHEN TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;


select * from dept;

--删除指定部门的记录信息,以确保该部门没有员工。
INSERT INTO dept VALUES(50, 'FINANCE', 'CHICAGO');
DECLARE
       v_deptno dept.deptno%TYPE :=&deptno;
       e_deptno_remaining EXCEPTION;
       PRAGMA EXCEPTION_INIT(e_deptno_remaining,2292);
       /* 2292 是违反一致性约束的错误代码*/
BEGIN
     DELETE FROM dept WHERE deptno=v_deptno;
EXCEPTION
     WHEN e_deptno_remainingTHEN
         DBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');
     WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;


select * from emp;

--更新指定员工工资,增加100
DECLARE
       v_empno emp.empno%TYPE :=&empno;
       no_result EXCEPTION;
BEGIN
     UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
     IF SQL%NOTFOUND THEN
     RAISE no_result;
     END IF;
EXCEPTION
     WHEN no_result THEN
         DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;



--创建一个函数get_salary, 该函数检索指定部门的工资总和,其中定义了20991和20992号错误,分别处理参数为空和非法部门代码两种错误
CREATE TABLE errlog(Errcode NUMBER,Errtext CHAR(40));

CREATE OR REPLACE FUNCTION get_salary(p_deptno NUMBER)
     RETURN NUMBER AS
     V_sal NUMBER;
BEGIN
     IF p_deptno IS NULL THEN
        RAISE_APPLICATION_ERROR(-20991,'部门代码为空');
     ELSIF p_deptno<0 THEN
        RAISE_APPLICATION_ERROR(-20992,'无效的部门代码');
     ELSE
        SELECT SUM(sal) INTO v_sal FROM EMP WHERE deptno=p_deptno;
        RETURN V_sal;
     END IF;
END;

select * from emp;

select * from errlog;
delete errlog;


DECLARE
       V_salary NUMBER(7,2);
       V_sqlcode NUMBER;
       V_sqlerr VARCHAR2(512);
       Null_deptno EXCEPTION;
       Invalid_deptno EXCEPTION;
       PRAGMA EXCEPTION_INIT(null_deptno,-20991);
       PRAGMA EXCEPTION_INIT(invalid_deptno, -20992);
BEGIN
     V_salary :=get_salary(1);
     DBMS_OUTPUT.PUT_LINE('1 号部门工资:'||TO_CHAR(V_salary));
     BEGIN
          V_salary :=get_salary(-10);
     EXCEPTION
         WHEN invalid_deptno THEN
              V_sqlcode :=SQLCODE;
              V_sqlerr :=SQLERRM;
              INSERT INTO errlog(errcode,errtext) VALUES(v_sqlcode, v_sqlerr);
              COMMIT;
     END inner1;
     
     V_salary :=get_salary(20);
     DBMS_OUTPUT.PUT_LINE('20 号部门工资:'||TO_CHAR(V_salary));
     BEGIN
          V_salary :=get_salary(NULL);
     END inner2;
     
     V_salary :=get_salary(30);
     DBMS_OUTPUT.PUT_LINE('30 号部门工资:'||TO_CHAR(V_salary));
     EXCEPTION
     WHEN null_deptno THEN
          V_sqlcode :=SQLCODE;
          V_sqlerr :=SQLERRM;
          INSERT INTO errlog(errcode,errtext) VALUES(v_sqlcode, v_sqlerr);
          COMMIT;
    WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END outer;



--将ORACLE 错误代码及其信息存入错误代码表

CREATE TABLE errors(errnum NUMBER(4), errmsg VARCHAR2(100));

select * from errors;

DECLARE
       err_msg VARCHAR2(100);
BEGIN
     /* 得到所有ORACLE 错误信息*/
     FOR err_num IN -100.. 0 LOOP
     err_msg := SQLERRM(err_num);
     INSERT INTO errors VALUES(err_num, err_msg);
     END LOOP;
END;

DROP TABLE errors;




--查询ORACLE 错误代码
select * from emp;

BEGIN
     INSERT INTO emp(empno,ename, hiredate, deptno) VALUES(2222, 'Jerry', SYSDATE, 20);
     DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
     INSERT INTO emp(empno,ename, hiredate, deptno) VALUES(2222, 'Jerry', SYSDATE, 20);
     DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');
EXCEPTION
         WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;



--获取某部门的工资总和
CREATE OR REPLACE FUNCTION get_salaryf(
       Dept_no NUMBER,
       Emp_count OUT NUMBER)
       RETURN NUMBER IS
       V_sum NUMBER;
BEGIN
     SELECT SUM(sal), count(*) INTO V_sum, emp_count FROM emp WHERE deptno=dept_no;
     RETURN v_sum;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
     WHEN TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');      
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END get_salaryf;


select * from emp;

--内嵌函数的调用-第一种参数传递格式称为位置表示法
--计算某部门的工资总和
DECLARE
     V_num NUMBER;
     V_sum NUMBER;
BEGIN
     V_sum := get_salaryf(3, v_num);
     DBMS_OUTPUT.PUT_LINE('3 号部门工资总和:'||v_sum||',人数:'||v_num);
END;


--内嵌函数的调用-第二种参数传递格式称为名称表示法
DECLARE
     V_num NUMBER;
     V_sum NUMBER;
BEGIN
     V_sum :=get_salaryf(emp_count=> v_num, dept_no => 3);
     DBMS_OUTPUT.PUT_LINE('3 号部门工资总和:'||v_sum||',人数:'||v_num);
END;


--内嵌函数的调用-第三种参数传递格式称为混合表示法
CREATE OR REPLACE FUNCTION demo_fun(
     Name VARCHAR2,
     Age INTEGER,
     Sex VARCHAR2)
     RETURN VARCHAR2
     AS
     V_var VARCHAR2(32);
BEGIN
     V_var := name||':'||TO_CHAR(age)||'岁,'||sex;
     RETURN v_var;
END;

DECLARE
     Var VARCHAR(32);
BEGIN
     Var := demo_fun('user1', 30, sex => '男');
     DBMS_OUTPUT.PUT_LINE(var);
     
     Var := demo_fun('user2', age => 40, sex => '男');
     DBMS_OUTPUT.PUT_LINE(var);
     
     Var := demo_fun('user3', sex => '女', age => 20);
     DBMS_OUTPUT.PUT_LINE(var);
END;



--参数默认值
CREATE OR REPLACE FUNCTION demo_fun2(
       Name VARCHAR2,
       Age INTEGER,
       Sex VARCHAR2 DEFAULT '男')
       RETURN VARCHAR2
       AS
       V_var VARCHAR2(32);
BEGIN
     V_var := name||':'||TO_CHAR(age)||'岁,'||sex;
     RETURN v_var;
END;


DECLARE
       Var VARCHAR(32);
BEGIN
     Var := demo_fun2('user1', 30);
     DBMS_OUTPUT.PUT_LINE(var);
     Var := demo_fun2('user2', age => 40);
     DBMS_OUTPUT.PUT_LINE(var);
     Var := demo_fun2('user3', sex => '女', age => 20);
     DBMS_OUTPUT.PUT_LINE(var);
END;



--存储过程
--用户连接登记记录
CREATE table logtable (userid VARCHAR2(10), logdate date);

CREATE OR REPLACE PROCEDURE logexecution IS
BEGIN
     INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;


--删除指定员工记录
CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE)AS
       No_result EXCEPTION;
BEGIN
     DELETE FROM emp WHERE empno=v_empno;
     IF SQL%NOTFOUND THEN
        RAISE no_result;
     END IF;
     DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');
EXCEPTION
     WHEN no_result THEN
          DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END DelEmp;


--计算指定部门的工资总和,并统计其中的职工数量
CREATE OR REPLACE PROCEDURE proc_demo(
    Dept_no NUMBER DEFAULT 10,
    Sal_sum OUT NUMBER,
    Emp_count OUT NUMBER)
IS
BEGIN
     SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no;
EXCEPTION
     WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END proc_demo;


select * from emp;

DECLARE
     V_num NUMBER;
     V_sum NUMBER(8, 2);
BEGIN
     Proc_demo(1, v_sum, v_num);
          DBMS_OUTPUT.PUT_LINE('1 号部门工资总和:'||v_sum||',人数:'||v_num);
     Proc_demo(sal_sum=> v_sum, emp_count => v_num);
          DBMS_OUTPUT.PUT_LINE('10 号部门工资总和:'||v_sum||',人数:'||v_num);
END;



--建立本地过程,用于计算指定部门的工资总和,并统计其中的职工数量
DECLARE
    V_num NUMBER;
    V_sum NUMBER(8, 2);
    PROCEDURE proc_demo(
        Dept_no NUMBER DEFAULT 10,
        Sal_sum OUT NUMBER,
        Emp_count OUT NUMBER)
    IS
    BEGIN
        SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); 
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('发生其它错误!');
    END proc_demo;
BEGIN
    Proc_demo(1, v_sum, v_num);
    DBMS_OUTPUT.PUT_LINE('1 号部门工资总和:'||v_sum||',人数:'||v_num);
    Proc_demo(sal_sum=> v_sum, emp_count => v_num);
    DBMS_OUTPUT.PUT_LINE('10 号部门工资总和:'||v_sum||',人数:'||v_num);
END;

--授权执行权给相关的用户或角色
CREATE OR REPLACE PUBLIC SYNONYM DBMS_JOB FOR DBMS_JOB;
GRANT EXECUTE ON dbms_job TO PUBLIC WITH GRANT OPTION;

--与过程相关数据字典
select * from USER_SOURCE;
select * from ALL_SOURCE;
select * from DBA_SOURCE;
select * from USER_ERRORS;


DESC proc_demo;
desc emp;

DROP PROCEDURE logexecution;


select * from dept;



--包的创建和应用

--包定义的说明
--创建的包为demo_pack, 该包中包含一个记录变量DeptRec、两个函数和一个过程
CREATE OR REPLACE PACKAGE demo_pack
   IS
   DeptRec dept%ROWTYPE;
   V_sqlcode NUMBER;
   V_sqlerr VARCHAR2(2048);
   FUNCTION add_dept(
       dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
       RETURN NUMBER;
   FUNCTION remove_dept(dept_no NUMBER)
       RETURN NUMBER;
   PROCEDURE query_dept(dept_no IN NUMBER);
END demo_pack;

--包主体的创建
CREATE OR REPLACE PACKAGE BODY demo_pack
   IS
   Flag INTEGER;
   FUNCTION check_dept(dept_no NUMBER)
        RETURN INTEGER;

   FUNCTION add_dept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
        RETURN NUMBER
        IS
             BEGIN
                  IF check_dept(dept_no)=0 THEN
                       INSERT INTO dept VALUES(dept_no, dept_name, location);
                       RETURN 1;
                  ELSE
                       RETURN 0;
             END IF;
   EXCEPTION
        WHEN OTHERS THEN
             V_sqlcode := SQLCODE;
             V_sqlerr := SQLERRM;
             RETURN 1;
   END add_dept;
   
   FUNCTION remove_dept(dept_no NUMBER)
        RETURN NUMBER
        IS
            BEGIN
                V_sqlcode := 0;
                V_sqlerr := NULL;
                IF check_dept(dept_no)= 1 THEN
                   DELETE FROM dept WHERE deptno=dept_no;
                   RETURN 1;
                ELSE
                   RETURN 0;
                END IF;
            EXCEPTION WHEN OTHERS THEN
                V_sqlcode := SQLCODE;
                V_sqlerr := SQLERRM;
                RETURN 1;
   END remove_dept;
   
   PROCEDURE query_dept(dept_no IN NUMBER)
   IS
       BEGIN
            IF check_dept(dept_no)=1 THEN
                 SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
            END IF;
   END query_dept;
   
   FUNCTION check_dept(dept_no NUMBER)
       RETURN INTEGER
       IS
           BEGIN
                SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;
                IF flag > 0 THEN
                   Flag := 1;
                END IF;
                RETURN flag;
   END check_dept;
                
BEGIN
   V_sqlcode := NULL;
   V_sqlerr := NULL;
END demo_pack;

select * from dept;
delete dept where deptno=90;

--对包内共有元素的调用格式为:包名.元素名称
DECLARE
    Var NUMBER;
BEGIN
    Var := demo_pack.add_dept(90,'Administration', 'Beijing');
    IF Var =-1 THEN
        DBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);
    ELSIF Var =0 THEN
        DBMS_OUTPUT.PUT_LINE('该部门记录已经存在!');
    ELSE
        DBMS_OUTPUT.PUT_LINE('添加记录成功!');
        Demo_pack.query_dept(90);
        DBMS_OUTPUT.PUT_LINE(demo_pack.DeptRec.deptno||'---'||demo_pack.DeptRec.dname||'---'||demo_pack.DeptRec.loc);
        Var := demo_pack.remove_dept(90);
        IF Var =-1 THEN
            DBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);
        ELSE
            DBMS_OUTPUT.PUT_LINE('删除记录成功!');
        END IF;
    END IF;
END;


select * from emp;

--创建包emp_package
CREATE OR REPLACE PACKAGE emp_package
   IS
   TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   PROCEDURE read_emp_table(p_emp_table OUT emp_table_type);
END emp_package;


CREATE OR REPLACE PACKAGE BODY emp_package
    IS
    PROCEDURE read_emp_table(p_emp_table OUT emp_table_type)
    IS
    I BINARY_INTEGER := 0;
    BEGIN
         FOR emp_record IN ( SELECT * FROM emp ) LOOP
         P_emp_table(i) := emp_record;
         I := I + 1;
         END LOOP;
    END read_emp_table;
END emp_package;


DECLARE
    E_table emp_package.emp_table_type;
BEGIN
    Emp_package.read_emp_table(e_table);
    FOR I IN e_table.FIRST .. e_table.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(e_table(i).empno||'---'||e_table(i).ename);
    END LOOP;
END;



--子程序重载
--PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同
--的名称,但拥有不同的参数变量、参数顺序或参数数据类型。
CREATE OR REPLACE PACKAGE demo_pack1
    IS
    DeptRec dept%ROWTYPE;
    V_sqlcode NUMBER;
    V_sqlerr VARCHAR2(2048);
    FUNCTION query_dept(dept_no IN NUMBER)
         RETURN INTEGER;
    FUNCTION
         query_dept(dept_no IN VARCHAR2)
         RETURN INTEGER;
END demo_pack1;



CREATE OR REPLACE PACKAGE BODY demo_pack1
    IS
    FUNCTION check_dept(dept_no NUMBER)
         RETURN INTEGER
    IS
    Flag INTEGER;
    BEGIN
         SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;
         IF flag > 0 THEN
            RETURN 1;
         ELSE
            RETURN 0;
         END IF;
    END check_dept;
    
    FUNCTION check_dept(dept_no VARCHAR2)
             RETURN INTEGER
    IS
    Flag INTEGER;
    BEGIN
         SELECT COUNT(*) INTO flag FROM dept WHERE deptno=dept_no;
         IF flag > 0 THEN
            RETURN 1;
         ELSE
            RETURN 0;
         END IF;
    END check_dept;
    
    FUNCTION query_dept(dept_no IN NUMBER)
         RETURN INTEGER
    IS
    BEGIN
         IF check_dept(dept_no)=1 THEN
            SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
            RETURN 1;
         ELSE
            RETURN 0;
         END IF;
    END query_dept;
    
    FUNCTION query_dept(dept_no IN VARCHAR2)
         RETURN INTEGER
    IS
    BEGIN
         IF check_dept(dept_no)=1 THEN
            SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
            RETURN 1;
         ELSE
            RETURN 0;
         END IF;
    END query_dept;
END demo_pack1;


--删除包
DROP PACKAGE demo_pack1;

--包的管理
select * from DBA_SOURCE;
select * from USER_SOURCE;
select * from USER_ERRORS;
select * from DBA_OBJECTS;


select * from emp;
select * from emp_his;


insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(100,'Test',30.5,sysdate,10,'president',1);
insert into emp(empno,ename,sal,hiredate,comm,job,deptno) values(7788,'Test2',100.23,sysdate,10.2,'manager',2);

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;

CREATE OR REPLACE TRIGGER del_emp
     BEFORE DELETE ON HAIYA1.emp FOR EACH ROW
BEGIN
     --将修改前数据插入到日志记录表del_emp ,以供监督使用。
     INSERT INTO emp_his(deptno, empno, ename, job, sal, comm, hiredate)
            VALUES( :old.deptno, :old.empno, :old.ename , :old.job, :old.sal, :old.comm, :old.hiredate );
END;

--删除记录触发触发器
DELETE emp WHERE empno=7788;

DROP TABLE emp_his;
DROP TRIGGER del_emp;

--创建替代(Instead_of)触发器
--我们可以创建INSTEAD_OF 触发器来为DELETE 操作执行所需的处理,即删除EMP表中所有基准行
CREATE OR REPLACE VIEW emp_view AS
       SELECT deptno, count(*) total_employeer, sum(sal) total_salary FROM emp GROUP BY deptno;
       
select * from emp_view;

CREATE OR REPLACE TRIGGER emp_view_delete
       INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
     DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;

DELETE FROM emp_view WHERE deptno=1;

DROP TRIGGER emp_view_delete;
DROP VIEW emp_view;

--改变一个触发器的状态
ALTER TRIGGER del_emp DISABLE;
ALTER TRIGGER del_emp ENABLE;

--使表EMP上的所有TRIGGER失效/生效
ALTER TABLE emp DISABLE ALL TRIGGERS;
ALTER TABLE emp ENABLE ALL TRIGGERS;

--触发器和数据字典
SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,STATUS, ACTION_TYPE
FROM user_triggers;





--数据库触发器的应用举例
BEGIN
  --创建用于记录事件日志的数据表
  DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE TABLE eventlog(
    Eventname VARCHAR2(20) NOT NULL,
    Eventdate date default sysdate,
    Inst_num NUMBER NULL,
    Db_name VARCHAR2(50) NULL,
    Srv_error NUMBER NULL,
    Username VARCHAR2(30) NULL,
    Obj_type VARCHAR2(20) NULL,
    Obj_name VARCHAR2(30) NULL,
    Obj_owner VARCHAR2(30) NULL
    )
  ');

  --创建DDL 触发器trig4_ddl
  DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig_ddl
    AFTER CREATE OR ALTER OR DROP
    ON DATABASE
    DECLARE
    Event VARCHAR2(20);
    Typ VARCHAR2(20);
    Name VARCHAR2(30);
    Owner VARCHAR2(30);
    BEGIN
    
    --读取DDL 事件属性
    Event := SYSEVENT;
    Typ := DICTIONARY_OBJ_TYPE;
    Name := DICTIONARY_OBJ_NAME;
    Owner := DICTIONARY_OBJ_OWNER;
    
    --将事件属性插入到事件日志表中
    INSERT INTO HAIYA1.eventlog(eventname,obj_type, obj_name, obj_owner) VALUES(event, typ, name, owner);
    END;
  ');
  
  --创建LOGON、STARTUP 和SERVERERROR 事件触发器
  DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig_after
    AFTER LOGON OR STARTUP OR SERVERERROR
    ON DATABASE
    DECLARE
    Event VARCHAR2(20);
    Instance NUMBER;
    Err_num NUMBER;
    Dbname VARCHAR2(50);
    User VARCHAR2(30);
    BEGIN
    Event := SYSEVENT;
    IF
    event = ''LOGON'' THEN
    User := LOGIN_USER;
    INSERT INTO eventlog(eventname,username)
    VALUES(event, user);
    ELSIF event = ''SERVERERROR'' THEN
    Err_num := SERVER_ERROR(1);
    INSERT INTO eventlog(eventname,srv_error)
    VALUES(event, err_num);
    ELSE
    Instance := INSTANCE_NUM;
    Dbname := DATABASE_NAME;
    INSERT INTO eventlog(eventname,inst_num, db_name) VALUES(event, instance, dbname);
    END IF;
    END;
  ');
  
  --创建LOGOFF 和SHUTDOWN 事件触发器
  DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig_before
    BEFORE LOGOFF OR SHUTDOWN
    ON DATABASE
    DECLARE
    Event VARCHAR2(20);
    Instance NUMBER;
    Dbname VARCHAR2(50);
    User VARCHAR2(30);
    BEGIN
    Event := SYSEVENT;
    IF event = ''LOGOFF'' THEN
    User := LOGIN_USER;
    INSERT INTO eventlog(eventname,username)
    VALUES(event, user);
    ELSE
    Instance := INSTANCE_NUM;
    Dbname := DATABASE_NAME;
    INSERT INTO eventlog(eventname,inst_num, db_name) VALUES(event, instance, dbname);
    END IF;
    END;
  ');
END;


CREATE TABLE mydata(mydate NUMBER);
drop table mydata;

select * from mydata;

SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error
FROM eventlog;


select * from eventlog;
drop table eventlog;

delete eventlog;

--删除触发器
DROP TRIGGER trig_ddl;
DROP TRIGGER trig_before;
DROP TRIGGER trig_after;

--删除记录事件日志的数据表
DROP TABLE eventlog;

plSql介绍

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
plsql是oracle的利器,下面介绍下一些配置,作为备忘。 1:配置文件   需要定位到:XXX\oracle\ap
解析: create procedure sp_pro3(spName varchar2,newSal number) is ---不用指定number的大小,只
问题:PL/SQL插入和更新乱码。 解决乱码问题需要关注的三点: 1. Oracle数据库内部的字符集 2. Orac
oracle plsql cursor usage 目录 一、游标的相关概念及特性   1.定义   2.游标的分类   3.游
安装pl sql developer 下载instant-client,这样可以不用安装oracle客户端就能连接oracle数据库,地
1.使用PL/SQL中经常性需要输入select * from 这样类似的语句。能不能配置一个快捷键,比如输入s ,
Oracle/PLSQL: Trunc Function (with dates): http://www.techonthenet.com/oracle/functions/trunc
Oracle/PLSQL: Trunc Function (with dates): http://www.techonthenet.com/oracle/functions/trunc
使用PL/SQL中经常性需要输入select * from或者select count(*) from 这样类似的语句。能不能配置一
不要安装在有括号的目录里面比如(Program Files(x86))。 打开之后之后填写 Username: Password: D
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号