Show Menu
Cheatography

ruby-plsql Cheat Sheet Cheat Sheet by

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

Resources

 

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
               
 

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

          vim-rails Cheat Sheet
            Ruby Cheat Sheet by CodeConquest

          More Cheat Sheets by jgebal

          utPLSQL v2 vs. ruby-plsql feature comparison Cheat Sheet
          utPLSQL v3.1.2 Cheat Sheet