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

ruby-plsql Cheat Sheet Cheat Sheet

Jacek Gebal (jgebal)

via

Resources

Select from a table/view (cont)

ruby-plsql project page

plsql.e m p l oy e es.se l ec t (:all, " O RDER BY employ e e_ i d")

-spec

ruby-p l sq l -spec project page

plsql.employees.all("ORDER BY employ e e_ i d")

Raimonds Simano v skis blog

plsql.employees.all(:order_by => :emplo y ee_id)

blog

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

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

Connection parameters

# returns all rows from a table sorted using ORDER BY

plsql.c o n n ec t io n .p r ef e tc h _rows = 100

plsql.e m p l oy e es.al l (: e mp l oy e e_id => 2, :order_by => :emplo y ee_id)

# sets number of rows to be fetched at once

[ {:empl o yee_id => 2, :name => 'Tony Stark', :hire_date => nil} ]

# returns all rows from a table with WHERE condition

plsql.c o n n ec t io n .d a ta b as e _v e rsion

plsql.e m p l oy e es.all " W HERE employ e e_id = 2 AND hire_date IS NULL"

# returns version of database as an Array => [11, 2, 0, 2]

plsql.employees.all( {:empl o yee_id => 2, :hire_date => nil} )

plsql.d b m s _o u tp u t_ s tream = STDOUT

[ {:empl o yee_id => 2, :name => 'Tony Stark', :hire_date => nil} ]

# redirects dbms_o u tput to standard output (console)

# returns all rows from a table with WHERE condition on NULL value

plsql.d b m s _o u tp u t_ b uf f er _ size = 100_000

plsql.e m p l oy e es.al l (: h ir e _date => :is_no t _null)

# sets dbms_o u tput buffer size to 100,000

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

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

# returns all rows from a table with WHERE condition on NOT NULL

Execute SQL statement or PLSQL block

value

plsql.e x ecute " C REATE SYNONYM employ e es _ sy n onym FOR

plsql.e m p l oy e es.se l ec t (: c ount)

employ e es "

plsql.employees.count

# executes any given string as a SQL or PLSQL statement

plsql.e x ecute <<-SQL

# returns count of rows in the table

C REATE TABLE test_e m pl o yees (

e mp l oy e e_id NUMBER(15),

Update table/view

name VARCHAR2(50),

h ir e _date DATE

plsql.e m p l oy e es.update :name => 'Test'

)

# updates field name in all records

SQL

plsql.e m p l oy e es.update :name => 'Superman II', :where =>

#executes multi-line string statements too

{:empl o yee_id => 7}

plsql.employees.update :name => 'Superman II', :where => " e mp l oy e e_id

Select from a table/view

= 7"

plsql.e m p l oy e es.se l ec t (: f irst, " O RDER BY employ e e_ i d")

# updates field in table with a where condition

plsql.employees.first("ORDER BY employ e e_ i d")

plsql.e m p l oy e es.update :name => 'Superman II', :hire_date =>

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

Time.l o ca l (2 0 00 , 01 , 01), :where => " e mp l oy e e_id = 7"

07'}

# updates two fields in table with a where condition

# returns first row from a table

plsql.e m p l oy e es.se l ec t (: f irst, " W HERE employ e e_id = :a", 2)

plsql.employees.first("WHERE employ e e_id = :a", 2)

plsql.employees.first(:employee_id => 2)

{:empl o yee_id => 2, :name => 'Tony Stark', :hire_date => nil}

# returns first row from a table with WHERE condition

By Jacek Gebal (jgebal)

Ruby-Plsql Cheat Sheet Page 3

Related Articles

Related forms

Related Categories