SQL> --init是一个备份副本，不是真正的启动参数文件，是文本类型

SQL> --init.ora是一个备份副本，不是真正的启动参数文件，是文本类型

SQL> --spfile+实例名.ora（spfileorcl.ora） 是一个编译过的二进制文件，是不可编辑改动的

SQL> --位置：C:\app\Administrator\product\11.2.0\dbhome_1\database

SQL> show parameter open_cursor;

SQL> alter system set open_cursors=350;

SQL> shutdown immediate;

SQL> startup;

SQL> connect / as sysdba;

SQL> show parameter open_cursor;

SQL> shutdown immediate;

SQL> startup pfile=C:\app\Administrator\admin\orcl\pfile\init.ora;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

SQL> show parameter processes;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

创建表Create table scott1.emp1 ( Empno number(5) primary key, Ename varchar2(15) not null, age number(2) CHECK(age between 18 and 65 ) ，Per_id varchar2(18) CONSTRAINT perid UNIQUE ,Job varchar2(10), Mgr number(5), Hiredate date default sysdate, Sal number(7,2) CHECK(sal>100), Comm number(3) default 0.0 , Deptno number ，constraint dept_fkey References scott.dept ) Tablespace users PCTFREE 10 PCTUSED 70 STORAGE ( INITIAL 50K NEXT 50k MAXEXTENTS 10 )；

修改表alter table dept add ( headcount number(3) ) | drop (headcount);

alter table dept modify( Dname char(20) );创建表空间create tablespace mytablespacedatafile'C:\app\Administrator\oradata\orcl\mytab01.dbf' size 50m DEFAULT STORAGE( initial 512k next 128k minextents 2 maxextents 999 pctincrease 0 );

查询表空间 select tablespace_name,file_name,bytes from dba_data_files;

主键ALTER TABLE PARK_REVENUE ADD(park_rev_pk PRIMARY KEY ( ACCOUNT_NO));

alter table emp2 add ( constraint pk_empno primary key (empno));

约束alter table emp2 add (sal number (9,2) check (sal>0 and sal < 99999));

alter table emp2 add (deptno number(2) references dept(deptno));

临时表create global temporary table myemp as select * from emp;

创建视图create view v_empsal2000 as select * from emp where sal>=2000 with check option;

创建实体试图grant create materialized view ,select any table to scott;

CREATE MATERIALIZED VIEW all_emps PCTFREE 5 PCTUSED 60 TABLESPACE users STORAGE (INITIAL 50K NEXT 50K) USING INDEX STORAGE (INITIAL 25K NEXT 25K) REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 AS SELECT * FROM scott.emp where deptno=20 UNION SELECT * FROM scott.emp where deptno=30;实体化视图的数据操insert delete update禁止

序列CREATE SEQUENCE emp_no INCREMENT By 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE; 修改ALTER SEQUENCE emp_no INCREMENT By 2 MAXVALUE 9999 CYCLE ;

使用insert into orders(orderno,custno) values(order_seq.nextval,1032)；

insert into line items(orderno,partno,quantity)values(order_seq.currval,20231,3)；

同义词CREATE PUBLIC SYNONYM emp FOR scott.emp ; DROP SYNONYM emp ;

alter table emp2 pctfree 10 pctused 85;

索引create index in_empno on emp(ename) tablespace users;

create index in_lename on emp(lower(ename)) tablespace users;

create index in_year on emp((sal+comm)*12) tablespace users;

聚簇create cluster personal (department_num number(2)) pctfree 40 pctused 10 tablespace users; or tablespace users STORAGE (INITIAL 100K NEXT 50K); grant create any cluster to scott;

修改日志组alter system switch logfile; 添加日志 alter database orcl add logfile group 4 'C:\app\Administrator\oradata\orcl\redo0401.log' size 30m; select group#,members,status from v$log          grant create any view to a with admin option;

权限 grant create session to zhao; grant create table,create view to zhao; revoke撤销

创建用户create user zhao identified by zhao default tablespace users quota 100m on users;

锁是数据库中防止并发征用资源、读到脏数据、有序访问数据的重要机制。锁有2种形式：X锁（exclusive，独占锁，一般也做写锁）和S锁（share，共享锁，也叫读锁）锁的级别有3种，分别是数据库锁、表锁、行锁（列锁）锁的可见性来讲，有2种，分别是明锁和暗锁

SQL> create or replace procedure addcomm 2 (p_empno in scott.emp.empno%type, 3 p_comm in scott.emp.comm%type, 4 p_income out scott.emp.sal%type) as 5 6 7 no_result exception; 8 begin 9 update scott.emp set comm=p_comm where empno=p_empno; 10 if SQL%notfound then 11 raise no_result; 12 end if; 13 select sal+nvl(comm,0) into p_income from scott.emp where empno=p_empno; 14 exception 15 when no_result then 16 dbms_output.put_line('该员工不存在！'); 17 when no_data_found then 18 dbms_output.put_line('未得到正确的职工月收入！'); 19 when others then 20 dbms_output.put_line('未知错误！'); 21 end; 22 / SQL> set serverout on  in 实参->形参，out形参->实参，in out 表示双向传输declare v_empno number(4); v_comm number(7,2);v_income number(7,2); begin v_empno:=&v_empno;  v_comm:=&v_comm; addcomm(v_empno,v_comm,v_income);dbms_output.put_line('该员工的月收入是'||to_char(v_income)); 10 end; 11 /

 游标declare

v_sal number(7,2);v_comm scott.emp.comm%type;v_name scott.emp.ename%type;v_total number(7,2);cursor c_total is select ename,sal,comm from scott.emp where deptno = 30;beginopen c_total;fetch c_total into v_name,v_sal,v_comm;while c_total%found loop v_total:=v_sal + nvl(v_comm,0);dbms_output.put_line(v_name||'的奖金是:'||to_char(v_total));

fetch c_total into v_name,v_sal,v_comm;end loop;close c_total;end;/

for i in 1..c_total%rowcount loop 1-1的for i in 1..5 loop 1-5

savepoint b; rollback to b;

触发器                       

create table emp_unemploye as select empno,ename,job,sal,comm,deptno from scott.emp ;

create or replace trigger tri_unemploye after delete on scott.empfor each row begin insert into emp_unemploye values (:old.empno,:old.ename,:old.job,:old.sal,:old.comm,:old.deptno);

end; /

instead of delete on v_sumsal   删除emp表中指定部门号的所有员工

delete from emp where deptno = :old.deptno;

after logon on schema   用户登录

after logon on database | insert into scott.user_log1 values (user,sysdate,'用户登录!');

before logoff on database | insert into scott.user_log1 values (user,sysdate,'用户离线!');

函数

CREATE OR REPLACE FUNCTION f_sum_sal(deptid IN emp.deptno%TYPE) RETURN NUMBER AS v_sumsal number; BEGIN SELECT SUM(sal)+SUM(nvl(comm,0)) INTO v_sumsal FROM emp WHERE deptno=deptid; --DBMS_OUTPUT.PUT_LINE(deptid||'的工资和为'||v_sumsal); RETURN V_sumsal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其它错误!'); END; /     下面是调用

DECLARE  v_sum_sal number; BEGIN V_sum_sal:=f_sum_sal(20); DBMS_OUTPUT.PUT_LINE('总工资是'||v_sum_sal); END; /