pl sql code in sql plus

... Using SQL* Plus to Create Reports and Manage PL/ SQL Code CĆ23 SQL* Plus Commands CĆ24 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder Entering Commands in SQL* Plus SQL* Plus contains ... Reports and Manage PL/ SQL Code CĆ5 Entering Commands in SQL* Plus CĆ6 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder Entering Commands in SQL* Plus Once you log in to SQL* Plus, you see ... message Creating SQL* Plus Files Containing PL/ SQL Blocks You can include PL/ SQL blocks in files of SQL and SQL* Plus commands Create the file using the operating system editor SQL* Plus commands...

Ngày tải lên: 17/01/2014, 09:20

... creating, 54 creating (security example), 538-539 data integrity (maintaining), 300-304 listing, 302 defining, 299-300, 313, 319 delete, 296 enabling/disabling, 310-311 event, 311-312 defining, ... (dbms output.put line() procedure), 17 processing benefits, platform independence (PL/ SQL benefits), Platinum Technology Inc Web site, 11 PLS_INTEGER datatype, 43 syntax, 44 plus sign (+) addition ... executing queries, 10 Server Manager, 11 SQL- Navigator (Quest Software), 11 SQL- Programmer (Sylvain Faust Inc.), 11 SQL- Station (Platinum Technology Inc.), 11 SQLCODE function, 160, 211-212 SQLERRM...

Ngày tải lên: 20/10/2013, 17:15

... back to the PL/ SQL program The important thing is that SQL* Plus does not execute your SQL queries SQL* Plus also does not execute your PL/ SQL code SQL* Plus simply serves as your window into the Oracle ... by SQL* Plus All the listings also have line numbers so that the analysis following each listing can easily refer to specific lines of code When you type PL/ SQL blocks into SQL* Plus, SQL* Plus generates ... SQL* Plus @ command • Use the SQL* Plus EDIT command The first method involves running Windows Notepad, typing your PL/ SQL code (or SQL queries) into it, and then copying and pasting from Notepad into...

Ngày tải lên: 07/11/2013, 20:15

... consider using SQLPlus Worksheet for the examples in this book SQLPlus Worksheet is completely compatible with SQL* Plus, and can be used for all the examples in this book The advantage that SQL* Plus ... toolbar button to execute it Executing a PL/ SQL Block Using SQLPlus Worksheet Figure 1.9 shows the SQLPlus Worksheet Learning the Basics of PL/ SQL 29 FIGURE 1.9 The SQLPlus Worksheet The Execute Button ... give you a good feel for the mechanics of programming with PL/ SQL 30 Day SQL* Plus is the tool used throughout this book for PL/ SQL code examples SQLPlus Worksheet and Procedure Builder are two other...

Ngày tải lên: 07/11/2013, 20:15

