oracle存储过程个人笔记习惯养成-

01 不带参数存储过程

--创建不带参数(不能加括号表示无参)存储过程 create or replace procedure procedure1 as --as与is区别：在存储过程（PROCEDURE）和函数(FUNCTION)中没有区别，在视图（VIEW）中只能用AS不能用IS，在游标(CURSOR)中只能用IS不能用AS。 --声明部分 countint number; --执行部分 begin select count(1) into countint from emp; dbms_output.put_line('emp表共有' || countint || '行记录'); --异常处理部分 exception      when others     then null; end; 

测试如图：

02 带参数存储过程

--带参数存储过程 --in 参数：用于接收参数，在子程序内部，不能进行修改。默认的参数模式：in --out 参数：输出模式的参数，用于输出值，会忽略传入的值。在子程序内部可以对其进行修改。调用时参数需要使用变量 --in out 参数：能接收传入的实参值；在子程序内部可以修改,可以输出。调用时参数需要使用变量 --当存储过程中含有输出参数时，调用该存储过程时必须通过begin...end语句块来实现。不能通过exec或call调用 --in 参数 drop procedure procedure2; create or replace procedure procedure2(empno_in number) as enametmp EMP.ENAME%type; begin select ename into enametmp from emp where empno = empno_in;     dbms_output.put_line('找到名称' || enametmp); --    empno_in:=0;--编译不通过 exception      when others then      dbms_output.put_line('没有找到该名称'); end; --in out 参数 drop procedure procedure3; create or replace procedure procedure3(empno_in in out number) as enametmp EMP.ENAME%type; begin select ename into enametmp from emp where empno = empno_in;     dbms_output.put_line('找到名称' || enametmp);     empno_in:=0;--调用时参数需要使用变量 exception      when others then      dbms_output.put_line('没有找到该名称'); end; --in参数；out 参数 create or replace procedure procedure4(empno_in in number,outename out varchar2,outmsg out varchar2) as begin select ename into outename from emp where empno = empno_in;     outmsg:='找到名称' || outename; exception      when others then      outmsg:='没有找到该名称'; end; 

测试如图：

03 参数默认值存储过程

--带有默认参数存储过程 drop procedure procedure5; create or replace procedure procedure5( no varchar2, name varchar2, gentle varchar2 default '男', age number default 0, dept varchar2 ) as begin insert into stu values(no,name,gentle,age,dept); commit; end; 

测试如图：

ALTER TABLE STU DROP PRIMARY KEY CASCADE; DROP TABLE STU CASCADE CONSTRAINTS; CREATE TABLE STU ( NO      VARCHAR2(10 BYTE),   NAME    VARCHAR2(10 BYTE),   GENTLE  VARCHAR2(2 BYTE),   AGE     NUMBER(2),   DEPT    VARCHAR2(20 BYTE) ); -------- DROP TABLE EMP CASCADE CONSTRAINT; CREATE TABLE EMP( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ); COMMENT ON COLUMN EMP.EMPNO IS '编码'; COMMENT ON COLUMN EMP.ENAME IS '名称'; COMMENT ON COLUMN EMP.JOB IS '工作'; COMMENT ON COLUMN EMP.MGR IS '主管'; COMMENT ON COLUMN EMP.HIREDATE IS '聘用时间'; COMMENT ON COLUMN EMP.SAL IS '工资'; COMMENT ON COLUMN EMP.COMM IS '提成'; COMMENT ON COLUMN EMP.DEPTNO IS '部门编码'; INSERT INTO EMP SELECT 7369 AS EMPNO, 'SMITH' AS ENAME, 'CLERK' AS JOB, 7902 AS MGR, TO_DATE('19801217','yyyy-MM-dd') AS HIREDATE, 800 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL SELECT 7499 AS EMPNO, 'ALLEN' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810220','yyyy-MM-dd') AS HIREDATE, 1600 AS SAL, 300 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL SELECT 7521 AS EMPNO, 'WARD' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810222','yyyy-MM-dd') AS HIREDATE, 1250 AS SAL, 500 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL SELECT 7566 AS EMPNO, 'JONES' AS ENAME, 'MANAGER' AS JOB, 7839 AS MGR, TO_DATE('19810402','yyyy-MM-dd') AS HIREDATE, 2975 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL SELECT 7654 AS EMPNO, 'MARTIN' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810928','yyyy-MM-dd') AS HIREDATE, 1250 AS SAL, 1400 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL SELECT 7698 AS EMPNO, 'BLAKE' AS ENAME, 'MANAGER' AS JOB, 7839 AS MGR, TO_DATE('19810501','yyyy-MM-dd') AS HIREDATE, 2850 AS SAL, NULL AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL SELECT 7782 AS EMPNO, 'CLARK' AS ENAME, 'MANAGER' AS JOB, 7839 AS MGR, TO_DATE('19810609','yyyy-MM-dd') AS HIREDATE, 2450 AS SAL, NULL AS COMM, 10 AS DEPTNO FROM DUAL UNION ALL SELECT 7788 AS EMPNO, 'SCOTT' AS ENAME, 'ANALYST' AS JOB, 7566 AS MGR, TO_DATE('19821209','yyyy-MM-dd') AS HIREDATE, 3000 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL SELECT 7839 AS EMPNO, 'KING' AS ENAME, 'PRESIDENT' AS JOB, NULL AS MGR, TO_DATE('19811117','yyyy-MM-dd') AS HIREDATE, 5000 AS SAL, NULL AS COMM, 10 AS DEPTNO FROM DUAL UNION ALL SELECT 7844 AS EMPNO, 'TURNER' AS ENAME, 'SALESMAN' AS JOB, 7698 AS MGR, TO_DATE('19810908','yyyy-MM-dd') AS HIREDATE, 1500 AS SAL, 0 AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL SELECT 7876 AS EMPNO, 'ADAMS' AS ENAME, 'CLERK' AS JOB, 7788 AS MGR, TO_DATE('19830112','yyyy-MM-dd') AS HIREDATE, 1100 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL SELECT 7900 AS EMPNO, 'JAMES' AS ENAME, 'CLERK' AS JOB, 7698 AS MGR, TO_DATE('19811203','yyyy-MM-dd') AS HIREDATE, 950 AS SAL, NULL AS COMM, 30 AS DEPTNO FROM DUAL UNION ALL SELECT 7902 AS EMPNO, 'FORO' AS ENAME, 'ANALYST' AS JOB, 7566 AS MGR, TO_DATE('19811203','yyyy-MM-dd') AS HIREDATE, 3000 AS SAL, NULL AS COMM, 20 AS DEPTNO FROM DUAL UNION ALL SELECT 7934 AS EMPNO, 'MILLER' AS ENAME, 'CLERK' AS JOB, 7782 AS MGR, TO_DATE('19820123','yyyy-MM-dd') AS HIREDATE, 1300 AS SAL, NULL AS COMM, 10 AS DEPTNO FROM DUAL; COMMIT;