1. stored procedure with scalar type - NUMBER, VARCHAR
2. stored procedure with REF CURSOR or SYS_REFCURSOR. What is difference between REF CURSOR and SYS_REFCURSOR?
Execute/Test stored procedure with primitive type:-
Step 1:- Create a stored procedure with primitive type.Below is sample code for the same ignore this step if you already have some stored procedure created. Execute it, SQL*PLUS/SQL Develope worksheet.create or replace procedure get_employee_total_saalry ( base_salary IN NUMBER , bonus IN NUMBER , total_sal OUT NUMBER ) as begin total_sal :=base_salary + .12*base_salary + bonus; end get_employee_total_saalry;
Step 2:- Create anonymous block to execute the stored procedure.Execute following blocks and see the output as total salary.
--Stored procedure having primitive type as paraemter declare type rc is ref cursor; v_base_sal NUMBER(5) := 1000; v_bonus NUMBER(5) := 2300; v_total NUMBER(10) ; begin --call stored procedure with two IN and one OUT type parameter HR.get_employee_total_saalry(v_base_sal,v_bonus,v_total); dbms_output.put_line(' Total sal is ' ||v_total); end;
Execute/Test stored procedure with REF_CURSOR/SYS_REFCURSOR type:-
Step 1:- Create a stored procedure with REF CURSOR. Below is the sample code for the same, ignore this step if you already have procedure created.create or replace procedure get_employees_name( v_deptId_in NUMBER, v_cur OUT SYS_REFCURSOR ) IS begin open v_cur for select FIRST_NAME,LAST_NAME from employees where DEPARTMENT_ID = v_deptId_in; end get_employees_name;
Step 2:- Now write execution block from where program unit with REF CURSOR is tested.
--using refcursor -- declare
-declare ref cursor type type rc is ref cursor; v_fname VARCHAR2(10); v_lname VARCHAR2(10); v_cur rc; -declare ref cursor variable v_deptId_in NUMBER(2) := 90; begin HR.get_employees_name(v_deptId_in,v_cur); dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME'); dbms_output.put_line('---------------------------------'); LOOP FETCH v_cur INTO v_fname, v_lname; EXIT WHEN v_cur%NOTFOUND; dbms_output.put_line(v_fname || ' ' || v_lname); END LOOP; CLOSE v_cur; end;
Using SYS_REFCURSOR also, we can write program blocks to test same stored procedure. (Remember, SYS_REFCURSOR is just a predefined weak REF CURSOR, so we do not have to declare a cursor type like "type rc is ref cursor". Following is testing program(differences from above program has been highlighted):
declare --type rc is ref cursor; NOT required v_fname VARCHAR2(10); v_lname VARCHAR2(10); v_cur SYS_REFCURSOR; --rc replaced with predefined type SYS_REFCURSOR v_deptId_in NUMBER(2) := 90; begin HR.get_employees_name(v_deptId_in,v_cur); dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME'); dbms_output.put_line('---------------------------------'); LOOP FETCH v_cur INTO v_fname, v_lname; EXIT WHEN v_cur%NOTFOUND; dbms_output.put_line(v_fname || ' ' || v_lname); END LOOP; CLOSE v_cur; end;
Alternate way execute stored procedure with REF_CURSOR/SYS_REFCURSOR type:-
Declare the cursor variable of type refcursor and run stored procedure with exec. In SQL developer/SQL*PLUS it can be executed like this :---cursor variable declaration variable v_emp_cur refcursor; --execute procedure execute get_employees_name(90,:v_emp_cur); --display result referenced by ref cursor. print v_emp_cur;
=====End of post=======
Read : Difference between SYS_REFCURSOR and REF CURSOR.
I am feeling great to read this.you gave a nice info for us.please update more.
ReplyDeleteJAVA Training in Chennai
Java training institute in chennai
Selenium Training in Chennai
Hadoop Training in Chennai
Python Training in Chennai
Software testing training in chennai
JAVA Training in Chennai
Java Training in Velachery
Great Article android based projects
DeleteJava Training in Chennai
Project Center in Chennai
Java Training in Chennai
projects for cse
The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training
The article is so informative. This is more helpful for our
ReplyDeletemagento training course in chennai
magento training institute in chennai
magento 2 training in chennai
magento development training
magento 2 course
magento developer training
Thanks for sharing.
Great efforts put to publish these kinds of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create great relations. You’re doing an excellent job. Keep it up.
ReplyDeleteMagento Development Training Course in Chennai Zuan Education
Selenium Training Course in Chennai Zuan Education
Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
ReplyDeleteDigital Marketing Course In Kolkata
Great efforts put to publish these kinds of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create great relations. You’re doing an excellent job. Keep it up.
ReplyDeleteMagento Development Training Course in Chennai Zuan Education
Selenium Training Course in Chennai Zuan Education
I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.
ReplyDeleteData Science Course
It's really nice and meanful. it's really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information.
ReplyDeleteData Science Training
Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
ReplyDeleteData Science Training Institute in Bangalore
ReplyDeleteExcellent Blog! I would like to thank for the efforts you have made in writing this post.
Best Data Science Courses in Bangalore
I have recently started a blog, the info you provide on this site has helped me greatly. Thanks for all of your time & work.
ReplyDeleteData Science Course in Hyderabad | Data Science Training in Hyderabad
You are in point of fact a just right webmaster. The website loading speed is amazing. It kind of feels that you're doing any distinctive trick. Moreover, The contents are masterpiece. you have done a fantastic activity on this subject!
ReplyDeleteBusiness Analytics Training in Hyderabad | Business Analytics Course in Hyderabad
NICE BLOG!!! Your blog is very informative for us. I would really like to come back again right here for likewise good articles or blog posts.
ReplyDeleteonline store developer in USA |Web Developer in USA | E-Store Developer in USA | Website Designer in California |Website Developer in California |Portal Developer in California |
https://digitalweekday.com/
ReplyDeletehttps://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
ReplyDeletehttps://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
https://digitalweekday.com/
Thanks for the Information.Interesting stuff to read.Great Article.
ReplyDeleteI enjoyed reading your post, very nice share.
Data Science Course Training in Hyderabad
Found your post interesting to read. I cant wait to see your post soon. Good Luck for the upcoming update. This article is really very interesting and effective, data science course
ReplyDeleteWonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.
ReplyDelete360DigiTMG Tableau Course
Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.
ReplyDelete360DigiTMG Business Analytics Course
Very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data sciecne course in hyderabad
ReplyDeleteThanks for sharing this great information on Oracle SOA. Actually I was looking for the same information on internet for SOA Interview Questions and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more about SOA by attending Oracle SOA Training training .
ReplyDeleteGreat article with excellent information found resourceful and enjoyed reading it thank you, looking forward for next blog.
ReplyDeletetypeerror nonetype object is not subscriptable
I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon…
ReplyDeletedata science course in Hyderabad
I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site.
ReplyDeletedata science training in Hyderabad
It fully emerged to crown Singapore's southern shores and has undoubtedly put it on the world residential monument map. Still, I scored more points than I have in one season for GS. I think it would be hard to find someone with the same consistency that I have had over the years, so I'm happy.
ReplyDeleteBusiness Analytics Course in Bangalore
I really enjoy reading all of your blogs. I just wanted to let you know that you have people like me who appreciate your work. Definitely a great article. Congratulations! The information you have provided is very helpful.
ReplyDeleteData Analytics Course in Bangalore
Aivivu vé máy bay giá rẻ
ReplyDeleteve may bay tet 2021 gia re
vé máy bay đi Mỹ giá bao nhiêu
vé máy bay đi Pháp khứ hồi
săn vé máy bay rẻ đi hàn quốc
vé máy bay vietnam airline đi nhật bản
vé máy bay đi Anh bao nhiêu tiền
Big Aquatic best it solution in gorakhpur
ReplyDeleteNino Nurmadi, S.Kom
ReplyDeleteNino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Nino Nurmadi, S.Kom
Thank you for this wonderful post. This is really amazing. I am looking after this type post. Finally, I am find it here. data science course in Hyderabad
ReplyDeleteThis Blog is very useful and informative.
ReplyDeletecertification of data science
Nice and very informative blog, glad to learn something through you.
ReplyDeletedata science course in delhi
Fantastic article with valuable information found very useful looking forward for next blog thank you.
ReplyDeleteData Science Course in Bangalore
Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
ReplyDeleteData Analyst Course