PL SQL programming Interview questions and answers
James Koopmann shares five common issues that could get you into trouble when writing PL/SQL (and SQL), and how you might answer those questions within the confines of a PL/SQL job interview.
An interviewer should see your attention to detail and desire to improve the environment, even if they, themselves, have tendencies to crank out code without regard to standards.
It is amazing to me that many writers of PL/SQL never give much thought as to how they access (query) data from within the database. For this reason, an overwhelming phrase that rings from many DBAs goes something like this, "All applications would be perfect if they didn't access my data "or" My database wouldn't have any performance problems if we just eliminated the applications. Either way we all understand that applications are a necessity. However, it is not necessarily true that applications cause or should cause database performance issues. This article looks at some of the more common issues when writing PL/SQL (and SQL), in the confines of a PL/SQL job interview, that could get you into trouble and how you might answer those questions.
1. How do you go about tuning your PL/SQL code?
This really hits at the core of this article. We must all understand, and relate this to our interviewer that we know that it is the SQL that will always cause the most difficulty, performance wise, integrity wise, bug wise, within our PL/SQL code. We can always talk about EXPLAIN plan usage, TKPROF, gathering runtime statistics, index optimization, and the list goes on, but let me suggest another tactic here that might get you noticed. Try working in the fact that you understand that data can change drastically within an organization and a static application (PL/SQL code) often does not cut it. What is needed, and what you will bring to the table is an ability to place an abstraction layer, using views, functions, triggers, procedures, etc. that maintains the integrity of the PL/SQL logic but allows for simplified maintenance to the data the PL/SQL code requires.
As a very simplistic example, imagine you needed to select a number of employees within your PL/SQL code. A very simple solution would be to SELECT directly all the employees form the EMP table. However, let's say we acquired another company and wanted this code to work with two different EMP tables. The old code would have to be modified to possibly perform a join. The better solution, one not affecting the code, would be to always use a view and then modify the view when the new company was acquired. A little abstraction goes a long way when requirements change.
2. How might you get around hard coding the elements in a fetch cursor?
I'd have to say that this is one of the most common forms of hard coding, other than actual values/IDs being used in a SQL statement. Practitioners will often use the %TYPE notation for individual variables, which is fine and well, within the declaration section but seem to lose sight of the %ROWTYPE. When fetching a cursor INTO variables those variables are often strung out in a list such as: FECTH empId, empFname, empLname INTO vempId, vempFname, vempLname; clearly requiring the addition of another variable in the declaration section and at the end of the INTO clause. What should happen here is use the %ROWTYPE and just issue something like: FETCH empId, empFname, empLname INTO empRowtype; removing all hard coding in the body of the PL/SQL code.
3. How do you get around repeating SQL code?
The answer seems simple and many would agree that repeating code is an accident waiting to happen; increasing the probability of changing all but one code segment and having a very difficult bug to find. Instead, we should always, for straight code or SQL statements, ensure we never perform the same function in two different places in our code. Instead, we should hide the SQL behind subprograms and then call those subprograms repeatedly. Not only will this make your code more efficient and maintainable but these subprograms can be called by other applications; creating a much more flexible environment.womens bike helmets stylish mens stylish jackets stylish bed frames stylish console tables stylish water bottles stylish side tables uk stylish hijabs uk stylish bedding stylish dinner set pakistani stylish maxi design stylish casual outfits stylish standard whatsapp dp stylish eats stylish websites stylish cute hiking boots instagram stylish hidden face girl pic stylish jackets stylish daily wear gold earrings stylish trousers 1more stylish true wireless stylish study stylish computer desk mens stylish watches stylish bedside tables stylish floor lamps stylish dog company mens stylish glasses pubg stylish name generator stylish underwear crossword clue stylish toy storage for living room stylish word stylish waterproof coat womens how to be stylish elegant stylish 8 letters stylish top prams stylish high chair stylish electric radiators stylish tote bags for work reverse stylish vehicles stylish rose dp stylish fruit bowl stylish coat hooks stylish coat stylish bookshelf stylish womens suits stylish wedding dresses stylish toy storage stylish waterproof boots stylish hallway mens stylish haircuts stylish women stylish modern bedroom door design short stylish haircuts stylish baby girl clothes stylish dog collars stylish fitness tracker stylish side table stylish shelving stylish recliner sofa women's stylish ladies laptop bags stylish lounge stylish attitude status in english stylish plus size clothing stylish grow lights for indoor plants stylish orthopedic chairs vip style award for most stylish newcomer stylish jackets for men stylish steel toe boots stylish tote bags stylish mens christmas outfit stylish cute ganesh images stylish dogs stylish womens jackets best stylish office chair stylish sweatshirts stylish men's clothing be stylish by kat stylish desks uk stylish tv unit stylish t shirts for mens stylish safety boots stylish vase stylish work boots stylish bathroom accessories stylish womens hiking shoes stylish number fonts stylish laptop bags for men stylish bike helmets for women stylish coats for men stylish office furniture stylish grandmother of the bride dresses stylish couple dp stylish walking shoes for women graceful stylish crossword stylish rings for men free fire stylish name stylish pens stylish wine glasses stylish full hand mehndi design stylish short haircuts for men stylish phone cases stylish bedroom chairs stylish hoodies for guys stylish office chairs for home stylish clothes rail stylish cooker hoods stylish door handles stylish speakers stylish quotes stylish pen stylish waterproof boots womens stylish bags for girls how to look stylish stylish winter jackets stylish boots for men stylish in the 60s stylish pregnancy outfits stylish baby girl names stylish bunk beds stylish bath mat stylish winter coats mens stylish simple casual pakistani dresses stylish bread bin stylish corner desk stylish lamp shades stylish alarm clock stylish abaya stylish wall mirror stylish hijab caps stylish halloween costumes your stylish home stylish bathroom lights comfortable stylish sandals the yuppie files a lifestyle blog for the stylish mom stylish summer outfits stylish stair gate stylish elegance crossword clue stylish chess set stylish dog bowl quick stylish crossword stylish man crossword clue 4 letters stylish winter boots women's 2022 stylish suits stylish suitcase stylish shower seats stylish jumpsuits stylish grace crossword clue stylish headboards stylish leg mehndi design stylish 14 august dpz for girlz text generator stylish what to wear on long-haul flight and look stylish stylish cartoon dp for girls stylish skin chichester stylish gardening clothes stylish face mask stylish whiteboard the stylish brides boutique london loafers put on by stylish leader stylish iphone 7 cases stylish pyjamas stylish cat furniture stylish office desks cheap stylish clothes stylish rooms penryn stylish blazers stylish kitchens padgene stylish 6 stylish gym bag stylish bedroom furniture stylish look stylish snow boots stylish home decor pleasingly stylish crossword clue stylish bathroom mirrors stylish kitchen design stylish ergonomic chair stylish name maker stylish shorts for men stylish file cabinet stylish door stop stylish skin