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;