数据库oracle学习笔记

beneastsun

--新建一个学生表
create table studentsb(
    sno number(5),
    sname varchar2(20),
    ssex varchar2(3),
    sdate date
);
--查询一个表所有约束
select * from all_constraints where table_name = upper('studentsb');
--
select * from studentsb;
--新建一个课程表(连同主键、不为空、默认值都一起建好)
create table course(
    cno number(5) primary key,
    cname varchar2(30) not null,
    cinfo varchar2(50) default '这是个测试课程'
);
insert into course(cno,cname) values(10000,'英语');
select * from course;
--新建一个选课表
create table courseselect(
    cno number(5),
    scocer integer constraint ck_scocer check( scocer between 0 and 100 ),   
    sno number(5),
    constraint pk_sc primary key (cno,sno),
    constraint fk_sno foreign key (sno) references studentsb(sno)
);
--外键约束
--清空表数据
truncate table studentsb;
truncate table courseselect;
insert into courseselect values(10001,90,10001);
insert into studentsb(sno,sname,ssex,sage) values (10001,'xulinlin','MAL',34);
delete from studentsb where sno = 10000;
select * from courseselect;
alter table courseselect add constraint fkc_cn foreign key (cno) references
    course(cno);
alter table courseselect drop constraint fkc_cn;
alter table courseselect add constraint fkc_cn foreign key (cno) references
    course(cno) deferrable initially immediate; --立即生效
alter table courseselect add constraint fkc_cn foreign key (cno) references
    course(cno) deferrable initially deferred; --提交时生效

beneastsun

-- SET SERVEROUTPUT ON
--变量和游标的定义部分
DECLARE
    width INTEGER;
    heigth INTEGER := 0;
    area INTEGER;
--函数部分
BEGIN
    area := 6;
    width := area / heigth;
--｜｜是连接符，调用输出函数输出一行
    DBMS_OUTPUT.PUT_LINE('width = ' || width);
--异常处理
EXCEPTION
--ZERO_DIVIDE系统本身定义好的
    WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Division by zero');  
END;
--控制输出的开关
set serveroutput on;

--变量和类型
可以通过%TYPE关键字来定义变量的类型（自定义变量）
如product_price products.price%TYPE;

--条件逻辑if开始，endif结尾
DECLARE
    counter integer :=6;
    area integer :=6;
    message varchar(50);
BEGIN
    IF counter > 0 THEN
        message := 'counter is over zero';
        IF area > 0 THEN
            message := 'counter and area are over zero';
            DBMS_OUTPUT.PUT_LINE('message  = ' || message );
        END IF;
    ELSIF counter = 0 THEN
        message := 'counter is zero';
        DBMS_OUTPUT.PUT_LINE('message  = ' || message );
    ELSE
        message := 'counter is below zero';
    END IF;
END;

--循环
--简单循环,只有在显示结束之前会一直运行
LOOP
    statements
END LOOP;
declare
counter integer :=0;
begin
LOOP
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('counter = ' || counter);
    EXIT WHEN counter = 5;
END LOOP;
end;
--WHILE 循环
WHILE condition LOOP
    statements
END LOOP;
DECLARE
    counter INTEGER := 1;
BEGIN
WHILE counter < 6 LOOP
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('counter = ' || counter);
END LOOP;
END;

--FOR 循环，固定是加1
FOR loop_variable IN [REVERSE] lower_bound .. upper_bound LOOP
    statements
END LOOP;
DECLARE
    counter INTEGER := 1;
BEGIN
FOR counter IN REVERSE 1 .. 5 LOOP
    DBMS_OUTPUT.PUT_LINE(counter);
END LOOP;
END;
--游标，帮助我们记录一个结果集，不是一个单条记录
当select语句从数据库中返回的记录多于一条一次时，就可以使用游标cursor。游标可以理解为可以访问一个表的一组记录。
使用的5大步骤：
1.声明一些变量，用来保存select语句返回的值；
DECLARE
    v_product_id products.product_id % TYPE;
    v_name products.name % TYPE;
    v_price products.price % TYPE;
2.声明游标，并指定select语句
CURSOR cv_product_cursor IS
    SELECT product_id, name, price FROM products order by product_id;
3.打开游标
OPEN cv_product_cursor;
4.从游标获取记录
FETCH  cv_product_cursor  INTO v_product_id, v_name, v_price;
5.关闭游标
CLOSE cv_product_cursor;

--过程
创建过程
CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter_name [IN | OUT | IN OUT] type [, ...] )]
    { IS | AS }
BEGIN
    procedure_body
END procedure_name;

IN:是默认参数，表示此参数在程序运行的时候就已经具有了值，这个值在程序体中不变
OUT:此模式定义的参数只在过程体内部赋值
IN OUT 当过程运行时已经有一个具体的值，单在过程中也可以修改
详细请见:store_schema.sql
--调用过程
CALL procedure_name;
--删除过程
DROP PROCEDURE procedure_name;
--查看过程的错误
SHOW ERRORS

--函数
跟过程类似，唯一的区别时函数必须向调用它的语句返回一个值
CREATE [OR REPLACE] FUNCTION function_name
    [(parameter_name [IN | OUT | IN OUT] type [, ...] )]
    { IS | AS }
RETURN type
BEGIN
    function_body
END function_name;

CREATE OR REPLACE FUNCTION circle_area ( p_radius IN NUMBER )
    RETURN NUMBER AS
        v_pi   NUMBER := 3.1415926;
        v_area NUMBER;
    BEGIN
        v_area := v_pi * POWER(p_radius, 2);
        DBMS_OUTPUT.PUT_LINE('v_area = ' || v_area);
        RETURN v_area;
END circle_area;
--调用函数
select circle_area(2) from dual;
--获取函数相关信息可以从user_procedures视图中获取
--删除函数
DROP FUNCTION circle_area;

--触发器
触发器(trigger)是当特定SQL DML语句如INSERT、UPDATE、DELETE语句在特定的数据库表上运行时，由数据库自动运行(或用技术术语来讲是自动激活)的过程。
创建触发器
CREATE [OR REPLACE] TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF } trigger_event
    ON table_name
    [ FOR  EACH  ROW [WHEN trigger_condition]
BEGIN
    trigger_body
END trigger_name;

CREATE OR REPLACE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
    dbms_output.put_line('product_id = ' || ld.product_id);
    dbms_output.put_line('Old price = ' || ld.price);
    dbms_output.put_line('New price = ' || :new.price);
    dbms_output.put_line('The price reduction is more than 25%');

    -- insert row into the product_price_audit table
    INSERT INTO product_price_audit (
        product_id, old_price, new_price
    ) VALUES (
        ld.product_id, ld.price, :new.price
  );
END before_product_price_update;
BEFORE UPDATE OF 子句指定触发器在更新价格之前激活
FOR EACH ROW 表示是一个行级触发器，意思是BEGIN到END之间包含的触发器代码对于每一行都会运行一次
在触发器中，可以通过ld和:new来访问列的原值和新值。

获取触发器相关信息可以从user_triggers视图中可以获得触发器的信息。

语句级别的触发器遵循简单的可视性原则：在语句之前（before）触发的触发器看不到所有语句做的修改， 而所有修改都可以被语句之后（after）触发的触发器看到。
导致触发器触发的数据改变（插入，更新，或者删除）通常是不能被一个before触发器里面执行的 SQL 命令看到的， 因为它还没有发生。
不过，在 before 触发器里执行的 SQL 命令将会看到在同一个外层命令前面处理的行做的数据改变。 这一点需要我们仔细，因为这些改变时间的顺序通常是不可预期的； 一个影响多行的 SQL 命令可能以任意顺序访问这些行。
在一个 after 触发器被触发的时候，所有外层命令产生的数据改变都已经完成， 可以被所执行的 SQL 命令看到。


作业：
1.新建一个一个临时表product_tmp，含有三个字段product_id, name, price（可以参考products表来创建）；
create table product_tmp as select product_id,name,price from products;
2.清空product_tmp表数据记录。
truncate table products_tmp;
3.写一个过程，把products表相关字段数据取出来一行一行插入到新建的product_tmp表中（要求用到游标）
CREATE OR REPLACE PROCEDURE updateproduct is
DECLARE

    -- step 1: declare the variables
    v_product_id products.product_id%TYPE;
    v_name       products.name%TYPE;
    v_price      products.price%TYPE;
   
    -- step 2: declare the cursor
    CURSOR cv_product_cursor IS
        SELECT product_id, name, price
        FROM products
        ORDER BY product_id;

BEGIN

    -- step 3: open the cursor
    OPEN cv_product_cursor;
   
    LOOP

        -- step 4: fetch the rows from the cursor
        FETCH cv_product_cursor
        INTO v_product_id, v_name, v_price;
        
        -- exit the loop when there are no more rows, as indicated by
        -- the Boolean variable cv_product_cursor%NOTFOUND (= true when
        -- there are no more rows)
        EXIT WHEN cv_product_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
         'v_product_id = ' || v_product_id || ', v_name = ' || v_name ||
        ', v_price = ' || v_price
        );

        -- insert into the new table product_tmp
        insert into product_bak(product_id,name, price) values(v_product_id,v_name, v_price);
        

    END LOOP;

    -- step 5: close the cursor
    CLOSE cv_product_cursor;

END;



正确的存储过程：
create or replace procedure updateproduct is

   v_product_id product_tmp.product_id%type;
   v_name product_tmp.name%type;
   v_price product_tmp.price%type;
    cursor cv_product_tmp_cursor is
         select product_id,name,price from products
         order by product_id;
begin
     open cv_product_tmp_cursor;
    loop
    fetch cv_product_tmp_cursor into v_product_id,v_name,v_price;
    exit when cv_product_tmp_cursor%notfound;
    Dbms_Output.put_line('v_product_id = '||v_product_id ||' v_name = '
    ||v_name||' v_price = '||v_price);

    insert into product_tmp(product_id,name,price) values(v_product_id,v_name,v_price);
    end loop;
    close cv_product_tmp_cursor;
end updateproduct;

beneastsun

sql 2000创建数据库语句.sql

create database ben2
on
(
  name=ben2_mdf,
  filename="c:\eastsun\ben2_mdf",
  size=5,
  filegrowth=1,
  maxsize=200
)
log on
(
  name=ben2_ldf,
  filename="c:\eastsun\ben2_ldf",
  size=5,
  filegrowth=1,
  maxsize=200
)

beneastsun

