PL/sql语法_to_date

CREATE TABLE XJYlog (--创建表
  emp_id  NUMBER(6),
  l_name  VARCHAR2(25),
  f_name  VARCHAR2(20)
);
drop table xjylog;
--使用sql window有时会报告未知参数
--使用comman window正常


drop table xjytab;
CREATE TABLE xjytab(col1 number,col2 varchar2(20),col3time date);
--ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };
--ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;
--增加xjytab  字段 date
--http://docs.oracle.com/database/121/LNPLS/nameresolution.htm#LNPLS01705
alter table xjytab
add(d date);

--insert into tabname(datecol) value(sysdate) ;  -- 插入date
--insert into tabname(datecol) value(sysdate+1) ;  -- 插入date
--insert into tabname(datecol) value(to_date('2014-02-14','yyyy-mm-dd')) ;  -- 插入to_date
--insert into tabname(datecol) value(to_date('2014-02-14 20:47:00','yyyy-mm-dd hh24:mi:ss')) ;  -- 插入to_date
--insert into tabname(datecol) value(to_date('20140214','yyyymmdd')) ;  -- 插入to_date
--insert into tabname(datecol) value(to_date('20140214204700','yyyymmddhh24miss')) ;  -- 插入to_date
insert into xjytab(col1,col2,col3TIME,D) values(2,'test:varchar2',to_date('20160601','yyyymmdd'),sysdate+1);

delete from xjytab where col3TIME<SYSDATE-30; 

UPDATE XJYTAB SET COL1=3,COL2='UPDATE TEST',COL3TIME=SYSDATE
   WHERE D=TO_DATE('2016-09-02-12','YYYY-MM-DD-HH');

--****************************************************************screen
SQL> CREATE TABLE xjytab(col1 number,col2 varchar2(20),col3time date);

Table created

SQL> alter table xjytab
  2  add(d date);

Table altered

SQL> insert into xjytab(col1,col2,col3TIME,D) values(2,'test:varchar2',to_date
  2  ('20160601','yyyymmdd'),sysdate+1);

1 row inserted

SQL> select *from xjytab;

      COL1 COL2                 COL3TIME    D
---------- -------------------- ----------- -----------
         2 test:varchar2        2016/6/1    2016/9/2 17

SQL> delete from xjytab where col3TIME<SYSDATE-30; 

1 rows deleted

SQL> insert into xjytab(col1,col2,col3TIME,D) values(2,'test:varchar2',to_date
  2  ('20160601','yyyymmdd'),sysdate+1);

1 row inserted

SQL> UPDATE XJYTAB SET COL1=3,COL2='UPDATE TEST',COL3TIME=SYSDATE
  2  WHERE D=TO_DATE('2016-09-02-12','YYYY-MM-DD-HH');

0 rows updated
SQL> update xjytab set d=sysdate where col1=2;

1 row updated

SQL> select *from xjytab
  2  ;

      COL1 COL2                 COL3TIME    D
---------- -------------------- ----------- -----------
         2 test:varchar2        2016/6/1    2016/9/2 10

SQL> select count(*) from xjytab where d=to_date(
  2  '2016-09-02-10-00-00','yyyy-mm-dd-hh24-mi-ss');

  COUNT(*)
----------
         0

SQL> select to_char(d,'yyyy'),to_char(d,'hh24'),to_char(d,'mi')
  2  ,to_char(d,'ss') from xjytab where col1=2;

TO_CHAR(D,'YYYY') TO_CHAR(D,'HH24') TO_CHAR(D,'MI') TO_CHAR(D,'SS')
----------------- ----------------- --------------- ---------------
2016              10                14              32

SQL> select to_char(d,'yyyy') as 年,to_char(d,'hh24') as 时 ,to_char(d,'mi') as 分
  2  ,to_char(d,'ss') as 秒 from xjytab where col1=2;

年   时 分 秒
---- -- -- --
2016 10 14 32

SQL> update xjytab set col1=3,col2='update_test',col3time=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') where
  2  d=to_date('2016-09-02-10-14-32','yyyy-mm-dd-hh24-mi-ss');

1 row updated

SQL> select *from xjytab;

      COL1 COL2                 COL3TIME    D
---------- -------------------- ----------- -----------
         3 update_test          2016/9/2    2016/9/2 10
        
SQL> select to_char(col3time,'yyyy') as 年,to_char(col3time,'hh24') as 时 ,to_char(col3time,'mi') as 分
  2  ,to_char(col3time,'ss') as 秒 from xjytab where col1=3;

