[ Pobierz całość w formacie PDF ]
.DELETE is oneof the database privileges controlled by the GRANT and REVOKE statements.The TRANSACTION clause can be used in multiple transaction SQL applications to specifywhich transaction controls the DELETE operation.The TRANSACTION clause is not availablein DSQL or isql.For searched deletions, the optional WHERE clause can be used to restrict deletions to asubset of rows in the table.IMPORTANT Without a WHERE clause, a searched delete removes all rows from a table.When performing a positioned delete with a cursor, the WHERE CURRENT OF clause mustbe specified to delete one row at a time from the active set.LANGUAGE REFERENCE 93 CHAPTER 2 SQL STATEMENT AND FUNCTION REFERENCEExamples The following isql statement deletes all rows in a table:DELETE FROM EMPLOYEE_PROJECT;The next embedded SQL statement is a searched delete in an embedded application.Itdeletes all rows where a host-language variable equals a column value.EXEC SQLDELETE FROM SALARY_HISTORYWHERE EMP_NO = :emp_num;The following embedded SQL statements use a cursor and the WHERE CURRENT OF optionto delete rows from CITIES with a population less than the host variable, min_pop.Theydeclare and open a cursor that finds qualifying cities, fetch rows into the cursor, anddelete the current row pointed to by the cursor.EXEC SQLDECLARE SMALL_CITIES CURSOR FORSELECT CITY, STATEFROM CITIESWHERE POPULATION = | 5000000;The next isql statement establishes a table alias in the SELECT clause and uses it to identifya column in the WHERE clause:SELECT C.CITY FROM CITIES CWHERE C.POPULATION 0) THENEXCEPTION REASSIGN_SALES;See Also WHEN & DOFor more information on creating exceptions, see CREATE EXCEPTION on page 52.EXECUTE PROCEDUREExecutes a stored procedure.Available in triggers and stored procedures.Syntax EXECUTE PROCEDURE name [:param [, :param & ]][RETURNING_VALUES :param [, :param & ]];Argument Descriptionname Name of the procedure being executed.Must have been previouslydefined to the database with CREATE PROCEDURE[param [, param & ]] List of input parameters, if the procedure requires them.Can beconstants or variables.Precede variables with a colon, except NEWand OLD context variables[RETURNING_VALUES param [, List of output parameters, if the procedure returns values.Precedeparam & ]] each with a colon, except NEW and OLD context variablesLANGUAGE REFERENCE 165 CHAPTER 3 PROCEDURES AND TRIGGERSDescription A stored procedure can itself execute a stored procedure.Each time a stored procedurecalls another procedure, the call is said to be nested because it occurs in the context of aprevious and still active call to the first procedure.A stored procedure called by anotherstored procedure is known as a nested procedure.If a procedure calls itself, it is recursive.Recursive procedures are useful for tasks thatinvolve repetitive steps.Each invocation of a procedure is referred to as an instance, sinceeach procedure call is a separate entity that performs as if called from an application,reserving memory and stack space as required to perform its tasks.Note Stored procedures can be nested up to 1,000 levels deep.This limitation helps toprevent infinite loops that can occur when a recursive procedure provides no absoluteterminating condition.Nested procedure calls may be restricted to fewer than 1,000 levelsby memory and stack limitations of the server.Example The following isql example illustrates a recursive procedure, FACTORIAL, which calculatesfactorials.The procedure calls itself recursively to calculate thefactorial of NUM, the input parameter.SET TERM !!;CREATE PROCEDURE FACTORIAL (NUM INT)RETURNS (N_FACTORIAL DOUBLE PRECISION)ASDECLARE VARIABLE NUM_LESS_ONE INT;BEGINIF (NUM = 1) THENBEGIN /**** Base case: 1 factorial is 1 ****/N_FACTORIAL = 1;EXIT;ENDELSEBEGIN /**** Recursion: num factorial = num * (num-1) factorial ****/NUM_LESS_ONE = NUM - 1;EXECUTE PROCEDURE FACTORIAL NUM_LESS_ONERETURNING_VALUES N_FACTORIAL;N_FACTORIAL = N_FACTORIAL * NUM;EXIT;ENDEND!!SET TERM ;!!See Also CREATE PROCEDURE, Input parameters, Output parametersFor more information on executing procedures, see EXECUTE PROCEDURE on page 113.166 INTERBASE 5 EXITEXITJumps to the final END statement in the procedure.Available in stored procedures only.Syntax EXIT;Description In both select and executable procedures, EXIT jumps program control to the final ENDstatement in the procedure.What happens when a procedure reaches the final END statement depends on the type ofprocedure:In a select procedure, the final END statement returns control to the application and setsSQLCODE to 100, which indicates there are no more rows to retrieve.In an executable procedure, the final END statement returns control and values of outputparameters, if any, to the calling application.SUSPEND also returns values to the calling program.Each of these statements has specificbehavior for executable and select procedures, as shown in the following table.Procedure type SUSPEND EXIT ENDSelect " Suspends execution of Jumps to final END " Returns control toprocedure procedure until next applicationFETCH is issued" Sets SQLCODE to 100" Returns output values (end of record stream)Executable " Jumps to final END Jumps to final END " Returns valuesprocedure" Not Recommended " Returns control toapplicationTABLE 3.1 SUSPEND, EXIT, and ENDExample Consider the following procedure from an isql script:SET TERM !!;CREATE PROCEDURE P RETURNS (r INTEGER)ASBEGINr = 0;WHILE (r
[ Pobierz całość w formacie PDF ]