chPL_SQLPart2

chPL_SQLPart2 - AdvancedPL/SQL Programming IS620...

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Advanced PL/SQL  Programming IS 620 Advanced Database  Projects
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Roadmap n Procedures n How to debug PL/SQL program n Functions n Dynamic SQL  n How to Automatically Generate Values  During Insert n VArray Data Type
Background image of page 2
3 Named Program Types n Procedures n Functions (procedures that return a  value) n functions, not covered) n Triggers (later)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 Program Units n Procedures n Can receive and pass multiple parameter  values n Can call other program units n Functions n Like procedures, except they return a  single value
Background image of page 4
5 General Steps n Create the function or procedure using create  function or create procedure command n Oracle will compile and store the procedure n In case of compilation error, type  show errors n You will see the line/column of error and  description of error n Try to fix errors n Otherwise, you will see procedure created  message
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6 Show Errors n Suppose you have created a procedure like create or replace procedure HelloWorld(s varchar) as begin   dbms_output.putline(s); end; n Now you type (must do it  after  you click execute) show errors;
Background image of page 6
7 Show Errors n Results: LINE/COL  ERROR 4/3  PL/SQL: Statement ignored 4/15  PLS-00302: component 'PUTLINE' must be  declared  n Meaning: Error occurs on Line 4 Putline is not recognized You can also google error number: PLS-00302 to  some idea
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
8 General Steps n Execute the function or procedure with passed  parameter n Execute  in SQL:  exec procedure-name(parameter values); n Execute in  PL/SQL  (in anonymous program or another  procedure function):  procedure-name(parameter values); n In the web interface, you must click execute button  after creating the procedure and before executing the  procedure. Don't create and execute at one time.
Background image of page 8
9 General Steps n To see the names of procedures Select * from user_procedures; To see the code SELECT * FROM USER_SOURCE WHERE  NAME='PROCEDURE-NAME';  
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
10 General Steps Using SQL Developer n The same way as web interface or click procedures,  right click, new procedure n n Click Save Icon, it will get compiled. n The error message is shown at the bottom of the  screen (compiler log) n Right click give options such as compile, run, drop n Watch for invisible strange characters if you copy  code from word or power point (use notepad or other  text editor will be better)
Background image of page 10
11 General Steps Using SQL Developer n When execute procedure, you can do the same thing  as Web Interface (type in SQL window) n Or you can right click on the procedure, select run n A window will pop up, with some PL/SQL code to call  the procedure n Edit the program to enter parameter values (usually  through assignment)
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 65

chPL_SQLPart2 - AdvancedPL/SQL Programming IS620...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online