Show Menu

ruby-plsql Cheat Sheet Cheat Sheet by

development     programming     oracle     ruby     ruby-plsql

Session connect

plsql(­:de­fau­lt).co­nnect! {:username => 'hr', :password => 'hr', :database => 'xe'}
# opens a default connection to database
plsql.c­on­nect! {:username => 'hr', :password => 'hr', :database => 'xe'}
# opens a default connection to database
plsql(­:an­oth­er).co­nnect! {:username => 'hr', :password => 'hr', :database => 'xe'}
# opens a second connection (refer­enced by Symbol :another)
plsql(­:an­oth­er).logoff
# discon­nects connection (refer­enced by symbol :another)

Transa­ction

plsql(­:an­oth­er).co­nne­cti­on.a­ut­ocommit = false
#disables auto commit in :another connection
plsql.a­ut­oco­mmit?
# returns the current status of autocommit
plsql.c­ommit
#commits a transa­ction in :default connection
plsql(­:an­oth­er).ro­llback
#rollbacks changes for :another connection
plsql.s­av­epoint "­sav­e_t­his­_po­int­"
#sets a transa­ction savepoint in :default connection
plsql.r­ol­lba­ck_to "­sav­e_t­his­_po­int­"
#rollbacks to specified savepoint in :default connection

Insert into table

plsql.e­mp­loy­ees.insert {:empl­oyee_id => 1, :name => 'James bond', :hire_date => Time.l­oca­l(0­007­,07­,07)}
# inserts one row into the employees table using key-value pairs (Ruby Hash object)
plsql.e­mp­loy­ees.insert {:empl­oyee_id => 2, :name => 'Tony Stark'}
# inserts one row into the employees table, with partial column list
plsql.e­mp­loy­ees.insert [ {:empl­oyee_id => 3, :name => 'Darth Vader'}, {:empl­oyee_id => 4, :name => 'Luke Skywal­ker'}]
# inserts multiple rows into the employees table using Array of Hashes
plsql.e­mp­loy­ees.in­ser­t_v­alues *[ [ 5, 'Batman', Time.l­oca­l(1­990­,01­,01)], [6,'Sp­ide­rma­n',­Tim­e.l­oca­l(1­999­,02­,02)] ]
# inserts multiple rows, specifying Array of Array of values
plsql.e­mp­loy­ees.in­ser­t_v­alues [:empl­oye­e_id, :name], *[ [ 7, 'Super­man'], [8, 'Hulk'] ]
# inserts multiple rows, specifying columns first and subset of values
plsql.e­mp­loy­ees.in­ser­t_v­alues [ 9, 'Thor', Time.l­oca­l(1­990­,09­,09)]
# inserts one row, specifying only Array of values
plsql.e­mp­loy­ees.in­ser­t_v­alues [:empl­oye­e_id, :name], [ 10, 'Sandman' ]
# inserts one row, specifying subset of columns (Array) and Array of values

Select statements

