Skip to content

Commit 048d357

Browse files
OsBlaineOraOsBlaineOra
committed
added delete examples
1 parent a818f1e commit 048d357

File tree

7 files changed

+318
-0
lines changed

7 files changed

+318
-0
lines changed

‎ruby/CRUD-examples/delete/extra_fun_1.rb

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Extra Fun 1"
3+
# Using the base template, the example code executes a simple delete using named bind variables.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
10+
con = OCI8.new(connectString)
11+
12+
# Query all rows
13+
statement = 'select id, name, age, notes from lcs_people order by id'
14+
15+
if data_type == 'pets'
16+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
17+
end
18+
19+
cursor = con.parse(statement)
20+
cursor.exec
21+
printf " %s:\n", label
22+
cursor.fetch do |row|
23+
if data_type == 'people'
24+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
25+
else
26+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
27+
end
28+
end
29+
printf "\n"
30+
end
31+
32+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data', 'pets')
35+
36+
statement = 'delete from lcs_pets where type = :type'
37+
cursor = con.parse(statement)
38+
cursor.bind_param(:type, 'bird')
39+
cursor.exec
40+
con.commit
41+
42+
get_all_rows('New Data', 'pets')

‎ruby/CRUD-examples/delete/extra_fun_2.rb

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Extra Fun 2"
3+
# Using the base template, the example code executes two simple deletes using named bind variables.
4+
# The child records are removed, followed by the parent record.
5+
6+
require 'oci8'
7+
connectString = ENV['DB_CONNECT']
8+
9+
def get_all_rows(label, data_type = 'people')
10+
connectString = ENV['DB_CONNECT']
11+
con = OCI8.new(connectString)
12+
13+
# Query all rows
14+
statement = 'select id, name, age, notes from lcs_people order by id'
15+
16+
if data_type == 'pets'
17+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
18+
end
19+
20+
cursor = con.parse(statement)
21+
cursor.exec
22+
printf " %s:\n", label
23+
cursor.fetch do |row|
24+
if data_type == 'people'
25+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
26+
else
27+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
28+
end
29+
end
30+
printf "\n"
31+
end
32+
33+
con = OCI8.new(connectString)
34+
35+
get_all_rows('Original People Data', 'people')
36+
get_all_rows('Original Pet Data', 'pets')
37+
38+
statement = 'delete from lcs_pets where owner = :owner'
39+
cursor = con.parse(statement)
40+
cursor.bind_param(:owner, 5)
41+
cursor.exec
42+
43+
statement = 'delete from lcs_people where id = :id'
44+
cursor = con.parse(statement)
45+
cursor.bind_param(:id, 5)
46+
cursor.exec
47+
con.commit
48+
49+
get_all_rows('New People Data', 'people')
50+
get_all_rows('New Pet Data', 'pets')

‎ruby/CRUD-examples/delete/foreign_keys.rb

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Deleting records referenced by Foreign Keys" 1st example
3+
# Using the base template, the example code executes a simple delete using named bind variables.
4+
# When following the tutorial with default data this section intentionally throws an error
5+
# to demonstrate foreign key functionality.
6+
7+
require 'oci8'
8+
connectString = ENV['DB_CONNECT']
9+
10+
def get_all_rows(label, data_type = 'people')
11+
connectString = ENV['DB_CONNECT']
12+
con = OCI8.new(connectString)
13+
14+
# Query all rows
15+
statement = 'select id, name, age, notes from lcs_people order by id'
16+
17+
if data_type == 'pets'
18+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
19+
end
20+
21+
cursor = con.parse(statement)
22+
cursor.exec
23+
printf " %s:\n", label
24+
cursor.fetch do |row|
25+
if data_type == 'people'
26+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
27+
else
28+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
29+
end
30+
end
31+
printf "\n"
32+
end
33+
34+
con = OCI8.new(connectString)
35+
36+
get_all_rows('Original People Data', 'people')
37+
get_all_rows('Original Pet Data', 'pets')
38+
39+
statement = 'delete from lcs_people where id = :id'
40+
cursor = con.parse(statement)
41+
cursor.bind_param(:id, 1)
42+
cursor.exec
43+
con.commit
44+
45+
get_all_rows('New People Data', 'people')
46+
get_all_rows('New Pet Data', 'pets')

‎ruby/CRUD-examples/delete/handle_pets.rb

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,51 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Deleting records referenced by Foreign Keys" 2nd example
3+
# Using the base template, the example code executes two simple deletes using named bind variables.
4+
# The child records are removed, followed by the parent record.
5+
6+
require 'oci8'
7+
connectString = ENV['DB_CONNECT']
8+
9+
def get_all_rows(label, data_type = 'people')
10+
connectString = ENV['DB_CONNECT']
11+
con = OCI8.new(connectString)
12+
13+
# Query all rows
14+
statement = 'select id, name, age, notes from lcs_people order by id'
15+
16+
if data_type == 'pets'
17+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
18+
end
19+
20+
cursor = con.parse(statement)
21+
cursor.exec
22+
printf " %s:\n", label
23+
cursor.fetch do |row|
24+
if data_type == 'people'
25+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
26+
else
27+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
28+
end
29+
end
30+
printf "\n"
31+
end
32+
33+
con = OCI8.new(connectString)
34+
35+
get_all_rows('Original People Data', 'people')
36+
get_all_rows('Original Pet Data', 'pets')
37+
38+
statement = 'update lcs_pets set owner = :newOwner where owner = :oldOwner'
39+
cursor = con.parse(statement)
40+
cursor.bind_param(:newOwner, 2)
41+
cursor.bind_param(:oldOwner, 1)
42+
cursor.exec
43+
44+
statement = 'delete from lcs_people where id = :id'
45+
cursor = con.parse(statement)
46+
cursor.bind_param(:id, 1)
47+
cursor.exec
48+
con.commit
49+
50+
get_all_rows('New People Data', 'people')
51+
get_all_rows('New Pet Data', 'pets')