--新建一个学生表
create table studentsb(
    sno number(5),
    sname varchar2(20),
    ssex varchar2(3),
    sdate date
);
--查询一个表所有约束
select * from all_constraints where table_name = upper('studentsb');
--
select * from studentsb;
--新建一个课程表(连同主键、不为空、默认值都一起建好)
create table course(
    cno number(5) primary key,
    cname varchar2(30) not null,
    cinfo varchar2(50) default '这是个测试课程'
);
insert into course(cno,cname) values(10000,'英语');
select * from course;
--新建一个选课表
create table courseselect(
    cno number(5),
    scocer integer constraint ck_scocer check( scocer between 0 and 100 ),   
    sno number(5),
    constraint pk_sc primary key (cno,sno),
    constraint fk_sno foreign key (sno) references studentsb(sno)
);
--外键约束
--清空表数据
truncate table studentsb;
truncate table courseselect;
insert into courseselect values(10001,90,10001);
insert into studentsb(sno,sname,ssex,sage) values (10001,'xulinlin','MAL',34);
delete from studentsb where sno = 10000;
select * from courseselect;
alter table courseselect add constraint fkc_cn foreign key (cno) references
    course(cno);
alter table courseselect drop constraint fkc_cn;
alter table courseselect add constraint fkc_cn foreign key (cno) references
    course(cno) deferrable initially immediate; --立即生效
alter table courseselect add constraint fkc_cn foreign key (cno) references
    course(cno) deferrable initially deferred; --提交时生效

beneastsun

表左右连接

connect scott/scott@ora9i
查看如下语句：
SELECT ename , dname
FROM Emp, Dept
WHERE Emp.Deptno(+) = Dept.Deptno
也可以写成：
SELECT ename , dname
FROM Emp RIGHT JOIN Dept
ON Emp.Deptno = Dept.Deptno
此SQL文使用了右连接，即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配，也就是说上例中无论会不会出现某个部门没有一个员工的情况，这个部门的名字都会在查询结果中出现。

反之：
查看如下语句：
SELECT ename , dname
FROM Emp, Dept
WHERE Emp.Deptno = Dept.Deptno(+)
也可以写成：
SELECT ename , dname
FROM Emp LEFT JOIN Dept
ON Emp.Deptno = Dept.Deptno

则是左连接，无论这个员工有没有一个能在Department表中得到匹配的部门号，这个员工的记录都会被显示

beneastsun

精典的SQL语句，推荐收藏


1. 行列转换--普通

假设有张学生成绩表(CJ)如下
Name    Subject     Result
张三    语文        80
张三    数学        90
张三    物理        85
李四    语文        85
李四    数学        92
李四    物理        82

想变成   
姓名   语文   数学   物理
张三   80     90     85
李四   85     92     82

declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)

2. 行列转换--合并

有表A,
id pid
1   1
1   2
1   3
2   1
2   2
3   1
如何化成表B:
id pid
  1  1,2,3
  2  1,2
  3  1

创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A

3. 如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

是不是太简单了？ 呵呵 不过经常用阿.

4. 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin  role   
EXEC  sp_password  NULL,  'newpassword',  'User'

如果帐号为SA执行EXEC  sp_password  NULL,  'newpassword',  sa  

5. 怎么判断出一个表的哪些字段不允许为空？

select  COLUMN_NAME  from  INFORMATION_SCHEMA.COLUMNS  where  IS_NULLABLE='NO'  and  TABLE_NAME=tablename  

6. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT  b.name  as  TableName,a.name  as  columnname  
From  syscolumns    a  INNER  JOIN    sysobjects  b   
ON  a.id=b.id   
AND  b.type='U'   
AND  a.name='你的字段名字'  

b. 未知列名查所有在不同表出现过的列名
Select  o.name  As  tablename,s1.name  As  columnname  
From  syscolumns  s1,  sysobjects  o  
Where  s1.id  =  o.id  
   And  o.type  =  'U'  
   And  Exists  (  
       Select  1  From  syscolumns  s2   
       Where  s1.name  =  s2.name   
       And  s1.id  <>  s2.id  
       )

7. 查询第xxx行数据

假设id是主键：  
select  *  
from  (select  top  xxx  *  from  yourtable)  aa  
where  not  exists(select  1  from  (select  top  xxx-1  *  from  yourtable)  bb  where  aa.id=bb.id)
  
如果使用游标也是可以的  
fetch  absolute  [number]  from  [cursor_name]  
行数为绝对行数

8. SQL Server日期计算
a. 一个月的第一天
SELECT  DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0)  
b. 本周的星期一
SELECT  DATEADD(wk,  DATEDIFF(wk,0,getdate()),  0)
c. 一年的第一天
SELECT  DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0)  
d. 季度的第一天
SELECT  DATEADD(qq,  DATEDIFF(qq,0,getdate()),  0)  
e. 上个月的最后一天
SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(mm,0,getdate()),  0))  
f. 去年的最后一天
SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate()),  0))
g. 本月的最后一天
SELECT  dateadd(ms,-3,DATEADD(mm,  DATEDIFF(m,0,getdate())+1,  0))  
h. 本月的第一个星期一
select  DATEADD(wk,  DATEDIFF(wk,0,                                                         
                               dateadd(dd,6-datepart(day,getdate()),getdate())        
                                                                                                 ),  0)      
i. 本年的最后一天
SELECT  dateadd(ms,-3,DATEADD(yy,  DATEDIFF(yy,0,getdate())+1,  0))。


转贴:
SQL SERVER 与EXCEL的数据转换
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Adminassword=;Extended properties=Excel 5.0')...[Sheet1$]

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Adminassword=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Adminassword=;Extended properties=Excel 5.0')...[Sheet1$]

实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Adminassword=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

在VB6中应用ADO导出EXCEL文件代码：
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
-------------------------------------------------------------------------------------------------

4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Adminassword=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

T-SQL代码：
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')

beneastsun