... comparing strings, especially when comparing a CHAR string to a VARCHAR2 string, is there a convenient way to tell PL/ SQL to ignore any trailing spaces in the CHAR string? A Yes Use the built -in ... following segment includes some examples of the MODE statement: FUNCTION addemployee( p_hiredate _in IN DATE, p_employeeID_out OUT NUMBER, p_hourlyrate _in_ out IN OUT NUMBER, p_empname _in_ out IN OUT ... typing the SQL command SHOW ERRORS at the SQL* Plus prompt You are now ready to enter a block of PL/ SQL code to see actual output and the passing of parameters Enter and then execute the code in...

Ngày tải lên: 07/11/2013, 20:15

... Comparing SQL Functions and PL/ SQL Functions As with any database, you can use SQL within PL/ SQL to take advantage of all the features of PL/ SQL Almost all the functions work within PL/ SQL except ... using stored functions is one appropriate method Jumping into a Lower-Level Block You can’t jump from an outer block of PL/ SQL code back to an inner block of PL/ SQL code Listing 5.1 is an example ... ON at the SQL* Plus prompt Then, execute the code in Listing 5.25 INPUT LISTING 5.25 Testing the Recursive Function with an Anonymous PL/ SQL Block DECLARE v_test NUMBER := 10; v_Counter INTEGER...

Ngày tải lên: 15/12/2013, 05:15

... the next line of code immediately following the procedure invocation line ANALYSIS Another example of the same procedure being executed from within SQL* Plus is the following: INPUT SQL> execute ... prompt PUT_LINE The code in Listing 7.2 illustrates the PUT_LINE command line that you can include inside a procedure INPUT LISTING 7.2 The PUT_LINE Command Within a Procedure CREATE OR REPLACE PROCEDURE ... same SQL statements into a PL/ SQL block and have them processed at one time, hence improving your overall performance Using SQL 223 FIGURE 8.2 SQL versus PL/ SQL code processing Oracle Server SQL...

Ngày tải lên: 15/12/2013, 05:15

... Listing 8.4 shows the PL/ SQL anonymous block that you can run to insert the necessary data INPUT LISTING 8.4 Inserting Records with PL/ SQL Code DECLARE insert department data first i_dept_id INTEGER, ... declaration in synch with the datatypes in the database Using SQL Q&A Q What happens if my SQL statement fails inside the PL/ SQL block? A The processing of your PL/ SQL block will terminate unless ... This sample code inserts a single record into the Employee table The employee id is created and provide by the emp_id_seq sequence number The employee name is hard coded and provide in the insert...

Ngày tải lên: 15/12/2013, 05:15

... GUTTING SCALE PROCESSING Technical Writing UNLOADING BOTTLING BOTTLING BOTTLING BOTTLING BOTTLING DISTILLATION Employee Newsletters FERMENTATION GUTTING SCALE PROCESSING Technical Writing UNLOADING ... Enforcing business rules • Maintaining referential integrity • Enforcing security • Maintaining a historical log of changes • Generating column values, including primary key values • Replication ... central point of control for maintaining the number of employees in a department, and they relieve you from having to program and test this logic several places in your application Maintaining History...

Ngày tải lên: 15/12/2013, 05:15

... from within SQL* Plus, although it could have been inside a PL/ SQL block Lines 12-22 show a PL/ SQL block that first instantiates an address object, and then inserts that object into the employee ... located in Detroit MI Victor Building is located in Lansing MI PL/ SQL procedure successfully completed In this example, a cursor is declared (lines 5–8) based on a SQL statement that selects building ... buildings OF building; Table created Lines 1–9 contain the building object’s type definition As you can see, the building object has three attributes containing the building’s name, the building’s...

Ngày tải lên: 15/12/2013, 05:15

... debugging server code by using SQL* Plus, don’t forget to turn on SERVEROUTPUT Writing a DEBUG Package is nice if you are debugging a procedure or function that you can invoke from SQL* Plus However, ... operating as expected Debugging Your Code and Preventing Errors 391 Preventing Errors and Planning for Debugging in the Future If your code needs to be debugged in the future, you can plan ahead ... FUNCTION COMPARE( Lob1 IN BFILE, Lob2 IN BFILE, Number_Bytes_to_Compare IN INTEGER, Origin_Lob1 IN INTEGER := 1, Origin_Lob2 IN INTEGER := 1) RETURN Compare_Result_Integer; In this syntax the parameters...

Ngày tải lên: 15/12/2013, 05:15

... Brandon’,3,1000.00,’S’); SAVEPOINT employee_1; INSERT INTO employee VALUES (7,’Catherine Ann’,2,2000.00,’S’); ROLLBACK TO SAVEPOINT employee_1; ANALYSIS In this example, the insertion of the employee Catherine Ann ... DEFINE_COLUMN (c IN INTEGER, position IN INTEGER, column IN VARCHAR2, column_size IN INTEGER); The header for the CHAR version of DEFINE_COLUMN is PROCEDURE DEFINE_COLUMN (c IN INTEGER, position IN INTEGER, ... are then displayed onscreen (if you are using SQL* Plus) You should execute the code in Listing 16.3, after making sure that you have entered SET SERVEROUTPUT ON at the SQL* Plus prompt INPUT 1: 2:...

Ngày tải lên: 15/12/2013, 05:15

... from a buffer in memory PL/ SQL Program Unit PUT_LINE Memory Buffer From PL/ SQL, you make calls to PUT_LINE in order to place text in the buffer DBMS_OUTPUT Package GET_LINE SQL* Plus reads text ... Shirley Tina PL/ SQL procedure successfully completed This PL/ SQL block writes one line into the buffer by using the PUT_LINE procedure (line 5) It then writes three more lines using a combination ... line SQL* Plus will pick this up and display it combined_names := ‘’; FOR inx1 IN lines_to_get LOOP IF inx1 > THEN combined_names := combined_names || ‘ and ‘; END IF; combined_names := combined_names...

Ngày tải lên: 15/12/2013, 05:15

... method of invoking the function involves the use of the SQL* Plus CALL command The CALL command allows you to invoke a function directly from SQL* Plus, and to return the results in a SQL* Plus bind variable ... writing PL/ SQL, then PL/ SQL will almost certainly die off If people continue to program in both environments, then both will continue to exist In any case, I think there’s little likelihood of PL/ SQL ... function, or you can use the new SQL* Plus CALL statement Listing 20.4 shows both methods being used INPUT/ OUTPUT LISTING 20.4 Invoking SS_THRESH from SQL* Plus 1: SQL> SELECT ss_thresh FROM dual;...

Ngày tải lên: 15/12/2013, 05:15

... for clarity Controlling Flow in PL/ SQL Blocks 23Ć7 23Ć8 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder The IF Statement continued Simple IF Statements PL/ SQL executes the conditional ... calc_val; Controlling Flow in PL/ SQL Blocks 23Ć11 23Ć12 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder Building Logical Conditions Build a simple Boolean condition by combining number, ... there is no explicit EXIT statement The condition determines when the loop is terminated Controlling Flow in PL/ SQL Blocks 23Ć25 23Ć26 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder...

Ngày tải lên: 21/12/2013, 06:17

... the following meanings: • declarations refers to any PL/ SQL variable definitions that you need in your PL/ SQL block refers to the PL/ SQL code in the block that you are executing • code • exception_handlers ... recognize it: • SQL* Plus • SQL* DBA • Server Manager What command tells SQL* Plus to display PL/ SQL output? SET SERVEROUTPUT ON Name at least two options for managing your PL/ SQL source code The three ... enqueuing and dequeuing are enabled Placing Messages in a Queue After creating and starting a queue, you place messages in the queue by enqueuing them Enqueuing a message involves these steps: Instantiate...

Ngày tải lên: 24/12/2013, 12:17

... control processing in a PL/ SQL block D Iterate statements by using various types of loops Controlling Flow in PL/ SQL Blocks 23Ć3 23Ć4 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder ... for clarity Controlling Flow in PL/ SQL Blocks 23Ć7 23Ć8 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder The IF Statement continued Simple IF Statements PL/ SQL executes the conditional ... calc_val; Controlling Flow in PL/ SQL Blocks 23Ć11 23Ć12 Introduction to Oracle: SQL and PL/ SQL Using Procedure Builder Building Logical Conditions Build a simple Boolean condition by combining number,...

Ngày tải lên: 17/01/2014, 09:20

... versus explicit, 263 importing jobs, 521 IN operator, 68, 72-73 expressions using, 73 listing, 73-74 syntax, 73 IN OUT arguments (calling Java from PL/ SQL) , 575-578 indenting code, 396 index-by ... creating directory object, 404 creating pointer to, 406-407 determining if open, 409-410 extracting from, 412 getting length, 410 getting name, 409 matching patterns in, 410-411 opening, 410 reading ... languages, 13 compiling and executing, 15-17 debugging (Procedure builder), 24-28 executing (SQLPlus Worksheet), 28-29 formatting (indenting), 396 function, 51 syntax, 51-52 nesting, 54 listing, 54-55...

Ngày tải lên: 21/01/2014, 18:20

... publishing, calling Java from PL/ SQL, 571-573 SELF parameter, 361-362 listing, 362 specifying, 362 nulls SSThresh calling from PL/ SQL, 567-568 calling from SQL* Plus, 566-567 publishing, 565-566 MIN function, ... creating, 54 creating (security example), 538-539 data integrity (maintaining), 300-304 listing, 302 defining, 299-300, 313, 319 delete, 296 enabling/disabling, 310-311 event, 311-312 defining, ... (dbms output.put line() procedure), 17 processing benefits, platform independence (PL/ SQL benefits), Platinum Technology Inc Web site, 11 PLS_INTEGER datatype, 43 syntax, 44 plus sign (+) addition...

Ngày tải lên: 26/01/2014, 15:20

Ngày tải lên: 22/03/2014, 10:20