PL/SQL User's Guide and Reference | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Goethe
You can use PL/SQL with a variety of application development tools. This chapter shows you how to use PL/SQL in the SQL*Plus, Oracle Precompiler, and OCI environments.
You input the PL/SQL block line by line. Ending the block with a period (.) on a line by itself stores the block in the SQL buffer.
You can save your PL/SQL block in a script file as follows:
SQL> SAVE <filename>
If you want to edit the file, you can use the SQL*Plus line editor. For instructions, see SQL*Plus User's Guide and Reference. After editing the file, you can save it again as follows:
SQL> SAVE <filename> REPLACE
SQL> RUN or SQL> /
When the block is finished running, you are returned to the SQL*Plus prompt. The SQL buffer is not cleared until you start inputting the next SQL statement or PL/SQL block.
CLEAR BREAKS CLEAR COLUMNS COLUMN ENAME HEADING Name TTITLE 'CLERICAL STAFF' DECLARE avg_sal NUMBER(7,2); BEGIN SELECT AVG(sal) INTO avg_sal FROM emp; IF avg_sal < 1500 THEN UPDATE emp SET sal = sal * 1.05 WHERE job = 'CLERK'; END IF; END; / SELECT ENAME, SAL FROM EMP WHERE JOB = 'CLERK';
The two CLEAR statements get rid of any settings left over from a previous report. The COLUMN statement changes the ENAME column heading to Name. The TTITLE statement specifies a title that appears at the top of each page in the report. The semicolon (;) following each SQL*Plus statement executes that statement. Likewise, the slash (/) following the PL/SQL block executes that block.
SQL> START <filename> or SQL> @<filename>
Your PL/SQL block can take advantage of the SQL*Plus substitution variable feature. Before running a script, SQL*Plus prompts for the value of any variable prefixed with an ampersand (&). In the following example, SQL*Plus prompts for the value of num:
SQL> BEGIN 2 FOR i IN 1..&num LOOP ... ... 8 END; 9 / Enter value for num:
SQL> CREATE PROCEDURE create_dept (new_name CHAR, new_loc CHAR) AS 2 BEGIN 3 INSERT INTO dept 4 VALUES (deptno_seq.NEXTVAL, new_name, new_loc); 5 END create_dept; 6 / Procedure created.
If SQL*Plus tells you that the subprogram, package, or trigger was created with compilation errors, you can view them by typing the SQL*Plus command SHOW ERRORS, as follows:
SQL> SHOW ERRORS
To declare a bind variable, you use the SQL*Plus command VARIABLE. In the following example, you declare a variable of type NUMBER:
VARIABLE return_code NUMBER
Note: If you declare a bind variable with the same name as a PL/SQL program variable, the latter takes precedence.
To reference a bind variable in PL/SQL, you must prefix its name with a colon(:), as the following example shows:
:return_code := 0; IF credit_check_ok(acct_no) THEN :return_code := 1; END IF;
To display the value of a bind variable in SQL*Plus, you use the PRINT command, as follows:
SQL> PRINT return_code RETURN_CODE ----------- 1
In the script below, you declare a bind variable of type REFCURSOR. (The SQL*Plus datatype REFCURSOR lets you declare cursor variables, which you can use to return query results from stored subprograms.) You use the SQL*Plus command SET AUTOPRINT ON to display the query results automatically.
CREATE PACKAGE emp_data AS TYPE EmpRecTyp IS RECORD ( emp_id NUMBER(4), emp_name CHAR(10), job_title CHAR(9), dept_name CHAR(14), dept_loc CHAR(13)); TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp; PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp); END; / CREATE PACKAGE BODY emp_data AS PROCEDURE get_staff (dept_no IN NUMBER, emp_cv IN OUT EmpCurTyp) IS BEGIN OPEN emp_cv FOR SELECT empno, ename, job, dname, loc FROM emp, dept WHERE emp.deptno = dept_no AND emp.deptno = dept.deptno ORDER BY empno; END; END; / COLUMN EMPNO HEADING Number COLUMN ENAME HEADING Name COLUMN JOB HEADING JobTitle COLUMN DNAME HEADING Department COLUMN LOC HEADING Location SET AUTOPRINT ON VARIABLE cv REFCURSOR EXECUTE emp_data.get_staff(20, :cv)
SQL> EXECUTE create_dept('ADVERTISING', 'NEW YORK')
This call is equivalent to the following call issued from an anonymous PL/SQL block:
SQL> BEGIN create_dept('ADVERTISING', 'NEW YORK'); END;
In the next example, you use the database link newyork to call the remote stored procedure raise_salary:
SQL> EXECUTE raise_salary@newyork(7499, 1500)
You can create synonyms to provide location transparency for remote standalone procedures.
CREATE PROCEDURE calc_payroll (payroll IN OUT REAL) AS CURSOR c1 IS SELECT sal,comm FROM emp; BEGIN payroll := 0; FOR c1rec IN c1 LOOP c1rec.comm := NVL(c1rec.comm, 0); payroll := payroll + c1rec.sal + c1rec.comm; END LOOP; /* Display debug info. */ dbms_output.put_line('payroll: ' || TO_CHAR(payroll)); END calc_payroll;
When you issue the following commands, SQL*Plus displays the value of payroll calculated by the procedure:
SQL> SET SERVEROUTPUT ON SQL> VARIABLE num NUMBER SQL> EXECUTE calc_payroll(:num)
For more information about package DBMS_OUTPUT, see Oracle7 Server Application Developer's Guide.
After writing a program, you precompile the source file. The precompiler checks the program for syntax errors, then generates a modified source file, which can be compiled, linked, and executed in the usual way.
EXEC SQL EXECUTE BEGIN ... END; END-EXEC;
Be sure to follow the keyword END-EXEC with the host-language statement terminator.
Both the host program and the PL/SQL block can set and reference the value of a host variable. The value of an input host variable is set by the host program and referenced by Oracle. Conversely, the value of an output host variable is set by Oracle and referenced by the host program.
All references to host variables in a PL/SQL block must be prefixed with a colon. That way, the precompiler can tell host variables from PL/SQL variables and database objects.
-- available online in file EXAMP9 #include <stdio.h> EXEC SQL BEGIN DECLARE SECTION; VARCHAR empname[11]; VARCHAR jobtype[9]; VARCHAR hired[9]; int salary; int dept; int served_longer; int higher_sal; int total_in_dept; VARCHAR uid[20]; VARCHAR pwd[20]; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; void sqlerror(); main() { /* Set up userid and password */ strcpy (uid.arr,"scott"); uid.len = strlen(uid.arr); strcpy (pwd.arr,"tiger"); pwd.len = strlen(pwd.arr); printf("\n\n\tEmbedded PL/SQL Demo\n\n"); printf("Trying to connect..."); /* Check for SQL errors */ EXEC SQL WHENEVER SQLERROR DO sqlerror(); /* Connect to Oracle */ EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; printf(" connected.\n"); for (;;) /* Loop indefinitely */ { printf("\n** Name of employee? (<CR> to quit) "); gets(empname.arr); /* Get the name */ if (strlen(empname.arr) == 0) /* No name entered, */ { EXEC SQL COMMIT WORK RELEASE; /* so log off Oracle */ exit(0); /* and exit program */ } empname.len = strlen(empname.arr); jobtype.len = 9; hired.len = 9;
/* ----- Begin PL/SQL block ----- */ EXEC SQL EXECUTE BEGIN SELECT job, hiredate, sal, deptno INTO :jobtype, :hired, :salary, :dept FROM emp WHERE ename = UPPER(:empname); /* Get number of people whose length * * of service is longer */ SELECT COUNT(*) INTO :served_longer FROM emp WHERE hiredate < :hired; /* Get number of people with a higher salary */ SELECT COUNT(*) INTO :higher_sal FROM emp WHERE sal > :salary; /* Get number of people in same department */ SELECT COUNT(*) INTO :total_in_dept FROM emp WHERE deptno = :dept; END; END-EXEC; /* ----- End PL/SQL block ----- */ /* Null-terminate character strings returned by Oracle */ jobtype.arr[jobtype.len] = '\0'; hired.arr[hired.len] = '\0'; /* Display the information */ printf("\n%s's job is: %s\n", empname.arr, jobtype.arr); printf("Hired on: %s\n", hired.arr); printf(" %d people have served longer\n", served_longer); printf("Salary is: %d\n", salary); printf(" %d people have a higher salary\n", higher_sal); printf("Department number is: %d\n", dept); printf(" %d people in the department\n", total_in_dept); } /* End of loop */ } void sqlerror() { /* Avoid infinite loop if rollback causes an error */ EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nOracle error detected:\n"); /* Print error message and disconnect from Oracle */ printf("\n%.70s\n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }
Notice that the host variable empname is assigned a value before the PL/SQL block is entered and that the other host variables are assigned values inside the block. When necessary, Oracle converts between its internal datatypes and standard host-language datatypes.
The next Pro*C example shows how two PL/SQL banking transactions might be implemented:
-- available online in file EXAMP10 #include <stdio.h> EXEC SQL BEGIN DECLARE SECTION; int acct, amount; VARCHAR tran_type[10]; VARCHAR status[65]; VARCHAR uid[20]; VARCHAR pwd[20]; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; void sqlerror(); main() { /* Set up userid and password */ strcpy (uid.arr,"scott"); uid.len=strlen(uid.arr); strcpy (pwd.arr,"tiger"); pwd.len=strlen(pwd.arr); printf("\n\n\tEmbedded PL/SQL Demo\n\n"); printf("Trying to connect..."); /* Check for SQL errors */ EXEC SQL WHENEVER SQLERROR DO sqlerror(); /* Connect to Oracle */ EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; printf(" connected.\n"); for (;;) /* Loop indefinitely */ { printf("\n\n** Account number? (-1 to quit)"); scanf("%d", &acct); if (acct == -1) /* Disconnect from Oracle and */ { /* exit program if acct is -1 */ EXEC SQL COMMIT WORK RELEASE; exit(0); } printf("\n Transaction type? (C)redit or (D)ebit "); scanf("%s", &tran_type.arr); tran_type.len = 1; /* Only want first character */
printf("\n Transaction amount? (in whole dollars) "); scanf("%d", &amount); /* ----- Begin PL/SQL block ----- */ EXEC SQL EXECUTE DECLARE old_bal NUMBER(11,2); no_account EXCEPTION; BEGIN :tran_type := UPPER(:tran_type); IF :tran_type = 'C' THEN -- credit the account UPDATE accounts SET bal = bal + :amount WHERE account_id = :acct; IF SQL%ROWCOUNT = 0 THEN -- no rows affected RAISE no_account; ELSE :status := 'Credit complete.'; END IF; ELSIF :tran_type = 'D' THEN -- debit the account SELECT bal INTO old_bal FROM accounts WHERE account_id = :acct; IF old_bal >= :amount THEN -- has sufficient funds UPDATE accounts SET bal = bal - :amount WHERE account_id = :acct; :status := 'Debit applied'; ELSE :status := 'Insufficient funds'; END IF; ELSE :status := :tran_type || ' is an illegal transaction'; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND OR no_account THEN :status := 'Nonexistent account'; WHEN OTHERS THEN :status := 'Error: ' || SQLERRM(SQLCODE); END; END-EXEC; /* ----- End the PL/SQL block ----- */ status.arr[status.len] = '\0'; /* null-terminate string */ printf("\n\n Status: %s", status.arr); } /* End of loop */ }
void sqlerror() { /* Avoid infinite loop if rollback causes an error */ EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nOracle error detected:\n"); /* Print error message and disconnect from Oracle */ printf("\n%.70s\n", sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }
For input host variables, the values your program can assign to an indicator variable have the following meanings:
-1 | Ignoring the value of the host variable, Oracle will assign a null to the database column. |
>= 0 | Oracle will assign the value of the host variable to the database column. |
-2 | Oracle assigned a truncated column value to the host variable but could not store the original length of the column value in the indicator variable because the number was too large. |
-1 | The database column contains a null, so the value of the host variable is indeterminate. |
0 | Oracle assigned an intact column value to the host variable. |
> 0 | Oracle assigned a truncated column value to the host variable and stored the original length of the column value in the indicator variable. |
:host_variable INDICATOR :indicator_variable
A host language needs indicator variables because it cannot manipulate nulls. PL/SQL meets this need by allowing an embedded PL/SQL block to accept nulls from the host program and return nulls or truncated values to it.
In the following Pro*COBOL example, the PL/SQL block uses an indicator variable to return a null status code to the host program:
EXEC SQL EXECUTE BEGIN ... SELECT ENAME, COMM INTO :MY-ENAME, :MY-COMM:COMM-IND FROM EMP WHERE EMPNO = :MY-EMPNO END; END-EXEC. MOVE MY-COMM TO MY-COMM-OUT. DISPLAY "Commission: " WITH NO ADVANCING. IF COMM-IND = -1 * If the value returned by an indicator variable is -1, * its output host variable is null. DISPLAY "N/A" ELSE DISPLAY MY-COMM-OUT.
Inside a PL/SQL block, an indicator variable must be prefixed with a colon and appended to its host variable.
You cannot refer to an indicator variable by itself. Furthermore, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block. In the next example, because the host variable appears with its indicator variable in the SELECT statement, it must also appear that way in the IF statement:
EXEC SQL EXECUTE DECLARE ... status_unknown EXCEPTION; BEGIN ... SELECT ename, job INTO :my_ename, :my_job:job_ind FROM emp WHERE empno = :my_empno; IF :my_job:job_ind IS NULL THEN RAISE status_unknown; END IF; ... END; END-EXEC;
Although you cannot refer directly to indicator variables inside a PL/SQL block, PL/SQL checks their values upon entering the block and sets their values correctly upon exiting the block.
EXEC SQL EXECUTE DECLARE ... name_missing EXCEPTION; BEGIN ... IF :my_ename:ename_ind IS NULL THEN RAISE name_missing; END IF; ... EXCEPTION WHEN name_missing THEN ... END; END-EXEC;
EXEC SQL EXECUTE DECLARE new_ename CHAR(10); ... BEGIN ... :my_ename:ename_ind := new_ename; ... END; END-EXEC;
EXEC SQL BEGIN DECLARE SECTION; VARCHAR my_ename[10] EXEC SQL END DECLARE SECTION;
into the following data structure:
struct { unsigned short len; unsigned char arr[10]; } my_ename;
To get the length of a VARCHAR, simply refer to its length field. You need not use a string function or character-counting algorithm.
Oracle automatically sets the length field of a VARCHAR output host variable. However, to use a VARCHAR output host variable in your PL/SQL block, you must initialize the length field before entering the PL/SQL block. So, set the length field to the declared (maximum) length of the VARCHAR, as shown in the following Pro*C example:
EXEC SQL BEGIN DECLARE SECTION; int my_empno; VARCHAR my_ename[10] /* declare variable-length string */ float my_sal; ... EXEC SQL END DECLARE SECTION; ... my_ename.len = 10; /* initialize length field */ EXEC SQL EXECUTE BEGIN SELECT ename, sal INTO :my_ename, :my_sal FROM emp WHERE empno = :my_empno; ... END; END-EXEC;
EXEC SQL DECLARE dept TABLE( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13));
If you use DECLARE TABLE to define a table that already exists, the precompiler uses your definition, ignoring the one in the Oracle data dictionary. Note that you cannot use the DECLARE TABLE statement inside a PL/SQL block.
The precompiler gets information needed for the semantic check by using embedded DECLARE TABLE statements or by connecting to Oracle and accessing the data dictionary. So, unless every database table referenced in a SQL statement or PL/SQL block is defined by a DECLARE TABLE statement., you must specify the option USERID on the command line. For more information see Programmer's Guide to the Oracle Precompilers.
The Oracle Precompilers treat a PL/SQL block like a single SQL statement. So, like a SQL statement, a PL/SQL block can be stored in a string host variable for processing by dynamic SQL commands.
However, recall from Chapter 2 that you cannot use single-line comments in a PL/SQL block that will be processed dynamically. Instead, use multi-line comments.
Following is a brief look at how PL/SQL is used with dynamic SQL Methods 1, 2, and 4. For more information, see Programmer's Guide to the Oracle Precompilers.
main() { printf("\nEnter a PL/SQL block: "); scanf("%s", user_block); EXEC SQL EXECUTE IMMEDIATE :user_block;
When you store a PL/SQL block in a string host variable, omit the keywords EXEC SQL EXECUTE, the keyword END-EXEC, and the statement terminator.
main() { printf("\nEnter a PL/SQL block: "); scanf("%s", user_block); EXEC SQL PREPARE my_block FROM :user_block; EXEC SQL EXECUTE my_block USING :my_empno;
Note that my_block is an identifier used by the precompiler, not a host or program variable.
The precompiler treats all PL/SQL host variables as input host variables whether they serve as input or output host variables (or both) inside the PL/SQL block. So, you must put all host variables in the USING clause.
When the PL/SQL string is executed, host variables in the USING clause replace corresponding placeholders in the prepared string. Although the precompiler treats all PL/SQL host variables as input host variables, values are assigned correctly. Input (program) values are assigned to input host variables, and output (column) values are assigned to output host variables.
DECLARE colx VARCHAR2(10); my_ename VARCHAR2(10); ... BEGIN colx := 'ename'; SELECT colx INTO my_ename FROM emp WHERE ... ... END;
However, you can mimic dynamic SQL by using the DECODE function. In the following example, the data returned depends on the value of my_column:
DECLARE my_column VARCHAR2(10); my_data emp.ename%TYPE; BEGIN ... my_column := 'hiredate'; ... SELECT DECODE(my_column, 'ename', ename, 'hiredate', TO_CHAR(hiredate, 'ddmmyy'), 'empno', empno) INTO my_data FROM emp WHERE ... ; END;
The value that DECODE returns is always forced to the datatype of the first result expression. In this example, the first result expression is ename, which has datatype VARCHAR2, so the returned value is forced to type VARCHAR2. Thus, my_data is correctly declared as emp.ename%TYPE.
You can use this technique in many environments. For example, it works in SQL*Plus and Oracle Forms.
EXEC SQL EXECUTE BEGIN create_dept(:number, :name, :location); END; END-EXEC;
Notice that the actual parameters number, name, and location are host variables.
In the next example, the procedure create_dept is part of a package named emp_actions, so you must use dot notation to qualify the procedure call:
EXEC SQL EXECUTE BEGIN emp_actions.create_dept(:number, :name, :location); END; END-EXEC;
In PL/SQL, all queries must have an INTO clause containing placeholders (host variables and/or PL/SQL variables) that correspond to items in the select list. For example, the following SELECT statement is not valid inside a PL/SQL block:
SELECT ename, sal FROM emp;
Instead, it must be coded as follows:
SELECT ename, sal INTO :my_ename, :my_sal FROM emp;
In the last statement, my_ename and my_sal are SQL placeholders that correspond to the ename and sal columns in the select list. You must bind these placeholders using the OBNDRA, OBINDPS, or OBNDRV call. You can bind host arrays to PL/SQL tables using the OBNDRA or OBINDPS call.
Also, you must use named placeholders such as my_ename in PL/SQL blocks. Numbered placeholders such as 10 and the corresponding OBNDRN call are not supported for PL/SQL blocks.
#include <stdio.h> #include <string.h> #include <stdlib.h> #include <oratypes.h> #include <ocidfn.h> #ifdef __STDC__ #include <ociapr.h> #else #include <ocikpr.h> #endif #include <ocidem.h> Cda_Def cda; Lda_Def lda; ub1 hda[256]; text sqlstm[2048]; void error_handler(); main() { int acct_number; text trans_type[1]; float trans_amt; text status[80]; if (olog(&lda, hda, "scott/tiger", -1, (text *) 0, -1, (text *) 0, -1, OCI_LM_DEF)) { printf("Connect failed.\n"); exit(EXIT_FAILURE); } if (oopen(&cda, &lda, (text *) 0, -1, -1, (text *) 0, -1)) { printf("Error opening cursor. Exiting...\n"); exit(EXIT_FAILURE); } printf("\nConnected to Oracle.\n"); /* Construct a PL/SQL block. */ strcpy(sqlstm, "DECLARE\ old_bal NUMBER(9,2);\ err_msg CHAR(70);\ nonexistent EXCEPTION;\ BEGIN\ :xtrans_type := UPPER(:xtrans_type);\ IF :xtrans_type = 'C' THEN\ UPDATE ACCTS SET BAL = BAL + :xtrans_amt\ WHERE ACCTID = :xacct_number;\ IF SQL%ROWCOUNT = 0 THEN\ RAISE nonexistent;\ ELSE\ :xstatus := 'Credit applied';\ END IF;\ ELSIF :xtrans_type = 'D' THEN\ SELECT BAL INTO old_bal FROM accts\ WHERE acctid = :xacct_number;\ IF old_bal = :xtrans_amt THEN\ UPDATE accts SET bal = bal - :xtrans_amt\ WHERE acctid = :xacct_number;\ :xstatus := 'Debit applied';\ ELSE\ :xstatus := 'Insufficient funds';\ END IF;\ ELSE\ :xstatus := 'Invalid type: ' || :xtrans_type;\ END IF;\ COMMIT;\ EXCEPTION\ WHEN NO_DATA_FOUND OR nonexistent THEN\ :xstatus := 'Nonexistent account';\ WHEN OTHERS THEN\ err_msg := SUBSTR(SQLERRM, 1, 70);\ :xstatus := 'Error: ' || err_msg;\ END;"); /* Parse the PL/SQL block. */ if (oparse(&cda, sqlstm, -1, 0, 2)) { error_handler(&cda); exit(EXIT_FAILURE); } /* Bind the status variable. */ if (obndrv(&cda, ":xstatus", -1, status, 70, 5, -1, (text *) 0, (text *) 0, -1, -1)) { error_handler(&cda); exit(EXIT_FAILURE); } /* Bind the transaction type variable. */ if (obndrv(&cda, ":xtrans_type", -1, trans_type, 1, 1, -1, (text *) 0, (text *) 0, -1, -1)) { error_handler(&cda); exit(EXIT_FAILURE); } /* Bind the account number. */ if (obndrv(&cda, ":xacct_number", -1, &acct_number, sizeof (int), 3, -1, (text *) 0, (text *) 0, -1, -1)) { error_handler(&cda); exit(EXIT_FAILURE); } /* Bind the transaction amount variable. */ if (obndrv(&cda, ":xtrans_amt", -1, &trans_amt, sizeof (float), 4, -1, (text *) 0, (text *) 0, -1, -1)) { error_handler(&cda); exit(EXIT_FAILURE); } for (;;) { printf("\nAccount number: "); scanf("%d", &acct_number); fflush(stdin); if (acct_number == 0) break; printf("Transaction type (D or C): "); scanf("%c", trans_type); fflush(stdin); printf("Transaction amount: "); scanf("%f", &trans_amt); fflush(stdin); /* Execute the block. */ if (oexec(&cda)) error_handler(&cda); printf("%s\n", status); } printf("Have a good day!\n"); exit(EXIT_SUCCESS); } void error_handler(cursor) Cda_Def *cursor; { sword n; text msg[512]; printf("\n-- ORACLE error--\n"); printf("\n"); n = oerhms(&lda, cursor->rc, msg, (sword) sizeof msg); fprintf(stderr, "%s\n", msg); if (cursor->fc > 0) fprintf(stderr, "Processing OCI function %s", oci_func_tab[cursor->fc]); }
strcpy(plsql_block, "BEGIN raise_salary(:emp_id, :amount); END;");
Then, the PL/SQL string can be bound and executed like a SQL statement.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |