Oracle PL/SQL Recipes

相似文件 换一批

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

Oracle PL/SQL Recipes

A Problem-Solution Approach

Book Description
Oracle PL/SQL Recipes is your go to book for PL/SQL programming solutions. It takes a task-oriented approach to PL/SQL programming that lets you quickly look up a specific task and see the pattern for a solution. Then it's as simple as modifying the pattern for your specific application and implementing it. And you're done and home for dinner.

Oracle PL/SQL Recipes is another in Apress' ongoing series of recipe books aimed at Oracle practitioners. The recipe format is ideal for the busy professional who just needs to get the job done.
Book Details

• Publisher:        Apress
• By:        Josh Juneau, Matt Arena
• ISBN:        978-1-4302-3207-0
• Year:        2010
• Pages:        456
• Language:        English
• File size:        6.2 MB
• File format:        PDF
• Download:      

  本帖隐藏的内容

  Oracle PL-SQL Recipes.rar (2.59 MB, 需要: 20 个论坛币) 本附件包括：

  • Oracle PL-SQL Recipes.pdf

  2015-7-17 08:11:44 上传

  
  需要: 20 个论坛币  [购买]
  
  
  

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏1 回帖

关键词：Recipes Oracle Recipe ACL CIP Oracle

1-1. Creating a Block of Code

Problem

You are interested in creating an executable block of PL/SQL code.

Solution

Write the keywords BEGIN and END. Place your lines of code between those two keywords. Here's an example:

1. BEGIN
   
2.    Executable statements go here…
   
3. END;

复制代码

If you want to introduce variables for your PL/SQL block, you must precede your block with a DECLARE section. Here's an example:

1. DECLARE
   
2.     One or more variable declarations
   
3. BEGIN
   
4.     One or more PL/SQL statements
   
5. END;

复制代码

1-2. Executing a Block of Code in SQL*Plus

Problem

You want to execute a block of PL/SQL code within the SQL*Plus command-line utility.

Solution

The solution to this recipe is multitiered, in that executing a block of code in SQL*Plus incorporates at least two steps:

Enter the PL/SQL code into the SQL*Plus utility.

Execute the code block by simply placing a backslash (/) as the last line of code, and then press the Enter key.

The following is an example displaying a code block that has been typed into SQL*Plus:

1. SQL> BEGIN
   
2.   2 DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
   
3.   3  END;

复制代码

  4  /

1-3. Storing Code in a ScriptProblem

Rather than typing your PL/SQL code into the SQL*Plus utility each time you want to run it, you want to store the code in an executable script.

Solution

Open your favorite text editor or development environment; type the PL/SQL code into a new file, and save the file using the .sql extension. The script can contain any number of PL/SQL statements, but the last line of the script must be a forward slash (/).

For example, you could place the following lines into a file named count_down.sql:

1. SET SERVEROUTPUT ON;
   
2. DECLARE
   
3. counter   NUMBER;
   
4. BEGIN
   
5.   FOR counter IN REVERSE 0..10 LOOP
   
6.     DBMS_OUTPUT.PUT_LINE (counter);
   
7.   END LOOP;
   
8. END;

复制代码

Now you have a file that you can execute from SQL*Plus any time you want to count down from ten to zero.

1-4. Executing a Stored Script

Problem

You have stored an SQL script to your file system and want to execute it in SQL*Plus.

Solution

Assume you have a stored script named my_stored_script.sql and that it is saved within a directory named /Oracle/scripts/. You can execute that script using any one of the following approaches:

• Traverse into the directory containing the script, then connect to a database via SQL*Plus, and finally issue the following command:@my_stored_script.sql
• Open the command line or terminal, connect to the database via SQL*Plus, and issue the following command:@/Oracle/scripts/my_stored_script.sql
• Open command line or terminal, and issue the following command:sqlplus username/password@database my_stored_script.sql
• 
  
  

1-5. Accepting User Input from the Keyboard

Problem

You want to write a script that prompts the user for some input. You want your PL/SQL code to then use that input to generate some results.

Solution

SQL*Plus provides a facility to accept user input. Use ampersand (&) character to indicate that a particular value should be entered from the keyboard. Here's an example:

1. DECLARE
   
2.   emp_count   NUMBER;
   
3. BEGIN
   
4.   SELECT count(*)
   
5.   INTO emp_count
   
6.   FROM employees
   
7.   WHERE department_id = &department_id;
   
8. END;

复制代码

If the previous block is executed from SQL*Plus, you will see the following text, which prompts you to enter a department ID. In this case, the department ID of 40 is used.

Enter value for department_id: 40
old   7:   WHERE department_id = &department_id;
new   7:   WHERE department_id = 40;

1-6. Displaying Results in SQL*Plus

Problem

You want to display query results at the SQL*Plus prompt.

Solution

Use the DBMS_OUTPUT package to assist in displaying query results or lines of text. The following example depicts both of these use cases:

1. DECLARE            
   
2.    first        VARCHAR2(20);
   
3.    last         VARCHAR2(25);
   
4. BEGIN
   
5.   SELECT first_name, last_name
   
6.   INTO first, last
   
7.   FROM employees
   
8.   WHERE email = 'VJONES';
   
9.   DBMS_OUTPUT.PUT_LINE('The following employee matches your query:');
   
10.   DBMS_OUTPUT.PUT_LINE(first || ' ' || last);
    
11. END;

复制代码

Commenting Your Code

Problem

You want to document your code with inline and multiline comments.

Solution

Place two dashes before any text to create a one-line comment. For example, in the following code there is a comment placed before the query to describe its functionality:

-- The following query obtains a count of rows from the employees

1. table
   
2. SELECT COUNT(*)
   
3. FROM EMPLOYEES;

复制代码

Multiline comments can be created beginning with a slash and asterisk (/*) and ending with an asterisk and slash (*/). The following lines depict a multiple-line comment for a given code block:

1. /* This comment describes the functionality
   
2.       in the following code block. */

复制代码

1-8. Referencing a Block of CodeProblem

You want to reference a block of code within a code segment later in your program.

Solution

Assign a label to the block of code that you want to reference. A PL/SQL label consists of a unique identifier surrounded by double angle brackets. For example, in the following code, you see that the block has been labeled dept_block:

1. <<dept_block>>
   
2. DECLARE
   
3.   dept_name    varchar2(30);
   
4. BEGIN
   
5.   SELECT department_name
   
6.   INTO dept_name
   
7.   FROM departments
   
8. WHERE department_id = 230;
   
9.   DBMS_OUTPUT.PUT_LINE(dept_name);
   
10. END;

复制代码

This code block can now be referenced by the label dept_block.