Ruby-Plsql Cheat Sheet (Page 4 of 4) in pdf

ruby-plsql Cheat Sheet Cheat Sheet

Jacek Gebal (jgebal)

via

Sequence

Procedure / Function (cont)

plsql.e x ecute " C REATE SEQUENCE employ e es _ se q "

#Given a PROCEDURE copy_proc( p_from VARCHAR2, p_to OUT

VARCHAR2, p_to_d o uble OUT VARCHAR2 )

#executes a statement to create a sequence

plsql.e m p l oy e es _ se q .n e xtval

plsql.c o p y _proc( 'abc', nil, nil)

plsql.c o p y _proc( :p_from => 'abc', :p_to => nil, :p_to_ d ouble => nil)

plsql.c o p y _proc( 'abc' )

# returns NEXTVAL for sequence

{ :p_to => 'abc', :p_to_ d ouble => 'abcabc' }

plsql.e m p l oy e es _ se q .c u rrval

# executes the procedure and returns a Hash of OUT parameters as a

:name => 'value' pairs

# returns CURRVAL for sequence

Cursors

Package

#Given a FUNCTION get_em p olyees RETURN SYS_REFCURSOR

plsql.t e s t _p a ck a ge.class

PLSQL::Package

plsql.g e t _ em p loyees do |result|

# A plsql package is Object of PLSQL: : Pa c kage class

result.columns

end

plsql.t e s t _p a ck a ge.te s t_ v ar i able = 1

[ :emplo y ee_id, :name, :hire_date ]

# Assigns a value to package public variable

# returns the list of columns of a cursor as an Array

plsql.t e s t _p a ck a ge.te s t_ v ar i able

plsql.g e t _ em p loyees do |result|

result.fetch_hash_all

# Reads a value to package public variable

end

plsql.g e t _ em p lo y ees{ |cursor| cursor.fe t ch _ ha s h_all }

Procedure / Function

plsql.g e t _ em p lo y ees{ |any_name| any_na m e.f e t c h_ h as h _all }

[ {:empl o yee_id => 1, :name => 'James bond', :hire_date =>

# given a FUNCTION uppercase( p_string VARCHAR2 ) RETURN

'0007- 0 7- 0 7'}, {...}, ... ]

VARCHAR2

# fetches all rows from a cursor and returns them as an Array of

Hashes

plsql.u p p e rcase( 'xxx' )

plsql.u p p e rcase( :p_string => 'xxx' )

plsql.g e t _ em p lo y ees{ |result| result.fe t ch _ hash }

'XXX'

{:empl o yee_id => 1, :name => 'James bond', :hire_date => '0007-07-

# executes the function binding parameters by position or name and

07'}

returns scalar Object as a value

# fetches one row from a cursor and returns it as a Hash

# given a FUNCTION copy_f u nc t ion( p_from VARCHAR2, p_to OUT

plsql.g e t _ em p lo y ees{ |result| result.fetch }

VARCHAR2, p_to_d o uble OUT VARCHAR2 ) RETURN NUMBER

[1, 'James bond', '0007-07-07']

# fetches one row from a cursor and returns it as a Array of values

plsql.c o p y _f u nc t ion( 'abc', nil, nil)

plsql.c o p y _f u nc t ion( :p_from => 'abc', :p_to => nil, :p_to_ d ouble => nil)

plsql.g e t _ em p lo y ees{ |result| result.fe t ch_all }

plsql.c o p y _f u nc t ion( 'abc' )

[[1, 'James bond', '0007- 0 7- 0 7'], [...], ... ]

[ 3, { :p_to => " a bc " , :p_to_ d ouble => " a bc a bc " } ]

# fetches all rows from a cursor and returns them as an Array of Arrays

# executes the function and returns 2 element Array

of values

# with first element being function result and second element being a

# cursor needs to be accessed inside a block ( do .. end / { .. } )

Hash of OUT parameters

# as cursors are automa t ically closed after the function call ends

By Jacek Gebal (jgebal)

Published 19th July, 2015.

Ruby-Plsql Cheat Sheet Page 4

Related Articles

Related forms

Related Categories