创建一个用户: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;