Oracle(listener.sqlnet.tnsnames)设置
2008-05-17 16:58
首先来说Oracle的网络结构，往复杂处说能加上加密、LDAP等等。。这里不做讨论，重点放在基本的网络结构也就是我们最常用的这种情况
　　
　　三个配置文件
　　
　　listener.ora、sqlnet.ora、tnsnames.ora ，都是放在$ORACLE_HOME\network\admin目录下。
　　
　　重点:三个文件的作用和使用
　　
　　#-----------------------
　　
　　sqlnet.ora-----作用类似于linux或者其他unix的nsswitch.conf文件，通过这个文件来决定怎么样找一个连接中出现的连接字符串，
　　
　　例如我们客户端输入
　　
　　sqlplus sys/oracle@orcl
　　
　　假如我的sqlnet.ora是下面这个样子
　　
　　SQLNET.AUTHENTICATION_SERVICES= (NTS)
　　
　　NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)
　　
　　那么，客户端就会首先在tnsnames.ora文件中找orcl的记录.如果没有相应的记录则尝试把orcl当作一个主机名，通过网络的途径去解析它的ip地址然后去连接这个ip上GLOBAL_DBNAME=orcl这个实例，当然我这里orcl并不是一个主机名
　　
　　如果我是这个样子
　　
　　NAMES.DIRECTORY_PATH= (TNSNAMES)
　　
　　那么客户端就只会从tnsnames.ora查找orcl的记录
　　
　　括号中还有其他选项，如LDAP等并不常用。
　　
　　#------------------------
　　
　　Tnsnames.ora------这个文件类似于unix 的hosts文件，提供的tnsname到主机名或者ip的对应，只有当sqlnet.ora中类似
　　
　　NAMES.DIRECTORY_PATH= (TNSNAMES) 这样，也就是客户端解析连接字符串的顺序中有TNSNAMES是，才会尝试使用这个文件。
　　
　　例子中有两个，ORCL 对应的本机，SALES对应的另外一个IP地址，里边还定义了使用主用服务器还是共享服务器模式进行连接，一句一句说
　　
　　#你所要连接的时候输入得TNSNAME
　　
　　ORCL =
　　
　　(DESCRIPTION =
　　
　　(ADDRESS_LIST =
　　
　　#下面是这个TNSNAME对应的主机，端口，协议
　　
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
　　
　　)
　　
　　(CONNECT_DATA =
　　
　　#使用专用服务器模式去连接需要跟服务器的模式匹配，如果没有就根据服务器的模式
　　
　　#自动调节
　　
　　(SERVER = DEDICATED)
　　
　　#对应service_name，SQLPLUS>show parameter service_name;
　　
　　#进行查看
　　
　　(SERVICE_NAME = orcl)
　　
　　)
　　
　　)
　　
　　#下面这个类似
　　
　　SALES =
　　
　　(DESCRIPTION =
　　
　　(ADDRESS_LIST =
　　
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.219)(PORT = 1521))
　　
　　)
　　
　　(CONNECT_DATA =
　　
　　(SERVER = DEDICATED)
　　
　　(SERVICE_NAME = sales)
　　
　　)
　　
　　)
　　
　　#----------------------
　　
　　客户端完了我们来看服务器端
　　
　　listener.ora------listener**进程的配置文件
　　
　　关于listener进程就不多说了，接受远程对数据库的接入申请并转交给oracle的服务器进程。所以如果不是使用的远程的连接，listener进程就不是必需的，同样的如果关闭listener进程并不会影响已经存在的数据库连接。
　　
　　Listener.ora文件的例子
　　
　　#listener.ora Network Configuration File: #E:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora
　　
　　# Generated by Oracle configuration tools.
　　
　　#下面定义LISTENER进程为哪个实例提供服务
　　
　　#这里是ORCL，并且它对应的ORACLE_HOME和GLOBAL_DBNAME
　　
　　#其中GLOBAL_DBNAME不是必需的除非使用HOSTNAME做数据库连接
　　
　　SID_LIST_LISTENER =
　　
　　(SID_LIST =
　　
　　(SID_DESC =
　　
　　(GLOBAL_DBNAME = boway)
　　
　　(ORACLE_HOME = E:\oracle\product\10.1.0\Db_2)
　　
　　(SID_NAME = ORCL)
　　
　　)
　　
　　)
　　
　　#**的名字，一台数据库可以有不止一个**
　　
　　#再向下面是**监听的协议，ip，端口等，这里使用的tcp1521端口，并且使#用的是主机名
　　
　　LISTENER =
　　
　　(DESCRIPTION =
　　
　　(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
　　
　　)
　　
　　上面的例子是一个最简单的例子，但也是最普遍的。一个listener进程为一个instance(SID)提供服务。
　　
　　**的操作命令
　　
　　$ORACLE_HOME/bin/lsnrctl start,其他诸如stop,status等。具体敲完一个lsnrctl后看帮助。
　　
　　上面说到的三个文件都可以通过图形的配置工具来完成配置
　　
　　$ORACLE_HOME/netca 向导形式的
　　
　　$ORACLE_HOME/netmgr
　　
　　本人比较习惯netmgr,
　　
　　profile 配置的是sqlnet.ora也就是名称解析的方式
　　
　　service name 配置的是tnsnames.ora文件
　　
　　listeners配置的是listener.ora文件，即**进程
　　
　　具体的配置可以尝试一下然后来看一下配置文件。
　　
　　这样一来总体结构就有了，是当你输入sqlplus sys/oracle@orcl的时候
　　
　　1. 查询sqlnet.ora看看名称的解析方式，发现是TNSNAME
　　
　　2. 则查询tnsnames.ora文件，从里边找orcl的记录，并且找到主机名，端口和service_name
　　
　　3. 如果listener进程没有问题的话，建立与listener进程的连接。
　　
　　4. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专用服务器模式，没有问题的话客户端就连接上了数据库的server process。
　　
　　5. 这时候网络连接已经建立，listener进程的历史使命也就完成了。

#---------------
　　
　　几种连接用到的命令形式
　　
　　1.sqlplus / as sysdba 这是典型的操作系统认证，不需要listener进程
　　
　　2.sqlplus sys/oracle 这种连接方式只能连接本机数据库，同样不需要listener进程
　　
　　3.sqlplus sys/oracle@orcl 这种方式需要listener进程处于可用状态。最普遍的通过网络连接。
　　
　　以上连接方式使用sys用户或者其他通过密码文件验证的用户都不需要数据库处于可用状态，操作系统认证也不需要数据库可用，普通用户因为是数据库认证，所以数据库必需处于open状态。
　　
　　然后就是
　　
　　#-------------
　　
　　平时排错可能会用到的
　　
　　1.lsnrctl status查看服务器端listener进程的状态
　　
　　LSNRCTL> help
　　
　　The following operations are available
　　
　　An asterisk (*) denotes a modifier or extended command:
　　
　　start stop status
　　
　　services version reload
　　
　　save_config trace change_password
　　
　　quit exit set*
　　
　　show*
　　
　　LSNRCTL> status
　　
　　2.tnsping 查看客户端sqlnet.ora和tnsname.ora文件的配置正确与否，及对应的服务器的listener进程的状态。
　　
　　C:\>tnsping orcl
　　
　　TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 16-8月 -
　　
　　2005 09:36:08
　　
　　Copyright (c) 1997, 2003, Oracle. All rights reserved.
　　
　　Used parameter files:
　　
　　E:\oracle\product\10.1.0\Db_2\network\admin\sqlnet.ora
　　
　　Used TNSNAMES adapter to resolve the alias
　　
　　Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
　　
　　(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_
　　
　　NAME = orcl)))
　　
　　OK (20 msec)
　　
　　3.
　　
　　SQL>show sga 查看instance是否已经启动
　　
　　SQL> select open_mode from v$database; 查看数据库是打开还是mount状态。
　　
　　OPEN_MODE
　　
　　----------
　　
　　READ WRITE
　　
　　#-----------------
　　
　　使用hostname访问数据库而不是tnsname的例子
　　
　　使用tnsname访问数据库是默认的方式，但是也带来点问题，那就是客户端都是需要配置tnsnames.ora文件的。如果你的数据库服务器地址发生改变，就需要重新编辑客户端这个文件。通过hostname访问数据库就没有了这个麻烦。
　　
　　需要修改
　　
　　服务器端listener.ora
　　
　　#**的配置文件listener.ora
　　
　　#使用host naming则不再需要tnsname.ora文件做本地解析
　　
　　# listener.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\listener.ora
　　
　　# Generated by Oracle configuration tools.
　　
　　SID_LIST_LISTENER =
　　
　　(SID_LIST =
　　
　　(SID_DESC =
　　
　　# (SID_NAME = PLSExtProc)
　　
　　(SID_NAME = orcl)
　　
　　(GLOBAL_DBNAME = boway)
　　
　　(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
　　
　　# (PROGRAM = extproc)
　　
　　)
　　
　　)
　　
　　LISTENER =
　　
　　(DESCRIPTION_LIST =
　　
　　(DESCRIPTION =
　　
　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
　　
　　)
　　
　　(DESCRIPTION =
　　
　　(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
　　
　　)
　　
　　)
　　
　　客户端sqlnet.ora 如果确认不会使用TNSNAME访问的话，可以去掉TNSNAMES
　　
　　# sqlnet.ora Network Configuration File: d:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
　　
　　# Generated by Oracle configuration tools.
　　
　　SQLNET.AUTHENTICATION_SERVICES= (NTS)
　　
　　NAMES.DIRECTORY_PATH= (HOSTNAME)
　　
　　Tnsnames.ora文件不需要配置，删除也无所谓。
　　
　　下面就是网络和操作系统的配置问题了，怎么样能够解析我的主机名的问题了
　　
　　可以通过下面的方式连接
　　
　　sqlplus sys/oracle@boway
　　
　　这样的话，会连接boway这台服务器，并且listener来确定你所要连接的service_name。

beneastsun

-- SET SERVEROUTPUT ON
DECLARE
    width INTEGER;
    heigth INTEGER := 0;
    area INTEGER;
BEGIN
    area := 6;
    width := area / heigth;
    DBMS_OUTPUT.PUT_LINE('width = ' || width);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Division by zero');  
END;

--变量和类型
可以通过%TYPE关键字来定义变量的类型
如product_price products.price%TYPE;

--条件逻辑
DECLARE
    counter integer :=6;
    area integer :=6;
    message varchar(50);
BEGIN
    IF counter > 0 THEN
        message := 'counter is over zero';
        IF area > 0 THEN
            message := 'counter and area are over zero';
            DBMS_OUTPUT.PUT_LINE('message  = ' || message );
        END IF;
    ELSIF counter = 0 THEN
        message := 'counter is zero';
        DBMS_OUTPUT.PUT_LINE('message  = ' || message );
    ELSE
        message := 'counter is below zero';
    END IF;
END;

--循环
--简单循环,只有在显示结束之前会一直运行
LOOP
    statements
END LOOP;
declare
counter integer :=0;
begin
LOOP
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('counter = ' || counter);
    EXIT WHEN counter = 5;
END LOOP;
end;
--WHILE 循环
WHILE condition LOOP
    statements
END LOOP;
DECLARE
    counter INTEGER := 1;
BEGIN
WHILE counter < 6 LOOP
    counter := counter + 1;
    DBMS_OUTPUT.PUT_LINE('counter = ' || counter);
END LOOP;
END;

--FOR 循环
FOR loop_variable IN [REVERSE] lower_bound .. upper_bound LOOP
    statements
END LOOP;
DECLARE
    counter INTEGER := 1;
BEGIN
FOR counter IN REVERSE 1 .. 5 LOOP
    DBMS_OUTPUT.PUT_LINE(counter);
END LOOP;
END;
--游标
当select语句从数据库中返回的记录多于一条一次时，就可以使用游标cursor。游标可以理解为可以访问一个表的一组记录。
使用的5大步骤：
1.声明一些变量，用来保存select语句返回的值；
DECLARE
    v_product_id products.product_id % TYPE;
    v_name products.name % TYPE;
    v_price products.price % TYPE;
2.声明游标，并指定select语句
CURSOR cv_product_cursor IS
    SELECT product_id, name, price FROM products order by product_id;
3.打开游标
OPEN cv_product_cursor;
4.从游标获取记录
FETCH  cv_product_cursor  INTO v_product_id, v_name, v_price;
5.关闭游标
CLOSE cv_product_cursor;

--过程
创建过程
CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter_name [IN | OUT | IN OUT] type [, ...] )]
    { IS | AS }
BEGIN
    procedure_body
END procedure_name;

IN:是默认参数，表示此参数在程序运行的时候就已经具有了值，这个值在程序体中不变
OUT:此模式定义的参数只在过程体内部赋值
IN OUT 当过程运行时已经有一个具体的值，单在过程中也可以修改
详细请见:store_schema.sql
--调用过程
CALL procedure_name;
--删除过程
DROP PROCEDURE procedure_name;
--查看过程的错误
SHOW ERRORS

--函数
跟过程类似，唯一的区别时函数必须向调用它的语句返回一个值
CREATE [OR REPLACE] FUNCTION function_name
    [(parameter_name [IN | OUT | IN OUT] type [, ...] )]
    { IS | AS }
RETURN type
BEGIN
    function_body
END function_name;

CREATE OR REPLACE FUNCTION circle_area ( p_radius IN NUMBER )
    RETURN NUMBER AS
        v_pi   NUMBER := 3.1415926;
        v_area NUMBER;
    BEGIN
        v_area := v_pi * POWER(p_radius, 2);
        DBMS_OUTPUT.PUT_LINE('v_area = ' || v_area);
        RETURN v_area;
END circle_area;
--调用函数
select circle_area(2) from dual;
--获取函数相关信息可以从user_procedures视图中获取
--删除函数
DROP FUNCTION circle_area;

--触发器
触发器(trigger)是当特定SQL DML语句如INSERT、UPDATE、DELETE语句在特定的数据库表上运行时，由数据库自动运行(或用技术术语来讲是自动激活)的过程。
创建触发器
CREATE [OR REPLACE] TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF } trigger_event
    ON table_name
    [ FOR  EACH  ROW [WHEN trigger_condition]
BEGIN
    trigger_body
END trigger_name;

CREATE OR REPLACE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
    dbms_output.put_line('product_id = ' || ld.product_id);
    dbms_output.put_line('Old price = ' || ld.price);
    dbms_output.put_line('New price = ' || :new.price);
    dbms_output.put_line('The price reduction is more than 25%');

    -- insert row into the product_price_audit table
    INSERT INTO product_price_audit (
        product_id, old_price, new_price
    ) VALUES (
        ld.product_id, ld.price, :new.price
  );
END before_product_price_update;
BEFORE UPDATE OF 子句指定触发器在更新价格之前激活
FOR EACH ROW 表示是一个行级触发器，意思是BEGIN到END之间包含的触发器代码对于每一行都会运行一次
在触发器中，可以通过ld和:new来访问列的原值和新值。

获取触发器相关信息可以从user_triggers视图中可以获得触发器的信息。

语句级别的触发器遵循简单的可视性原则：在语句之前（before）触发的触发器看不到所有语句做的修改， 而所有修改都可以被语句之后（after）触发的触发器看到。
导致触发器触发的数据改变（插入，更新，或者删除）通常是不能被一个before触发器里面执行的 SQL 命令看到的， 因为它还没有发生。
不过，在 before 触发器里执行的 SQL 命令将会看到在同一个外层命令前面处理的行做的数据改变。 这一点需要我们仔细，因为这些改变时间的顺序通常是不可预期的； 一个影响多行的 SQL 命令可能以任意顺序访问这些行。
在一个 after 触发器被触发的时候，所有外层命令产生的数据改变都已经完成， 可以被所执行的 SQL 命令看到。


作业：
1.新建一个一个临时表product_tmp，含有三个字段product_id, name, price（可以参考products表来创建）；
create table product_tmp as select product_id,name,price from products;
2.清空product_tmp表数据记录。
truncate table products_tmp;
3.写一个过程，把products表相关字段数据取出来一行一行插入到新建的product_tmp表中（要求用到游标）
CREATE OR REPLACE PROCEDURE updateproduct is
DECLARE

    -- step 1: declare the variables
    v_product_id products.product_id%TYPE;
    v_name       products.name%TYPE;
    v_price      products.price%TYPE;
   
    -- step 2: declare the cursor
    CURSOR cv_product_cursor IS
        SELECT product_id, name, price
        FROM products
        ORDER BY product_id;

BEGIN

    -- step 3: open the cursor
    OPEN cv_product_cursor;
   
    LOOP

        -- step 4: fetch the rows from the cursor
        FETCH cv_product_cursor
        INTO v_product_id, v_name, v_price;
        
        -- exit the loop when there are no more rows, as indicated by
        -- the Boolean variable cv_product_cursor%NOTFOUND (= true when
        -- there are no more rows)
        EXIT WHEN cv_product_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
         'v_product_id = ' || v_product_id || ', v_name = ' || v_name ||
        ', v_price = ' || v_price
        );

        -- insert into the new table product_tmp
        insert into product_bak(product_id,name, price) values(v_product_id,v_name, v_price);
        

    END LOOP;

    -- step 5: close the cursor
    CLOSE cv_product_cursor;

