Oracle 记录

连接数据库：
SQL> sqlplus /nolog SQL> connect / as sysdba

启动数据库:  
SQL> startup

启动监听：
lsnrctl start 

查看监听：
lsnrctl status 

停止监听：
lsnrctl stop 

关闭数据库正常关闭：
SQL> shutdown normal 

立即关闭： SQL> shutdown immediate

直接关闭：  SQL> shutdown abort
查看数据库： select name from v$database;

查看数据库结构：  desc v$database;
用户管理
查看当前用户:show user;
查看数据库实例：select * from v$instance;
查看系统用户：select * from all_users;
增加用户：create user a identified by a;（默认建在SYSTEM表空间下）grant connect,resource to a; 
设置密码：自己的：password username;别人的：alert user username identified by xxxx;
连接到新用户：conn a/a;
查询当前用户下所有对象：select * from tab;
删除用户：drop user username [cascade];
查看表结构：desc table_name;
回滚：roll;rollback;
提交：commit;
运行脚本：start sqlfile

pl/sql

块：

declare
...
begin
    .....    
    exception      
        ...
end 

 

游标：

declare
  type sp_emp_cursor is ref cursor;
  test_cursor sp_emp_cursor;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  open test_cursor for select ename,sal from emp where deptno=10;
  loop
    fetch test_cursor into v_ename,v_sal;
    exit when test_cursor%notfound;
    dbms_output.put_line('name:'|| v_ename||' sal:'||v_sal);
  end loop;
end;

控制流程：

-- 分支
if..then...end if;
if..then...elsif....else...end if;

-- 循环 loop
create or replace procedure sp_test(name varchar2)
is
v_num number:=1;
begin
    loop
        ...
        exit when v_num>=10;
        v_num:=v_num+1;
    end loop;
end 

--循环 while
create or replace procedure sp_test(name varchar2)
is
v_num number:=11;
begin
    while v_num>=0;
        ...
        v_num:=v_num-1;
    end loop;
end 

函数：

create function my_func(name varchar2)
return number 
is
var number(7,2);
begin
    ......
    return val;
end;

var xxx number;
call my_func('xx') into:xxx;

过程：

create or replace procedure proc_1 is
begin
    ....
end ;

--执行过程
exec proc_1();
call proc_1();

create or replace procedure proc_2(param1 varchar2,param2 number) is
declare
...
begin
    ...
end 

包：

--包声明
create or replace package sp_package is
	procedure xxxx;
	function xxx;
end;

--具体实现
create or replace package body sp_package is
	procedure a(...)
	is
	begin
		....
	end; 
	function func(...) return ...
	is
	...
	begin
		...
		return ..;
	end 
end;

call sp_package.xxx(...) ;

视图：

create or replace view view_name 
as
select ....
[with read only]


--删除视图
drop view view_name;

复合类型：

declare
type emp_record_type is record(name emp.ename%type , salary emp.sal%type);
sp_record emp_record_type;
begin
    select e.name,sal into emp_record_type from emp;
    dbms_output.put_line(sp_record.name||':'||sp_record.salary);
end;

异常：

declare
v_ename emp.ename%type;
begin
    select ename into v_ename from emp where empno=3344;
    dbms_output.put_line('name:'||v_ename);
    exception
        when no_data_found then
        dbms_output.put_line('no data found!');
end;

 其他异常如：

/**
 * 其他的异常
 * case_not_found
 * cursor_already_open
 * dup_val_on_index 
 * invaild_cursor
 * invalid_number
 * too_many_rows
 * zero_divide
 * value_error
 * login_denide
 * not_logged_on
 * storage_error
 * timeout_on_resource 
 * ...
 * */

自定义异常：

--自定义异常
create or replace  procedure ext_test(n number)
is
myexception exception
begin
	update ......
	if sql%notfound then
		raise myexception;
	end if;
	exception
		when myexception then
		....
end;