oracle笔记

 

Database = D:\work\100知识管理\DR_JAVA_090912.nyf

创建用户和表 1、创建用户orcl_admin a、用system登录 C:\>sqlplus system/drsoft@orcl35 b、创建orcl_admin SQL> create user orcl_admin   2  identified by orcl_admin   3  / 用户已创建。 c、用刚刚创建的orcl_admin 用户登录是登不上的，因为没有赋予conn权限 SQL> connect orcl_admin/orcl_admin@orcl35 ERROR: ORA-01045: user ORCL_ADMIN lacks CREATE SESSION privilege; logon denied ------------------------------------------------------------------------------ 2、创建完用户之后还需要给用户赋予权限 SQL> show user USER 为 "SYSTEM"  ， 确认当前用户是system管理员 SQL> grant create session, dba to orcl_admin; 授权成功。 create session 是一个系统权限，它赋予用户连接数据库的能力，即，若用户没有这个权限，则用户无法登录oracle数据库。 dba 是一个拥有超过120多个系统权限的集合，用户拥有dba权限就相当于拥有了这120多个系统权限，几乎可以完成所有的管理工作。 用orcl_admin再次登录： SQL> conn orcl_admin/orcl_admin@orcl35 已连接。 SQL> show user USER 为 "ORCL_ADMIN" ------------------------------------------------------------------------------ 3、修改用户 3.1 修改用户密码（密码丢失或就是为了修改） SQL> conn system/drsoft@orcl35 已连接。 SQL> show user USER 为 "SYSTEM" 创建用户 hr_audit SQL> create user hr_audit   2  identified by hr_audit   3  / 用户已创建。 用hr_audit 登录： SQL> conn hr_audit/hr_audit@orcl35 ERROR: ORA-01045: user HR_AUDIT lacks CREATE SESSION privilege; logon denied system再次登录： SQL> conn system/drsoft@orcl35 已连接。 SQL> show user USER 为 "SYSTEM" 为hr_audit授权： SQL> grant create session, resource to hr_audit; 授权成功。 SQL> conn hr_audit/hr_audit@orcl35 已连接。 SQL> show user USER 为 "HR_AUDIT" 修改密码： SQL> alter user hr_audit identified by drsoft; 用户已更改。 SQL> conn hr_audit/drsoft@orcl35 已连接。 SQL> show user USER 为 "HR_AUDIT" 用system为oe用户解锁： SQL> alter user oe account unlock; 用户已更改。 ------------------------------------------------------------------------------ 4、修改表空间 查看所有表空间： SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX TEMP USERS EXAMPLE TBS_EASAST 已选择7行。 修改用户hr_audit的表空间： SQL> alter user hr_audit   2  default tablespace users   3  temporary tablespace temp   4  / 用户已更改。 4.2 修改表空间配额 首先用hr_audit用户在users表空间创建表: SQL> create table student   2  (   3  sid number(4),   4  sname varchar2(30)   5  )   6  / 表已创建。 由于创建hr_audit用户时，授权了resource权限集合，其中包含create table权限，就可以使用create table 创建表。 修改hr_audit表空间配额： SQL> alter user hr_audit   2  quota unlimited on users   3  quota 10M on temp   4  quota 0M on system   5  / alter user hr_audit * 第 1 行出现错误: ORA-30041: 无法在表空间上授予限额 SQL> alter user hr_audit   2  quota 10M on users   3  quota 0M on system   4  / 用户已更改。 在oracle 10.2.1版本以后，不允许在temp表空间授予配额 Cause: User tried to grant quota on an undo or temporary tablespace ------------------------------------------------------------------------------ 5、删除用户 SQL> drop user hr_audit; 用户已删除。 SQL> select * from student; 未选定行 SQL> desc student; 名称                                      是否为空? 类型 ----------------------------------------- -------- -------------- SID                                                NUMBER(4) SNAME                                              VARCHAR2(30) 说明：由于在删除用户hr_audit时没有加入 CASCADE关键字，那么用户创建的对象，如：表 student是不能自动删除的。 那么student表就成为了垃圾数据，需要手动清除： SQL> drop table student; 表已删除。 ------------------------------------------------------------------------------ 实验： a、创建dropme用户并分配表空间及配额 b、授权create session、create table权限 c、用dropme用户创建表 d、用dropme用户删除表，要加上CASCADE关键字，可以把用户创建的对象一并删除。 ------------------------------------------------------------------------------ 通过oracle内置视图查询数据字典： SQL> set linesize 200 SQL>      OWNER                          TABLE_NAME                     TABLESPACE_NAME ------------------------------ ------------------------------ --------------- SCOTT                          DEPT                           USERS SCOTT                          EMP                            USERS SCOTT                          BONUS                          USERS SCOTT                          SALGRADE                       USERS HR_AUDIT                       STUDENT                        USERS HR_AUDIT                       DEPT_COPY                      USERS HR_AUDIT                       EMP_COPY                       USERS 已选择7行。 数据类型 1、NUMBER     number(10,2) 表示总的数字长度为10，小数点后有2位数字；     number , 默认是38位，等价于 number(38)         2、 CHAR     定长字符，不足的部分用空格填充，最大长度2000个字节。 3、VARCHAR2     变长字符，最大长度4000个字节。 4、大对象 BLOB    存储二进制数据，如：音频、视频等。最大长度可达4G。 CLOB    存储字符数据，当字符长度大于4000个，就应该用CLOB。 5、DATE a、利用TO_DATE(日期,'日期格式') 把字符串类型的日期转换为DATE类型 SQL> select to_date('2012-01-01','YYYY-MM-DD') as worlddate from dual; b、sysdate 是oracle内置的对象，表示当前日期。 SQL> select sysdate from dual; dual 表是空表，一般用它做运算，获取时间、测试数据库是否连接正常。 c、可以通过TO_CHAR(日期, '日期格式') 把日期类型转换为字符串类型 SQL> select TO_CHAR(sysdate,'YYYY-MM-DD') as curdate from dual; CURDATE ----------- 2011-06-26 SQL> select TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') as curdate from dual; CURDATE ------------------- 2011-06-26 11:39:43 ----------------------------------------------------------------------- 6、创建表： 6.1 首先创建用户 SQL> create user dropme identified by dropme   2  default tablespace users   3  temporary tablespace temp   4  / 用户已创建。 6.2 为用户授权 SQL> grant create session, create table to dropme; 授权成功。 SQL> alter user dropme   2  quota 10M on users   3  / 用户已更改。 6.3 用新建用户登录 SQL> conn dropme/dropme@orcl35 已连接。 SQL> show user USER 为 "DROPME" 6.4 用dropme创建表 SQL> create table student(   2  sid number(4) not null,   3  sname varchar2(30) not null,   4  study_date date default sysdate,   5  tel varchar2(20),   6  constraint student_pk   7     primary key(sid)   8  )   9  / 表已创建。 通过desc查看表结构： SQL> desc student; 名称                                      是否为空?  类型 ----------------------------------------- -------- --------------- SID                                       NOT NULL  NUMBER(4) SNAME                                     NOT NULL VARCHAR2(30) STUDY_DATE                                           DATE TEL                                                   VARCHAR2(20) ------------------------------------------------------------------------------------- 7、约束 7.1 把scott用户的dept表的SELECT 授权给hr_audit用户，达到hr_audit用户可以查询scott.DEPT表的内容。 SQL> select table_name from user_tables; TABLE_NAME ------------------------------ DEPT EMP BONUS SALGRADE SQL> grant select on dept to hr_audit; 授权成功。 7.2  用户hr_audit 就可以查询scott.DEPT表 SQL> select * from scott.dept;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON 7.3 用户hr_audit 通过create table as select 语句创建一个和scott.dept表结构一模一样的表。 SQL> create table dept_copy as select * from scott.dept; 表已创建。 总结：通过create table as select 语句可以复制一个表的结构和内容。但复制的表没有把DEPTNO的主键约束复制过来。 SQL> insert into dept_copy values(40, 'dev', 'beijing'); 已创建 1 行。 SQL> select * from dept_copy;     DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON         40 dev            beijing 发现复制的表dept_copy可以允许插入DEPTNO为40的记录，说明主键约束没有复制过来。 在目前状态下为dept_copy表增加主键，但是上面增加了一条记录的DEPTNO=40的数据，故先删除 SQL> delete from dept_copy where dname='dev'; SQL> alter table dept_copy   2  add constraint dept_copy_pk   3  primary key(deptno)   4  / 表已更改。 ------------------------------------------------------------------------------------- 8、外键 8.1 把scott的emp的select权限授权给hr_audit用户 SQL> grant select on emp to hr_audit; 授权成功。 8.2 通过create table tab_name as select 创建scott.emp表的副本 SQL> create table emp_copy as select * from scott.emp; 表已创建。 SQL> desc emp_copy 名称                                      是否为空? 类型 ----------------------------------------- -------- ------------------ 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) 8.3  现在已经具备了dept_copy、emp_copy两个表，建立emp_copy的外键， emp_copy.depno -> dept_copy.deptno SQL> alter table emp_copy   2  add constraint dept_copy_fk   3  foreign key(deptno) references dept_copy(deptno)   4  / 表已更改。 SQL> insert into emp_copy(deptno) values(60); insert into emp_copy(deptno) values(60) * 第 1 行出现错误: ORA-02291: 违反完整约束条件 (HR_AUDIT.DEPT_COPY_FK) - 未找到父项关键字 8.4 唯一性约束 为表emp_copy增加新的列 ssn： SQL> alter table emp_copy   2  add(ssn varchar2(9))   3  / 表已更改。 SQL> desc emp_copy; 名称                                      是否为空? 类型 ----------------------------------------- -------- ----------------- 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) SSN                                                VARCHAR2(9) 为表emp_copy增加ssn列的唯一性约束： SQL> alter table emp_copy   2  add constraint emp_copy_ssn_uk   3  unique(ssn)   4  / 表已更改。 实验：向emp_copy表增加两条记录，插入两条ssn的值一样的数据： SQL> insert into emp_copy(empno, ssn) values(7940, 'abc'); 已创建 1 行。 SQL> insert into emp_copy(empno, ssn) values(7941, 'abc'); insert into emp_copy(empno, ssn) values(7941, 'abc') * 第 1 行出现错误: ORA-00001: 违反唯一约束条件 (HR_AUDIT.EMP_COPY_SSN_UK) ------------------------------------------------------------------------------------- check 约束 首先为emp_copy表增加一个gender列： SQL> alter table emp_copy   2  add(gender char(1))   3  / 表已更改。 为gender列增加检查约束，gender的取值只能是'F'或'M' SQL> alter table emp_copy   2  add constraint ck_gender   3  check(gender in('F','M'))   4  / 表已更改。 验证： 想emp_copy表增加一行数据，其gender的值为'O': SQL> insert into emp_copy(empno, gender) values(7392, 'O'); insert into emp_copy(empno, gender) values(7392, 'O') * 第 1 行出现错误: ORA-02290: 违反检查约束条件 (HR_AUDIT.CK_GENDER) SQL> insert into emp_copy(empno, gender) values(7392, 'F'); 已创建 1 行。 ------------------------------------------------------------------------------------- 9、复制表结构 SQL>create table emp_copy as select * from scott.emp where 1=0;  /--PL/SQL语句吕晓东 08:52:32 (多人发送)   --以sysdba身份登录 C:\>sqlplus / as sysdba   --创建表空间 SQL> CREATE SMALLFILE TABLESPACE TBS_DRTEST DATAFILE 'E:\oradata\orcl\tbs_drtest.dbf' SIZE 10M AUTOEXTEND ON;   Tablespace created.   --创建用户 drtest/drsoft CREATE USER drtest PROFILE DEFAULT IDENTIFIED BY drsoft DEFAULT TABLESPACE tbs_drtest TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;   User created.   --给用户指定表空间 alter user drtest default tablespace tbs_drtest;   --授权 grant connect,resource to drtest; User created. --用drtest/当然soft登录 SQL> conn drtest/drsoft Connected.    --创建表 CREATE TABLE emp (   id int NOT NULL,   hiredate date not null,   PRIMARY KEY  (id) ) tablespace tbs_drtest; -----用户drtest编写PL/SQL程序块 SQL> conn drtest/drsoft Connected.   SQL> set serveroutput on  //打开服务器的输出显示,即打开oracle自带的输出方法dbms_output。 SQL> declare   2  l_text varchar2(100);   3  begin   4  l_text := 'Hello, World!';   5  dbms_output.put_line(l_text);   6  exception   7  when others then   8  dbms_output.put_line('We encountered an exception!');   9  raise;  10  end;  11  / Hello, World!   PL/SQL procedure successfully completed. -------%type和%rowtype来声明变量     ---- PL/SQL集合——记录 SQL> conn scott/tiger Connected. SQL> show user USER is "SCOTT" SQL> desc dept  Name                                      Null?    Type  ----------------------------------------- -------- ------------------  DEPTNO                                    NOT NULL NUMBER(2)  DNAME                                              VARCHAR2(14)  LOC                                                VARCHAR2(13)   SQL> set serverout on SQL> declare   2  type dept_record_type is record(   3  deptno number,   4  dname  varchar2(14),   5  loc varchar2(13)   6  );   7  l_my_dept dept_record_type;   8  begin   9  l_my_dept.deptno := 80;  10  l_my_dept.dname := 'dev';  11  l_my_dept.loc := 'TangShan';  12  dbms_output.put_line('MyDept is :');  13  dbms_output.put_line(l_my_dept.deptno);  14  dbms_output.put_line(l_my_dept.dname);  15  dbms_output.put_line(l_my_dept.loc);  16  end;  17  / MyDept is : 80 dev TangShan   PL/SQL procedure successfully completed. --使用%type和%rowtype来声明变量，而不必规定特定的数据类型 SQL> declare   2    type dept_rec_type is record(   3    deptno dept.deptno%type,   4    deptname dept.dname%type,   5    deptloc dept.loc%type   6    );   7    mydept dept_rec_type;   8    begin   9    mydept.deptno := 90;  10    mydept.deptname := 'hr';  11    mydept.deptloc := 'tangshang';  12    insert into dept values(mydept.deptno,mydept.deptname,mydept.deptloc);  13    end;  14    /   PL/SQL procedure successfully completed.   SQL> select * from dept;       DEPTNO DNAME          LOC ---------- -------------- -------------         10 ACCOUNTING     NEW YORK         20 RESEARCH       DALLAS         30 SALES          CHICAGO         40 OPERATIONS     BOSTON         90 hr             tangshang   SQL>   ---游标 SQL> set serveroutput on SQL> SQL> declare   2      cursor emp_cur(deptid in number)   3          is select * from emp where deptno = deptid;   4   5      l_emp emp%rowtype;   6  begin   7      -- 打开游标   8      open emp_cur(30);   9  10      -- 通过游标遍历数据集  11      loop  12          fetch emp_cur into l_emp; -- 获取一条数据到l_emp  13          exit when emp_cur%notfound;  14  15          dbms_output.put_line(TO_CHAR(l_emp.empno) || ',' || l_emp.ename || ',' || TO_CHAR(l_emp.deptno));  16      end loop;  17  18      -- 关闭游标  19      close emp_cur;  20  end;  21  / 7499,ALLEN,30 7521,WARD,30 7654,MARTIN,30 7698,BLAKE,30 7844,TURNER,30 7900,JAMES,30   PL/SQL procedure successfully completed.   SQL> -------用游标实现查询工资是1500以上的员工信息 SQL>   set serveroutput on  --设置打印输出参数，on表示打印输出显示 SP2-0735: unknown SET option beginning "--设置打印..." SQL> SQL> declare   2      tmpsal scott.emp.sal%type;   3      cursor csemp is   4          select * from scott.emp where sal >= tmpsal order by sal;   5      cursorrec csemp%rowtype;   6  begin   7      tmpsal := 1500;   8      open csemp; --打开游标   9      fetch csemp into cursorrec;  10      dbms_output.put_line('员工编号 姓名 工资  部门编号');  11      while(csemp%found) loop  12          dbms_output.put_line(to_char(cursorrec.empno) || '   ' || cursorrec.ename || '   ' || to_char(cursorrec.sal) || '   ' || to_char(cursorrec.deptno));  13          fetch csemp into cursorrec; --移动游标获取下一条纪录到cursorrec变量  14      end loop;  15      close csemp; --关闭游标  16  end;  17  / 员工编号 姓名 工资  部门编号 7844   TURNER   1500   30 7499   ALLEN   1600   30 7782   CLARK   2450   10 7698   BLAKE   2850   30 7566   JONES   2975   20 7788   SCOTT   3000   20 7902   FORD   3000   20 7839   KING   5000   10   PL/SQL procedure successfully completed.   ----------------------------------------------------------------------------------     --隐式游标 set serveroutput on   declare     l_deptno dept.deptno%type; begin     l_deptno := 30;     for mydept in (select empno, ename, sal from emp where deptno = l_deptno)     loop     dbms_output.put_line(to_char(mydept.empno) || ',' || mydept.ename || ',' || mydept.sal);     end loop; end; / -----------------游标 吕晓东 14:49:53 (多人发送)   --无参游标 set serveroutput on DECLARE    v_name emp.ename%TYPE;    v_job emp.job%TYPE;    CURSOR c1       --声明游标,没有参数没有返回值    IS       SELECT ename, job FROM emp WHERE deptno = 20; BEGIN    OPEN c1; --打开游标    LOOP       FETCH c1 INTO v_name, v_job;    --提取游标       IF c1%FOUND THEN          DBMS_OUTPUT.PUT_LINE(v_name||'的岗位是'||v_job);       ELSE          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');          EXIT;       END IF;    END LOOP;    CLOSE c1;   --关闭游标 END; / -----------------------------------------------------   --有参游标 set serveroutput on DECLARE    v_name emp.ename%TYPE;    v_firedate emp.hiredate%TYPE;    CURSOR c2(dept_id NUMBER, jobname VARCHAR2) --声明游标,有参数没有返回值    IS       SELECT ename, hiredate FROM emp              WHERE deptno = dept_id AND job = jobname; BEGIN    OPEN c2(20, 'ANALYST');  --打开游标,传递参数值    LOOP       FETCH c2 INTO v_name, v_firedate;    --提取游标       IF c2%FOUND THEN          DBMS_OUTPUT.PUT_LINE(v_name||'的雇佣日期是'||v_firedate);       ELSE          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');          EXIT;       END IF;    END LOOP;    CLOSE c2;   --关闭游标 END; / ----------------------------------------------------- --自定义记录类型的游标 set serveroutput on DECLARE    TYPE emp_record_type IS RECORD(         e_name emp.ename%TYPE,         h_date emp.hiredate%TYPE);    v_emp_record EMP_RECORD_TYPE;      CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值       RETURN EMP_RECORD_TYPE    IS       SELECT ename, hiredate FROM emp              WHERE deptno = dept_id AND job = j_id; BEGIN    OPEN c3(j_id => 'MANAGER', dept_id => 10);  --打开游标,传递参数值    LOOP       FETCH c3 INTO v_emp_record;    --提取游标       IF c3%FOUND THEN          DBMS_OUTPUT.PUT_LINE(v_emp_record.e_name||'的雇佣日期是' ||v_emp_record.h_date);       ELSE          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');          EXIT;       END IF;    END LOOP;    CLOSE c3;   --关闭游标 END; / ----------------------------------------------------- set serveroutput on DECLARE    CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值    IS       SELECT e_name, hiredate FROM emp               WHERE deptno = dept_id AND job = j_id;       --基于游标定义记录变量，比声明记录类型变量要方便，不容易出错    v_emp_record c4%ROWTYPE; BEGIN    OPEN c4(30, 'SALESMAN');  --打开游标,传递参数值    LOOP       FETCH c4 INTO v_emp_record;    --提取游标       IF c4%FOUND THEN          DBMS_OUTPUT.PUT_LINE(v_emp_record.e_name || '的雇佣日期是' ||v_emp_record.hiredate);       ELSE          DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');          EXIT;       END IF;    END LOOP;    CLOSE c4;   --关闭游标 END; /   -----------------------------------------------------   -----异常 吕晓东 11:39:17 (多人发送) /---自定义异常   declare    l_count number;    exp_too_less exception; begin    select count(*) into l_count from emp where deptno=30;    if l_count <= 6 then       raise exp_too_less;    end if; exception    when NO_DATA_FOUND then        dbms_output.put_line('没有找到数据');     when exp_too_less then        dbms_output.put_line('数据记录太少');     when others then        dbms_output.put_line('插入数据异常'); end; /   -------case例子 吕晓东 11:53:59 (多人发送)   declare    l_num dept.deptno%type; begin    for mydept_rec in (select * from dept)    loop         l_num := mydept_rec.deptno;         case l_num         when 10 then dbms_output.put_line(to_char(l_num) || ', ACCOUNTING');         when 20 then dbms_output.put_line(to_char(l_num) || ', RESEARCH');         when 30 then dbms_output.put_line(to_char(l_num) || ', SALES');         else dbms_output.put_line(to_char(l_num) || ', unknown');       end case;            end loop; exception    when NO_DATA_FOUND then        dbms_output.put_line('没有找到数据');       when others then        dbms_output.put_line('插入数据异常'); end; /     -----存储过程的创建和执行 SQL> set serveroutput on SQL> create procedure myproc as   2  begin   3      dbms_output.put_line('欢迎学习存储过程');   4  end myproc;   5  /   Procedure created.   SQL> begin   2  myproc;   3  end;   4  / 欢迎学习存储过程   PL/SQL procedure successfully completed.   SQL>   ------a创建mark用户并授权 SQL> conn system/drsoft Connected. SQL> show user USER is "SYSTEM" SQL> create user mark identified by mark   2  default tablespace tbs_drtest   3  temporary tablespace temp   4  account unlock   5  /   User created.   SQL> grant connect, resource to mark;   Grant succeeded.   SQL> conn mark/mark; Connected. SQL> show user USER is "MARK"   SQL> create user chris identified by chris   2  default tablespace tbs_drtest   3   temporary tablespace temp   4   account unlock   5  /   User created.   SQL> conn system/drsoft Connected. SQL> show user USER is "SYSTEM" SQL> create user sean identified by sean   2  default tablespace tbs_drtest   3  temporary tablespace temp   4  account unlock   5  /   User created.   SQL> grant connect, resource to sean;   Grant succeeded.   --b用mark用户创建存储过程 SQL> show user USER is "MARK" SQL> set serveroutput on SQL> create procedure marks_proc as   2  begin   3      dbms_output.put_line('欢迎学习存储过程');   4  end;   5  /   Procedure created.   ---c用chris用户执行存储过程会出错因为没有授权 SQL> conn chris/chris; Connected. SQL> show user USER is "CHRIS" SQL> execute mark.marks_proc; BEGIN mark.marks_proc; END;         * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'MARK.MARKS_PROC' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored   ----d mark用户为chris用户授予execute权限 SQL> conn mark/mark Connected. SQL> show user USER is "MARK" SQL> grant execute on marks_proc to chris;   Grant succeeded.   ----e chris再次执行存储过程mark.marks_proc   SQL> conn chris/chris Connected. SQL> show user USER is "CHRIS" SQL> set serveroutput on SQL> execute mark.marks_proc; 欢迎学习存储过程   PL/SQL procedure successfully completed.   -----f用sean执行mark.marks_proc SQL> conn sean/sean Connected. SQL> show user USER is "SEAN" SQL> excute mark.marks_proc; SP2-0734: unknown command beginning "excute mar..." - rest of line ignored. SQL> execute mark.marks_proc; BEGIN mark.marks_proc; END;         * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'MARK.MARKS_PROC' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored   ----g mark把存储过程的权限授予PUBLIC，这样其他用户就可以执行mark的存储过程   SQL> conn mark/mark Connected. SQL> show user USER is "MARK" SQL> grant execute on marks_proc to public;   Grant succeeded.   ----h 再用sean执行mark.marks_proc SQL> conn sean/sean Connected. SQL> show user USER is "SEAN" SQL> set serveroutput on SQL> execute mark.marks_proc; 欢迎学习存储过程   PL/SQL procedure successfully completed.   ---带参数的存储过程 吕晓东 16:53:42 (多人发送)   create or replace procedure p_insert_dept(   dept_no in number,   dept_name in varchar2,   dept_loc in varchar2   ) is begin   dbms_output.put_line('deptno:' || dept_no || '; dname:' || dept_name || '; loc:' || dept_loc);   insert into dept values(dept_no,dept_name,dept_loc); end; /   项目开发中对存储过程应用存储过程的案例。 吕晓东 09:27:18 (多人发送) 1、表结构 电压等级表： create table T_VOL_LEVEL (   VID      NUMBER(2) not null, --id   VOLTAGE  VARCHAR2(30) not null, --电压   400V  / 10KV   VOL_SIMP CHAR(2) not null ----电压等级简拼   DY / GY )   工程编号表 create table T_PROJECT_NUMBER (   NUM_ID   NUMBER(8) not null,   VOL_SIMP CHAR(2) not null, --电压等级简拼   DY / GY   YEAR     CHAR(4) not null, --年份   SEQ_NUM  NUMBER(5) not null --流水号 )   创建工程编号主键序列： -- Create sequence create sequence SEQ_PROJECT_NUM minvalue 1 maxvalue 9999 start with 6 increment by 1 cache 5;     2、生成工程编号规则：电压等级 + '-' + 当前年 + 五位流水 --创建存储过程 create or replace procedure p_get_project_apply_num(voltage_level_id in number, project_apply_number out varchar2) is    yy char(4);    yy_temp number(4);    vl_sim char(2);    n number(1);    curr_seq_num number(5); begin    select extract(year from sysdate) into yy_temp from dual;    yy := to_char(yy_temp);    dbms_output.put_line('当前年:' || yy);       select VOL_SIMP into vl_sim FROM T_VOL_LEVEL where VID=voltage_level_id;    dbms_output.put_line('电压等级:' || vl_sim);       select count(1) into n from T_PROJECT_NUMBER where YEAR=yy and VOL_SIMP=vl_sim;    dbms_output.put_line('对应工程申请编号记录:' || to_char(n));       if n <= 0 then       --新增一条当前年对应电压等级的工程编号记录，格式： DY-201100001       project_apply_number := vl_sim || '-' || yy || lpad('1',5,'0');       insert into T_PROJECT_NUMBER values(SEQ_PROJECT_NUM.NEXTVAL, vl_sim, yy, 2);         else       --更新当前年和指定电压等级的工程编号的序列号       select SEQ_NUM into curr_seq_num from T_PROJECT_NUMBER where YEAR=yy and VOL_SIMP=vl_sim;       project_apply_number := vl_sim || '-' || yy || lpad(to_char(curr_seq_num),5,'0');       update T_PROJECT_NUMBER set SEQ_NUM=SEQ_NUM+1 where YEAR=yy and VOL_SIMP=vl_sim;         end if;       commit;    dbms_output.put_line('获取工程申请编号:' || project_apply_number); exception   when NO_DATA_FOUND then     rollback;     dbms_output.put_line('data not found:vl_sim=' || vl_sim);   when others then     rollback;     dbms_output.put_line('未知错误');   end p_get_project_apply_num;     --PL/SQL测试 declare      voltage_level_id number(2);      project_apply_number varchar2(11); begin   -- Call the procedure     voltage_level_id := 1; --低压   p_get_project_apply_num(voltage_level_id, project_apply_number); end;     3、JDBC 调用存储过程   public class ProjectApplyDaoImpl implements ProjectApplyDao {     ......       private SimpleJdbcCall procReadPAN; //调用获取工程申请编号存储过程             @Resource(name="dataSource")     public void setDataSource(DataSource dataSource){         this.procReadPAN = new SimpleJdbcCall(dataSource).withProcedureName("p_get_project_apply_num"); //初始化SimpleJdbcCall     }       @SuppressWarnings("unchecked")     @Override     public String getProjecdtApplyNum(short vid) throws ProjectApplyException {         //SimpleJdbcCall 调用存储过程的使用方法参考spring官方参考手册11章相关部分         //voltage_level_id 为存储过程 p_get_project_apply_num(voltage_level_id in number, project_apply_number out varchar2)中的输入参数名         SqlParameterSource in = new MapSqlParameterSource().addValue("voltage_level_id", vid);         log.debug(in.getValue("voltage_level_id"));         Map out = this.procReadPAN.execute(in);                 log.debug(out);                     //注意SQL中返回的都是大写的,刚开始弄成小写的就取不到，但是数据库变化了说明这里有问题，通过日志打印发现是大写，罪过罪过         String pan = (String) out.get("PROJECT_APPLY_NUMBER");         log.debug("获取的工程编号：" + pan);         return pan;     } } -----------------   吕晓东 17:01:54 (多人发送)   create or replace  procedure p_get_dept(   dept_no in number,   dept_name out dept.dname%type, --使用%type表示变量不需要确定的类型   dept_loc out dept.loc%type   ) is begin   select dname into dept_name from dept where deptno=dept_no;   select loc into dept_loc from dept where deptno=dept_no;       dbms_output.put_line('deptno:' || dept_no || '; dname:' || dept_name || '; loc:' || dept_loc); end;   declare dn varchar2(14); dl varchar2(13); begin   p_get_dept(10, dn, dl);   dbms_output.put_line('dn=' || dn || '; dl=' || dl); end; /     吕晓东 17:16:52 (多人发送)   1、编写函数f_get_emp_count,要求根据输入参数deptno查找对应部门的员工总数。 create or replace function f_get_emp_count(dept_no in number) return number as   l_count number(2); begin   dbms_output.put_line('deptno:' || to_char(dept_no));   select count(1) into l_count from emp where deptno=dept_no;   return l_count; exception   when NO_DATA_FOUND then     dbms_output.put_line('no data found: deptno=' + dept_no);   when others then     dbms_output.put_line('other exception: no data found: deptno=' + dept_no); end;   客户端sqlplus测试： declare     l_deptno number(2);     l_num number(2) default 0; begin     l_deptno := 10;     l_num := f_get_emp_count(l_deptno);     dbms_output.put_line('emp count is:' || to_char(l_num)); end; /     按照输入的部门编号查询指定部门的工资总和，若没有该部门需要异常处理。同时OUT形参输出该部门的人数总和。   代码：   CREATE OR REPLACE FUNCTION getSalaryByDeptNo(v_deptno in emp.deptno%type,v_empcount out number) RETURN NUMBER AS     吕晓东 17:59:46 (多人发送)   示例：   1、用序列和过程创建一个系统流水号，要求格式为：当前时间(业务代码+yyyymmddhhmiss+5位流水号(不足用0补充))，如：ZZHZ2010060611093600001   知识点：       a、日期转化函数 to_char       b、字符串连接 ||       c、 字符串补全 lpad     ------------------------------------------------- 代码：   --序列 create sequence seq_lsh minvalue 1 maxvalue 99999 start with 1 increment by 1 cache 20 cycle order;   --过程 CREATE OR REPLACE PROCEDURE gen_lsh(v_ywdm in char,v_lsh out char) IS     v_datestr varchar2(14);     v_lshstr varchar2(5);     v_lshhz number;     exp_ywdm EXCEPTION; BEGIN     if length(v_ywdm) != 4 then         raise exp_ywdm;     end if;       SELECT to_char(sysdate,'yyyymmddhh24miss') INTO v_datestr FROM dual;     select seq_lsh.nextval into v_lshhz from dual;     v_lshstr := lpad(v_lshhz,5,'0');     v_lsh := v_ywdm || v_datestr || v_lshstr; EXCEPTION     WHEN exp_ywdm THEN     DBMS_OUTPUT.PUT_LINE('业务代码长度应为4位字母：' || v_ywdm); END gen_lsh; /     2、创建一个id序列生成器sq_id_book，用来产生id值，创建一个表t_book(id number(4) not null primary key, name varchar2(30))，要求t_book表的id从序列中获取. 测试：用insert into 保存新纪录。如：insert into t_book(id,name) values(sq_id_book.nextval, 'java');   --序列 create sequence sq_id_book minvalue 1 maxvalue 99999 start with 1 increment by 1 cache 20 cycle order;   --创建表 create table t_book(     id number not null primary key,     name varchar2(30) ) /   --插入数据 insert into t_book(id,name) values(sq_id_book.nextval, 'java'); insert into t_book(id,name) values(sq_id_book.nextval, 'oracle'); insert into t_book(id,name) values(sq_id_book.nextval, 'jquery');     -------------------包   吕晓东 15:42:20 (多人发送)   --创建包的规范 create or replace package emp_pkg as   --根据部门编号获得部门名称   procedure p_get_dept_info(dept_no in number, deptname out varchar2);   --根据部门编号获得部门的总人数   function f_get_emp_num(dept_no number) return number; end emp_pkg; /   --创建包的主体 create or replace package body emp_pkg as   procedure p_get_dept_info(dept_no in number, deptname out varchar2) is   begin     select dname into deptname from dept where deptno=dept_no;   exception     when NO_DATA_FOUND then       dbms_output.put_line('invalid depno:' + dept_no);     when OTHERS then       dbms_output.put_line('unkown exception');     end p_get_dept_info;                 function f_get_emp_num(dept_no number) return number as     l_count number(2);   begin     select count(1) into l_count from emp where deptno=dept_no;     return l_count;   exception     when NO_DATA_FOUND then       dbms_output.put_line('invalid depno:' + dept_no);     when OTHERS then       dbms_output.put_line('unkown exception');   end f_get_emp_num; end emp_pkg;   -----------触发器 吕晓东 17:24:58 (多人发送)   实验： 1、drtest用户拥有了scott.emp/dept表，在创建一个新的统计表 create table sta(    deptno number(2), ---部门编号    emp_count number(4), --部门人员统计    primary key(deptno)    ); /   2、在emp表中建立行级触发器，当向emp插入数据时，更新sta表中相应的统计结果 create or replace trigger tr_emp_insert before insert on emp for each row declare     deptno_new dept.deptno%type;     count_curr number(2); begin     deptno_new := :new.deptno;     dbms_output.put_line('新插入数据的deptno=' || to_char(deptno_new));     --判断deptno_new的值在sta表中是否存在，若存在就更新，否则就插入一条记录     select count(1) into count_curr from sta where deptno = deptno_new;         dbms_output.put_line('count_curr=' || to_char(count_curr));       if count_curr <= 0 then         dbms_output.put_line('向sta表插入数据');         insert into sta values(deptno_new, 1);     else         dbms_output.put_line('更新sta表统计数据');         update sta set emp_count = emp_count + 1 where deptno = deptno_new;     end if; exception     when others then         dbms_output.put_line('触发器失败'); end; /   3、测试：向emp表插入一条记录 insert into emp(empno, ename, deptno) values(8006, 'ccc', 10); commit;   然后查看sta表发现增加了一条数据，当多次向统计表中已存在的部门添加人员时，则发生更新。