年   时 分 秒
---- -- -- --
2016 00 00 00

-----------------------------
dml statements
-----------------------------delete
delete [#]<table_name>
 where <where_clause>;
-----------------------------insert
insert into [#]<table_name>
  (<column_list>)
values
  (<value_list>);
-----------------------------lock record
select [#]<item_list>
  into <variable_list>
  from <table_name>
 where <where_clause> 
   for update nowait;
-----------------------------lock table
[$QUERY tables =
        select lower(object_name) from user_objects
        where object_type = 'TABLE'
        order by object_name]
lock table [Table name=$tables,...] in 
[Mode=row share,row exclusive,share update,share,share row exclusive,exclusive] 
mode[No wait=/" nowait"];
-----------------------------select
select [#]<item_list>
  from <table_list>
 where <where_clause>
 order by <order_by_clause> 
----------------------------select into
select [#]<item_list>
  into <variable_list>
  from <table_list>
 where <where_clause>;
 ---------------------------sequence nextval
 [$QUERY sequences =
        select lower(object_name) from user_objects
        where object_type = 'SEQUENCE'
        order by object_name]
select [Sequence=$sequences,...].nextval into [Variable name] from dual;
-----------------------------set transaction
set transaction 
[Mode=read only,read write,isolation level serializable,isolation level read committed,
use rollback segment 
<rollback segment>];
-----------------------------update
update [#]<table_name>
   set <column_assignment>
 where <where_clause>;
-----------------------------
Error handing
-----------------------------exception block
[$TEXT exceptions=access_into_null, collection_is_null, cursor_already_open, dup_val_on_index,
 invalid_cursor, invalid_number, login_denied, no_data_found, not_logged_on, others, program_error, 
 rowtype_mismatch, self_is_null, storage_error, subscript_beyond_count, subscript_outside_limit, 
 sys_invalid_rowid, timeout_on_resource, too_many_rows, value_error, zero_divide]
begin
  [#]
exception
  when [Exception=$exceptions] then
    <statements>
[Others=end;/"  when others then
    <statements>
end;"]
-----------------------------exception init
[Name] exception;
pragma exception_init([Name], -[Error number (positive)]);
-----------------------------raise_application_error
Raise_application_error(-[Error number=20000], '[Message]');
-----------------------------User-defined exception
[Name] exception;
-----------------------------
loops
-----------------------------basic loop
loop
  [#]
  exit when [End condition];
end loop;
-----------------------------collection loop
[Index variable=i] := [Collection variable].first;
while [Index variable] is not null 
loop
  [#]  
  [Index variable] := [Collection variable].next([Index variable]);
end loop;
-----------------------------explicit cursor loop
open [Cursor name];
loop
  fetch [Cursor name] into [Cursor record variable];
  exit when [Cursor name]%notfound;
  [#]
end loop;
close [Cursor name];
-----------------------------implicit cursor loop
for [Cursor name=c] in ([Select statement=select ])
loop
  [#]
end loop;
-----------------------------integer for loop
for [Index variable=i] in [Reverse = /"reverse "][Lower bound=1]..[Upper bound=10]
loop
  [#]
end loop;
-----------------------------while loop
while [Loop condition=true]
loop
  [#]
end loop;
-----------------------------
package elements
-----------------------------function declaration
function [Name][+Parameters="("][Parameters="Name in type, Name in type, ..."][+Parameters=")"] 
return [Return type=varchar2,integer,number,date,boolean,long,long raw,clob,blob,binary_integer,
<table.column>%type,<table>%rowtype,...];
-----------------------------function declaration with Purity level
function [Name][+Parameters="("][Parameters="Name in type, Name in type, ..."][+Parameters=")"] 
return [Return type=varchar2,integer,number,date,boolean,long,long raw,clob,blob,binary_integer,
<table.column>%type,<table>%rowtype,...];
pragma restrict_references([Name], [Purity level=RNPS,WNPS,RNDS,WNDS,...]);
-----------------------------function implementation
function [Name][+Parameters="("][Parameters="Name in type, Name in type, ..."][+Parameters=")"] 
return [Return type=varchar2,integer,number,date,boolean,long,long raw,clob,blob,binary_integer,
<table.column>%type,<table>%rowtype,...] is
  Result [Return type];
begin
  [#]
  return(Result);
end [Name];
-----------------------------procedure declaration
procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"];
-----------------------------procedure declaration with Purity level
procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"];
pragma restrict_references([Name], [Purity level=RNPS,WNPS,RNDS,WNDS,...]);

-----------------------------procedure implementation
procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"] is
begin
  [#]
end [Name];
-----------------------------
program units
-----------------------------function
create or replace function [Name][+Parameters="("][Parameters="Name in type, Name in type, ..."][+Parameters=")"] 
return [Return type=varchar2,integer,number,date,boolean,long,long raw,clob,blob,binary_integer,
<table.column>%type,<table>%rowtype,...] is
  Result [Return type];
begin
  [#]
  return(Result);
end [Name];
/
-----------------------------java source
create or replace and compile java source named [Name] as
public class [Class]
{
  public static void entry()
  {
  }
}
/
----------------------------package
create or replace package [Name] is

  -- Author  : $OSUSER
  -- Created : $DATE $TIME
  -- Purpose : [Purpose]
  
  -- Public type declarations
  [#]type <TypeName> is <Datatype>;
  
  -- Public constant declarations
  <ConstantName> constant <Datatype> := <Value>;

  -- Public variable declarations
  <VariableName> <Datatype>;

  -- Public function and procedure declarations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;

end [Name];
/
create or replace package body [Name] is

  -- Private type declarations
  type <TypeName> is <Datatype>;
  
  -- Private constant declarations
  <ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  <VariableName> <Datatype>;

  -- Function and procedure implementations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
    <LocalVariable> <Datatype>;
  begin
    <Statement>;
    return(<Result>);
  end;

begin
  -- Initialization
  <Statement>;
end [Name];
/
----------------------------package body
create or replace package body [Name] is

  -- Private type declarations
  [#]type <TypeName> is <Datatype>;
  
  -- Private constant declarations
  <ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  <VariableName> <Datatype>;

  -- Function and procedure implementations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
    <LocalVariable> <Datatype>;
  begin
    <Statement>;
    return(<Result>);
  end;

begin
  -- Initialization
  <Statement>;
end [Name];
/
-----------------------------package specification
create or replace package [Name] is

  -- Author  : $OSUSER
  -- Created : $DATE $TIME
  -- Purpose : [Purpose]
  
  -- Public type declarations
  [#]type <TypeName> is <Datatype>;
  
  -- Public constant declarations
  <ConstantName> constant <Datatype> := <Value>;

  -- Public variable declarations
  <VariableName> <Datatype>;

  -- Public function and procedure declarations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;

end [Name];
/
-----------------------------procedure
create or replace procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"] is
begin
  [#]
end [Name];
/
-----------------------------trigger_name
[$QUERY tables_and_views =
        select lower(object_name) from user_objects
        where object_type in ('TABLE', 'VIEW')
        order by object_type, object_name]
create or replace trigger [Name]
  [Fires = before, after, instead of] [Event = insert, update, delete, ...] on [Table or view = $tables_and_views,...]  
  [Statement level? = for each row/]
declare
  -- local variables here
begin
  [#]
end [Name];
/
----------------------------type
create or replace type [Name] as object
(
  -- Author  : $OSUSER
  -- Created : $DATE $TIME
  -- Purpose : [Purpose]
  
  -- Attributes
  <Attribute> <Datatype>,
  
  -- Member functions and procedures
  member procedure <ProcedureName>(<Parameter> <Datatype>)
)
/
create or replace type body [Name] is
  
  -- Member procedures and functions
  member procedure <ProcedureName>(<Parameter> <Datatype>) is
  begin
    <Statements>;
  end;
  
end;
/
----------------------------type body
create or replace type body [Name] is
  
  -- Member procedures and functions
  member procedure <ProcedureName>(<Parameter> <Datatype>) is
  begin
    <Statements>;
  end;
  
end;
/
-----------------------------type specification
create or replace type [Name] as object
(
  -- Author  : $OSUSER
  -- Created : $DATE $TIME
  -- Purpose : [Purpose]
  
  -- Attributes
  [#]<Attribute> <Datatype>,
  
  -- Member functions and procedures
  member procedure <ProcedureName>(<Parameter> <Datatype>)
)
/
-----------------------------
创建过程语法： 

create [or replace] procedure procedure_name 
[ (argment [ { in| in out }] type, 
argment [ { in | out | in out } ] type 
{ is | as } 
<类型.变量的说明> 
( 注: 不用 declare 语句 ) 
Begin 
<执行部分> 
exception 
<可选的异常处理说明> 
end; 

l 这里的IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数； 
l 在存储过程内的变量类型只能指定变量类型；不能指定长度； 
l 在AS或IS 后声明要用到的变量名称和变量类型及长度； 
l 在AS或IS 后声明变量不要加declare 语句。