END;

beneastsun

--查询所有客户所有信息
select * from customers;
select c.first_name,c.last_name from customers c;
select first_name as "姓 名" from customers;
select customers.customer_id,products.product_id from customers,products
    where customers.customer_id = products.product_id;
   
select c.first_name,
    --p.name,
    c.last_name,
    c.first_name
    from customers c,products p
    where c.customer_id = p.product_id;
   
-----------------------------------------------------------------------------------
--sdfdsgdgdf
select c.customer_id,
    --p.product_id,
    --c.*,
    p.*
    from customers c,products p
    where c.customer_id = p.product_id ;
-----------------------------------------------------------------------------------

select c.customer_id,c.*,p.product_id from customers c,products p
    where c.customer_id = p.product_id;
select c.*,c.first_name,c.phone from customers c;
select first_name,phone from customers;
Insert Into customers Values(44,'test','test',Null,Null,9);
select c.customer_id, p.product_id from customers c, products p
    where c.customer_id = p.product_id;
select c.CUSTOMER_ID As "用户 Id",c.FIRST_NAME,d.NAME from customers c,products d
    where c.CUSTOMER_ID = d.PRODUCT_ID;
Select c.CUSTOMER_ID,c.* From CUSTOMERS c;
Select customers.CUSTOMER_ID 用户, customers.FIRST_NAME, products.NAME
    From customers, products
    Where customers.CUSTOMER_ID = products.PRODUCT_ID;
select * from CUSTOMERS;
select price*2 as "新的价格",price from products;
SELECT 20*(10/5-1) AS "OMPUTATION" FROM dual;
select * from dual;
select * from dual;
select sysdate from dual;
select sysdate from dual;
select first_name || last_name from customers;
--查看当前用户拥有的所有表
select * from user_all_tables;
select 'select * from ' || table_name || ';' from user_all_tables;
select 'drop table ' || 'table_name' from user_all_tables;
SELECT first_name || 'testing' || last_name AS "Customer Name" FROM customers;
select 'sfsdfsdf' from user_all_tables;
select 'drop table ' || table_name || ';' from user_all_tables;
select 'delete from ' || table_name || ' where ' || ';' from user_all_tables;
SELECT TO_DATE('20-JUL-2007')+2 FROM dual;
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
alter session set nls_date_format ='YYYY-MM-DD HH24:MI:SS';
alter session set nls_date_format = 'DD-MON-RR';
select to_date('2008-08-09 14:34:35', 'YYYY-MM-DD HH24:MI:SS') from dual;
SELECT customer_id,first_name,last_name, NVL(phone,'Unknown Phone Number')
    AS PHONE_NUMBER FROM customers;
select NVL2(sum(price),'列非空','列为空') from products where name ='xu' ;
select * from products;
select distinct product_type_id from products;
select * from customers for update ;
select * from dual;

select * from customers for update ;

SELECT customer_id,first_name,last_name, NVL2(phone,'Unknown Phone Number','Knowned Phone')
    AS PHONE_NUMBER
    FROM customers;

--查询所有客户的用户id，firstname，lastname和电话号码
select c.CUSTOMER_ID  "用户id",c.FIRST_NAME,c.LAST_NAME,c.PHONE from customers c;
/*--查询所有客户的用户id，firstname，lastname和电话号码，如果电话号码为空，则用Unknown Phone Number填充*/
Select customer_id  "用户 id", first_name From CUSTOMERS ;
select c.* from CUSTOMERS c for update;


SELECT customer_id,first_name,last_name, NVL(phone,'Unknown Phone Number'),NVL2(phone, '为空','非空')
    AS "PHONE_NUMBER" FROM customers;
SELECT * FROM customers WHERE customer_id <ALL (2,3,4);
select * from customers;
SELECT * FROM departments WHERE department_id <> 2’;
SELECT * FROM customers WHERE customer_id > ANY (2,3,4);
SELECT * FROM customers WHERE customer_id > ALL (2,3,4);
SELECT * FROM customers WHERE first_name like 'X_';
SELECT * FROM customers WHERE customer_id IN (2,3,5);
SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 3;
SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 3;
select * from customers c where c.first_name = 'Xul' or last_name = 'sai';
SELECT * FROM customers WHERE first_name ='xu' AND customer_id < 3
   OR phone = '800-555-1214'
select * from customers ;


select * from customers for update;
update customers set TEST=7,last_name ='linlin' where first_name ='xu';
--查询所有会话的视图
select s.sid, q.sql_text, s.terminal, s.program
   from v$session s, V$sqlarea q where s.sql_id = q.sql_id;
select * from v$parameter where name in ('sessions','sga_max_size','db_block_size');
select * from v$parameter where name like '%sort%';
--查找当前用户下所有的表
select * from User_All_Tables;
--
select * from all_all_tables;
select upper('c.first_name'),lower(c.last_name),initcap(c.last_name)
    from customers c;
select replace(c.first_name,'xu','ux') from customers c;
select replace(p.name,'ModerS','wusxdc')from products p where p.product_id =1;
select translate(p.name,'Moder','wusddx')from products p where p.product_id =1;
select substr(p.name,2,length(p.name))from products p where p.product_id =1;
select * from ALL_ALL_TABLES where owner='STORE' And table_name Like '%CUSTOMERS%';
select * from V$SQL;
select * from v$session;
select * from V$SQLAREA;
Select table_name from user_tables Intersect Select table_name From USER_ALL_TABLES;
select * from v$parameter where name like '%sort%';
select * from V$PARAMETER where value is not null;
select * from V$INSTANCE;
select * from DICTIONARY;
select * from all_users;
select * from DBA_ROLES;
select * from ALL_TAB_COL_STATISTICS;
select * from ALL_VIEWS;
select * from ALL_QUEUE_TABLES;
SELECT * FROM departments;
SELECT department_id, location_id FROM departments;
SELECT price*2 "DOUBLE_ PRICE"  FROM products;
select * from products;
SELECT price*2 "双倍价格"  FROM products;
select t.*,t.FIRST_NAME from customers t;
select c.FIRST_NAME, p.NAME from CUSTOMERS c, PRODUCTS p
    where c.CUSTOMER_ID = p.PRODUCT_ID;
SELECT 20*(10/5-1) AS "COMPUTATION" FROM dual;
select sysdate from dual;
select * from customers for update;
select DISTINCT c.FIRST_NAME from customers c;
select c.first_name From customers c Order By c.first_name Desc;
select c.FIRST_NAME from customers c;
SELECT first_name || ' ' || last_name AS "全名" FROM customers;
select 'truncate table ' || table_name || ';' from user_tables;
select concat(first_name,last_name) as "全名" FROM customers;
select concat(' ',first_name) as "quanming" from customers;
ALTER SESSION SET NLS_DATE_FORMAT='MONTH-DD-YYYY';
Alter Session Set NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss';
select * from v$parameter where name = lower('NLS_DATE_FORMAT') ;
SELECT TO_date('2007-8-10 23:59:59','YYYY-MM-DD hh24:mi:ss')+2 FROM dual;
Select to_char(Sysdate, 'YYYY-MM-DD hh24:mi:ss') From dual;
Select Sysdate From dual;
select * from PURCHASES;
select customer_id "用户id" from customers;
select * from products;
select product_id,product_type_id,
    case product_type_id
    when 1 then '产品类型id为1'
    when 2 then 'tank'
    else 'no'
    end
    from products;
select c.*,  decode(customer_id, 5,'id is 5', 3, 'first_name is wang', 'others') from customers c;
select c.*, c.customer_id,
    case customer_id
    when 5 then 'id is 5'
    when 3 then 'first_name is wang'
    else 'others'
    end
    from customers c;

beneastsun

SELECT DISTINCT purchased_by FROM purchases;
select * from purchases for update;
select * from products;
select * from customers for update;
insert into customers(customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST) values (
    20,'pan','weiwei',null, null,null);
Select * From customers Where customer_id =20;
Insert Into customers Values (21,'testing','my',Null,Null,Null);
Insert Into customers_bak Values (Select customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST From CUSTOMERS Where customer_id = 19);
Select * From CUSTOMERS_BAK For Update;
Truncate Table customers;
Delete From CUSTOMERS;
Insert Into CUSTOMERS_bak(customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST) Select customer_id,FIRST_NAME,last_name,DOB,PHONE,TEST From CUSTOMERS Where customer_id = 19;
select * from CUSTOMERS_BAK Where customer_id =19;
Drop Table CUSTOMERS_BAK;
Create Table customers_bak As Select * From CUSTOMERS;
Select * From customers_bak For Update;
drop table  CUSTOMERSBAK;
create table CUSTOMERSBAK as select * from customers;
select * from CUSTOMERSBAK ;
SELECT customer_id,first_name,last_name, NVL(phone,'Unknown Phone Number')
    AS PHONE_NUMBER FROM customers;
SELECT * FROM customers WHERE customer_id = 1;
select * from departments for update;
insert into departments(DEPARTMENT_ID,DEPARTMENT_NAME,DEPARTMENT_INFO)
    values(6,'testing006','infotesting06');
SELECT * FROM customers WHERE customer_id <> 1;
SELECT * FROM customers WHERE customer_id > ANY (2,3,4);
SELECT * FROM customers WHERE customer_id > ALL (2,3,4);
select min(department_id) from DEPARTMENTS;
SELECT * FROM customers WHERE first_name like '_a%';
SELECT * FROM customers WHERE customer_id IN (2,3,5);
select * from customers where exists (select customer_id from customers where customer_id = 3);
SELECT * FROM customers WHERE customer_id BETWEEN 2 AND 3;
SELECT * FROM customers WHERE customer_id >= 1 And customer_id <= 3;
SELECT * FROM customers WHERE customer_id BETWEEN 1 AND 3;
ALTER SESSION SET NLS_DATE_FORMAT='MONTH-DD-YYYY';
SELECT * FROM customers WHERE dob > to_date('20-8-2007 23:46:50','DD-MM-YYYY hh24:mi:ss')
    OR customer_id < 2 AND phone like '%1211';
