Creating Procedure (IN parameter)

Khaled Alkhudari
A free video tutorial from Khaled Alkhudari
Sr Oracle Developer/ Sr system analyst
4.5 instructor rating • 7 courses • 31,390 students

Learn more from the full course

Oracle PL/SQL is My Game: EXAM 1Z0-149

Become Oracle PL/SQL Developer / Covers ORACLE university track 100%. All the presentations & scripts are attached

21:51:49 of on-demand video • Updated January 2020

  • The full track of Oracle university Exam 1Z0-144 : Program with PL/SQL
  • Oracle Database 12c Architecture
  • Downloading and installing oracle database 12c in details
  • Overview of PLSQL
  • Declaring PLSQL Variables
  • Writing executable statements
  • Interacting with Oracle DB Server
  • Writing Control structure
  • Working with Composite Data Types
  • Using explicit cursors
  • Handling Exceptions
  • Creating Procedures
  • Creating Functions
  • Creating Packages
  • Working with Packages
  • Using oracle-supplied Packages in Application Development
  • Using dynamic SQL
  • Design consideration for PLSQL Code
  • Creating triggers
  • Creating Compound, DDL, and Event Database Triggers
  • Using the PLSQL Compiler
  • Managing PLSQL Code
  • Managing Dependencies
English [Auto] Hello everyone will come back. I hope that you are doing fine in the previous lecture. We understand the syntax for creating a procedure today. We will do a full example and you will know many things. So now let's see this example create or replace a procedure update underscores that. So I create a procedure and there's a procedure called update underscores and then I would open the Plunkett's. I will mention two parameters B underscore a and b underscore ID and number comma B amount and number. So I defined a procedure. This procedure take two parameters with datatype number the end. What is the meaning of. And that this parameter will be sent from that calling environment. And we will see now when we execute the procedure as then begin then. And now a very important note if you have variables you can define the variables between AS and begin for example and number. Then I will make this statement about that employees said salary equals salary plus the amount and that amount is here where employee ID equal be underscored. MP ID and the P underscore and P ID is here. That's it. Then I would make comments I make exception when others then I'll put the code and the On Error message. Now I will select this code I can compile the procedure by clicking here or here run a script. Now I would execute this. I will find a procedure update under Scorsone comp. So I dont have any error. You can go to the view and go to the logs and you will find your updates will compile. So I don't have any errors so I create a procedure. This procedure take two parameters the employee ID and the amount and I will update the salary for the employees based on these parameters. Now lets do this. I will remove this semicolon OK and I will execute the code again. You will find here procedures update underscores are compiled but here you will find errors Shick compiler block. So I will go to the compiler here I click here you will find the error askew statement ignore. So you can now DoubleClick and you will see that there is some problem. Then you will put the semi-colon again like this. Another way to see the error is to make this statement. Select the start from user underskirt else where a name equal objects. So I have additional tables called user underscore errors and you can pass the procedure name to this dictionary table. I would execute this and you will find exactly the same that you have to enter. This is the first error and the second error. Now I will fix the code and I compiled again. You will find here procedure updates are compiled procedure updates are compiled and here you will not find any log. Now if you select again from use of underskirt errors were made equal or better. You will find here no. So this means that you have fixed the errors in this procedure. Now I will do and I think I will remove the semi-colon again. I will take this code Control-C I will go to the ask you on the plus I will connect to the H. Our use of our SLAs are at or R C PDP and collected. I will make test for this code. I will put this in Flash. So you will find here wanting a procedure created with compilation error to see the compilation error and its Karplus you will write show error. That's it. OK. I will make now that I will put that semicolon again here I will close our Save our execute the code again. You will find the procedure to create it. And here I don't have else. OK. Very very nice. So now I will return this again and that's it. So now if you go to the edge of here the schema if you go to the procedures you will find the procedures now already here up there. So it will be stored as a schema object like views like. OK. Very very nice. So now let's test this a procedure. I will make Selecta start from employees were empty underscored ID 100 I would execute this experiment. I have the employee ID 100 Stephen. His salary now is twenty four thousand one hundred. Now I want to stick with the procedure and I want to increment Steven by $50. So its very simple that method 1 execute then you will put the procedure name. Then you will open a packet you will give them free ID and the amount. So this means that the 100 it will go to that and the score and B ID and the 50 will go to that amount. So the update is certainly to be update and please sit salary equal salary plus 50 where employee ID equal 100 then you will come in. Thats it. So this is the end parameter. This means that the parameters will come from that calling environment and this is the calling statement. So 100 it will go to that underscore the ID and the 50 will go to that amount. So now I will execute the code. You will find the sequel procedure successfully completed if you now make this statement to make sure I will next look the stuff from employees where employee and school ID equal 100. You will find the salary now is twenty four thousand one hundred fifty. Now I can put this call between Begin and End and I will make that underscores. I will open up markets. I can put in percent and be ID and 10 percent amount. So I would execute this. I would put the employee ID 100 and I will put 50. Again our click OK. Then you will find the sequel procedure successfully completed. I want to make sure now you will find out the salary is twenty four thousand two hundred. So I have two methods to cause the procedure to make execute like this or to put my procedure between Begin and End or inside and it as you. You can find the information for the procedure in a dictionary table called use or underskirt objects. So I will make. So lets start from use of underskirt objects where object and thus scorn them equal objects. I would expect this you will find that I have object called update underscores and this is the object id the object type as a procedure and you will find here to create it. And the time stamp if you want to see that text for your code or your procedure then this information exists indiction a table called use of socks. So I will make select start from user underscore souls where a name equal update underscores that this is the procedure name order bylined I would execute this statement and you will find this is the code for your procedure and it is stored in the database. OK. Very nice. And finally if you want to wrap the procedure simply you make it drop the procedure and you will put the procedure on them. That's it. In order to drop a procedure you should have it drop privileges. By the way if you already have the create a procedure then you can drop the procedure because you are the owner of this procedure. Thats it. So this picture is very important to give you the guidelines for creating the procedure. Thank you for listening and see you next to.