oracle

最新推荐文章于 2024-04-29 10:15:00 发布

zzsyzxzm

最新推荐文章于 2024-04-29 10:15:00 发布

阅读量232

点赞数

分类专栏： sql 文章标签： insert null file 测试存储 user

本文链接：https://blog.csdn.net/zzsyzxzm/article/details/7823052

版权

sql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

sqlplus system/oracle@orcl
conn / as sysdba;
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;


用scott/tiger用户
执行：完整的HR_SCOTT.sql

-----或者--------------
导出、导入HR表：
exp system/oracle tables=(hr.countries,hr.departments,hr.employees,hr.jobs,hr.job_history,hr.locations,hr.regions) file=d:\hr_table.dmp
imp system/oracle file=d:\hr_table.dmp fromuser=hr touser=scott

导出、导入SCOTT表：
exp system/oracle tables=(scott.bonus,scott.dept,scott.emp) file=d:\scott_table.dmp
imp system/oracle file=d:\scott_table.dmp fromuser=scott touser=scott



创建工资等级表：
create table scott.salgrades(
  lowest_salary number,
  highest_salary number,
  grade_level number
 );

  insert into salgrades values(1000,5000,1);
  insert into salgrades values(5001,10000,2);
  insert into salgrades values(10001,20000,3);
  insert into salgrades values(20001,50001,4);
  insert into salgrades values(50001,10000,5);




-----JDBC-----------------------------------------------
创建测试存储过程
create or replace procedure SCOTT.P_INSERT_DEPT(
DEPTNO IN NUMBER, 
DNAME IN VARCHAR2, 
LOC IN VARCHAR2) is
begin
     INSERT INTO SCOTT.DEPT VALUES (DEPTNO,DNAME,LOC);
end P_INSERT_DEPT;

创建测试的包内存储过程
create or replace package scott.PACKAGE_SCOTT is

  PROCEDURE P_INSERT_DEPT(
        DEPTNO         IN     NUMBER,
        DNAME          IN     VARCHAR2  DEFAULT NULL,
        LOC            IN     VARCHAR2  DEFAULT NULL);
  PROCEDURE P_INSERT_DEPT_OUT(
        DEPTNO         IN     NUMBER,
        DNAME          IN     VARCHAR2  DEFAULT NULL,
        LOC            IN     VARCHAR2  DEFAULT NULL,
        RETURNVALUE    OUT    VARCHAR2);

end PACKAGE_SCOTT;
/
create or replace package body scott.PACKAGE_SCOTT is
  PROCEDURE P_INSERT_DEPT(
        DEPTNO         IN     NUMBER,
        DNAME          IN     VARCHAR2  DEFAULT NULL,
        LOC            IN     VARCHAR2  DEFAULT NULL) AS

        BEGIN
           INSERT INTO SCOTT.DEPT VALUES (DEPTNO,DNAME,LOC);
        END P_INSERT_DEPT;

  PROCEDURE P_INSERT_DEPT_OUT(
        DEPTNO         IN     NUMBER,
        DNAME          IN     VARCHAR2  DEFAULT NULL,
        LOC            IN     VARCHAR2  DEFAULT NULL,
        RETURNVALUE    OUT    VARCHAR2) AS

        BEGIN
           INSERT INTO SCOTT.DEPT VALUES (DEPTNO,DNAME,LOC);
           SELECT to_char(sysdate,'yyyy-MM-dd') INTO RETURNVALUE FROM DUAL;
        END P_INSERT_DEPT_OUT;

end PACKAGE_SCOTT;
/