select sysdate from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select to_char(c.DOB,'DD-MM-YYYY hh24:mi:ss') from CUSTOMERS c where c.DOB is not null;
select c.DOB from CUSTOMERS c where c.DOB is not null;
SELECT product_id, INITCAP(description) FROM products where product_id=4;
select * from products;
select * from customers for update ;
SELECT UPPER('first_name'),LOWER(last_name),initcap(last_name) FROM customers;
SELECT name,LENGTH('Name') FROM products;
select * from products where product_id =1;
SELECT customer_id, NVL(phone, 'Unknown phone Number'),NVL2(phone,'号码不为空','号码为空')
     FROM customers;
select * from customers;
SELECT customer_id, NVL2(phone, '已知','Unkown') As "PHONE" FROM customers order by 1 ,"PHONE"  Desc;
select c.LAST_NAME,c.FIRST_NAME from customers c Order By 1 ,2 Desc;

select * from products WHERE product_id =1;
select upper(c.first_name),upper('first_name'),initcap(last_name),lower(first_name)
    from customers c;
select * from products where product_id =1;
SELECT REPLACE(name,'Scidnce','Physicsb') FROM products WHERE product_id =1;
---当被转换的字符比转换的短时，用空来替代
SELECT translate(name,'Science','Phys') FROM products WHERE product_id =1;
select Name from products where product_id =1;
update products set name='Science' where name like '%Physics%';
select * from products where name like '%Physics%';
Select c.* From USER_ALL_TABLES c Where c.table_name Like '%CUSTOMER_';
select p.name from products p;
SELECT SUBSTR(name,2,LENGTH('name')) FROM products
    WHERE product_id =1 order by product_id;
select length(substr(name,2,length(name))),length(name) from products;
select name from products;
SELECT RPAD(name,30,'.'), LPAD(price,8,' ') FROM products WHERE product_id =1;
SELECT LTRIM('    Hello,Welcome To 51testing!'),
      length(LTRIM('    Hello,Welcome To 51testing!')),
      length('    Hello,Welcome To 51testing!'),        
      RTRIM('Hi,Welcome To Beijing!abcabzc', 'eabtfcd')
      FROM dual;
SELECT FLOOR(5.8),FLOOR(-5.8) FROM dual;
--select FLOOR(5.75),FLOOR(-5.86, 1) from dual;
select FLOOR(5.8) from dual;
SELECT MOD(8,3), MOD(8,4) FROM dual;
SELECT ROUND(5.73), ROUND(5.73,-2),ROUND(-5.73),ROUND(56.73,-1),ROUND(5.759993,4)
FROM dual;

SELECT TRUNC(5.73,0),TRUNC(5.785,2),TRUNC(5.73,-1) FROM dual;
SELECT DECODE(x,1,'x is 1', 2, 'x is 2', 'others') FROM dual ;
select * from CUSTOMERS for update;
select c.first_name, DECODE(c.first_name,
             'Gail','全名为Gail Black',
             'Xu','全名为xulinlin',
             '其他情况'
              ) from customers c;
select c.first_name,
       case c.first_name
       when 'Gail' then '全名为Gail Black'
       when 'Xu' then '全名为xulinlin'
       else '其他情况'
       end
       from customers c;
