包 存储过程 触发器 游标

create or replace package emp_package as V_PI constant number:=3.14;--定义常量 type v_cur is ref cursor;--引用游标定义 --定义过程procedure emp_insert(p_ENAME    VARCHAR2,                       p_JOB      VARCHAR2,                       p_MGR      NUMBER,                       p_HIREDATE DATE,                       p_SAL      NUMBER,                       p_COMM     NUMBER,                       p_DEPTNO   NUMBER,                       p_EMPNO out NUMBER);procedure emp_update(p_EMPNO    NUMBER,                       p_ENAME    VARCHAR2,                       p_JOB      VARCHAR2,                       p_MGR      NUMBER,                       p_HIREDATE DATE,                       p_SAL      NUMBER,                       p_COMM     NUMBER,                       p_DEPTNO   NUMBER); procedure emp_delete(p_EMPNO NUMBER); procedure emp_load(p_EMPNO NUMBER,p_cur out v_cur);procedure emp_list(p_cur out v_cur);                       end;B、按照规范完成包主体的书写，必须实现规范中的CRUD操作【此题6分】create or replace package body emp_package as--定义过程procedure emp_insert(p_ENAME    VARCHAR2,                       p_JOB      VARCHAR2,                       p_MGR      NUMBER,                       p_HIREDATE DATE,                       p_SAL      NUMBER,                       p_COMM     NUMBER,                       p_DEPTNO   NUMBER,                       p_EMPNO out NUMBER) is begin  select seq_emp.nextval into p_empno from dual;  insert into emp    (empno,ename, job, mgr, hiredate, sal, comm, deptno)  values    (p_empno,p_ename, p_job, p_mgr, p_hiredate, p_sal, p_comm, p_deptno);  commit; exception when others then   rollback; end emp_insert; procedure emp_update(p_EMPNO    NUMBER,                       p_ENAME    VARCHAR2,                       p_JOB      VARCHAR2,                       p_MGR      NUMBER,                       p_HIREDATE DATE,                       p_SAL      NUMBER,                       p_COMM     NUMBER,                       p_DEPTNO   NUMBER) is begin update emp set emp.ename=p_ENAME,emp.job=p_JOB,emp.mgr=p_MGR,emp.hiredate=p_HIREDATE,emp.sal=p_SAL,emp.comm=p_COMM,emp.deptno=p_DEPTNO where emp.empno=p_EMPNO;  commit; exception when others then   rollback; end emp_update;procedure emp_delete(p_EMPNO number) isbegin delete emp where empno=p_EMPNO; commit; exception when others then   rollback; end emp_delete; procedure emp_load(p_EMPNO NUMBER,p_cur out v_cur) isbeginopen p_cur for select * from emp where emp.empno=p_EMPNO;--select empno,ename,job, mgr, hiredate, sal, comm, deptno into v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8 from emp where empno=p_EMPNO;--dbms_output.put_line(to_char(v_5,'yyyy-mm-dd'));end emp_load;procedure emp_list(p_cur out v_cur) is begin open p_cur for select * from emp; end emp_list;                        end emp_package;

触发器

 

  create or replace trigger tr_t_insertbefore insert or update or delete on tfor each rowdeclare  v_key number;begin  if inserting then  select seq_t.nextval into v_key from dual;  :new.tid:=v_key;  dbms_output.put_line('insert'); elsif updating then   if :new.tname='fuck' then --映像     :new.tname:='xxx';   dbms_output.put_line('update');   end if; elsif deleting then    if to_char(sysdate,'d')='7' then     dbms_output.put_line('disable');     --insert into t(tname)values(:old.tname);   else     dbms_output.put_line('delete.....');   end if; end if;end;

-游标只是方便用户对数据库中的数据一条一条的进行处理,pl/sql中,select 语句返回多条记录时,必须用到游标. -游标的类型有两种：隐式和显式 

DECLARE  CURSOR c1 IS    SELECT empno, ename, job      FROM emp     WHERE deptno = 20; 再open,fetch,close  PL/SQL包含隐式游标和显式游标等两种游标类型，其宗隐式游标用于处理select into和DML语句，而显示游标则专门用于处理select语句返回的多行数据。使用显式游标为了处理select语句返回的多行数据，可以使用显式游标，使用显式游标包括定义游标，打开游标，提取数据和关闭游标四个阶段。例子：declare  curosr emp_cursor is    select ename,sal from emp       where deptno=10;  v_ename emp.ename%type;  v_sal emp.sal%type;begin  open emp_cursor;  loop   fetch emp_cursor into v_ename,v_sal;  exit when emp_cursor%notfound;  dbms_output.put_line(v_name||': '||v_sal);   end loop;   close emp_cursor;end;

比如说你需要把某一张或者几张表的数据遍历一边。才会用到游标。只是单纯的逻辑判断及处理的话，就不需要了。