Oracle SQL Jumpstart with Examples- P12

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

95
lượt xem 9
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P12: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle SQL Jumpstart with Examples- P12

520 23.2 Privileges PRINCE originally granted the CREATE VIEW privilege to ARIEL. Revoked system privileges do not cause cascading revokes; only object priv- ilege revokes can do that. CREATE VIEW CA_ARTISTS AS SELECT * FROM MUSIC.ARTIST WHERE STATE_PROVINCE='CA'; We will now examine some rules about revoking privileges. Using graphic examples, here are some key points to remember about how revok- ing of privileges works. 23.2.2.1 Revoked System Privileges DO NOT Cascade When you revoke a system privilege, the revoke affects only the user you are naming and does not affect any objects or users created. For example, SYS- TEM grants the CREATE USER privilege WITH ADMIN OPTION to ASSISTANT. Then ASSISTANT creates a user named INTERN and grants her the CREATE USER privilege. Now, INTERN creates another user named JOE. Figure 23.10 illustrates these events. Figure 23.10 One New User Is Created by Each of These Users: SYSTEM, ASSISTANT, and INTERN. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23.2 Privileges 521 Figure 23.11 ASSISTANT Failed to Create MATTHEW, but INTERN Created BETH. Now, as the DBA, you decide that your assistant does not need to create users at this point, so you revoke the CREATE USER privilege from ASSISTANT. ASSISTANT can no longer create users; however, the users she created still exist. And, INTERN, who received the system privilege CREATE USER from ASSISTANT, retains that privilege. Figure 23.11 illustrates this idea by showing that ASSISTANT cannot create a user, while INTERN can create a user. 23.2.2.2 Revoked Object Privileges DO Cascade Revoking an object privilege does result in a cascading set of revoked privi- leges. For example, imagine that SYSTEM grants SELECT on MUSIC.ARTIST to ASSISTANT using the WITH GRANT OPTION clause. Then ASSISTANT grants the same object privilege to INTERN who in turn grants the privilege (without the WITH GRANT OPTION) to JOE. Figure 23.12 shows the scenario. After careful thought, you decide that your assistant no longer requires the SELECT privilege on the MUSIC.ARTIST table, so you revoke the privilege. The revoke actually cascades and revokes the privilege from INTERN, and then it cascades again and revokes the privilege from JOE. Chapter 23 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
522 23.3 Grouping Privileges Using Roles Figure 23.12 SYSTEM, ASSISTANT, and INTERN Grant Object Privileges. Now, only SYSTEM can successfully query the MUSIC.ARTIST table. Figure 23.13 shows how this works. Remember that revoked system privileges do not cascade and revoked object privileges do cascade. One of the more repetitive DBA tasks is that of granting the proper privileges to new users and maintaining privileges for all existing users. Very often, a group of users has identical privileges. The next section shows you how to take advantage of this with roles. Roles allow groupings of privileges and subsequent granting of privilege groups with a single granting or revoke of a role. 23.3 Grouping Privileges Using Roles A role is a set or grouping of object and/or system privileges that is assigned a name. Once a role is established, you can grant the role instead of grant- ing all of the individual privileges to a user. This capability saves a great deal of time! Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23.3 Grouping Privileges Using Roles 523 Figure 23.13 Revoking an Object Privilege Cascades to Other Users to whom the Revokee Granted the Same Object Privilege. Note: PL/SQL code blocks may not recognize database access through roles. Explicit object privileges may be required for PL/SQL. PL/SQL is covered in Chapter 24. 23.3.1 Creating and Altering Roles Figure 23.14 shows the syntax of the CREATE ROLE and ALTER ROLE commands. Options are identical for both commands. Any user with the CREATE ROLE system privilege can create a role. The SYSTEM user, of course, has this privilege. The DBA often grants this privilege to users who own tables, so that users can create roles associated with their tables and grant those roles to other users. A role that will contain sensitive privileges can be assigned a password. Any user who wants to use that role must provide the password (except when the role is one of the user’s default roles). You will ﬁnd out more about default roles later. At this stage, all we will do is lay some groundwork for later and create two roles, substitute strings where appropriate. Chapter 23 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
524 23.3 Grouping Privileges Using Roles Figure 23.14 A New Role Does Not Contain Any Privileges at First. CONNECT SYSTEM/password@OLTP; CREATE ROLE MINIDBA; CREATE ROLE MUSIC_ACCESS; The MINIDBA role will be a highly privileged role, thus I am using the ALTER ROLE command to restrict access using a pass- word. ALTER ROLE MINIDBA IDENTIFIED BY DBA#9876; Note: The password is the only portion of a role that can be altered. You can add, change, or remove the password on a role. If you want to change the name of a role, you must drop and then re-create it with the changed name. Once roles are created, privileges can be granted to them as if they are users. Then roles can be granted to users. Once a user has a role granted, he or she inherits all of the privileges assigned to that role. 23.3.2 Granting and Revoking Privileges on Roles Granting privileges to a role is exactly the same (syntax-wise) as granting privileges to a user. Figures 23.5 and 23.9 show the syntax of granting and revoking privileges to and from roles. Roles can be granted to a user, a role, or PUBLIC. Let’s grant some privileges. First connect to the SYSTEM user. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23.3 Grouping Privileges Using Roles 525 CONNECT SYSTEM/password@OLTP; Now we give the MINIDBA role three system privileges that you wish to delegate to an assistant DBA. GRANT CREATE USER, CREATE SESSION, CREATE ROLE TO MINIDBA; Connect to the MUSIC user to grant some object privileges to the other role. CONNECT MUSIC/MUSIC@OLTP; Let’s say that you are the designer for the MUSIC schema’s application and you know that all users need to be able to change and query some tables and only query other tables. GRANT SELECT ON ARTIST TO MUSIC_ACCESS; GRANT SELECT ON SONG TO MUSIC_ACCESS; GRANT SELECT ON MUSICCD TO MUSIC_ACCESS; GRANT SELECT, INSERT, UPDATE, DELETE ON STUDIOTIME TO MUSIC_ACCESS; GRANT SELECT, INSERT, UPDATE, DELETE ON GUESTAPPEARANCE TO MUSIC_ACCESS; Now that roles are conﬁgured, we should now grant the roles to users. Granting a role to a user uses the same syntax as granting a system privilege. Refer to Figures 23.5 and 23.9 again. Notice that you can grant a system privilege, a role, or ALL PRIVILEGES. A role can even be granted to another role! This can be useful when you have subsets of privileges that can be logically grouped together under a single role. So we have added privileges to both roles and now wish to grant roles to users. The MUSIC user did not create any roles and does not have the GRANT ANY ROLE system privilege. We have to connect to SYSTEM again. CONNECT SYSTEM/password@OLTP; Chapter 23 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
526 23.3 Grouping Privileges Using Roles Let’s say that you want PRINCE to be allowed to use the MUSIC appli- cation. In addition, PRINCE will be allowed to grant the role to other users. Grant the appropriate role to PRINCE using this command: GRANT MUSIC_ACCESS TO PRINCE WITH ADMIN OPTION; Granting a role to a user has the same syntax as granting system privi- leges; therefore, you use the WITH ADMIN OPTION when you want the user to be able to grant the role to others. We also decide that the MINIDBA role should have all privileges granted to the MUSIC_ACCESS role in addition to the system privileges already granted to it. Grant the MUSIC_ACCESS role to the MINIDBA role. GRANT MUSIC_ACCESS TO MINIDBA; Now, grant the MINIDBA role to ARIEL. GRANT MINIDBA TO ARIEL; ARIEL has all privileges from both roles. Connect to PRINCE. CONNECT PRINCE/CHARMING@OLTP; PRINCE is allowed to grant the MUSIC_ACCESS role. He grants it to ARIEL. GRANT MUSIC_ACCESS TO ARIEL; After doing this, we realize that ARIEL already has the MUSIC_ACCESS role because it is included in the MINIDBA role. So PRINCE can revoke the redundant role. REVOKE MUSIC_ACCESS FROM ARIEL; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23.3 Grouping Privileges Using Roles 527 Figure 23.15 Modify a User’s Default Roles with ALTER USER. Note: Roles can be granted to other roles, establishing groups of groupings of privileges. 23.3.3 Setting User Roles A role, once assigned to a user, can be either enabled or disabled in the user’s session. By default, any role assigned to a user is enabled. The DBA can adjust which roles are enabled by default for each user when that user logs in, using the ALTER USER command. In addition, a user can enable a role using the SET ROLE command. The ALTER USER command syntax is shown in Figure 23.15. The ALTER USER command has many other uses. Figure 23.15 shows only portions of syntax catering to user default roles. When a user starts a session (connects to a database), roles are enabled according to settings made by the DBA using the ALTER USER command. A user can modify his or her session and change the enabled role set using the SET ROLE command. Figure 23.16 shows the syntax for the SET ROLE command. Let’s show some use of role allocation. First, reconnect to SYSTEM using this command: CONNECT SYSTEM/password@OLTP; Chapter 23 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
528 23.3 Grouping Privileges Using Roles Figure 23.16 Users Can Only Enable Roles Previously Granted to Them. All roles assigned to a user start out enabled by default, including roles with passwords. If you want the user to be required to use the password before enabling the role, you must remove the role from the user’s list of default roles. The MINIDBA role has a password and has been granted to ARIEL. Remove this role from ARIEL’s default roles. ALTER USER ARIEL DEFAULT ROLE ALL EXCEPT MINIDBA; Now connect to ARIEL replacing the variable as usual. CONNECT ARIEL/MERMAID@OLTP; ARIEL cannot perform any tasks that need the system privileges found in the MINIDBA role (such as creating new users), because the role is dis- abled. She enables the MINIDBA role by using the SET ROLE command, including the appropriate password. SET ROLE MINIDBA IDENTIFIED BY DBA#9876; Note: Be careful to include all of the roles you wish to enable in your SET ROLE command. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
23.3 Grouping Privileges Using Roles 529 Roles not included in the SET ROLE command become disabled. For example, let’s say you have three roles enabled by default (VIEWMUSIC, UPDATEMUSIC, and DELETEMUSIC) and one role (INSERTMUSIC) disabled by default. If the command SET ROLE INSERTMUSIC is exe- cuted, you will enable the INSERTMUSIC role and disable the VIEW- MUSIC, UPDATEMUSIC, and DELETEMUSIC roles. Oracle Database 10g provides some predeﬁned roles you can use if you wish. There are many predeﬁned roles. Some of them are listed as follows: CONNECT. System privileges needed to log on and work as a data- base developer. Privileges include CREATE TABLE, CREATE VIEW, CREATE SESSION, CREATE CLUSTER, and so on. Each operating system has a slightly different group of privileges, but gen- erally, you have all you need to do basic database work. RESOURCE. System privileges needed for other database develop- ment, such as creating types. Privileges include CREATE TYPE and CREATE PROCEDURE. Like the CONNECT role, the exact priv- ileges vary from system to system. SELECT_CATALOG_ROLE. Allows access to data dictionary metadata and performance views, the catalog. Use these to help you get started in administering your database. Oracle recommends, however, that you study the underlying privileges and create your own roles for most tasks. The CONNECT and RESOURCE roles may not be created automatically in future releases of Oracle. 23.3.4 Dropping Roles This ﬁnal section on roles involves removing roles. Whenever you remove a role, it is revoked from all users who currently have the role. Syntax for the DROP ROLE command is shown in Figure 23.17. Roles are an excellent way to consolidate privileges needed for running applications. Figure 23.17 Dropping a Role Also Revokes the Role from Users. Chapter 23 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
530 23.4 Metadata Views 23.4 Metadata Views This section simply describes metadata views applicable to users, privileges, and roles. Chapter 19 describes the basis and detail of Oracle Database metadata views. USER_USERS. Information on the logged-in user. ALL_USERS and DBA_USERS detail information for all users currently existing in the database. USER_SYS_PRIVS. Granted system privileges. USER_TAB_PRIVS[_MADE|RECD]. All object privileges (granted to and from plus owned). MADE and RECD implies granted object privileges and grantee object privileges, respectively. Note: The term grantee implies that a user has been granted a privilege by another user. USER_COL_PRIVS[_MADE|RECD]. As for USER_TAB_PRIVS but as applied to speciﬁc columns only, not entire tables. ROLE_PRIVS. Roles granted to a user, both enabled and disabled. USER_ROLE_PRIVS. Roles granted to the connected user, both enabled and disabled. SESSION_ROLES. A connected session’s enabled roles. ROLE_ROLE_PRIVS. Roles granted to other roles. ROLE_TAB_PRIVS. Object privileges granted to roles. ROLE_SYS_PRIVS. System privileges granted to roles. DBA_ROLE_PRIVS. Roles granted to users and other roles, who or which role granted it to the user or role, respectively, and whether the user has WITH ADMIN OPTION for the role. This chapter has described security and controlling database access using users, both system and object privileges, and ﬁnally privilege groupings using roles. The next chapter, the ﬁnal chapter in this book, digresses from Oracle SQL more so than this chapter, examining the very basics of Pro- gramming Language/SQL (PL/SQL). Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
24 Basic PL/SQL In this chapter: What is PL/SQL? What are variables and PL/SQL datatypes? What are procedures, functions, triggers, and packages? How is data retrieved from the database using PL/SQL? What programming control structures exist in PL/SQL? What is dynamic or generic SQL? This chapter covers basic reference material and examples on how to write programs in PL/SQL. It should be noted that the PL/SQL is a wrap- per extension of Oracle SQL in that its original purpose was that of data- base access only. However, in recent years, PL/SQL has been expanded voluminously to become more of a programming language. 24.1 What is PL/SQL? PL/SQL is an acronym for Programming Language/SQL. Structured Query Language (SQL) is a scripting language. A scripting language usually does not allow any dependencies between separate, following commands. Note: This is not strictly true for all scripting languages. Even though UNIX shell scripting has many features, attempting to write complex appli- cations using only UNIX shell scripting can lead to expensive problems. 531 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
532 24.2 Why Is PL/SQL a Programming Language? PL/SQL extends SQL with programming controls and features such as procedures, variables, and control structures. Let’s begin the meat of this chapter by asking: Why is PL/SQL classiﬁed as a programming language? 24.2 Why Is PL/SQL a Programming Language? PL/SQL is a programming language because, unlike SQL, it allows depen- dencies to exist between multiple SQL commands, within the same block of code. In Oracle SQL, each SQL statement cannot pass a result on to another SQL statement or control structure, but PL/SQL can. Also, per- haps more important, a programming language block structure allows one procedure to call another, allowing for a modular, compartmentalized, or perhaps even pseudo-object hierarchical programming structure. Therefore, PL/SQL is a programming language because it contains the ability to do the following things: Allows dependencies between commands within the same block of code. Allows for parameter passing up and down code block hierarchies. It allows for structure, namely modular. Contains a deﬁnition of variable scope across code block hierarchies, strict data typing, and allows use of commonly used programming control structures. The downside of PL/SQL is that it should be primarily used as a data- base access programming language. PL/SQL does not perform well as a number cruncher like C or Java. One more point to make is as follows: PL/SQL is becoming increasingly more capable as an object-like programming language, where the Oracle relational database allows for hierarchical object data structures. It tries to anyway. For what it is worth in my experienced opinion, I would avoid using Oracle Database or PL/SQL to manage objects. If you want to use object methodologies to manage complexity, put it at the application level using something like Java or use an object database. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
24.2 Why Is PL/SQL a Programming Language? 533 24.2.1 Blocks and Exception Trapping A block of code is a group of lines of SQL or PL/SQL code enclosed between BEGIN and END statements. A block of code is parsed and exe- cuted after the END statement is submitted using the front slash (/) charac- ter. The following SQL block consists of a variable declaration section followed by a BEGIN to END code block. See the result in Figure 24.1. This block of code queries the ARTIST table for the ARTIST_ID of Sheryl Crow. It stores the ARTIST_ID in a variable and then uses the variable to ﬁnd the title of the ﬁrst song of Sheryl Crow in the SONG table. It stores the title in another variable. Then it displays the title and completes. SET SERVEROUTPUT ON; DECLARE vARTIST_ID ARTIST.ARTIST_ID%TYPE; vTITLE SONG.TITLE%TYPE; BEGIN SELECT ARTIST_ID INTO vARTIST_ID FROM ARTIST WHERE NAME='Sheryl Crow'; SELECT TITLE INTO vTITLE FROM SONG WHERE ARTIST_ID = vARTIST_ID AND ROWNUM = 1; DBMS_OUTPUT.PUT_LINE(vTITLE); EXCEPTION WHEN OTHERS THEN RAISE; END; / SET SERVEROUTPUT OFF; Note: The statement SET SERVEROUTPUT ON is essential for the proper functioning of the DBMS_OUTPUT.PUT_LINE packaged proce- dure. DBMS_OUTPUT is an Oracle-provided package. The PUT_LINE procedure within that package sends a line to the output. SET SERVER- OUTPUT OFF switches output off. Note: %TYPE sets a variable to the datatype of the speciﬁed TABLE.COLUMN. Chapter 24 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
534 24.2 Why Is PL/SQL a Programming Language? Figure 24.1 PL/SQL Block Structure and Exception Trapping. The block of code in Figure 24.1 is an anonymous PL/SQL block, which is effectively an unnamed procedure without parameters. It is exe- cuted by the front slash (/) character, is not stored, and thus cannot be exe- cuted again as a stored, named object. The last two lines in Figure 24.1 before the block END statement com- prise an error exception trap. Any errors occurring between the BEGIN statement and the EXCEPTION statement will cause control to pass to the EXCEPTION trap, which executes the RAISE statement. The RAISE statement does nothing in this procedure, passing an exception to the call- ing block. If no calling block exists, then an error called unhandled exception will be returned to the calling application. In our case, SQL*Plus Work- sheet is the calling application. 24.2.2 Procedures, Functions, Triggers, and Packages Unlike an anonymous block, stored procedures are named, compiled, and stored in the database. They can be executed repeatedly in the future by executing the procedure name. PL/SQL stored objects include procedures, functions, triggers, and packages. What are the differences between these four compiled, executable database objects? They are as follows: Procedure. Allows by value and by reference parameters with no return value. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
24.2 Why Is PL/SQL a Programming Language? 535 Function. Like a procedure but allows a return value. Trigger. No transactional termination commands allowed and exe- cuted automatically by database event occurrences. Triggers are known as event-driven procedures. Package. Groups multiple procedures and functions together into blocked units. 24.2.2.1 Using Named Procedures The following named procedure is a slightly more sophisticated copy of the anonymous procedure presented previously. The procedure now has a name, accepts a parameter, is stored in the database, and can be executed repeatedly by executing the procedure name as shown in the following script. The result is shown in Figure 24.2. CREATE OR REPLACE PROCEDURE GETSONG (pARTIST IN VARCHAR2) AS vARTIST_ID ARTIST.ARTIST_ID%TYPE; vTITLE SONG.TITLE%TYPE; BEGIN SELECT ARTIST_ID INTO vARTIST_ID FROM ARTIST WHERE NAME=pARTIST; SELECT TITLE INTO vTITLE FROM SONG WHERE ARTIST_ID = vARTIST_ID AND ROWNUM = 1; DBMS_OUTPUT.PUT_LINE(vTITLE); EXCEPTION WHEN OTHERS THEN RAISE; END; / SET SERVEROUTPUT ON; EXEC GETSONG('Sheryl Crow'); EXEC GETSONG('Avril Lavigne'); SET SERVEROUTPUT OFF; 24.2.2.2 Using Functions Following are two versions of a function used previously in this book. This function will split a string time value of HH:SS into its hours and seconds constituent parts and convert them to a real number. CREATE OR REPLACE FUNCTION GETTIME(pTIME IN VARCHAR2) RETURN NUMBER IS --variable declaration section Chapter 24 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
536 24.2 Why Is PL/SQL a Programming Language? Figure 24.2 PL/SQL Block Structure and Exception Trapping. vLEN INTEGER DEFAULT 0; vSPLIT INTEGER DEFAULT 0; vHOURS INTEGER DEFAULT 0; vSECONDS INTEGER DEFAULT 0; BEGIN --execution section vSPLIT := INSTR(pTIME,':'); vLEN := LENGTH(pTIME); vHOURS := TO_NUMBER(SUBSTR(pTIME,1,vSPLIT-1)); vSECONDS := TO_NUMBER(SUBSTR(pTIME, vSPLIT+1,vLEN-vSPLIT)); RETURN vHOURS+(vSECONDS/60); EXCEPTION WHEN OTHERS THEN --exception trap section RETURN 0; END; / Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
24.2 Why Is PL/SQL a Programming Language? 537 Note: Note in the previous PL/SQL code how variables are accessed as vari- able := value; This is PL/SQL syntax. Here is a single-line version of the same function showing how best to write properly performing PL/SQL code: CREATE OR REPLACE FUNCTION GETTIME(pTIME IN VARCHAR2) RETURN NUMBER IS BEGIN RETURN TO_NUMBER(SUBSTR(pTIME,1,INSTR(pTIME,':')- 1))+(TO_NUMBER(SUBSTR(pTIME,INSTR(pTIME,':')+1,LENGTH(pTIME)- INSTR(pTIME,':')))/60); EXCEPTION WHEN OTHERS THEN RETURN 0; END; / I can execute the GETTIME function on the SONG table PLAYING_TIME column (SONG.PLAYING_TIME) using the following script. The result is shown in Figure 24.3. SELECT PLAYING_TIME, GETTIME(PLAYING_TIME) FROM SONG WHERE PLAYING_TIME IS NOT NULL; Note: The GETTIME function is also known as a custom-written or user- deﬁned function. 24.2.2.3 Using Triggers Here are some simple example triggers. The ﬁrst trigger detects insertions to the ARTIST table, the second updates, and the third deletions. Figure 24.4 shows the response from an INSERT, an UPDATE, and a DELETE com- mand, one after the other. CREATE OR REPLACE TRIGGER iARTIST AFTER INSERT ON ARTIST FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('New Artist '||:NEW.NAME||' added.'); Chapter 24 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
538 24.2 Why Is PL/SQL a Programming Language? Figure 24.3 Executing a Named, Stored Procedure from within SQL. EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE)); RAISE; END; / CREATE OR REPLACE TRIGGER uARTIST AFTER UPDATE OF NAME ON ARTIST FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('Artist changed from ' ||:OLD.NAME||' to '||:NEW.NAME); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE)); RAISE; END; / CREATE OR REPLACE TRIGGER dARTIST AFTER DELETE ON ARTIST FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('Artist '||:OLD.NAME ||' has been deleted'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE)); RAISE; END; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
24.2 Why Is PL/SQL a Programming Language? 539 Figure 24.4 Executing Triggers from DML Commands. / SET SERVEROUTPUT ON; INSERT INTO ARTIST(ARTIST_ID,NAME) VALUES(100,'Robert Alan Zimmerman'); UPDATE ARTIST SET NAME='Bob Dylan' WHERE NAME='Robert Alan Zimmerman'; DELETE FROM ARTIST WHERE NAME='Bob Dylan'; SET SERVEROUTPUT OFF; 24.2.2.4 Using Packages Packages can be used to group commonly stored PL/SQL units into a single chunk of code. A package must have a declaration section and a body sec- tion. The declaration simply deﬁnes named units within the package, and the package body contains the actual procedures. The following script is a simple package converting temperatures between degrees Fahrenheit (F˚), degrees Celsius (C˚), and degrees Kelvin (K˚). Example executions of the various functions are shown in Figure 24.5. CREATE OR REPLACE PACKAGE TEMPERATURE AS FUNCTION cTOf(c VARCHAR2 DEFAULT 0) RETURN VARCHAR2; FUNCTION fTOc(f VARCHAR2 DEFAULT 0) RETURN VARCHAR2; Chapter 24 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.