‎ruby/CRUD-examples/delete/reset_data.rb

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Resetting the data"
3+
# The following resets the data for use with the update section
4+
# For both tables:
5+
# Table data is removed.
6+
# The identity column is set to start with the id after the starting data.
7+
# Using the executemany function an array of starting data is inserted into the table.
8+
9+
require 'oci8'
10+
connectString = ENV['DB_CONNECT']
11+
con = OCI8.new(connectString)
12+
13+
# Delete rows
14+
cursor = con.parse('delete from lcs_pets')
15+
cursor.exec
16+
17+
# Reset Identity Coulmn
18+
cursor = con.parse('alter table lcs_pets modify id generated BY DEFAULT as identity (START WITH 8)')
19+
cursor.exec
20+
21+
# Delete rows
22+
cursor = con.parse('delete from lcs_people')
23+
cursor.exec
24+
25+
# Reset Identity Coulmn
26+
cursor = con.parse('alter table lcs_people modify id generated BY DEFAULT as identity (START WITH 8)')
27+
cursor.exec
28+
29+
# Insert default people rows
30+
cursor = con.parse('INSERT INTO lcs_people(id, name, age, notes) VALUES (:id, :name, :age, :notes)')
31+
cursor.max_array_size = 7
32+
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
33+
cursor.bind_param_array(:name, %w[Bob Kim Cheryl Bob Stacey Pete Pat])
34+
cursor.bind_param_array(:age, [35, 27, 23, 27, 45, 23, 36])
35+
cursor.bind_param_array(:notes, ['I like dogs', 'I like birds', 'I like horses', 'I like rabbits', 'I like snakes', 'I like cats', 'I like dogs'])
36+
people_row_count = cursor.exec_array
37+
printf " %d people rows inserted\n", people_row_count
38+
39+
# Insert default pet rows
40+
cursor = con.parse('INSERT INTO lcs_pets(id, name, owner, type) VALUES (:id, :name, :owner, :type)')
41+
cursor.max_array_size = 7
42+
cursor.bind_param_array(:id, [1, 2, 3, 4, 5, 6, 7])
43+
cursor.bind_param_array(:name, %w[Duke Dragon Sneaky Red Red Buster Fido])
44+
cursor.bind_param_array(:owner, [1, 2, 5, 2, 3, 1, 7])
45+
cursor.bind_param_array(:type, %w[dog bird snake bird horse dog cat])
46+
pet_row_count = cursor.exec_array
47+
printf " %d pet rows inserted\n", pet_row_count
48+
49+
con.commit

‎ruby/CRUD-examples/delete/simple.rb

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Simple delete"
3+
# Using the base template, the example code executes a simple delete using named bind variables.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
10+
con = OCI8.new(connectString)
11+
12+
# Query all rows
13+
statement = 'select id, name, age, notes from lcs_people order by id'
14+
15+
if data_type == 'pets'
16+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
17+
end
18+
19+
cursor = con.parse(statement)
20+
cursor.exec
21+
printf " %s:\n", label
22+
cursor.fetch do |row|
23+
if data_type == 'people'
24+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
25+
else
26+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
27+
end
28+
end
29+
printf "\n"
30+
end
31+
32+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data', 'pets')
35+
36+
statement = 'delete from lcs_pets where id = :id'
37+
cursor = con.parse(statement)
38+
cursor.bind_param(:id, 1)
39+
cursor.exec
40+
con.commit
41+
42+
get_all_rows('New Data', 'pets')

‎ruby/CRUD-examples/delete/template.rb

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
# Code Sample from the tutorial at https://learncodeshare.net/2016/11/09/delete-crud-using-ruby-oci8/
2+
# section titled "Boilerplate template"
3+
# The following code is used as the base template for the other examples.
4+
5+
require 'oci8'
6+
connectString = ENV['DB_CONNECT']
7+
8+
def get_all_rows(label, data_type = 'people')
9+
connectString = ENV['DB_CONNECT']
10+
con = OCI8.new(connectString)
11+
12+
# Query all rows
13+
statement = 'select id, name, age, notes from lcs_people order by id'
14+
15+
if data_type == 'pets'
16+
statement = 'select id, name, owner, type from lcs_pets order by owner, id'
17+
end
18+
19+
cursor = con.parse(statement)
20+
cursor.exec
21+
printf " %s:\n", label
22+
cursor.fetch do |row|
23+
if data_type == 'people'
24+
printf " Id: %d, Name: %s, Age: %d, Notes: %s\n", row[0], row[1], row[2], row[3]
25+
else
26+
printf " Id: %d, Name: %s, Owner: %d, Type: %s\n", row[0], row[1], row[2], row[3]
27+
end
28+
end
29+
printf "\n"
30+
end
31+
32+
con = OCI8.new(connectString)
33+
34+
get_all_rows('Original Data', 'pets')
35+
36+
# Your code here
37+
38+
get_all_rows('New Data', 'pets')

0 commit comments

Comments
 (0)