Oracle学习笔记-0004

1、创建Oracle简单存储过程。
创建一张表
SQL> create table mytest(name varchar2(20),passwd varchar2(20));
     Table created.
创建一个存过程
SQL> create or replace procedure my_pro1
  2  is
  3  begin
  4  insert into mytest values('李红','my1234');
  5  end;
  6  /
    Procedure created.
调用存储存储
SQL> exec my_pro1;
PL/SQL procedure successfully completed.
查看mytest表
SQL> select *from mytest;

NAME                 PASSWD
-------------------- --------------------
李红               my1234
SQL> desc user_source;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)
查看存储过程
SQL> select name,type,line from user_source;
NAME                           TYPE               LINE
------------------------------ ------------ ----------
MY_PRO1                        PROCEDURE             1
MY_PRO1                        PROCEDURE             2
MY_PRO1                        PROCEDURE             3
MY_PRO1                        PROCEDURE             4
MY_PRO1                        PROCEDURE             5
MY_PRO2                        PROCEDURE             1
MY_PRO2                        PROCEDURE             2
MY_PRO2                        PROCEDURE             3
MY_PRO2                        PROCEDURE             4
MY_PRO2                        PROCEDURE             5
SQL> drop procedure my_pro1;
Procedure dropped.

2、SQL> set serveroutput on;打开输出选项。
3、SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';修改日期格式。
   Session altered.
   SQL> select sysdate from dual;
   SYSDATE
   --------------------
   27-nov-2011 15:54:26
   SQL> alter session set nls_date_format='yyyy-mon-dd hh24;mi:ss';
   Session altered.
   SQL> select sysdate from dual;
   SYSDATE
   --------------------
   2011-nov-27 15;57:45
4、SQL模糊查询
   SQL>select *from user where username like '%李%;%表示0个或多个字符。
   SQL>select *from user where username like '李_;_表示任意单个字符。
   SQL>select *from user where username like '[张李王]三';表示括号内所列字符中的一个。
   SQL>select *from user where username like '[^张李王]三';表示不在括号所列之内的单个字符。
5、添加联机重做日志文件组
   先通过v$logfile视图查看重做日志文件组的存储路径。
   SQL>select *from v$logfile;
   SQL>alter database add logfile ('f:\app\administrator\oradata\linux\redo04.log') size 15m;