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 语句。