select c.*,  decode(customer_id, 5,'id is 5', 3, 'first_name is wang', 'others') from customers c;
select * from customers c order by c.CUSTOMER_ID desc;
select to_date(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
SELECT TO_CHAR(12345.67,’99,999.99’) FROM dual;
SELECT TO_DATE(’08-JUL-2007’),TO_DATE(’08-JUL-06’) FROM dual;
alter session set nls_date_format='YYYY-MM-DD';
SELECT to_date('08-AUG-2007','MM.DD.YY') FROM dual;
select sysdate from dual;
SELECT TO_NUMBER('913.13') FROM dual;
SELECT TRANSLATE('testing','ts','arf') FROM dual;

SELECT product_id, DECODE (warehouse_id, 1, 'Southlake',
    2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non-domestic')
    "Location of inventory" FROM inventories WHERE product_id < 1775;
SELECT c.*  FROM customers c ORDER BY 2;
SELECT * FROM customers ORDER BY first_name ASC, last_name DESC;
SELECT customer_id, first_name, last_name  FROM customers ORDER BY 1;
SELECT customer_id, first_name, last_name LASTNAME FROM customers ORDER BY LASTNAME;
SELECT product_type_id FROM products GROUP BY product_type_id ;
SELECT product_id, customer_id  FROM purchases GROUP BY product_id,customer_id ;
SELECT product_type_id,AVG(price) FROM products GROUP BY product_type_id ;
SELECT product_type_id,AVG(price) FROM products WHERE price <15
         GROUP BY product_type_id
         HAVING AVG(price)>13
         ORDER BY AVG(price);






select * from products;
--正确写法
select product_type_id,
    product_id,
    --name,
    AVG(price) from products
    where product_type_id >2
    --having AVG(price) > 3
    group by product_type_id,product_id
    --,product_id
    --having AVG(price) > 3
    order by product_id
      
   
   
select * from products order by name;
select p.product_type_id from products p order by p.product_id;
select * from products;
select * from product_types;
select * from product_price_audit;
update products set price = price * 0.7;
select product_type_id,
    --product_id,
    AVG(price)from products  
    where product_type_id > 1
    --having product_id > 1
    group by product_type_id
    --order by product_id
    --where product_type_id > 1
    having product_id > 1
    --where product_type_id > 1
    order by product_id;


--写一SQL查询产品类型为DVD的所有价格有调整的所有产品信息
select a.* from products a,product_types b, product_price_audit c
    where b.name = 'DVD'
    and c.old_price <> c.new_price
    and a.product_id = c.product_id
    and a.product_type_id = b.product_type_id;

--写一SQL语句查询老价格低于25且产品类型为DVD的价格有调整的所有产品信息并对
--其进行分组统计
select a.product_id,a.product_type_id,a.name,a.description,a.price
    from products a,product_types b, product_price_audit c
    where c.old_price < 20
    and b.name = 'DVD'
    and c.old_price <> c.new_price
    and a.product_id = c.product_id
    and a.product_type_id = b.product_type_id
    group by a.product_type_id,a.product_id,a.name,a.description,a.price

select * from product_types;
select * from products;
select * from product_price_audit;   
--查询所有价格小于10的所有产品类型名称
select p.name from product_types p where p.product_type_id in
    (select c.product_type_id from products c where c.price < 10 );
        
select distinct(p.name) from product_types p, products c
    where c.product_type_id = p.product_type_id
    and c.price < 10;

   
--查询所有价格有变动的所有产品信息
select p.* from products p, product_price_audit c
    where c.old_price <> c.new_price
    and p.product_id = c.product_id
   
--查询所有DVD产品价格的总计
select sum(price) from products p, product_types f
    where f.name = 'DVD'
    and p.product_type_id = f.product_type_id;
        
--查询老价格高于新价格而且类型中含有D字符的所有产品信息
select c.* from product_price_audit p, products c, product_types f
    where p.old_price > p.new_price
    and f.product_type_id = c.product_type_id
    and c.product_id = p.product_id
    and f.name like '%D%';
select c.* from product_types p, products c
    where p.product_type_id = c.product_type_id
    and c.name like '%W%'
    and c.product_id in
    (select f.product_id from product_price_audit f where f.old_price > f.new_price )
   
--查询产品价格小于DVD产品平均价格的所有产品信息，并对其进行分组和统计
select c.product_id,c.product_type_id,c.name,
    c.description,c.price
    from products c where price <
    (select avg(price) from products a, product_types c
         where a.product_type_id = c.product_type_id
         and c.name = 'DVD'
    )
    group by cube(c.product_id,c.product_type_id,c.name,c.description,c.price);
select * from (select * from customers);

beneastsun

SELECT product_id, name, price,AVG(price)
         FROM products
         WHERE price <
         (SELECT AVG(price) FROM products)
         group by product_id,name,price
         having AVG(price)>0;
SELECT  product_id, name, price
         FROM products
         WHERE price <
         (SELECT AVG(price)
                   FROM products)
          ORDER BY product_id DESC;
  

select c.* from product_price_audit c where c.old_price > 30;
select * from products for update ;
select * from all_constraints where table_name = upper('products');
alter table products drop constraints PRODUCTS_PK;


select product_id, product_type_id, Avg(price)
    from products
    Where product_id >=1
    group by product_type_id,product_id
    having AVG(price) > 3
    order by product_type_id ;
select * from PRODUCTS;
select product_type_id,product_id,AVG(price) from products  where product_type_id > 0
    having AVG(price) > 3
   
    group by product_type_id
    --having AVG(price) > 3
    --order by product_id ;
select * from products;
select product_type_id,AVG(price) from products where product_type_id > 0
    group by product_type_id
    having AVG(price) > 3
    order by name ;
select * from products;
select product_type_id from products where product_type_id > 0
    --group by product_type_id
    order by product_type_id;

SELECT product_type_id,COUNT(product_type_id) FROM products
    GROUP BY product_type_id ORDER BY COUNT(product_type_id);
SELECT product_type_id,AVG(price) FROM products GROUP BY product_type_id HAVING AVG(price)>20;
SELECT product_type_id,AVG(price) FROM products WHERE price <15
         GROUP BY rollup(product_type_id)
         HAVING AVG(price)>1
         ORDER BY AVG(price);
select * from products;
alter session set nls_date_format='DD-MM-YYYY';
SELECT ADD_MONTHS('08-8-2007',2) FROM dual ;
SELECT LAST_DAY('08-2-2008') FROM dual;
SELECT NEXT_DAY('08-12-2007',7) FROM dual;
SELECT SYSDATE FROM dual;
select ROUND(-2,-1) from dual;
select ROUND(-2.56,1) from dual;
SELECT DECODE(x,1,'x is 1', 2, 'x is 2', 'others') FROM dual ;
select * from USER_ALL_TABLES;
select * from products;
select * from employees2;
select * from customers for update;
select * from customers;
select * from customers;
create table customers_bak as select * from customers;
select * from customers_bak;
select * from user_tables;
select 'truncate table ' || table_name ||';'
    from user_tables;

select division_id as "部门id", job_id as "职位id",
    sum(salary) from EMPLOYEES2
    group by cube(division_id,job_id) minus
select division_id as "部门id", job_id as "职位id",
    sum(salary) from EMPLOYEES2
    group by rollup(division_id,job_id)
update EMPLOYEES2 set class_id ='LOW' where salary < 200000;  
select * from  EMPLOYEES2   
--rollup子句扩展group by 子句，为每个分组返回一条小计记录，并返回全部分组返回总计
select division_id as "部门id",  job_id as "职位id" , class_id as "职称",
    sum(salary) from EMPLOYEES2
    group by cube(division_id,job_id,class_id) minus
select division_id as "部门id",  job_id as "职位id" , class_id as "职称",
    sum(salary) from EMPLOYEES2
    group by rollup(division_id,job_id,class_id)

select * from employees2 for update;
update employees2 set pro_id = 'FBI' where salary < 150000;

select division_id as "部门id",sum(salary) from EMPLOYEES2
    group by cube(division_id) minus
select division_id as "部门id" ,sum(salary) from EMPLOYEES2
    group by (division_id)
   
select division_id as "部门id",  job_id as "职位id" ,sum(salary) from EMPLOYEES2
    group by rollup(division_id,job_id);
select division_id, job_id, sum(salary) from EMPLOYEES2
    group by cube(division_id,job_id) minus
select division_id as "部门id",  job_id as "职位id" ,sum(salary) from EMPLOYEES2
    group by rollup(division_id,job_id) ;
select * from ( select * from customers );
   
select first_name from customers minus select first_name from customers;
   
select division_id as "部门id",  job_id as "职位id" ,sum(salary) from EMPLOYEES2
    group by (division_id,job_id);
select division_id, job_id, sum(salary) from EMPLOYEES2
    group by cube(division_id,job_id);   

select * from employees2
select sum(salary) from EMPLOYEES2 where division_id ='SAL';
select division_id, sum(salary) from EMPLOYEES2 group by (division_id);
select * from EMPLOYEES2;
select division_id, job_id, sum(salary) from EMPLOYEES2 group by rollup(division_id,job_id);
--cube 子句扩展group by 子句，返回cube中所有列组合的小计信息，并显示总计信息
--工资根据division_id 和job_id进行求和，cube在每个division_id中都返回一条记录，表示其中的工资总数，同时在
--快结束的时候显示每一种job_id的工资总数，最后一条记录显示所有工资总数
select division_id "部门", job_id "职位", sum(salary) "薪水"
    from EMPLOYEES2 group by division_id,job_id;
select division_id "部门", job_id "职位", sum(salary) "薪水"
    from EMPLOYEES2 group by cube(division_id,job_id) minus
select division_id "部门", job_id "职位", sum(salary) "薪水"
    from EMPLOYEES2 group by rollup(division_id,job_id);
SELECT first_name, last_name
          FROM customers
          WHERE customer_id =
          (SELECT customer_id FROM customers
          );

   
SELECT product_type_id, AVG(price)
         FROM  products
         GROUP by product_type_id
         HAVING  AVG(price)  >
         (SELECT MIN(AVG(price))
                   FROM products
                   GROUP BY product_type_id
                   )
         order by AVG(price);
SELECT AVG(price) FROM products
                   GROUP BY product_type_id
SELECT  product_id
        FROM
        (SELECT product_id,name
                 FROM  products
                 WHERE product_id < 3 );
select * from (select * from products);
create table products_bak as select * from products;
select * from products_bak;
SELECT  product_id, name, price
         FROM products
         WHERE price <
         (SELECT AVG(price)
                   FROM products)
          order BY product_id DESC;
           
--在多行子查询中使用IN操作符
SELECT product_id, name  FROM  products WHERE product_id IN
         (SELECT product_id FROM products WHERE name = 'Chemistry');
--在多行子查询中使用ANY操作符
SELECT employee_id, last_name, salary FROM employees WHERE salary < ANY
         (SELECT low_salary FROM salary_grades);
--在多行子查询中使用ALL操作符   
SELECT employee_id, last_name,salary FROM employees WHERE salary < ALL
         (SELECT high_salary FROM salary_grades );
--编写多列子查询
SELECT  product_id, price, name  FROM products
         WHERE  (product_id, price) IN
         (SELECT product_type_id, MIN(price) FROM products
          GROUP BY product_type_id Order By product_type_id )
          Order By product_type_id ;
--1.使用UNION  ALL操作符，例如：
SELECT product_id, product_type_id, name  FROM products  UNION ALL
SELECT prd_id, prd_type_id, name FROM more_products;
--2.使用UNION操作符，例如：
SELECT product_id, product_type_id, name  FROM products UNION
SELECT prd_id, prd_type_id, name FROM more_products;
--3.使用INTERSECT操作符，例如：
SELECT product_id, product_type_id, name  FROM products INTERSECT
SELECT prd_id, prd_type_id, name FROM more_products;
--4.使用MINUS操作符，例如：
SELECT product_id, product_type_id, name  FROM products MINUS
SELECT prd_id, prd_type_id, name FROM more_products union all
SELECT prd_id, prd_type_id, name FROM more_products

Select * From products Order By product_id;
Select * From MORE_PRODUCTS Order By prd_id;

select * from v$parameters where ;

SELECT product_id, name, price
    FROM products
    ORDER BY product_id;
create table product_tmp as select product_id,name, price from products;
select * from product_tmp For Update;
truncate table product_tmp;
Drop Table PRODUCT_TMP;

beneastsun

DECLARE

    -- step 1: declare the variables
    v_product_id products.product_id%TYPE;
    v_name       products.name%TYPE;
    v_price      products.price%TYPE;
   
    -- step 2: declare the cursor
    CURSOR cv_product_cursor IS
        SELECT product_id, name, price
        FROM products
        ORDER BY product_id;

BEGIN

    -- step 3: open the cursor
    OPEN cv_product_cursor;
   
    LOOP

        -- step 4: fetch the rows from the cursor
        FETCH cv_product_cursor
        INTO v_product_id, v_name, v_price;
        
        -- exit the loop when there are no more rows, as indicated by
        -- the Boolean variable cv_product_cursor%NOTFOUND (= true when
        -- there are no more rows)
        EXIT WHEN cv_product_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
         'v_product_id = ' || v_product_id || ', v_name = ' || v_name ||
        ', v_price = ' || v_price
        );

        -- insert into the new table product_tmp
        insert into product_tmp(product_id,name, price) values(v_product_id,v_name, v_price);
        

    END LOOP;

    -- step 5: close the cursor
    CLOSE cv_product_cursor;

END;
select * from  PRODUCT_TMP;
select t.PRODUCT_ID,t.price from products t where t.PRODUCT_ID=1;


CREATE OR REPLACE PROCEDURE update_product_price(
    p_product_id IN products.product_id%TYPE,
    p_factor     IN NUMBER ) AS
    v_product_count INTEGER;
BEGIN
    -- count the number of products with the
    -- supplied product_id (should be 1 if the product exists)
    SELECT COUNT(*)
        INTO v_product_count
        FROM products
        WHERE product_id = p_product_id;

    -- if the product exists (v_product_count = 1) then
     -- update that product's price
    IF v_product_count = 1 THEN
        UPDATE products
        SET price = price * p_factor
        WHERE product_id = p_product_id;
    COMMIT;
    END IF;
    EXCEPTION
        WHEN OTHERS THEN
        ROLLBACK;
END update_product_price;


CREATE OR REPLACE PACKAGE product_package AS
    TYPE t_ref_cursor IS REF CURSOR;
    FUNCTION get_products_ref_cursor RETURN t_ref_cursor;
    PROCEDURE update_product_price (
        p_product_id IN products.product_id % TYPE;
        p_factor IN NUMBER;
    }
END product_package;

CREATE OR REPLACE TRIGGER after_product_price_update
AFTER UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
    dbms_output.put_line('product_id = ' || ld.product_id);
    dbms_output.put_line('Old price = ' || ld.price);
    dbms_output.put_line('New price = ' || :new.price);
    dbms_output.put_line('The price reduction is more than 25%');

    -- insert row into the product_price_audit table
    INSERT INTO product_price_audit (
        product_id, old_price, new_price
    ) VALUES (
        ld.product_id, ld.price, :new.price
  );
END;
update products set price = price*0.7;
select * from product_price_audit;
truncate table product_price_audit;
delete from product_price_audit;
select * from products;

--修改拥护密码
alter user store identified by store;
--授创建会话的权限
grant create session to store;
--系统特权
create session ---连接到数据库
create sequence ---创建序列
create synonym ---创建同名对象，也就是同义词
create table --创建表
create any table --在任何模式创建表
drop table --删除表
drop any table --在任何模式删除表
create procedure --创建存储过程
execute any procedure --执行任何模式中的存储过程
create user --创建用户
drop user --删除用户
create view --创建视图
select * from all_users;
--with admin option可以再次授权
grant create session,create user, create table to mytest with admin option;
--检查权限
select * from USER_SYS_PRIVS where username = upper('xulinlin');
select * from USER_SYS_PRIVS;
--撤销用户的系统特权
revoke create table from mytest;
--对象特权
---常用的对象特权select、insert、update、delete、execute。
grant select, insert, update on store.PRODUCTS to mytest;
select * from PRODUCT_TYPES for update;
grant select on store.EMPLOYEES to mytest;
select * from USER_COL_PRIVS_MADE;
--检查已授权的对象特权
select t.grantee as "被授权的用户",t.table_name as "对象名", t.grantor as "授权者"
    , t.privilege as "被授权的权限", t.grantable as "再授权能力",t.hierarchy
    as "层次关系"
    from USER_TAB_PRIVS_MADE t where t.table_name ='PRODUCTS';
--哪些列对象开放了哪些特权
select * from USER_COL_PRIVS_MADE;
--某个用户被授予了哪些表上的哪些对象特权
select * from USER_TAB_PRIVS_RECD;
--创建同义词
create public synonym products for store.PRODUCTS;
grant create public synonym to store;
--角色，就是一组特权，它可以分配给一个用户或者其他角色。要创建角色，系统必须具备
--create role系统特权
grant create role to store;
grant create user to store with admin option;
create role product_manager;
grant select, insert, update, delete on products to product_manager;
grant product_manager to mytest;
--检查授予用户的角色
select * from USER_ROLE_PRIVS;
--检查授予角色的系统特权
select * from ROLE_SYS_PRIVS;
--检查授予角色的对象特权
select * from ROLE_TAB_PRIVS;
/*
Create [global temporary] table table_name (
    column_name type [constraint constraint_def default defualt_exp]
    [,column_name type [constraint constraint_def default defualt_exp] ...]
    )
    [on commit {delete | preserve} rows] --delete 事务的末尾删除，--preserve会话的末尾删除
    tablespace tabspace_name;
*/
create table courseselect (
    courseid integer,
    coursename varchar(20),
    studentid integer not null,
    studentname varchar2(20) default 'xulinlin',
    info varchar2(40)
);



Create Global Temporary Table Order_status_tmp(
    Id Integer,
    status VARCHAR2(13),
    last_modified Date Default Sysdate
    )
    On Commit Preserve Rows;
   
select * from Order_status_tmp;
select * from course;
insert into order_status_tmp values(0,'sdgfsd',sysdate);   
create table xulinlintab (
    numberid integer,
    fullname varchar(20),
    lastname varchar2(500),
    datetime date
);

create table xulinlintab_bak as select fullname,lastname,datetime from xulinlintab;
select * from  xulinlintab_bak;
truncate table xulinlintab_bak;
select * from xulinlintab for update;
insert into xulinlintab_bak (select * from xulinlintab);

create table course (
    courseid integer,
    coursename varchar(20),
    info varchar2(500)
);

create table student (
    studentid integer,
    studentname varchar(20),
    info varchar2(500)
);

create table courseselect (
    courseid integer,
    coursename varchar(20),
    studentid integer,
    studentname varchar(20)
);
select * from courseselect ;
select * from course;
select * from student;


select * from xulinlintab;
select * from Order_status_tmp for update;
insert into Order_status_tmp(id,Status,Last_Modified) values (1,'xulinlin',null);
insert into Order_status_tmp(id,Status) values (2,'testing');
---testing
Drop Table Order_status_tmp;
Create Table products_bak As Select * From products;
select * from products_bak Intersect Select * From products;
Insert Into order_status_tmp(Id,STATUS) Values (1,'New');
Select * From ORDER_STATUS_TMP;
create table order_status2 (id integer constraint order_status_pk primary key,
    status VARCHAR2(10),
    last_modified date default SYSDATE)
    TABLESPACE users;
insert into student(studentid,studentname,info,sex) values (1,'xulinlin','testing','dfd')
select * from student;
insert into order_status(id,STATUS) values (1,'old');
--获取表相关信息
select * from USER_TABLES Where ;
select * from all_constraints where table_name=upper('student');
select c.user_stats from USER_ALL_TABLES c;
select c.* from USER_TABLES c ;
select * from USER_TAB_COLUMNS;
--
select * from USER_TAB_COMMENTS;
select * from xulinlintab;
select * from all_constraints where table_name = upper('courseselct');
--修改表结构
alter table order_status add modified_by integer;
alter table order_status add initially_created date default sysdate not null;
alter table order_status modify status varchar2(15);
alter table order_status modify status char(10);
alter table ORDER_STATUS2 drop column initially_created;
alter table ORDER_STATUS add constraint order_status2_status_ck2
    check (status in ('Order placed','Pending','Shipped'));
Alter Table order_status Drop Constraint order_status2_status_ck2;
Select * From ALL_CONSTRAINTS Where table_name = upper('xulinlintab');
Select * From ALL_CONSTRAINTS Where table_name = upper('employees');
--禁用已有的约束
alter table ORDER_STATUS disable constraint DEFAULT_EXAMPLE_PK  ;
alter table ORDER_STATUS enable constraint DEFAULT_EXAMPLE_PK  ;
--外键约束
alter table courseselect add constraint courseselect_fk_course cid references course(courseid) on delete cascade;
Alter table order_status add constraint order_status_id_fk id references employees(employee_id) on delete cascade;
alter table courseselect add constraint courseselect_student_id student_id references course(course_id) on delete cascade;
select * from course;
insert into course values(2,'英语','测试英语');
select * from student for update;
select * from courseselect for update;
select * from order_status for update ;
select * from employees;
delete from employees where employee_id = 4;

beneastsun

select * from employees For Update;
select * from Order_status;
Insert Into order_status(order_status_id,status,INITIALLY_created,Id)
    Values(4,'Order placed',Sysdate,5);
Truncate Table Order_status;
insert into ORDER_STATUS(order_status_id,STATUS,initially_created) values (1,'Placee',Sysdate);
insert into ORDER_STATUS(order_status_id,STATUS,initially_created) values (1,'Order placed',Sysdate);
alter table order_status modify test default 'testing' not null

Select test From ORDER_STATUS;
Truncate Table ORDER_STATUS;
select * from ORDER_STATUS Order By 1;
alter table ORDER_STATUS2 modify last_modified not null;
--on delete cascade可以指定再父表中删除一行记录时，子表中匹配的所有行也都被删除
--on delete set null子句可以指定再父表中删除一行记录时，子表中匹配的行的外键将被设置为空值
alter table ORDER_STATUS add constraint order_status2_modified_by_fk
   modified_by references EMPLOYEES(employee_id) on delete cascade;
select * from USER_CONS_COLUMNS Where table_name = upper('order_status');
select * from USER_CONS_COLUMNS Where table_name = upper('employees');
Alter Table order_status Drop Constraint SYS_C005861 ;
alter table ORDER_STATUS add constraint order_status_test_id_fk
   TEST_id references products(product_id) on delete Set Null;
Select * From products;

select * from EMPLOYEES;
Delete From products  Where product_id =4;

Rename ORDER_STATUS2 To ORDER_STATUS;
Select * From order_status;
Insert Into order_status Values (1,'Order placed',Sysdate,Sysdate,'testing',1,5,4,4);
create table order_testing (
    status char(10),
    last_modified date default sysdate,
    modified_by integer,
    initially_created date default sysdate not null,
    test varchar2(15),
    testb varchar2(10) default 'testb'
    );
--
alter table order_testing add constraint order_status_status_ck
    check (status in ('Male','Female'));   
alter table order_testing add constraint order_status_id_fk
      id references employees(employee_id) on delete cascade;  
--3.修改某一列，使其不允许为空且给它赋予一个缺省的值，如testing；
alter table order_testing modify test default 'testing' not null
--4.为这个表添加注释，且为其每一列添加相应的注释；
comment on table order_testing is "testing"
comment on column order_testing.id is "编号"
comment on column order_testing.STATUS is "状态"
comment on column order_testing.LAST_MODIFIED is '最后修改时间'
comment on column order_testing.INITIALLY_CREATED is "初始创建时间"
comment on column order_testing.MODIFIED_BY is "MODIFIED_BY"
comment on column order_testing.TEST is "TEST"
comment on column order_testing.TESTB is "TESTB"

select * from products;
comment on column products.name is '产品名称';
select * from all_col_comments where table_name = 'PRODUCTS';

--查看字典
select * from dictionary where table_name like '%TAB%';
--查看当前用户相关的所有表
select * from user_all_tables ;
select * from user_tables;
select * from all_all_tables where owner =upper('store');
--查看数据参数
select * from v$parameter;
--查看会话信息
select t.MACHINE,t.PROGRAM,t.USERNAME,t.SQL_HASH_VALUE from v$session t;
select s.SQL_TEXT,s.HASH_VALUE, t.MACHINE,t.PROGRAM from V$sqlarea s, v$session t
    where t.SQL_HASH_VALUE = s.HASH_VALUE;
select * from products;


truncate table products_bak;
select * from products_bak;
insert into products_bak(name,price,product_id) (select name,price,product_id from products);

--添加unique约束
alter table ORDER_STATUS add constraint order_status2_status_uq unique(status);
select * from order_status for update;
truncate table order_status;
insert into order_status values (1,'Order placed',null,null);
--删除约束
alter table ORDER_STATUS drop constraint order_status_status_uq ;
--禁用约束DEFAULT_EXAMPLE_PK
alter table ORDER_STATUS2 add constraint order_status2_status_uq unique(status) disable;
--禁用已有的约束
alter table ORDER_STATUS enable constraint DEFAULT_EXAMPLE_PK  ;
--启用
alter table ORDER_STATUS2 enable constraint order_status2_status_uq  ;
--延迟约束
alter table ORDER_STATUS add constraint order_status_status_uq unique(status)
    deferrable initially immediate;
Alter Table mytesttab Add Constraint mytesttab_id_uq Unique(Id) deferrable initially deferred;
Truncate Table mytesttab;
Select * From ALL_IND_COLUMNS Where table_name = upper('mytesttab');
Drop  Index MYTESTTAB_ID_BITMAPINDEX;
--initially immediate：添加数据、修改表、删除表
--initially deferred 事务提交时才会检查
--可以获得所有的约束信息
select * from ALL_CONSTRAINTS where table_name ='ORDER_STATUS';
--获取有关列的约束信息
select * from USER_CONS_COLUMNS Where table_name = upper('order_status');
--重命名表
rename ORDER_STATUS to order_statusb;
create table products_bak as select * from products;
select * from products_bak MINUS select * from products ;
Select * From ORDER_STATUSB;
select * from order_state;
comment on table order_state is 'testing';
comment on column order_status.STATUS is '测试';
Comment On Column ORDER_STATUS.INITIALLY_CREATED Is '初始创建时间';
--获得表的注释
select * from USER_TAB_COMMENTS Where table_name ='ORDER_STATUS';
--获得列的注释
select * from USER_COL_COMMENTS Where table_name ='ORDER_STATUS' ;
/*创建一个用户只能访问三次（密码错误）的概要文件：

CREATE PROFILE MYPROFILE LIMIT
    SESSIONS_PER_USER Default
    CPU_PER_SESSION Default
    CPU_PER_CALL Default
    CONNECT_TIME Default
    IDLE_TIME Default
    LOGICAL_READS_PER_SESSION Default
    LOGICAL_READS_PER_CALL Default
    COMPOSITE_LIMIT Default
    PRIVATE_SGA Default
    FAILED_LOGIN_ATTEMPTS Default
    PASSWORD_LIFE_TIME Default
    PASSWORD_REUSE_TIME Default
    PASSWORD_REUSE_MAX Default
    PASSWORD_LOCK_TIME 3
    PASSWORD_GRACE_TIME Default
    PASSWORD_VERIFY_FUNCTION NULL ;*/
select * from store.products;
select * from V$PARAMETER where name ='license_max_users';
create or replace procedure myfirstproc(num in NUMBER) as
begin
    for i in 1 .. num loop
              insert into mytesttabbak values(MYTESTSEQB.NEXTVAL,dbms_random.string('U',10),sysdate);
    end loop;   
end myfirstproc;

select * from  user_procedures Where object_type = upper('function');
select * from mytesttable; --15.359
truncate table mytesttable;
/*
create sequence sequence_name
    [start with start_number]
    [increment by increment_number]
    [{maxvalue maxvalue_number | nomaxvalue}]
    [{minvalue minvalue_number | nominvalue}]
    [{cycle | nocycle}]
    [{cache cache_number | nocache}] --cache cache_number指定要保留在内存中整数的个数，默认为20
    [{order | noorder}]
*/
--创建序列
create sequence MYTESTSEQ
    minvalue 1
    maxvalue 9999999
    start with 1
    increment by 1
    cache 20
order;

alter sequence mytestseqb maxvalue 9999999999;
alter sequence mytestseq increment by 2;
select * from mytestseq.currval;

Drop Sequence mytestseq;
create sequence MYTESTSEQB
    minvalue 1
    maxvalue 99999
    start with 1
    increment by 1
    cache 10
    Cycle
order;
Drop Sequence mytestseqb;
--获取序列相关的信息
insert into mytesttab values(MYTESTSEQ.NEXTVAL,dbms_random.string('U',30),sysdate);
Insert Into mytesttab Values (MYTESTSEQb.NEXTVAL,'test',Sysdate);
Select * From MYTESTTAB;
Truncate Table MYTESTTAB;
Select * From USER_SEQUENCES;
--
Select * From mytestseq.NEXTVAL ;
--修改序列
Alter Sequence mytestseq Cycle  ;
Alter Sequence mytestseqb Increment By  2;
alter sequence mytestseq maxvalue 130;


--删除序列
Drop Sequence mytestseq;
select * from mytesttab;

--创建一个表
create table mytesttabbak (
    id integer primary key not null,
    name varchar2(50),
    dateb date default sysdate
);

select * from mytesttabbak;
create or replace procedure myfirstproc(num in number) as
begin
    for i in 1..num loop
        insert into mytesttabbak values (mytestseqbg.nextval,dbms_random.string('T',10),sysdate);       
    end loop;
end myfirstproc;
select * from products;
select * from product_price_audit;

beneastsun

--使用sys用户登陆进入系统，然后新建一个mytestuser，密码为mytestuser，默认的表空间为users
create user mytestuser identified by mytestuser default tablespace users;
grant create session,create table to mytestuser;
--然后使用新建的用户登陆进入，然后创建一个表名字为mytesttab，包含三个字段，一个是id，类型是INTEGER且不许为空，
--二个是name 类型是varchar2长度是50，三个是date 类型是date来行，默认值为系统时间。
create table mytesttab(id INTEGER not null, name varchar(50), mydate date default sysdate);
create or replace procedure myfirstpro(num in NUMBER) as
begin
    for i in 1 .. num loop
              insert into mytesttable values(MYTESTSEQ.nextval, dbms_random.string('U',10),sysdate);
    end loop;   
end myfirstpro;
truncate table MYTESTTABLE ;
Select count(Id) From mytesttable Order By 1;
insert into mytesttab values (MYTESTSEQ.NEXTVAL,DBMS_RANDOM.string('U',30),sysdate);
create table mytesttab(id INTEGER not null, name varchar(50), mydate date default sysdate);
create or replace procedure myfirstpro(num in NUMBER) as
begin
    for i in 1 .. num loop
              insert into mytesttab values(mytestseq.nextval,'testing',sysdate);
    end loop;   
end myfirstpro;
alter table mytesttab add constraint mytesttab_id_uq unique(id);
select * from mytesttab ;
delete from mytesttab where id = 1;
insert into mytesttab values(1,'testing',sysdate);
select * from v$session where machine ='' ;
select * from mytesttab;
select Id from mytesttab;
Select * From mytesttable;
--添加索引
Create Index mytesttab_id_index On mytesttab(Id); --耗时4.938秒 --0.015
--删除索引
Drop Index mytesttable_id_index; --耗时5.969秒 --0.031
Truncate Table mytesttab;
select * from (select rownum id ,p.* from products p) where id between 4 and 6;
Select * From mytesttable Where Id < (select count(Id) from mytesttable Where Id = 9);
Create Table mytesttable (Id Integer,Name Varchar2(10),datetime Date);
Create Index mytesttable_id_index On mytesttable(Id);
select * from ALL_IND_COLUMNS Where table_name = upper('mytesttable');
select * from USER_INDEXES Where table_name = upper('mytesttab');
--添加位图索引
Create Bitmap Index mytesttable_id_bitmapindex On mytesttable(Id); --耗时9.468秒 --0.016秒
Drop Index mytesttab_id_bitmapindex;
Select * From mytesttable Order By 1;
/*--修改表mytesttab，为其增加一列名字为city，类型为varchar2长度为30，并为其添加约束，只允许输入 北京 和 上海。*/
alter table mytesttab add city varchar2(30);
alter table mytesttab add constraint  mytesttab_city_ck
    check (city in ('北京','上海','深圳'));
select * from mytesttab;
--修改表mytesttab，设计id为其主键。
alter table mytesttab add (constraint mytest_id_pk primary key(id));
create table mytesttab_bak as select * from mytesttab;
select * from mytesttab_bak ;
select t.id,t.NAME, t.MYDATE,t.CITY from mytesttab_bak t;
--create table mytesttab(id INTEGER not null, name varchar(50), mydate date default sysdate);
/*
--创建索引
create [unique] index index_name on table_name(column_name [column_name ...])
    tablespace tablespace_name
*/
select name from mytesttabbak order by name; --49.219  52秒
select count(name) from mytesttabbak;
create unique index mytesttabbak_id_name on mytesttabbak(name);
alter sequence mytestseqb increment by 1;
Create Index customers_lastname_index On customers(LAST_NAME);
Create Unique Index customers_phone_index On CUSTOMERS(PHONE);
--函数索引
Create Index customers_lastname_func_index On customers(upper(last_name));
--获取索引信息
Select * From USER_INDEXES where table_name =upper('mytesttabbak');
--获取索引列信息
Select * From USER_IND_COLUMNS where table_name = upper('customers');
--修改索引
Alter Index CUSTOMERS_MYINDEX Rename To CUSTOMERS_MYINDEXtest;
--删除索引
Drop Index MYTESTTABBAK_ID_NAME;
--创建B树索引
create index idx_emp_ename on emp(ename)
    tablespace mytbs;
--创建位图索引
create bitmap index bmidx_mytesttabbak_job on mytesttabbak(name);
select /*+ index(bmidx_mytesttabbak_job)*/name from mytesttabbak; --54.921
select name from mytesttabbak; --51.031
Select /*+ index(mytesttable_id_index) */ Count(Id) From mytesttabbak;
Create Index mytest_func_index On mytesttable((Id)*3);
Select Id*3 From mytesttable;
    from customers ;  
--创建函数索引
create index funidx_sal
    on emp(sal+comm+sal*0.2) ;

select * from dba_jobs;
--相关初始化参数
select * from V$PARAMETER where name ='job_queue_processes';
--相关视图
select * from dba_jobs;
select * from all_jobs;
select * from user_jobs;
select * from dba_jobs_running;
--创建测试表
drop table test;
create table test(d  date);
--创建一个自定义过程
create or replace procedure MYPROC as
    begin
        insert into TEST values(sysdate);
end;
--创建job
variable jobtest number;
begin
    dbms_job.submit(:jobtest,'MYPROC;',sysdate,'sysdate+1/1440');
end;
--运行job
begin
    DBMS_JOB.run(:jobtest);
end;

--
select * from TEST;
truncate table test;
--删除job
begin
    dbms_job.remove(:jobtest);
end;
--利用dbms_job.run()立即执行该job
--利用dbms_job.broken()重新将broken标记为false
--job change//修改作业
/*DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列）          类型                 描述
JOB                NUMBER          任务的唯一标示号
LOG_USER           VARCHAR2(30)    提交任务的用户
PRIV_USER          VARCHAR2(30)    赋予任务权限的用户
SCHEMA_USER        VARCHAR2(30)    对任务作语法分析的用户模式
LAST_DATE          DATE            最后一次成功运行任务的时间
LAST_SEC           VARCHAR2(8)     如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE     DATE            正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC     VARCHAR2(8)     如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE          DATE            下一次定时运行任务的时间
NEXT_SEC           VARCHAR2(8)     如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME         NUMBER          该任务运行所需要的总时间，单位为秒
BROKEN             VARCHAR2(1)     标志参数，Y标示任务中断，以后不会运行
INTERVAL           VARCHAR2(200)   用于计算下一运行时间的表达式
FAILURES    NUMBER     任务运行连续没有成功的次数
WHAT               VARCHAR2(2000)  执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW          MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI      RAW MLSLABEL     该任务可信任的Oracle最大间隙
CLEARANCE_LO      RAW              MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV           VARCHAR2(2000)   任务运行的NLS会话设置
MISC_ENV          RAW(32)          任务运行的其他一些会话参数
*/
select t.JOB as "任务的唯一标示号",t.LOG_USER as "提交任务的用户 ", t.PRIV_USER as " 赋予任务权限的用户"
    ,t.SCHEMA_USER as "对任务作语法分析的用户模式 ",t.LAST_DATE as "最后一次成功运行任务的时间 ",
    t.LAST_SEC as "last_date日期的小时，分钟和秒 ", t.THIS_DATE as
    "正在运行任务的开始时间",t.interval as "用于计算下一运行时间的表达式"
    ,t.WHAT as "执行任务的PL/SQL块"
    from DBA_JOBS t;


/*--描述                    INTERVAL参数值
--每天午夜12点            
'TRUNC(SYSDATE + 1)'
--每天早上8点30分        
'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
--每星期二中午12点         
'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
--每个月第一天的午夜12点   
'TRUNC(LAST_DAY(SYSDATE ) + 1)'
--每个季度最后一天的晚上11点
'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
--每星期六和日早上6点10分   
'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY", NEXT_DAY(SYSDATE, "SUNDAY")) + （6×60+10）/（24×60）'
*/
--格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
--实例:
EXP store/store GRANTS=Y TABLES=(customer,products)
/*USERID 用户名/口令
FULL 导出整个文件
BUFFER 数据缓冲区的大小
OWNER 所有者用户名列表
FILE 输出文件
TABLES 表名列表
COMPRESS 导入一个范围
RECORDLENGTH IO 记录的长度
GRANTS 导出权限
INCTYPE 增量导出类型
INDEXES 导出索引
RECORD 跟踪增量导出
ROWS 导出数据行
PARFILE 参数文件名
CONSTRAINTS 导出限制
CONSISTENT 交叉表一致性
LOG 屏幕输出的日志文件
STATISTICS 分析对象
DIRECT 直接路径
TRIGGERS 导出触发器 (Y)
FEEDBACK 显示每 x 行 (0) 的进度
FILESIZE 各转储文件的最大尺寸
QUERY 选定导出表子集的子句*/


--调试未通过，请大家调试
create or replace procedure myfirstprob(num in NUMBER) as

    v_id mytesttab.id % TYPE;
    v_name mytesttab.name % TYPE;
    v_date mytesttab.date % TYPE;
    v_city mytesttab.CITY % type;
    cursor myfirst_cursor is select t.ID,t.NAME,t.MYDATE,t.CITY from mytesttab t;
begin
    open myfirst_cursor;
    for i in 1 .. num loop
        FETCH  myfirst_cursor INTO v_id, v_name, v_date,v_city;
              insert into mytesttab_bak(id,NAME,MYDATE,city) values (v_id,v_name,v_date,v_city);
        EXCEPTION
            WHEN notfound THEN
                DBMS_OUTPUT.PUT_LINE('没有数据了');  
    end loop;
    close myfirst_cursor;
end myfirstprob;


CREATE OR REPLACE TRIGGER before_product_price_update
BEFORE UPDATE OF price
ON products
FOR EACH ROW WHEN (new.price < old.price * 0.75)
BEGIN
    dbms_output.put_line('product_id = ' || ld.product_id);
    dbms_output.put_line('Old price = ' || ld.price);
    dbms_output.put_line('New price = ' || :new.price);
    dbms_output.put_line('The price reduction is more than 25%');

    -- insert row into the product_price_audit table
    INSERT INTO product_price_audit (
        product_id, old_price, new_price
    ) VALUES (
        ld.product_id, ld.price, :new.price
  );
END before_product_price_update;

select * from PRODUCT_PRICE_AUDIT;
Truncate Table product_price_audit;
Update products Set price = price * 0.6;

rainy1985

真强大啊

nuanxin2007

强悍啊！！！

xiongy2012

辛苦了

xiongy2012

辛苦了

wangqiang88

真强大啊

12qwsa

为什么不作为附件发呢？