Oracle PL/SQL Stored Procedures and Functions

In this video, we talk about how to create sprocs and functions then how to use them to accomplish various tasks
Video Timeline Index for video:
00:00 to 06:34 How to create a stored procedure
06:34 to 10:34 How to call a stored procedure
10:34 to 13:21 How to code optional parameters
13:21 to 16:48 How to raise an error
16:48 to 29:52 Example: Stored Proc that validates and inserts data
29:52 to 35:18 Functions Syntax and example (return id for vendor name)
35:18 to 37:15 NOTE: Functions don't require inputs (like SYSDATE)
37:15 to 40:27 Example: Function that gets balance due of invoice
40:27 to 43:10 Function within a function

[@jakeg9711]

Hi Clint, thank you for the video. It is very insightful, I'm just wondering how would you do this if you wanted to have the SPROC insert in to multiple tables?
Taking the example in the video, e.g. insert in to invoice & supplier table?
Thanks!

[@clinttuttle]

Depending on the use case, you may have an application screen that uses a screen to create the vendor using an "insert vendor" SPROC and then have a separate SPROC for inserting invoice. It's also possible that an application has a screen to create vendor and invoice at once which sounds like what you're describing. Assuming you have the required fields you need to create a vendor and invoice, you could put all of that into a single SPROC.
You could write a SPROC that takes in the required parameters for a vendor and invoice and then write two separate inserts in the SPROC's main body (one for vendor and one for invoice) and then include a Commit; after these two inserts. If there is an error in either insert you can have the Exception block include a ROLLBACK; statement to reverse the changes. That's how you can manage the transaction. Hope this helps!

[@bartolomedelascasas3447]

Hello thanks for the video, I have an exercise where I have to do the following: Prepare a stored procedure that verifies the Reimbursement request
status and changes the status from Set pay to Paid and updates the
Payment date to the current date. The stored procedure MUST
update the referred two columns in the REIMBURSEMENTREQUEST
table. Do you think this video can help me with that exercise?

[@clinttuttle]

This will help you setup the structure of a stored proc but sounds like you may need to know how to select into a variable and manage logical flow with an if statement. If this video doesn’t cover what you need I have one more on the basics of PL/SQL syntax that can show you that stuff. Happy coding!

[@craigronald2717]

Hi
Thanks for the video. But i have a question. Let's suppose I've already created a function and now i want to get output of that code. How will i do it. Is it possible to get output by selecting the functions from left panel and then run it and get the output?
I tried but it ask for input values ..
I just don't want to run the code again and again to get the output

[@clinttuttle]

Not sure you can run the function from the lefthand menu like you describe. The way I think of user-defined functions is to use them like I would any standard function like SUM or AVG which would be used in a SELECT commonly. So could you try selecting your function from the DUAL table? Try this:
Select YourFunction(hardcode a value to pass in) from Dual;