Oracle - Curso ultra rapido para desarrolladores nivel Samurai

Constants

****************************

[Name] constant binary_integer := [Value];

[$QUERY columns =

   select lower(table_name) || '.' || lower(column_name)

     from user_tab_columns

    order by table_name, column_name]

[Name] constant [Column = $columns,...]%type := [Value];

[Name] constant date := to_date('[Value (dd-mm-yyyy)]', 'dd-mm-yyyy');

[Name] constant integer := [Value];

[Name] constant number := [Value];

[Name] constant varchar2([Length]) := '[Value]';

Default

****************************

[$WINDOW TYPE = COMMAND]

[$WINDOW TYPE = PLAN]

select * from [#]

[$WINDOW TYPE = SQL]

create materialized view [Name] as

  select [Item list=*]

    from [Table list]

   [Where clause=where ]

[$WINDOW TYPE = SQL]

[$WINDOW TYPE = TEST]

-- Created on $DATE by $OSUSER

declare

  -- Local variables here

  i integer;

begin

  -- Test statements here

  [#]

end;

[$WINDOW TYPE = SQL]

create or replace view [Name] as

  select [Item list=*]

    from [Table list]

   [+Where clause="where "][Where clause=]

DML_Tatements

****************************

delete [#]<table_name>

 where <where_clause>;

insert into [#]<table_name>

  (<column_list>)

values

  (<value_list>);

select [#]<item_list>

  into <variable_list>

  from <table_name>

 where <where_clause>

   for update nowait;

[$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 [#]<item_list>

  into <variable_list>

  from <table_list>

 where <where_clause>;

select [#]<item_list>

  from <table_list>

 where <where_clause>

 order by <order_by_clause>

[$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 [Mode=read only,read write,isolation level serializable,isolation level read committed,use rollback segment <rollback segment>];

update [#]<table_name>

   set <column_assignment>

 where <where_clause>;

Error_Handling

****************************

[$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;"]

[Name] exception;

pragma exception_init([Name], -[Error number (positive)]);

Raise_application_error(-[Error number=20000], '[Message]');

[Name] exception;

Loops

****************************

loop

  [#]

  exit when [End condition];

end loop;

[Index variable=i] := [Collection variable].first;

while [Index variable] is not null

loop

  [#]

  [Index variable] := [Collection variable].next([Index variable]);

end loop;

open [Cursor name];

loop

  fetch [Cursor name] into [Cursor record variable];

  exit when [Cursor name]%notfound;

  [#]

end loop;

close [Cursor name];

for [Cursor name=c] in ([Select statement=select ])

loop

  [#]

end loop;

for [Index variable=i] in [Reverse = /"reverse "][Lower bound=1]..[Upper bound=10]

loop

  [#]

end loop;

while [Loop condition=true]

loop

  [#]

end loop;

Package_Elements

****************************

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 [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,...];

-- Author  : $OSUSER

-- Created : $DATE $TIME

-- Purpose : [Purpose]

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 [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"];

pragma restrict_references([Name], [Purity level=RNPS,WNPS,RNDS,WNDS,...]);

procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"];

-- Author  : $OSUSER

-- Created : $DATE $TIME

-- Purpose : [Purpose]

procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"] is

begin

  [#]

end [Name];

PLSQL_Types

****************************

type [Name] is table of [Element datatype=varchar2,integer,number,date,boolean,long,long raw,clob,blob,binary_integer,<table.column>%type,<table>%rowtype,...][Not null=/" not null"] index by binary_integer;

type [Name] is table of [Element datatype=varchar2,integer,number,date,<table.column>%type,<table>%rowtype,...][Not null=/" not null"];

type [Name] is record

(

  [#]<Field> <Datatype>,

  <Field> <Datatype>

);

[$QUERY tables_and_views =

        select lower(object_name) from user_objects

        where object_type in ('TABLE', 'VIEW')

        order by object_type, object_name]

type [Name] is ref cursor return [Table or view = $tables_and_views,...]%rowtype;

type [Name] is varray([Size limit]) of [Element datatype=varchar2,integer,number,date,<table.column>%type,<table>%rowtype,...][Not null=/" not null"];

Program_Units

****************************

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];

/

create or replace and compile java source named [Name] as

public class [Class]

{

  public static void entry()

  {

  }

}

/

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];

/

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 [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];

/

create or replace procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"] is

begin

  [#]

end [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];

/

create or replace type body [Name] is

  -- Member procedures and functions

  member procedure <ProcedureName>(<Parameter> <Datatype>) is

  begin

    <Statements>;

  end;

end;

/

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 [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;

/

SQL_Functions

****************************

Abs([#]n)

Acos([#]n)

Add_months([#]date, months)

Ascii([#]c)

Asin([#]n)

Atan([#]n)

Atan2([#]n, m)

Avg([#]expr)

BFilename([#]directory, filename)

Ceil([#]n)

CharToRowid([#]char)

Chr([#]n)

Concat([#]c1, c2)

Convert([#]char, dest_char_set, source_char_set)

Cos([#]radians)

Cosh([#]radians)

Count([#]expr)

Deref([#]e)

Dump([#]expr, return_fmt, start_position, length)

Empty_BLOB()

Empty_CLOB()

Exp([#]n)

Floor([#]n)

Greatest([#]expr1, expr2, ...)

Grouping([#]expr)

HexToRaw([#]char)

InitCap([#]char)

Instr([#]char, search_string, startpos, occurrence)

Instrb([#]char, search_string, startpos, occurrence)

Last_day([#]date)

Least([#]expr1, expr2, ...)

Length([#]char)

Lengthb([#]char)

Ln([#]n)

Log([#]base, n)

Lower([#]char)

LPad([#]char, length, pad_string)

LTrim([#]char, set)

Make_ref([#]table, key1, key2, ...)

Max([#]expr)

Min([#]expr)

Mod([#]n, divisor)

Months_between([#]high_date, low_date)

New_time([#]date, from_timezone, to_timezone)

Next_day([#]date, day)

NLSSort([#]char, nlsparam)

NLS_charset_decl_len([#]bytecnt, csid)

NLS_charset_id([#]csid)

NLS_charset_name([#]n)

NLS_initcap([#]char, nlsparam)

NLS_lower([#]char, nlsparam)

NLS_upper([#]char, nlsparam)

Nvl([#]expr1, expr2)

Power([#]n, power)

RawToHex([#]raw)

Ref([#]c)

RefToHex([#]ref)

Replace([#]char, search_string, replacement_string)

Round([#]n, places)

RowidToChar([#]rowid)

RPad([#]char, length, pad_string)

RTrim([#]char, set)

Sign([#]n)

Sin([#]radians)

Sinh([#]radians)

Soundex([#]char)

Sqrt([#]n)

Stddev([#]expr)

Substr([#]char, startpos, length)

Substrb([#]char, startpos, length)

Sum([#]expr)

Sysdate

SYS_Context([#]namespace, attribute_name)

SYS_guid()

Tan([#]radians)

Tanh([#]radians)

To_char([#]expr, format, nlsparam)

To_date([#]char, format, nlsparam)

To_LOB([#]long_column)

To_multi_byte([#]char)

To_number([#]char, format, nlsparam)

To_single_byte([#]char)

Translate([#]char, from_chars, to_chars)

Trim([#]Leading|Trailing|Both trim_character from trim_source)

Trunc([#]date, format)

Trunc([#]n, decimals)

Uid

Upper([#]char)

User

Userenv([#]option)

Value([#]c)

Variance([#]expr)

VSize([#]expr)

Type_elements

****************************

member 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,...]),

member 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,...],

-- Author  : $OSUSER

-- Created : $DATE $TIME

-- Purpose : [Purpose]

member 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];

member procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"],

pragma restrict_references([Name], [Purity level=RNPS,WNPS,RNDS,WNDS,...]),

member procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"],

-- Author  : $OSUSER

-- Created : $DATE $TIME

-- Purpose : [Purpose]

member procedure [Name][+Parameters="("][Parameters="Name in out type, Name in out type, ..."][+Parameters=")"] is

begin

  [#]

end [Name];

Variables

****************************

[#]i binary_integer;

[#]b BLOB;

[#]b boolean;

[#]c CLOB;

[$QUERY columns =

   select lower(table_name) || '.' || lower(column_name)

     from user_tab_columns

    order by table_name, column_name]

[#]c [Column = $columns,...]%type;

cursor [Name=c][+Parameters = " ("][Parameters = "Name type, Name type, ..."][+Parameters = ")"] is

  [Select statement = select ...];

[#]d date;

[#]i integer;

[#]l long raw;

[#]l long;

[#]n number;

[$QUERY tables_and_views =

        select lower(object_name) from user_objects

        where object_type in ('TABLE', 'VIEW')

        order by object_type, object_name]

[#]r [Table or view = $tables_and_views,...]%rowtype;

[#]v varchar2(10);