Stored procedures Flashcards
Cite a main problem with mixing sql code with say c#, java, or python production code?
If any of the tables or rows are changed that code will also have to be refactored. Use stored procedures instead
What is considered the ‘body’ of this stored procedure?
CREATE PROCEDURE get_clients()
BEGIN
SELECT * FROM clients
END
SELECT * FROM clients
What is the syntax for creating a stored procedure?
CREATE PROCEDURE named_procedure()
True or False
a single statement in a store procedure requires termination
True
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
select * from clients;
END$$
What is the purpose of the DELIMITER clause in sql?
DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
select * from clients;
END$$
treats the entire stored procedure as a single unit
True or False
You can use anything as a DEMILITER (&&, **, $$, //)
True
but $$, // seems conventional
True or False
When executing a stored procedure, a resulting table is generated?
False
not in mysql
In myql what must be done to see any changes after creating a stored procedure?
must refresh the left workbench panel
What is the syntax for calling a stored procedure?
CALL named_procedure()
– Create a stored procedure called
– get_invoices_with_balance
– to return all the invoices with a balance > 0
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
select *
from invoices_with_balance
where balance > 0;
END$$
DELIMITER ;
True or False
When creating a view, stored procedure, trigger … it’s best practice to make sure the underlining query works as anticipated.
True, always do this!
True or False
Whenever you change the DELIMITER, you must change it back to it’s default value of?
True
DELIMITER ;
To create a stored procedure in mysql workbench all you have to do is?
Right-click stored procedure, create stored procedure.
When creating a store procedure in mysql through the workbench, you always have to adjust the delimiter.
No this is performed automatically in mysql workbench
Why does mysql use back ticks when naming a stored procedure? ie get_payments
To prevent naming clashes with sql keywords
What is the syntax to drop a stored procedure?
DROP PROCEDURE ‘get_payments’
What is the syntax for dropping a store procedure if it exists?
DROP PROCEDURE IF EXISTS ‘get_payment’
True or False
An error will be thrown if you attempt to drop a view or stored procedure if it no longer exists or was previously dropped.
True
Which is why you should use IF EXITS condition when dropping views or stored procedures
Write a stored procedure that returns the state when called.
CALL get_clients_by_state(‘CA’)
DELIMITER $$
CREAT STORED PROCEDURE get_cleints_by_state
(
state CHAR(2) – ‘FL’
)
BEGIN
SELECT * FROM clients c
WHERE c.state = state;
END $$
True or False
All parameters are required in mysql?
True
– Write a stored procedure to return invoices
– for a given client
– call get_invoices_by_client
create procedure ‘invoices_by_client’
(
client_id INT
)
begin
select *
from invoices i
where i.client_id = client_id;
end
True or False
When creating a stored procedure via workbench console you have to ctrl+enter just like executing any sql query.
False
You have to click the apply button on the bottom right corner (apply | revert)
What syntax is required when setting a stored procedure default value with an IF clause?
END IF;
Break down this stored procedure. By name? Was this create in the workbench? What is the delimiter $$ and end $$ mean? What is happening with the IF clause statement?
name: get_clients_by_state,
not created in workbench can see delimiter being set manually, the delimiter allows the SP to be seen as a whole, the IF clause is a condition all null values will default to “CA”
What is the query to get this default (NULL) result from a stored procedure ‘get_clients_by_state’
CALL get_clients_by_state(NULL)
True or False
The NULL parameter is required in the following query?
CALL get_clients_by_state(NULL)
True
Refactor this into a single SELECT query
SELECT * FROM clients c
WHERE c.state = IFNULL(state, c.state)
– ifnull c.state = state is true, state will be returned
– ifnull false then c.state = c.state which is always true
– Write a stored procedure called get_payments
– with two parameters
–
– client_id => INT
– payment_method_id => TINYINT
CREATE PROCEDURE ‘get_payments’(
cleint_id INT,
payment_method_id TINYINT
)
BEGIN
SELECT *
FROM payments p
WHERE
p.client_id = IFNULL(cleint_id, p.client_id) AND
p.payment_method = (payment_method_id, p.payment_method);
END