plsql.s­el­ect­(:f­irst, "­SELECT * FROM employ­ees­")
{:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-­07-07'}
# returns first row of a query as a Ruby Hash
plsql.s­el­ect­_on­e("S­ELECT count(*) FROM employ­ees­")
10
# returns a scalar value from a first row from single column query
plsql.s­el­ect­_on­e("S­ELECT employ­ee_id FROM employees WHERE 1=2")
nil
# returns nil Object (NULL) when no data found
plsql.s­el­ect­(:all, "­SELECT * FROM employees ORDER BY employ­ee_­id")
[ {:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-­07-­07'}, {...}, ... ]
# returns all rows from a query as an Array of Hashes

Delete from table/view

plsql.e­mp­loy­ees.delete :emplo­yee_id => 10
plsql.employees.delete "­emp­loy­ee_id = 10"
#delete record in table with WHERE condition

Table/View meta-data

plsql.e­xecute "­CREATE OR REPLACE VIEW employ­ees_v AS SELECT * FROM employ­ees­"
#creates a VIEW
plsql.e­mp­loy­ees­_v.c­lass
PLSQL:­:View
# The employ­ees_v Object is of PLSQL:­:View class
plsql.e­mp­loy­ees.class
PLSQL:­:Table
# The employees Object is of PLSQL:­:Table class
plsql.e­mp­loy­ees­_sy­non­ym.c­lass
PLSQL:­:Table
# The emplye­es_­synonym Object is also of PLSQL:­:Table class
plsql.e­mp­loy­ees.co­lum­n_names
plsql.employees_v.column_names
[ employ­ee_id, name, hire_date ]
# returns all column names in table
plsql.e­mp­loy­ees.co­lumns
plsql.employees_v.columns
{ :emplo­yee_id => {
 ­ ­ ­ ­:po­sit­ion­=>1, :data_­typ­e=>­"­NUM­BER­", :data_­len­gth­=>22, :data_­pre­cis­ion­=>15, :data_­sca­le=­>0, :char_used=>nil,
 ­ ­ ­ ­:ty­pe_­own­er=­>nil, :type_­nam­e=>nil, :sql_t­ype­_na­me=­>nil, :nullable => false, :data_­default => nil}
 ­ , ...}
# returns column meta-data

Record and Object Types

#Given a FUNCTION get_fu­ll_­name( p_employee employ­ees­%RO­WTYPE ) RETURN VARCHAR2

plsql.g­et­_fu­ll_­name( {:p_em­ployee => {:empl­oyee_id => 2, :first­_name => 'Tony', :last_name => 'Stark', :hire_date => nil} } )
plsql.g­et­_fu­ll_­name( {:empl­oyee_id => 2, :first­_name => 'Tony', :last_name => 'Stark', :hire_date => nil} )
plsql.g­et­_fu­ll_­name( {'EMPL­OYE­E_ID' => 2, 'first­_name' => 'Tony', 'last_­NaMe' => 'Stark', 'hire_­date' => nil} )
'Tony Stark'
# Accepts a record as a parameter (by name or by position) and executes the function returning String (VARCHAR2)
# Record fields can be defined as a Symbol (:empl­oye­e_id) or as a String ('empl­oye­e_id')
# Works the same way with package level record types and Oracle object types

Varrays and Nested Tables

#Given a TYPE table_­of_int IS TABLE OF INTEGER;
#Given FUNCTION sum_it­ems­(p_­items TABLE_­OF_INT) RETURN INTEGER

plsql.s­um­_items( [1,2,3­,4,5] )
plsql.s­um­_items( :p_items => [1,2,3­,4,5] )
15
# Nested tables are passed in and returned as Ruby Array Object type
# Works the same way for VARRAYS

Associ­ative arrays (plsql tables, index-by tables)

#Given a package MY_PACKAGE
# contains TYPE index_­tab­le_­of_int IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
# contains FUNCTION sum_it­ems­(p_­items INDEX_­TAB­LE_­OF_INT) RETURN INTEGER;

plsql.m­y_­pac­kag­e.s­um_­items( { -1 => 1, 5 => 2, 3 => 3, 4 => 4} )
10
# Associ­ative arrays are passed in and returned as a Ruby Hash containing list of key value pairs
# Where key is the element position in Array and value is the value at the position
 

Connection parameters

plsql.c­on­nec­tio­n.p­ref­etc­h_rows = 100
# sets number of rows to be fetched at once
plsql.c­on­nec­tio­n.d­ata­bas­e_v­ersion
# returns version of database as an Array => [11, 2, 0, 2]
plsql.d­bm­s_o­utp­ut_­stream = STDOUT
# redirects dbms_o­utput to standard output (console)
plsql.d­bm­s_o­utp­ut_­buf­fer­_size = 100_000
# sets dbms_o­utput buffer size to 100,000

Execute SQL statement or PLSQL block

plsql.e­xecute "­CREATE SYNONYM employ­ees­_sy­nonym FOR employ­ees­"
# executes any given string as a SQL or PLSQL statement
plsql.e­xecute <<-SQL
 ­ ­CREATE TABLE test_e­mpl­oyees (
 ­ ­ ­ ­emp­loy­ee_id NUMBER(15),
 ­ ­ ­ name VARCHAR2(50),
 ­ ­ ­ ­hir­e_date DATE
  )
SQL
#executes multi-line string statements too

Select from a table/view

plsql.e­mp­loy­ees.se­lec­t(:­first, "­ORDER BY employ­ee_­id")
plsql.employees.first("ORDER BY employ­ee_­id")
{:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-­07-07'}
# returns first row from a table
plsql.e­mp­loy­ees.se­lec­t(:­first, "­WHERE employ­ee_id = :a", 2)
plsql.employees.first("WHERE employ­ee_id = :a", 2)
plsql.employees.first(:employee_id => 2)
{:empl­oyee_id => 2, :name => 'Tony Stark', :hire_date => nil}
# returns first row from a table with WHERE condition
plsql.e­mp­loy­ees.se­lec­t(:all, "­ORDER BY employ­ee_­id")
plsql.employees.all("ORDER BY employ­ee_­id")
plsql.employees.all(:order_by => :emplo­yee_id)
[ {:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-­07-­07'}, {...}, ... ]
# returns all rows from a table sorted using ORDER BY
plsql.e­mp­loy­ees.al­l(:­emp­loy­ee_id => 2, :order_by => :emplo­yee_id)
[ {:empl­oyee_id => 2, :name => 'Tony Stark', :hire_date => nil} ]
# returns all rows from a table with WHERE condition
plsql.e­mp­loy­ees.all "­WHERE employ­ee_id = 2 AND hire_date IS NULL"
plsql.employees.all( {:empl­oyee_id => 2, :hire_date => nil} )
[ {:empl­oyee_id => 2, :name => 'Tony Stark', :hire_date => nil} ]
# returns all rows from a table with WHERE condition on NULL value
plsql.e­mp­loy­ees.al­l(:­hir­e_date => :is_no­t_null)
[ {:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-­07-­07'}, {...}, ... ]
# returns all rows from a table with WHERE condition on NOT NULL value
plsql.e­mp­loy­ees.se­lec­t(:­count)
plsql.employees.count
10
# returns count of rows in the table

Update table/view

plsql.e­mp­loy­ees.update :name => 'Test'
# updates field name in all records
plsql.e­mp­loy­ees.update :name => 'Superman II', :where => {:empl­oyee_id => 7}
plsql.employees.update :name => 'Superman II', :where => "­emp­loy­ee_id = 7"
# updates field in table with a where condition
plsql.e­mp­loy­ees.update :name => 'Superman II', :hire_date => Time.l­oca­l(2­000­,01­,01), :where => "­emp­loy­ee_id = 7"
# updates two fields in table with a where condition

Sequence

plsql.e­xecute "­CREATE SEQUENCE employ­ees­_se­q"
#executes a statement to create a sequence
plsql.e­mp­loy­ees­_se­q.n­extval
1
# returns NEXTVAL for sequence
plsql.e­mp­loy­ees­_se­q.c­urrval
1
# returns CURRVAL for sequence

Package

plsql.t­es­t_p­ack­age.class
PLSQL::Package
# A plsql package is Object of PLSQL:­:Pa­ckage class
plsql.t­es­t_p­ack­age.te­st_­var­iable = 1
# Assigns a value to package public variable
plsql.t­es­t_p­ack­age.te­st_­var­iable
1
# Reads a value to package public variable

Procedure / Function

# given a FUNCTION uppercase( p_string VARCHAR2 ) RETURN VARCHAR2

plsql.u­pp­ercase( 'xxx' )
plsql.u­pp­ercase( :p_string => 'xxx' )
'XXX'
# executes the function binding parameters by position or name and returns scalar Object as a value
# given a FUNCTION copy_f­unc­tion( p_from VARCHAR2, p_to OUT VARCHAR2, p_to_d­ouble OUT VARCHAR2 ) RETURN NUMBER

plsql.c­op­y_f­unc­tion( 'abc', nil, nil)
plsql.c­op­y_f­unc­tion( :p_from => 'abc', :p_to => nil, :p_to_­double => nil)
plsql.c­op­y_f­unc­tion( 'abc' )
[ 3, { :p_to => "­abc­", :p_to_­double => "­abc­abc­" } ]
# executes the function and returns 2 element Array
# with first element being function result and second element being a Hash of OUT parameters
#Given a PROCEDURE copy_proc( p_from VARCHAR2, p_to OUT VARCHAR2, p_to_d­ouble OUT VARCHAR2 )

plsql.c­op­y_proc( 'abc', nil, nil)
plsql.c­op­y_proc( :p_from => 'abc', :p_to => nil, :p_to_­double => nil)
plsql.c­op­y_proc( 'abc' )
{ :p_to => 'abc', :p_to_­double => 'abcabc' }
# executes the procedure and returns a Hash of OUT parameters as a :name => 'value' pairs

Cursors

#Given a FUNCTION get_em­polyees RETURN SYS_REFCURSOR

plsql.g­et­_em­ployees do |result|
  result.fields
end
[ :emplo­yee_id, :name, :hire_date ]
# returns the list of columns of a cursor as an Array
plsql.g­et­_em­ployees do |result|
  result.fetch_hash_all
end
plsql.g­et­_em­plo­yees{ |cursor| cursor.fe­tch­_ha­sh_all }
plsql.g­et­_em­plo­yees{ |any_name| any_na­me.f­et­ch_­has­h_all }
[ {:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-­07-­07'}, {...}, ... ]
# fetches all rows from a cursor and returns them as an Array of Hashes
plsql.g­et­_em­plo­yees{ |result| result.fe­tch­_hash }
{:empl­oyee_id => 1, :name => 'James bond', :hire_date => '0007-07-07'}
# fetches one row from a cursor and returns it as a Hash
plsql.g­et­_em­plo­yees{ |result| result.fetch }
[1, 'James bond', '0007-07-07']
# fetches one row from a cursor and returns it as a Array of values
plsql.g­et­_em­plo­yees{ |result| result.fe­tch_all }
[[1, 'James bond', '0007-­07-­07'], [...], ... ]
# fetches all rows from a cursor and returns them as an Array of Arrays of values
# cursor needs to be accessed inside a block ( do .. end / { .. } )
# as cursors are automa­tically closed after the function call ends

Download the ruby-plsql Cheat Sheet Cheat Sheet

4 Pages
//media.cheatography.com/storage/thumb/jgebal_ruby-plsql-cheat-sheet.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

Like this cheat sheet? Check out our sponsors!

Readability-Score.com is a collection of tools to make your writing better. More readabile content means higher conversion rates and better reader engagement. Measure website and document readability, measure keyword density and more!

Click Here To Get Started!

 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          Regular Expressions Cheat Sheet
          vim-rails Cheat Sheet

          More Cheat Sheets by jgebal

          utPLSQL vs. ruby-plsql feature comparison Cheat Sheet