oracle 函数、包的定义及使用

方法定义：
CREATE OR REPLACE 
function insert_emp 
(v_EMPNO NUMBER,v_ENAME VARCHAR2,v_JOB VARCHAR2,v_MGR NUMBER,v_HIREDATE DATE,v_SAL NUMBER,v_COMM NUMBER,v_DEPTNO NUMBER)
return NUMBER
IS
BEGIN
INSERT into EMP values(v_empno,v_ENAME,v_JOB,v_MGR,v_HIREDATE,v_SAL,v_COMM,v_DEPTNO);
return 1;
end insert_emp;


创建存储过程调用方法：
create or replace PROCEDURE function_test(
v_EMPNO NUMBER,
v_ENAME VARCHAR2,
v_JOB VARCHAR2,
v_MGR NUMBER,
v_HIREDATE DATE,
v_SAL NUMBER,
v_COMM NUMBER,
v_DEPTNO NUMBER,
v_out out number)
AS
BEGIN
v_out:= insert_emp(v_EMPNO,v_ENAME,v_JOB,v_MGR,v_HIREDATE,v_SAL,v_COMM,v_DEPTNO);
end;


调用存储过程：
DECLARE
p_out number;
BEGIN
 function_test('7347', 'Kitty', 'CLERK', '7902', TO_DATE('1980-12-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '800', null, '20',p_out);

end;

包：

1、定义

create or replace package my_1
is
PROCEDURE sayhello(vname varchar2);
end;

2、实现包体
CREATE or replace package body my_1
is
PROCEDURE sayhello(vname varchar2)
IS
BEGIN
dbms_output.putline('hello'||vname);
end;
end;

3、创建存储过程调用
create or replace PROCEDURE package_test(p1 string)
AS
BEGIN
my_1.sayhello(p1);
end;


BEGIN
package_test('a');
end;

Sequence

CREATE sequence num
INCREMENT BY 1
START WITH 1
nomaxvalue
nocycle
cache 10;

主要有num.nextval,num.currval属性

游标：

DECLARE
 cursor result is SELECT * from STUDENT for UPDATE ;
 resultInfo STUDENT%rowtype;
 i_Status STUDENT.status%type;
BEGIN
 for resultInfo IN result loop
  if resultInfo.MAJOR1SCORE >60 AND resultInfo.MAJOR2SCORE >60
    and resultInfo.MAJOR3SCORE >60 AND resultInfo.totalScore >400 THEN      
   UPDATE STUDENT set STATUS= '1' WHERE current OF result;
   dbms_output.put_line(resultInfo.sid||'-'||resultInfo.sname||'-已录取');
 ELSE
   UPDATE STUDENT set STATUS= '0' WHERE current OF result;
   dbms_output.put_line(resultInfo.sid||'-'||resultInfo.sname||'-未录取');
 end if;
 end loop;
END;