oracle的简单笔记

创建一个用户：create user test identified by test.

给该用户授予相应的权利：grant connect,create sesison,create table,resource to test.

创建表空间：create tablespace mytablespace datafile 'd:/oradata/mytablespace.dbf'  size 200m;

创建临时表空间：create temporary tablespace mytemptablespace tempfile 'd:/oradata/mytemptablespace.dbf' size 200m;

设置默认的表空间：alter user test default tablespace mytablespace;

设置默认的临时表空间：alter user test default tablespace mytemptablespace temporary tablespace mytemptablespace;

更改表空间：alter tablespace mytablespace add datafile 'd:/newFile.dbf' size 20m autoextend on next 20m maxsize 200m;

创建视图：create or replace view user_sal_veiw(用户编号,用户名,部门编号) as select UserId,UserName,DeptNo from UserInfo

创建序列：create sequence User_seq increment by 1 start with 1 maxvalue 1000 nocycle nocache;

修改序列：alter sequence User_seq increment by 50 start with 10 maxvalue 50000 nocycle nocache;

创建索引：create foreign index index_userId  on UserInfo(DeptId);

为scott用户解锁：alter user scott account unlock;

创建存储过程：create or replace procedure my_pro

(

  v_deptno in emp.deptno%type,

 v_count out number

)

as

begin

  select  count(*) into v_count from emp where deptno = v_deptno; 

   dbms_output.put_line('总的记录数为：'|| to_char(v_count));

 exception

  when no_data_found  then

  dbms_output.put_line('没有相关数据！');

end my_pro;

调用存储过程：variable v_count number;

exec my_pro(20,:v_count);

 

创建函数：create or replace function get_emp_sal

(

  v_empno in emp.empno%type

)

return number

is

 v_sal number(10,2);

begin

  select sal  into v_sal from emp where empno = v_empno; return v_sal;

end get_emp_sal;

调用函数：variable al number;

exec al :||  get_emp_sal(7788);  or select get_emp_sal(7788) from emp;

创建游标：declare cursor empinfo(cno number) is select ename,sal from emp where deptno = cno;

v_ename emp.ename%type;         v_sal emp.sal%type;

begin

    if not empinfo%isopen then

            open empinfo(10);

    end if;

     loop

          fetch empinfo into v_enam,sal;

         exit when empinfo%notfound;

                 dbms_output.put_line(v_ename||':'||v_sal);

    end loop

end

使用for创建游标：declare cursor empinfo_sal is select * from emp where sal > 2000;

    empifno empinfo_sal%rowtype;

begin

     for empifno in empinfo loop

          dbms_output.put('员工编号：'||to_char(empinfo.empno));

          dbms_output.put('员工姓名：'||to_char(empinfo.ename));

          dbms_output.put('员工薪水：'||to_char(empinfo.sal));

     end loop;

end;

 

创建包(创建包头和包体)

create or relace package empinfo_pkg

is

   pragma serially_reusable;

    procedure emp_pro(v_emp out emp%rowtype);

end empinfo_pkg;

create or replace package body empinfo_pkg

 is

    pragma serially_reusable;

     procedure emp_pro

     (

          v_emp out emp%rowtype

     )

    is

     begin

         select * into v_emp from emp where empno = 7788;

          dbms_output.put_line(v_emp.empno);

    end emp_pro;

begin

   dbms_output.put_line('程序从这里开始！');

end empinfo_pkg ;

调用包中的存储过程：declare

v_emp emp%rowtype;

begin

    empinfo_pkg.emp_pro(v_emp);

end;

创建触发器：create or replace trigger my_tri

before insert on emp for each row when(new.job <> 'manager')

begin

   raise_application_error(-2001,'不能添加manager');

end;