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

ruby-plsql Cheat Sheet Cheat Sheet

Jacek Gebal (jgebal)

via

Select statements (cont)

Record and Object Types

plsql.s e l e ct ( :all, " S ELECT * FROM employees ORDER BY employ e e_ i d")

#Given a FUNCTION get_fu l l_ n ame( p_employee

employ e es % RO W TYPE ) RETURN VARCHAR2

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

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

plsql.g e t _ fu l l_ n ame( {:p_em p loyee => {:empl o yee_id => 2, :first _ name =>

# returns all rows from a query as an Array of Hashes

'Tony', :last_name => 'Stark', :hire_date => nil} } )

plsql.g e t _ fu l l_ n ame( {:empl o yee_id => 2, :first _ name => 'Tony',

Delete from table/view

:last_name => 'Stark', :hire_date => nil} )

plsql.g e t _ fu l l_ n ame( {'EMPL O YE E _ID' => 2, 'first _ name' => 'Tony',

plsql.e m p l oy e es.delete :emplo y ee_id => 10

'last_ N aMe' => 'Stark', 'hire_ d ate' => nil} )

plsql.employees.delete " e mp l oy e e_id = 10"

'Tony Stark'

#delete record in table with WHERE condition

# Accepts a record as a parameter (by name or by position) and executes

Table/View meta-data

the function returning String (VARCHAR2)

# Record fields can be defined as a Symbol (:empl o ye e _id) or as a String

plsql.e x ecute " C REATE OR REPLACE VIEW employ e es_v AS SELECT *

('empl o ye e _id')

FROM employ e es "

# Works the same way with package level record types and Oracle object

#creates a VIEW

types

plsql.e m p l oy e es _ v.c l ass

Varrays and Nested Tables

PLSQL: : View

# The employ e es_v Object is of PLSQL: : View class

#Given a TYPE table_ o f_int IS TABLE OF INTEGER;

#Given FUNCTION sum_it e ms ( p_ i tems TABLE_ O F_INT) RETURN

plsql.e m p l oy e es.class

INTEGER

PLSQL: : Table

# The employees Object is of PLSQL: : Table class

plsql.s u m _ items( [1,2,3 , 4,5] )

plsql.e m p l oy e es _ sy n on y m.c l ass

plsql.s u m _ items( :p_items => [1,2,3 , 4,5] )

PLSQL: : Table

# The emplye e s_ s ynonym Object is also of PLSQL: : Table class

# Nested tables are passed in and returned as Ruby Array Object type

plsql.e m p l oy e es.co l um n _names

# Works the same way for VARRAYS

plsql.employees_v.column_names

[ employ e e_id, name, hire_date ]

Associ a tive arrays (plsql tables, index-by tables)

# returns all column names in table

#Given a package MY_PACKAGE

plsql.e m p l oy e es.co l umns

# contains TYPE index_ t ab l e_ o f_int IS TABLE OF INTEGER INDEX BY

plsql.employees_v.columns

BINARY_INTEGER;

# contains FUNCTION sum_it e ms ( p_ i tems INDEX_ T AB L E_ O F_INT)

{ :emplo y ee_id => {

RETURN INTEGER;

: po s it i on = >1, :data_ t yp e => " N UM B ER " , :data_ l en g th = >22,

:data_ p re c is i on = >15, :data_ s ca l e= > 0, :char_used=>nil,

plsql.s u m _ items( { -1 => 1, 5 => 2, 3 => 3, 4 => 4} )

: ty p e_ o wn e r= > nil, :type_ n am e =>nil, :sql_t y pe _ na m e= > nil, :nullable

=> false, :data_ d efault => nil}

, ...}

# Associ a tive arrays are passed in and returned as a Ruby Hash

# returns column meta-data

containing list of key value pairs

# Where key is the element position in Array and value is the value at the

position

By Jacek Gebal (jgebal)

Published 19th July, 2015.

Ruby-Plsql Cheat Sheet Page 2

Related Articles

Related forms

Related Categories